Lab: cause four incidents and contain them

Before you start

You need a PostgreSQL you can throw away, the shop practice database loaded, and three terminals open on it, called A, B and C below. Use a superuser for A, B and C (on a local install, the user you created the database with). Step 3 opens about a hundred connections from a shell loop.

Create a role to play the application:

CREATE ROLE app LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;

Our numbers come from PostgreSQL 18.3 on an Apple M1 Pro laptop. The waits you see will depend on how quickly you type; the shape of each result should match ours.

1. The lock queue

Terminal A, a long report that has not finished:

BEGIN;
SELECT count(*) FROM orders;
-- leave this transaction open

Terminal B, the migration:

ALTER TABLE orders ADD COLUMN note text;
-- hangs

Terminal C, an ordinary application query:

SELECT count(*) FROM orders;
-- also hangs

Now open a fourth terminal (or cancel C with Ctrl-C and reuse it) and find out who is waiting for whom:

SELECT pid, state, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by,
       now() - query_start AS waiting, left(query, 40) AS query
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid()
ORDER BY query_start;

Read the blocked_by column as a chain: the reads are blocked by the migration, the migration by the report. In our scripted run, five reads arrived one second after the migration and each waited 17.9 seconds, until the report’s transaction ended. The migration itself then took under a millisecond.

Run COMMIT; in A. Everything finishes at once. Drop the column again (ALTER TABLE orders DROP COLUMN note;) and repeat the step, but this time start terminal B with:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;

ERROR:  canceling statement due to lock timeout

With the timeout, the migration gave up after two seconds and the reads it had been holding ran immediately. The migration failed, which is the point: a failed migration can be retried a minute later; a queue of blocked checkouts cannot be undone. Run COMMIT; in A when you are done.

2. The forgotten transaction

Make a table worth vacuuming:

CREATE TABLE events (id bigint PRIMARY KEY, payload text);
INSERT INTO events SELECT g, repeat('x', 100) FROM generate_series(1, 1000000) g;
VACUUM ANALYZE events;

Terminal A, a transaction that wrote something and then was forgotten, as when application code returns early without committing:

BEGIN;
UPDATE customers SET city = city WHERE id = 1;
-- leave it open and do nothing

Terminal B, normal cleanup:

DELETE FROM events WHERE id <= 500000;
VACUUM (VERBOSE) events;

Find the line starting tuples:. Ours:

tuples: 0 removed, 999982 remain, 500000 are dead but not yet removable

Vacuum removed nothing. The open transaction in A started before the delete, so as far as PostgreSQL knows it might still need to see those rows. Find the culprit:

SELECT pid, state, backend_xid, now() - xact_start AS open_for, left(query, 40) AS last_query
FROM pg_stat_activity WHERE state = 'idle in transaction';

last_query is the last statement it ran, not something it is running: an idle transaction is doing nothing at all. Run COMMIT; in A, then vacuum again in B:

tuples: 500000 removed, 250002 remain, 0 are dead but not yet removable

On a real system the transaction is not in a terminal you control. It is in an application server, maybe for hours, while every table in the database accumulates dead rows. Now contain it. In B:

ALTER ROLE app SET idle_in_transaction_session_timeout = '5s';

Connect a new terminal as app (psql -U app -d shop), start the same forgotten transaction, wait ten seconds and run anything:

FATAL:  terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly

The session was ended, its transaction rolled back, and its hold on vacuum released.

3. Connections run out

Check the limits:

SHOW max_connections;                -- ours: 100
SHOW superuser_reserved_connections; -- ours: 3

From a shell, open 97 sleeping connections as app. That is every slot not reserved for superusers, minus one for the superuser session you will check with:

for i in $(seq 1 97); do psql -U app -d shop -qc "SELECT pg_sleep(30)" >/dev/null 2>&1 & done

While they sleep:

psql -U app -d shop -c "SELECT 1"

FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

psql -d shop -c "SELECT 'superuser still gets in'"   # works

The three reserved slots are what let an administrator connect, look at pg_stat_activity and end sessions during exactly this kind of incident. If your monitoring, backups or on-call tools connect as ordinary roles, they are locked out with the application. PostgreSQL 16 added reserved_connections, which keeps more slots for roles granted pg_use_reserved_connections; give that role to monitoring, not to the application.

4. The runaway query

Give the application a statement limit and run something no application should:

ALTER ROLE app SET statement_timeout = '2s';

As app:

SELECT count(*) FROM events a JOIN events b ON a.payload = b.payload;

ERROR:  canceling statement due to statement timeout
Time: 2021.037 ms

Without the limit, this join of 500,000 identical payloads against each other would run until someone noticed.

What you measured

• Five trivial reads waited 18 seconds behind a migration that needed under a millisecond. With lock_timeout, they waited one.
• One idle transaction stopped vacuum from removing any of 500,000 dead rows.
• At 97 application connections, the 98th was refused while a superuser could still get in.
• Each guard turned an open-ended incident into one clear error.

Clean up

ALTER ROLE app RESET ALL;
DROP TABLE IF EXISTS events;
ALTER TABLE orders DROP COLUMN IF EXISTS note;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM app;
DROP ROLE app;

Check yourself

1. In step 1, pg_blocking_pids listed the migration as the blocker of the reads, although the migration held no lock. Why?

   Show answer

   It was waiting for an ACCESS EXCLUSIVE lock that conflicts with the reads’ lock, and it was ahead of them in the queue. PostgreSQL counts queued conflicting requests as blockers (a soft block), not just locks already held.

   I could explain this without looking

2. Vacuum reported 500,000 rows “dead but not yet removable”. What is the first query you run, and what are you looking for?

   Show answer

   pg_stat_activity, looking for sessions in idle in transaction or with an old xact_start and a non-null backend_xid or backend_xmin: the oldest open transaction is what holds cleanup back.

   I could explain this without looking

3. Why should monitoring not connect with the same role as the application?

   Show answer

   When the application uses up every ordinary connection slot, monitoring would be refused at the moment it is needed most. A separate role with reserved slots can still connect.

   I could explain this without looking