Query forensics

This query ran in 40 ms for a year. This morning it takes 90 seconds. Nothing was deployed. Explain.

The plan flipped, and the new data distribution is why.

The planner estimates how many rows match tenant_id = $1 using column statistics — n_distinct and the most-common-values list in pg_stats. Two mechanisms can produce this:

1. Stale statistics. The new tenant's rows arrived faster than autovacuum's analyse threshold, so the planner still believes tenants are uniformly distributed. It estimates a small number of matching rows, chooses a nested loop or an index scan with a filter that it expects to satisfy quickly, and then has to walk a vastly larger set than it planned for.
2. Correct statistics, bad plan for this parameter. Once the big tenant is in the MCV list, the planner knows that tenant is 60% of the table — so for that parameter it may reasonably decide a sequential scan is cheaper. Meanwhile for every small tenant the index is right. With a prepared statement, Postgres may build a generic plan after five executions and reuse it for all parameter values, so one plan is now serving two wildly different distributions.

The diagnosis path, in order:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;      -- compare estimated vs actual rows
SELECT n_distinct, most_common_vals, most_common_freqs
  FROM pg_stats WHERE tablename='events' AND attname='tenant_id';
SELECT last_analyze, last_autoanalyze, n_live_tup, n_mod_since_analyze
  FROM pg_stat_user_tables WHERE relname='events';

An estimate/actual ratio above about 100x is the tell for a statistics problem. If ANALYZE events fixes it immediately, that was the cause, and the durable fix is to raise the statistics target on that column (ALTER TABLE events ALTER COLUMN tenant_id SET STATISTICS 1000) and lower the autoanalyze scale factor for this table so a fast-growing tenant triggers a re-analyse sooner.

If the plan is generic-vs-custom, the fixes are plan_cache_mode = force_custom_plan for that workload, or not using a prepared statement for this query.

Someone added an index to speed up a query. The query got slower and writes fell off a cliff. How?

Several mechanisms, and a good answer names more than one:

• The planner now chooses the new index and it is worse. An index scan that matches many rows costs one random heap fetch per row; a sequential scan reads pages in order. Above roughly 5-10% selectivity, the sequential scan wins, and a newly-available index can tempt the planner into the wrong choice — particularly with bad statistics or a low random_page_cost that misrepresents your storage.
• Write amplification. Every INSERT/UPDATE/DELETE must maintain every index on the table. Going from four indexes to five is a 25% increase in write work on that table, plus WAL volume, plus more pages dirtied per checkpoint.
• HOT updates are now disabled. Postgres can do a cheap "heap-only tuple" update — no index maintenance at all — if no indexed column changed. Adding an index on a frequently-updated column (status, updated_at) silently switches those updates from cheap to expensive, and increases bloat because each update now creates index entries that must be vacuumed. This is the answer that impresses, because it explains a large write regression from one small index.
• Cache eviction. The new index competes for shared_buffers. If the working set no longer fits, previously-cached pages get evicted and unrelated queries start doing I/O.
• The index is never used at all — it duplicates the prefix of an existing composite index, so it costs writes and memory and returns nothing.

Two transactions both do SELECT ... FOR UPDATE and you still get a lost update. How?

SELECT ... FOR UPDATE locks the rows that the query returned. It does not lock rows that do not exist yet, it does not lock rows the query did not match, and it does nothing at all if you read outside the transaction. The realistic causes:

1. The read happened before the transaction, or outside it. The classic ORM shape: obj = Model.objects.get(pk=1) (no transaction), then with atomic(): obj.count += 1; obj.save(). The lock, if any, was taken after the stale read. The value written is based on data read before anyone was locked out.
2. A phantom. Transaction A locks all rows matching WHERE status='pending'. Transaction B inserts a new pending row — which A's lock cannot cover, because you cannot lock a row that does not exist. Under Read Committed this is permitted. Preventing it needs SERIALIZABLE, or a lock on something that does exist (a parent row, or an advisory lock keyed on the predicate).
3. Write skew. Two transactions lock and read different rows, each verifies an invariant that spans both, and both commit. Nothing was concurrently modified, no lock was violated, and the invariant is now broken. FOR UPDATE cannot help because the transactions never touch the same row. This is the anomaly that only SERIALIZABLE prevents.
4. FOR UPDATE on the wrong query. Locking the parent while updating the child, or a FOR UPDATE on a query whose JOIN means the lock lands on a different table than you think — FOR UPDATE OF orders exists for exactly this reason.
5. The update path bypasses the lock. A bulk UPDATE ... WHERE, an admin script, or a different service writing to the same table without participating in the protocol. A lock is a convention: it only works if every writer follows it.

An ALTER TABLE that should have been instant took the site down for eleven minutes. Walk me through what happened.

The ADD COLUMN ... DEFAULT itself is not the problem: since Postgres 11 a non-volatile default is stored in the catalog and does not rewrite the table. The eleven minutes came from lock queuing:

1. ALTER TABLE requires an ACCESS EXCLUSIVE lock.
2. Some long-running transaction held a lock on orders — an analytics SELECT, an idle-in-transaction session left open by a console, or a long pg_dump. Even a plain SELECT holds ACCESS SHARE, which conflicts with ACCESS EXCLUSIVE.
3. The ALTER therefore waits. And because Postgres's lock queue is ordered, every subsequent query on orders — including trivial reads — queues behind the waiting ALTER. One slow reader plus one DDL statement equals a total outage on that table.
4. When the long transaction finally ended, the ALTER ran in milliseconds and everything drained. Which is why the migration log shows a fast statement and the graphs show eleven minutes of errors.

The fixes are procedural as much as technical:

SET lock_timeout = '2s';          -- fail fast rather than queue behind a reader
SET statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN region text;                 -- nullable, no default
-- backfill in batches, then:
ALTER TABLE orders ALTER COLUMN region SET DEFAULT 'unknown';
ALTER TABLE orders ADD CONSTRAINT region_nn CHECK (region IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT region_nn;          -- takes only SHARE UPDATE EXCLUSIVE

lock_timeout is the single most valuable line: with it, the migration fails harmlessly in two seconds and you retry, instead of taking the table hostage. Add a retry loop and you have a migration that is safe to run at any hour.

Your reporting endpoint does one query per row. The developer says the ORM is the problem. Is it?

Usually the ORM is not the problem; lazy loading is a default, and defaults are chosen for the common case. The N+1 is a symptom of a query written without regard for the access pattern, and every ORM offers the tools to fix it (select_related/prefetch_related, joinedload/selectinload, includes, with).

But be fair, because sometimes it genuinely is the ORM:

• A polymorphic or conditional relationship where the related table depends on a column value; eager loading cannot express it, and you need a manual two-phase fetch.
• Aggregates across several one-to-many relationships, where the naive annotation multiplies rows and inflates the counts. The correct SQL is a lateral join or a subquery per aggregate — often easier to write by hand.
• Recursive structures (a category tree, a comment thread). The ORM cannot express a recursive CTE, and looping in Python is O(depth) queries.
• A report that is fundamentally analytical. Grouping, window functions and rollups over millions of rows are what SQL is for. Building it out of objects means instantiating a million Python objects to compute one number.

The senior position: the ORM is for the object graph, raw SQL is for set operations, and mixing them deliberately is not a failure. Write the report query in SQL, put it in a named, tested function in the data layer, and return plain rows or dataclasses rather than model instances.

You need to add a column, backfill 300M rows, and make it non-null, with zero downtime. Give me the plan.

The constraint that shapes everything: during a rolling deploy, old and new application code run simultaneously against one schema. So every intermediate state must be valid for both.

Release 1 — expand.

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN region text;      -- nullable, catalog-only, instant

Deploy code that writes the new column (dual-write: set it on every insert and update) but does not read it and does not require it. Old code that knows nothing about region still works, because the column is nullable.

Release 2 — backfill. A batched, resumable, idempotent job:

UPDATE orders SET region = derive(...)
WHERE region IS NULL AND id BETWEEN $1 AND $1 + 10000;

Keyed on the primary key, committed per batch, with a sleep between batches to let replication catch up and autovacuum keep pace. Never one giant UPDATE: it takes a long transaction, bloats the table by 300M dead tuples at once, blocks vacuum, and blows out replication lag. Track progress so it can resume after a failure, and monitor replica lag as the throttle signal.

Release 3 — constrain.

ALTER TABLE orders ADD CONSTRAINT orders_region_nn CHECK (region IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_nn;   -- scans, but only SHARE UPDATE EXCLUSIVE

NOT VALID applies the check to new rows immediately without scanning; VALIDATE then scans without blocking writes. (Postgres 12+ can convert a validated check into a real NOT NULL cheaply.)

Release 4 — read, then contract. Switch reads to the new column, verify, then in a later release remove the dual-write and any old column. Never in the same deploy as the read switch — you need the ability to roll back to the previous release at every step.