Reference architectures, RPO and RTO
A production PostgreSQL design is a set of answers to two numbers: how much data you can lose (RPO) and how long you can be down (RTO). Synchronous replicas, WAL archiving, poolers and multiple regions each buy a better number at a cost.
- Map RPO and RTO targets to concrete PostgreSQL features
- Describe the standard single-region and multi-region architectures
- Check the durability settings of a running server
conceptThe concept
| Architecture | RPO | RTO |
|---|---|---|
| Single server, nightly pg_dump | Up to 24 hours | Hours |
| Single server, base backups and continuous WAL archiving (PITR) | Seconds to minutes | Restore time: minutes to hours |
| Primary and async standby with automatic failover | Seconds (unreplicated WAL at failover) | Under a minute |
| Primary, synchronous standby, async replicas, failover manager | Zero for committed transactions | Under a minute |
| Second region with async standby cluster | Seconds to minutes on region loss | Minutes, often a manual decision |
The standard single-region production layout: three nodes across availability zones managed by Patroni or a Kubernetes operator (CloudNativePG, Crunchy PGO, Percona, Zalando), one synchronous standby (ANY 1 of two), PgBouncer in front, base backups and WAL archived to object storage, and a separate restore test. Every architecture still needs backups: replication copies mistakes such as a DROP TABLE instantly.
Active-active multi-writer PostgreSQL (logical replication in both directions) exists, but conflict handling is the application's problem. Most teams run active-passive across regions and route writes to one region.
whyWhy it matters
Without written RPO and RTO targets, teams either over-build (synchronous cross-region commits that double write latency) or under-build (no tested restore). The targets turn a vague "highly available" into specific settings you can verify.
Use caseReal-world scenario
A payments service needs RPO 0 and RTO under 60 seconds in-region, and RPO under 5 minutes on region loss. It runs three zones with one synchronous standby, async replication to a standby cluster in a second region, WAL archiving in both regions, and a monthly restore drill.
Try itExamples
Run and edit these in the interactive lesson.
SELECT name, setting
FROM pg_settings
WHERE name IN ('synchronous_commit', 'synchronous_standby_names', 'wal_level', 'archive_mode',
'max_wal_senders', 'full_page_writes', 'fsync', 'data_checksums')
ORDER BY name;BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO events (customer_id, kind, payload, at)
VALUES (42, 'page_view', '{"path": "/"}', now());
SHOW synchronous_commit;
ROLLBACK;apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: shop
spec:
instances: 3
postgresql:
synchronous:
method: any
number: 1
storage:
size: 200Gi
# plus: PgBouncer via a Pooler resource, and backups with
# base backups and WAL archiving to object storageinternalsUnder the hood
synchronous_commit sets what COMMIT waits for: local (WAL flushed on the primary), remote_write (received by the synchronous standby), on (flushed on the standby), remote_apply (replayed on the standby, visible to its readers). It can be set per transaction, so non-critical writes can opt out of the wait.
Avoid / preferDo this, not that
-- "we have a replica, so we do not need backups"
-- replicas for availability, PITR backups for mistakes, -- and a scheduled restore test to prove both
A DROP TABLE or a bad UPDATE replicates in milliseconds. Only point-in-time recovery from backups undoes it.
synchronous_standby_names = 'standby_in_other_region'
synchronous_standby_names = 'ANY 1 (zone_b, zone_c)' -- cross-region standby stays asynchronous
A synchronous standby in another region adds the round trip to every commit, and if it is the only one, the primary stops accepting commits when the link fails.
PitfallsCommon mistakes
- Setting a single synchronous standby, so losing that one node blocks all commits on the primary.
- Never testing a restore; the first restore attempt happens during the incident.
- Failing over to another region automatically on a short network blip, causing split-brain risk and data loss.
LimitsWhen not to use it
A small internal tool with a generous RPO does not need three zones and synchronous replication; a single server with PITR backups and a tested restore is the right-sized answer.
SecuritySecurity notes
Backups and WAL archives contain all your data. Encrypt them at rest, restrict the bucket to the backup role, and keep at least one copy in an account or project that production credentials cannot delete, so ransomware or a compromised key cannot remove every copy.
ChallengeTry it yourself
Before a cross-region migration with logical replication, list each table in public with its replica identity, whether it has a primary key, and whether UPDATE and DELETE on it can be replicated.
Show one solution
SELECT c.relname,
c.relreplident AS replica_identity,
EXISTS (SELECT 1 FROM pg_constraint k WHERE k.conrelid = c.oid AND k.contype = 'p') AS has_pk,
c.relreplident IN ('f', 'i')
OR (c.relreplident = 'd' AND EXISTS (SELECT 1 FROM pg_constraint k WHERE k.conrelid = c.oid AND k.contype = 'p')) AS updates_replicate
FROM pg_class c
WHERE c.relnamespace = 'public'::regnamespace AND c.relkind IN ('r', 'p')
ORDER BY 1;InterviewQuestions you should be able to answer
Define RPO and RTO and map them to PostgreSQL features.
Design a highly available PostgreSQL for a payments service.
QuizCheck your understanding
4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.