标签：#pos

Introduction I used to think "connecting to a database" was one skill. Turns out it's two: connecting to a database chilling quietly on your own laptop, and connecting to one living in the cloud, behind a login, in this case, an SSL certificate that will not let you in until you treat it with respect. This week I did both. Same tool (Power BI), same dataset, two very different vibes. Grab a coffee, here's the full walkthrough local PostgreSQL first, then Aiven's cloud version, side by side, screenshots and all. Part 1: Local PostgreSQL → Power BI Step 1 : Create a schema Nothing fancy, just giving my table a home: CREATE SCHEMA powerbi ; Step 2 : Import the dataset Right-click the new schema → Import Data in DBeaver, point it at your CSV, and let the wizard do its thing. Step 3 : Check the table landed properly A quick peek at the columns to make sure nothing got mangled on the way in. Step 4 : Connect Power BI In Power BI Desktop: Get Data → Database → PostgreSQL database. In the Server field, type localhost (or 127.0.0.1 ) and your database name. localhost Choose Import , hit OK, and log in with your local username and password. Click Load . That's it. That's the whole local experience. Part 2: Aiven PostgreSQL (Cloud) → Power BI Now for the part that actually taught me something. Step 1 : Grab your connection details Everything you need lives on Aiven's Overview page: Host, Port, Database name, User, SSL mode. Your service URI will look something like this (don't worry, this isn't a real password, Aiven masks it in the console): postgres : // avnadmin : •••••••• @ pg - xxxxxxxx - yourproject . c . aivencloud . com : 22016 / defaultdb ? sslmode = require Step 2 : Import the dataset into Aiven Same DBeaver wizard as before, just pointed at the Aiven connection instead of local. CREATE SCHEMA powerbi ; Step 3 : Aiven's certificate. Download the CA cert from the Overview page: Now here's the part that actually tripped me up: Power BI's PostgreSQL connector doesn't ha

I open sourced a project I have been building on the side: a Go MCP server that connects Claude Code (or Cursor) directly to a live PostgreSQL database. Repo: github.com/gupta-akshay/postgres-mcp The problem it solves Most "AI plus database" workflows still look like this: copy SQL out of a chat window, paste it into a DB client, run it, copy the output back. It breaks flow, and the assistant never sees your actual schema, so it guesses. MCP fixes the connection problem. This server is what sits on the other end for Postgres. What it does The server exposes nine tools over MCP: Schema introspection - real tables, columns, indexes, constraints execute_sql - run queries directly (read only in restricted mode) explain_query - EXPLAIN ANALYZE, including against a hypothetical index get_top_queries - pull slow queries from pg_stat_statements Index advisors - recommend indexes using a greedy Database Tuning Advisor built on hypopg analyze_db_health - vacuum, XID wraparound, replication lag, invalid indexes, and more, checked in parallel That means you can ask "why is this query slow" and the assistant actually runs the EXPLAIN, checks the stats, and can simulate an index before anyone touches the schema. Why Go The project is inspired by the Python crystaldba/postgres-mcp . I rebuilt it from scratch in Go so it ships as a single ~15 MB static binary. No Python runtime, no dependency chasing. docker build , point Claude Code at it, done. Restricted mode wraps every call in a read only transaction, so write protection comes from Postgres itself, not string matching on the query text. Where to look The repo has the full setup instructions, the Docker config, and the test suite (unit, integration, and end to end against a real Postgres container with pg_stat_statements and hypopg ). CI fails under 95% coverage. If you spend real time in Claude Code or Cursor and also spend real time worrying about Postgres performance, take a look: github.com/gupta-akshay/postgres-mcp I wrote

Just for kicks, I took a look at Jersey Mike's IPO documents. Surely a sandwich shop would have no need to mention AI. But lo and behold.

My dashboard had a useMemo doing arithmetic it had no business doing. It was a Pokémon TCG Pocket collection tracker, but that part doesn't matter. What matters is that the home page needed to show three things: overall completion, completion per set, and which set you were closest to finishing. The way I'd built it, the browser was fetching every card and every owned record, then grinding through the math on each render to figure all of that out. It worked. It also got slower and harder to read every time the data grew or I added a metric. So I moved the aggregation out of React and into Postgres, and the surprising result was that my frontend got boring . Fewer hooks, less state, almost nothing left to break. That's the whole argument of this post: aggregation belongs in the database, and when you put it there, the React code that's left over is the kind of boring you actually want in the layer your users touch. What "fetching everything into React" actually looks like Here's the shape of the original dashboard. Load all the cards, load the user's owned rows, then derive everything on the client. const [ cards , setCards ] = useState < Card [] > ([]); const [ owned , setOwned ] = useState < OwnedCard [] > ([]); useEffect (() => { ( async () => { const { data : allCards } = await supabase . from ( " cards " ). select ( " * " ); const { data : ownedRows } = await supabase . from ( " user_cards " ) . select ( " card_id " ); setCards ( allCards ?? []); setOwned ( ownedRows ?? []); })(); }, []); const ownedIds = useMemo (() => new Set ( owned . map (( o ) => o . card_id )), [ owned ]); const perSet = useMemo (() => { const groups : Record < string , { total : number ; have : number } > = {}; for ( const card of cards ) { const g = ( groups [ card . set_id ] ??= { total : 0 , have : 0 }); g . total += 1 ; if ( ownedIds . has ( card . id )) g . have += 1 ; } return groups ; }, [ cards , ownedIds ]); const overall = useMemo (() => { const total = cards . length ; const ha

SokoFlow Build Log — Month 1 of 4 Last semester I set out on a new strategic plan to level up my software development skills through deliberate, project-based learning. That work produced one of the most ambitious things I've built so far: Sim-Pesa , a local-first transactional appliance that lets developers working in the M-Pesa ecosystem test and simulate STK Push workflows entirely on their own machines, without depending on the Daraja sandbox. I documented that build in 16 weekly posts, which you can find here . This semester, the focus shifts — from fintech foundations to cloud-native integration and real-world systems. The flagship project is SokoFlow , a conversational ERP for small Kenyan shopkeepers to track inventory and record sales entirely through WhatsApp chat. No app to download, no training session required — just natural language. Where Sim-Pesa lived in a controlled, predictable transactional world, SokoFlow steps into the mess of cloud-native reality: third-party API failures, webhook signature verification, the statelessness of HTTP, and container orchestration. The target audience shifts too — Kenyan SMEs operating on infrastructure that is often unreliable by design, not by exception. It's an ambitious project, but the goal was always to learn as much as possible from it. With the plan in place, I got to work. 1. The Vision of a Headless ERP The first real question I had to answer before writing a line of code: what does "headless" actually mean? Headless architecture decouples the frontend — the "head," or user interface — from the backend, the "body" that holds the data and business logic. A conventional ERP bundles both: backend plus a dashboard or UI on top. A headless ERP, by contrast, is just the engine. The brain. There's no built-in screen. So how do users interact with a system that has no interface of its own? SokoFlow doesn't actually care. It could be: WhatsApp SMS A web app A mobile app A voice assistant In this case, the "frontend

My "serverless" database was billing me like it never slept. Neon has this great feature called scale-to-zero. Your Postgres compute suspends when it's idle, so you only pay for the queries you actually run. For a pre-revenue product, that should cost a few cents a month. Mine ran 24/7. The compute never once scaled to zero. The culprit wasn't my app logic. It was my database driver. I was using postgres-js, which holds a persistent connection open to the database. From Neon's side, an open connection looks like activity, so it never suspended. It just stayed awake and kept quietly billing me. The fix was basically one conceptual change: switch to @neondatabase/serverless, the HTTP driver. Instead of a long-lived connection, every query becomes a stateless one-shot HTTP request. The query fires, the connection closes, and the compute is free to suspend. Scale-to-zero finally worked. The lesson I keep relearning as a solo founder: "serverless" is a property of your whole stack, not a checkbox on one service. One persistent connection upstream and the whole cost model breaks.

You add an enum to an entity, slap @Enumerated on it, and move on. Five seconds. It is the kind of decision nobody writes a design doc for. Then six months later a row comes back as SHIPPED when it was PAID , no exception was thrown, no query failed, and you spend an afternoon learning that the default you never thought about has been silently rewriting history. Here is the order lifecycle we will use the whole way through: public enum OrderStatus { PENDING , PAID , SHIPPED , DELIVERED } Five ways to store it. They are not equivalent, and the gap between them only shows up under change. @Enumerated(ORDINAL): store the position This is the default. Leave the annotation bare and JPA stores the enum's ordinal, its index in the declaration order. @Enumerated ( EnumType . ORDINAL ) private OrderStatus status ; PENDING is 0, PAID is 1, SHIPPED is 2, DELIVERED is 3. The column is a tidy little smallint . Everything works. Until someone needs a new status and adds it where it reads well: public enum OrderStatus { PENDING , PAID , CANCELLED , // inserted here SHIPPED , DELIVERED } CANCELLED is now 2. SHIPPED is 3. DELIVERED is 4. Every row written before this change still holds the old integer, so every order that was SHIPPED (2) now reads back as CANCELLED . The database is correct. Your data is wrong. And nothing told you. If you are stuck with ORDINAL on a legacy schema, pin it with a test that fails the build the moment someone reorders: @Test void ordinalsAreFrozen () { assertEquals ( 0 , OrderStatus . PENDING . ordinal ()); assertEquals ( 1 , OrderStatus . PAID . ordinal ()); assertEquals ( 2 , OrderStatus . SHIPPED . ordinal ()); assertEquals ( 3 , OrderStatus . DELIVERED . ordinal ()); } New constants may only be appended. The test turns an invisible runtime corruption into a loud compile-time-ish failure. It is a guardrail, not a fix. @Enumerated(STRING): store the name Store the constant name instead of its position. @Enumerated ( EnumType . STRING ) private OrderS

Originally published on bckinfo.com Redis with Docker Compose: Persistence, Security, and Production-Ready Configuration Table of Contents Why Redis Containers Lose Data RDB vs AOF: Choosing a Persistence Strategy Basic Setup with Docker Compose Full Persistence Configuration Securing Redis in Docker Setting Resource Limits Redis in a Multi-Service Stack Backing Up and Restoring a Redis Volume Common Issues and Quick Fixes Closing Notes Redis is one of the most common services to run in Docker — it's fast to spin up, lightweight, and perfect for caching, session storage, and queues. But that same simplicity hides a trap: by default, Redis in Docker stores everything in memory, and the moment a container is removed, all of that data disappears . This guide walks through setting up Redis with Docker Compose the right way — covering persistence, authentication, resource limits, and the health checks you need before putting it anywhere near production. Why Redis Containers Lose Data A Docker container is meant to be disposable. That's a feature for stateless services, but it's a liability for a database like Redis. If you start a plain Redis container without a mounted volume, here's what happens: The container writes its dataset only inside its own writable layer. docker compose down or docker rm removes that layer entirely. The next time the container starts, Redis initializes with an empty dataset. This single oversight accounts for a large share of "we lost our session data" incidents in small teams running Redis in containers for the first time. The fix is straightforward once you understand the two persistence mechanisms Redis offers. RDB vs AOF: Choosing a Persistence Strategy Redis supports two persistence models, and production setups typically combine both: RDB (Redis Database snapshots) Point-in-time snapshots of the dataset, saved at intervals you define. Fast to restore, but you can lose any writes that happened after the last snapshot. AOF (Append Only Fil

I Built Commercial Redis CDC Source Drivers for Ruby — Here's What I Learned For the past couple of years I've been building a Change Data Capture (CDC) ecosystem for Ruby. Like many CDC projects, it started with PostgreSQL. PostgreSQL's Write-Ahead Log (WAL) is an excellent source of truth: durable, ordered, replayable, and well understood. It provides exactly the properties you want when you're building reliable event pipelines. But the deeper I went into distributed systems, the more I realized something important. Many systems don't observe change from PostgreSQL first. They observe it from Redis. Redis often sits at the front of modern architectures: Redis Streams carry application events. Pub/Sub distributes transient state changes. Keyspace notifications react to cache invalidation and key expiry. Redis Cluster routes events across multiple primaries. In many systems, Redis sees a change before PostgreSQL ever commits it. That raised an interesting question: Can Redis become a first-class Change Data Capture source? The obvious answer is "yes." The interesting answer is "yes—but not in the same way PostgreSQL does." That distinction eventually became cdc-redis-pro , a commercial Redis source driver for the Ruby CDC ecosystem. This article isn't a product announcement. It's an engineering write-up about the architectural decisions behind the project, the tradeoffs Redis forces you to make, and the execution model that ultimately emerged. Redis Doesn't Have One CDC Interface One misconception I frequently encounter is the assumption that Redis has an equivalent of PostgreSQL's WAL. It doesn't. Instead, Redis exposes several completely different mechanisms for observing change. Source Delivery Replay Streams At-least-once Yes Pub/Sub At-most-once No Sharded Pub/Sub At-most-once No Keyspace Notifications At-most-once No At first glance they all look like "events." Operationally they're completely different systems. Streams are durable. Pub/Sub isn't. Keyspace not

The Post-Mortem Nobody Learns From I've sat through hundreds of post-mortems. Most follow the same pattern: something breaks, someone writes a Google Doc, we have a meeting, we list action items, nobody follows up, the same thing happens again in 3 months. Here's how to break the cycle. The Blameless Culture Trap "Blameless" doesn't mean "actionless." The biggest failure mode I see is teams that use blameless culture as an excuse to avoid accountability. Blameless means: we don't punish the person who pushed the bad deploy. Blameless does NOT mean: nobody is responsible for fixing the systemic issue. My Post-Mortem Template # Incident: [SERVICE] [SYMPTOM] on [DATE] ## Impact - Duration: X minutes - Users affected: N - Revenue impact: $X - SLO budget consumed: X% ## Timeline (UTC) - HH:MM - First alert fired - HH:MM - On-call acknowledged - HH:MM - Root cause identified - HH:MM - Fix deployed - HH:MM - Service recovered - HH:MM - All-clear declared ## Root Cause [2-3 sentences. Technical but readable.] ## Contributing Factors 1. [Factor that made the incident possible] 2. [Factor that made detection slow] 3. [Factor that made resolution slow] ## What Went Well - [Something that worked] - [Something that helped] ## What Went Wrong - [Process failure] - [Technical gap] ## Action Items | Action | Owner | Priority | Due Date | Status | |--------|-------|----------|----------|--------| | ... | ... | P1/P2/P3 | ... | Open | ## Lessons Learned [1-2 paragraphs of genuine insight] The Action Item Problem Action items from post-mortems have a 30% completion rate industry-wide. That's terrible. Here's why: Too many items (I've seen post-mortems with 15 action items) No clear ownership No deadline No follow-up mechanism Competing with feature work The Fix: Three Rules Rule 1: Maximum 3 action items per post-mortem. If you can't narrow it to 3, you haven't identified the real problems. Rule 2: Every action item gets a JIRA ticket linked to the next sprint. Not "someday." Not "bac

I'll be honest: I almost did multi-tenancy the wrong way. When I started building InspectIQ "a SaaS platform for Florida home inspectors" my first instinct was to add a tenant_id column to every table and filter it in the application layer. Every query would have a WHERE tenant_id = :current_tenant clause. Simple, familiar, done. Then I thought about what happens when you forget one. One missing WHERE clause. One endpoint that skips the filter. One inspector sees another inspector's client data. In a home inspection business, that's not just a bug — it's a HIPAA-adjacent nightmare and a trust-destroying moment with your first customer. So I did it properly from day one: Row Level Security at the database layer. What is Row Level Security? RLS is a PostgreSQL feature that lets you define policies directly on tables. When a user queries a table, the policy runs automatically, before your application code even sees the results. You can't forget to apply it. You can't bypass it with a careless JOIN. It's enforced at the lowest possible layer. For a multi-tenant SaaS, this is exactly what you want. How I implemented it Every table in InspectIQ has this pattern: ALTER TABLE inspections ENABLE ROW LEVEL SECURITY ; ALTER TABLE inspections FORCE ROW LEVEL SECURITY ; CREATE POLICY tenant_isolation ON inspections USING ( tenant_id = NULLIF ( current_setting ( 'app.current_tenant_id' , true ), '' ):: uuid ); The FORCE is important — it applies the policy even to the table owner. No superuser backdoor. The tenant context comes from the JWT. When an inspector logs in, their tenant_id is embedded as a custom Cognito claim. The FastAPI middleware extracts it and sets it at the start of every request: await session . execute ( text ( f " SET LOCAL app.current_tenant_id = ' { tenant_id } '" ) ) SET LOCAL scopes the setting to the current transaction. When the transaction ends, it's gone. No leakage between requests. Aurora PostgreSQL Serverless v2 I'm running this on Aurora PostgreSQ

Deploying a Spring Boot backend should be simple in theory. Build the JAR, set the environment variables, connect the database, and ship it. In practice, my deployment exposed several assumptions that worked locally but failed immediately in the cloud. I recently deployed a modular Spring Boot application to Render using Docker, Render Blueprint, PostgreSQL, Redis, Flyway migrations, Spring profiles, Hibernate/JPA, and environment variables. The application worked locally with MySQL and Redis, but deployment exposed several production-specific issues that were easy to miss in local development. This article documents the problems, why they happened, and how I fixed them properly. Who This Article Is For This article is useful if you are deploying a Spring Boot application to Render and your local setup uses MySQL, Redis, Flyway, Docker, or a multi-module Maven structure. It is especially relevant if you are moving from a local MySQL setup to PostgreSQL in the cloud. The Stack The backend was a Java 17 Spring Boot application with multiple Maven modules: alagbafo/ ├── api-contracts ├── core ├── users ├── orders ├── payments ├── wallet ├── notifications ├── admin ├── subscriptions └── app The app module was the actual Spring Boot entry point. Locally, the project used MySQL and Redis: spring.datasource.url = jdbc:mysql://localhost:3306/alagbafo spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver spring.data.redis.host = localhost spring.data.redis.port = 6379 For Render, the target setup was: Spring Boot app PostgreSQL database Redis-compatible Key Value store Docker deployment Flyway migrations Render Blueprint was the best fit because it allowed the infrastructure to be described in a render.yaml file. Step 1: Dockerfile for a Multi-Module Spring Boot App Because the project was a multi-module Maven application, the Dockerfile had to copy all module pom.xml files before copying the source code. This improves Docker layer caching because dependencies can b

I used Postman for years. It was the first thing I installed on every new laptop, the default answer...

We opened Chapter 1 with a single line, SELECT * FROM users WHERE id = 1 . For that line to leave the client and come back as a result row, the PostgreSQL backend went through five stages. First it decided which processing path the message should take; then the parser and analyzer turned the text into a tree and gave it meaning from the catalog. The rewriter expanded views and injected policies to transform the tree, the planner weighed the possible execution paths by cost and picked the cheapest one, and the executor followed that plan, pulling up one tuple at a time and sending them back to the client. Chapter 1 was a story about how a query is processed . What tree a given SQL becomes, what plan it turns into, in what order it runs. From start to finish, a chain of logical transformations. But what every one of those stages ultimately deals with is data. The executor pulls up tuples, yet where on disk those tuples lie and in what shape, how they come up into memory, Chapter 1 never asked. When the planner judged an index scan cheaper than a sequential scan, it never opened up what that index physically is. Chapter 1 followed only the logical journey of a query, leaving untouched the substance of the data that journey stands on. Chapter 2, Storage & Access Methods, opens up that substance. In what unit data sits on disk (page), where disk and memory meet (buffer manager), where and how a row survives (heap), and how that row is found quickly (B-tree and the specialized indexes). The very tuple the planner weighed by cost and the executor pulled up in Chapter 1, where it actually came from and how it came to be there, is what Chapter 2 reveals. If Chapter 1 was the logical life of a query, Chapter 2 is the physical dwelling of data. We now look at how the data a query reaches for actually lives on disk.

A scan node sits at the leaf of the tree and pulls rows from a single table. A join node sits in the middle and brings together the rows that its two children send up. It takes one row from users , one row from orders , checks whether they belong to the same user, and if they match, emits the combined row. PostgreSQL has three nodes for this one job: NestLoop, HashJoin, and MergeJoin. The reason a single task splits into three nodes is much like the reason scans did. There is more than one way to find matching pairs from two inputs, and which way is cheapest depends on the size of the inputs and the shape of the join condition. Deciding which way is cheapest, by costing the alternatives, was the planner's job in an earlier chapter. This section looks at what those three nodes actually do when they execute. Given the same two tables, the three find matches in completely different ways, and that difference in approach is exactly what tells them apart. How the three nodes route requests All three join nodes are internal nodes with two children. One child is called the outer, the other the inner. All three run on the Volcano model's pull framework: when the parent asks for the next row, the join node takes rows from its two children, builds one matched row, and sends it up. The only difference is the order and manner in which it routes pull requests to its two children. NestLoop pulls the inner from the start all over again for each outer row it receives. HashJoin slurps the inner in one pass to build an index in memory, then takes outer rows one at a time and probes that index. MergeJoin, on the assumption that both sides are sorted in the same order, advances both sides one step at a time in lockstep. NestLoop: rescan the inner for every outer row The simplest method is NestLoop. As the name says, the loops are nested. The outer loop takes one row from the outer; the inner loop scans the inner from beginning to end, looking for inner rows that match that outer row. Wh

By the time 1.4 ends, the planner has produced one PlannedStmt. Inside it is an execution tree built from Plan nodes, frozen into a form you can follow step by step, something like "go into the primary key index on users, fetch the one matching row, then output that whole row." But that is still only a blueprint. Reading actual pages off disk, picking out the rows that match the condition, handing results back to the caller: none of that has happened yet. The stage that takes that blueprint and produces actual rows is the executor. The difference between the planner and the executor is the difference between deciding and doing. The planner was the stage that weighed "which index, in what order, with what join method" by cost and chose . The executor takes the chosen approach and carries it out as is . There is nothing left to choose. It just runs the nodes baked into the plan tree and pulls rows out of them. To run it, the executor takes the Plan tree it received and turns it into a PlanState tree. The Plan tree is the static blueprint the planner made, and it does not change during execution. But to actually run, each node needs state that changes as execution proceeds: which row it is reading now, whether the hash table is fully built, what tuple it has buffered from a child. So when execution begins, a PlanState tree with the exact same shape as the Plan tree is created. The blueprint Plan tree is left untouched, and the running state lives in that PlanState tree instead. How the executor produces result rows is the heart of the stage. The executor does not build the entire result set at once and stack it up. Instead, it asks the topmost node of the tree for "the next row," and that request travels down the tree to the leaves. When a leaf scan node reads one row from a page and passes it up to its parent, that row climbs up one level at a time through joins and filters until it reaches the top. The top sends that single row to the caller (the client, or the targe

Everything from 1.4.1 through 1.4.9 happened inside a single function, standard_planner() . Building paths, costing them, searching for a join order, estimating cardinality from statistics: all of it runs inside that one function. Yet PostgreSQL does not call standard_planner() directly. It puts another function, planner() , one step ahead of it, and has planner() call standard_planner() . And planner() can be made to call some other function instead of standard_planner() . That replacement is what the planner hook enables. When pg_stat_statements measures per-query planning time, or pg_hint_plan rewrites a plan according to hints, it all goes through this hook. Let's look at how PostgreSQL provides a way to observe or change planning behavior without touching a single line of the core, and how external code plugs into it. All planner() does is check the hook The body of planner() is essentially this. if ( planner_hook ) result = ( * planner_hook ) ( parse , query_string , cursorOptions , boundParams ); else result = standard_planner ( parse , query_string , cursorOptions , boundParams ); planner_hook is a global function pointer. Its default value is NULL , in which case standard_planner() is called right away. A plain PostgreSQL build always takes this path: planner_hook is empty, so the incoming query goes straight to standard_planner() . The key here is the type of planner_hook . typedef PlannedStmt * ( * planner_hook_type ) ( Query * parse , const char * query_string , int cursorOptions , ParamListInfo boundParams ); This signature is identical, down to the character, to that of planner() and standard_planner() . It takes the same Query and returns the same PlannedStmt (the execution plan). So external code only has to write a planner function matching this type and store its address in planner_hook . Let's call this function, written by external code to register in planner_hook , a custom planner function. The moment its address is stored, every planning reque

In the earlier posts of this series, we looked at practical query tuning tips and how to read and interpret query plans . A recurring theme in both was: "add an index here." But "add an index" is a bit like saying "use the right tool" — the interesting part is which one. PostgreSQL ships with several index types, each tuned for a different kind of data and query. Picking the wrong one means PostgreSQL quietly ignores your index and goes back to a sequential scan. In this post, we'll walk through the main index types, when each shines, and the special index variations (composite, partial, covering, expression) that often matter more than the type itself. Setting the Scene: Schema and Sample Data We'll reuse the same schema from the previous posts, with one small addition — a metadata JSONB column and a tags array on orders , so we can explore the more exotic index types. CREATE TABLE customers ( id SERIAL PRIMARY KEY , customer_name VARCHAR ( 255 ), email VARCHAR ( 255 ), created_at TIMESTAMPTZ DEFAULT NOW () ); CREATE TABLE orders ( id SERIAL PRIMARY KEY , customer_id INT REFERENCES customers ( id ), order_date TIMESTAMPTZ DEFAULT NOW (), total_amount NUMERIC ( 10 , 2 ), status VARCHAR ( 20 ), tags TEXT [], metadata JSONB ); -- Insert sample customers INSERT INTO customers ( customer_name , email ) SELECT 'Customer ' || i , 'customer' || i || '@example.com' FROM generate_series ( 1 , 1000000 ) AS s ( i ); -- Insert sample orders INSERT INTO orders ( customer_id , order_date , total_amount , status , tags , metadata ) SELECT ( RANDOM () * 1000000 ):: INT , NOW () - interval '1 day' * ( RANDOM () * 365 ):: int , ( RANDOM () * 500 + 20 ), ( ARRAY [ 'pending' , 'shipped' , 'delivered' , 'cancelled' ])[ FLOOR ( RANDOM () * 4 + 1 )], ARRAY [( ARRAY [ 'gift' , 'priority' , 'fragile' , 'bulk' ])[ FLOOR ( RANDOM () * 4 + 1 )]], jsonb_build_object ( 'channel' , ( ARRAY [ 'web' , 'mobile' , 'store' ])[ FLOOR ( RANDOM () * 3 + 1 )]) FROM generate_series ( 1 , 1000000 ) AS s ( i

pgrac is an open attempt to rebuild Oracle RAC's core machinery (shared-everything storage, multiple active nodes all writing one database, a cluster-wide change number) on top of PostgreSQL 16. Build log #1 laid out the four problems that fight back. This one is about the problem that turns a node failure into silent data corruption, and the first, deliberately modest, layer pgrac ships against it. The failure mode In a shared-nothing cluster an evicted node is mostly harmless: it owns its own disks, so the cluster routes around it. In a shared-everything cluster the same event is dangerous, because every node writes the same storage. Picture the classic split: node 2 misses heartbeats, the cluster declares it dead and remasters its work elsewhere, but node 2 is not actually dead. It is frozen on a long GC pause, or its interconnect NIC flaked, and it is about to wake up and finish the write it started. Now two nodes believe they own the same blocks, and shared storage will accept both writes. That is not a crash. It is corruption you find three days later. Oracle RAC's answer is I/O fencing: before remastering a dead node's resources, you make certain it can no longer touch the storage, with STONITH, SCSI-3 persistent reservations, or a hardware watchdog. The node is fenced at a layer below its own software, because the whole point is that you cannot trust the dead node's software to behave. That hardware layer is real work, and it is not what pgrac built first. What it built first is the layer above it: an in-process cooperative write-fence, now default-ON. The rest of this is precise about what that does and does not buy you, because "we have fencing" is the kind of claim that is worth less than nothing if it is overstated. A fence needs an authority everyone can agree on You cannot fence on local opinion, because the whole problem is that the dead node disagrees about being dead. Authority has to live on durable, shared, quorum-backed storage. pgrac writes a sm

PostgreSQL Error 22036: non numeric sql json item PostgreSQL error code 22036 ( non numeric sql json item ) occurs when a SQL/JSON path expression attempts to perform a numeric operation on a JSON item that is not a number — such as a string, boolean, array, or object. This error was introduced alongside the SQL/JSON Path feature in PostgreSQL 12 and typically surfaces in queries using jsonb_path_query , jsonb_path_exists , or the @@ and @? operators. Top 3 Causes 1. Numeric Values Stored as Strings The most common cause is JSON data where numbers are stored as quoted strings (e.g., "price": "100" instead of "price": 100 ). This frequently happens with data from external APIs or legacy systems that don't enforce type consistency. -- Triggers 22036: "price" is a string, not a number SELECT jsonb_path_query ( '{"price": "100"}' , '$.price + 50' ); -- ERROR: non numeric SQL/JSON item -- Fix: Use the .double() conversion method in JSON Path SELECT jsonb_path_query ( '{"price": "100"}' , '$.price.double() + 50' ); -- Result: 150 -- Alternative fix: Cast at the SQL level SELECT ( data ->> 'price' ):: numeric + 50 FROM ( SELECT '{"price": "100"}' :: jsonb AS data ) t ; -- Result: 150 2. Arithmetic Applied Directly to Arrays or Objects Developers sometimes write JSON Path expressions that target an entire array or object instead of individual elements, then attempt arithmetic on the result. -- Triggers 22036: $.scores returns an array, not a number SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores + 10' ); -- ERROR: non numeric SQL/JSON item -- Fix: Target a specific array index SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores[0] + 10' ); -- Result: 90 -- Fix: Use wildcard to apply operation to each element SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores[*] + 10' ); -- Results: 90, 100, 80 -- Fix: Use unnest for aggregation use cases SELECT elem :: numeric + 10 FROM jsonb_array_elements ( '{"scores": [80, 90, 70]}' :: jsonb ->