How databases actually go down

Reliable means a number

“The database should be reliable” cannot be checked. A target can. Site reliability engineering uses three words for this:

• SLI, a service level indicator: something you measure, such as the share of queries that finish under 100 ms.
• SLO, a service level objective: the target for it, such as 99.9% of them over 30 days.
• SLA, a service level agreement: a contract with consequences if you miss.

For a database, four indicators cover most of what users feel:

The SRE book is blunt that 100% is the wrong target: it is unrealistic, and chasing it stops you from ever changing anything. The gap between the target and 100% is an error budget: the amount of failure you can spend on deploys, migrations and experiments.

RPO and RTO are the two people forget until they are needed. They are promises about backups and failover, and a promise you have never tested is a guess.

Most incidents are self-inflicted

Disks and servers do fail, and replication and backups exist for that. But the incidents a team actually lives through are mostly the database doing exactly what it was asked:

• A migration waits for a lock, and every query behind it waits too.
• A forgotten transaction stays open for hours, and vacuum cannot clean up anything newer than it.
• Connections run out because each app instance opened its own pool.
• A runaway query reads a whole table every few seconds.

None of these crash PostgreSQL. They make it unavailable to the application while it is perfectly healthy.

The lock queue

Locks are granted in order. A plain SELECT takes the weakest table lock, ACCESS SHARE, which conflicts with only one mode: ACCESS EXCLUSIVE, the lock many forms of ALTER TABLE take. So a migration that adds a column needs, for a moment, the table to itself.

 pid   | query                                    | wait_event | blocked_by
-------+------------------------------------------+------------+-----------
 64160 | BEGIN; SELECT count(*) FROM orders; ...  | PgSleep    | {}
 64196 | ALTER TABLE orders ADD COLUMN note text; | relation   | {64160}
 64277 | SELECT count(*) FROM orders;             | relation   | {64196}
 64279 | SELECT count(*) FROM orders;             | relation   | {64196}
 ...three more, all blocked by 64196

The guards, all off by default

PostgreSQL has a timeout for each of these failures. Every one defaults to zero, meaning “wait forever”:

The documentation recommends against setting most of these in postgresql.conf, because they would apply to every session, including the backup and the maintenance jobs that legitimately run for hours. Set them per role instead, where each kind of work has a sensible limit:

ALTER ROLE app        SET statement_timeout = '5s';
ALTER ROLE app        SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE migrations SET lock_timeout = '2s';
ALTER ROLE reporting  SET statement_timeout = '5min';

Each new session of that role starts with those values. A migration with lock_timeout = '2s' fails after two seconds instead of holding the queue for as long as the slowest query; it can then be retried. You will measure the difference in the lab.

Check yourself

1. Why is “99.99% available” not by itself a complete database reliability target?

   Show answer

   It says nothing about data loss or recovery. A database can be available almost all the time and still lose six hours of data in a disaster. RPO and RTO cover that, and they only mean something if restores and failovers are tested.

   I could explain this without looking

2. In the lock queue experiment, which session actually blocked the five reads, and why?

   Show answer

   The migration. It was waiting for an ACCESS EXCLUSIVE lock, which conflicts with the reads’ ACCESS SHARE lock, and it was ahead of them in the queue. The report alone would not have blocked them.

   I could explain this without looking

3. Why set statement_timeout with ALTER ROLE rather than in postgresql.conf?

   Show answer

   A server-wide value applies to every session, including backups, vacuum jobs and reports that legitimately run long. Per-role values let the application have a tight limit while other kinds of work get limits that suit them.

   I could explain this without looking