The engine in production

The log that every change is written to before the data. This lesson is about everything a real server builds on that log: the replica that reads it, the archive that keeps it, and the slot that holds it.

Before you start. WAL & Recovery showed the write-ahead log: every change goes into an append-only log before it touches a data page, so a crash can be replayed. The Database That Won't Answer showed how to find the query that is hurting a sick server. This lesson is the other half of an on-call week: the server around the engine, which can hurt you without a single slow query. Replication is told in general in Replication, and restores in The Restore Nobody Tested; here they are measured on Postgres.

The idea in 30 seconds

Postgres runs one process per connection, with a fixed ceiling, max_connections. Past it, new clients are refused, not queued. A pooler such as PgBouncer shares a few real connections among many clients, and transaction pooling gives up session features to do it.
A streaming replica replays the primary’s write-ahead log and can serve reads, but it runs behind. A read from it can be stale, and the lag is yours to monitor.
A replication slot makes the primary keep the log a replica needs. An abandoned slot keeps it forever and can fill the disk. max_slot_wal_keep_size caps the log it can hold and invalidates the slot instead of the server.
A replica copies mistakes as faithfully as data. A backup needs a base backup plus an archive of the log, and a restore you have actually tried.
GitLab on 31 January 2017 had a lagging replica, a deleted primary and no log archive, and recovered from a six-hour-old snapshot.

Read the story · Skip to the numbers