DEV Community: 晖莫

Pagination That Skips Rows Is an Off-by-One in the Data Model

晖莫 — Sun, 25 Oct 2026 13:00:03 +0000

A customer exported their transaction history and got 4,127 rows. The database said 4,203. Nobody could reproduce it, because the export had paginated through a table that was being written to at the time.

OFFSET is a count, not a position

SELECT * FROM transactions WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 100 OFFSET 2000;

OFFSET 2000 means "skip the first 2000 rows of a result I am computing right now". If three rows were inserted at the top between page 20 and page 21, every subsequent page shifts by three and the reader never sees three rows. Delete three instead and three rows appear twice.

The cost is the other half of the problem: OFFSET 200000 still computes 200,000 rows and throws them away. On a busy table, page 2,000 takes seconds while page 1 takes milliseconds — a latency curve your monitoring will attribute to "the database".

Keyset pagination, done properly

The fix is to paginate on a value, not a count:

SELECT id, created_at, amount_cents
FROM transactions
WHERE account_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 100;

The (created_at, id) row comparison is the important part, and it is where most attempts go wrong.

A cursor on created_at alone skips ties. If forty transactions share a timestamp — a batch job, a backfill, a now() default that resolves per transaction — then WHERE created_at < $2 skips all forty, or <= returns them again on the next page. Any cursor that is not unique is a page that either loses rows or repeats them.

The ORDER BY must match the tuple exactly, in the same direction. ORDER BY created_at DESC, id DESC with (created_at, id) < (...) is consistent. Mixing DESC on one and ASC on the other produces a filter that does not describe the sort, and Postgres will happily return rows in an order nobody asked for.

The index has to cover the tuple in that order. (account_id, created_at DESC, id DESC) makes the query a range scan with no sort step. Without it, keyset pagination is still correct but you are sorting the whole partition on every page.

Why this is a data-model bug

Both variants are the same mistake: the application assumes the query has defined an order when it has only defined a partial one. With OFFSET, the assumption is that the set does not change between pages. With a non-unique cursor, the assumption is that equal keys are distinguishable. Neither is true, and neither failure is visible in a test that pages through a static fixture.

The test that catches it pages while writing:

def test_pagination_does_not_skip_or_duplicate(db, client):
    seen = []
    cursor = None
    for _ in range(50):
        page = client.get("/transactions", params={"cursor": cursor}).json()
        if not page["items"]:
            break
        insert_a_new_transaction(db)   # the thing OFFSET cannot survive
        seen.extend(i["id"] for i in page["items"])
        cursor = page["next_cursor"]

    assert len(seen) == len(set(seen)), "a row was returned twice"
    assert set(seen) == {r.id for r in db.query("SELECT id FROM transactions")}

The second assertion is the one that fails on OFFSET: rows inserted above the cursor never appear, so the sets differ.

Practical notes

Return an opaque cursor (base64 of the tuple), not a page number. Clients treat numbers as indexes and will compute page 3 themselves, which puts the pagination contract back in their hands.

Decide what happens when the sort key is mutable. Keyset pagination assumes created_at never changes; a row edited to the top of the list can be seen twice. If the ordering column can change, order by (created_at, id) from an append-only table, or accept that the cursor describes a snapshot of that column.

The export that started this came back with 4,203 rows after the change, and the query time was flat across all 43 pages instead of climbing from 8ms to 900ms. Same database, same index budget. The only thing that moved was the arithmetic.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

Retrying on a 429 Is the Wrong Response to Backpressure

晖莫 — Sat, 24 Oct 2026 13:00:03 +0000

We hit our own rate limit at 200 requests per second, and the retry logic turned it into 600. The retries did not cause the incident. They caused the incident to last four hours instead of twenty minutes.

Three codes, three meanings

Most retry helpers treat "retryable" as one bucket. It is three, and they want opposite things.

429 Too Many Requests. You are over your quota. The server is not overloaded; it is protecting itself from you specifically, and it usually tells you exactly how long to wait. Retrying immediately is a request to be told no again. The correct response is to honour Retry-After and reduce your own rate — a client-side token bucket that the 429 is the feedback signal for.

503 Service Unavailable. The server is under pressure from everyone. Retrying is correct, but only with backoff and jitter, and only a bounded number of times. Here the retry is a hope that the load shifts; a retry storm makes the load worse but the 503 is not addressed to you.

Timeouts and connection resets. The outcome is unknown. The request may have been processed. Retrying is required for the client's behaviour and dangerous for the system, which is why this is the only case that demands an idempotency key rather than a backoff policy.

If your retry wrapper cannot tell these apart, it is guessing. Ours looked like this:

for attempt in range(3):
    resp = session.post(url, json=body)
    if resp.status_code != 429:
        return resp
    sleep(2 ** attempt)

Two seconds after a 429 is two more requests inside the window that just closed. With three attempts and 200 concurrent workers, the number of requests in flight tripled.

What we changed

Retry on 429 became "do not retry, slow down". The caller got a token bucket sized to the documented limit, shared across the process, refilled per second. A 429 now drains the bucket and the request waits for a token instead of failing and retrying. Concurrency to the upstream dropped from burst-shaped to flat.

Retry-After is respected even when it is impolite. An upstream answering Retry-After: 30 is a thirty-second pause for that key, not a suggestion. The alternative is a ban.

Bounded queue with a deadline in front. The requests that used to retry now sit in a short queue with a hard deadline. When the queue is full, the caller gets a 429 from us immediately, which is honest and cheap. When the deadline passes, the work is dropped rather than executed late — a payment captured forty seconds after the user gave up is a worse outcome than a clean failure.

Retries got a cap per key, not just per request. Three attempts per request, but also a total budget per idempotency key. Without that, a client retrying a failing request adds its own attempts on top of ours, and the two multiplication tables compound.

The counter-intuitive part

The instinct under load is to try harder: more attempts, shorter sleeps, more parallelism. Every one of those increases pressure on the thing that is signalling distress. Backpressure is the system telling you its capacity, and the correct response to a capacity signal is to send less traffic, not the same traffic more persistently.

Two numbers worth knowing for any dependency:

Your steady-state request rate, so a 429 is a comparison and not a surprise.
The p99 latency of a successful request, which sets the minimum sane timeout. A 500ms timeout on a dependency with a 2s p99 turns latency into errors and errors into retries.

We ended with roughly the same throughput as before the incident, at a third of the peak request count. The retries were never doing useful work. They were just loud.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

Your Graceful Shutdown Drops the Request It Was Supposed to Finish

晖莫 — Fri, 23 Oct 2026 13:00:14 +0000

Deploys were producing about a dozen 502s each, concentrated in a two-second window, and only on requests that took longer than five seconds. The service had preStop hooks, a terminationGracePeriodSeconds, and a SIGTERM handler. All of it was there. The order was wrong.

What shutdown actually did

def shutdown(signum, frame):
    server.stop_accepting()
    time.sleep(5)
    sys.exit(0)

That reads like "stop taking new work, let in-flight work finish, exit". The third line is the problem: sys.exit does not wait for anything. It raises SystemExit in the main thread, which unwinds the stack and ends the process with open transactions, open sockets, and any in-flight requests still being handled on worker threads.

Five seconds happened to be shorter than the slowest write path. So the requests that mattered — the ones holding an open transaction — were exactly the ones killed.

Graceful shutdown is four ordered steps

1. Fail readiness before you stop serving. The load balancer needs time to notice. If the readiness probe runs every ten seconds, the pod must fail it and then wait at least one full interval before refusing connections. Otherwise the LB keeps sending traffic for up to ten seconds after you stopped accepting it, and those requests fail at the TCP layer. This is not a shutdown problem, it is a "the LB never heard you" problem, and it is the most common cause of 502s during rollout.

2. Stop accepting new connections, keep the listener open just long enough. In Kubernetes, preStop: sleep 10 before SIGTERM is the crude version and it works. The precise version is a readiness endpoint that returns 503 immediately on the signal, a sleep of two probe intervals, then the socket closes.

3. Drain in-flight requests with a counter, not a sleep. Track started/finished requests around the handler. Wait until the counter is zero or the deadline expires. A sleep is a guess about your tail latency; the counter is the actual number.

4. Close the database pool last, and never mid-transaction. The pool's close should wait for checked-out connections. If a handler is between BEGIN and COMMIT, killing the process rolls back work the client already believes succeeded — and if there is no idempotency key, the client retries and you get a double write instead of a clean failure.

The budget has to be smaller than the killer

The orchestrator sends SIGKILL at the end of the grace period, and that is not negotiable. So the app's own deadline must be strictly shorter:

Stage	Budget
Readiness turns 503	t+0
Two probe intervals	t+0 → t+20s
Drain in-flight	t+20s → t+45s
Close pool, flush logs	t+45s → t+50s
`terminationGracePeriodSeconds`	60s

If the drain deadline is 30s and the grace period is 30s, you have written code that assumes the drain always finishes instantly. It does not. The failing requests are always the slow ones — that is what "slowest" means.

What to test

Shutdown is testable and almost nobody tests it. Send SIGTERM to a running instance under load, and assert two things: zero requests return a connection error, and every request that was mid-transaction either committed or was cleanly rolled back and retried.

The version that finally worked here was eleven lines: a signal handler that flips readiness, a counter around the handler, a drain loop with a deadline, and a pool.close() that waits. The 502s went to zero. The original five-second sleep was not too short — it was in the wrong place, doing the job of a counter.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

The Backfill That Ran Twice and Doubled a Balance

晖莫 — Thu, 22 Oct 2026 13:00:06 +0000

The backfill was 300 lines of a script somebody wrote carefully. At row 1.2 million it hit a deadlock, the process died, and the operator did the obvious thing: ran it again. Every account in the first 1.2 million got its adjustment applied a second time.

Nobody noticed for three days, until support asked why some customers had a credit balance larger than their lifetime spend.

The shape of the bug

The script did this:

for account in accounts_needing_backfill():
    conn.execute(
        "UPDATE accounts SET balance_cents = balance_cents + %s WHERE id = %s",
        (credit_for(account), account.id),
    )

That is an increment, not an assignment. It has no memory of whether it has run before, so running it twice is not a retry — it is a second credit. The script was safe exactly once.

The failure was not the deadlock. The failure was that recovery from the deadlock was "run it again", and the script could not tell the difference between the first run and the second.

A backfill must be a function of the data

The property to aim for: running it N times produces the same rows as running it once. There are three standard ways to get it, and they trade off differently.

Compute the absolute value from the source of truth. If the credit is derivable, set it instead of adding it:

UPDATE accounts a
SET balance_cents = (
    SELECT sum(amount_cents) FROM ledger_entries e WHERE e.account_id = a.id
);

This is the strongest version, because the second run is not just safe, it is corrective — if any earlier run was wrong, this one fixes it. It also fails loudly when the ledger cannot explain the balance, which is the signal you want.

Mark rows in the same transaction as the change.

UPDATE accounts
SET balance_cents = balance_cents + 500,
    backfilled_at = now()
WHERE backfilled_at IS NULL AND id = ANY($1);

The guard column and the mutation commit together, so a crash leaves either both or neither. The next run skips what is done. This costs a column, and the column is worth it.

Record the work in a progress table when you cannot touch the target table's shape: (backfill_name, row_id, applied_at) with a unique key on the first two. Insert-and-update in one transaction; the unique violation is the "already done" signal.

All three share the same idea: the script needs a durable record of what it has done, and that record must move in the same transaction as the effect.

What the operator should have had

Two things would have caught this in minutes instead of three days.

A checksum before and after. Before the backfill, record sum(balance_cents) and the row count. After it, compare against the expected delta. If the delta is double what the business case says, stop. That is one query against a number the finance team already knows.

An idempotency test in the script's own test suite. Run the backfill twice against a fixture and assert the result equals running it once. That test is six lines and it fails on the version above:

def test_backfill_is_idempotent(db):
    run_backfill(db)
    once = db.scalar("SELECT sum(balance_cents) FROM accounts")
    run_backfill(db)
    assert db.scalar("SELECT sum(balance_cents) FROM accounts") == once

The rule

Any script that mutates money must be able to run twice, and the second run must be a no-op. Not "should be safe" — must be, and tested, because the alternative is discovered by a customer.

The general version: a backfill is a migration of data, and a migration you cannot re-run is a migration whose failure mode is a manual investigation. Write the guard first. The increment is the bug; the deadlock was just the witness.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

The Migration That Passed in Staging Because Staging Was Empty

晖莫 — Wed, 21 Oct 2026 13:00:06 +0000

The migration ran in 40ms in staging and took the checkout table down for six minutes in production. Both statements in it were correct. Staging just had nothing to say about either of them.

Two failures that look like one

The migration was:

ALTER TABLE orders ADD COLUMN fulfilled_at timestamptz;
UPDATE orders SET fulfilled_at = shipped_at WHERE shipped_at IS NOT NULL;
ALTER TABLE orders ALTER COLUMN fulfilled_at SET NOT NULL;

On a table with eleven rows, that is a round trip. On 40 million rows:

The UPDATE rewrote every row with a non-null shipped_at. That is a lot of WAL, a lot of dead tuples, and a lock on all of them.
SET NOT NULL had to scan the table to validate, holding ACCESS EXCLUSIVE for the duration. No reads, no writes, no queries at all on that table while it ran.

Staging was created from a migration chain, not from data. Every table was empty or nearly so. Both operations completed before anyone could notice they were the wrong shape.

What a data-shaped database would have said

The point of staging is not "the SQL is syntactically valid" — a linter does that. The point is to answer three questions that only data can answer:

How many rows does this touch? If the answer is "all of them", the migration needs to be batched.
How long does the lock last? Measure it, do not guess. Add a statement timeout and see it fire.
What does the new constraint do to existing rows? An empty table can satisfy any constraint.

A production-shaped staging database answers all three in the same 40 seconds the empty one took. A masked dump of a few million rows is enough; it does not need to be recent or complete, it needs to have the same shape of distribution.

The migration we should have written

Three separate deploys, each boring.

Add the column, nullable. No rewrite on a modern Postgres, no lock beyond the catalog update.

Backfill in batches with a bound.

UPDATE orders
SET fulfilled_at = shipped_at
WHERE id IN (
    SELECT id FROM orders
    WHERE fulfilled_at IS NULL AND shipped_at IS NOT NULL
    LIMIT 5000
);

Run it until it returns zero rows. Each batch is a short transaction, so autovacuum can keep up and a failure resumes where it stopped. Log the row count per batch; a batch that suddenly takes ten times longer is the warning that the table changed under you.

Add the constraint as a validated-none index first.

CREATE UNIQUE INDEX CONCURRENTLY orders_fulfilled_at_idx ON orders (fulfilled_at);

For NOT NULL there is no CONCURRENTLY, but there is a two-step: add a CHECK (fulfilled_at IS NOT NULL) NOT VALID constraint, which is instant and only applies to new rows, then VALIDATE CONSTRAINT in its own transaction. Validation takes a SHARE UPDATE EXCLUSIVE lock — reads and writes continue.

The cheap version

If you cannot get a production-shaped staging database, at least make the migration fail loudly on the wrong size:

SET lock_timeout = '3s';
SET statement_timeout = '30s';

A migration that times out in staging and gets rewritten is infinitely cheaper than one that holds a table for six minutes. And add one assertion to the deploy: count the rows the migration expects to touch, and compare it with the count it actually touched. Eleven is a number that should have looked wrong.

Staging that cannot disagree with you is not a test. It is a syntax check with a database attached.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

The Lock You Did Not Know You Were Taking

晖莫 — Tue, 20 Oct 2026 13:00:06 +0000

GET /invoices/{id} went from 180ms p99 to 2.4s over a Tuesday afternoon, and no deploy had touched it. Nothing about the endpoint writes: it loads an invoice, checks a feature flag, and returns JSON. It was taking row locks anyway.

A read that writes

The handler had grown a helper during a previous incident:

def load_invoice_for_display(conn, invoice_id):
    row = conn.execute(
        "SELECT * FROM invoices WHERE id = %s FOR UPDATE",
        (invoice_id,),
    ).fetchone()
    if row is None:
        raise NotFound()
    # ... feature flag lookup, an outbound call to the tax service ...
    return row

Someone added FOR UPDATE because a different caller wanted a read-modify-write and this helper looked like the place to put it. The read-only caller inherited the lock and never knew.

FOR UPDATE takes a row-level exclusive lock. It blocks other FOR UPDATE, UPDATE, and DELETE on those rows, and it blocks them for the rest of the transaction. So the question that decides the damage is not "how long is this query" but "how long is this transaction".

In this case the transaction also called the tax service over HTTP, with a 2-second timeout. Every request held two row locks while waiting on a remote dependency. Under load, requests queued behind each other on the same invoice ids, which is why p99 collapsed while average latency barely moved.

Finding it

Two queries answer almost every version of this question. First, who is waiting:

SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by,
       now() - query_start AS waiting
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY waiting DESC;

Second, what is holding the lock and for how long:

SELECT l.pid, l.locktype, l.mode, l.granted,
       c.relname, a.state, now() - a.xact_start AS tx_age,
       left(a.query, 80) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE l.mode IN ('ExclusiveLock', 'RowExclusiveLock')
ORDER BY tx_age DESC;

xact_start is the column that matters. A transaction that has been open for 4 seconds is the problem, not the statement it is running right now.

What we changed

Three things, in order of impact.

The read stopped taking a lock. It did not need one. FOR UPDATE is a write lock borrowed by a read path, and the path that needed it got its own function.

The transaction stopped containing a network call. The tax service call moved outside the transaction boundary: read, commit, call, then a second short transaction if the result needed writing. A transaction should describe one unit of database work, not one unit of work in general.

The remaining read-modify-write used the weakest lock that works. For a row you are going to update but whose key you are not changing, FOR NO KEY UPDATE is enough, and it does not block foreign-key inserts that reference the row. FOR SHARE if you only need the row not to change underneath you. Take the strongest lock only when you actually need it.

The pattern to grep for

Any FOR UPDATE inside a function called from a read path. Then check the transaction boundary around it: tracing helpers, log shipping that opens a transaction, and retry decorators that wrap a whole function are the usual way a lock ends up spanning a remote call.

Two rules survived this:

A lock lives as long as the transaction, not as long as the statement. Optimising the query that takes it is treating the symptom.
If a function is called from a read path, it must not be able to write. The way to enforce that is not a comment. It is a separate connection with a read-only role for anything that claims to be a read.

The endpoint was never slow. It was waiting in line behind itself.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

The Runbook That Sent Me to a Dashboard That No Longer Exists

晖莫 — Sun, 04 Oct 2026 13:00:02 +0000

The alert fired at 02:14. Payments queue depth climbing, no consumers draining it. The runbook told me to check the "Payments Worker Health" dashboard in Grafana. It did not exist. Someone had renamed it to svc-payments-consumer; the runbook still pointed at the old name.

Fine. Annoying, but survivable. Two steps later it told me to restart the consumer with payments-worker restart --queue=main. The binary is now ledger-consumer. That command has not existed for months.

I typed it anyway. command not found. I sat at 02:19 with a paging incident and a document that had lied to me twice. Then I closed the runbook and stopped reading it.

The cost is not the two wrong steps

Here is what happened next. Everything else in the runbook — checking broker backlog before restarting anything, the DLQ inspection, the note that a stuck consumer is usually a broker problem — I skipped. I went to memory and guesswork.

I restarted the consumer. That was the wrong move. The broker had partitioned and the consumer was holding a lock it could not release. Restarting made it worse. The runbook had a section warning about exactly that. I never got to it.

That is the real failure mode. It is not that step four is stale. It is that a responder who has been lied to twice treats the remaining eight steps as suspect, including the correct ones. A bad runbook destroys the value of the good parts.

Why they rot

Runbooks get written once, right after an incident, by the person with the most context. That person already understood the system, so they wrote an index into their own head. "Check the Payments Worker Health dashboard" made sense to them, not to a stranger.

Then the system moved. Dashboards get renamed during cleanup. Commands get consolidated when a repo splits. Nobody deletes the old line, because deleting it is not part of any ticket, and whoever would notice never needs the runbook.

No bug, no alerting on documentation drift. It drifts until an incident finds out.

What makes a runbook survive

A step survives if a tired person can run it and see, without judgment, whether it worked. That means executable and verifiable:

# Is the consumer actually draining? Expect lag to fall over ~30s.
ledger-consumer status --queue payments --format json | jq '.lag_seconds'

# Broker-side backlog. If this is climbing while consumer lag is flat,
# the consumer is not the problem. Do not restart it.
rabbitmqctl list_queues name messages_ready messages_unacknowledged | grep payments

The commands can be copied and run, and each has an expected result next to it. "Expect lag to fall" tells the responder what done looks like. No interpretation required.

A link beats a description. Do not name a dashboard — paste the URL. Do not describe where the log lives — paste the query. A link that 404s is a signal you can act on. A name that quietly points at a renamed thing is a trap.

Every runbook needs a named owner and a review date at the top. Not a team. A person:

owner: @dana
reviewed: 2026-08-14
review_by: 2027-02-14

The date converts an invisible problem into a calendar item, and tells a responder how much to trust the page. A runbook reviewed four months ago is different from one reviewed three years ago.

"Contact X" is not a step

"Page the payments team" is not a step. It is an admission that the runbook has nothing to say, and it fails at the moment it matters: the named person is asleep, on a plane, or already in the incident channel asking what you tried.

Replace it with what you would ask them to do. If the answer is "ask Dana to run the replay tool," then the step is how to run the replay tool, what a good run looks like, and what to do if it fails. If Dana is genuinely the only one who can do it safely, write the threshold that makes it Dana's problem and what to do meanwhile. Escalation is a fallback, not a step.

Fix it during the incident, not after

Runbooks rot because updating them is a separate chore with its own ticket, and separate chores lose. Do not make it separate. Make the runbook part of the fix.

My rule now: the incident is not closed until the runbook the responder actually used has been corrected. Not in a follow-up. Before the postmortem, before the channel goes quiet. If you hit a stale step, fix it in the same session while the context is still in your head.

Concretely, the closing checklist becomes:

Every step you ran: did it work as written?
Every step that failed: corrected now, with a command that exists.
Any new step you improvised: added.
Owner and review date bumped.

Anyone who fought an incident has already debugged the documentation. That knowledge lasts about an hour. Spend it in the same session, on the same page. Otherwise the next responder at 02:14 gets the same broken dashboard, the same dead command, and one less reason to trust the rest of it.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

Configuration Drift Is a Production Incident With a Long Fuse

晖莫 — Sat, 03 Oct 2026 13:00:02 +0000

The checkout pods came back up after a routine node rotation and started returning 502s on every request that touched the payments path. Same image tag, same application version, same deploy log as the week before. The pods were healthy. The CPU was flat. The payment client was timing out against a provider that answered every health check we threw at it.

We restarted pods. We rolled back a deploy that turned out to be unrelated. We checked the provider status page twice. The answer was an environment variable that only existed on the nodes we had just replaced.

The change nobody wrote down

Months before that morning, the payment provider had a slow afternoon. Requests were hanging on a client timeout longer than our load balancer was willing to wait, so every slow response turned into a 502 at the edge. A deploy through the pipeline meant review, a build, and a canary. An operator with console access ran one command against the running deployment instead, raising the client timeout, and watched the error rate fall.

It worked. The graph went green. Everyone moved on to the next page. The variable lived in the running environment and nowhere else.

That is how drift accumulates. It is rarely one dramatic edit. It is a kubectl set env here, a console slider there, an env var added to a single task definition because staging had the wrong value, a cron schedule someone patched directly on the box because the repo version had a typo in it. Each change is small, each is justified in the moment, and each is invisible to anyone who was not in the room.

The bill arrives later, and it arrives somewhere else.

Why the incident makes no sense

Drift failures are confusing because every signal you normally trust is silent. The code did not change, so the deploy log is clean. The image tag did not change, so the registry tells you nothing. Nobody touched the config repo, so the diff you would normally read is empty.

What changed is the ground under the code. A node rotation, a scale-up, an instance refresh, a region failover — anything that builds new instances from the repo's version of the truth instead of the running environment's version. The old instances still carry the hotfix. The new ones do not. For a while you run a mixed fleet, which is the worst version of the problem, because two pods behind the same service will answer the same request differently.

The symptom usually looks like a hard failure with a soft explanation. A timeout that comes back from the dead. A feature flag that turns itself off. A connection pool that quietly shrinks to its original size. If you catch yourself saying "it works on the old pods," you are looking at drift.

Compare the running config to the repo

The only comparison that finds drift is running state against committed state. Documentation describes intent at the time someone wrote it, and that someone may have left the company. The repo is the source. Everything else is hearsay.

So make the running environment describe itself, and diff that description against the repo:

# Dump the live environment and diff it against the committed one.
kubectl exec deploy/payments -- env | sort > /tmp/live.env
grep -E '^[A-Z_]+=' config/payments.env | sort > /tmp/repo.env
diff -u /tmp/repo.env /tmp/live.env

Run it on a schedule, not while you are on fire. Anything present in /tmp/live.env and missing from /tmp/repo.env is a finding, and so is any value that differs. The same shape works for database parameters, feature-flag defaults, and infrastructure a console manages.

Here is the file that diff compares against, and the value that leaked out of it:

# config/payments.yaml — what the service is supposed to run with.
payment:
  clientTimeoutMs: 8000
  retries: 3
  circuitBreaker:
    enabled: true

The console edit set clientTimeoutMs to 20000. The repo still says 8000. Every pod created from the repo gets 8000, and the provider is still slow.

The rule that stops it

If it is not in the repo, it does not exist. That is the whole rule. A setting that lives only inside a running process is not configuration. It is an undocumented mutation with an expiry date, and the expiry date is the next restart.

The escape hatch is real, so write it down before you need it. Emergencies happen, and sometimes changing a value by hand at 2 a.m. is the correct call. The hatch has a price: every manual change gets a commit opened against the repo in the same shift, linked in the incident channel, with the reason in the message. The incident does not close until that commit merges. No commit, no fix.

Then drift becomes visible in the one place people actually look. Reviewers see the change. The next operator inherits the reason, not just the value. And the next node rotation deploys an environment that matches the working one, because the working one was never a secret.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

Your edge rate limit is not protecting the database

晖莫 — Fri, 02 Oct 2026 13:00:03 +0000

Our search endpoint's p99 fell off a cliff on a Tuesday afternoon. The edge dashboard was calm: request rate per IP flat, 429s near zero, WAF quiet. Postgres was logging FATAL: remaining connection slots are reserved, and every service on that database was stuck waiting for a pool connection. I had spent the morning tuning the wrong layer.

Request rate and query cost are different units

The edge counts requests per IP per second. That is a scalar. One request equals one unit, no matter what the request does.

The database counts concurrent queries and what each one costs. A request that returns a memoized ID is nearly free. A request that scans a large table with LIKE '%term%', or runs a report with several joins and an unindexed ORDER BY, holds a backend process, memory, and often a lock for seconds. A thousand cheap requests and a dozen expensive ones are the same number to the edge and completely different numbers to Postgres. Your limit can be perfectly enforced and still irrelevant, because the thing you capped is not the thing that ran out.

The mismatch is worse than it looks. The edge limit is per second. The database ceiling is concurrent connections, a fixed integer set at boot, minus the superuser reserve. Once those slots are taken, no request gets one until another finishes. That is a queue with a hard wall, and the wall is reached by duration multiplied by concurrency, not by rate.

Per-IP limits do nothing against one valid token

A per-IP rule assumes the load is spread across many addresses. The incidents I have been paged for came from one client with one valid bearer token. A mobile app retrying a failed export. An internal job fanning a report out across every tenant. A customer's integration polling a list endpoint with a filter that defeats the index.

One IP. One identity. Well under any sane per-IP threshold. Each request burning hundreds of milliseconds of database time. The edge sees a polite client. The pool sees a stampede.

Caching is the real protection for expensive reads

If a query is expensive and its inputs repeat, do not throttle it. Do not run it. A cache protects the database because it removes the work instead of declining it.

The catch is the key. A cache keyed on the URL with a good global hit ratio can still miss on every request to the one endpoint that matters, because each request carries a different combination of sort, page, and date range. Key on the normalized query and put the filter set in the key. Bound the cardinality, or you have built a slower database with worse durability.

For reads that genuinely cannot be cached, the fallback is a cheaper query: an index that matches the filter, a materialized view refreshed on a schedule, a precomputed count. Measure before guessing. Run EXPLAIN (ANALYZE, BUFFERS) with real parameters and look at actual rows and actual time. Those two numbers decide whether the answer is a cache, an index, or an admission limit.

Put the limit where the resource is

The limit belongs next to the pool, not at the edge. A semaphore in front of the connection pool caps concurrency at the exact resource that runs out.

import asyncio

DB_CONCURRENCY = 32
db_gate = asyncio.Semaphore(DB_CONCURRENCY)

async def fetch_report(pool, query, params):
    async with db_gate:                 # the real ceiling: concurrent queries
        async with pool.acquire() as conn:
            await conn.execute("SET LOCAL statement_timeout = '2s'")
            return await conn.fetch(query, params)

Two things are doing the work here. The semaphore is a ceiling on concurrent queries, not on requests per second, so it fails closed exactly when the database would. statement_timeout means a query that goes wrong dies inside Postgres instead of holding a backend forever. Set it at the transaction level so it does not leak into the next checkout of that connection.

For anything heavier, add a cost-based admission check: ask the planner for an estimate, and if the projected cost exceeds a budget, push the job to a queue or reject it with a clear error rather than starting work you cannot finish.

Then instrument the resource, not the edge. Watch pg_stat_activity for state, wait event, and query start time. A long-running query is load. A session sitting in idle in transaction is a leak, and no rate limit will fix it.

The edge limit is still worth having

None of this makes the edge rule useless. It absorbs the dumb traffic: credential stuffing, scrapers, a retry loop that never authenticates. It is cheap, it is global, and it keeps noise away from your application. Keep it.

Just stop treating it as the last line of defence. The edge limit protects the edge. The semaphore, the statement timeout, and the cache protect the database. Count the thing that runs out.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

The Retry You Added to the Write Path Made Duplicates Worse

晖莫 — Thu, 01 Oct 2026 13:00:04 +0000

The duplicate charges showed up in support tickets, both the same shape: one order, two rows in payments, same amount, same card, a few hundred milliseconds apart. The gateway logged two POSTs with different request ids and no shared key. Our retry wrapper had done exactly what we told it to.

The retry policy came from a shared HTTP client that had only ever wrapped reads. max_attempts=3, exponential backoff, retry on timeout, retry on connection errors, retry on any 5xx. Someone reused the call site for a write, and nothing in that client objected. The policy was correct for GETs and wrong for POSTs, and the code could not tell the difference.

A timeout is not a failure

A ReadTimeout means the client stopped waiting. It does not mean the server stopped working. The usual sequence is: TCP connect succeeded, request line, headers and body went out, the server started processing, the response came back late or never. The socket gave up on our side. On their side the row was already committed.

On a read path that mistake costs one extra query, so nobody notices. On a write path it costs one extra order. Same exception, same handler, different blast radius.

Before the request left, and after

These are different failures and they need different handling. Name the exception rather than catching Exception.

A ConnectTimeout or ConnectionRefusedError on a fresh connection happens before anything reached the server. Nothing was written. Retrying is reasonable, with one caveat: make sure no bytes of the request body were sent, because a ConnectionError raised after a partial send is not the same event.

A ReadTimeout, or RemoteDisconnected after the body was sent, is the unknown case. The server may have processed the request completely. It may have processed it and then failed while writing the response. There is no local signal that separates "never arrived" from "arrived and committed." The two situations collapse into one exception class, which is exactly why a blanket decorator is unsafe.

Retrying a 500 from a peer

A 500 is not evidence that nothing happened. It is evidence that something broke after the request arrived. The handler ran. The write may have committed and the error may have come from a post-commit step: sending a receipt, updating a search index, serializing a response. Retrying that POST inserts the row a second time.

A 502 or 503 from a proxy is more ambiguous. The upstream may never have been reached, or it may have received the request and answered too slowly for the proxy. Unless you own that layer and know its behavior, treat it as unknown too.

The only condition under which retrying a write is safe is idempotency: the server can distinguish a repeat from a new intent. That requires a key that travels with the original request, not one generated when the retry fires.

Make the key part of the request

# wrong: a fresh key on every attempt, so the retry looks like a new charge
def charge(order_id, amount):
    for attempt in range(3):
        key = str(uuid4())
        try:
            return gateway.post("/charges", json={"amount": amount},
                                headers={"Idempotency-Key": key})
        except (ReadTimeout, ConnectionError):
            continue

# right: the key comes from the intent and is stable across attempts
def charge(order_id, amount):
    key = "charge:%s" % order_id
    for attempt in range(3):
        try:
            return gateway.post("/charges", json={"amount": amount},
                                headers={"Idempotency-Key": key})
        except ConnectTimeout:
            continue                      # nothing was sent
        except ReadTimeout:
            raise UnknownWrite(order_id)  # arrived, outcome unknown

The second version makes the retry a repeat of the same request. The gateway sees charge:4471 again, finds its stored result, and returns that instead of taking the money twice. If the endpoint does not implement the key, no client-side change makes the retry safe.

Treat a timed-out write as unknown

The honest state after a ReadTimeout on a POST is unknown. Not failed. Code that models it as failed retries, and the retry is what creates the duplicate.

What to do instead:

Retry only writes you have made idempotent, and derive the key from the operation: order id, job id, a value you already persist. Never generate it inside the retry loop.
Classify before you retry. Retry a connect failure that provably happened before the body was sent. Do not retry a response timeout, a 500 from a peer that received the request, or an ambiguous 502 unless the operation is idempotent.
On an unknown write, stop and reconcile. Read the resource back by the key you sent, or query the server's idempotency record, before doing anything else.
Store the key on the write path so a retry after a process restart uses the same value. A key built in memory does not survive a crash.
If the endpoint has no idempotency key and you cannot add one, the only safe retry is one where no request was sent. Everything else needs a reconciliation job or a human.

To see how often this bites you, measure the timeout and 5xx rates on write endpoints separately from read endpoints, and count duplicate-key violations or duplicate rows per endpoint. A write endpoint with a nonzero timeout rate and no idempotency key is already producing duplicates. They show up as support tickets before they show up in your dashboards.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

ORDER BY Without a Tiebreaker Is a Flaky Test Generator

晖莫 — Wed, 30 Sep 2026 13:00:04 +0000

I had a test that asserted orders.first().id == 9917. It passed locally for months. Then it failed in CI, twice in a row, then passed again on a rerun. Nobody had touched the query.

The query was this:

SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 1;

The test seeded three orders for one customer inside a single transaction. All three got created_at = now(). now() is transaction_timestamp() — it does not advance during a transaction. So three rows shared the exact same timestamp to the microsecond.

ORDER BY created_at DESC gives one value. Three rows tie on it. Postgres picks whichever it happens to read first. That order depends on the plan, the physical row order, the visibility map, and whether it chose an index scan or a seq scan. Local Postgres and CI Postgres do not make the same choice.

The standard does not promise you anything here

SQL does not define which row comes back when the sort key ties. The ORDER BY clause establishes a partial order, not a total one. Two rows with equal keys are "equal" for sorting purposes, and the engine may emit them in any order. No standard text says otherwise.

This is not a Postgres bug. It is Postgres behaving correctly. The bug is in the query, and then in the test that trusted it.

LIMIT 1 makes it worse. Without the limit you would see the instability — the row order would visibly shuffle between runs. LIMIT 1 hides it and turns it into a coin flip that lands on heads in your terminal.

Pagination is where this stops being a test problem

I hit this again in a feed endpoint. Keyset pagination over created_at alone:

SELECT id, created_at, body
FROM posts
WHERE created_at < $1
ORDER BY created_at DESC
LIMIT 20;

A backfill wrote a chunk of posts with the same created_at, all from one job running now() in one transaction. The first page ended at created_at = '2026-09-14 10:03:22.481913+00'. The next page asked for everything strictly before that value. Every post sharing that timestamp got skipped. Users saw a gap in the feed and I spent an afternoon blaming the client.

Flip the comparison to <= and you get the mirror failure: the boundary rows repeat on every page. Offset pagination has the same shape. OFFSET 20 counts rows in an order the database never promised, so a row inserted or reordered between requests can push another row across the page boundary.

Add a unique column to the sort key

The fix is one line and it is not optional. Every ORDER BY that feeds a LIMIT, an OFFSET, or a cursor needs a unique tiebreaker. The primary key is right there.

ORDER BY created_at DESC, id DESC

Now the sort key is unique. (created_at, id) is a total order over the table. Ties cannot exist, so the engine has no freedom left to exercise. LIMIT 1 returns exactly one row, deterministically.

Pagination needs the same treatment in the predicate, not just the sort:

SELECT id, created_at, body
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Row-value comparison does the lexicographic work in one expression. Pass the last row's created_at and id as the cursor. Nothing is skipped, nothing repeats.

For the index, an index on (created_at DESC, id DESC) matches the sort directly. Check with EXPLAIN (ANALYZE, BUFFERS) — if you see a Sort node above the scan, the index is not matching your order and you are paying for it on every page.

Make it a rule

A tiebreaker is not a stylistic preference. It belongs in review. Two habits make it stick for me:

If I write ORDER BY, I write the unique column in the same keystroke. ORDER BY created_at DESC alone looks unfinished now.
Any test that reads .first() or .last() from a query must sort on a unique key, or it is not testing the query — it is testing the plan.

I also stopped seeding multiple rows with now() when the test cares about their order. clock_timestamp() advances; now() does not. Better yet, set explicit timestamps in the fixture so the data has the order the test claims to assert.

The failure mode is quiet. Nothing errors, no constraint fires, no log line appears. You get a wrong row, a missing row, or a green test that owes you a red one later. Add the tiebreaker.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

Blue-green deploys do not save you from a bad migration

晖莫 — Tue, 29 Sep 2026 13:00:05 +0000

The deploy went green in ninety seconds. Then the error rate on blue hit 100%, and every request touching /orders returned column "orders.status_v2" does not exist. Two healthy app stacks, no working rollback.

Blue-green only ever protected one thing

Blue-green gives you two copies of your application code and a router that flips between them. It does not give you two copies of your database, because you cannot cheaply fork a live Postgres cluster with continuous writes and merge the forks later. Both environments point at the same host, the same schema, the same tables.

So the moment migrate finishes, blue runs new code against a new schema and green runs old code against that same new schema. The swap is atomic. The schema change is not.

Here is the migration that took us down:

ALTER TABLE orders DROP COLUMN status;
ALTER TABLE orders ADD COLUMN status_v2 text NOT NULL DEFAULT 'pending';

Order matters. Green still selects status. Between the DROP and the moment green stops serving, every green query fails. Green keeps serving for as long as the router takes to drain, plus every in-flight request, plus every retry. The rollback plan said "flip back to blue." Blue was already broken by a migration that ran before the flip.

Rollback means two different things

Code rollback is a pointer change: flip the router, and the old artifact still exists. It works only while the data underneath still fits.

Schema rollback is not symmetric. The reverse of DROP COLUMN is ADD COLUMN, and the values are gone. You cannot un-drop a column, un-truncate a table, or un-run an UPDATE that rewrote rows in place. The database has one state, it moves forward, and it has no previous release to point at.

There is a third, worse case: the migration fails halfway. Postgres runs most DDL in a transaction, so a failed ALTER TABLE inside BEGIN rolls back cleanly. But a file with several statements, some CONCURRENTLY, or explicit commits between steps leaves a schema that is neither the old shape nor the new one. That is the state nobody rehearsed.

Reversible schema, irreversible data

The distinction I should have drawn before that deploy.

A schema change is reversible when old code can still run against the new schema. Adding a nullable column, an index, a table — all reversible, because old code ignores what it does not know about.

A data change is irreversible when the migration rewrites or removes values. Dropping a column, a lossy type cast, a backfill of computed values — the new shape is not derived from the old one, so no reverse migration reconstructs it.

A reversible schema change often accompanies an irreversible data change. That is the trap. ADD COLUMN status_v2 alone is safe. ADD COLUMN plus a backfill plus a DROP COLUMN in one file is a one-way door.

Expand, contract, flag

The safe path has three releases, not two.

Release one: expand. Add the new column as nullable, and dual-write in application code. Old code reads the old column and keeps working. New code writes both. Nothing breaks if you roll back, because both shapes exist.

def write_order(order):
    db.execute(
        "UPDATE orders SET status = %s, status_v2 = %s WHERE id = %s",
        (order.legacy_status, order.status, order.id),
    )

Release two: backfill in batches, outside the deploy, with a query you can stop. Backfill must be restartable. Run it in chunks, log the last processed id, and make a rerun a no-op.

Release three: switch reads to the new column behind a feature flag. The flag is the rollback. If reads break, flip the flag, not the deploy. Only after the flag has been on for a full traffic cycle do you contract — drop the old column in a boring release where the code no longer references it.

Do the flag before the drop, not after. A flag is reversible in seconds; a DROP COLUMN is not.

When the migration is already half-applied

Stop the deploy. Do not roll back the app, and do not run a second migration to "fix" the first. Both make the schema harder to reason about.

Find out what actually landed. In Postgres, read the real state instead of trusting your migration table:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

Then check whether the migration is holding a lock. pg_stat_activity and pg_locks show a blocked ACCESS EXCLUSIVE waiting on a long-running read. Kill the blocker if it is a stale report query, or your next deploy deadlocks.

Then pick a direction and commit. If the old shape still exists, disable the new code path with the flag and leave the schema alone — you are back to release one. If the old shape is gone, the only path is forward: ship code matching the current schema, patch the failing queries today, and accept that you are deploying under pressure. Restore from a backup only if you can afford to lose everything written since the migration, and measure that window first.

The lesson I keep: the database is not a deployment target. It is a shared, single-state dependency, and every migration is a change to production data. Rehearse the migration on a copy with real row counts, and time how long the lock is held.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky