Incident: 502s after a deploy (pool exhaustion, missing FK index)

A payments ledger's release 4.2 adds one read to every balance request:

SELECT sum(amount_cents) FROM ledger_entries WHERE account_id = $1;

ledger_entries was created with account_id bigint NOT NULL REFERENCES accounts (id) and a primary key on id, and has no other index. Today it holds 1,500,000 entries and it gains 120,000 a day; no account holds more than 400 of them. On the production database one query scans 4,000,000 rows a second. Each API pod serves 20 balance requests a second with a pool of 10 connections, and nothing else keeps a connection for long.

A music service's release 12.4 shows a "plays this week" badge on every player heartbeat; its count makes each heartbeat keep its database connection for 1,600 ms instead of 8 ms. Each of the 6 API pods: a pool of 10 with connectionTimeoutMillis: 2000, 25 heartbeats a second, and a liveness probe whose handler runs SELECT 1 through the same pool (periodSeconds: 10, timeoutSeconds: 1, failureThreshold: 3). Heartbeats have failed with 503s and 502s for 5 minutes, during a live concert. Every change below except ending the counts reaches the pods by replacing them, one every 30 s.

A chat service's release 7.3 adds an unread badge to every message send. A send now runs, in order:

SELECT user_id FROM sessions WHERE id = $1;
SELECT user_id FROM sessions WHERE id = $1;
INSERT INTO messages (id, room_id, author_id, body) VALUES ($1, $2, $3, $4);
SELECT count(*) FROM messages WHERE room_id = $1;

The first lookup is the login check and the second the rate limiter. messages.room_id references rooms (id); messages and sessions have only their primary keys. messages holds 200,000 rows and sessions 5,000. The on-call engineer resets pg_stat_statements, lets 500 sends run, then lists its rows by shared_blks_hit + shared_blks_read, largest first.

A ticketing service's release 5.1 adds one statement to every seat hold, run on its own after the hold is saved:

SELECT count(*) FROM holds WHERE event_id = $1;

holds.event_id references events (id), holds has only its primary key, and it holds 1,200,000 rows. The rolling update has replaced 3 of the 6 API pods; each pod has a pool of 10 and puts its name in application_name. Holds now fail with 503s. The on-call engineer groups pg_stat_activity by pod and state, and each group by wait type and statement.

In the interview you are given EXH-2930's page and asked: "Users see 502s right after a deploy.

An ExamHall release adds a proctor dashboard that shows, for each candidate in a sitting, how often they left the exam tab. Each refresh sends one statement:

SELECT a.id, (SELECT count(*) FROM focus_events f WHERE f.attempt_id = a.id) AS tab_leaves FROM attempts a WHERE a.exam_id = $1;

focus_events.attempt_id references attempts (id), and each table has only its primary key. In review, the team ran EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF) on it for ECON-201, on a copy of production. The server runs with parallel query off.

Seq Scan on attempts a (actual rows=40 loops=1)
  Filter: (exam_id = 'ECON-201'::text)
  Rows Removed by Filter: 1960
  Buffers: shared hit=76451
  SubPlan 1
    ->  Aggregate (actual rows=1 loops=40)
          Buffers: shared hit=76440
          ->  Seq Scan on focus_events f (actual rows=12 loops=40)
                Filter: (attempt_id = a.id)
                Rows Removed by Filter: 299988
                Buffers: shared hit=76440

On production one query scans 4,000,000 rows a second, and each API pod serves 2 refreshes a second.

A wallet's transfers have failed with 503s since 10 minutes after release 9.0 went out, and database CPU is 22%. The 4 API pods have pools of 10. The on-call engineer groups the API's 40 connections in pg_stat_activity by state, wait type, wait event and query:

state | wait_event_type | wait_event | query | count
idle in transaction | Client | ClientRead | SELECT sum(amount_cents) FROM ledger_entries WHERE account_id = $1 | 40