DEV Community: Jatin Jain Saraf

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

Idempotency Keys: How to Make Retries Safe in Distributed Systems

Jatin Jain Saraf — Fri, 24 Jul 2026 20:34:05 +0000

Idempotency Keys: How to Make Retries Safe in Distributed Systems

A customer clicks "Pay Now" once. The request reaches your payment service, the charge succeeds, but the response times out on the way back. The client, seeing no answer, retries. Somewhere in your logs there are now two successful charges for one click. Nobody wrote a bug. The network just did what networks do.

Retries Are Not Optional, They're the Default

In a single process, a function call either returns or the whole program crashes with it. In a distributed system, that guarantee disappears. A request can fail before it reaches the server, after the server processes it but before the response comes back, or anywhere in between, and from the caller's side, all three failures look identical: silence, or a timeout.

Given that, a caller has exactly two honest choices: give up, or retry. Almost every serious system chooses to retry, because giving up on a payment, an order, or a blockchain transaction just because a router hiccupped is worse than the alternative. The problem is that "the alternative" retrying turns a network problem into a correctness problem, unless the operation you're retrying can tolerate being run more than once.

That property has a name: idempotency. An operation is idempotent if running it once and running it five times leave the system in the same state. SET x = 5 is idempotent — running it twice still leaves x at 5. x = x + 5 is not, run it twice and you've added 10. Retrying a network call is safe by default only when the operation behind it is naturally idempotent. Charging a card, placing an order, sending an email — none of these are. That's the gap idempotency keys exist to close.

How an Idempotency Key Actually Works

The mechanism is simpler than the name suggests. The client generates a unique key, typically a UUID, once, at the moment the user takes the action, before any request is sent. That key is attached to the request, usually as a header (Idempotency-Key: 8f14e...). Every retry of that same logical action, the same click, the same order, reuses the exact same key.

On the server, before doing any real work, the first thing that happens is a lookup: has this key been seen before?

Not seen before → this is genuinely a new request. Process it (charge the card, place the order), store the key alongside the result, and return.
Seen before, and finished → this is a retry of something already done. Skip the actual work entirely and return the stored result from the first attempt. The client gets the same success response it would have gotten the first time, and no charge happens twice.
Seen before, and still in progress → a retry arrived while the original request is still being processed (a slow response, not a failed one). The correct move is to make the retry wait or reject it, never to run the operation again concurrently.

That storage of "key → result" is the entire trick. It turns "run this action" into "run this action, but only the first time you're asked, and remember what happened so every later ask gets the same answer." The database row or cache entry holding that key is doing the same job as the WAL in PostgreSQL, giving you a durable record of intent so a retry, a crash, or a redelivery doesn't have to guess what already happened.

Why This Matters in Production, Not Just in Theory

This isn't a hypothetical edge case; it's the default behavior of almost every reliability mechanism you already rely on:

Payment gateways retry. Stripe, for instance, builds idempotency keys into its API specifically because a client-side timeout on a successful charge is common enough to have a name in their docs.
Message queues retry. SQS, Kafka consumers, and most queue systems offer at-least-once delivery, not exactly-once, because exactly-once delivery across a network is provably expensive to guarantee. "At least once, so dedupe on your end" is the honest tradeoff, and idempotency keys are "your end." This is exactly why background job systems like BullMQ build retries with exponential backoff and dead letter queues into the framework itself, rather than leaving it to each job handler to reinvent.
Mobile clients retry. A user on a bad connection taps "Submit" once, but the app, seeing no response after a few seconds, quietly retries the request behind the scenes. Without an idempotency key, that one tap becomes two orders.
Load balancers and proxies retry. Some infrastructure automatically retries a request that failed to connect, before your application code ever sees it happen once, let alone twice.
Webhooks retry, by design. Stripe, GitHub, and most webhook senders will redeliver an event if your endpoint doesn't return a fast 2xx response, on the assumption that a timeout means you never got it. Idempotent webhook processing — checking the event ID before acting on it — is the only thing standing between that redelivery and a duplicate side effect on your end.

Without an idempotency key sitting between "the network is unreliable" and "the operation isn't naturally repeatable," every one of these standard, unremarkable mechanisms becomes a live double-charge, double-order, or double-send bug waiting for the right timing to trigger it.

Example: Why a Blockchain Indexer Cannot Survive Without This

Nowhere is this more visible, or more unforgiving, than in a blockchain indexer, and it's worth walking through concretely.

An indexer's whole job is to watch a chain, pull each new block, extract the events or transfers inside it, and write them into your own database so your application can query them fast. That sounds like a simple one-pass pipeline: get block, process block, move on. In practice it never runs exactly once per block, for reasons entirely outside your control:

Reorgs. The chain itself can rewind and replay a range of blocks when a fork resolves. Your indexer will see block 18,402,001 more than once, as two different, competing versions of "reality."
Crash recovery. If your indexer process dies mid-batch, on restart it doesn't know precisely which of the last few blocks were fully written versus half-written, so the safe move is to reprocess a small overlapping window, not trust a fragile "last processed block" pointer to be exactly right.
RPC retries. The call to fetch a block from a node can itself time out and get retried, occasionally landing you the same block payload twice from two separate requests racing each other.

Now picture what happens without idempotency built in: a transfer event says "500 USDC moved from wallet A to wallet B." If that event gets processed twice, because of a reorg replay or a crash-recovery reprocess, and your indexing logic does balance += 500 each time it sees the event, wallet B's balance is now wrong by 500 USDC in your database, permanently, until someone notices the number doesn't match the chain and re-runs a full backfill. On a system indexing tens of millions of events, that's not a rare accident, it's a near-certainty over enough uptime.

The fix is the exact same pattern as the payment example, just with a different key. Instead of a client-generated UUID, the natural idempotency key is something already unique to the data itself: (transaction_hash, log_index) for an EVM chain, for instance, uniquely identifies one specific event, no matter how many times it's delivered to you. The write becomes an upsert keyed on that pair, not a blind balance += amount. Seen this (tx_hash, log_index) before? Skip it, or overwrite with the identical result, never add on top of it again. Reorgs, crash-recovery reprocessing, and duplicate RPC responses all become harmless, because the operation of "record this event" is now idempotent, exactly the same property a payment API gets from a client-supplied key.

Where to Put the Key

Idempotency keys work at whatever layer needs the guarantee, and the source of the key changes depending on who's best positioned to know "this is the same logical request":

Client-generated (payments, order placement, form submissions): the client mints a UUID once, before the first attempt, and resends the identical key on every retry of that same user action.
Data-derived (blockchain events, webhook deliveries, message queue consumers): the key comes from something already unique in the payload itself, a transaction hash, a webhook delivery ID, a message ID, so you never have to coordinate key generation between sender and receiver at all.

Either way, the underlying requirement is the same: the key must be stored durably, checked before any side effect runs, and scoped to an appropriate time window (payment idempotency keys are typically only guaranteed for 24 hours; a blockchain indexer's (tx_hash, log_index) uniqueness is effectively permanent).

Where This Fits in the Full Course

Idempotent webhook processing and BullMQ retry/backoff patterns are both covered hands-on in the Node.js In-Depth course, in the Practitioner phase — module P-7 ("Connecting External Services and Caching") and module P-12 ("Background Jobs and Task Queues with BullMQ"), respectively.

The Question Worth Asking Yourself

Next time you write a piece of code that has a side effect, charges something, sends something, increments something, and sits behind a network call, ask one question before you ship it: if this exact request arrived twice, on purpose or by accident, would the result still be correct?

If the honest answer is no, that's not a rare failure mode you're accepting, it's a bug you've already written. The network will find it for you eventually. It's better to find it first.

DistributedSystems #SoftwareEngineering #Backend #SystemDesign #Blockchain #APIDesign

How the LRU Cache Actually Works and Why Redis, Browsers, and Postgres All Approximate It

Jatin Jain Saraf — Tue, 21 Jul 2026 19:19:44 +0000

You've felt this before: you restart a service, or open your laptop after the weekend, and everything is sluggish for the first few minutes. Every page load feels like it's dragging. Then, without you doing anything, it speeds back up. Nothing changed in your code. What changed is that the cache went cold, and it just warmed back up.

Almost every system that makes this speed-up possible Redis, your browser, your database, even a function that "remembers" its last few results is solving the exact same problem underneath: it has limited room, so when that room runs out, it has to decide what to throw away first. The answer nearly all of them land on is the same one: throw away whatever hasn't been touched in the longest time. That policy has a name Least Recently Used, or LRU and the way it's actually built is one of the more satisfying "two weak pieces make one strong piece" stories in software.

The problem neither obvious answer solves

Say you want to build this yourself: a fixed-size cache that instantly tells you if something is stored, and instantly evicts the "stalest" entry the moment you're full.

Your first instinct might be a plain lookup table a dictionary, a hash map, whatever your language calls it. That gives you instant answers to "is this here?" But a lookup table has no concept of time. It doesn't know that key A was touched ten seconds ago and key B was touched ten minutes ago. To find the stalest entry, you'd have to check everything slow, and it gets slower as the cache grows.

So your second instinct might be a simple ordered list keep every entry in a line, most recently used at the front. That solves the ordering problem. But now finding a specific entry means walking the line from the front until you happen to find it. And worse, once you find it, moving it to the front means pulling it out of the middle of the line which, in a plain list, means shifting everything around it.

Neither piece alone works. One gives you instant lookup with no memory of time. The other gives you a sense of time with no instant lookup.

The fix: don't choose, combine

The actual solution used almost everywhere is to stop treating "look something up" and "track its age" as the same job, and instead let one structure handle each, wired together.

Picture a line of people, ordered by who was served most recently front of the line is "just here," back of the line is "hasn't been seen in ages." That's your time-ordering structure, and because it's a doubly linked line (each person knows who's directly in front of and behind them), pulling anyone out of the middle and moving them to the front is instant you're just relinking a few neighbors, not shuffling the whole line.

Now add a cashier holding a notebook that maps every person's name directly to their exact spot in that line. You don't scan the line to find someone you check the notebook, and it points you straight at them. That notebook is your lookup table, except its entries don't hold the person's information directly they hold a pointer to where that person is standing.

Put those two together and something interesting happens: a lookup stops being "search the line" and becomes "check the notebook, then relink two spots in the line." Both steps are instant, regardless of how many people are in line. The lookup table gives you the address; the line gives you a cheap way to reorder once you're there. Neither piece is doing the other's job they're doing their own job, wired to the same underlying entries.

Eviction is the same trick pointed at the back of the line instead of the front: whoever is standing at the very back is, by definition, the stalest entry so when the cache is full, that's who gets dropped, and the notebook forgets their name too.

Why real systems don't build this exactly

Here's the part worth sitting with: at genuinely large scale, most real caching systems don't implement pure LRU, because keeping a perfectly ordered "line" updated on literally every single read becomes its own overhead once you're handling millions of operations a second.

Redis's LRU eviction, for instance, doesn't maintain one global perfectly-ordered line at all it samples a handful of random keys and evicts whichever of those looks stalest, repeated as needed. It's an approximation, and a good one, because it gets 90% of the benefit of true LRU for a fraction of the bookkeeping cost.

Your browser's HTTP cache and CDN edge caches lean on the same core idea evict whatever's gone longest untouched once storage fills up to decide what to drop when they hit their limits, which is exactly why re-visiting a page you loaded five minutes ago feels instant, but a page from three weeks ago fetches fresh.

Databases play the same game one layer down. A database's in-memory buffer pool the pages of the table currently held in RAM instead of read from disk often uses something called clock-sweep instead of textbook LRU: cheaper to maintain, same underlying goal of keeping "hot" pages in memory and letting cold ones get evicted first.

And plenty of ordinary application code hits this without ever calling it LRU by name "cache the last 500 computed results, and once that limit is hit, drop whatever hasn't been asked for in a while" is the identical problem, just at a size where the textbook version runs perfectly well with no approximation needed.

The actual takeaway

The mechanism is almost deceptively small once you see it: pair a lookup table with an ordered line, and let the table's entries point directly into the line instead of duplicating what's in it. That's the whole trick. What's genuinely interesting is how far that one idea travels from a toy interview problem, to the reason your database feels fast after warming up, to the reason revisiting a webpage is instant and revisiting an old one isn't. You've probably relied on a system built this way today without ever knowing it had a name.

Why a Single ALTER TABLE Can Take Down Your Whole Database

Jatin Jain Saraf — Wed, 15 Jul 2026 16:05:59 +0000

Why a Single ALTER TABLE Can Take Down Your Whole Database

A team runs a routine migration: ALTER TABLE transactions ADD COLUMN region TEXT. Nothing dramatic on paper. Twelve minutes later the connection pool is full, every request from every user is failing, and on-call is paged. The migration itself was never the problem. A lock queue was.

A Lock Isn't a Wall, It's a Queue Ticket

The word "lock" makes people picture a barrier: something is locked, everything else waits outside. That's not quite how PostgreSQL locking works, and the difference is exactly what causes outages like this one.

A better picture is a queue ticket. Every statement that touches a table takes a ticket. Some tickets are compatible with each other, holders can be served side by side, no problem. Other tickets are not compatible, and a ticket holder has to wait for the incompatible one ahead of it to finish before it can be served.

Two plain SELECT statements are always compatible. They take the weakest kind of ticket there is, and a hundred of them can run at once without ever noticing each other. The trouble starts when someone joins the line holding the strongest possible ticket.

The Strongest Ticket Blocks Everyone, Even the Compatible Ones

PostgreSQL has a handful of table-level lock strengths, but only one really matters for this story. It's called ACCESS EXCLUSIVE, and it's acquired by ALTER TABLE, DROP TABLE, and TRUNCATE. It conflicts with every other lock in the system, including a plain read.

Here's the part that catches people off guard: PostgreSQL mostly serves lock requests in the order they arrive. So if an ALTER TABLE shows up and has to wait for one long-running SELECT to finish, it doesn't just wait quietly off to the side. It gets in line. And every SELECT that shows up after it also has to get in line, behind the ALTER TABLE, even though those new SELECT statements would have been perfectly happy running alongside the original one.

One slow reader plus one waiting migration is enough to freeze every future read on that table, until the slow reader finally lets go.

How Twelve Minutes Becomes an Outage

This is close to how it actually plays out in production. An analytics query kicks off against a busy table and, because nobody set a timeout on it, keeps running for twelve minutes. A few minutes in, someone ships a routine schema migration on that same table. The migration asks for its ACCESS EXCLUSIVE ticket and starts waiting on the analytics query.

From that moment, every new request the application sends to that table queues up behind the migration. Within half a minute, hundreds of connections are stuck waiting on a lock, not on any actual work. The connection pool, which was never designed to hold hundreds of idle-but-blocked connections, fills up completely. New requests can't even get a connection to wait with. The site goes down, and the root cause line in the postmortem is almost funny in how small it is: one long read, one migration with no timeout.

The Fix Is Two Settings, Not a Rewrite

The defense here isn't clever code, it's two timeouts that should be set before any schema change ever touches a live table.

The first tells the migration itself to give up quickly if it can't get its lock in a few seconds, rather than parking itself at the front of a queue that keeps growing. The second is a ceiling on how long any query is allowed to run at all, so a stray analytics query can never hold a lock for twelve minutes in the first place. Neither setting is exotic. Both are usually just missing.

Set correctly, the same migration either succeeds in under a second because nothing was in its way, or it fails immediately and cleanly so you can retry it a minute later. What you never want is the third option: waiting, silently, gathering a crowd behind it.

Row Locks Are a Separate, Smaller Story

Everything above is about locking the table's structure. There's a second, unrelated locking system for locking individual rows of data, and it solves a different problem: two transactions trying to change the same row at the same time.

The classic example is a balance check before a debit. Read the balance, confirm there's enough money, then subtract the amount. Without a row lock, two concurrent withdrawals can both read the same starting balance and both proceed, and the account ends up wrong. Locking that one row for the duration of the transaction closes the gap.

There's also a neat variant built for queues: instead of locking a row and making every other worker wait for it, a worker can ask to skip any row that's already locked and grab the next free one instead. That's the difference between ten background workers piling up behind each other and ten workers each picking up a different job at the same instant.

Deadlocks: When Two Correct Transactions Still Collide

Occasionally two transactions each hold a lock the other one needs. Transaction A has locked account 1 and wants account 2. Transaction B has locked account 2 and wants account 1. Neither can proceed, and neither will ever let go voluntarily. PostgreSQL notices this after about a second, picks one of the two transactions, and kills it so the other can continue.

The fix isn't a database setting, it's a coding discipline: always acquire locks in the same order, everywhere in the codebase. If every transfer function locks the lower account ID first regardless of direction, the two transactions above stop being able to collide at all. It's the same trick as everyone in a crowded hallway agreeing to keep to the right.

Locks With No Data Attached

PostgreSQL also offers a kind of lock that has nothing to do with any table or row: an application-defined lock, identified by whatever number you choose. It's mainly used for one thing, making sure a background job runs on only one server at a time, even when there are several application instances that could all try to start it. Grab the lock, run the job, and if the process crashes mid-job, the lock releases itself automatically instead of leaving things stuck. It's a lighter, more reliable substitute for the Redis-based mutex a lot of teams reach for by default.

The Takeaway

A lock isn't something you fight, it's a ticket you're standing in line with, and most locking outages don't start with a bad lock. They start with someone who forgot to set a timeout and ended up holding the line for everyone behind them.

Where This Fits in the Full Course

This is Module 13 of the PostgreSQL In-Depth course, from the Architect phase covering the internals senior engineers eventually have to learn the hard way. If this scenario feels familiar, the earlier modules on MVCC and transaction internals build the concurrency model this one depends on.

PostgreSQL #Database #Backend #SQL #ProductionIncidents #SoftwareEngineering

What the PostgreSQL Server Is Actually Doing

Jatin Jain Saraf — Sat, 11 Jul 2026 09:00:00 +0000

What the PostgreSQL Server Is Actually Doing

When you run CREATE DATABASE, PostgreSQL creates a directory. When you run INSERT, it writes a row to a file and logs the write for crash safety. When you run SELECT, it reads that file back. That's the whole trick, once you see it, the "magic" disappears.

What the PostgreSQL Server Is Actually Doing

Most developers use PostgreSQL for years without ever forming a picture of what it's doing when a query runs. SQL goes in, rows come out, and everything in between feels like a sealed box. It isn't a sealed box. It's a program, running on a computer, reading and writing files, same as any other program you've written. This article builds that basic picture, in plain language, with no prior database knowledge assumed.

A Database Is a Directory

Run CREATE DATABASE learning_postgres and PostgreSQL doesn't do anything exotic. It creates a directory on disk to hold that database's data. That's it. Every database you create gets its own folder, sitting under PostgreSQL's data directory, the same way any application on your computer might create a folder to store its files.

This is worth sitting with, because it quietly answers a question that trips up a lot of people: "where does my data actually live?" It lives in a directory on the disk of whatever machine is running the PostgreSQL server. Not in the cloud in some abstract sense, not floating in "the database." On disk, in files, in a folder PostgreSQL controls.

It also explains something you may have noticed without thinking about it: CREATE DATABASE returns almost instantly, milliseconds, not minutes. That's because creating a database is just creating an empty directory. There's no data in it yet. The real work, and the real cost, only begins once rows start filling those files.

A Table Is a File, a Row Is Bytes in That File

Inside that directory, each table you create is backed by its own file (large tables get split across several, but the idea holds). When you run:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

PostgreSQL takes that row and writes it, as bytes, into the file backing the users table. Nothing more mysterious than that.

When you run:

SELECT * FROM users WHERE email = 'alice@example.com';

PostgreSQL opens that file (or consults an index that points into it, more on indexes in a later module), scans through the rows stored there, checks each one against your WHERE condition, and returns the ones that match.

Think of the file as a very disciplined spreadsheet that only PostgreSQL is allowed to touch directly. You never open it yourself, you always go through SQL, but structurally, that's what it is: rows, stored as bytes, in a file, on disk.

Under the hood, PostgreSQL is really answering a short chain of questions before it hands you an answer: Is the data I need already sitting in memory, or do I have to go to disk for it? Is there a shortcut, an index, that gets me there without reading the whole file? And once I've found a row, is it actually the current, correct version, or an old one nobody needs anymore? You don't write any of that logic. But knowing it happens is what lets you start reasoning about why one query is fast and an almost-identical one is slow.

PostgreSQL Is a Process, Not a Black Box

When you connect to PostgreSQL, whether from psql, a Node.js app, or a GUI tool, you're talking to a running process on a server. That process listens for your connection, hands you off to a dedicated worker just for your session, and from that point on, every query you send is handled by an ordinary program doing ordinary things: reading bytes from files, writing bytes to files, holding some of that data in memory so it doesn't have to touch the disk every single time.

That last part, keeping frequently-used data in memory, is why the second time you query something is usually much faster than the first. The process remembers what it recently read from disk and serves it from memory instead. This is the same idea as any caching you've done in application code, just built into the database itself.

This is also why a query can get slower for no reason you can find in your SQL. If PostgreSQL just restarted, deployed, crashed, rebooted, that memory is empty again. Nothing is cached yet. The first round of queries has to go back to disk to rebuild that cache from scratch, before things speed back up. If you've ever seen a database feel sluggish right after a restart and assumed you'd broken something, this is usually the real reason: the cache went cold, not your code.

The Log That Saves You From Crashes

Here's the one detail that separates "just writing to a file" from what a production database actually needs to guarantee: what happens if the power goes out, or the process crashes, in the middle of a write?

PostgreSQL's answer: before it changes the actual data file, it first writes down what it's about to do, to a separate log. Only after that note is safely on disk does it go ahead and make the real change.

If the server crashes mid-write, it doesn't matter, on restart, PostgreSQL reads that log and replays anything that didn't finish. It's the same instinct as keeping a to-do list before starting a task: if you get interrupted, you don't have to remember what you were doing, you just check the list. This log is one of the reasons people trust PostgreSQL with data they can't afford to lose.

Why This Mental Model Matters

Once "PostgreSQL is a process that reads and writes files, with a safety log protecting every write" is in your head, a lot of things that used to feel like separate pieces of magic start looking like variations on the same idea:

Indexes are just extra files that let PostgreSQL find the right rows without scanning the whole table.
Caching (shared buffers) is just PostgreSQL keeping recently-used file contents in memory.
Replication is just another server reading that same safety log and replaying it, to keep a second copy of the data in sync.
VACUUM is just PostgreSQL cleaning up old row versions it no longer needs, so the files don't grow forever.

None of these are separate systems bolted onto a mysterious core. They're all extensions of the same basic loop: read files, write files, log before you write, keep useful things in memory.

PostgreSQL isn't magic. It's a server process that manages files intelligently, and every advanced feature it has is that idea, applied more cleverly.

Where This Fits in the Full Course

This is the very first mental model from the Foundation phase of the PostgreSQL In-Depth course, the phase built for zero prior database knowledge. If this clicked, the next modules build on it directly: the client-server connection model in detail, the relational mental model (tables, rows, and how they connect), and your first complete schema.

For readers who want the advanced version of this same territory, process architecture, MVCC, the planner, replication internals, see "The PostgreSQL Elephant in the Room." That article assumes you already have the basic picture this one just gave you.

The Question Worth Asking Yourself

Next time a PostgreSQL query does something you don't expect, slow, fast, returning stale-looking data, try asking the simplest possible question first: what files is it reading or writing right now, and what would that look like from the outside?

You'll be surprised how often that question, on its own, points you toward the answer, long before you need to reach for anything more advanced.

PostgreSQL #Database #Backend #SoftwareEngineering #DatabaseFundamentals #LearnToCode #SQL

Competition Is Inevitable. Cruelty Is Optional

Jatin Jain Saraf — Tue, 30 Jun 2026 14:47:34 +0000

Competition Is Inevitable. Cruelty Is Optional.

Every promotion in tech has an invisible downside.

When someone becomes an Intern, thousands of applicants don't.
When someone becomes a Junior Developer, another candidate receives a rejection email.
When someone becomes a Senior Developer, someone else waits another review cycle.
When someone becomes a Tech Lead or Engineering Manager, dozens of equally ambitious engineers aren't selected.

That's the reality of a competitive industry.

But here's where many people get it wrong.

Success isn't about destroying people. It's about becoming the best choice.

You don't need to sabotage a colleague.
You don't need office politics.
You don't need to hope others fail.

You need to build skills that make the decision obvious.

Learn continuously.
Take ownership.
Communicate clearly.
Deliver consistently.
Solve bigger problems than yesterday.

Competition is inevitable.
Cruelty is optional.

The goal isn't to terminate careers. The goal is to become so valuable that opportunities naturally come your way.

In the end, the market doesn't reward the loudest engineer.

It rewards the one who consistently creates the most value.

Outperform the competition. Respect the competitors.

SoftwareEngineering #CareerGrowth #Leadership #TechCareers #Engineering #ContinuousLearning #TechLeadership

Transactions and ACID in Practice: What Every Backend Developer Must Know

Jatin Jain Saraf — Fri, 26 Jun 2026 18:45:18 +0000

Most backend developers think they understand transactions.
Then a server crashes mid-transfer and $100 disappears between two SQL statements.

I wrote a deep dive on Transactions & ACID in practice isolation levels, FOR UPDATE SKIP LOCKED for job queues, why long transactions silently destroy performance, and the Node.js patterns that actually get it right.

Read the full article → https://insight.jatinjainsaraf.com/blog/transactions-and-acid-in-practice-what-every-backend-developer-must-know

From the PostgreSQL In-Depth course, built from years running Postgres at TB scale → https://academy.jatinjainsaraf.com/courses/postgresql-in-depth

PostgreSQL Backend Database SoftwareEngineering

When Judgment Becomes the Bottleneck

Jatin Jain Saraf — Sun, 21 Jun 2026 17:29:30 +0000

There's a moment in most senior engineers' careers that nobody warns you about.

You've earned trust. Your instincts are sharp.
People come to you before committing to a design. Before merging a PR. Before choosing a database. Before making a call.

And slowly, without noticing it, you stop being an engineer and start being a gate.

The trap looks like success.

You're being consulted because your judgment is valued.
You're in every important meeting because your perspective matters.
You're the person who "gets it."

But here's what's actually happening:

PRs sit waiting for your review.
Decisions stall until you're available.
Engineers stop thinking through options, because they'll just ask you anyway.
Your calendar fills with meetings that exist to get your approval.

The team isn't scaling. It's depending.

This is what judgment-as-bottleneck looks like:

Velocity is tied to your availability, not the team's capacity
Junior engineers learn your answers, not your reasoning
Decisions that could have been made in an hour wait three days
You feel indispensable, but the system is actually fragile

The irony? The more you care about quality, the more likely you are to accidentally create this pattern.

The fix isn't doing less. It's building judgment transfer.

Share the why behind decisions, not just the decision.
Write down the heuristics you use, the instincts you've built over years deserve to be externalised.
Define decision boundaries: "You own anything under this scope. Come to me only when it crosses these lines."
Let people make calls you'd have made differently, then debrief rather than override.

The best engineering leaders I've seen don't protect quality by owning every decision.
They protect it by raising the quality of how the team decides.

Your judgment shouldn't be the ceiling. It should be the foundation.

When you make yourself the bottleneck, even unintentionally, you're not protecting quality.
You're just delaying it.

The real work isn't being right every time.
It's building a team that can be right when you're not in the room.

💬 Have you ever caught yourself becoming the bottleneck, or working under one?

EngineeringLeadership #TechLead #SoftwareEngineering #LeadershipMindset #TeamScaling

Engineering Courses By JJS

Jatin Jain Saraf — Thu, 11 Jun 2026 16:59:59 +0000

Most tutorials teach you how to build a "Hello World" app.
But how do you actually build, scale, and optimize a full-stack application for production?

I’m thrilled to announce the launch of my new Academy—a dedicated learning hub for serious backend and full-stack engineers.

I’ve launched with 4 comprehensive, deep-dive courses:

🐘 PostgreSQL In-Depth MVCC, WAL, Autovacuum, & Query Planning.
🔗 https://academy.jatinjainsaraf.com/postgresql-in-depth

🟢 Node.js In-Depth Event Loop internals, streams, and performance tuning.
🔗 https://academy.jatinjainsaraf.com/nodejs-in-depth

⚛️ Next.js In-Depth Server components, caching strategies, and advanced routing.
🔗 https://academy.jatinjainsaraf.com/nextjs-in-depth

⚡ Redis In-Depth Caching patterns, persistence, and pub/sub architecture.
🔗 https://academy.jatinjainsaraf.com/redis-in-depth

🟢 Master JavaScript Testing
🔗 https://academy.jatinjainsaraf.com/master-javascript-testing

🟢 Docker In-Depth
🔗 https://academy.jatinjainsaraf.com/docker-in-depth

Here is how the Academy works:

🔓 100% Open Access I hate paywalls. You can read every single module in all 4 courses right now, completely free, without even logging in.

💬 Join the Discussion Got a question about a specific concept? Create a free account to unlock comments and talk directly with me and the community.

Ready to level up your engineering skills?

Browse the full catalog and start reading here:
🔗 https://academy.jatinjainsaraf.com/

Let me know which course you are diving into first! 👇

SoftwareEngineering #WebDevelopment #PostgreSQL #NodeJS #NextJS #Redis #TechCommunity

https://insight.jatinjainsaraf.com/mastering-postgresql-5-essential-tips-for-performance-optimization

Jatin Jain Saraf — Thu, 11 Jun 2026 16:56:14 +0000

Mastering PostgreSQL: 5 Essential Tips for Performance Optimization — Jatin Jain Saraf

When your database slows down, your whole application suffers. Discover 5 essential PostgreSQL optimization tips—from EXPLAIN ANALYZE to smart indexing—that…

insight.jatinjainsaraf.com

DEV Community: Jatin Jain Saraf

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

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

Only one of your four timeouts actually bounds anything

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

Backoff needs jitter, and jitter isn't a refinement

Retry amplification: the incident that outlives its own cause

Cancellation has to actually cancel

Layer Two: A Breaker Stops Making the Calls At All

The four ways a breaker gets misconfigured

Bulkheads: stop one dependency from starving everything else

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

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

An unbounded queue is not a buffer

Backpressure where you can, shedding where you can't

Shed on the signal that tells the truth earliest

The Order Matters, Not Just the Presence

Connection Pooling in the Serverless Era: Five Failure Modes

First: A Postgres Connection Is Not a Socket

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

Failure Mode 2: Serverless Turns Concurrency Into Connections

The blast radius is the real story

Failure Mode 3: Raising the Pool Makes It Worse

Failure Mode 4: Transaction Pooling Silently Eats Session State

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

The Metric Nobody Graphs

The Fixes, and What Each One Actually Costs

The Short Version

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

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

The Shape of the Two Releases

1. SQL/PGQ: Property Graph Queries

2. REPACK: Unifying and Unlocking VACUUM FULL / CLUSTER

3. Native Partition Merge/Split

4. GROUP BY ALL

5. FOR PORTION OF: Temporal UPDATE/DELETE

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

7. TOAST Default Compression: pglz → lz4

8. Parallel Autovacuum Workers

9. Logical Replication: Native Sequence Synchronization

Which of These Actually Change How You Operate Postgres

Where This Fits

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

Idempotency Keys: How to Make Retries Safe in Distributed Systems

Idempotency Keys: How to Make Retries Safe in Distributed Systems

Retries Are Not Optional, They're the Default

How an Idempotency Key Actually Works

Why This Matters in Production, Not Just in Theory

Example: Why a Blockchain Indexer Cannot Survive Without This

Where to Put the Key

Where This Fits in the Full Course

The Question Worth Asking Yourself

DistributedSystems #SoftwareEngineering #Backend #SystemDesign #Blockchain #APIDesign

How the LRU Cache Actually Works and Why Redis, Browsers, and Postgres All Approximate It

The problem neither obvious answer solves

The fix: don't choose, combine

Why real systems don't build this exactly

The actual takeaway

Why a Single ALTER TABLE Can Take Down Your Whole Database

Why a Single ALTER TABLE Can Take Down Your Whole Database

A Lock Isn't a Wall, It's a Queue Ticket

The Strongest Ticket Blocks Everyone, Even the Compatible Ones

How Twelve Minutes Becomes an Outage

The Fix Is Two Settings, Not a Rewrite

Row Locks Are a Separate, Smaller Story

Deadlocks: When Two Correct Transactions Still Collide

Locks With No Data Attached

The Takeaway

Where This Fits in the Full Course

PostgreSQL #Database #Backend #SQL #ProductionIncidents #SoftwareEngineering

What the PostgreSQL Server Is Actually Doing

What the PostgreSQL Server Is Actually Doing

What the PostgreSQL Server Is Actually Doing

A Database Is a Directory

A Table Is a File, a Row Is Bytes in That File

PostgreSQL Is a Process, Not a Black Box

The Log That Saves You From Crashes

Why This Mental Model Matters

Where This Fits in the Full Course

The Question Worth Asking Yourself

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

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

4. `GROUP BY ALL`

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

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

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