A production reference architecture · PostgreSQL

This is where the course arrives. Not a template to copy — a design where every choice traces to something measured, so you can defend it or change it deliberately.

The design

                    ┌──────────────┐
   clients ────────▶│    proxy     │◀──── asks the HA stack who is primary
                    └──────┬───────┘
                           │
              ┌────────────┼────────────┐
              ▼            ▼            ▼
        ┌──────────┐ ┌──────────┐ ┌──────────┐
        │ PRIMARY  │ │ STANDBY  │ │ STANDBY  │
        │ + agent  │ │ + agent  │ │ + agent  │  reporting replica,
        └────┬─────┘ └──────────┘ └──────────┘  tuned differently
             │            ▲            ▲
             │   streaming replication, slots capped
             │
             ├──▶ WAL ARCHIVE  ──▶  separate failure domain
             └──▶ BASE BACKUPS ──▶  separate account, immutable

        consensus store: 3 nodes, 3 failure domains, NOT on the DB hosts

Each component, and the failure it addresses

Component	Addresses	Measured in
Data checksums on	Silent corruption	XVIII-06: `CORRUPTX` returned with no error
`pg_wal` on its own filesystem	One filesystem filling taking the rest	XVII-02: PANIC, then recovery failing
Local low-latency storage for WAL	Commit ceiling	VI-01: 854 µs fsync → 842 tps
Two standbys, `ANY 1` if synchronous	Host failure without an availability liability	XIV-05: one sync standby blocks all commits
`max_slot_wal_keep_size` set	Unbounded `pg_wal` from an abandoned slot	XIV-06: 169 MB pinned; slot invalidated
WAL archive and slots	A standby that fell too far behind	XIV-03
Backups in another failure domain	Everything replication cannot	XV-08
Consensus store off the DB hosts	A store that cannot arbitrate its own failure	XV-04
Proxy asking the HA stack	Routing ahead of fencing	XV-06
Connection pooler	Connection cost and count	IV-01, IV-06
`pg_stat_statements` preloaded	Cannot be added during an incident	XVI-05
`lock_timeout` on the migration role	A waiting `ALTER` blocking everything	XVII-04
`idle_in_transaction_session_timeout`	Held snapshots pinning the vacuum horizon	VII-05: 1.7 MB → 19 MB

Configuration, with the reasoning

# Memory — lesson XI-01's arithmetic, against the cgroup limit if
# containerised (lesson XVIII-02: free reports the host)
shared_buffers = <25% of RAM, then measured>   # XI-02's usage counts
work_mem = <small; it is per sort node>        # XI-03: bigger was slower
maintenance_work_mem = <generous>              # XI-04

# Durability — non-negotiable
fsync = on
synchronous_commit = on                        # or per-transaction, XIV-05
full_page_writes = on
data_checksums = on                            # XVIII-06

# WAL
wal_level = replica
max_wal_size = <so checkpoints are timed, not requested>   # XII-04
archive_mode = on
archive_command = '<returns non-zero on failure, refuses overwrite>'  # XIII-05
max_slot_wal_keep_size = <finite>              # XIV-06
wal_log_hints = on                             # XV-07: pg_rewind
summarize_wal = on                             # XIII-04: incremental backup

# Autovacuum — per table where measured, not globally
autovacuum = on                                # never off. VIII-04
autovacuum_vacuum_cost_limit = <raised>        # VIII-03

# Observability
shared_preload_libraries = 'pg_stat_statements'
log_line_prefix = '%m [%p] %q%u@%d/%a '        # XVI-01
log_min_duration_statement = '1s'
log_lock_waits = on
log_temp_files = 0
log_checkpoints = on

Operational practice

Daily: backups run and are verified; pg_stat_archiver clean; replication connected and within lag budget; slots healthy; checksum_failures zero.

Weekly: size and growth recorded (XVII-01); bloat measured (VIII-06); pg_stat_statements reviewed (XVI-05).

Monthly: a restore test, timed, into a scratch cluster (XIII-08), with amcheck run against the restored copy while its buffer pool is cold (XVIII-05).

Quarterly: a failover rehearsal with real traffic, through to the rejoin (XV-07); minor version upgrades applied (XVII-05).

Annually: a major version upgrade rehearsed on a restored copy (XVII-08); RPO and RTO re-measured and written down with the date (XV-08).

Scaling this down

Most of it is not about scale.

A single small database still needs: checksums, backups off the host, a tested restore, pg_stat_statements, lock_timeout, and idle_in_transaction_session_timeout. All of those are free.

Drop first if you must: the standbys and the HA stack. They cost the most and address the failure that is rarest. A documented manual failover from a warm standby, or a restore, is a legitimate answer — lesson XV-01 made that argument.

Never drop: backups in a separate failure domain, a tested restore, and checksums. Those address the failures that end companies rather than afternoons.

The ordering matters more than the completeness. A team with tested backups and no HA recovers from anything slowly. A team with HA and untested backups recovers from host failure instantly and from a DROP TABLE not at all.

What this design does not cover

Stated plainly, because a reference architecture that implies completeness is worse than none:

Application behaviour. Almost every failure measured in this course originated above the database.
Sharding or horizontal write scaling. One primary takes all writes.
Multi-region active-active. Not addressed, and not simple.
Regulatory requirements for encryption, audit and residency.
Cost. Every component has one.

The habits, rather than the architecture

The diagram will be wrong for someone. The reasoning behind it transfers, and it comes down to a small number of habits that this course has been arguing for throughout.

Measure rather than assume. Every surprising result here came from measuring something that “everyone knows”: raising work_mem made a sort slower, SET STORAGE EXTERNAL cost 83× the space and was slower on every access pattern, a slot created the ordinary way retained nothing, and an amcheck passed on a corrupt table. None of those were predictable from documentation.

Prefer the instrument that could have disagreed. The strongest diagnosis in this course was pg_stat_statements saying one UPDATE was 85% of execution time while wait events independently showed eleven of sixteen backends queued on that same row lock. Two instruments, one conclusion, either of which could have contradicted the other.

Know what your database does when it fails, because that is the knowledge you cannot acquire during the failure. A PANIC on a full pg_wal that then cannot restart, a promotion that creates a second primary, a checksum error that refuses to answer — every one of those was produced deliberately here so it would not have to be met for the first time in production.

Test the recovery, not the backup. The distinction between a backup job exiting zero and a restore that produced a working database is the single most consequential idea in Part XIII, and it generalises: the failover you have not rehearsed, the runbook nobody has followed, the alert nobody has seen fire.

Say what you do not know. This course marked several things NOT DEMONSTRATED rather than describing them as measured — JIT, an OOM kill, --link upgrade timing, a Kubernetes cluster. An operations document that distinguishes what was verified from what was reasoned is one you can act on; one that does not is one you have to re-verify entirely.

Those habits are portable to PostgreSQL 19, to a different database, and to systems that are not databases at all. The architecture is a snapshot of what they produced against one set of measurements on one day.

What to take from this

Every component here addresses a failure measured earlier in this course, and can be traced to it.
Every value in the configuration is a placeholder. The settings are defensible; the numbers must be measured on your system.
Operational practice is daily checks, monthly restore tests, quarterly failover rehearsals, annual upgrade rehearsals.
Scaling down: drop the HA stack before the backups. Never drop checksums, off-host backups, or a tested restore.
The design does not cover application behaviour, write scaling, active-active, compliance or cost.
The habits transfer even when the architecture does not.

Cross-course references

Linux for Production Sysadmins — Part LI (Linux fleet architecture) and Part LXXXIII (Operational documentation) cover writing an architecture down in a form somebody else can operate.
Observability for Production Sysadmins — Part CXIV (Final production reference architecture) covers the monitoring half of the same estate.
Git, CI/CD & GitOps — Part CVII (Production infrastructure delivery architecture) covers how changes reach it.