DEV Community: Jatin Jain Saraf

Every Postgres Write Triggers Five Background Processes. Here's What Each One Actually Does.

Jatin Jain Saraf — Mon, 14 Sep 2026 05:05:17 +0000

An UPDATE statement returns in two milliseconds. From where you're sitting, that's the whole story: the row changed, the client got an acknowledgment, done. Underneath, Postgres just did five separate jobs to make that write durable, recoverable, reusable, and still fast for the next query to plan correctly. Most of the time you never see any of them. Then a dashboard shows periodic latency spikes with no slow query in sight, or pg_stat_activity shows a backend "stuck" on something with a name you've never had to learn, and you're suddenly debugging a part of Postgres that has been running the entire time.

This is a walkthrough of that background layer: the Write-Ahead Log, checkpoints, the wait events that make both of them visible, and the vacuum/analyze cycle that cleans up after every write and keeps the query planner honest.

WAL: the black box that makes crash recovery possible

Postgres never writes a change directly to a table's data file first. It writes a record of the change to the Write-Ahead Log (WAL) first, and only later, asynchronously, does the actual data file get updated. This is the same idea as an aircraft's flight data recorder: it doesn't prevent anything from going wrong, but if the plane goes down, the black box is what lets you reconstruct exactly what happened, in order, right up to the last recorded moment.

If Postgres crashes between "WAL record written" and "data file updated," the data file is allowed to be behind. On restart, Postgres replays the WAL from the last confirmed checkpoint forward and reconstructs every change that was durably logged but not yet applied to the heap. That's the entire crash-recovery guarantee, and it's also the entire replication guarantee: a replica is, at its core, a process continuously replaying WAL records shipped from the primary.

Why this matters in production: synchronous_commit controls whether a transaction waits for its WAL record to actually hit disk before returning success to the client. Turn it off (or set it to a weaker level like local) and writes get faster, because you're no longer waiting on an fsync. You're also now willing to lose the last few transactions if the server crashes before that WAL hits disk. That's a real trade-off teams make deliberately for high-throughput, loss-tolerant write paths, and a real trade-off teams make by accident when they copy a config from a benchmark blog post.

Checkpoints: the point WAL gets allowed to forget the past

WAL can't grow forever, and replaying an unbounded WAL after a crash would make recovery take unbounded time. A checkpoint is Postgres periodically flushing every dirty page sitting in shared buffers out to the actual data files, so that everything the WAL described up to that point is now durably reflected on disk. Once a checkpoint completes, the WAL segments before it are no longer needed for crash recovery and can be recycled or removed (subject to what replication and archiving still need them for).

Checkpoints run on a schedule controlled by two settings: checkpoint_timeout (time-based, five minutes by default) and max_wal_size (a soft cap on how much WAL can accumulate before a checkpoint is forced early). Whichever threshold is hit first triggers the checkpoint.

Why this matters in production: a checkpoint means writing out every dirty page at once, which is a burst of I/O. If that burst happens all at once, you get the classic "checkpoint spike," periodic latency jumps that correlate with wall-clock time, not query volume. checkpoint_completion_target exists specifically to spread that I/O out over the checkpoint interval instead of dumping it all at once. If your latency graphs show a sawtooth pattern with a period matching your checkpoint_timeout, that's usually the first setting worth checking, not the query itself.

Wait events: the window into both of the above

pg_stat_activity has two columns, wait_event_type and wait_event, that tell you what a backend is actually blocked on right now, if it's blocked on anything. This is the diagnostic surface that turns "the query is slow" into "the query is waiting on something specific":

SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

The wait_event_type column groups events into categories: Lock (waiting on a row, table, or advisory lock held by another transaction), LWLock (waiting on an internal lightweight lock protecting shared memory structures), IO (waiting on an actual disk read or write), BufferPin, IPC, Timeout, and a few others. A backend showing IO wait events tied to WAL activity, or an LWLock wait tied to the WAL insert path, is a backend that's been caught behind the exact mechanism described above: it's trying to get its change durably logged and something (usually disk throughput, sometimes a checkpoint mid-flush) is making it wait its turn.

Why this matters in production: the specific wait event names attached to WAL and checkpoint activity have changed across major Postgres versions as the I/O statistics system was reworked, so don't memorize a table of exact strings from a blog post (including this one) and assume it matches your version. Instead, learn the query above, run it against your own instance during a slow period, and read whatever wait_event_type/wait_event pair comes back against the current docs for your version. That habit outlives any specific release.

Vacuum: cleaning up after MVCC, not just reclaiming disk

Postgres never overwrites a row in place on UPDATE or DELETE. MVCC (multi-version concurrency control) means an UPDATE writes a new row version and marks the old one dead, and a DELETE just marks a row dead, because other transactions that started before yours might still need to see the old version. Those dead row versions don't disappear on their own. VACUUM is the process that finds them, confirms no transaction still needs them, and marks that space reusable by future inserts on the same table.

Plain VACUUM does not shrink the file on disk (only VACUUM FULL does, by rewriting the whole table, which takes an exclusive lock). It reclaims space within the existing file for future rows to reuse. autovacuum runs this automatically, table by table, once the number of dead tuples crosses a threshold (autovacuum_vacuum_threshold plus a percentage of the table's row count, autovacuum_vacuum_scale_factor, which defaults to 20%). Vacuum also has a second job unrelated to space at all: it's what prevents transaction ID wraparound, a much rarer but far more serious failure mode where an un-vacuumed table's transaction ID counter runs out of room entirely.

Why this matters in production: vacuum generates its own WAL records, since it's modifying pages, so a large autovacuum run on a big table shows up as real write volume, not a free background chore. It also competes for the same I/O bandwidth a checkpoint is using, which is why an aggressive autovacuum kicking off right as a checkpoint is flushing dirty pages is a common, specific cause of a latency spike that doesn't correlate with any query change. Database Efficiency 101 goes deeper on the bloat side of this; the piece worth adding here is that vacuum isn't an isolated background job, it's sharing the same WAL and I/O path as everything above.

ANALYZE: the step that keeps the planner honest

ANALYZE is a separate operation from vacuum, even though autovacuum triggers both and people often say "autovacuum" to mean either. ANALYZE samples a table's rows and updates the planner's statistics (row counts, most common values, distribution histograms) stored in pg_statistic. The query planner uses those statistics, not a live count, to estimate how many rows a WHERE clause will match and decide whether a sequential scan, index scan, or bitmap heap scan will be cheapest.

Stale statistics don't cause a query to return wrong results. They cause the planner to misjudge how many rows it's dealing with, and a bad row-count estimate is one of the most common root causes behind "this query used to be fast and now it isn't," with no schema change and no data corruption involved, just a planner making a good decision on outdated information. autovacuum_analyze_threshold and autovacuum_analyze_scale_factor (10% by default) control when this runs automatically, similarly to vacuum's thresholds, and an insert-heavy, delete-free table (an append-only events table, for instance) still needs ANALYZE on a schedule even though it may rarely need vacuuming for dead tuples.

Why this matters in production: after a large bulk load, either via a migration or an initial data import, running ANALYZE explicitly before serving real traffic is worth the few seconds it costs. Waiting for autovacuum to notice on its own schedule means the planner may make its first few hours of decisions on statistics from before the table had any real data in it.

Putting the five back together

None of these are separate systems bolted onto Postgres. They're one pipeline: WAL makes every write durable and replicable, checkpoints periodically settle that WAL's guarantees into the actual data files and bound how long crash recovery takes, wait events are the live diagnostic surface showing you when a backend is stuck somewhere in that path, vacuum reclaims the space MVCC leaves behind and prevents wraparound, and ANALYZE keeps the planner's model of your data close enough to reality to keep picking good plans. A slow query is sometimes a bad query. Just as often, in a system that's been running fine for months, it's one of these five processes falling behind, and pg_stat_activity is the first place that tells you which one.

Your ORM Isn't Lying to You. It's Just Not Telling You Everything.

Jatin Jain Saraf — Thu, 03 Sep 2026 13:32:46 +0000

An ORM is a translator sitting in a business meeting. It's fluent, it's fast, and after a while you stop paying attention to the original language entirely. You trust it. Most days that trust is fine. Then one sentence gets mistranslated, the deal falls apart, and you realize you never actually learned the language yourself. You just learned to rely on someone who did.

That's what Prisma, Drizzle, TypeORM, and Sequelize do to your relationship with Postgres. They don't lie. They just don't tell you everything, and the gaps are exactly where production incidents live.

This post is not "ORMs are bad, use raw SQL." Most apps should use one. It's a map of the specific places where the ORM's model of your database and Postgres's actual behavior diverge, so you know where to look when something is slow, wrong, or gone.

Why teams reach for an ORM in the first place

Before the shortcomings, the honest case for using one:

Types generated from your schema. Rename a column, your build breaks at compile time instead of at 2am in production.
Migrations as code. Schema changes are version-controlled, reviewable in a pull request, and reproducible across environments.
Boilerplate CRUD disappears. Most application code touching the database is SELECT, INSERT, UPDATE, DELETE on one or two tables. An ORM turns that into a few lines instead of a hand-written query and a row-mapping function, every time.

None of that is wrong. The problem is what it costs you when you stop thinking about the SQL underneath.

The N+1 query, hiding in plain sight

This is the failure mode that catches the most teams, because the ORM makes it look identical to the correct version.

const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

That's 1 query to fetch users, plus N more queries, one per user, to fetch their posts. With 10 users you might not notice. With 10,000 users, those extra round trips can turn an otherwise simple endpoint into a timeout.

The dangerous part isn't that this pattern exists. Every engineer knows N+1 queries are bad. The dangerous part is that the ORM's relational syntax looks the same whether it's doing this or a single join:

const users = await prisma.user.findMany({
  include: { posts: true },
});

Relation loading isn't necessarily equivalent to the join you would write by hand. Depending on the ORM and its configuration, that one line can compile down to a single query with a join, or to a batch of separate queries stitched together in application code. Prisma, for example, defaults to the second approach for include on one-to-many relations: it batches the related rows with a SQL IN clause rather than emitting a native JOIN, unless you're on a relation mode or raw query that says otherwise. You can't reliably tell which one you got just by reading the application code. Turn on query logging and look.

Why this matters in production: N+1 doesn't necessarily mean N new connections. Queries can reuse the pool. The problem is the number of database round trips. Under concurrent traffic, many requests running their own N+1 loops can keep pooled connections busy for longer, increasing pool contention and request latency. This is where an ORM's convenience directly creates the incident.

The SQL your ORM actually generates is not the SQL you'd write

Turn on query logging for a week on a real Prisma or TypeORM project and read what comes out. It is rarely what you'd write by hand.

With nested includes, some ORMs, Prisma among them, can issue several separate queries and stitch the results together in application code instead of one query with joins. That's not a bug, it's a design trade-off, and it varies by ORM, version, and configuration. But it means EXPLAIN ANALYZE on the query you think you're running and the query Postgres actually saw can tell two different stories, and only one of them is visible in your codebase.

Why this matters in production: if you've read through the query planning module of the PostgreSQL In-Depth course, you know Postgres picks between a sequential scan, an index scan, and a bitmap heap scan based on cost estimates for the specific query it receives. An ORM that reshapes your intent into three smaller queries instead of one joined query gives the planner three separate decisions to make, rather than one plan for the operation as a whole. You lose the planner's ability to optimize across the whole operation.

The fix isn't abandoning the ORM. It's treating query logging as mandatory, not optional, and periodically running EXPLAIN ANALYZE on queries that matter to your application's hot paths or have noticeable latency.

Migrations: the auto-diff can silently drop data

Most ORMs generate migrations by diffing your schema file against the last known state and producing SQL. This works well for additive changes. It gets dangerous on renames.

Rename a column in your schema file, and a schema diff doesn't always have enough information to know that you intended a rename rather than a drop-and-add. Some ORMs can detect renames, while others may generate a drop-and-add migration depending on the schema change and migration workflow. The generated migration can be:

ALTER TABLE users DROP COLUMN full_name;
ALTER TABLE users ADD COLUMN display_name text;

instead of the safe version:

ALTER TABLE users RENAME COLUMN full_name TO display_name;

The first version runs without error. It also silently discards every value in that column on deploy.

Why this matters in production: a migration that runs clean and drops a column's worth of data doesn't announce itself. Nobody gets an error. You find out when a support ticket comes in asking why a field is suddenly empty. This is one of the highest-leverage things to check before merging any ORM-generated migration: read the actual SQL it produces, not just the schema diff you wrote.

Connection pooling: every ORM has different defaults, and it matters more than ever in serverless

If you've read Connection Pooling in the Serverless Era, you already know that each connection Postgres accepts consumes server resources, whether or not it's doing anything, and enough concurrent connections becomes a significant resource cost. ORMs each ship their own default pool size, their own idle-timeout behavior, and their own assumptions about how long your process lives.

Those defaults were mostly written assuming a long-lived server process. In a serverless or edge function, where every invocation can be a fresh process, an ORM's default pool of, say, 10 connections means every cold start tries to open 10 connections to Postgres. Multiply that by concurrent invocations during a traffic spike and you can exhaust your database's max connection limit in seconds, well before you exhaust CPU or memory on the compute side.

Why this matters in production: this is a common cause of "it works locally, it falls over under load" for teams deploying ORM-based apps to serverless platforms. The fix usually involves an external pooler (PgBouncer, RDS Proxy, or a managed equivalent) sitting between your functions and Postgres, plus explicitly tuning the ORM's own pool size down, not trusting the default. One trap worth knowing before you reach for a pooler: PgBouncer's transaction-mode pooling, the mode most serverless setups need, doesn't preserve a session across queries, and ORMs that rely on prepared statements (Prisma and TypeORM both do, by default) can fail against it in ways that only show up under load. Check your pooler's mode against your ORM's prepared-statement behavior before you assume adding one fixes everything.

Write-heavy systems: where the ORM's convenience becomes the bottleneck

Everything above assumes a fairly typical application: reads dominate, writes happen when a user submits a form or an admin edits a record. That's most CRUD apps, and it's exactly where an ORM's ergonomics pay for themselves. It is not a blockchain indexer, an event pipeline, or anything else ingesting a continuous, high-volume stream of writes. Those systems are where an ORM's row-at-a-time model of the world stops being a convenience and starts being the bottleneck.

A blockchain indexer is a clean example because the write pattern is unforgiving: every new block can carry hundreds or thousands of events (transfers, contract calls, state changes), and all of them need to land in Postgres before the indexer can call itself caught up. Three places an ORM adds real cost in that path:

Row-by-row INSERT instead of COPY. Postgres's COPY protocol is built specifically for loading large volumes of rows in one streamed operation, and it is meaningfully faster than even a well-batched multi-row INSERT. Most ORMs don't expose COPY at all. Their bulk-insert APIs (createMany, bulkCreate, and similar) are a real improvement over inserting one row per statement, but under the hood they're still building INSERT statements with many value tuples, not streaming through COPY. For an indexer processing thousands of events per block, that gap compounds every single block.
Object hydration on the way in and out. An ORM that maps every row to and from a model instance is doing real work per row: validation, type coercion, applying defaults. That cost is invisible at CRUD volumes and very visible at ingestion volumes, where it adds CPU and allocation overhead to every event in the stream, not just the ones a human is waiting on.
More, smaller transactions than the workload needs. An ORM's ergonomic defaults tend toward one transaction per logical operation. At indexer-scale throughput, that can mean far more transaction and WAL overhead than a design that deliberately batches writes into fewer, larger transactions per block.

Why this matters in production: all three of these show up as the same symptom eventually, insert throughput that can't keep up with the source it's indexing, and the indexer falls further behind with every block. If you've read Taming PostgreSQL Replication Lag in Real-Time Blockchain Indexers, this is the same category of problem from a different angle: it's not always replication configuration that causes an indexer to lag, sometimes it's the write path itself, one ORM abstraction away from the bulk-loading tools Postgres actually gives you for this job.

None of this means an ORM is disqualified from a write-heavy system. It means the write path deserves the same scrutiny as any other hot path in this article: measure it, and if it's the bottleneck, that's exactly the place to drop to COPY, hand-batched multi-row INSERTs, or a dedicated bulk-loading library, and keep the ORM everywhere else it's not in the way.

When the abstraction stops being useful

Every ORM's query builder is designed around the common case: filter, sort, join, paginate. The moment your requirement moves past that, you're negotiating with the abstraction instead of using it. In practice, that means:

Window functions (running totals, ranking within groups) — most ORM query builders don't model these at all.
Recursive CTEs (org charts, threaded comments, category trees) — almost always raw SQL, even in projects that otherwise avoid it entirely.
Bulk operations — an ORM's .update() in a loop is N statements; a single UPDATE ... WHERE id = ANY($1) is one. The difference compounds fast on large batches.
ON CONFLICT (upsert) and RETURNING — supported by some ORMs, but often with a narrower API than the SQL clause itself allows, especially bulk upserts (ON CONFLICT DO UPDATE across many rows at once) and the newer OLD/NEW aliases in RETURNING.
SKIP LOCKED for queue-style workloads — the standard pattern for letting multiple workers pull from the same table without blocking on each other's locked rows. Most ORM query builders have no vocabulary for it at all, so it's raw SQL from the start.
Partial and expression indexes — PostgreSQL can use indexes defined with a WHERE clause or an expression, but the generated SQL still needs to satisfy the conditions that make those indexes usable. An ORM won't necessarily make that obvious from the application code.
JSONB queries with GIN indexes — the operators that make these fast (@>, ?, #>>) are Postgres-specific and rarely have first-class ORM support.
Row-level and advisory locking — SELECT ... FOR UPDATE, pg_advisory_lock, and friends are concurrency primitives most ORMs expose thinly or not at all.
Reading EXPLAIN ANALYZE output — no ORM will do this for you. It's the one skill that stays entirely yours no matter which tool you pick.

None of this means avoid the ORM for these cases. It means know, ahead of time, that you'll drop to raw SQL here, and treat that as normal rather than a sign something went wrong.

Is your ORM a black box? It depends which one

So what should you actually use?

Not all ORMs hide the same amount, and the trade-off usually runs opposite to how much boilerplate they remove:

Approach	Abstraction	SQL visibility	Best fit
Prisma	High	Medium	CRUD-heavy product development
TypeORM	High	Medium	Traditional TypeScript applications, decorator-based models
Sequelize	High	Medium	Mature Node.js codebases already built around it
Drizzle	Medium	High	Type-safe apps that still want SQL-shaped queries
`pg` / `node-postgres`	Low	Full	SQL-heavy code and hot paths

There's no winner in that table on purpose. A team shipping CRUD-heavy internal tools benefits enormously from Prisma's or Sequelize's ergonomics and rarely hits the edges above. A team running a write-heavy, high-traffic public API needs to know exactly which SQL is running on every hot path, and might be better served by Drizzle or hand-written queries where it matters most. Plenty of production codebases mix approaches deliberately: an ORM for the CRUD majority, raw SQL for the handful of queries that are actually hot.

The actual takeaway

The ORM isn't the problem. Forgetting that an ORM is generating database operations is the problem. It doesn't remove the need to understand what Postgres is doing, it just moves the moment you're forced to learn it, from day one of the project to the incident where your database is drowning in unnecessary queries because the ORM issued forty small UPDATE statements instead of one bulk operation.

Use the ORM for what it's good at: the 80% of your code that's routine CRUD, type-safe and fast to write. But keep query logging on, read the SQL your ORM generates for anything on a hot path, and read the actual migration SQL before it hits production, not just the schema diff. The ORM is a very good translator. It is still worth knowing enough of the language yourself to catch it when it gets a sentence wrong.

The Dual-Write Problem: Why "Update the DB, Then Publish an Event" Is Broken

Jatin Jain Saraf — Sat, 29 Aug 2026 03:28:24 +0000

An order gets saved to Postgres. The next line of code publishes an "order created" event to a message broker, so the payments service can charge the customer, shipping can start prepping a label, and notifications can send a confirmation email. That's the whole design. It works in every test, in staging, and in production, for months.

Then one day the process gets killed between those two lines. Deploy rolled a pod mid-request. The broker connection timed out for four seconds during a network blip. A GC pause pushed the request past a load balancer timeout and the client retried against a different instance while the first one was still finishing up. The order is sitting in the database, fully committed, real, billable. Nobody downstream ever hears about it. No exception was thrown. No alert fired. The system looks healthy. A customer just placed an order that will never ship.

Why You Can't Just Wrap It in a Transaction

The instinct is to reach for a transaction: begin, insert the order, publish the event, commit. That doesn't work, and it's worth being precise about why, because the reason is architectural, not a missing try/catch.

A database transaction gives you atomicity over one resource: the database. Postgres can guarantee the order row either exists or doesn't, with nothing in between visible to other readers. A message broker (Kafka, RabbitMQ, SQS) is a separate system with its own commit protocol, its own durability guarantees, and no shared transaction coordinator with your database. There's no operation that atomically says "commit this Postgres row and this Kafka message together, or neither." You are making two independent network calls to two independent systems, and between them there is always a window where one has succeeded and the other hasn't.

Move the event publish before the database commit and you get the opposite failure: the event goes out, downstream services start acting on an order that doesn't exist yet, and then the database write fails (constraint violation, connection drop, whatever) and now payments has charged a card for an order that was never actually created. Order doesn't matter. Two independent writes to two independent systems, with no atomicity across the boundary, will eventually diverge no matter which one goes first. This is the dual-write problem, and it's not a bug in your code, it's a gap in what a database transaction can promise you.

Why this matters more than it looks like it should: this isn't a rare edge case you can accept as background noise. Every deploy is a process restart. Every autoscaling event is new pods coming up while old ones drain mid-request. Every network blip between your service and your broker is a window for this to happen. At low traffic it might occur once a month and get written off as "weird, must've been a fluke." At real scale, with thousands of writes a minute, this gap fires constantly, and it fires exactly during the conditions you're least equipped to notice it: deploys and incidents, when your attention is already somewhere else.

Two-Phase Commit: The Theoretically Correct Answer Nobody Uses

The textbook fix for "atomically commit across two systems" is Two-Phase Commit (2PC). A coordinator asks every participant (the database, the broker) to prepare, meaning "lock this resource and confirm you can commit, but don't commit yet." Once every participant says yes, the coordinator tells everyone to commit for real. If any participant says no, the coordinator tells everyone to roll back.

It's a real protocol with a real correctness proof, and it's almost never what production systems actually use for this problem, for reasons that show up the moment you operate it instead of just reading about it:

It's blocking. Once a participant says "yes, I can commit," it has to hold that lock until the coordinator's final decision arrives. If the coordinator crashes after collecting votes but before sending the commit decision, every participant is stuck holding locks indefinitely, waiting for a coordinator that might not come back.
Most message brokers don't implement the participant side of the protocol at all. Kafka has no native 2PC participant role. You'd be building and maintaining that coordination layer yourself, on top of a system that was never designed to expose it.
It doesn't survive network partitions gracefully. The entire protocol assumes the coordinator can eventually reach every participant. A partition during the decision phase leaves things in exactly the indeterminate, locked state the protocol was supposed to prevent.

2PC is the right answer to a narrower question: coordinating multiple databases that all speak a compatible protocol, in a controlled environment where you own the failure modes. It's the wrong tool for "coordinate my database with my message broker," which is the shape of the dual-write problem almost everyone actually hits.

The Outbox Pattern: Make the Second Write Boring

The pattern that actually gets used starts from a reframe: stop trying to make two different systems commit atomically, and instead make the second write something the same database transaction can own.

Instead of publishing to the broker directly, you write the event as a row in an outbox table, in the exact same transaction as the order insert:

BEGIN;
INSERT INTO orders (id, customer_id, total_cents, status)
  VALUES ('ord_123', 'cust_456', 4999, 'created');
INSERT INTO outbox (id, aggregate_id, event_type, payload, created_at)
  VALUES (gen_random_uuid(), 'ord_123', 'order.created', '{"order_id":"ord_123","total_cents":4999}', now());
COMMIT;

Now atomicity is trivial, because both writes are ordinary rows in the database you already have transactional guarantees for. Either both rows exist or neither does. There is no window where the order exists but its event doesn't, because "the event exists" now just means "a row in the same table transaction says so."

A separate relay process, running continuously, polls the outbox table for unpublished rows (or reads Postgres's write-ahead log directly via logical replication, which is how tools like Debezium do it without polling), publishes each one to the real message broker, and marks it published. If the relay crashes, it resumes from the last unpublished row on restart. If the broker is briefly unavailable, the events just sit in the table until it recovers. The order write itself never blocks on the broker being up at all.

The trade-off is honest, not hidden: downstream consumers now see events after a short delay (however often the relay polls, typically sub-second to a few seconds), and they can receive the same event more than once if the relay publishes successfully but crashes before marking the row as sent. That second part is why every consumer of these events has to be idempotent, not because it's good hygiene in the abstract, but because at-least-once delivery is the actual guarantee the outbox gives you, and "exactly once" isn't achievable without it.

Sagas: The Same Problem, Stretched Across Multiple Services

The outbox pattern solves atomicity for one write plus one event. A saga is what you reach for when the operation itself spans multiple services, each with its own local database, and there's no way to wrap the whole thing in a transaction even in principle: reserve inventory, charge the payment, create the shipment. Three services, three databases, one logical operation.

A saga runs this as a sequence of local transactions, each one committing on its own, with a compensating action defined for each step in case a later step fails:

Inventory service reserves the item. Commits locally.
Payment service charges the card. Commits locally.
Shipping service creates the shipment. If this fails...
...run the compensations in reverse: refund the payment, release the inventory reservation.

Nothing here is atomic in the ACID sense. There's a real window, between steps 1 and 3, where inventory is reserved and no shipment exists yet. A saga doesn't hide that window, it makes it explicit and time-bounded, and gives you a defined path back to a consistent state if the last step doesn't complete. That's a fundamentally different consistency model than a database transaction (eventual, with defined compensations, instead of immediate and atomic), and pretending otherwise is where sagas go wrong in practice: teams build the happy path, skip writing the compensating actions because "that won't really happen," and then an incident forces someone to write the refund logic live, under pressure, for a case they never tested.

Each step publishing its "I'm done" or "I failed" signal is itself a dual-write problem at a smaller scale, which is why saga implementations lean on the outbox pattern internally for each step, rather than being a separate mechanism from it. The two patterns compose: outbox solves atomic local write-plus-event, sagas solve the multi-step orchestration built on top of that primitive.

What This Actually Demonstrates

None of this is about picking the "advanced" pattern to look sophisticated. It's about recognizing that "write to the database, then tell everyone else" is not one operation, it's two, and the honest response to that is either to make the second write ride inside the first one's transaction (Outbox) or to make the multi-step version of that gap explicit and recoverable (Sagas) — not to assume the gap won't matter until an incident proves otherwise.

Elasticsearch Isn't Dead. You Probably Don't Need It

Jatin Jain Saraf — Fri, 21 Aug 2026 01:34:27 +0000

Elasticsearch Isn't Dead. You Probably Don't Need It.

Every team hits the same moment. Search gets slow, someone says "we need Elasticsearch," and two weeks later there's a new cluster, a sync pipeline, and a Slack channel called #search-is-down.

Here's the case for not doing that. PostgreSQL's built-in full-text search handles the kind of workload many teams actually have, and it does it without adding a second database to the architecture.

The real cost isn't the cluster, it's the copy

Adding Elasticsearch doesn't add a search feature. It adds a second brain that has to keep agreeing with your first one.

Your data lives in Postgres. Now it also has to live in Elasticsearch, duplicated, reshaped into documents, kept current through a queue or a change-data-capture pipeline or a reindex job someone wrote in a hurry two years ago. Every insert, update, and delete in Postgres needs a matching write on the other side. That sync layer is where the real cost lives, not in cluster fees:

A customer updates their email. Postgres has the new one. Elasticsearch still returns the old one for six hours because the CDC consumer fell behind.
A row gets deleted in Postgres. The reindex job crashed last Tuesday, so it still shows up in search, and support gets a ticket about a ghost customer.
Someone runs a backfill migration, forgets the matching backfill in Elasticsearch, and search results quietly diverge from the database for a month before anyone notices.

None of these are Elasticsearch bugs. They're the tax you pay for keeping two representations of the same data that don't share a transaction boundary. Postgres full-text search skips this entirely, because there's nothing to sync. The searchable representation lives in the same database and is maintained transactionally with the data.

What Postgres actually gives you: tsvector and tsquery

Full-text search in Postgres is built on two pieces: tsvector, which turns text into a normalized, searchable format, and tsquery, which turns a search string into something you can match against it.

SELECT to_tsvector('english', 'PostgreSQL indexes make searches fast');
-- 'fast':5 'index':2 'make':3 'postgresql':1 'search':4

to_tsvector normalizes the text, removes stopwords according to the text-search configuration (here "make" survives, it isn't one), and applies stemming where the configured dictionary supports it. That's why a search for search can match text containing searching, the same behavior that makes Elasticsearch feel necessary in the first place.

SELECT to_tsvector('english', 'PostgreSQL indexes make searches fast')
       @@ to_tsquery('english', 'search & index');
-- true

The @@ operator matches a tsvector against a tsquery. That's full-text matching as a native Postgres operator, not a separate search service. It doesn't score relevance on its own, that's what ts_rank is for below.

For a real search box, to_tsquery is less useful than it looks, because it throws a syntax error on ordinary user input like an unbalanced quote or a trailing &. websearch_to_tsquery is the better default: it accepts web-search-style syntax (quoted phrases, -exclude, or) and never rejects plain text.

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, websearch_to_tsquery('english', $1) query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

Reach for to_tsquery when you control the query syntax yourself. Reach for websearch_to_tsquery when the query comes from a search box.

The part that makes it production-ready: GIN indexes

If you compute the tsvector during every query, Postgres has to process the candidate rows to build those vectors on the fly, fine for a thousand rows, a real cost at ten million. For data that's searched regularly, storing the vector and indexing it with a GIN index (Generalized Inverted Index) avoids that repeated work.

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

That GENERATED ALWAYS AS ... STORED column is the detail that matters in production. Postgres computes it whenever the underlying row is inserted or updated, and the GIN index is maintained as part of the same database transaction, the same way any index is maintained when its underlying column changes. There's no separate consumer to fall behind and no second system that can temporarily disagree with the row.

A GIN index works like the index at the back of a textbook. Instead of scanning every page for the word "vacuum," you jump straight to the pages listed under V. Postgres does the same: instead of scanning every row's tsvector, it jumps straight to the rows containing your search terms.

Worth saying plainly: that index isn't free. It costs write overhead on every insert and update, and it costs disk, same as any index does. The difference isn't zero cost versus some cost, it's an index cost you were always going to pay somewhere versus that same index cost plus a whole second system to keep in sync. One cost, not two.

ts_rank scores matches by relevance, giving you the ranking step you'd otherwise reach for a search engine to provide. The query runs inside Postgres, alongside the rest of your relational data, without a network hop to a separate search cluster.

Filtering is where a bolted-on search engine shows its seams

Most real search features aren't just "find text," they're "find text, then filter by category, status, or whatever's live right now." If Elasticsearch is populated asynchronously from Postgres, that filter is only as current as the index. A row can go inactive in Postgres while the search index still considers it active, for however long the sync pipeline lags. In Postgres, the filter and the full-text predicate run against the same transactional dataset in the same WHERE clause. There's no separate search index that can lag behind the row, because the filter and the full-text predicate operate on the same Postgres data.

That single detail, an asynchronously maintained index lagging the source of truth, is usually the thing that quietly breaks in production long after the Elasticsearch launch party is over.

Where Elasticsearch still earns its keep

This isn't "Elasticsearch is bad." It's "most teams adopted it for a problem they didn't have."

Elasticsearch is worth reaching for when you need fuzzy matching and aggressive autocomplete beyond what pg_trgm comfortably covers, sophisticated relevance tuning, large-scale aggregations and faceting, distributed search across genuinely large datasets, log and observability workloads, or a search workload you deliberately want isolated from your transactional database. Any one of those is a real reason. None of them are "search felt slow once."

And Postgres full-text search isn't a drop-in replacement for all of that. If your product depends on typo tolerance, heavy autocomplete, deep linguistic analysis, or search-specific aggregations at real scale, the tradeoff changes quickly, and that's exactly when Elasticsearch's cost starts paying for itself.

Postgres does cover more of that ground than people expect, though, particularly the typo tolerance part. pg_trgm handles a different class of problem from full-text search: finding strings that are similar even when the spelling isn't exact, which is what powers a lot of "did you mean" and fuzzy-match behavior.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX articles_title_trgm_idx
  ON articles USING GIN (title gin_trgm_ops);

SELECT title
FROM articles
WHERE title % 'elastcsearch'
ORDER BY similarity(title, 'elastcsearch') DESC
LIMIT 10;

Full-text search matches words and lexemes. pg_trgm matches approximate string similarity. Elasticsearch earns its place when you need substantially more search infrastructure than either of those provides.

The actual decision

This was never really "Postgres vs. Elasticsearch." It's "don't add a second system until you've established that Postgres isn't enough."

Start with Postgres. Add full-text search. Add a GIN index. Add pg_trgm if you need fuzzy matching. Measure. Introduce Elasticsearch when the workload actually demands it, not when search first feels slow.

For many teams, that measurement never gets there. The search bar was never the hard part. Keeping two systems telling the same story was.

Why Your Docker Image Is 3GB and Nobody Noticed Until the Deploy Timed Out

Jatin Jain Saraf — Wed, 19 Aug 2026 17:41:42 +0000

Why Your Docker Image Is 3GB and Nobody Noticed Until the Deploy Timed Out

A service that used to deploy in ninety seconds now takes six minutes. Nobody changed the code that day. The base image hasn't changed, docker push is uploading to the same registry it always has, and the CI runner is the same size it always has been. What changed is that the image itself quietly grew, build after build, revision after revision, until one day the push step timed out and someone finally looked. Nothing broke on purpose. The image just never stopped getting heavier, and nobody was watching the number.

An Image Is a Stack of Layers, Not a Snapshot

A Docker image isn't one file, it's a stack of read-only layers, one per instruction in the Dockerfile that changes the filesystem: a RUN, a COPY, an ADD. Each layer is a diff against the one below it, and at runtime the container's filesystem is presented as the combination of all those layers through a union filesystem, with a thin writable layer on top for the container itself. The layers stay separate on disk; nothing flattens them into one file. Crucially, a layer never shrinks once written. If a RUN apt-get install layer downloads 400MB of packages and a later RUN apt-get clean layer deletes the package cache, the image doesn't get 400MB smaller. The delete happens in a new layer on top; the old layer with the 400MB still sits underneath it, and that layer remains part of the image that nodes need to have available. The deleted file disappears from the filesystem you actually see when the container runs, but the bytes are still physically present in the image and still contribute to its size. If a registry or node doesn't already have that layer, those bytes have to be transferred during a push or pull.

Think of it like a suitcase you never fully unpack between trips. You don't take everything out and repack from scratch, you just add what you need for this trip on top of what's already in there. Take something out and it's not gone, it's still in the suitcase, just buried under a note that says "ignore this." The suitcase only gets heavier. That's a Docker image with unpruned layers: every stale cache, every intermediate build tool, every "temporary" file that got rm'd in a later step is still physically present, just marked as superseded.

Why this shows up in production: a base image with a full OS toolchain (Ubuntu with build-essential, a full Node.js image instead of a smaller variant) starts you 500MB to 1GB heavier before your application code exists at all. Every dependency installed and not cleaned up in the same layer, every COPY . . that grabs node_modules, .git, and test fixtures because there's no .dockerignore, adds weight that never comes back off. None of it fails a build. It just makes every image bigger than the last one, silently, until push and pull times are the bottleneck. A rough breakdown of how an image gets to 3GB:

Base image                900 MB
Build dependencies        700 MB   (compilers, headers, dev packages)
node_modules               500 MB
Source and test files      200 MB
Deleted-but-retained data  700 MB  (old caches, files removed in a later layer)
------------------------------------
Total                     ~3.0 GB

No single layer looks unreasonable on its own. It's the accumulation across all of them, plus the ones that should have been discarded but structurally can't be, that adds up.

Why Bad Layer Ordering Makes Every Build Slower

Layer caching is supposed to make builds fast: Docker walks the Dockerfile instruction by instruction and reuses the cached result for each one, until it hits the first instruction whose cache is invalid, either because the instruction itself changed or because a file it depends on changed. From that instruction onward, every remaining layer has to be rebuilt, even if most of them don't actually depend on what changed. That's the entire point of the layer model, and it only works if the Dockerfile is ordered so that the things which change least often come first, and the things that change on every commit come last.

The single most common mistake is COPY . . before the dependency-install step:

# Bad: any file change invalidates the install layer
COPY . .
RUN npm install

Every commit touches some file in the repo, so that COPY layer's cache is invalidated on every build, and npm install right after it has to rerun from a cold cache every time, even when package.json didn't change. Reordering it fixes the problem directly:

# Better: only a change to package*.json invalidates the install layer
COPY package*.json ./
RUN npm ci
COPY . .

Now changing src/foo.js invalidates only the final COPY, and the dependency-install layer above it stays cached. A build that might otherwise take fifteen seconds because nothing dependency-related moved instead can take three minutes on the bad version, on every revision, forever, because the Dockerfile put the wrong instruction first.

Why this shows up in production: this is the mechanism behind "deploys used to be fast and now they're not," with no code change to point at. It's not the application getting slower, it's the build losing its cache on every run because of layer ordering, on top of an image that's also grown heavier over time, so both the build step and the push/pull step degrade independently and get blamed on each other.

The Registry Doesn't Forget Either

The same "nothing shrinks unless someone tells it to" problem exists one level up, in the artifact registry itself. Every build that pushes a new tag, latest, a commit SHA, a version number, adds a new manifest and a new set of layer blobs to the registry. Layers that are byte-identical get deduplicated by content hash, which is why the registry doesn't grow linearly with every push, but every layer that's actually different (a new dependency version, a new base image patch, a rebuilt application layer) is new data that has to be stored, and old images don't get deleted just because a newer one exists.

A registry doesn't necessarily know which images are no longer useful. Unless you give it retention rules or clean them up yourself, old manifests and their unique layer blobs just accumulate: every feature-branch build, every hotfix, every image built by a CI run that never got cleaned up, all still sitting there, still counted against storage. A registry with a two-year-old image nobody has pulled in eighteen months isn't just wasted storage, it's an image nobody is auditing for CVEs anymore, sitting right next to the one that's actually in production, indistinguishable to anyone browsing tags without a naming convention.

Why this shows up in production: registry storage bills climbing with no obvious cause, and CI jobs or registry maintenance operations getting slower or more expensive as a repository accumulates large numbers of manifests and unique layer blobs. A single docker pull for a specific tag isn't slowed down by unrelated stale tags sitting elsewhere in the repository, it only fetches the layers that tag's manifest actually references, but repository-wide operations, listing tags, running garbage collection, scanning for vulnerabilities across everything stored, all get heavier as the pile grows. The fix isn't a bigger registry plan, it's a retention policy: expire untagged manifests and feature-branch tags after N days, keep only the last M builds per branch, and let content-addressable deduplication do the rest.

Measure Before You Guess

Before reordering a Dockerfile or swapping a base image, find out what's actually taking up space. Two commands answer that directly:

docker image history myapp:latest

Lists every layer in the image with its size, in order, so you can see exactly which instruction added the most weight.

docker image inspect myapp:latest

Gives the full layer manifest and metadata, useful for scripting size checks in CI. For a closer look at what's actually sitting inside the largest layers, a tool like dive walks the filesystem contents layer by layer, which is usually faster than guessing from the Dockerfile alone. Run docker image history first: it takes thirty seconds and tells you whether the problem is the base image, an unpruned dependency cache, or the build context, before you change anything.

What Actually Fixes It

Order the Dockerfile by change frequency. Dependency manifests (package.json, requirements.txt) get copied and installed first, source code gets copied last. That one reordering is usually the single biggest build-time win available.
Use multi-stage builds. Build in one stage with the full toolchain, compilers, dev dependencies, and copy only the compiled output into a clean final stage. The build tools never make it into the image that ships. A simplified Node example, for a service that produces a self-contained dist directory:

FROM node:22 AS build
WORKDIR /app
COPY package*.json ./
RUN npm ci
COPY . .
RUN npm run build

FROM node:22-slim
WORKDIR /app
COPY --from=build /app/dist ./dist
COPY --from=build /app/package*.json ./
RUN npm ci --omit=dev
CMD ["node", "dist/index.js"]

The final image only contains what COPY --from=build explicitly pulls forward: the compiled output and production dependencies. The compiler, dev dependencies, and source files from the build stage never exist in the shipped image at all.

Pick a smaller runtime image where it makes sense. -slim, distroless, or Alpine variants are often the difference between a 900MB image and a 90MB one before any application code is added, but they're not a free win in every case: Alpine's musl libc can surface compatibility issues with some native dependencies, so weigh the size/security gain against your actual runtime's tolerance for it rather than defaulting to it blindly.
Write a real .dockerignore. node_modules, .git, test fixtures, and local env files should never be in the build context in the first place. This keeps the image smaller directly, and it also keeps irrelevant files from ever reaching a COPY layer and invalidating its cache for no reason.
Set a registry retention policy. Expire untagged and stale feature-branch images automatically instead of relying on someone remembering to clean up. Most registries (ECR, Artifact Registry, GitHub Container Registry, Harbor) support this natively.

None of these are exotic. They're three separate mechanisms with the same operational lesson: unused data doesn't disappear unless you design for it or clean it up on purpose. A service that deploys in six minutes instead of ninety seconds usually isn't a mystery. It's a suitcase that's been repacked on top of itself for a year, and nobody's taken anything out.

Why Postgres and Cassandra Made Opposite Bets on Storage Engines

Jatin Jain Saraf — Sat, 15 Aug 2026 09:54:24 +0000

Why Postgres and Cassandra Made Opposite Bets on Storage Engines

A write-heavy ingestion table starts slow on Postgres. Not immediately, but a few weeks in: inserts that used to take a millisecond now take ten, autovacuum is perpetually behind, and EXPLAIN ANALYZE shows time going into index maintenance nobody remembers configuring. The instinct is to blame the schema, or the hardware, or "Postgres doesn't scale." None of those are quite right. The real answer is a decision Postgres made in the 1990s, long before this table existed: it keeps its indexes as B-Trees, and every one of them has to stay sorted, on every write.

Every Write Has to Land Somewhere

Strip a database down to its storage engine and the job is always the same: take a write, put it on disk in a shape that makes future reads fast, and don't lose it. There are two dominant answers to how to do that, and most databases you've heard of lean on one.

B-Trees keep some part of the data sorted on disk, in place, at all times. MySQL's InnoDB and SQLite go all the way: the table itself is a B-Tree, keyed by its primary key or rowid, so the table and its main index are the same structure (a "clustered index"). Postgres is more layered: the table (the heap) is an unordered file with no sort order of its own, rows just go wherever the free space map says there's room, but every index on that table, including the primary key, is a separate B-Tree that has to stay sorted and has to be updated on every write that touches an indexed column.

LSM-Trees, log-structured merge trees (Cassandra, RocksDB, LevelDB, and CockroachDB's Pebble storage engine), take the opposite approach everywhere: a write never touches its final sorted position immediately. It gets appended to whatever's currently open, and getting everything back into sorted order is a job for later, done in the background, in bulk.

That fork, sorted-in-place versus sorted-later, explains almost every practical difference in how these two families of databases behave under load.

B-Trees: Pay at Write Time, Save at Read Time

Think of each B-Tree index as a filing cabinet that's always perfectly alphabetized. Every entry goes directly into its correct folder, in its correct position, the moment it arrives. Finding anything later is fast and predictable, you walk straight to the folder. But filing it correctly in the first place means locating the right spot, possibly shifting other entries out of the way, and writing to a specific place on disk rather than just the next free spot.

Here's what a Postgres insert actually does. The row itself is appended to the heap, close to a sequential write, wherever the free space map finds room. But every index on that table, the primary key, any unique constraint, any column you've indexed for lookups, is a B-Tree, and each one needs a new entry written to its correct leaf page, wherever that page happens to sit on disk. Two indexes on a table means one heap append plus two separate random writes, every single insert.

Updates are the more expensive case, and this is where MVCC changes the arithmetic. Postgres never overwrites a row in place: an UPDATE marks the old row version dead and writes an entirely new version elsewhere in the heap. That means an UPDATE costs everything an INSERT costs, a new heap entry, plus a new entry in every index, plus it leaves a dead tuple behind that autovacuum eventually has to clean up.

Why this shows up in production: early in a table's life, its hot pages and index pages fit comfortably in shared_buffers, so those "random" writes are really just writes to RAM, flushed to disk lazily and cheaply. Once the table and its indexes outgrow memory, every insert or update has a real chance of touching an index page that isn't cached, turning a cheap write into a disk seek, on every indexed column, on every write. Add autovacuum needing to revisit the dead tuples MVCC leaves scattered across heap pages, and you get the exact symptom this post opened with: a table that used to be fast getting steadily slower with no code change, just growth, and just more indexes to maintain per write.

LSM-Trees: Write First, Sort Later

An LSM-Tree handles the same problem by refusing to do that work up front. Picture an inbox tray instead of a filing cabinet: every new entry just gets dropped on top of the pile. Filing is instant, because there's no filing, you're not finding anything, you're just adding to a stack. Periodically, someone takes the whole pile, sorts it properly, and merges it into the already-sorted sections below it.

Mechanically: writes go into an in-memory structure (a memtable) and are also logged to a commit log for durability, both sequential operations. When the memtable fills up, it's flushed to disk as an immutable sorted file (an SSTable). Over time you accumulate many of these sorted files, and a background process called compaction merges them together, discarding values that have been overwritten or deleted along the way.

Deletes are handled the same indirect way updates are avoided: a delete doesn't remove anything immediately, it writes a tombstone, a marker saying "this key is deleted as of this timestamp." The actual old data only disappears once compaction physically rewrites the SSTables that contained it.

Why this shows up in production: writes are cheap and consistently fast, because they're always sequential appends, regardless of how big the dataset gets or where the "right place" for the data would eventually be. That's why Cassandra-style databases are the default reach for write-heavy workloads: time-series ingestion, event logs, metrics pipelines. The cost is deferred, not eliminated. A read might have to check the memtable and several SSTables before it can be sure it has the latest version of a key, that's read amplification, and it's the mirror image of the index-maintenance cost a B-Tree pays on every write. Compaction itself is a real, ongoing background cost too: it consumes disk I/O and CPU that has to be budgeted for, and if compaction ever falls behind sustained write load, both read latency and disk usage climb, in a way that looks a lot like Postgres autovacuum falling behind, just triggered by the opposite kind of pressure.

"Just Switch to Cassandra" Isn't the Fix It Sounds Like

It's tempting to read the above and conclude LSM-Trees are strictly better for anything write-heavy. They're not, they're differently shaped, with their own failure modes:

Read amplification is real. A point lookup that's a single B-Tree traversal in Postgres might mean checking a memtable plus several SSTables in an LSM-Tree, unless bloom filters and careful compaction keep that bounded.
Compaction is not free. It's extra write I/O (rewriting data that was already written once), and a compaction strategy mismatched to the workload (size-tiered vs. leveled) can make total write cost in an LSM-Tree worse than a B-Tree's, not better.
Tombstones linger. Until compaction actually removes the old versions and their tombstones, deleted data still occupies space and still gets scanned past on reads, and in Cassandra specifically, an excess of tombstones scanned in a single read can trip query-side warnings or failures.
Transactional guarantees differ. Postgres gives you multi-row ACID transactions and foreign keys as first-class features because the B-Tree indexes, MVCC, and WAL were co-designed for exactly that. Most LSM-based systems trade some of that away for horizontal write scale, which is a real cost if your workload actually needs cross-row consistency, not just fast ingestion.

Migrating a workload from Postgres to Cassandra to solve a write-scaling problem, without checking whether the workload also needs the guarantees Postgres was giving up in the trade, is how you end up rebuilding transactional consistency by hand in application code, which is a worse position than the one you started in.

When Each Model Actually Wins

The honest framework isn't "which database is better," it's "which cost am I willing to pay":

Choose a B-Tree-indexed engine (stay on Postgres, tune it) when reads dominate, or when transactional consistency and relational integrity across tables matter more than raw write throughput. Most application backends, most OLTP workloads, most systems where "this row is correct right now" matters, belong here.
Choose an LSM-Tree engine when the workload is write-dominated and append-heavy by nature, event streams, time-series metrics, logs, and reads can tolerate either eventual consistency or a slightly higher read cost in exchange for writes that don't degrade as the dataset grows.
Or, more often than either extreme: the write-heavy table causing the pain doesn't need to be in Postgres at all. Partitioning it, moving it to a purpose-built time-series or log store, or reducing the number of indexes it maintains, frequently solves the actual problem without touching the primary transactional database's engine at all.

Postgres isn't losing to Cassandra when a table gets slow under heavy writes. It's honoring the trade it made on day one: keep every index sorted and correct on every write, so reads stay cheap and consistent forever. Whether that's still the right trade for a specific table, and how many indexes it really needs, is a workload question, not a verdict on the database.

The Codebase You Sit In Rubs Off On You

Jatin Jain Saraf — Tue, 11 Aug 2026 17:55:26 +0000

The Codebase You Sit In Rubs Off On You

Spend all day at a fish market and you'll leave smelling like fish, even if you never touched one.

Spend your day in a codebase full of shortcuts, and you'll start writing shortcuts too, even if nobody told you to and even if you swore you never would.

That's how environments work. They rub off on you, whether you notice it or not. And in engineering, this isn't a metaphor it shows up in your git log.

The lie of "it's all about discipline"

Most engineers think their habits come from training, courses, or sheer effort. If your code is sloppy, the assumption is you didn't try hard enough. If it's clean, you must have "good discipline."

That's only half true. A huge part of how you code comes from what surrounds you every day, on repeat, long before you're conscious of it:

The PRs you review, and the ones that get rubber-stamped instead of actually reviewed.
The Slack threads where someone asks "does this really need a test?" and the answer is a shrug.
The postmortems you sit through, the ones that hunt for a person to blame versus the ones that hunt for a broken process.
The teammate whose code you copy-paste from at 2am because it's the closest example you can find, bugs and all.

None of this announces itself as a lesson. Nobody sends a Slack message saying "today I am teaching you to skip error handling." It just seeps in through repetition, the same way an accent seeps in from the people you grow up around.

Where this actually shows up in production

This isn't abstract. It shows up as specific, recognizable symptoms:

Silent try/catch blocks everywhere. Not because anyone taught "swallow your errors," but because the first three examples of error handling you saw in that codebase did exactly that, and you pattern-matched off them without questioning it.

A team where nobody writes tests for "obvious" changes. Six months later, someone ships an "obvious" one-liner that takes down checkout for twenty minutes. The postmortem calls it human error. It was actually a cultural default nobody chose on purpose, it was absorbed.

Incident channels full of finger-pointing instead of timelines. New hires learn within a week that admitting a mistake early gets you blamed, so they learn to sit on problems until they're unavoidable. That delay is the expensive part, not the original mistake.

Commit messages that are just "fix," "update," "wip." You'll find the one senior engineer on the team who started that pattern years ago, still committing "fix" today, and everyone quietly inherited it.

Compare that to a team where the senior engineers write commit messages that explain why, not just what; where someone flags an edge case out loud in standup instead of letting it slide; where an incident review ends with "here's the missing guardrail" instead of "here's who missed it." Sit in that environment for six months and you'll start doing the same things, not because you decided to, but because that's what normal looks like now.

Why "just work harder" doesn't fix this

If your habits were purely a function of effort, then two equally hardworking engineers in different environments should converge on similar code quality. They don't. Drop the same disciplined engineer into a team that treats testing as optional, and within a year their testing habits will have eroded, not because they got lazy, but because the environment stopped rewarding the behavior and stopped modelling it.

This is also why "hire good people and leave them alone" doesn't scale culture. Good habits decay under a bad environment faster than bad habits improve under a good one is slow. Osmosis runs both directions, and it runs constantly, not just during onboarding.

Changing what you're surrounded by is the actual lever

This is why the fastest way to level up often isn't another course or another book, it's deliberately changing what you're marinating in every day:

Review code from engineers better than you, even when it's not required. You're not looking for bugs; you're absorbing their defaults.
Sit in on incident calls outside your own team. Watching how a genuinely good incident is run, calm, timestamped, blameless, recalibrates what "normal" looks like faster than any postmortem template.
Follow people who write and talk publicly about the specific problems you want to get better at. Your feed is an environment too. It rubs off exactly like your team does.
Choose the side projects and open-source codebases you spend your evenings in as carefully as the one you're paid to work in. If every codebase you touch after 6pm is a mess, don't be surprised when "mess" starts to feel acceptable at 10am.

Your first team teaches you more about "how to engineer" than any style guide, wiki page, or onboarding doc ever will, whether that team is excellent or quietly broken. And it keeps teaching you, every day you stay in it, long after onboarding ends.

The real question

The scent of your environment always follows you into your next commit.

So the question worth asking isn't "am I working hard enough?"

It's "what am I letting rub off on me, and is it actually who I want to become as an engineer?"

PostgreSQL's Async I/O Engine: Why Your Sequential Scans Just Got 2-3x Faster

Jatin Jain Saraf — Sat, 08 Aug 2026 16:23:05 +0000

For as long as PostgreSQL has existed, reading a page from disk has looked the same: a backend process calls read(), the OS schedules the I/O, and the process blocks until the page comes back. Then it asks for the next one. One request, one wait, repeat.

That's fine when your data is sitting in memory. It becomes a real problem on modern NVMe storage. An NVMe drive can have 32 or more I/O requests in flight at once, happily serving them in parallel. Postgres, until version 18, never took advantage of that. It asked for one page, waited for it to come back, then asked for the next. The hardware was capable of a highway; Postgres was driving it one car at a time.

PostgreSQL 18 changes that with a real asynchronous I/O subsystem. Here's the mental model worth keeping: synchronous I/O is a single waiter who takes one table's order, walks it to the kitchen, stands there until it's ready, and only then goes to take the next order. Async I/O is that same waiter dropping ten orders at the kitchen window at once and picking up whichever plates are ready as they come up. Same kitchen, same waiter, dramatically less standing around.

What actually changed

Postgres now has a pluggable I/O backend, controlled by a new io_method setting:

io_method = io_uring   # Linux 5.1+, lowest overhead
io_method = worker      # cross-platform fallback (macOS, Windows, older Linux)
io_method = sync        # pre-PG18 behavior

io_uring is the interesting one. It's a Linux kernel interface that gives Postgres two ring buffers shared with the kernel: one to submit I/O requests, one to collect completions. Submitting a batch of reads costs a single syscall instead of one syscall per page. On worker, a pool of background threads makes the blocking calls on your process's behalf so your backend never has to sit and wait itself.

The parameter that actually controls the payoff for read-heavy workloads is effective_io_concurrency. Before PG18 this only affected bitmap heap scan prefetching. Now it controls how many pages ahead a sequential scan, a VACUUM, or a checkpoint will request before it actually needs them:

effective_io_concurrency = 16    # SSD: 16-64
effective_io_concurrency = 200   # NVMe: 64-256
effective_io_concurrency = 2      # HDD: seeks dominate, async barely helps

Why this matters in production

The scenarios where this pays off are the ones that were always I/O-bound: a nightly ETL job doing a full sequential scan of a fact table, VACUUM chewing through a heavily-updated table whose pages aren't in shared_buffers, a checkpoint flushing a large batch of dirty pages, a replication standby trying to flush WAL fast enough to keep lag down.

On paper, a sequential scan of a 10GB table doing 1.3 million page reads at roughly 50 microseconds each costs about 65 seconds fully synchronous. With requests batched 32-deep, that drops toward single-digit seconds in theory. Real workloads don't hit the theoretical ceiling (there's coordination overhead, and the OS page cache intervenes), but a 2-3x throughput improvement on genuinely I/O-bound scans is a realistic, repeatable result, not a marketing number.

The part that matters just as much: if your working set already fits in shared_buffers or the OS page cache, async I/O buys you almost nothing. A cache hit has no I/O latency to overlap in the first place. This isn't a "just turn it on and everything gets faster" feature. It's a fix for exactly one bottleneck: waiting on disk.

Verifying it on your own workload

PostgreSQL 18 also ships pg_stat_io, the first built-in view that breaks down I/O by process type and operation. This is how you check whether async I/O is actually doing anything for you, instead of taking it on faith:

SELECT pg_stat_reset_shared('io');

-- run your actual workload here

SELECT
  backend_type,
  reads,
  read_time,
  ROUND(reads::numeric / NULLIF(read_time, 0) * 1000, 0) AS reads_per_second
FROM pg_stat_io
WHERE reads > 1000
ORDER BY reads DESC;

Run that once with effective_io_concurrency = 1 and once with it set to something like 64, on the same cold query, and compare read_time. If the number barely moves, your data was already cached and async I/O was never going to help. If it drops meaningfully, that's your workload confirming it was genuinely I/O-bound and the new prefetching is doing real work.

What it doesn't change

It's worth being precise about the boundaries. Async I/O doesn't touch MVCC, doesn't change the durability guarantees around WAL flushing, doesn't change lock acquisition, and doesn't reduce the per-connection process overhead that still makes connection pooling mandatory at scale. It's a storage-layer optimization, full stop. Everything above the buffer manager works exactly as it did before.

PostgreSQL isn't getting a new execution model here. It's getting permission to stop waiting in line.

Vector Search in Postgres: What pgvector's Indexes Actually Cost You

Jatin Jain Saraf — Wed, 05 Aug 2026 18:23:39 +0000

First, What Are We Even Searching?

Let's ground this in one real example and stick with it the whole way through: you're building a "find similar support tickets" feature. A customer submits a new ticket, and you want to show them five old tickets that are about the same problem, even if they used totally different words to describe it.

To do that, you can't search for matching keywords, because "my payment failed" and "checkout won't go through" mean the same thing but share zero words. So instead, every ticket gets converted into a list of a few hundred (or a few thousand) numbers, called an embedding, using an AI model. Two tickets about similar problems end up with number-lists that are mathematically close to each other. Two tickets about unrelated problems end up far apart.

That list of numbers is what Postgres calls a vector. "Vector search" just means: given one ticket's list of numbers, find the other tickets whose lists of numbers are closest to it. pgvector is the Postgres extension that lets you store these number-lists in a normal table column and search through them with plain SQL.

The Part Every Tutorial Skips

Every "add AI to your app" tutorial has the same steps: install pgvector, add a vector column, run one search query, done. And for a demo with a thousand tickets, that's genuinely all it takes. It just works.

Here's the part tutorials skip: once your support-ticket table grows to a few hundred thousand rows, that same search query, which used to take 8 milliseconds, now takes 900 milliseconds. Someone on your team says "just add an index," like that fixes it the way adding an index fixes a normal slow query. It doesn't, not in the same simple way. Picking the right kind of index means giving up a small amount of accuracy in exchange for speed, and how much accuracy you give up is the whole subject of this post.

Why a Normal Index Doesn't Work Here

A normal Postgres index (a B-tree) is built for questions like "give me every ticket where status = 'open'." That's an exact match: a row either satisfies it or it doesn't.

A vector search question is different in kind: "of these 2 million tickets, which five have number-lists closest to this new ticket's number-list?" There's no exact match to look up. Every single row has to have its distance calculated and compared.

Postgres can do this the honest way: calculate the distance from your new ticket to every other ticket in the table, one by one, then sort and keep the closest five. This is called an exact search, and it's exactly what your thousand-row demo was quietly doing. At a thousand rows, checking every single one is instant, so you never noticed.

Why this matters in production: the time this takes grows in a straight line with the number of rows. Double your tickets, double the search time. Forever. There's no clever Postgres trick, no ANALYZE, no EXPLAIN setting that fixes this, because the problem isn't that Postgres is being inefficient. It's that checking every row really is the only way to get a guaranteed-correct answer. The only way to get faster is to stop insisting on a guaranteed-correct answer, and accept "almost certainly correct" instead.

Two Ways to Cheat (on Purpose): IVFFlat and HNSW

pgvector gives you two index types that both make this trade deliberately: they return an answer that's usually the true closest five tickets, but occasionally misses one, in exchange for being dramatically faster. This is called approximate nearest neighbor search, or ANN for short. Think of it like asking a knowledgeable local for the nearest coffee shop instead of checking every coffee shop in the city yourself: almost always right, occasionally not the actual single closest one, but you get an answer in two seconds instead of an hour.

The two options, IVFFlat and HNSW, cheat in different ways, and picking between them is the actual skill here.

IVFFlat: Sort Tickets Into Labeled Bins First

Picture a post office that pre-sorts mail into bins by zip code before a carrier ever looks at it. IVFFlat does the same thing to your tickets, before any search happens:

When you build the index, Postgres groups all your existing tickets into a fixed number of bins (you choose how many, say 100), based on which tickets' number-lists are similar to each other. Each bin gets a "center point" that represents everything in it, the same way a zip code represents a neighborhood.
When a new ticket comes in and you search, Postgres doesn't check all 100 bins. It only checks a handful of the bins whose center point is closest to your new ticket (you choose how many to check, say 10).
Inside just those 10 bins, it does the slow, exact, check-every-row search, which is now fast because there are far fewer rows to check.

-- build the index: sort existing tickets into 100 bins
CREATE INDEX ON support_tickets
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- when searching: only check the 10 closest bins
SET ivfflat.probes = 10;
SELECT id FROM support_tickets
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 5;

The catch nobody mentions in the quickstart: those bins are decided once, on the day you build the index, based on the tickets that existed that day. Six months later, your product has a whole new feature, and a wave of brand-new kinds of tickets start coming in that don't look like anything in your original bins. Postgres still has to put them somewhere, so it shoves each new ticket into whichever old bin is the "least bad fit," even if that bin isn't really a good match. Slowly, quietly, your search results get worse, and there's no error message telling you this is happening. The fix is to periodically rebuild the index (REINDEX) so the bins get redrawn using current data, which on a big table is a real maintenance job, not a background setting you flip once.

HNSW: Build a Map With Highways and Side Streets

HNSW works completely differently: instead of bins, it builds a connected map between all your tickets, like a road network, with some very long "highways" connecting far-apart regions and lots of short local roads connecting nearby tickets.

Think about how you'd actually travel from a small town to another small town on the far side of the country. You wouldn't drive the whole way on back roads. You'd take a short local road to a highway, drive the highway most of the way, then take local roads again at the other end. HNSW searches the exact same way: start on the "highway" layer where a few big jumps get you into the right general area fast, then drop down onto smaller, denser layers to fine-tune and land on the actual closest tickets.

-- build the index: construct the layered map, m = how many roads each point connects to
CREATE INDEX ON support_tickets
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- when searching: how wide an area to explore before settling on an answer
SET hnsw.ef_search = 40;

m is roughly "how many roads does each ticket connect to." More roads means better odds of finding the true closest tickets, but the map itself takes up more memory. ef_search is roughly "how much of the map do you explore before giving up and answering." Explore more, and you get a more accurate answer, but it takes longer.

Why this matters in production: because HNSW never sorts tickets into fixed bins, it doesn't go stale the way IVFFlat does. New kinds of tickets just get woven into the existing map naturally. But that map has real costs. Every single ticket stores a list of which other tickets it's "connected to" by road, and that list has to live somewhere, so memory use grows with how many roads each point has (m) and how many tickets you have, not just with how big your rows are. A table that used to fit comfortably in memory as plain rows can suddenly need a lot more RAM once this road-map is layered on top. Also, building this map isn't a quick one-time calculation like a normal index: Postgres has to insert each ticket into the map one at a time, figuring out its connections as it goes. On a table with tens of millions of tickets, that build can take hours, and depending on your Postgres version it can hold a lock that blocks other people from writing to that table the whole time. If tickets are constantly being added, that's a real scheduling problem you need to plan around, not something to discover mid-build.

Putting Both Side by Side

	Exact search (no index)	IVFFlat (labeled bins)	HNSW (road map)
Always finds the true closest matches?	Yes	No, usually close	No, usually close
Gets slower as the table grows?	Yes, in a straight line	Yes, but much more slowly	Yes, but even more slowly
Handles brand-new kinds of data well?	Doesn't matter, always exact	Poorly, needs periodic rebuilding	Well, no fixed bins to outgrow
Cost to build the index	None	Cheap, one quick grouping pass	Expensive, inserts one ticket at a time
Extra memory needed	None	A little, just the bin centers	More, scales with how many "roads" each ticket has
Good for tables with constant new writes?	Fine, just slow	Accuracy quietly drifts over time	Handles it well, but writes slow down as the map grows

Neither one is simply "the better index." If your ticket table is built once and doesn't change character much over time, like a fixed product catalog, IVFFlat's cheap build and simple bins are the sensible default. If your table keeps growing and keeps having new, different kinds of data written to it all the time, like a live support-ticket stream, HNSW's steadier accuracy is usually worth its slower, more expensive build.

The One Question That Actually Matters

Before shipping either index, the important question isn't "which one is faster," because both are fast enough for almost any real app. The real question is: how often is it okay for this search to miss the actual best match, and have you ever checked?

That "miss rate" is called recall: the percentage of the true best matches your approximate index actually returns. It doesn't show up anywhere. It won't throw an error, and EXPLAIN ANALYZE won't flag it. The only way anyone finds out recall has gotten bad is a vague complaint months later, something like "the suggested tickets don't feel as relevant as they used to," long after the table has grown large enough for the approximation to start visibly slipping.

The fix is simple to say and easy to skip: while your table is still small enough to run a true exact search for comparison, run one, and compare its results against what your approximate index returns. That tells you your real recall number. Do this early, because once the table is big enough that an exact search is too slow to run anymore, you've lost your only way of checking whether the fast index is still giving you good answers.

The Three Layers of Failure Isolation: Timeouts, Circuit Breakers, and Load Shedding

Jatin Jain Saraf — Mon, 03 Aug 2026 17:53:46 +0000

500 requests a second are hitting a dependency that is completely dead. Every single one gets a clean timeout after 2 seconds, exactly as configured. And you are still down.

That sentence is the whole problem with treating resilience as a single setting. A timeout did its job, nothing hung forever, and the aggregate is still an outage, because you're holding 1,000 concurrent doomed requests at once, and every one of them is load on a dependency that's trying to restart.

There isn't one pattern that fixes this. There are three, stacked, each one picking up exactly where the last one's guarantee runs out. Get the order wrong, or skip one, and you don't get partial protection, you get a different failure mode wearing the same symptoms.

This is that stack: timeouts and retries, circuit breakers and bulkheads, backpressure and load shedding. What each one actually bounds, why the one before it isn't enough, and the specific way each gets misconfigured in a way that looks fine until the day it doesn't.

Layer One: A Timeout Doesn't Wait Patiently, It Fails Together

Most client libraries default to no overall timeout. fetch in Node has no total deadline unless you add one. Plenty of database drivers wait indefinitely. That default isn't neutral, it's a decision to couple your availability to your slowest dependency, and Little's Law explains exactly how much:

concurrency = arrival rate × service time

A dependency's p99 goes from 50ms to 30 seconds. Your arrival rate hasn't changed. Your concurrency just rose by a factor of 600, and every one of those in-flight requests is holding a socket, a pool connection, a request slot, memory. At 200 requests/second and a 30-second service time, you need 6,000 concurrent slots. You don't have 6,000.

downstream slows → your concurrency climbs → pool exhausted → queue builds
                 → YOUR p99 rises for every endpoint, including ones that
                   never call that dependency → your own callers time out
                 → and they retry, adding load → you are now the outage

Nothing failed. A dependency got slow, and the absence of a bound propagated it outward. A timeout is how you convert someone else's latency problem into your error rate, which sounds like a downgrade and isn't, because an error is bounded and recoverable, while unbounded latency spreads.

The queue at a single service desk, where one customer's transaction is taking forty minutes. With no policy, the twenty people behind them wait forty minutes, the next twenty leave, and the shop's throughput collapses over one difficult case. With a policy, "five minutes per customer, then take a ticket and come back", one customer is inconvenienced and the queue keeps moving.

Only one of your four timeouts actually bounds anything

A single number labelled "timeout" is usually not the number you think it is:

Connect bounds the TCP handshake. Catches a host that's down or unroutable.
Time to first byte bounds server thinking time. Catches a slow query or a saturated server.
Idle / socket bounds the gap between bytes. Catches a stream that stalls mid-response.
Total / overall bounds the whole operation, including retries. This is the only one that actually protects your resource usage.

A 2-second connect timeout and a 5-second read timeout don't add up to a guarantee, a response trickling one byte every 4 seconds never trips either one. Set the overall deadline, always, and treat the other three as diagnostics that let you fail earlier with a clearer reason why.

Choose the number from the p99.9 of the successful response distribution, not the average. Too short and you abandon requests that would have succeeded, converting them into retries, a load amplifier disguised as a safety measure. Too long and you hold resources through a failure you could have detected sooner.

And here's the arithmetic that catches nearly everyone:

Your caller's timeout:      5s
Your per-attempt timeout:   3s
Your retry policy:          3 attempts
Your worst-case total:      3 + backoff + 3 + backoff + 3 ≈ 10s

At t=5s your caller already gave up. Attempts 2 and 3 are work
for nobody, executed against a dependency that's already struggling.

A retry policy has to fit inside the overall budget: per-attempt timeout is remaining_budget / max_attempts, not a number picked independently.

Not every failure is safe to retry, and a status code isn't proof

Connection refused, DNS failure, 503, 502, safe to retry, nothing happened yet. A 429, safe, and honour Retry-After. A 500 or a timeout, safe only if the operation is idempotent, because both are the canonical ambiguous failure: the request may have already succeeded server-side and you just never heard back. A 400, 422, 404, never retry; identical bytes fail identically.

HTTP method semantics are a hint, not a guarantee. GET and PUT are specified idempotent, and plenty of real handlers aren't, a GET that increments a view counter, a PUT that appends. Decide from what the operation does and whether it carries an idempotency key, not from the verb.

Backoff needs jitter, and jitter isn't a refinement

Fixed-interval retries fail for a specific reason: a thousand clients that failed at the same moment retry at the same moment.

Fixed 1s:      ████    ████    ████     ← full fleet, in phase, forever

Exponential:   ████      ████        ████    ← spaced out, still in phase

Exp + jitter:  ▁▃▂▁▄▂▃▁▂▄▁▃▂▄▁▂▃▁▄▂▃  ← smeared into a manageable trickle

Exponential backoff fixes the frequency of retries. It does nothing for synchronisation. Without jitter, a recovering dependency gets knocked over by the first aligned burst, which restarts the whole cycle.

// Full jitter: sleep is uniform over [0, capped exponential]
const backoff = (attempt: number) => {
  const exp = Math.min(30_000, 200 * 2 ** attempt);   // cap matters, uncapped, attempt 12 waits 9 hours
  return Math.random() * exp;
};

Cap the exponential, bound the attempt count, and make the first retry near-immediate for a plain connection refusal, that failure costs the dependency nothing to answer again.

Retry amplification: the incident that outlives its own cause

Here's the part that turns a thirty-second blip into a two-hour outage.

Retries multiply through layers. A request path where every layer retries three times:

client ──3×──► gateway ──3×──► API ──3×──► service ──3×──► database
                                                  81 attempts at the bottom
                                                  for ONE user request

Each layer is individually reasonable. Together they're an 81× amplifier, and it engages exactly when the deepest component is failing, because that's what triggered the retries in the first place. A database at a 50% error rate, hit with three times its normal load, doesn't recover. It produces more errors. Which produce more retries. Which produce more errors.

This is a metastable failure state: one that sustains itself after its trigger is gone. A 30-second network blip causes a wave of retries. The retries push load above capacity. Being above capacity produces timeouts, which produce more retries. The network has been fine for an hour, and the system does not recover, because the load keeping it down is the load its own failure generated. Removing the original cause changes nothing. The only way out is reducing load: shedding traffic, opening circuit breakers, or manually pulling the service out of rotation until queues drain.

Two rules follow from this. Retry at one layer, not at every layer, pick the one closest to the business logic, the one that holds the idempotency key, and make every other layer pass failures straight through. If a service mesh retries for you, the application must not also retry, or you've silently rebuilt the multiplier.

And bound it structurally with a retry budget, this is the single most valuable thing in this entire layer, and the mechanism most systems lack:

// Allow retries only up to ~10% of successful request volume.
// Errors can spike without retry load spiking, that's the whole point.
if (retryTokens.tryConsume()) { /* attempt */ }   // token bucket, refilled by SUCCESSES

With a per-request retry count, a 100% error rate produces 3× or 81× load. With a 10% retry budget, that same 100% error rate produces 1.1× load, the system fails fast and cheap, and leaves the dependency enough headroom to actually recover.

Why this matters in production: the trigger for these incidents is almost always mundane, a failover, a deploy, a brief partition. The duration is set entirely by whether the system can shed the load its own retries created. A retry budget and a circuit breaker cost about an afternoon each to build, and they're the difference between a five-minute blip and a two-hour incident.

Cancellation has to actually cancel

A client-side timeout does not stop the server. This is the detail that surprises people the most, and it matters most at the database:

A Node query timeout abandons the response. The Postgres backend keeps executing the query, holding the connection, the snapshot, and its locks.

So a client-side timeout on a slow query gives you the worst of both worlds, the client has moved on and may retry, doubling the work, while the server runs the original query to completion anyway. Enforcement has to happen server-side:

SET statement_timeout = '2s';                       -- the actual bound
SET lock_timeout = '1s';                            -- don't queue behind a lock
SET idle_in_transaction_session_timeout = '10s';    -- kill abandoned transactions

Layer Two: A Breaker Stops Making the Calls At All

Layer one bounds each call. It does not stop you from making a thousand doomed calls a second.

Go back to that dependency that's genuinely down, 2-second timeout, 500 requests/second arriving:

500 req/s × 2s each = 1,000 concurrent in-flight requests
                      ...all of which will fail
                      ...each holding a socket, a slot, and memory
                      ...and all of it is load on a dependency trying to restart

The timeout did its job. Each request failed in bounded time. The aggregate is still an outage, you're spending your entire concurrency budget discovering, five hundred times a second, something you already knew.

A circuit breaker is the observation that after enough failures, you can just stop asking. It converts a 2-second failing call into a sub-millisecond local rejection, and that does three things at once: it frees your resources instantly, it removes load from the dependency, giving it room to actually recover, which is the exit from the metastable state above, and it fails fast enough that a fallback becomes viable. A 2-second wait before serving a cached value is a bad experience. A 1-millisecond rejection followed by a cache read is a fine one.

That second point is easy to undervalue and is often the decisive one. A dependency at 100% error rate does not recover while it's still receiving full traffic plus retries. A breaker is how the traffic actually stops, without a human doing it by hand.

The electrical breaker the pattern is named after doesn't protect the appliance that shorted, that appliance is already broken. It protects the rest of the house, by isolating the one circuit so the wiring doesn't overheat and every other room keeps its lights on. And it needs a delay before reclosing, because closing it immediately onto an unfixed short achieves nothing but another trip.

The four ways a breaker gets misconfigured

Trigger on a failure rate over a rolling window, not consecutive failures. A consecutive-failure counter fails in both directions, on a low-traffic endpoint, five consecutive failures might span twenty minutes and open a breaker for a problem long since resolved, and on a dependency where half of calls succeed, a consecutive counter never reaches five at all. Use a rate with a minimum-volume guard, so a single failure after a quiet period doesn't read as a "100% failure rate over a sample of one":

const stats = window.last(10_000);                 // last 10 seconds
if (stats.total >= 20 && stats.failureRate > 0.5) open();

A 4xx is not a failure. This is the misconfiguration that causes the most damage in practice. A 400, 404, or 422 means the dependency is healthy and rejected your request, bad input, a missing resource, failed validation. Count those as breaker failures and a single client bug, a URL scanner, or one malformed integration takes down a perfectly healthy dependency for every other caller.

Threshold on slow calls too, not just errors. A dependency at 100% success and 8 seconds per call is doing just as much damage as one that's down, arguably more, because nothing about it looks like an error. A slow-call-rate threshold ("if 60% of calls exceed 1 second, open") is the check most implementations skip entirely.

Half-open must admit a trickle, not a flood. A cooldown that expires and lets the full 500 requests/second back through at once re-hammers a recovering dependency and reopens the breaker instantly. Admit one to three concurrent probes, require a few successes before closing, and ideally ramp: half-open at 5% of traffic, then 25%, then closed.

Bulkheads: stop one dependency from starving everything else

A ship's hull is divided into watertight compartments so one breach floods a single compartment, not the vessel. The system equivalent: partition the resource requests compete for, so one dependency's slowness can't consume all of it.

Without: 200 concurrent slots, shared.
  The recommendations service slows to 8s. Requests to it accumulate.
  Within seconds all 200 slots hold recommendation calls.
  Checkout, which never calls recommendations, gets no slot. Total outage.

With:  recommendations capped at 20 concurrent.
  Slot 21 onwards is rejected immediately → the fallback runs.
  Checkout's 180 slots are untouched. Recommendations are degraded. Nothing else is.

In Node there's no thread pool to partition, which leads some people to conclude bulkheads don't apply. They do, the shared resource is event-loop time, the socket pool, and memory held by pending promises. The mechanism is a semaphore that rejects when full, not one that queues without bound:

import pLimit from "p-limit";
const limits = {
  recommendations: pLimit(20),     // optional feature: small allowance
  payments:        pLimit(60),     // critical path: generous
};

async function callRecommendations(userId: string) {
  if (limits.recommendations.pendingCount > 50) throw new BulkheadFull();
  return limits.recommendations(() => fetchRecs(userId));
}

A semaphore that queues indefinitely isn't a bulkhead, it's a delay, and the memory held by that queue is exactly the resource you were trying to protect. Size it from Little's Law: a dependency with a 50ms p99 sustaining 200 requests/second needs 10 concurrent slots, not 200. And the single highest-value bulkhead most teams skip is separate connection pools per workload, web traffic and a reporting job should never draw from the same Postgres pool.

Degradation is design work, and it hides the failure that follows

Breakers and bulkheads decide when to stop calling something. Degradation decides what the user gets instead, and it's the part that can't be configured away, it needs a decision per dependency, often a product decision rather than an engineering one.

The rule that catches the most bugs: the fallback must not depend on what failed.

// BROKEN: the fallback path hits the same overloaded database.
try   { return await cache.get(key); }
catch { return await db.expensiveQuery(); }   // ← 100% of traffic now goes here

Redis going down doesn't just remove the cache, it redirects the entire cache hit rate onto the database. A cache at a 95% hit ratio failing means the database sees 20× its normal read load, and the fallback has just converted a cache outage into a database outage. The correct version serves the degraded path behind a bulkhead and a request-coalescing lock, and sheds the excess rather than manufacturing a second failure.

And here's the trap specific to this layer, worth sitting with: degradation removes failure from your error rate. Once it's working, a failing dependency produces no errors. Requests succeed. Latency might even improve, because a fast fallback replaced a slow call. Your dashboard is flat. Your alerts are quiet.

So a system can serve the generic homepage instead of the personalised one for three weeks, because a breaker opened after a deploy changed a hostname and never closed, and nobody notices, because nothing is red. The first report comes from a product manager asking why engagement dropped. The fix is instrumenting the thing degradation hides: breaker state as a metric with an alert on "open for longer than N minutes," and a degraded-serve rate tracked as its own SLI, right next to your error rate, because it is the error rate you decided not to show users.

Layer Three: When Nothing Is Broken and You're Still Down

Layers one and two both answer the same question: a dependency I call is broken, what do I do? This layer answers a different one entirely.

Everything downstream is healthy. Every query is fast. And 9,000 requests a second are arriving at a service that can serve 6,000.

No breaker opens, because nothing is failing. No bulkhead helps, because no single dependency is being monopolized, the resource is being consumed by legitimate, healthy work. Queues just grow, latency climbs uniformly across everything, and eventually it all times out at once.

The counterintuitive fact underneath this: capacity isn't a ceiling you bump into. It's a knee, after which things get worse, not just full. Throughput is requests completed per second. Goodput is requests completed per second that anybody still wanted, inside the caller's deadline. Past the knee, throughput can look almost flat while goodput falls off a cliff, because the work still being completed is work whose caller gave up on seconds ago. A service can sit at 100% CPU, complete 6,000 requests a second, and deliver a genuinely useful 400, and its throughput graph will look perfectly fine the entire time.

A kitchen that takes every order the floor brings. At 30 covers it's fine. At 90, tickets pile up, and by the time each dish is plated the table has left. The kitchen is working flat out, food is going out at the maximum rate the stoves allow, and nobody is eating it. The fix isn't a bigger ticket rail, it's the maître d' at the door saying "we're full, forty-five minute wait." That refusal is the only intervention that gets the guests who are seated actually fed.

An unbounded queue is not a buffer

"Add a queue so we can absorb the spike" is correct advice for a transient burst and catastrophic advice for sustained overload. The arithmetic is Little's Law again:

queue depth 50,000, throughput 500/s → wait time = 100 seconds

Every one of those 50,000 items gets processed eventually. Every single one gets processed after its caller has already timed out. The system does the full amount of work and delivers none of the value, and the memory holding that queue is itself now a failure mode.

Worse, the queue changes the shape of the failure into a less useful one. Without it, request 6,001 gets an immediate 503, the client knows right away, can retry with backoff, can fall back, can tell the user something. With an unbounded queue, request 6,001 gets accepted and times out 30 seconds later, after the client has waited and held its own resources for nothing, learned nothing sooner, and now retries, adding a second item to the queue for work you'd already started.

So: every queue is bounded, and the bound comes from the latency target you actually care about.

max_depth = target_latency × service_rate

Serving at 500/s with a 2-second latency target means a queue of 1,000 items, not one more. Beyond that, reject. This applies to your HTTP accept queue, your connection-pool wait queue, your job queue, every in-process channel. An unbounded queue anywhere in the request path is exactly where the latency will accumulate.

Backpressure where you can, shedding where you can't

Backpressure means the consumer tells the producer to slow down, and the producer can. It's strictly better wherever it's available, because no work gets discarded, the rate just matches. TCP flow control is the original version of this idea; HTTP/2 flow-control windows, reactive streams, bounded channels where the producer blocks, and consumer pause/resume on a Kafka worker are all re-implementations of it. In Node, writable.write() returning false is backpressure, ignoring that return value and writing anyway is the standard way to leak memory in a pipeline.

Backpressure works inside a closed system, your own pipeline, code you control. It fails completely at an open boundary: you can't tell a million browsers, a partner's integration, or a mobile app fleet to send fewer requests. There's no window to shrink. At that boundary, the only option left is load shedding, refuse work immediately and cheaply, so the work you do accept actually completes. Shedding isn't a failure of design. It's the design. The choice was never "shed or serve everyone", it's "shed deliberately, or let the overload choose for you by timing everything out instead."

Shed on the signal that tells the truth earliest

Not every signal is equally honest, and the ranking matters:

Queue wait time is the best signal, it is the latency you're about to violate, and it leads everything else. Concurrency in flight versus your limit is next, direct and cheap. Queue depth is good but needs a service rate to interpret. Event-loop delay in Node measures the actual contended resource. CPU utilisation is mediocre, saturation isn't linear in CPU, and I/O-bound work can show low CPU while queueing badly. Latency p99 lags, by the time it moves, the queue is already deep. And error rate is the worst signal of all: it's the outcome you were trying to prevent in the first place, arriving last.

For Node specifically, event-loop delay is an excellent, cheap, local signal:

import { monitorEventLoopDelay } from "node:perf_hooks";
const h = monitorEventLoopDelay({ resolution: 10 });
h.enable();
// Shed when the loop is persistently behind: the process cannot keep up, full stop.
const overloaded = () => h.mean / 1e6 > 70;   // ms

And a shed response is only cheap if it happens early, reject before authentication, before deserialising a large body, before touching the database. A 503 that costs as much to produce as a 200 protects nothing at all.

The Order Matters, Not Just the Presence

Put together, the three layers cover three genuinely different failures, and none of them substitutes for another:

A timeout bounds one call to a dependency that's slow. It does nothing about the volume of calls you keep making to one that's dead, that's what a circuit breaker stops. A breaker does nothing about legitimate, healthy traffic simply exceeding your own capacity, that's backpressure and shedding. And underneath all three sits the retry logic that, misconfigured, turns any one of these into a self-sustaining outage regardless of how well the other two are built.

This is also, not coincidentally, the fuller version of a claim made in passing in an earlier piece on connection pooling in serverless environments: that platform retry policies, re-invoking every failed serverless request during a connection exhaustion event, are "one of the few failure modes where client retries are strictly harmful." That's retry amplification, in exactly the shape described above, a thousand concurrent invocations failing at once, each one retried by the platform, adding load to a connection table whose entire problem was already too many clients. A retry budget at the right layer, or a breaker that stops the calls outright, is the actual fix; raising max_connections again is not.

None of these three layers is optional once you're running anything with a real dependency graph. But they answer different questions, they fail in different ways when misconfigured, and the order in which you reach for them, bound the call, stop the calls, then shed what you can't backpressure, is the order that actually holds under load.

Sourced from the System Design In-Depth course, Timeouts, Retries, and Backoff, Circuit Breakers, Bulkheads, and Graceful Degradation, and Backpressure and Load Shedding.

Connection Pooling in the Serverless Era: Five Failure Modes

Jatin Jain Saraf — Sun, 02 Aug 2026 08:23:44 +0000

Your database CPU is at 20%. Your slowest query is 12ms. Your slow-query log is empty. And your p99 is three seconds. This is what a connection pool failure looks like — and it never looks like a database problem.

Every engineer learns the same sentence about connection pooling: "reuse connections instead of opening a new one per request." It's true, it's useful, and it's where most people stop.

Then you deploy to serverless. Or you turn on autoscaling. Or someone adds a metrics exporter. And you discover that connection pooling isn't a performance optimisation you bolt on — it's a shared, global, hard-capped budget that half your infrastructure is spending without telling anyone.

This article is about the failure modes. Not "what is a connection pool," but the five specific ways pooling breaks in modern deployments, why each one disguises itself as something else, and what the fix actually costs you.

First: A Postgres Connection Is Not a Socket

Engineers price a database connection like an HTTP connection — a file descriptor, some buffers, a few kilobytes. Cheap. Open a thousand of them.

In PostgreSQL, a connection is a forked operating system process. Per connection, you get:

An OS process with its own page tables and scheduler entry, plus a few megabytes of private memory that never becomes shared. This is why connecting to Postgres costs orders of magnitude more than connecting to Redis, and why "one connection per request" is a design error rather than a style preference.

The right to allocate work_mem — and not once per connection, but per sort or hash node in the running query. One query with three hash joins holds three multiples of it. This is how databases get OOM-killed while every dashboard looks calm.

A snapshot, if the connection is inside a transaction, which joins the vacuum horizon. That's how a single forgotten idle in transaction connection blocks dead-tuple cleanup across the entire database.

So max_connections = 100 isn't an arbitrary cap the Postgres developers picked to annoy you. It's a memory and scheduler budget expressed as a count. Raising it to 2,000 doesn't buy you 2,000 connections' worth of throughput — it buys you 2,000 processes contending for the same cores and the same shared_buffers, trading a polite refusal of the 101st client for death by memory pressure.

The analogy worth keeping: your database is a restaurant with a fixed number of tables and a fixed number of cooks. max_connections is the tables. On a busy night the tempting move is to cram in more tables — but the cooks didn't multiply, so every dish arrives late and the kitchen falls behind on all of it at once. The restaurant that keeps ten tables and queues everyone else at the door serves more diners per hour.

That queue at the door is your connection pool's wait queue. It is the cheapest place in your entire system for a request to wait, and the last place anyone thinks to measure it.

Failure Mode 1: The Only Number That Matters Is `N × Pool Size`

A pool is configured per process. The limit is global. Nearly every exhaustion incident lives in that gap.

Here's a realistic accounting for a modest production system running against max_connections = 100:

Consumer	Count	Connections
API instances × pool	12 × 20	240
Worker instances × pool	4 × 10	40
Cron / scheduled jobs	3	3
Migration job during deploy	1	1–5
Metrics exporter	2	2–10
An analyst's `psql` session	1	1
`superuser_reserved_connections`	—	3 reserved
Total demand		~290 against 97 usable

Two things make this vicious.

It's a deploy-time cliff, not a load-time one. A rolling deploy briefly runs old and new instances side by side, doubling that top row. Which is why these incidents correlate with deploys rather than with traffic, and why the postmortem keeps looking at the wrong graph.

The bottom rows are invisible. Nobody counts the metrics exporter. Nobody counts the migration container. Those are exactly what tip you over.

The rule, stated properly: allocate max_connections as a budget across all consumers, then derive per-instance pool size by division.

pool_size_per_instance = floor(max_connections / max_instances) - safety_buffer

And the cost of that rule, stated honestly: pool size now depends on replica count. It has to be computed against your autoscaler's ceiling, not your current instance count — which means you deliberately run a pool smaller than any single instance could use at peak.

Why this matters in production: your autoscaler's max-replica setting is a database configuration setting. If max_replicas × pool_size exceeds max_connections, you haven't got a risk. You've configured an outage and scheduled it for the next traffic spike.

Failure Mode 2: Serverless Turns Concurrency Into Connections

A function runtime has no shared pool because it has no shared process. Each concurrent invocation is an isolated environment whose pool has a maximum useful size of one — it serves exactly one request.

Warm containers reuse a connection across invocations, which helps, and which is precisely why this problem is intermittent and maddening to reproduce. But the scaling unit is concurrency, and concurrency is the one thing you don't control.

1,000 concurrent invocations × 1 connection each = 1,000 connections
max_connections = 100, minus 3 reserved, minus everything in the table above

Roughly 90 invocations get a connection. The rest get FATAL: sorry, too many clients already.

There's a specific version of this that catches teams who did read the tutorial:

// Looks correct. Is correct — in a long-running Node server.
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
});

export default async function handler(req, res) {
  const result = await pool.query('SELECT * FROM transactions LIMIT 10');
  res.json(result.rows);
}

In a long-running process, const pool is created once and reused across every request. Correct. In a serverless function, the module is re-imported on each cold start — so max: 10 isn't a ceiling of 10 connections, it's a ceiling of 10 per concurrent environment. A hundred concurrent invocations makes it 1,000.

The global singleton pattern helps at the margins, because it survives warm starts within a container:

// lib/db.ts
import { Pool } from 'pg';

const globalForPg = global as typeof global & { pgPool?: Pool };

if (!globalForPg.pgPool) {
  globalForPg.pgPool = new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 2,                      // small per instance — the pooler owns the total
    idleTimeoutMillis: 10_000,
    connectionTimeoutMillis: 5_000,
  });
}

export const pool = globalForPg.pgPool;

But be clear about what this is: damage control, not a fix. It caps each environment at 2 connections instead of 10. It does not stop the platform from giving you 500 environments. On Vercel Functions and equivalents, the singleton pattern alone is insufficient — every team shipping a direct Postgres connection plus a singleton is running on borrowed time, and hasn't hit the limit only because they haven't hit enough concurrent traffic yet.

The blast radius is the real story

What makes this an architecture problem rather than a tuning problem is that the failure does not land on the traffic that caused it.

The connection table is global. When it's full, it's full for everybody:

The internal admin panel stops loading — and its on-call isn't yours.
The payouts worker's queue backs up silently.
The metrics exporter fails, so your dashboards go blank during the incident.
An engineer opens psql to investigate and gets refused.

Then the platform's retry policy re-invokes every failed request, adding load to a resource whose entire problem is too many clients. This is one of the few failure modes where client retries are strictly harmful.

Why this matters in production: connection exhaustion is a tenancy problem as much as a capacity one. If one spiky autoscaled workload can consume the budget of your payments worker, you've coupled two services through a resource neither of them monitors. The cheap mitigation is a per-role cap — ALTER ROLE app_web CONNECTION LIMIT 40 — which converts a shared outage into a contained one, at the cost of one workload hitting its ceiling while slots sit idle elsewhere.

Failure Mode 3: Raising the Pool Makes It Worse

This is the counterintuitive one, and it's the standard incident response.

Requests are timing out waiting for connections. An engineer raises the per-instance pool from 20 to 100 across 10 instances, restarts, and throughput drops further while p99 gets worse.

Little's Law explains why. L = λW — the number of connections you need busy at once is arrival rate times hold time. Take 400 req/s where each request runs one 5ms query, then run it again after a bad plan pushes that query to 200ms:

Healthy:  L = 400/s × 0.005s = 2 connections busy on average
          ρ = (400 × 0.005) / 20 = 0.1   → waits negligible

Degraded: L = 400/s × 0.200s = 80 connections wanted, against a pool of 20
          ρ = (400 × 0.200) / 20 = 4.0   → demand exceeds capacity, queue grows unbounded

Two connections is the honest answer at healthy load. Intuition wants pool size to track concurrent requests; Little's Law says it tracks concurrent requests × the fraction of their life spent inside the database, which is usually small. That gap is why correctly-sized pools look absurdly low to people who haven't done the arithmetic.

So why doesn't a bigger pool help in the degraded row? Because service time S isn't a constant. It depends on how many queries are executing concurrently against fixed cores and a fixed disk. Past hardware saturation, extra in-flight queries add no throughput — they divide the same throughput into slower pieces. Then second-order costs push throughput actively down: context switching between hundreds of runnable backends, contention on buffer-mapping and lock-manager partitions, work_mem allocations evicting your working set from cache, and more concurrent snapshots holding back the vacuum horizon.

Hence the principle worth memorising: queueing outside the database is nearly free; queueing inside it is expensive. A request in a pool's FIFO costs a promise and a timer. A request inside the database costs a process, several megabytes, a snapshot, and a share of every lock partition it touches.

The community starting point, popularised by HikariCP, is connections ≈ (2 × core_count) + effective_spindles. Treat it as a hypothesis to load-test, not a law — effective_spindles is a rotational-disk-era proxy for storage concurrency, and on NVMe at 500K+ random IOPS it means something quite different from its name. Note also that it lands in the low tens for the whole database, not per instance.

Why this matters in production: during pool exhaustion, the right move is almost never "raise the pool." It's to find what raised hold time — a slow query, a lock wait, an external call inside a transaction — because L = λW makes pool demand linear in W, and W is the term you can usually cut by an order of magnitude. Raising C to match a broken W just relocates the collapse into the database, where it costs more and hides better.

Failure Mode 4: Transaction Pooling Silently Eats Session State

The structural fix for serverless is a transaction-mode pooler — PgBouncer, RDS Proxy, Supavisor, Prisma Accelerate. A lightweight process holds a few real backends and multiplexes many clients onto them, lending a backend only for the duration of a transaction. Ten thousand clients, twenty backends.

What it costs is session state, because the backend you get is not the one you had last time. And the way you find out is a production error that says nothing about pooling.

Feature	Why it breaks under transaction pooling
Server-side prepared statements	`PREPARE` lives on one backend; your next statement may land on another. PgBouncer 1.21+ can track these — verify your version rather than assume
Session `SET` variables	`SET search_path` or `SET timezone` applies to a backend you're about to lose. `SET LOCAL` inside a transaction is safe
`LISTEN` / `NOTIFY`	Needs a persistent session to receive on. Silently receives nothing — no error, just missing events
Session-level advisory locks	`pg_advisory_lock()` is held by a session about to be lent elsewhere, and can never be released by its owner. Use `pg_advisory_xact_lock()`
Temp tables, `WITH HOLD` cursors	Session-scoped objects. Gone at transaction end

The failure signature is worth committing to memory, because nothing in it mentions pooling: the ORM works perfectly locally against a direct connection, and throws prepared statement "s0" does not exist in production. Or relation "work_items" does not exist halfway through a request. Or — worst of all — no error, just timezone drift producing quietly wrong date arithmetic, because a session SET didn't survive.

For Prisma specifically, the two-connection-string setup is non-negotiable, because the migration engine uses session-level advisory locks and will hang indefinitely through a transaction-mode pooler:

DATABASE_URL="postgresql://user:pass@pooler-host:6432/mydb?pgbouncer=true"
DIRECT_URL="postgresql://user:pass@postgres-host:5432/mydb"

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")   // pooler, for app queries
  directUrl = env("DIRECT_URL")     // direct, for migrations
}

The general pattern, whatever your stack: two endpoints against one database. A transaction-mode port for application traffic, and a session-mode or direct connection for migrations, LISTEN-based workers, and human debugging. The cost is a second connection string to configure, get wrong once, and document.

One more thing worth saying plainly: session mode is the compatibility escape hatch, not a fix for exhaustion. It holds a backend for the client's entire connection, giving roughly 1:1 reuse — all of a proxy's operational cost with none of the multiplexing benefit.

Failure Mode 5: Holding a Connection Across a Call You Don't Control

This one passes code review every single time.

await client.query('BEGIN');
await client.query('UPDATE orders SET status = $1 WHERE id = $2', ['charging', id]);

// Three seconds of somebody else's p99 — with a backend, a snapshot,
// and a row lock all held open, executing nothing.
const charge = await stripe.charges.create({ amount, currency: 'usd' });

await client.query('UPDATE orders SET charge_id = $1 WHERE id = $2', [charge.id, id]);
await client.query('COMMIT');

Run L = λW on it. At 50 req/s with a 3-second vendor call, you need 150 connections held to do essentially no database work. Your pool is 20.

Your pool size is now a function of a vendor's p99. When their latency doubles, every unrelated endpoint on that instance goes down with it.

And statement_timeout will not save you — this is the genuinely useful part. No statement is running. The backend is idle in transaction, holding a snapshot that blocks vacuum database-wide and row locks that stall other writers, while executing nothing at all. The setting that actually fires is idle_in_transaction_session_timeout, which every application role should have, and which still only acts after the damage, aborting mid-payment.

The fix is structural, not configurational: commit an intent carrying an idempotency key, make the external call holding nothing, then commit the outcome in a second short transaction.

The cost, named honestly: one atomic operation became two, so a crash between them leaves an order stuck in charging. That needs a reconciliation job that finds stale intents and asks the provider what happened — which is exactly why the idempotency key is written in the first transaction rather than generated at call time. The trade is real: a recoverable inconsistency in exchange for not tying your pool to someone else's uptime.

The Metric Nobody Graphs

Every failure mode above shares a diagnostic signature, and it's why these incidents burn hours.

When the pool saturates, latency accumulates before any query runs — so every tool you'd reach for measures the wrong interval:

pg_stat_statements reports execution time. Your queries look fine at 8ms.
Slow-query logs are silent. Nothing ran slowly.
Your APM's database span starts once the driver already holds a connection.
Database CPU is low, which reads as "the database is healthy" and sends the investigation into application code.

Meanwhile p99 is 3 seconds, of which 2.99 were spent inside pool.connect() — an interval on no component's dashboard, because the app thinks it's database time and the database has never heard of the request.

So instrument the acquisition yourself:

async function acquire(pool: Pool) {
  const start = performance.now();
  const client = await pool.connect();
  // The number that holds your p99 during saturation. Histogram it. Alert on p99.
  metrics.histogram('db.pool.wait_ms', performance.now() - start);
  return client;
}

Also worth having permanently: waiting count (pool.waitingCount) for queue depth, in-use vs idle (totalCount, idleCount) which gives you utilisation, and acquisition timeouts per minute. Set connectionTimeoutMillis — unset means unbounded queueing, which is a queue with no admission control.

On the database side, the first query of any connection incident:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count DESC;

A large idle in transaction count is Failure Mode 5, live in production.

And if you're running PgBouncer, the single most important view:

SHOW POOLS;
-- cl_waiting: clients waiting for a server connection → THIS SHOULD BE 0

Alert thresholds worth setting today: warning at 70% of max_connections, critical at 85% (you're roughly 90 seconds from user-visible errors), page immediately on more than 10 connections idle in transaction, and alert on sustained cl_waiting > 0.

The Fixes, and What Each One Actually Costs

There is no free option. Pick the bill you'd rather pay.

Fix	What it buys	What it costs
Transaction-mode pooler (PgBouncer, RDS Proxy, Supavisor)	Ten thousand clients onto twenty backends. The right answer for serverless	Session state — prepared statements, session `SET`, `LISTEN`/`NOTIFY`, session advisory locks, temp tables. Plus a network hop (~0.5–2ms) and a new component in the request path that can fail
SQL over HTTP (Neon serverless driver, Supabase client)	Nothing persistent, so nothing to exhaust. Works in Edge Runtime, where TCP doesn't exist at all	The interactive transaction. Read-decide-write has nowhere to live, so every `SELECT ... FOR UPDATE` needs rethinking. Plus ~10–30ms per-query HTTP overhead and a vendor-specific driver
Managed pooler + cache (Prisma Accelerate)	Pooling plus per-query TTL/SWR caching, no infrastructure to operate	Vendor dependency — your database connectivity now depends on their uptime even when your Postgres is healthy. Plus a hop, plus pricing
Keep the pool in a long-running process	Puts the pool where a pool can live; functions call it over HTTP	The thing you were trying to delete. You now operate a server with deploys, health checks, and a scaling policy — and the bottleneck relocates to that tier's concurrency limit

Mapping that to real deployments:

Infrastructure	Strategy
Vercel / Netlify Functions	HTTP driver or managed pooler. Never a direct connection — the singleton pattern alone is insufficient
AWS Lambda	RDS Proxy or self-managed PgBouncer, transaction mode
Always-on K8s / Fly.io / Railway	Global singleton client, `connection_limit = floor(max_connections / max_pods) - buffer`
Supabase hosted	Supavisor on port 6543 for app traffic; port 5432 for migrations only
Edge Runtime / Middleware	HTTP driver only — V8 isolates have no TCP sockets, so `pg`, `postgres.js`, and standard Prisma simply cannot run
Local dev	Direct connection, but configure `DIRECT_URL` anyway so prod parity isn't a surprise

On that Edge Runtime row, the simplest advice is the best advice: don't query your database from Edge Runtime unless you're on an HTTP driver. Move database access to the Node.js runtime and reserve the edge for work that only touches KV or cache.

The Short Version

A Postgres connection is a forked process with megabytes of private memory, the right to allocate work_mem per plan node, and a snapshot that holds back vacuum. max_connections is a memory budget wearing a counter's clothing.

The only number the database sees is N instances × pool size, plus workers, cron, migrations, exporters, and reserved slots. Rolling deploys briefly double the app tier, which is why these incidents track deploys rather than traffic.

Small pools are faster under load. L = λW puts required concurrency at arrival rate × hold time — usually single digits. When the pool saturates, cut W; don't raise C.

Transaction-mode pooling costs session state, and announces it through errors that mention prepared statements rather than pooling.

Never hold a connection across a call you don't control, because statement_timeout cannot interrupt a backend that isn't executing anything.

And instrument pool-wait time. It's the interval that holds your p99 during every one of these failures, and it appears on nobody's dashboard by default.

Connection pooling isn't a performance optimisation. It's a shared budget with a hard ceiling, spent by more consumers than anyone has counted — and in serverless, the spending is done by a concurrency number you don't control.

PostgreSQL 19 vs. 18: What Actually Changed, Feature by Feature

Jatin Jain Saraf — Mon, 27 Jul 2026 15:23:36 +0000

PostgreSQL 19 vs. 18: What Actually Changed, Feature by Feature

Every major PostgreSQL release gets the same LinkedIn headline treatment: "this changes everything." Most of the time it doesn't, it's a dozen genuine improvements wrapped in the language of a paradigm shift. PostgreSQL 19 is a real release with real substance, but the honest way to evaluate it isn't the headline feature, it's asking what specifically didn't work in PG18 that now does, and what the actual cost of adopting it is.

The Shape of the Two Releases

PostgreSQL 18 was primarily an I/O and observability release: the Asynchronous I/O subsystem rewired how the engine reads from disk, pg_stat_io got a near-total overhaul, and B-Tree skip scans fixed a decade-old multicolumn-index limitation. PostgreSQL 19 is primarily a DDL/DML ergonomics and operational-maintenance release: the headline feature (SQL/PGQ) is a new query language surface, but the features that will actually change your on-call life are REPACK CONCURRENTLY, native partition reshaping, and parallel autovacuum.

That distinction matters because it tells you where to look for value. If PG18 already fixed your I/O-bound scan performance, PG19 isn't going to double it again, it's going to fix the maintenance operations that PG18 left untouched.

1. SQL/PGQ: Property Graph Queries

What it is: PostgreSQL 19 adds SQL/PGQ, the SQL:2023 standard for querying graph-shaped data. You define a property graph as a read-only view over existing relational tables (a node table, an edge table), then query it with MATCH and Neo4j-style arrow syntax: MATCH (a:Employee)-[:MANAGES]->(b:Employee). Internally, PostgreSQL rewrites the arrow syntax into ordinary joins against your existing tables before the planner ever runs, so it inherits your existing indexes and your existing row-level security automatically.

Comparison (PG18 vs. PG19): In PG18, and every version before it, modeling a graph relationship (a dependency tree, an org chart, an authorization graph, "who reports to whom") meant writing a recursive CTE, WITH RECURSIVE, walking a self-referencing table one level at a time. Recursive CTEs work, but they're procedural: you write the recursion, you write the termination condition, and the planner frequently struggles to estimate the cost of a deep or unbounded traversal, which is exactly the kind of query that "chokes the planner" that gets complained about on social media. There was no declarative way to say "find all paths matching this pattern" — you had to hand-build the loop yourself in SQL.

Pros:

No secondary graph database, no sync pipeline. If your graph queries are shallow-to-medium depth over data that's already relational, you get graph-shaped querying without standing up Neo4j and building an ETL job to keep it current.
Declarative pattern matching gives the planner a clearer picture of intent than a hand-written recursive CTE, which can lead to better plans for the same logical query.
Inherits your existing security model for free, since a property graph is "just a view."

Cons:

It's still running joins under the hood. You do not get index-free adjacency, the property that makes a native graph database like Neo4j fast for deep, multi-hop traversal (5+ hops over millions of edges). If your actual workload is that kind of traversal, SQL/PGQ won't rescue you from needing a dedicated graph store.
New syntax surface means new things to learn and new things the planner can misjudge; it's not yet battle-tested the way recursive CTEs are, having existed for two decades.
The realistic audience for this feature is "developers who currently reach for a recursive CTE for shallow hierarchical queries," not "teams running production graph analytics at Neo4j scale." Know which one you are before switching.

2. `REPACK`: Unifying and Unlocking `VACUUM FULL` / `CLUSTER`

What it is: PostgreSQL 19 introduces a single REPACK command that replaces both VACUUM FULL (rewrite the table to reclaim bloat) and CLUSTER (rewrite the table in index order). The critical addition is a CONCURRENTLY option — REPACK ... CONCURRENTLY — that rebuilds the table without taking the ACCESS EXCLUSIVE lock that made both of the old commands unusable on a live, high-traffic table.

Comparison (PG18 vs. PG19): In PG18, if a table had accumulated enough bloat (dead row versions from updates/deletes that VACUUM alone couldn't reclaim) that you needed to physically shrink it, your only in-core option was VACUUM FULL, which locks the table exclusively for the entire rewrite. On a table serving live traffic, that's not a maintenance task, it's a scheduled outage. The workaround was the third-party pg_repack extension, which achieves a similar result without full locking by building a shadow copy and swapping it in, but that means installing and trusting an external extension for something this fundamental.

Pros:

This is the single biggest operational upgrade in the release for anyone who has had to fight table bloat on a production system: the capability that pg_repack (the extension) existed specifically to provide is now in core, with CONCURRENTLY.
One command name instead of two (VACUUM FULL and CLUSTER remain for backward compatibility, but REPACK is now the unified entry point).
Removes a dependency on a third-party extension for a core maintenance operation.

Cons:

CONCURRENTLY avoiding the exclusive lock doesn't mean it's free, it still consumes I/O and CPU rewriting the table, and the new max_repack_replication_slots variable exists because concurrent repack has its own resource considerations to tune.
If your team already has pg_repack (the extension) working reliably, migrating to core REPACK CONCURRENTLY is a "nice to have simplify," not an urgent fix, don't rip out something that already works without testing the native replacement first.

3. Native Partition Merge/Split

What it is: ALTER TABLE ... MERGE PARTITIONS and ALTER TABLE ... SPLIT PARTITIONS, letting you reshape an existing partitioned table's partition boundaries natively.

Comparison (PG18 vs. PG19): PG18 invested heavily in partition query performance, more efficient planning over many partitions, better partitionwise joins, reduced memory use for partition pruning, but did nothing for partition maintenance. If a monthly partition grew too large and you wanted to split it into two, or several small partitions had accumulated and you wanted to merge them, there was no native command. You did it by hand: create new partition(s), migrate the relevant rows with INSERT ... SELECT, detach and drop the old partition, all while carefully managing locks and application downtime windows.

Pros:

Turns a multi-step, error-prone, DBA-scripted operation into a single DDL statement.
Makes it realistic to actually right-size partitions over time as data volume and access patterns change, rather than living with whatever partition scheme you picked at design time.
Complements PG18's partition query-planning work: PG18 made partitioned tables fast to query, PG19 makes them practical to maintain.

Cons:

Any partition reshape on a large table still means physically moving rows, this is not a free metadata-only operation, plan for I/O and lock impact accordingly.
Doesn't retroactively fix a bad initial partitioning key choice, it makes boundary changes easier, not a full re-partitioning strategy change.

4. `GROUP BY ALL`

What it is: New SELECT syntax, GROUP BY ALL, which automatically groups by every column in the SELECT list that isn't an aggregate or window function, no manual enumeration required.

Comparison (PG18 vs. PG19): PG18 addressed a related but distinct problem at the planner level: it learned to ignore GROUP BY columns that were functionally dependent on other grouped columns (for example, if you group by a table's unique primary key, other same-table columns don't logically need to be listed, and PG18's planner recognized this and dropped them from the actual grouping operation for efficiency). That's an internal cost optimization, it didn't change what you had to type. In PG18 you still hand-wrote every column in the GROUP BY clause, no matter how wide the SELECT list.

Pros:

Removes the tedious, error-prone task of keeping a long GROUP BY list in sync with the SELECT list, adding a column to one and forgetting the other is a classic source of "column must appear in GROUP BY" errors.
Particularly valuable for wide analytical queries with 10+ grouping dimensions.

Cons:

Implicit grouping means it's slightly easier to accidentally group by more (or fewer) columns than you intended if the SELECT list changes later, explicit lists are more self-documenting for complex queries. Use with intention, not as a default habit for every query.

5. `FOR PORTION OF`: Temporal UPDATE/DELETE

What it is: New clause for UPDATE and DELETE, FOR PORTION OF <period> FROM <start> TO <end>, that lets you modify or delete just a sub-range of a temporal (range-based) row, automatically splitting the surrounding range as needed.

Comparison (PG18 vs. PG19): PG18 introduced the constraint half of temporal tables: WITHOUT OVERLAPS for PRIMARY KEY/UNIQUE and PERIOD for foreign keys, letting you enforce that time ranges in a table never overlap. But it gave you no corresponding operation half. If you had a row valid from Jan–Dec and needed to change just the March–June portion, you had to manually delete the original row and insert two (or three) new rows representing the split ranges yourself, exactly the kind of fiddly, off-by-one-prone logic a database feature should be doing for you.

Pros:

Closes the gap PG18 left half-finished: PG18 gave you guardrails (non-overlapping constraints), PG19 gives you the verbs (safely editing a slice of history without hand-rolling the split).
Meaningful for any system tracking effective-dated data, pricing history, contract terms, HR assignment periods, insurance coverage windows.

Cons:

Temporal tables remain a niche feature relative to PostgreSQL's overall audience, most applications don't model data this way, and adopting WITHOUT OVERLAPS + FOR PORTION OF is a genuine schema-design commitment, not a drop-in swap.

6. `INSERT ... ON CONFLICT DO SELECT ... RETURNING`

What it is: Extends ON CONFLICT beyond DO NOTHING/DO UPDATE with a new DO SELECT ... RETURNING option, returning (and optionally locking, via FOR UPDATE/FOR SHARE) the row that caused the conflict, without modifying it.

Comparison (PG18 vs. PG19): PG18 made real progress on returning row state, adding OLD/NEW alias support to RETURNING across INSERT/UPDATE/DELETE/MERGE, so you could see before-and-after values in a single statement. But ON CONFLICT itself was unchanged: still only DO NOTHING (silently skip, tell you nothing about the existing row) or DO UPDATE (you must actually modify something to get a RETURNING result). The common "get-or-create" pattern, insert a row if it doesn't exist, otherwise just give me the existing one, had no clean native expression; teams worked around it with a no-op DO UPDATE SET col = col just to trigger a RETURNING clause.

Pros:

Directly solves get-or-create without a no-op write, which matters for both clarity and for avoiding unnecessary row versions from a fake update (fewer wasted row versions means less bloat, which loops back to why REPACK CONCURRENTLY matters less often).
Optional row locking (FOR UPDATE/FOR SHARE) on the conflicting row means you can safely read-then-act on it within the same statement, useful for concurrent upsert-adjacent logic.

Cons:

Another ON CONFLICT branch to learn and to get right in mixed application code that already juggles DO NOTHING and DO UPDATE logic; worth auditing existing upsert helper functions to see where this actually simplifies things versus where it's unnecessary.

7. TOAST Default Compression: `pglz` → `lz4`

What it is: The default compression algorithm for TOASTed (large, out-of-line) values changes from pglz to lz4.

Comparison (PG18 vs. PG19): lz4 TOAST compression already existed as an option in PG18 (and earlier), you could set default_toast_compression = lz4 or specify it per-column. But almost nobody did, because defaults are what most schemas actually run with. PG18 shipped with pglz as the out-of-the-box default; PG19 flips that default.

Pros:

A genuinely free win for anyone storing large JSONB, text, or bytea values who never manually tuned this setting, lz4 compresses and decompresses meaningfully faster than pglz at a comparable ratio.
Zero migration effort for new databases; the new default just applies.

Cons:

Existing tables don't retroactively recompress, this only affects newly TOASTed values going forward (or values rewritten via REPACK/VACUUM FULL), so an existing large database won't see the benefit until data is naturally rewritten or explicitly reprocessed.

8. Parallel Autovacuum Workers

What it is: A single autovacuum job on one table can now use multiple parallel workers, controlled globally by autovacuum_max_parallel_workers and per-table by the autovacuum_parallel_workers storage parameter. PG19 also adds a scoring system (autovacuum_vacuum_score_weight, autovacuum_freeze_score_weight, and related variables) to decide which tables get processed first, replacing a simpler threshold-only heuristic.

Comparison (PG18 vs. PG19): PG18 improved autovacuum's behavior significantly: "eager freezing" let normal vacuums freeze all-visible pages proactively (reducing the cost of a later full freeze), autovacuum_worker_slots let you raise the effective worker cap at runtime without a restart, and autovacuum_vacuum_max_threshold let you set a fixed dead-tuple trigger point instead of relying purely on percentages. What PG18 didn't change: each individual vacuum job on a given table still ran as one single worker process. On a genuinely huge, high-churn table, that one worker was the throughput ceiling, no matter how many total autovacuum worker slots your cluster had available.

Pros:

Directly attacks the specific case that bites teams running very large, high-write tables: a vacuum job that used to take hours on one worker can now split the work across several, on the same table, at the same time.
The new scoring system for processing order is a real improvement over pure threshold checks, better prioritizing which of many candidate tables actually needs attention most urgently.
Complements, rather than duplicates, PG18's improvements, PG18 made each vacuum pass smarter and more proactive; PG19 makes the biggest passes faster by parallelizing them.

Cons:

More parallel workers means more concurrent I/O and CPU contention during vacuum; on a system already tight on resources, this needs the same careful tuning any parallelism feature does, it's not automatically a net win without headroom to spend.
Per-table autovacuum_parallel_workers is one more tuning knob DBAs now need to understand and set deliberately for their biggest tables, rather than relying entirely on cluster-wide defaults.

9. Logical Replication: Native Sequence Synchronization

What it is: Sequences can now be included in logical replication. CREATE/ALTER PUBLICATION ... ALL SEQUENCES publishes all sequences, and ALTER SUBSCRIPTION ... REFRESH SEQUENCES syncs sequence values (not just existence) on the subscriber to match the publisher. pg_get_sequence_data() lets you inspect sync state directly.

Comparison (PG18 vs. PG19): PG18 made solid logical replication improvements, generated column values could finally be replicated, and the default streaming mode for new subscriptions switched from off to parallel for better apply performance. But sequences were entirely untouched by logical replication in PG18 and every prior version: a subscriber's sequences had no relationship to the publisher's. If you promoted a logically-replicated subscriber to primary during a failover, its sequences would still be wherever they last were on that subscriber, not caught up to the publisher's actual nextval() position, unless you manually reset them yourself before cutting traffic over.

Pros:

Removes a genuinely dangerous, well-known logical-replication gap: without this, a poorly-timed failover onto a logically-replicated subscriber can hand out primary-key values that collide with rows the old primary already committed, a duplicate-ID bug hiding specifically in your disaster-recovery path, the worst possible place for a bug to hide since it only shows up when you're already in an incident.
pg_get_sequence_data() gives visibility into sync state that simply didn't exist before, useful for confirming a subscriber is actually safe to promote.

Cons:

This is a correctness fix for a previously-silent gap, not a performance feature, teams need to actively adopt ALL SEQUENCES and REFRESH SEQUENCES in their subscription setup; it isn't retroactively applied to existing subscriptions without action.

Which of These Actually Change How You Operate Postgres

Ranking by realistic production impact, not headline appeal:

REPACK CONCURRENTLY — removes a hard operational constraint (mandatory downtime for bloat reclaim) that has existed since the beginning of Postgres.
Parallel autovacuum — directly extends throughput on the exact tables where autovacuum has historically fallen behind.
Logical replication sequence sync — closes a real correctness gap sitting specifically in failover paths.
Partition merge/split — makes partition schemes maintainable as data grows, rather than fixed at design time.
SQL/PGQ — genuinely useful for a specific class of query (shallow hierarchical/relationship modeling), not a universal graph-database replacement, know which camp you're in before treating it as a headline reason to upgrade.
Everything else (GROUP BY ALL, FOR PORTION OF, ON CONFLICT DO SELECT, TOAST lz4 default) — real, welcome ergonomics and default-quality improvements, but incremental rather than architectural.

Where This Fits

If you're running the PostgreSQL In-Depth course, the maintenance-and-bloat modules built around pg_repack and manual VACUUM FULL tradeoffs are the direct prerequisite for understanding why REPACK CONCURRENTLY matters as much as it does, and the partitioning phase is the natural place to slot in the new merge/split DDL. For the deep architectural picture PG19 builds on top of (process model, MVCC, the planner, WAL), see "The PostgreSQL Elephant in the Room."

PostgreSQL 19 isn't a rewrite of what Postgres is, it's the same server process, managing files intelligently, that "What the PostgreSQL Server Is Actually Doing" describes, just with more of the maintenance and ergonomic rough edges sanded down. That's not a smaller story than "Postgres killed the recursive CTE", it's a more honest one.

PostgreSQL #Database #Backend #SoftwareEngineering #DatabaseInternals #SQL #DevOps