Drill 14 — The Bad Deploy

Detect. The error-rate alert with a deploy marker on the graph, which points at the release within seconds. In the database, pg_stat_activity shows hundreds of sessions with wait_event_type = 'Lock', and pg_blocking_pids() names one blocker: the migration.

Contain. In phase 1, the fastest fix is to cancel the migration: SELECT pg_cancel_backend(<pid>). The transaction rolls back, the lock is released, and the waiting queries run within a second. The deploy is aborted, which is what you want. Once it has committed (phase 2), let the rollout finish, or speed it up. Rolling back would put all 12 pods on code that can't read the table (Break 3).

Degrade. Nothing useful to switch off here: almost every request touched the locked table through the auth middleware. That's a finding in itself. The auth path should not need a row from the billing table on every request.

The budget. Phase 1: about 2.5 minutes at roughly 85% errors ≈ 2.1 full-outage minutes, plus the ramp from 14:00:00 to 14:00:40. Call it 2.5. Phase 2: falling from 40% to 0 over 6 minutes, 20% on average, ≈ 1.2 minutes. Total about 3.7 of 43.2 minutes: 9% of the month's budget in 9 minutes. At the peak burn rate of 800×, the whole month's budget would last under an hour (720 h ÷ 800 ≈ 54 min).

Prevent.

• lock_timeout on every migration session, for example SET lock_timeout = '2s'. A migration that can't get its lock quickly fails, instead of waiting at the head of the lock queue. That matters because a waiting ALTER TABLE also blocks every new query behind it. We checked this on Postgres 18: with a long SELECT holding the table, an ALTER TABLE with no lock timeout made a 1 ms read wait 3 seconds. With lock_timeout = '1s', the ALTER failed with "canceling statement due to lock timeout" and nothing queued behind it.
• Never backfill inside the DDL transaction. Add the column alone (a nullable column with no default is a catalog change, done in milliseconds), then backfill in batches of a few thousand rows, each its own short transaction.
• Make NOT NULL cheap. SET NOT NULL scans the whole table under the strongest lock. Instead, add CHECK (unit_amount_cents IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT (it takes a weaker lock that allows reads and writes), and then SET NOT NULL. On Postgres 12 and later it uses the valid constraint and skips the scan. On Postgres 18 the debug log says exactly that: "existing constraints on column … are sufficient to prove that it does not contain nulls".
• Never drop a column in the release that stops using it. That's Break 3.

Keep the short lock timeout, and retry the DDL in a loop with backoff: try for 2 s, wait 10–30 s with jitter, try again, maybe 30 times, then give up and alert. Each attempt blocks other queries for at most 2 s, which is a latency blip instead of an outage. Then deal with the cause: a 20-minute report on the primary's hottest table belongs on the replica. Raising the lock timeout is the wrong fix: it just brings back the queue of blocked requests behind the waiting ALTER.

The bug, for the record:

// v2.31 renewal (simplified)
const sub = await subscriptions.findById(id)   // the model no longer has `price`
const amount = sub.price == null               // meant: "no negotiated price"
  ? plan.listPriceCents                        // now true for every subscription
  : sub.unitAmountCents
await subscriptions.update(id, { unitAmountCents: amount })

Detect. Nothing paged, and that's the finding. Add a correctness signal next to the availability SLO: a check every 5 minutes that compares new invoices with their contracts and alerts when more than 0.1% disagree. It would have fired at about 14:08, 43 minutes before the support emails.

Contain. Stop the writes first, then the side effects. Of the three side effects, two are already out of your hands (emails sent, the data overwritten), and one isn't: the card charge at 18:00. At an average overcharge of about $240, 3,604 wrong charges would be about $865,000 taken from customers' cards, plus refunds, chargebacks and support. Holding the charge for the affected customers is the most valuable action of the whole incident.

Degrade. Plan changes are switched off for an afternoon. Customers who try see "Plan changes are temporarily unavailable" instead of a wrong price.

Recover. Breaks 3–5.

Prevent. The correctness check; side effects behind a delay and a switch (the 10-minute email delay helped nothing, because nobody was watching. A delay is only useful with a check that can stop the sending); and expand/contract, so a column the code relies on can't vanish in the same release.

Availability and latency SLOs measure whether the service answers. This service also has to answer correctly, so give it a correctness SLI for its most important output: "the share of invoices whose amount matches the contract and plan", measured by an independent check, not by the code that wrote the invoice. Target 99.99%, with a burn-rate alert like any other SLO. Add a freshness SLI for the pipelines that matter ("the renewal sweep finished by 15:00"). An independent check is a few SQL queries on a schedule. It's cheap, and it catches the incident the other SLOs can't see (SD 2 Ch 18).

Why it fails. v2.30 needs a column that is gone. The down script recreates the column but fills it from the overwritten values, so it makes the corruption permanent in both places, and it costs another 3-minute lock. It is also untested: down scripts almost never run until the worst possible day.

What to do: roll forward. v2.32 is a one-line fix: decide "negotiated" from contract_id IS NOT NULL, not from a column that no longer exists. Deploy it with plan changes still switched off, test it on a canary pod with a handful of real plan changes against staging data, then switch plan changes back on. The data repair is separate work (Break 4).

Prevent: expand / contract. Change a schema in steps, each compatible with the code before it and after it:

Enforce it in CI with a migration linter that rejects a DROP COLUMN or a RENAME in the same release as the code that stops using it, and rejects DDL without a lock_timeout. Ch 8 covers the full method, and PostgreSQL First Principles covers the locks.

Only the contract step waits, and it waits in the background: deploys 1–3 can ship the same day, and the drop is a cleanup ticket for next week. The cost is a week of writing two columns and one more small deploy. Compare that with today: 9% of the error budget, 3,604 wrong invoices emailed to customers, $865,000 nearly charged, and an afternoon of senior engineers on a restore. Additive changes (a new column, a new table) need only one step. Expand/contract is for changes that remove or rename. That's the rule to give product: "anything that deletes takes two releases".

PITR restores the cluster, never a table. Postgres replays WAL for the whole database. Restoring production to 14:03 would also throw away everything good written since: about 50 minutes of signups, payments and invoices at 3,000 requests a second. So you restore a copy on a separate server and copy back only what you need.

The steps:

1. Pick the target. Migration 0142 committed at 14:03:10. That commit dropped price, and the bad writes started after it. A target of 14:03:00 gives a copy that still has the price column with every negotiated price. Write the target down before you start. It is the decision everything else depends on.

2. Restore the base backup taken before the target (02:00) to a new server.

3. Configure recovery: restore_command to fetch WAL from the archive, recovery_target_time = '… 14:03:00', recovery_target_action = 'pause', and create an empty recovery.signal file. pause keeps the server read-only at the target, so you can check you stopped in the right place before ending recovery. We ran this on Postgres 18: the log said "starting point-in-time recovery to …", then "recovery stopping before commit of transaction …", and the paused copy held the values from before the bad update.

4. Export id, price for the 3,604 affected ids.

5. Reconcile in production, in one transaction, and only where the row still holds the bad value and nobody else has changed it since:

   sqlCopy

   UPDATE subscriptions s
   SET unit_amount_cents = round(r.old_price * 100)
   FROM repair_0142 r, plans p
   WHERE s.id = r.id
     AND p.id = s.plan_id
     AND s.unit_amount_cents = p.list_price_cents        -- still the bad value
     AND NOT EXISTS (
       SELECT 1 FROM subscription_audit a
       WHERE a.row_id = s.id
         AND a.changed_at > '2026-10-20 14:03:10+00'
         AND a.app_version <> 'v2.31'
     );
   

   The 37 rows a person changed after 14:03 are not touched. They go to the account managers with both values.

6. Fix what the data produced. Void the 3,604 wrong invoices and issue corrected ones, rather than editing invoices customers already have (accounting needs the trail), and send one clear correction email. Then release the held charges.

Recovery time. About 4 hours from start to fixed data, against a runbook that says 1. The 18:00 charge had to run before the restore finished, which is why you held only the affected customers and let everyone else's go through.

Make it faster next time:

• A delayed replica: a replica with recovery_min_apply_delay = '4h' is always 4 hours behind. At 14:51 it was at about 10:51, before the bug. Pause it with pg_wal_replay_pause() and read the old values within minutes, no restore needed. It also catches a DELETE without a WHERE, if you notice within 4 hours.
• More frequent base backups: every 6 hours means at most 6 hours of WAL to replay.
• Keep old values in the audit table for money columns: then this repair is a query, not a restore.

A logical dump is a good second tool, but it's a snapshot from 02:00. Any negotiated price changed between 02:00 and 14:03 would be restored to its morning value, and you'd need the audit table to find those rows (and the morning value would be wrong for them). Dumps also get slow and heavy as the database grows: a full pg_dump of 400 GB every night competes with production for I/O. PITR gives you any second within the retention window. The dump gives you a second, independent copy in a format you can read without Postgres internals. Have both, and use the dump for the case PITR can't cover: a broken archive. That's Break 5.

What Postgres did. Since version 13, if recovery runs out of WAL before reaching the configured target, Postgres stops with "recovery ended before configured recovery target was reached" instead of quietly opening the database at an earlier point. We reproduced it on Postgres 18 by deleting one archived WAL file from the middle of a backup's archive: replay stopped at the gap with exactly that error. We also reproduced the cause: with archive_command = 'exit 0', pg_stat_archiver.archived_count went up and the file never reached the archive. Postgres trusts the exit code, and only the exit code.

Recover tonight. You can still get a copy as of 09:14. Restart the restore server with no recovery target: it replays to the end of the WAL it has and opens there. (We checked this too: the same server that failed came up as a normal read-write database at the end of the available WAL.) Then:

• For the 3,571 affected subscriptions that the audit table shows were not changed between 09:14 and 14:03, the 09:14 value is the right one. Restore those.
• For the 33 that were changed in that window, use last month's invoice, the signed contract in the CRM, and the account managers. That's a few hours of people's time, not a technical problem.

The other ways an untested restore fails (say several, it shows you've done this):

Prevent.

• An automated restore drill, weekly at least: restore the latest base backup to a scratch server, replay to "now minus one hour", run checks (row counts against production, the newest updated_at close to the target, a few known rows), record how long it took, and throw the server away. Alert if it fails or takes longer than your RTO. Untested backups are a hope, not a plan.
• An archive command that fails when the upload fails. Postgres then keeps the WAL file and retries. The cost is that pg_wal grows if archiving stays broken, so alert on pg_stat_archiver.failed_count, on last_archived_time older than a few minutes, and on disk usage. Better still, use a maintained tool such as pgBackRest or WAL-G, or your managed service's built-in PITR, instead of a home-grown script.
• A continuity checker that lists the archive every hour and alerts on any missing WAL file name.
• State RPO and RTO, and measure them. RPO for PITR is how far behind the archive is: with archive_timeout = 60s, about a minute. RTO is whatever the last drill took, not what the runbook says.

The trade-off: a weekly 400 GB restore costs a server for a few hours a week and some engineering to automate. Today it would have caught the gap at 09:20 instead of 18:05, and would have told you the real RTO was about 4 hours before anyone promised 1.

An action item changes the system when it removes a way to fail, not when it asks people to be more careful. "Be careful with migrations" changes nothing.

Write the timeline without blame. The engineer who wrote sub.price == null made a reasonable choice for the schema they had, and the system let a column vanish under them (Debugging 15).