12. Replication and High Availability

A single database server is a single point of failure: when its disk dies, its data center loses power, or it simply needs a restart for an upgrade, every application that depends on it stops. This chapter covers how PostgreSQL keeps copies of data on other servers, both as exact physical replicas and as selective logical streams of row changes, how those copies are turned into a highly available system that survives the loss of a server, and how replication, sharding, and federation let a database grow beyond what one machine can handle. These are the techniques behind every "the database never goes down" promise, and the failure modes they introduce are just as important to understand as the features themselves.

<a id="121-physical-replication"></a>

12.1 Physical Replication

Physical replication keeps one or more standby servers as byte-for-byte copies of a primary by continuously shipping and replaying its write-ahead log; this subchapter explains how the pieces fit together, how to build a standby, how replication slots, synchronous commit, and read-only standbys work, and how to monitor and troubleshoot the result.

Streaming Replication Architecture

Backups (11.3) protect against losing data, but restoring a large backup takes hours. If the primary server fails at 3 a.m., nobody wants to wait for a restore; they want a second server that already has all the data and can take over in seconds. Keeping such a copy up to date by re-copying files would be hopeless for a busy database. PostgreSQL instead reuses a mechanism it already has: the write-ahead log (WAL, 11.2). Every change to any data file is first described in the WAL, and crash recovery works by replaying the WAL against the data files. A standby is simply a server that stays in recovery forever, replaying WAL that it receives from the primary as fast as it arrives.

Analogy. Imagine a chef who writes down every action in a notebook before doing it: "add 200 g flour to bowl 3", "stir bowl 3 for 2 minutes". An apprentice in an identical second kitchen receives each page of the notebook as it is written and performs exactly the same actions. The apprentice never needs to understand the recipe; following the notebook produces an identical kitchen. If the chef collapses, the apprentice's kitchen is ready to continue, minus whatever pages had not arrived yet.

The moving parts

                PRIMARY                                              STANDBY
 +--------------------------------------+         +------------------------------------------+
 | client backends                      |         |                                          |
 |   | write WAL records                |         |                                          |
 |   v                                  |         |                                          |
 | WAL buffers --> pg_wal/ (on disk)    |         |  pg_wal/ (on disk) --> startup process    |
 |                     |                |  TCP    |     ^                    | replays records |
 |                     v                | stream  |     |                    v                 |
 |              walsender process ------+-------->+-- walreceiver     data files (identical)   |
 |                     ^                |         |        |                                  |
 |                     +----------------+---------+--------+ status replies: written, flushed,|
 |                       feedback       |         |          replayed positions               |
 +--------------------------------------+         +------------------------------------------+

• walsender: a process on the primary, one per connected standby, that reads WAL and sends it over a replication connection.
• walreceiver: a process on the standby that receives WAL, writes it to the standby's own pg_wal, and periodically reports back how far it has written and flushed.
• startup process: the recovery process on the standby that reads WAL from pg_wal and applies it to the data files, exactly as after a crash.

Positions in the stream. Every WAL record has a log sequence number (LSN), a byte position in the WAL such as 2B/84A1C3B0. Replication progress is measured as four LSNs, which explain most lag questions later in this subchapter:

 primary generates WAL  ......................................................... pg_current_wal_lsn()
 walsender has sent     ................................................ sent_lsn
 standby has written    ......................................... write_lsn      (in OS cache)
 standby has flushed    ................................... flush_lsn            (durable on standby disk)
 standby has replayed   ............................ replay_lsn                (visible to standby queries)
                        <-------- older                      newer -------->

Two delivery paths. WAL can reach a standby in two ways, and production setups often use both:

Path	How it works	Delay	Role
Streaming	walreceiver holds a TCP connection to the primary; records arrive as they are written	Milliseconds	Normal operation
Log shipping	Standby fetches completed 16 MB WAL segments from the archive with restore_command	Up to a full segment or archive_timeout	Fallback when streaming falls too far behind, or when no network path to the primary exists

A standby that cannot stream (for example, because the primary already recycled the WAL it needs) falls back to restore_command, catches up from the archive, and then switches back to streaming automatically.

What gets replicated. Because replication works on WAL, the standby is a copy of the entire cluster: every database, every table and index, roles, DDL, sequences, and even bloat. The consequences:

Replicated	Not replicated
All databases, schemas, tables, indexes, sequences	Configuration files (postgresql.conf, pg_hba.conf)
DDL (CREATE, ALTER, DROP)	Unlogged tables (they exist on the standby but are empty)
Roles and privileges	Temporary tables
Vacuum results, page-level layout	Statistics views (pg_stat_* are per server)

Physical replication also has hard requirements: the standby must run the same major version on the same CPU architecture and OS family, because it applies changes at the level of 8 kB disk pages. It cannot replicate a subset of tables or replicate between major versions; those are jobs for logical replication (12.2).

Seeing it on a running system

# On the primary: one walsender per connected standby
ps -o pid,cmd -u postgres | grep walsender
#  41237 postgres: 17/main: walsender replicator 10.0.1.11(51544) streaming 2B/84A1C3B0

# On the standby: the walreceiver and the startup process
ps -o pid,cmd -u postgres | grep -E 'walreceiver|startup'
#  2210 postgres: 17/main: startup recovering 000000010000002B00000084
#  2215 postgres: 17/main: walreceiver streaming 2B/84A1C3B0

-- Which role does this server have?
SELECT pg_is_in_recovery();          -- false on the primary, true on a standby

-- On the primary: connected standbys
SELECT application_name, client_addr, state, sent_lsn, replay_lsn
FROM pg_stat_replication;
--  application_name | client_addr |   state   |  sent_lsn   | replay_lsn
-- ------------------+-------------+-----------+-------------+-------------
--  db2              | 10.0.1.11   | streaming | 2B/84A1C3B0 | 2B/84A1C3B0

-- On the standby: the receiving side
SELECT status, sender_host, written_lsn, flushed_lsn, latest_end_time
FROM pg_stat_wal_receiver;

Key takeaways

• A standby is a server in permanent recovery that replays the primary's WAL; walsender, walreceiver, and the startup process do the work.
• Progress is tracked by LSNs: sent, written, flushed, replayed. Each gap means something different.
• WAL arrives by streaming (normal) or from the archive via restore_command (fallback).
• Physical replication copies the whole cluster and requires the same major version and platform; use logical replication for anything selective or cross-version.

🧪 Practice

1. (Beginner) On a running primary with one standby, find the walsender, walreceiver, and startup processes with ps, and match the LSN in their process titles with pg_stat_replication.sent_lsn.
2. (Intermediate) Create an unlogged table and a regular table on the primary, insert rows into both, and query them on the standby. Explain the result for the unlogged table using what you know about WAL.
3. (Interview) "Why can't physical replication replicate a single database out of a cluster, or replicate from PostgreSQL 16 to 17?" Hint: Think about what a WAL record actually describes (pages and blocks, not rows and SQL), and about on-disk format changes between major versions.

Primary and Standby Setup

Building a standby takes three steps: allow replication connections on the primary, copy the primary's data directory to the new server, and tell the copy to start as a standby that streams from the primary. Since PostgreSQL 12, all of this is done with ordinary configuration parameters and a signal file; the old recovery.conf no longer exists and its presence prevents startup.

Step 1: prepare the primary

The defaults in modern versions already allow replication (wal_level = replica, max_wal_senders = 10, max_replication_slots = 10), so usually only authentication needs work.

# postgresql.conf on the primary (defaults shown where they are already fine)
wal_level = replica                  # minimal is NOT enough; logical also works
max_wal_senders = 10                 # one per standby, plus pg_basebackup, plus headroom
max_replication_slots = 10
wal_keep_size = 1GB                  # optional safety buffer of WAL kept for lagging standbys
hot_standby = on                     # ignored on the primary, but the file gets copied to standbys
wal_log_hints = on                   # lets pg_rewind resync an old primary later (12.3); not needed with data checksums

-- A dedicated role with the REPLICATION attribute (10.2), not a superuser
CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'change-me';

Replication connections are matched in pg_hba.conf against the special database name replication (all does not match them):

# TYPE    DATABASE     USER        ADDRESS          METHOD
hostssl   replication  replicator  10.0.1.0/24      scram-sha-256

SELECT pg_reload_conf();   -- pg_hba.conf changes need only a reload

Step 2: clone the primary with pg_basebackup

# On the new standby host, as the postgres user, with the target data directory empty
sudo systemctl stop postgresql@17-main
rm -rf /var/lib/postgresql/17/main/*

# -h/-U      connect to the primary as the replication role
# -D         where to write the copy
# -X stream  stream WAL during the copy so the backup is self-consistent
# -C -S db2  create a physical replication slot named db2 on the primary and use it
# -R         write standby.signal and primary_conninfo for us
# -c fast    request an immediate checkpoint instead of waiting for the next one
# -P         show progress
pg_basebackup -h 10.0.1.10 -U replicator \
  -D /var/lib/postgresql/17/main \
  -X stream -C -S db2 -R -c fast -P

The password is read from ~/.pgpass (or prompted), never written on the command line. On a multi-terabyte database this step can take hours; -R makes it the last manual step.

What -R wrote

ls /var/lib/postgresql/17/main/standby.signal          # empty file: "start in standby mode"
cat /var/lib/postgresql/17/main/postgresql.auto.conf
# primary_conninfo = 'user=replicator passfile=''/var/lib/postgresql/.pgpass'' host=10.0.1.10 port=5432 sslmode=prefer ...'
# primary_slot_name = 'db2'

Add an application_name so the primary can tell standbys apart (it defaults to cluster_name, or walreceiver if that is empty). Synchronous replication (later in this subchapter) depends on these names.

# postgresql.auto.conf on the standby, edited before the first start
# (ALTER SYSTEM needs a running server, so edit the file directly at this point)
primary_conninfo = 'host=10.0.1.10 port=5432 user=replicator application_name=db2 sslmode=require'
primary_slot_name = 'db2'

Step 3: start and verify

sudo systemctl start postgresql@17-main
tail -n 5 /var/log/postgresql/postgresql-17-main.log
# LOG:  entering standby mode
# LOG:  redo starts at 2B/84000028
# LOG:  consistent recovery state reached at 2B/84000138
# LOG:  database system is ready to accept read-only connections
# LOG:  started streaming WAL from primary at 2B/85000000 on timeline 1

-- On the standby
SELECT pg_is_in_recovery();                 -- t
-- On the primary
SELECT application_name, state, sync_state FROM pg_stat_replication;
--  application_name |   state   | sync_state
--  db2              | streaming | async

Signal files decide the startup mode

File in the data directory	Behavior at startup
none	Normal primary (after crash recovery, if needed)
standby.signal	Standby: replays WAL forever, from streaming and/or restore_command, until promoted
recovery.signal	Targeted recovery (PITR, 11.3): replays until a recovery target, then promotes or pauses
both	standby.signal wins

Parameters that must match. Some parameters size shared memory structures that the standby must mirror. max_connections, max_worker_processes, max_wal_senders, max_prepared_transactions, and max_locks_per_transaction must be equal to or higher on the standby than on the primary. If the primary raises one, raise it on standbys first; otherwise the standby pauses replay (PG 14+) or refuses to start, with an error naming the parameter.

Promotion in one line. Turning a standby into a primary ends recovery, removes standby.signal, and switches to a new timeline (a new branch of WAL history, numbered 2, 3, ...):

SELECT pg_promote();         -- or: pg_ctl promote -D /var/lib/postgresql/17/main

When to promote, and how to keep the old primary from coming back, is the subject of 12.3.

Key takeaways

• On the primary: wal_level of at least replica, enough max_wal_senders, a REPLICATION role, and a replication line in pg_hba.conf.
• pg_basebackup -X stream -C -S <slot> -R produces a ready-to-start standby with its slot and connection settings.
• standby.signal plus primary_conninfo (in postgresql.auto.conf) is what makes a server a streaming standby; recovery.conf is gone since PG 12.
• Set a distinct application_name per standby and keep shared-memory-sizing parameters at least as high as the primary's.
• Promotion (pg_promote()) ends recovery and starts a new timeline.

🧪 Practice

1. (Beginner) Build a primary and a standby on one machine using two data directories and ports 5432 and 5433. Verify with pg_stat_replication and by creating a table on the primary and querying it on the standby.
2. (Intermediate) Raise max_connections on the primary only, restart it, and observe what the standby logs. Then fix it in the correct order and explain why the order matters.
3. (Interview) "A new standby connects but immediately logs no pg_hba.conf entry for replication connection. The pg_hba.conf already has host all replicator 10.0.1.0/24 scram-sha-256. What is wrong?" Hint: Which database name do replication connections request, and does all match it?

Replication Slots

The primary does not keep WAL forever. After each checkpoint, segments that are no longer needed for crash recovery are recycled. If a standby is disconnected for a while (a network outage, a maintenance reboot) and comes back after the WAL it needs was recycled, it can never catch up by streaming:

FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000002B00000041 has already been removed

The standby must then fetch WAL from the archive or be rebuilt from scratch. A replication slot prevents this: it is a named, persistent bookmark on the primary that records how far a specific consumer has confirmed receiving. The primary will not remove any WAL newer than the oldest slot position.

Analogy. A library normally shreds newspapers older than a week. A slot is a reader's reservation slip: "keep everything from March 3 onward for me". The library honors it indefinitely, which is exactly the problem if the reader moves away and never returns: the archive room fills up.

Three ways to keep WAL for standbys

Mechanism	How much WAL is kept	Risk
wal_keep_size	Fixed amount, regardless of whether anyone needs it	A standby that falls further behind still breaks
WAL archive + restore_command	Everything, in external storage	Archive must be reachable; catch-up from archive is slower
Replication slot	Exactly what each consumer still needs	An abandoned slot retains WAL without limit and can fill the primary's disk

Creating and inspecting slots

-- Physical slot for a standby (pg_basebackup -C -S does the same)
SELECT pg_create_physical_replication_slot('db3');

-- The standby uses it via: primary_slot_name = 'db3'

SELECT slot_name, slot_type, active, active_pid,
       restart_lsn,                                      -- oldest WAL this slot still needs
       wal_status,                                       -- reserved / extended / unreserved / lost
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
--  slot_name | slot_type | active | active_pid | restart_lsn | wal_status | retained_wal
-- -----------+-----------+--------+------------+-------------+------------+--------------
--  db2       | physical  | t      |      41237 | 2B/84A1C3B0 | reserved   | 312 kB
--  db3       | physical  | f      |            | 2A/10000000 | extended   | 29 GB          -- trouble

The db3 row is the classic incident: the standby was decommissioned but its slot was not dropped, and 29 GB of WAL is pinned in pg_wal.

Bounding the damage. Since PostgreSQL 13, max_slot_wal_keep_size caps how much WAL any slot may retain. When a slot exceeds it, the slot is invalidated (wal_status = lost) and its consumer must be rebuilt, which is better than the primary running out of disk and stopping. PostgreSQL 18 adds idle_replication_slot_timeout to invalidate slots that have been inactive for too long.

# postgresql.conf on the primary
max_slot_wal_keep_size = 100GB        # -1 (default) means unlimited

wal_status	Meaning
reserved	Retained WAL is within max_wal_size
extended	Retained WAL exceeds max_wal_size but is protected by the slot or wal_keep_size
unreserved	WAL needed by the slot will be removed at the next checkpoint; the consumer must catch up now
lost	Needed WAL is gone; the slot is unusable

Dropping a slot

SELECT pg_drop_replication_slot('db3');   -- fails if a consumer is currently using it (active = t)

Slots and vacuum. If a standby uses hot_standby_feedback (see Standby Conflicts and Feedback), the slot also stores the standby's oldest needed transaction ID in its xmin column. That holds back vacuum on the primary even while the standby is disconnected (8.3). Logical slots (12.2) do the same for catalog rows through catalog_xmin.

Slot types at a glance

Type	Created by	Tracks	Typical consumer
Physical	pg_create_physical_replication_slot, pg_basebackup -C	WAL position (+ optional xmin)	Streaming standby, pg_receivewal
Logical	pg_create_logical_replication_slot, CREATE SUBSCRIPTION	Decoded change position + catalog_xmin	Subscriber, Debezium, pg_recvlogical
Temporary	Either, with temporary => true	Same, but dropped when the session ends	pg_basebackup without -S

Key takeaways

• A slot guarantees that the WAL (and, with feedback, the row versions) a consumer needs is not removed, regardless of how long it is offline.
• An abandoned or stuck slot is one of the most common causes of a full pg_wal and a primary outage.
• Always set max_slot_wal_keep_size and alert on inactive slots and on retained WAL size.
• Drop the slot whenever you decommission a standby or a CDC consumer.

🧪 Practice

1. (Beginner) Create a physical slot that nothing uses, run pgbench for a few minutes, and watch retained_wal and the size of pg_wal grow. Drop the slot and run CHECKPOINT; what happens to pg_wal?
2. (Intermediate) Set max_slot_wal_keep_size = 256MB, repeat the experiment, and record each wal_status transition. Then try to start a standby with that slot and read its error.
3. (Interview) "Our primary went down because pg_wal filled a 500 GB volume, although archiving was working. What do you check first, and how do you prevent a repeat?" Hint: pg_replication_slots, active, restart_lsn, logical slots of CDC tools, max_slot_wal_keep_size, and alerting on retained WAL.

Synchronous vs. Asynchronous Replication

By default, replication is asynchronous: the primary reports COMMIT to the client as soon as the commit record is flushed to its own disk, and sends WAL to standbys independently. That is fast, but it opens a window: if the primary dies, transactions that were committed but not yet received by the standby are lost when the standby is promoted. Synchronous replication closes the window by making commit wait until one or more standbys confirm they have the commit record. The price is latency on every commit and a new dependency: if the synchronous standbys are unavailable, commits wait.

Analogy. Asynchronous replication is sending a letter and walking away; you trust the mail. Synchronous replication is sending a registered letter and waiting at the counter until the recipient signs for it. Safer, but every letter costs you the round trip.

What the client waits for

 client          primary                          sync standby
   |  COMMIT ----->|                                   |
   |               | flush commit record locally       |
   |               |---- WAL (commit record) --------->|
   |               |                                   | write / flush / replay (depends on level)
   |               |<--------- ack (LSN) --------------|
   |<-- COMMIT ok -|                                   |
           ^ asynchronous replication returns here, right after the local flush

Configuring it. Synchronous standbys are listed by application_name in synchronous_standby_names on the primary:

# postgresql.conf on the primary
synchronous_standby_names = 'ANY 1 (db2, db3)'     # quorum: any one of db2/db3 must confirm
# synchronous_standby_names = 'FIRST 1 (db2, db3)' # priority: db2 if connected, else db3
# synchronous_standby_names = ''                   # default: all replication asynchronous
synchronous_commit = on

Form	Meaning
FIRST n (a, b, c)	The first n connected standbys in list order are synchronous; others are potential replacements
ANY n (a, b, c)	Commit waits for any n of them (quorum); more robust to a single slow standby
* inside the list	Matches any standby name

SELECT application_name, sync_state, sync_priority FROM pg_stat_replication;
--  application_name | sync_state | sync_priority
--  db2              | quorum     |             1
--  db3              | quorum     |             1
-- (FIRST would show sync / potential instead of quorum)

How strong is "synchronous"? synchronous_commit chooses the durability level per transaction, which gives a sliding scale:

synchronous_commit	Commit returns after	Safe across primary crash and restart	Safe on failover to the standby	Standby query sees the row when commit returns
off	WAL in memory on the primary (flushed shortly after)	No (last ~600 ms may be lost)	No	Not guaranteed
local	Local flush only	Yes	No	Not guaranteed
remote_write	Standby has written WAL to its OS	Yes	Yes, unless the standby's OS also crashes	Not guaranteed
on (default)	Standby has flushed WAL to disk	Yes	Yes	Not guaranteed
remote_apply	Standby has replayed the commit	Yes	Yes	Yes

Without any synchronous standbys configured, remote_write, on, and remote_apply all behave like local.

Per-transaction choices. The level can be relaxed or tightened per session or transaction, so you pay for durability only where it matters:

-- Click-tracking data: losing a few rows on failover is acceptable, latency is not
BEGIN;
SET LOCAL synchronous_commit = local;
INSERT INTO page_views (user_id, url) VALUES (42, '/home');
COMMIT;

-- Payment: must be visible on read replicas as soon as the client is told it succeeded
BEGIN;
SET LOCAL synchronous_commit = remote_apply;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
COMMIT;

The latency cost. Every synchronous commit pays one network round trip to the standby (plus its disk flush for on):

Standby location	Typical round trip	Effect on single-row commits
Same availability zone	0.1 - 0.5 ms	Usually negligible
Different zone, same region	0.5 - 2 ms	Noticeable for high-rate small transactions
Different region	20 - 100 ms	Dominates commit time; rarely used synchronously

The availability trap. If the only synchronous standby is down, every commit on the primary waits indefinitely: the database appears hung for writes. Mitigations:

• Use at least two candidates (ANY 1 (db2, db3)), so one can fail.
• Let an HA manager adjust the setting automatically (Patroni's synchronous_mode, Automatic Failover with Patroni).
• Know the emergency switch: set synchronous_standby_names = '' and reload; waiting commits are released.

A subtle gotcha. If a client cancels a commit that is waiting for a standby, the transaction is already committed locally; only the replication wait is abandoned:

WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.

Applications that treat a cancelled commit as "rolled back" will be wrong; the row exists on the primary.

Key takeaways

• Asynchronous replication is fast but can lose recently committed transactions on failover (RPO > 0).
• Synchronous replication waits for standby acknowledgment; synchronous_standby_names chooses which standbys and how many (FIRST or ANY).
• synchronous_commit levels range from off to remote_apply, and can be set per transaction.
• A single synchronous standby is an availability risk: if it is down, writes block. Use quorums of two or more candidates or automated management.
• A cancelled synchronous commit is still committed on the primary.

🧪 Practice

1. (Beginner) Configure synchronous_standby_names = 'FIRST 1 (db2)', stop db2, and try an INSERT on the primary. Then release it without restarting the standby.
2. (Intermediate) Use pgbench -c 8 -T 60 against a primary with one standby in another network namespace with 2 ms added latency (tc qdisc add ... netem delay 2ms). Compare TPS with synchronous_commit set to local, on, and remote_apply.
3. (Interview) "The business wants zero data loss on failover, but the CTO also says the database must keep accepting writes if a replica dies. How would you configure this, and what remains unprotected?" Hint: Quorum with ANY 1 over two or three standbys, automated reconfiguration, and the case where the primary and all synchronous standbys fail together.

Hot Standby and Read Replicas

A standby that only replays WAL is valuable for failover but wastes hardware the rest of the time. With hot standby (hot_standby = on, the default), the standby also accepts read-only connections while it replays. A hot standby is the most common form of read replica: it can serve reports, analytics, backups, and read-heavy application endpoints without adding load to the primary.

What is allowed on a hot standby

Allowed	Not allowed
SELECT, COPY ... TO, cursors, EXPLAIN	INSERT, UPDATE, DELETE, MERGE, COPY ... FROM
SET, SHOW, PREPARE, LOCK ... IN ACCESS SHARE MODE	Any DDL, CREATE INDEX, ANALYZE, VACUUM
Transactions with REPEATABLE READ or READ COMMITTED	SERIALIZABLE (not supported on standbys)
EXECUTE, DEALLOCATE, DISCARD, LOAD	Temporary tables, nextval(), LISTEN/NOTIFY
pg_dump, pg_basebackup	Two-phase commit (PREPARE TRANSACTION)

-- On a standby
INSERT INTO orders (customer_id) VALUES (1);
-- ERROR:  cannot execute INSERT in a read-only transaction

SELECT pg_is_in_recovery(), current_setting('transaction_read_only');
--  pg_is_in_recovery | current_setting
--  t                 | on

Everything comes from the primary. Indexes, statistics, and schema changes all arrive through WAL. If a report on the standby needs an index, you create it on the primary. Planner statistics are replicated too (they live in pg_statistic, a regular table), so ANALYZE on the primary benefits standby queries. Runtime statistics such as pg_stat_statements and pg_stat_user_tables are per server; to find the slow queries on a replica, look at the replica.

Eventual consistency. Replay on an asynchronous standby is a little behind the primary, usually milliseconds, occasionally seconds or more. That creates the read-your-writes problem:

 t=0 ms   app -> primary : UPDATE profile SET name = 'Ada' WHERE id = 7;  COMMIT ok
 t=2 ms   app -> replica : SELECT name FROM profile WHERE id = 7;   -->  'Ann'   (old value!)
 t=5 ms   replica replays the UPDATE

Common solutions, discussed further in Load Balancing Reads:

• Route reads that follow a write in the same user request (or session) to the primary.
• Use synchronous_commit = remote_apply for writes whose results must be visible on replicas immediately.
• Record the primary's LSN after a write and have the replica wait until it has replayed past it.

Detecting role from the application. libpq-based drivers can choose a server by role from a list of hosts, which is the basis of most failover-aware connection strings (Virtual IPs and Service Discovery):

# Connect to whichever host is currently a standby, preferring standbys but falling back to the primary
psql 'host=db1,db2,db3 dbname=shop user=report target_session_attrs=prefer-standby'

Dedicated replicas for heavy workloads. Long analytical queries on a standby conflict with replay (next topics). Many teams therefore separate standbys by purpose:

Replica role	Configuration	Trade-off
Failover candidate	Low lag, hot_standby_feedback off or carefully bounded, few long queries	Must be ready to take over in seconds
Reporting replica	Long max_standby_streaming_delay or hot_standby_feedback = on	May lag minutes; never promoted
Delayed replica	recovery_min_apply_delay = '4h'	Protects against human errors (dropped table) by staying hours behind

Key takeaways

• hot_standby = on lets standbys serve read-only queries while they replay WAL.
• No writes, DDL, VACUUM, ANALYZE, temporary tables, sequences, or SERIALIZABLE on a standby; schema, indexes, and planner statistics come from the primary.
• Replicas are eventually consistent: plan for read-your-writes explicitly.
• Separate failover replicas from reporting replicas when queries run long.

🧪 Practice

1. (Beginner) Connect to a standby and try CREATE TEMP TABLE, SELECT nextval('orders_id_seq'), and ANALYZE orders. Record each error and explain why the operation needs to write.
2. (Intermediate) Write a small script that updates a row on the primary and immediately reads it from an asynchronous standby in a loop of 10,000 iterations. Count how often the read returns the old value, then repeat with synchronous_commit = remote_apply.
3. (Interview) "Product says the replica shows stale data right after a user saves their profile. Propose three fixes with their trade-offs." Hint: Routing by request, remote_apply, LSN-based waiting, and sticky sessions after writes.

Replication Lag Monitoring

Replication lag is how far a standby is behind the primary. It matters in three ways: it is the amount of data you could lose if you fail over to an asynchronous standby (RPO), it is how stale reads from a replica can be, and a lag that keeps growing means WAL is piling up on the primary (when a slot is used) and the standby may never catch up.

Measuring in bytes and in time. Lag can be expressed as the WAL distance between two LSNs (bytes) or as elapsed time. Both are useful: bytes tell you how much work remains; time tells you how stale the data is.

-- On the PRIMARY: one row per standby, the most complete picture
SELECT application_name,
       client_addr,
       state,                                                              -- startup / catchup / streaming
       sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn))   AS not_yet_sent,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn))              AS sent_not_flushed,
       pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn))            AS flushed_not_replayed,
       write_lag, flush_lag, replay_lag,                                   -- measured time intervals
       reply_time                                                          -- last message from standby
FROM pg_stat_replication;
--  application_name | client_addr |   state   | sync_state | not_yet_sent | sent_not_flushed | flushed_not_replayed |    write_lag    |    flush_lag    |   replay_lag    |          reply_time
-- ------------------+-------------+-----------+------------+--------------+------------------+----------------------+-----------------+-----------------+-----------------+-------------------------------
--  db2              | 10.0.1.11   | streaming | async      | 0 bytes      | 0 bytes          | 0 bytes              | 00:00:00.000412 | 00:00:00.001107 | 00:00:00.001298 | 2026-10-06 14:30:02.118+00
--  db3              | 10.0.2.11   | streaming | async      | 0 bytes      | 128 kB           | 1450 MB              | 00:00:00.03     | 00:00:00.04     | 00:03:12.7      | 2026-10-06 14:30:01.902+00

Reading the result: db3 receives and flushes WAL quickly, but replay is 1450 MB and over three minutes behind. The network is fine; something on the standby is slowing or blocking replay.

Where each gap points

Large gap between	Likely cause	Check
pg_current_wal_lsn() and sent_lsn	Primary cannot send fast enough: CPU, WAL read I/O, or many standbys	Primary load, number of walsenders
sent_lsn and write_lsn/flush_lsn	Network bandwidth or latency; slow standby disk for pg_wal	Network graphs, standby iostat
flush_lsn and replay_lsn	Replay is slow or paused: conflicts with standby queries, single-threaded replay of a heavy operation, slow data disk	pg_stat_database_conflicts, long queries on the standby, pg_is_wal_replay_paused()
Standby disconnected (no row)	Network, authentication, WAL removed	Standby log, pg_replication_slots

From the standby's side. A standby can estimate its own lag, which is useful for load balancers that should stop routing to a lagging replica:

-- On the STANDBY
SELECT pg_last_wal_receive_lsn()                            AS received,
       pg_last_wal_replay_lsn()                             AS replayed,
       pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replay_backlog_bytes,
       CASE
         WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN interval '0'   -- fully caught up
         ELSE now() - pg_last_xact_replay_timestamp()                                    -- age of last replayed commit
       END                                                  AS approx_lag;

The CASE matters: now() - pg_last_xact_replay_timestamp() alone measures the time since the last transaction was replayed. On an idle primary that number grows forever even though the standby is perfectly in sync.

A precise lag probe. For an end-to-end measurement independent of these subtleties, write a heartbeat row on the primary and read it on the replica:

-- Primary, every second from a cron job or the monitoring agent
CREATE TABLE IF NOT EXISTS heartbeat (id int PRIMARY KEY, ts timestamptz NOT NULL);
INSERT INTO heartbeat VALUES (1, clock_timestamp())
ON CONFLICT (id) DO UPDATE SET ts = excluded.ts;

-- Replica: true staleness of data as seen by queries
SELECT clock_timestamp() - ts AS replica_staleness FROM heartbeat WHERE id = 1;

Clocks on both hosts must be synchronized (NTP or chrony) for any time-based lag measurement.

Alerting. Alert on the trend, not only on absolute values:

# prometheus-rules.yml (metric names depend on your exporter configuration)
groups:
  - name: postgresql-replication
    rules:
      - alert: PostgresReplicationLagHigh
        expr: pg_replication_lag_seconds > 60
        for: 5m
        labels: { severity: warning }
        annotations:
          summary: "Replica {{ $labels.instance }} is more than 60s behind"
      - alert: PostgresStandbyMissing
        expr: count(pg_replication_is_replica == 1) < 2      # fewer than the 2 standbys we expect are reporting
        for: 2m
        labels: { severity: critical }
        annotations:
          summary: "Fewer standbys than expected are connected"

Intentional lag. A delayed standby keeps replay a fixed time behind on purpose (recovery_min_apply_delay = '4h'), so that after an accidental DROP TABLE you can pause replay, extract the table, and avoid a full PITR restore. Exclude delayed standbys from lag alerts and never make them failover candidates.

-- On a delayed standby after an incident
SELECT pg_wal_replay_pause();          -- freeze before the bad transaction is applied
-- ... extract the data with pg_dump -t ...
SELECT pg_wal_replay_resume();

Key takeaways

• Measure lag on the primary with pg_stat_replication (sent, write, flush, replay LSNs and *_lag intervals).
• The location of the gap tells you where the bottleneck is: sending, network, standby disk, or replay.
• now() - pg_last_xact_replay_timestamp() misreports lag on an idle primary; compare receive and replay LSNs or use a heartbeat table.
• Alert on sustained lag, missing standbys, and retained WAL in slots; synchronize clocks.
• A delayed standby is a deliberate, valuable form of lag for recovering from human error.

🧪 Practice

1. (Beginner) Run the primary-side lag query while pgbench -i -s 200 runs on the primary. Which of the gaps grows, and why?
2. (Intermediate) Start a 5-minute query on the standby that conflicts with replay (see the next topic) and watch replay_lag grow while flush_lag stays small. Write an alert expression that would have fired.
3. (Interview) "Monitoring says a replica is 6 hours behind, yet a query against it returns today's orders. How can both be true?" Hint: What does pg_last_xact_replay_timestamp() measure on an idle or nearly idle primary, and what happens at night?

Cascading Replication

Every standby connected to the primary costs it a walsender process, network bandwidth, and, if the standby is far away, a long-distance stream per replica. With many replicas or replicas in another region, it is better to let a standby relay WAL to other standbys. This is cascading replication: a standby that receives WAL also acts as a sending server for downstream standbys.

Analogy. A teacher dictating to a large lecture hall cannot shout to everyone. Instead, the teacher speaks to a few students at the front of each section, and each of them repeats the dictation to their own rows.

Topology

   Region A (primary site)                         Region B (DR site)
 +---------------------------+                  +-------------------------------+
 |  db1 (primary)            |                  |                               |
 |    |-------> db2 (standby)|   one WAN stream |                               |
 |    |-------------------------------------------> db4 (standby, "relay")      |
 |                           |                  |      |------> db5 (standby)   |
 |                           |                  |      |------> db6 (standby)   |
 +---------------------------+                  +-------------------------------+

The primary sends WAL across the WAN once, to db4; db5 and db6 stream from db4 over the local network.

Configuring a cascaded standby. There is nothing special to enable beyond what any sender needs. The upstream standby must allow replication connections, and the downstream one points primary_conninfo at it:

# On db4 (upstream standby): it acts as a sender
max_wal_senders = 10
hot_standby = on
# pg_hba.conf on db4 must contain a "replication" line for db5/db6

# Build db5 by cloning db4, not the primary
pg_basebackup -h db4 -U replicator -D /var/lib/postgresql/17/main -X stream -C -S db5 -R -P
# -C -S creates the slot on db4: slots can be created on standbys for downstream consumers

-- On db4: its downstream standbys appear in pg_stat_replication, just like on a primary
SELECT application_name, state, replay_lag FROM pg_stat_replication;
--  application_name |   state   |   replay_lag
--  db5              | streaming | 00:00:00.002
--  db6              | streaming | 00:00:00.003

-- On db1 (primary): only db2 and db4 appear

Behavior to know

Topic	Behavior
Lag	Adds up: the lag of db5 relative to db1 is db4's lag plus db5's lag behind db4
Synchronous replication	Only direct standbys of the primary can be synchronous; cascaded standbys are always asynchronous
Upstream failure	If db4 fails, db5 and db6 stop receiving WAL until repointed to another sender
Upstream promotion	If db4 is promoted, db5 and db6 follow it to the new timeline automatically (recovery_target_timeline = 'latest', the default)
Primary failover	If db1 fails and db2 is promoted, db4 must be repointed to db2; its downstream standbys follow db4
Archiving	A standby archives WAL only with archive_mode = always; useful to keep a regional archive

Repointing a standby. Since PostgreSQL 13, primary_conninfo can be changed with a reload instead of a restart:

-- On db5, if db4 has failed: stream from db1 instead
ALTER SYSTEM SET primary_conninfo = 'host=db1 port=5432 user=replicator application_name=db5';
SELECT pg_reload_conf();      -- walreceiver restarts with the new upstream
-- Remember: the slot named in primary_slot_name must exist on the NEW upstream

HA managers such as Patroni support cascading through per-node tags (for example replicatefrom: db4) and repoint nodes for you after a failover.

Key takeaways

• Cascading replication lets standbys relay WAL to other standbys, reducing load and WAN traffic on the primary.
• A cascaded standby is configured like any standby, with primary_conninfo pointing at its upstream standby.
• Lag accumulates along the chain; cascaded standbys cannot be synchronous.
• Plan what happens when an intermediate node fails or the primary changes; primary_conninfo can be changed with a reload (PG 13+).

🧪 Practice

1. (Beginner) Build a chain primary -> standby1 -> standby2 on one machine. Show which pg_stat_replication view lists which standby.
2. (Intermediate) Promote standby1 and observe in standby2's log how it switches to the new timeline. Then repeat the setup and instead stop standby1; repoint standby2 to the primary with a reload, creating the needed slot first.
3. (Interview) "You need 12 read replicas spread across three regions. Sketch a topology and explain how you would keep failover simple." Hint: One relay per region, slot placement, what a primary failover means for the relays, and whether an HA manager handles repointing.

Standby Conflicts and Feedback

A hot standby replays the primary's changes while users run queries on it, and sometimes the two collide. The classic case: a report on the standby is reading old row versions, while on the primary VACUUM has already removed those versions because no query on the primary needed them. When the standby replays that cleanup, it must either wait (falling behind) or cancel the report. This is a recovery conflict.

Analogy. A museum (the standby) shows visitors a copy of a painting. Headquarters (the primary) decides to repaint part of it and sends instructions. If a visitor is studying exactly that part, the restorer must either wait for the visitor to finish or ask them to leave. Waiting too long means the museum's copy falls behind; asking too quickly annoys visitors.

Types of conflict

Conflict	Caused by (on the primary)	pg_stat_database_conflicts column
Snapshot	VACUUM or HOT pruning removes row versions a standby query can still see	confl_snapshot
Lock	ACCESS EXCLUSIVE lock: DROP TABLE, TRUNCATE, many ALTER TABLE forms, lock taken by VACUUM truncating a table's end	confl_lock
Buffer pin	Replay must modify a page that a standby query holds pinned	confl_bufferpin
Tablespace	DROP TABLESPACE while standby queries use it for temp files	confl_tablespace
Deadlock	Replay waits for a buffer pin held by a query that waits for replay	confl_deadlock
Logical slot	Catalog rows needed by a logical slot on the standby are removed (PG 16+)	confl_active_logicalslot

The cancelled query receives:

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Option 1: let replay wait. max_standby_streaming_delay (default 30s) and max_standby_archive_delay (for WAL from the archive) set how long replay may wait for conflicting queries before cancelling them. The delay is a budget for the standby as a whole, not per query: once replay has fallen that far behind, conflicting queries are cancelled until it catches up.

# postgresql.conf on a reporting standby
max_standby_streaming_delay = 15min    # long reports win; replay lag can reach 15 minutes
max_standby_archive_delay   = 15min
# -1 = wait forever (replay can stall indefinitely)

Option 2: tell the primary what the standby needs. With hot_standby_feedback = on, the standby regularly reports the oldest transaction its queries still need (its xmin). The primary's VACUUM then treats that as if a query on the primary needed those rows, so snapshot conflicts largely disappear.

# postgresql.conf on the standby
hot_standby_feedback = on

-- On the primary: the horizon each standby is holding back
SELECT application_name, backend_xmin, age(backend_xmin) AS xmin_age
FROM pg_stat_replication;
--  application_name | backend_xmin | xmin_age
--  report1          |    918201344 |   1250331     -- a long query on report1 is holding back vacuum

The trade-off. Feedback moves the cost from the standby to the primary: a 6-hour report on the standby now prevents VACUUM on the primary from removing dead rows for 6 hours, which causes bloat there (8.5).

Approach	Standby queries	Replay lag	Primary
Defaults (30 s delay, no feedback)	Long queries may be cancelled	Bounded by ~30 s	Unaffected
Large max_standby_streaming_delay	Rarely cancelled	Can grow to the configured delay	Unaffected
hot_standby_feedback = on	Snapshot conflicts avoided (lock conflicts still possible)	Low	Bloat while long standby queries run
Feedback + statement_timeout on the standby	Bounded runtime	Low	Bounded bloat

Diagnosing

-- On the standby: conflicts per database since stats reset
SELECT datname, confl_snapshot, confl_lock, confl_bufferpin, confl_deadlock
FROM pg_stat_database_conflicts
WHERE datname = current_database();

-- Is replay paused or waiting?
SELECT pg_is_wal_replay_paused(),
       pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS backlog_bytes;

Log recovery waits explicitly (PG 14+) to see which queries hold up replay:

log_recovery_conflict_waits = on    # logs when replay waits longer than deadlock_timeout

Reducing lock conflicts. Snapshot conflicts can be addressed with feedback, but lock conflicts come from DDL on the primary. Avoid surprises with:

• Running DDL during low standby activity, and with lock_timeout on the primary.
• Keeping vacuum_truncate = off (table storage parameter, or the PG 18 global setting) on tables where VACUUM's end-of-table truncation keeps causing ACCESS EXCLUSIVE conflicts.
• Using separate standbys for long reports versus failover, as described in Hot Standby and Read Replicas.

Key takeaways

• Recovery conflicts occur when replay must change something that a standby query still uses; the standby either waits or cancels the query.
• max_standby_streaming_delay trades replay lag for fewer cancellations.
• hot_standby_feedback prevents snapshot conflicts by holding back VACUUM on the primary, which can cause bloat there.
• Monitor pg_stat_database_conflicts, backend_xmin on the primary, and enable log_recovery_conflict_waits.
• Lock conflicts come from DDL and VACUUM truncation on the primary; feedback does not help with them.

🧪 Practice

1. (Beginner) On a standby with default settings, start BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM t; and keep the transaction open. On the primary, delete half of t and run VACUUM t. Wait 30 seconds and run another query in the standby transaction. Read the error and check pg_stat_database_conflicts.
2. (Intermediate) Repeat with hot_standby_feedback = on and show on the primary that n_dead_tup for t stays high and backend_xmin is set. Then add statement_timeout = '10min' on the standby and explain what problem it bounds.
3. (Interview) "Analysts complain their queries on the replica get cancelled; the DBA team refuses to enable hot_standby_feedback. What are the alternatives?" Hint: A dedicated reporting replica with a large max_standby_streaming_delay, a logical replica or warehouse, splitting queries, and why the DBAs are worried about the primary.

<a id="122-logical-replication"></a>

12.2 Logical Replication

Logical replication sends row-level changes (this row was inserted, that row was updated) instead of disk pages, which makes it possible to replicate selected tables, rows, and columns, between different major versions, into databases that have their own data and writes; this subchapter covers how it is configured, how it works internally, how to filter it, what goes wrong, and how the same machinery feeds change data capture pipelines.

Publications and Subscriptions

Physical replication is all or nothing: the standby is an exact, read-only copy of the whole cluster on the same major version. Many real needs do not fit that shape. An analytics database wants only the orders and customers tables. A new PostgreSQL 18 server must be kept in sync with a PostgreSQL 14 server during an upgrade (Logical Replication Upgrades). Twenty regional databases should feed one central reporting database. All of these need the meaning of changes ("row 42 of orders now has status shipped") rather than the bytes of changed pages.

Logical replication provides exactly that, with a publish/subscribe model:

• A publication on the source (the publisher) names which tables and which kinds of changes are published.
• A subscription on the target (the subscriber) connects to the publisher, subscribes to one or more publications, and applies the changes to its own tables.

Analogy. Physical replication photocopies the whole newspaper every time anything changes. Logical replication is a news subscription: you choose the sections you care about, and you receive each article as a separate message that you file in your own archive, which can also contain your own notes.

How a subscription runs

 PUBLISHER (wal_level = logical)                                 SUBSCRIBER (any wal_level)
 +----------------------------------+                           +----------------------------------+
 | PUBLICATION shop_pub             |                           | SUBSCRIPTION shop_sub            |
 |   tables: orders, customers      |                           |                                  |
 |                                  |  1. initial copy (COPY)   |  tablesync workers, one per table|
 | logical slot "shop_sub" ---------+-------------------------->|  (parallel, then hand off)       |
 |   pgoutput decodes WAL into      |  2. stream of changes     |                                  |
 |   INSERT/UPDATE/DELETE/TRUNCATE  +-------------------------->|  apply worker: replays changes   |
 |                                  |<--------------------------+  as normal SQL-level writes      |
 |                                  |   confirmed position      |  (triggers, constraints apply)   |
 +----------------------------------+                           +----------------------------------+

Prerequisites

Requirement	Why
wal_level = logical on the publisher (restart required)	WAL must contain enough information to reconstruct rows
Tables already exist on the subscriber with compatible columns	DDL is not replicated; columns are matched by name
A replica identity on every published table that publishes UPDATE/DELETE	The subscriber must know which row to change (usually the primary key)
A role with REPLICATION (or superuser) and SELECT on published tables	For the slot and the initial copy
pg_hba.conf allows the subscriber to connect to the database (not the replication pseudo-database)	Logical replication connects to a specific database

Setting it up

-- PUBLISHER (database shop)
CREATE ROLE repl_shop WITH LOGIN REPLICATION PASSWORD 'change-me';
GRANT SELECT ON orders, customers TO repl_shop;

CREATE PUBLICATION shop_pub FOR TABLE orders, customers;
-- Other forms: FOR ALL TABLES; FOR TABLES IN SCHEMA sales (PG 15+)

SELECT * FROM pg_publication_tables WHERE pubname = 'shop_pub';

# SUBSCRIBER: create the table definitions first (schema only, no data)
pg_dump -h publisher -d shop --schema-only -t orders -t customers | psql -h subscriber -d analytics

-- SUBSCRIBER (database analytics)
CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=publisher port=5432 dbname=shop user=repl_shop sslmode=require'
  PUBLICATION shop_pub
  WITH (copy_data = true,          -- default: copy existing rows first
        streaming = parallel,      -- apply large in-progress transactions with parallel workers (PG 16+; default in PG 18)
        disable_on_error = true);  -- stop instead of retrying forever on an error (PG 15+)
-- This creates a logical replication slot named shop_sub on the publisher.

Watching it

-- SUBSCRIBER: per-table state of the initial copy
SELECT srrelid::regclass AS table_name, srsubstate
FROM pg_subscription_rel;
--  table_name | srsubstate
--  customers  | r            -- r = ready (streaming)
--  orders     | d            -- d = data is being copied

-- SUBSCRIBER: apply worker progress
SELECT subname, received_lsn, latest_end_lsn, latest_end_time
FROM pg_stat_subscription;

-- PUBLISHER: the slot and the walsender serving it
SELECT slot_name, active, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots WHERE slot_type = 'logical';

Changing a running setup

-- PUBLISHER: publish one more table
ALTER PUBLICATION shop_pub ADD TABLE products;

-- SUBSCRIBER: create the table, then ask the subscription to pick it up (copies its data)
ALTER SUBSCRIPTION shop_sub REFRESH PUBLICATION;

-- Pause and resume
ALTER SUBSCRIPTION shop_sub DISABLE;
ALTER SUBSCRIPTION shop_sub ENABLE;

-- Remove: also drops the slot on the publisher if it can connect
DROP SUBSCRIPTION shop_sub;

If the publisher is unreachable, DROP SUBSCRIPTION fails; disassociate the slot first with ALTER SUBSCRIPTION shop_sub DISABLE; ALTER SUBSCRIPTION shop_sub SET (slot_name = NONE);, drop the subscription, and remember to drop the orphaned slot on the publisher later.

Replica identity. For UPDATE and DELETE, the publisher writes the identifying columns of the old row into WAL:

REPLICA IDENTITY	Old-row data written to WAL	Notes
DEFAULT	Primary key columns	Normal case
USING INDEX idx	Columns of a unique, non-partial index on NOT NULL columns	For tables without a primary key
FULL	Entire old row	Works without any key, but more WAL and slower apply on the subscriber
NOTHING	Nothing	UPDATE/DELETE fail if the table is in a publication that publishes them

UPDATE event_log SET processed = true WHERE id = 1;
-- ERROR:  cannot update table "event_log" because it does not have a replica identity and publishes updates
-- HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
ALTER TABLE event_log ADD PRIMARY KEY (id);     -- best fix

What is and is not replicated

Replicated	Not replicated (PG 18)
INSERT, UPDATE, DELETE, TRUNCATE on published tables	DDL (CREATE, ALTER, DROP)
Data in partitioned tables (by leaf or via the root)	Sequence values (sync them manually before cutover)
Stored generated columns, optionally (PG 18 publish_generated_columns)	Large objects (pg_largeobject)
	Views, materialized views, roles

Physical vs. logical

	Physical	Logical
Unit	WAL bytes for disk pages	Row changes
Scope	Whole cluster	Selected tables, rows, columns
Versions	Same major version and platform	Across major versions and platforms
Target	Read-only standby	Writable database with its own tables
DDL, sequences	Replicated	Not replicated
Failover target	Yes, the standard choice	Possible but needs care (sequences, slots)

Key takeaways

• Publications choose what to send; subscriptions connect, copy the initial data, and apply changes through a logical slot.
• The publisher needs wal_level = logical; the subscriber needs the tables to exist already.
• Every table that publishes UPDATE/DELETE needs a replica identity, ideally a primary key.
• DDL and sequences are not replicated; plan schema changes and sequence sync explicitly.
• Dropping a subscription normally drops its slot; when it cannot, clean up the slot on the publisher yourself.

🧪 Practice

1. (Beginner) Replicate two tables from one database to another on the same server. Insert, update, and delete rows, and verify the subscriber. Then add a column on the publisher only and insert a row: what happens, and how do you fix it?
2. (Intermediate) Write a query that lists every table in a database that would fail an UPDATE if added to a FOR ALL TABLES publication. Fix one table with a primary key and another with REPLICA IDENTITY USING INDEX.
3. (Interview) "When would you choose logical replication over a physical standby, and what would you lose?" Hint: Selectivity, cross-version, writable targets versus DDL, sequences, large objects, and failover simplicity.

Logical Decoding Internals

Logical replication is built on a general facility called logical decoding: reading the WAL, which describes physical page changes, and turning it back into a stream of row changes grouped by transaction. Understanding the pipeline explains most operational surprises, such as why a single huge transaction makes subscribers lag, why logical slots hold back vacuum of catalogs, and why changes arrive only after commit.

Why the WAL needs extra information. A physical WAL record says, in effect, "in file 16384, block 812, put these bytes at offset 1200". To turn that into "INSERT into orders (id=42, status='new')", the decoder must know which table file 16384 belonged to at that moment and what its columns were. With wal_level = logical, PostgreSQL writes extra information into WAL: the old key columns for UPDATE/DELETE (per replica identity) and enough data to rebuild the catalog state as of any decoded transaction.

The pipeline

 WAL (all transactions interleaved, in write order)
   T1:INSERT  T2:UPDATE  T1:UPDATE  T3:INSERT  T2:COMMIT  T1:ABORT  T3:COMMIT
      |
      v
 +----------------------+     +---------------------------------+     +----------------------+
 | decoder              | --> | reorder buffer                  | --> | output plugin        | --> client
 | reads WAL records,   |     | groups changes per transaction; |     | (pgoutput,           |     (subscriber,
 | uses historic catalog|     | holds them until COMMIT;        |     |  test_decoding,      |      Debezium,
 | snapshots            |     | spills to disk above            |     |  wal2json)           |      pg_recvlogical)
 +----------------------+     | logical_decoding_work_mem       |     | formats each change  |
                              +---------------------------------+     +----------------------+
 Output order:  T2 (BEGIN, UPDATE, COMMIT)   then   T3 (BEGIN, INSERT, COMMIT)      T1 is never emitted

Key consequences:

• Only committed transactions are emitted, in commit order. Aborted work is discarded. Consumers never see uncommitted data.
• Large transactions are buffered. A transaction that changes 50 million rows is held (in memory, then spilled to files under pg_replslot/) until it commits. Since PG 14, with streaming enabled on the subscription, in-progress transactions can be streamed early and applied (or applied in parallel, PG 16+) to reduce this delay.
• Historic catalog snapshots need old catalog row versions. A logical slot therefore records a catalog_xmin, and VACUUM cannot remove catalog rows newer than it. A stuck logical slot bloats system catalogs, in addition to retaining WAL.

Output plugins

Plugin	Format	Ships with	Used by
pgoutput	Binary logical replication protocol	Core	Built-in subscriptions, Debezium (default), many CDC tools
test_decoding	Human-readable text	contrib	Learning and debugging
wal2json	JSON	Third-party package	Custom consumers, some CDC tools

Watching decoding by hand. test_decoding and the SQL-level slot functions make the pipeline visible:

-- Requires wal_level = logical
SELECT pg_create_logical_replication_slot('demo', 'test_decoding');

CREATE TABLE accounts (id int PRIMARY KEY, balance numeric);
BEGIN;
INSERT INTO accounts VALUES (1, 100), (2, 50);
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
COMMIT;
BEGIN; DELETE FROM accounts WHERE id = 2; ROLLBACK;     -- will not appear

-- peek = look without consuming; get = consume (advances the slot)
SELECT lsn, xid, data FROM pg_logical_slot_peek_changes('demo', NULL, NULL);
--     lsn     | xid |                                    data
-- ------------+-----+------------------------------------------------------------------------------
--  0/1A2B3C8  | 771 | BEGIN 771
--  0/1A2B3C8  | 771 | table public.accounts: INSERT: id[integer]:1 balance[numeric]:100
--  0/1A2B4A0  | 771 | table public.accounts: INSERT: id[integer]:2 balance[numeric]:50
--  0/1A2B518  | 771 | table public.accounts: UPDATE: id[integer]:1 balance[numeric]:70
--  0/1A2B5F0  | 771 | COMMIT 771
-- (CREATE TABLE appears only as an empty BEGIN/COMMIT pair: DDL is not decoded)

SELECT count(*) FROM pg_logical_slot_get_changes('demo', NULL, NULL);   -- consumes them
SELECT pg_drop_replication_slot('demo');                                -- never leave test slots behind

The same stream can be consumed over the replication protocol from the shell:

pg_recvlogical -d shop --slot demo --create-slot --plugin test_decoding
pg_recvlogical -d shop --slot demo --start -f -        # stream changes to stdout until Ctrl+C

Measuring decoding cost

-- PG 14+: per-slot decoding statistics
SELECT slot_name,
       spill_txns,  pg_size_pretty(spill_bytes)  AS spilled,     -- transactions too big for memory
       stream_txns, pg_size_pretty(stream_bytes) AS streamed,    -- sent before commit (streaming on)
       total_txns,  pg_size_pretty(total_bytes)  AS decoded
FROM pg_stat_replication_slots;

Lots of spilling suggests raising logical_decoding_work_mem (default 64MB) or enabling streaming on subscriptions.

Slots across failover. Before PG 17, logical slots existed only on the primary; after a failover to a physical standby, every logical consumer had to be resynchronized. PG 16 allowed logical decoding from a standby, and PG 17 added failover slots: a logical slot created with failover = true is synchronized to standbys that set sync_replication_slots = on, so consumers can continue from the promoted standby (Change Data Capture Pipelines).

Key takeaways

• Logical decoding converts physical WAL into per-transaction row changes using historic catalog snapshots.
• The reorder buffer emits only committed transactions, in commit order; huge transactions spill to disk unless streaming is enabled.
• Output plugins format the stream: pgoutput for built-in replication and most tools, test_decoding for learning.
• Logical slots hold back WAL removal and catalog vacuum (catalog_xmin); abandoned ones are dangerous.
• Since PG 17, failover slots let logical consumers survive a physical failover.

🧪 Practice

1. (Beginner) Using test_decoding, decode a transaction that inserts, updates, and deletes rows in a table with REPLICA IDENTITY DEFAULT, then change it to FULL and compare the UPDATE and DELETE output.
2. (Intermediate) Set logical_decoding_work_mem = '1MB' in a session-independent way (ALTER SYSTEM and reload), run one transaction that inserts 1 million rows, consume the slot, and inspect pg_stat_replication_slots and the pg_replslot directory while it runs.
3. (Interview) "A logical replication subscriber shows hours of lag right after a nightly batch job, then catches up suddenly. Explain why." Hint: What does the reorder buffer do with a single huge transaction before its commit, and which subscription option changes that?

Selective Table Replication

Replicating everything is rarely the goal of logical replication. More often you want a specific slice: a few tables for a reporting database, one schema for a microservice that is being extracted, or the same tables from many shards merged into one place. Publications control this precisely, both in which tables are included and in which operations are sent.

Choosing tables

-- Explicit list: new tables are NOT added automatically
CREATE PUBLICATION reporting_pub FOR TABLE orders, order_items, customers;

-- Whole schemas (PG 15+): tables created later in the schema are included automatically
CREATE PUBLICATION sales_pub FOR TABLES IN SCHEMA sales;

-- Mix both (PG 15+)
CREATE PUBLICATION mixed_pub FOR TABLES IN SCHEMA sales, TABLE public.customers;

-- Everything in the database, including future tables
CREATE PUBLICATION all_pub FOR ALL TABLES;

FOR ALL TABLES and FOR TABLES IN SCHEMA require superuser, because they implicitly publish tables the creator may not own. Even when the publisher includes a new table automatically, the subscriber needs the table to exist and an ALTER SUBSCRIPTION ... REFRESH PUBLICATION to start copying it.

Choosing operations. The publish option limits which kinds of changes are sent. This turns logical replication into a tool for archiving:

-- The production database keeps 90 days of events and deletes older rows nightly.
-- The archive database should keep everything, so deletes must not be replicated.
CREATE PUBLICATION events_archive_pub FOR TABLE events
  WITH (publish = 'insert, update');           -- default: 'insert, update, delete, truncate'

Partitioned tables. By default, changes are published under each leaf partition's name, so the subscriber needs identically named partitions. With publish_via_partition_root, changes are published as if they came from the root table, so the subscriber can partition differently or not at all (6.4):

CREATE PUBLICATION metrics_pub FOR TABLE metrics            -- metrics is partitioned by month
  WITH (publish_via_partition_root = true);
-- Subscriber can have a single unpartitioned "metrics" table, or partitions by week

Common topologies

 Fan-out (distribution)                      Fan-in (consolidation)

          +--> analytics (orders, customers)     shard_eu  --+
 primary -+--> search_sync (products)            shard_us  --+--> central_reporting
          +--> billing (invoices)                shard_apac --+    (same tables, merged)

For fan-in, each shard publishes the same tables and the central database has one subscription per shard. Rows must not collide: use globally unique keys (UUIDs, or a composite key including a shard or region column).

-- Central reporting database: one subscription per source
CREATE SUBSCRIPTION from_eu   CONNECTION 'host=shard-eu dbname=shop user=repl ...'   PUBLICATION orders_pub;
CREATE SUBSCRIPTION from_us   CONNECTION 'host=shard-us dbname=shop user=repl ...'   PUBLICATION orders_pub;
CREATE SUBSCRIPTION from_apac CONNECTION 'host=shard-apac dbname=shop user=repl ...' PUBLICATION orders_pub;

One subscription, several publications. A subscription can list multiple publications from the same publisher, which lets you compose slices without one large publication:

CREATE SUBSCRIPTION analytics_sub
  CONNECTION 'host=publisher dbname=shop user=repl_shop'
  PUBLICATION reporting_pub, events_archive_pub;

Inspecting membership

-- Which tables does each publication contain right now?
SELECT pubname, schemaname, tablename FROM pg_publication_tables ORDER BY 1, 2, 3;

-- Publication options
SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete, pubtruncate, pubviaroot
FROM pg_publication;

Key takeaways

• Publish explicit tables, whole schemas (PG 15+), or all tables; explicit lists do not pick up new tables.
• The publish option filters by operation; skipping delete and truncate is a simple archiving pattern.
• publish_via_partition_root decouples the subscriber's partitioning from the publisher's.
• Fan-out and fan-in topologies are both straightforward; fan-in requires globally unique keys.
• New tables always need to exist on the subscriber and a REFRESH PUBLICATION.

🧪 Practice

1. (Beginner) Create a publication FOR TABLES IN SCHEMA sales, subscribe to it, then create a new table in sales on the publisher. List the steps needed before its rows appear on the subscriber.
2. (Intermediate) Build an archive: production keeps 7 days of events and deletes older rows; the archive keeps everything. Verify with row counts after running the delete job, and explain what happens if someone runs TRUNCATE events in production.
3. (Interview) "Three regional databases each have an orders table with a bigserial primary key. Management wants a single global orders table for reporting via logical replication. What breaks, and how would you fix it?" Hint: Key collisions, composite keys with a region column, UUIDs, and whether the subscriber table can have a different primary key from the publishers.

Row Filters and Column Lists

Choosing tables is often too coarse. A regional office should receive only its own customers. A data warehouse should receive users but not password hashes or email addresses. PostgreSQL 15 added row filters (WHERE clauses per table) and column lists to publications, so a publication can send exactly the rows and columns needed, and nothing else ever leaves the publisher.

Row filters

CREATE PUBLICATION eu_pub FOR TABLE
  customers WHERE (region = 'EU'),
  orders    WHERE (region = 'EU' AND status <> 'draft');

Restrictions on the expression:

• It can reference only columns of the table, constants, and immutable built-in operators and functions (no user-defined functions, no subqueries, no now()).
• If the publication publishes UPDATE or DELETE, every column in the filter must be part of the replica identity (for example, the primary key must include region, or the table must use REPLICA IDENTITY FULL). The publisher must be able to evaluate the filter on the old row too.

What happens when an UPDATE changes whether a row matches. The filter is evaluated on both the old and the new row version, and the change is transformed so the subscriber stays consistent:

Old row matches	New row matches	Sent to subscriber
No	No	Nothing
No	Yes	INSERT of the new row
Yes	No	DELETE of the old row
Yes	Yes	UPDATE

-- On the publisher, a customer moves from US to EU:
UPDATE customers SET region = 'EU' WHERE id = 77;
-- The EU subscriber receives an INSERT for customer 77; a US subscriber (filter region = 'US') receives a DELETE.

Column lists

-- Publish users without sensitive columns
CREATE PUBLICATION warehouse_pub FOR TABLE
  users (id, created_at, country, plan),       -- password_hash, email, phone stay on the publisher
  orders;                                       -- all columns

• For publications that publish UPDATE/DELETE, the column list must include the replica identity columns.
• The subscriber's table needs at least the listed columns; extra columns on the subscriber are left NULL or take their default.
• Columns added to the table later are not published until added to the list, which is a safe default for sensitive data.

Combining both, and changing them

CREATE PUBLICATION partner_pub FOR TABLE
  products (id, sku, name, price, stock) WHERE (is_public);

-- Change a filter or column list on a running publication
ALTER PUBLICATION partner_pub SET TABLE
  products (id, sku, name, price, stock, category) WHERE (is_public AND stock > 0);

Changing the definition affects only future changes. Rows already on the subscriber are not removed or backfilled: for example, rows that no longer match a new filter stay on the subscriber until they change. REFRESH PUBLICATION copies only tables that are new to the subscription, so to realign a table you re-add it: drop it from the publication, refresh the subscription, truncate it on the subscriber, add it back with the new definition, and refresh again, which re-copies it through the new filter.

Initial copy respects the filters. The tablesync workers copy only matching rows and listed columns, so a subscriber that should contain 2% of a 500 GB table copies about 10 GB.

Inspecting

SELECT pubname, tablename, attnames, rowfilter
FROM pg_publication_tables
WHERE pubname IN ('eu_pub', 'warehouse_pub');
--     pubname     | tablename |            attnames             |         rowfilter
-- ----------------+-----------+---------------------------------+---------------------------
--  eu_pub         | customers | {id,name,email,region}          | (region = 'EU'::text)
--  warehouse_pub  | users     | {id,created_at,country,plan}    |

Multiple publications for the same table. If a subscription includes several publications that publish the same table, row filters are combined with OR (and a publication without a filter means "all rows"). Different column lists for the same table across those publications are not supported; keep one definition per table per subscriber.

A note on security. Filters and column lists reduce what is sent, which is a real data-minimization control: excluded columns never leave the publisher. The replication role still needs SELECT on the table for the initial copy, though, so grant column-level privileges as well if the role could be misused (10.3).

Key takeaways

• Row filters (PG 15+) publish only matching rows; filter columns must be in the replica identity if updates or deletes are published.
• An UPDATE that moves a row into or out of a filter becomes an INSERT or DELETE on the subscriber.
• Column lists (PG 15+) keep sensitive or bulky columns on the publisher; they must include replica identity columns.
• Changing filters affects only future changes; existing subscriber data is not corrected automatically.
• The initial copy respects filters and lists, which can make it far smaller.

🧪 Practice

1. (Beginner) Publish customers WHERE (region = 'EU') and subscribe to it. Insert customers in several regions, then move one customer from US to EU and another from EU to US. Verify the subscriber after each step.
2. (Intermediate) Try to publish orders WHERE (status = 'paid') with default replica identity, then run an UPDATE. Read the error, and fix it in two different ways. Compare the WAL volume of both fixes with pg_current_wal_lsn() before and after a batch of updates.
3. (Interview) "Legal says personal data must not leave the EU, but the US analytics team needs order statistics from the EU database. How would you design this with logical replication?" Hint: Column lists that exclude personal columns, pseudonymous keys, row filters, privileges on the replication role, and what the initial copy reads.

Conflict Handling

A physical standby is read-only, so nothing can contradict the changes it replays. A logical subscriber is an ordinary writable database, and its own data can contradict incoming changes: a row with the same primary key already exists, a row to be updated has been deleted locally, or a column the publisher sends does not exist on the subscriber. These are conflicts, and core PostgreSQL resolves very few of them automatically. Most stop replication until a human intervenes.

Analogy. A clerk copies entries from head office's ledger into a branch ledger. If the branch ledger already has an entry with the same invoice number, the clerk does not guess which one is right; they stop and wait for a manager. Meanwhile, head office keeps sending pages, and they pile up on the clerk's desk (WAL on the publisher).

Conflict types (PG 18 names)

Conflict	Situation	Default behavior
insert_exists	Incoming INSERT violates a unique constraint on the subscriber	Error, apply stops
update_exists	Incoming UPDATE would create a duplicate unique key	Error, apply stops
multiple_unique_conflicts	Incoming row violates several unique constraints	Error, apply stops
update_missing	Row to update does not exist on the subscriber	Change skipped, logged
delete_missing	Row to delete does not exist on the subscriber	Change skipped, logged
update_origin_differs	Row to update was last modified by a different origin (for example, a local write)	Change applied, logged (needs track_commit_timestamp = on)
delete_origin_differs	Same, for DELETE	Change applied, logged

Before PG 18, the "missing" and "origin differs" cases were silently handled; PG 18 logs them and counts them in pg_stat_subscription_stats. Other errors stop replication the same way: check constraint failures, foreign key violations, type conversion errors, or a missing column.

What an error looks like

ERROR:  conflict detected on relation "public.customers": conflict=insert_exists
DETAIL:  Key already exists in unique index "customers_pkey", modified locally in transaction 740 at 2026-10-06 10:47:04.727+00.
         Key (id)=(1001); existing local row (1001, 'Ada', 'EU'); remote row (1001, 'Ada L.', 'EU').
CONTEXT:  processing remote data for replication origin "pg_16395" during message type "INSERT" for replication target relation "public.customers" in transaction 725, finished at 0/14C0378
LOG:  background worker "logical replication apply worker" (PID 4182) exited with exit code 1

Without disable_on_error, the apply worker restarts a few seconds later, retries the same transaction, fails again, and loops forever. The slot on the publisher stops advancing, so WAL accumulates on the publisher until someone notices.

Resolving a stuck subscription

-- 1. See errors per subscription (PG 15+) and conflict counters (PG 18+)
SELECT subname, apply_error_count, sync_error_count,
       confl_insert_exists, confl_update_missing, confl_delete_missing
FROM pg_stat_subscription_stats;

-- 2a. Preferred: fix the data on the subscriber so the transaction can apply
DELETE FROM customers WHERE id = 1001;             -- if the local row is the wrong one
-- the apply worker retries automatically and succeeds

-- 2b. Or: skip the whole failing remote transaction (PG 15+), using the "finished at" LSN from the log
ALTER SUBSCRIPTION shop_sub SKIP (lsn = '0/14C0378');
-- Skipping discards ALL changes of that transaction; the subscriber is now missing them.

-- 3. If the subscription was disabled by disable_on_error, re-enable it
ALTER SUBSCRIPTION shop_sub ENABLE;

Schema drift conflicts. Because DDL is not replicated, schema changes must be ordered so that the subscriber can always accept what the publisher sends:

Change	Safe order
Add a column	Subscriber first, then publisher
Drop a column	Publisher first (stop sending it), then subscriber
Add a NOT NULL or check constraint	Publisher first (data is valid there), then subscriber
Widen a type (int to bigint)	Subscriber first

ERROR:  logical replication target relation "public.orders" is missing replicated column: "discount_code"

Preventing conflicts

• Treat subscriber tables as read-only for applications: revoke INSERT/UPDATE/DELETE from application roles on replicated tables.
• Use globally unique keys (UUIDs) where several sources write to the same table.
• Understand triggers. The apply worker runs with session_replication_role = replica, so ordinary triggers do not fire on the subscriber. Mark the ones that must fire with ALTER TABLE ... ENABLE ALWAYS TRIGGER or ENABLE REPLICA TRIGGER.
• Enable disable_on_error so a conflict stops cleanly and alerts, instead of looping silently.
• Alert on apply_error_count increases, disabled subscriptions (pg_subscription.subenabled = false), and the publisher's slot lag.

Key takeaways

• Unique-key conflicts and most other errors stop the apply worker; missing rows on update or delete are skipped (and logged since PG 18).
• A stuck subscription retains WAL on the publisher, turning a data problem into a disk problem.
• Fix the subscriber's data when possible; ALTER SUBSCRIPTION ... SKIP discards an entire remote transaction.
• Order DDL so that the subscriber can always accept incoming rows; DDL is never replicated.
• Prevent conflicts by keeping subscriber tables read-only, using unique keys across sources, and alerting on errors.

🧪 Practice

1. (Beginner) Create an insert_exists conflict by inserting a row directly on the subscriber and then the same key on the publisher. Find the error in the log and resolve it by fixing the subscriber's data.
2. (Intermediate) Repeat with disable_on_error = true, and resolve it with ALTER SUBSCRIPTION ... SKIP. Show which changes were lost by comparing both sides with EXCEPT queries over dblink or postgres_fdw.
3. (Interview) "Logical replication stopped three days ago and nobody noticed. What do you check, what damage might have happened on both sides, and how do you make sure it is noticed in minutes next time?" Hint: Subscriber logs and pg_stat_subscription_stats, publisher slot and disk, max_slot_wal_keep_size, whether the slot is still valid, and alerting on lag and disabled subscriptions.

Change Data Capture Pipelines

Applications often need to keep other systems consistent with the database: a search index, a cache, a data warehouse, other microservices. The obvious approach, having the application write to the database and to Kafka or Elasticsearch, is the dual-write problem: if the second write fails or the process crashes between the two, the systems diverge, and no transaction spans both. Change data capture (CDC) solves it by reading committed changes from the database's own log. If it is in the WAL, it was committed, and it will be delivered.

Analogy. Instead of asking every cashier to also phone the accounting office after each sale (and hoping they never forget), the accounting office reads the till rolls. The till roll is the single source of truth.

Typical architecture

 +-------------+   logical slot     +-------------------+     +--------+     +-----------------------+
 | PostgreSQL  |   (pgoutput)       | Debezium          |     | Kafka  | --> | search indexer        |
 | publication |------------------->| (Kafka Connect)   |---->| topics | --> | cache invalidator     |
 |  dbz_pub    |<-------------------| tracks LSN offsets|     |        | --> | warehouse loader      |
 +-------------+  confirmed LSN     +-------------------+     +--------+     +-----------------------+

Database side

-- wal_level = logical (restart), then a dedicated role and publication
CREATE ROLE debezium WITH LOGIN REPLICATION PASSWORD 'change-me';
GRANT SELECT ON orders, customers, outbox TO debezium;       -- for the initial snapshot
CREATE PUBLICATION dbz_pub FOR TABLE orders, customers, outbox;

-- Full "before" images for updates and deletes, if consumers need old values
ALTER TABLE orders REPLICA IDENTITY FULL;

Connector configuration (Debezium PostgreSQL connector)

{
  "name": "shop-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "plugin.name": "pgoutput",
    "database.hostname": "db1.internal",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "${file:/secrets/db.properties:password}",
    "database.dbname": "shop",
    "topic.prefix": "shop",
    "slot.name": "debezium_shop",
    "publication.name": "dbz_pub",
    "publication.autocreate.mode": "disabled",
    "table.include.list": "public.orders,public.customers,public.outbox",
    "snapshot.mode": "initial",
    "heartbeat.interval.ms": "10000",
    "heartbeat.action.query": "UPDATE debezium_heartbeat SET ts = now() WHERE id = 1"
  }
}

Each row change becomes an event with the old and new row and its position in the WAL:

{
  "op": "u",
  "before": { "id": 42, "status": "paid",    "total": 99.50 },
  "after":  { "id": 42, "status": "shipped", "total": 99.50 },
  "source": { "table": "orders", "lsn": 734012345, "txId": 918944, "ts_ms": 1791297002118 }
}

The transactional outbox pattern. Publishing raw table changes couples consumers to your schema. The outbox pattern publishes deliberate business events instead: the application writes its business rows and an event row in the same transaction, and CDC streams only the outbox table.

CREATE TABLE outbox (
  id             uuid PRIMARY KEY DEFAULT uuidv7(),   -- PG 18; gen_random_uuid() on older versions
  aggregate_type text  NOT NULL,                      -- e.g. 'order'; used to route to a topic
  aggregate_id   text  NOT NULL,
  event_type     text  NOT NULL,
  payload        jsonb NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now()
);

BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 42;
INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
VALUES ('order', '42', 'OrderShipped', '{"orderId": 42, "carrier": "DHL"}');
COMMIT;            -- both or neither: no dual-write gap

-- Rows can be deleted right away; the INSERT is already in the WAL and will be captured
DELETE FROM outbox WHERE created_at < now() - interval '1 hour';

Writing a minimal consumer. For simpler needs, a small program can consume a slot directly. This sketch uses psycopg2 with the wal2json plugin:

import json
import psycopg2
import psycopg2.extras

conn = psycopg2.connect(
    "host=db1 dbname=shop user=cdc_reader",
    connection_factory=psycopg2.extras.LogicalReplicationConnection,  # speaks the replication protocol
)
cur = conn.cursor()
try:
    cur.create_replication_slot("cdc_demo", output_plugin="wal2json")
except psycopg2.errors.DuplicateObject:
    pass                                             # slot exists: resume from its confirmed position

cur.start_replication(slot_name="cdc_demo", decode=True,
                      options={"format-version": "2"})  # wal2json v2: one JSON object per change

def handle(msg):
    change = json.loads(msg.payload)
    if change.get("action") in ("I", "U", "D"):
        index_into_search(change)                    # your handler; must be idempotent (at-least-once delivery)
    # Confirm only AFTER processing; the slot then frees WAL up to this point
    msg.cursor.send_feedback(flush_lsn=msg.data_start)

cur.consume_stream(handle)                           # blocks; sends keepalives automatically

Operational risks

Risk	What happens	Mitigation
Consumer down for hours	Slot retains WAL; pg_wal grows	max_slot_wal_keep_size, alert on slot lag, runbook to drop and resnapshot
Low-traffic database on a busy cluster	WAL from other databases piles up because the slot's position only advances when its database has changes	Heartbeat writes (heartbeat.action.query)
Primary failover	Logical slot missing on the new primary (before PG 17)	PG 17+ failover slots (failover = true, sync_replication_slots = on on standbys, synchronized_standby_slots on the primary); otherwise resnapshot
Duplicate delivery after a crash	Consumer reprocesses events since the last confirmed LSN	Idempotent consumers, keyed upserts
Schema changes	Consumers break on renamed or dropped columns	Outbox pattern, schema registry, additive changes
Unchanged TOAST columns	Large values not changed by an UPDATE are not included in the event	REPLICA IDENTITY FULL or consumer-side merging

Key takeaways

• CDC reads committed changes from the WAL through a logical slot, eliminating dual writes.
• Debezium with pgoutput is the common choice; a small custom consumer is possible for simple needs.
• The outbox pattern publishes explicit business events in the same transaction as the data change.
• Delivery is at-least-once; consumers must be idempotent and confirm positions only after processing.
• Watch slot lag, use heartbeats on quiet databases, and use failover slots (PG 17+) so CDC survives a failover.

🧪 Practice

1. (Beginner) Using pg_recvlogical and wal2json (or test_decoding), stream changes from one table to a file. Stop the consumer for a while, generate changes, and restart it: show that nothing was lost.
2. (Intermediate) Implement the outbox pattern for an orders table and write a consumer that prints OrderShipped events exactly once even when the consumer is killed and restarted mid-stream. Explain where you store the processed-event IDs.
3. (Interview) "The data team wants to stream changes from production into Kafka with Debezium. What has to change on the database, and what are the operational risks?" Hint: wal_level and the restart, the publication and role, replica identity, slot retention when the connector stops, heartbeats, failover slots, and monitoring.

Bidirectional Replication Considerations

If logical replication can send changes from A to B, why not also from B to A, so that both servers accept writes? This bidirectional (or multi-master, active-active) setup is attractive for multi-region applications that want local writes everywhere, and for migrations that need an instant rollback path. It is also one of the most failure-prone designs in database engineering, because two servers that accept writes independently will eventually disagree.

Analogy. Two editors work on separate copies of the same document and mail each other their changes. If they edit different paragraphs, merging is trivial. If they both rewrite the same sentence on the same afternoon, someone's work is lost, and the mail system cannot know whose version is "right".

Problem 1: infinite loops. A change made on A is applied on B, which, as a change on B, would be sent back to A, and so on. Since PG 16, a subscription can ignore changes that did not originate on the publisher itself:

-- On node A
CREATE PUBLICATION pub_a FOR TABLE customers;
-- On node B
CREATE PUBLICATION pub_b FOR TABLE customers;

-- On node B: receive A's local changes only
CREATE SUBSCRIPTION sub_b_from_a
  CONNECTION 'host=node-a dbname=app user=repl'
  PUBLICATION pub_a
  WITH (origin = none,        -- skip changes that A itself received via replication (PG 16+)
        copy_data = true);    -- B starts empty and copies A's data

-- On node A: receive B's local changes only; data already exists on A
CREATE SUBSCRIPTION sub_a_from_b
  CONNECTION 'host=node-b dbname=app user=repl'
  PUBLICATION pub_b
  WITH (origin = none,
        copy_data = false);   -- copying back would duplicate every row

 Without origin filtering:                 With origin = none:
 A --(row 1)--> B --(row 1)--> A --> ...   A --(row 1, origin A)--> B     B does not resend row 1

Problem 2: write conflicts. Core PostgreSQL has no conflict resolution between nodes. Concurrent writes produce errors or silent divergence:

 time   node A                                 node B
 t0     UPDATE customers SET plan='pro'        UPDATE customers SET plan='free'
        WHERE id = 7;   COMMIT                 WHERE id = 7;   COMMIT
 t1     receives B's update: plan='free'       receives A's update: plan='pro'
 result plan = 'free'                          plan = 'pro'           <-- nodes disagree permanently

Both updates apply (each is logged as update_origin_differs in PG 18), and the nodes end up with different values without any error.

Problem 3: uniqueness and identity. A unique constraint is checked only on the local node, so two nodes can each insert email = 'ada@example.com', and replication then fails with insert_exists on both. Sequences generate overlapping values unless separated:

-- Interleaved sequences: node A generates odd ids, node B even ids
-- node A:
CREATE SEQUENCE orders_id_seq START 1 INCREMENT 2;
-- node B:
CREATE SEQUENCE orders_id_seq START 2 INCREMENT 2;
-- Simpler and more robust: uuidv7() primary keys (PG 18) or a node column in a composite key

Problem 4: everything that is not replicated. DDL must be applied to both nodes in a careful order, sequences are not synchronized, and cascading foreign key actions or triggers can fire on both sides.

Making it workable

Strategy	How it avoids conflicts
Write ownership by partition	Each tenant, region, or key range is written on exactly one node; other nodes only read it. Conflicts become impossible by design
Insert-only or append-only tables	No updates means no update conflicts; unique keys via UUIDs
Single writer at a time	Used for migrations: both directions run, but the application writes to one node; switching back is instant
Specialized products	EDB Postgres Distributed (formerly BDR) and pgEdge add conflict resolution (for example, last-update-wins with commit timestamps), DDL replication, and global sequences

When to avoid it. Last-update-wins depends on synchronized clocks and silently discards the losing write, which is unacceptable for balances, inventory, or anything with invariants spanning rows. If the actual requirement is "survive the loss of a server", a single primary with physical standbys and automatic failover (12.3) is far simpler. If it is "low write latency in several regions", partitioning data by region (each region owns its rows) usually achieves it without true multi-master writes.

Key takeaways

• Bidirectional logical replication requires loop prevention (origin = none, PG 16+) and copy_data = false in one direction.
• Core PostgreSQL detects but does not resolve multi-node conflicts; concurrent updates to the same row can leave nodes permanently different.
• Unique constraints are only local; use UUIDs or partitioned key spaces, and apply DDL to all nodes deliberately.
• Design write ownership so each row is written on one node; use specialized products only when true multi-master is unavoidable.
• Prefer single-primary HA unless there is a concrete need that it cannot meet.

🧪 Practice

1. (Beginner) Set up two databases with bidirectional replication of one table using origin = none. Insert different rows on each side and verify that both converge. Then remove origin = none from one subscription in a fresh setup and describe what happens.
2. (Intermediate) Produce a divergence by updating the same row on both nodes within the same second. Detect it with a query comparing both tables via postgres_fdw, and enable track_commit_timestamp to see how PG 18 reports the conflict.
3. (Interview) "Our product needs to accept writes in Europe and the US with low latency. A colleague proposes bidirectional logical replication between two primaries. What questions do you ask, and what would you propose?" Hint: Which data is truly shared, row ownership by region, conflict semantics for each table, global uniqueness, failure and partition behavior, and alternatives such as regional sharding.

<a id="123-high-availability-architecture"></a>

12.3 High Availability Architecture

Replication gives you copies of the data; high availability (HA) is the machinery around those copies that decides when the primary has failed, promotes the right standby without ever creating two primaries, and redirects clients to the new primary, all within seconds; this subchapter covers planned and unplanned role changes, the tools that automate them, the fencing that keeps them safe, how clients find the primary, and how to choose an availability target.

Failover vs. Switchover

A standby is only useful for availability if it can become the primary. There are two very different situations in which that happens:

• A switchover is planned: both servers are healthy, and you move the primary role on purpose, for example to patch the operating system of the primary, to move to new hardware, or to test the procedure. Nothing is lost, and the old primary becomes a standby of the new one.
• A failover is unplanned: the primary has crashed or is unreachable, and a standby is promoted in its place. If replication was asynchronous, the last transactions committed on the old primary may be missing, and the old primary cannot simply rejoin because its history has diverged.

Analogy. A switchover is a pilot handing control to the co-pilot during cruise: "your aircraft", "my aircraft", with both fully aware. A failover is the co-pilot taking over because the pilot is unresponsive; it must happen fast, and the co-pilot must be very sure the pilot is truly out before grabbing the controls.

	Switchover	Failover
Trigger	Operator decision	Primary failure detected
Old primary	Shut down cleanly first	Crashed, hung, or isolated
Data loss	None (standby has replayed everything)	Possible with asynchronous replication
Old primary afterwards	Rejoins as a standby directly	Must be rewound (pg_rewind) or rebuilt
Downtime	Seconds	Detection time + promotion + client reconnection
Main risk	Small, if the procedure is followed	Split brain: two primaries (Split-Brain Prevention and Fencing)

Timelines. Every promotion starts a new timeline, a numbered branch of WAL history. The new primary writes a small history file recording where it branched off:

 timeline 1:  ...--A--B--C--D--E        old primary (wrote D and E before crashing; standby only had up to C)
                           \
 timeline 2:                C--X--Y--Z  new primary, promoted at C

 00000002.history:  1   0/3000000   no recovery target specified

Standbys with recovery_target_timeline = 'latest' (the default) follow the new timeline automatically. The old primary is on timeline 1 with changes D and E that do not exist on timeline 2, so it cannot stream from the new primary until those are undone.

A manual switchover, step by step

# 0. On the primary: confirm the target standby is streaming and caught up
psql -h db1 -c "SELECT application_name, state, replay_lag FROM pg_stat_replication;"

# 1. Stop application writes (maintenance mode, or pause the connection pooler: PAUSE in PgBouncer)

# 2. Cleanly stop the old primary. A fast shutdown waits until walsenders have sent
#    all WAL, including the final shutdown checkpoint, to connected standbys.
sudo systemctl stop postgresql@17-main                       # on db1

# 3. Verify the standby has everything: its replay position must match the old primary's
#    final checkpoint location
sudo -u postgres /usr/lib/postgresql/17/bin/pg_controldata /var/lib/postgresql/17/main | grep 'Latest checkpoint location'   # on db1
psql -h db2 -c "SELECT pg_last_wal_replay_lsn();"            # should equal or exceed it

# 4. Promote the standby
psql -h db2 -c "SELECT pg_promote();"

# 5. Turn the old primary into a standby of the new one and start it
sudo -u postgres touch /var/lib/postgresql/17/main/standby.signal           # on db1
# set primary_conninfo = 'host=db2 ...' in postgresql.auto.conf, then:
sudo systemctl start postgresql@17-main

# 6. Point clients at db2 (VIP, DNS, proxy) and resume traffic

A manual failover, step by step

# 1. Decide that db1 is really dead, not just slow or unreachable from your laptop.
#    Fence it so it cannot accept writes even if it comes back (power off, block network, detach storage).

# 2. Pick the most advanced standby: the highest received LSN loses the least data
psql -h db2 -Atc "SELECT pg_last_wal_receive_lsn();"     # 2B/9F000120
psql -h db3 -Atc "SELECT pg_last_wal_receive_lsn();"     # 2B/9E7FF000   -> promote db2

# 3. Promote it, then repoint the remaining standbys to it (they follow the new timeline)
psql -h db2 -c "SELECT pg_promote();"
psql -h db3 -c "ALTER SYSTEM SET primary_conninfo = 'host=db2 user=replicator application_name=db3'" \
            -c "SELECT pg_reload_conf();"

# 4. Redirect clients

Bringing the old primary back with pg_rewind. Rebuilding a multi-terabyte server with pg_basebackup takes hours. pg_rewind instead copies only the blocks that changed since the timelines diverged, which usually takes minutes. It requires wal_log_hints = on or data checksums on the old primary, set before the failure.

# On db1 (old primary), with PostgreSQL stopped
/usr/lib/postgresql/17/bin/pg_rewind \
  --target-pgdata=/var/lib/postgresql/17/main \
  --source-server='host=db2 port=5432 user=postgres dbname=postgres' \
  --write-recovery-conf \
  --progress
# pg_rewind: servers diverged at WAL location 2B/9F000120 on timeline 1
# pg_rewind: rewinding from last common checkpoint at 2B/9E000060 on timeline 1
# pg_rewind: Done!
sudo systemctl start postgresql@17-main       # starts as a standby of db2 thanks to --write-recovery-conf

The transactions D and E from the diagram are discarded by the rewind. If they matter, extract them from the old primary's WAL with pg_waldump before rewinding.

Key takeaways

• A switchover is a planned, lossless role swap; a failover is an emergency promotion that may lose recent asynchronous commits.
• Promotion creates a new timeline; standbys follow it automatically, but the old primary has diverged.
• For a switchover, stop the old primary cleanly and confirm the standby has its final checkpoint before promoting.
• For a failover, fence the old primary first, then promote the standby with the highest received LSN.
• pg_rewind (with wal_log_hints or checksums) resynchronizes an old primary in minutes instead of a full rebuild.

🧪 Practice

1. (Beginner) With a primary and a standby on one machine, perform the switchover procedure above and then switch back. At each step, record each server's pg_is_in_recovery() and timeline (pg_control_checkpoint()).
2. (Intermediate) Simulate a failover: kill the primary with kill -9 while pgbench is running, promote the standby, then rejoin the old primary using pg_rewind. Count how many pgbench transactions reported as committed are missing on the new primary.
3. (Interview) "Walk me through what you would do in the first ten minutes after monitoring reports the primary database is unreachable." Hint: Confirm from more than one vantage point, check whether automation already acted, fence, choose the standby, promote, redirect clients, and communicate. What must you never do first?

Automatic Failover with Patroni

Manual failover needs a human to notice, decide, and act, which takes at least several minutes and often far longer at night. Automating it sounds easy ("if the primary does not answer, promote a standby"), but naive automation is dangerous: a network glitch between the monitor and the primary would promote a second primary while the first is still accepting writes. Safe automation needs a source of truth that all nodes agree on. Patroni, the most widely used open-source HA manager for PostgreSQL, gets that from a distributed configuration store (DCS) such as etcd, Consul, ZooKeeper, or the Kubernetes API.

Analogy. A team decides who holds the office keys by a sign-up sheet kept by a strict secretary (the DCS). The keyholder must re-sign every 10 seconds. If they stop signing for 30 seconds, the secretary lets the next qualified person sign, and only one person can ever be signed in. A keyholder who cannot reach the secretary hands back the keys voluntarily rather than risk two keyholders.

Architecture

            +----------------------- etcd cluster (3 or 5 members, separate failure domains) -----------------+
            |   /service/shop/leader   = "db1"   (TTL 30 s, renewed every loop_wait)                           |
            |   /service/shop/members/ = db1, db2, db3 with their state, LSN, tags                             |
            |   /service/shop/config   = cluster-wide PostgreSQL and Patroni settings                          |
            +------------^------------------------^-----------------------------^------------------------------+
                         | update leader key       | watch / report             | watch / report
                 +-------+--------+        +-------+--------+           +-------+--------+
                 | Patroni (db1)  |        | Patroni (db2)  |           | Patroni (db3)  |
                 | REST API :8008 |        | REST API :8008 |           | REST API :8008 |
                 | PostgreSQL     |------->| PostgreSQL     |           | PostgreSQL     |
                 | PRIMARY        |------------------------------------>| STANDBY        |
                 +----------------+        | STANDBY        |           +----------------+
                                           +----------------+

Patroni runs next to PostgreSQL on every node and fully controls it: it starts, stops, configures, promotes, and demotes the local server. Every loop_wait seconds:

1. The leader renews the leader key in the DCS. If the renewal fails for longer than retry_timeout, it demotes itself to read-only, because it can no longer prove it is the only leader.
2. Replicas check the leader key. If it has expired (the leader has not renewed it for ttl seconds), each healthy replica checks whether it is a good candidate (not too far behind, not tagged nofailover, the most advanced among reachable members) and tries to create the key. The DCS guarantees only one succeeds.
3. The winner promotes its PostgreSQL; the others reconfigure themselves to stream from it. When the old primary returns, Patroni rewinds or rebuilds it as a replica.

Configuration

# /etc/patroni/patroni.yml on db1 (db2 and db3 differ in name and addresses)
scope: shop                       # cluster name; the DCS key prefix
name: db1                         # this member's name

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.10:8008

etcd3:
  hosts: 10.0.9.1:2379,10.0.9.2:2379,10.0.9.3:2379

bootstrap:
  dcs:                            # written to the DCS once, then shared by all members
    ttl: 30                       # leader key lifetime in seconds
    loop_wait: 10                 # seconds between HA loop iterations
    retry_timeout: 10             # how long to retry DCS and PostgreSQL operations before giving up
    maximum_lag_on_failover: 1048576    # bytes; a replica further behind may not be promoted
    synchronous_mode: false       # true = Patroni manages synchronous_standby_names (zero-loss failover)
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        wal_log_hints: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        max_slot_wal_keep_size: 100GB

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.10:5432
  data_dir: /var/lib/postgresql/17/main
  bin_dir: /usr/lib/postgresql/17/bin
  authentication:
    replication: { username: replicator, password: change-me }
    superuser:   { username: postgres,   password: change-me }

watchdog:
  mode: required                  # see Split-Brain Prevention and Fencing
  device: /dev/watchdog

tags:
  nofailover: false               # true = never promote this node (e.g. a reporting replica)
  noloadbalance: false

Operating the cluster

patronictl -c /etc/patroni/patroni.yml list
# + Cluster: shop (7312345678901234567) ----+-----------+----+-----------+
# | Member | Host      | Role    | State     | TL | Lag in MB |
# +--------+-----------+---------+-----------+----+-----------+
# | db1    | 10.0.1.10 | Leader  | running   |  3 |           |
# | db2    | 10.0.1.11 | Replica | streaming |  3 |         0 |
# | db3    | 10.0.2.11 | Replica | streaming |  3 |         0 |
# +--------+-----------+---------+-----------+----+-----------+

# Planned switchover (Patroni stops the leader cleanly, promotes the candidate, rejoins the old leader)
patronictl -c /etc/patroni/patroni.yml switchover shop --leader db1 --candidate db2 --force

# Change a PostgreSQL parameter cluster-wide (stored in the DCS, applied by every member)
patronictl -c /etc/patroni/patroni.yml edit-config shop -p 'work_mem=64MB'
patronictl -c /etc/patroni/patroni.yml restart shop --pending     # restart members that need it

# Maintenance mode: Patroni stops making automatic decisions (for manual work on PostgreSQL)
patronictl -c /etc/patroni/patroni.yml pause shop
patronictl -c /etc/patroni/patroni.yml resume shop

Never change postgresql.conf by hand or use pg_ctl on a Patroni-managed node; Patroni will overwrite or fight the change.

The REST API lets load balancers and scripts ask each node about its role:

Endpoint	Returns 200 when
GET /primary (or /leader)	This node is the running leader
GET /replica	This node is a running replica (optionally ?lag=16MB to exclude laggards)
GET /read-only	Node can serve reads (leader or replica)
GET /sync	Node is a synchronous standby
GET /health	PostgreSQL is running

curl -s -o /dev/null -w '%{http_code}\n' http://10.0.1.10:8008/primary   # 200 on the leader, 503 elsewhere

How fast is it? With ttl: 30 and loop_wait: 10, a crashed primary is replaced in roughly 30 to 40 seconds: the leader key must expire, then the next loop iteration on a replica acquires it and promotes. Lower values give faster failover but more false positives from short network blips or DCS hiccups; the rule loop_wait + 2 * retry_timeout <= ttl must hold.

Zero-loss mode. With synchronous_mode: true, Patroni sets synchronous_standby_names itself and only promotes a replica that was synchronous at the time of failure, so no acknowledged commit is lost. If the synchronous replica fails, Patroni picks another or, unless synchronous_mode_strict is on, temporarily falls back to asynchronous replication to keep writes flowing.

Key takeaways

• Patroni runs on each node, controls PostgreSQL completely, and uses a DCS (etcd, Consul, ZooKeeper, Kubernetes) as the single source of truth for who is leader.
• The leader key has a TTL; a leader that cannot renew it demotes itself, and only one replica can acquire an expired key.
• Failover candidates must be within maximum_lag_on_failover and not tagged nofailover; synchronous_mode gives zero-loss failover.
• Use patronictl for switchovers, configuration changes, restarts, and maintenance mode; do not manage Patroni nodes by hand.
• The REST API (/primary, /replica) drives load balancer health checks.

🧪 Practice

1. (Beginner) Run a three-node Patroni cluster with a three-node etcd using containers. Run patronictl list, perform a switchover, and observe the timeline number change.
2. (Intermediate) Stop the leader's Patroni process with kill -STOP (simulating a hang) and time how long until a new leader is elected. Then block only the leader's access to etcd with iptables and observe what the old leader does to its own PostgreSQL. Explain the difference.
3. (Interview) "Why does Patroni need etcd? Couldn't the two database nodes simply check each other's health?" Hint: Think about a network partition between exactly two nodes, who can break the tie, and why the DCS itself needs an odd number of members.

repmgr and Alternative Tools

Patroni is the most common choice for self-managed clusters, but it is not the only approach, and the alternatives differ in a fundamental way: how they decide that the primary is dead and that it is safe to promote. Knowing the landscape helps you maintain existing systems and pick the right tool for a new one.

repmgr. A suite from EDB (originally 2ndQuadrant) consisting of a command-line tool for cloning, registering, and switching over nodes, and a daemon (repmgrd) that monitors the primary and performs automatic failover. It stores cluster metadata in a repmgr database that is itself replicated, and it does not use an external consensus store.

# /etc/repmgr.conf on db2
node_id=2
node_name='db2'
conninfo='host=10.0.1.11 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/postgresql/17/main'
location='dc1'                                   # used to avoid promoting in an isolated location
failover='automatic'
promote_command='/usr/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='/usr/bin/repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n'

# On the primary
repmgr -f /etc/repmgr.conf primary register

# On a new standby: clone from the primary, start it, then register
repmgr -h 10.0.1.10 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone
sudo systemctl start postgresql@17-main
repmgr -f /etc/repmgr.conf standby register

repmgr -f /etc/repmgr.conf cluster show
#  ID | Name | Role    | Status    | Upstream | Location | Priority | Timeline
# ----+------+---------+-----------+----------+----------+----------+----------
#  1  | db1  | primary | * running |          | dc1      | 100      | 1
#  2  | db2  | standby |   running | db1      | dc1      | 100      | 1
#  3  | db3  | standby |   running | db1      | dc2      | 100      | 1

# Planned switchover, with the other standbys following the new primary
repmgr -f /etc/repmgr.conf standby switchover --siblings-follow

# Rejoin a failed old primary, rewinding it if necessary
repmgr -f /etc/repmgr.conf node rejoin -d 'host=db2 dbname=repmgr user=repmgr' --force-rewind

Without a consensus store, repmgrd relies on heuristics: standbys vote among themselves, a witness server in a third location helps break ties, and location prevents a standby that has lost contact with everything from promoting itself. This is simpler to deploy but gives weaker split-brain guarantees than a lease-based design, so fencing (next topic) becomes your responsibility.

Other options

Tool	Decision mechanism	Notes
Patroni	DCS leader lease (etcd, Consul, ZooKeeper, Kubernetes API)	Most widely used; flexible; needs a DCS to operate
repmgr	Standby voting, witness node, location rules	No DCS; strong tooling for cloning and switchover; weaker split-brain protection
pg_auto_failover	Central monitor node (itself a PostgreSQL) with state machine	Simple to learn; the monitor must be made reliable separately
Pacemaker + Corosync with the PAF resource agent	Cluster quorum plus STONITH fencing	Classic Linux HA; robust fencing, complex to operate
CloudNativePG (Kubernetes operator)	Kubernetes API leases, operator-driven	Native Kubernetes resources; backups and failover built in (13.4)
Managed services (RDS Multi-AZ, Aurora, Cloud SQL HA, Azure Flexible Server)	Provider's control plane	No tool to run; less control, provider-specific failover behavior and timing

Choosing

• Running on VMs or bare metal, new cluster: Patroni with a three-node etcd is the default recommendation.
• On Kubernetes: a mature operator (CloudNativePG, or Patroni-based operators) rather than hand-built StatefulSets.
• Existing repmgr cluster that works: keep it, but add a witness node, fencing, and tested runbooks.
• Little operations capacity: a managed service; the HA problem becomes the provider's.

Key takeaways

• repmgr provides cloning, registration, switchover, and automatic failover (repmgrd) without a consensus store.
• Tools differ mainly in how they decide it is safe to promote: DCS lease, voting heuristics, monitor node, or cluster quorum with fencing.
• Patroni is the common default on servers; operators like CloudNativePG on Kubernetes; managed services when you prefer not to run HA yourself.
• Whatever the tool, test switchover and failover regularly and understand its split-brain guarantees.

🧪 Practice

1. (Beginner) Build a primary and two standbys with repmgr, run cluster show, and perform a standby switchover --siblings-follow.
2. (Intermediate) Enable repmgrd, then isolate the primary from both standbys with firewall rules while it remains reachable by clients. Observe what repmgr does, and how a witness node in a third location changes the outcome.
3. (Interview) "Compare Patroni and repmgr for a company with two data centers. Which failure scenarios does each handle well?" Hint: Where the DCS or witness lives, a partition between data centers, which side should keep the primary, and fencing.

Split-Brain Prevention and Fencing

Split brain is the worst failure an HA system can cause: two servers both act as primary and accept writes at the same time. Each side's data is internally consistent, but the two histories cannot be merged automatically, so recovering means deciding which writes to keep and reconciling the rest by hand. Every HA design must make split brain impossible, not just unlikely.

How it happens

           before                                  network partition
 clients ---> db1 (primary)              clients A ---> db1 (primary)      |  clients B ---> db2 (PROMOTED)
               |                                       "standby is gone,   |   "primary is gone,
               v                                        keep serving"      |    take over"
             db2 (standby)                                                 |
                                         both accept writes: orders 1001-1050 exist twice, with different contents

The root cause is that a node cannot distinguish "the other node is dead" from "I cannot reach the other node". Any safe design therefore relies on two ideas: quorum (decide by majority, so at most one side of a partition can act) and fencing (make sure the old primary cannot accept writes, rather than hoping it won't).

Analogy. Two people are told "if the other person stops answering the radio, take command". If the radio breaks, both take command. Quorum adds a third party that both must reach to take command; fencing means that whoever takes command first locks the other one's office door.

Quorum. A decision needs a majority of voters. With three voters, a partition leaves at most one side with two; the other side, with one, knows it is in the minority and must stand down. With two voters, neither side has a majority, which is why a two-node cluster cannot fail over safely without an external tiebreaker.

Voters	Tolerated voter failures	Notes
2	0	Any partition leaves no majority
3	1	Minimum for automatic failover
5	2	For higher resilience; more latency for each decision

Place voters in three separate failure domains (three availability zones, or two data centers plus a small tiebreaker elsewhere). Three voters in one rack protect against nothing that takes out the rack.

Fencing mechanisms

Mechanism	How it works	Strength
Self-demotion (lease expiry)	A primary that cannot renew its lease in the DCS restarts itself read-only	Good, if the node is still functioning enough to notice
Watchdog	A hardware or kernel timer (/dev/watchdog) reboots the node unless the HA agent keeps resetting it; Patroni arms it to fire before its lease expires	Covers a hung agent or a frozen VM
STONITH ("shoot the other node in the head")	Power off the old primary through IPMI, a PDU, or the hypervisor or cloud API before promoting	Strongest: a node without power cannot write
Storage fencing	Revoke the old primary's access to its disk (cloud volume detach, SCSI reservations)	Strong for shared-storage designs
Network fencing	Remove the old primary from the VIP, load balancer, or security group	Stops clients, not the server itself

Patroni's protection layers

# patroni.yml
watchdog:
  mode: required           # do not become leader unless the watchdog could be activated
  device: /dev/watchdog    # load the softdog kernel module if there is no hardware watchdog
  safety_margin: 5         # reboot 5 s before the leader key would expire

 t = 0 s    leader renews key (TTL 30 s); watchdog armed to fire at t = 25 s
 t = 3 s    Patroni on the leader freezes (bug, CPU starvation, frozen VM)
 t = 25 s   watchdog fires: the node reboots; PostgreSQL is gone
 t = 30 s   leader key expires; a replica acquires it and promotes
            -> at no moment did two primaries exist

Client-side safeguards. Connection strings with target_session_attrs=read-write only connect to a node that accepts writes, and load balancers that health-check /primary stop routing to a demoted node. These reduce the impact of a brief overlap, but they are not fencing: an existing connection to the old primary can keep writing until that node is stopped.

Data loss is a separate question. Fencing prevents two primaries; it does not recover transactions that were committed on the old primary but never reached the new one (with asynchronous replication). When the old primary is rewound, those transactions are discarded. Only synchronous replication with zero-loss promotion rules (Patroni synchronous_mode) prevents that.

Recovering from a split brain if it happens anyway:

1. Stop writes to one side immediately (pick the side with less traffic or fewer critical writes since the split).
2. Keep a copy of the losing side's data directory before any rewind.
3. Identify the divergence point from the timeline history files, and extract the losing side's changes (pg_waldump, or compare tables with postgres_fdw).
4. Reapply the needed changes manually or through application logic; then rebuild the losing node as a standby.

Key takeaways

• Split brain means two primaries accepting writes; the histories cannot be merged automatically.
• Use quorum (three or more voters in separate failure domains) so only one side of a partition can elect a primary.
• Fence the old primary: self-demotion, watchdog, STONITH, storage, or network fencing; stronger mechanisms do not rely on the failed node behaving well.
• Patroni combines a DCS lease, self-demotion, and a watchdog; enable the watchdog in production.
• Fencing prevents two writers but not the loss of asynchronous commits; that requires synchronous replication.

🧪 Practice

1. (Beginner) Explain with a diagram why a two-node cluster with automatic failover and no tiebreaker must choose between never failing over and risking split brain.
2. (Intermediate) In a Patroni test cluster without a watchdog, freeze the leader's Patroni process with kill -STOP while leaving PostgreSQL running, and check whether two servers accept writes after failover. Repeat with the softdog watchdog enabled.
3. (Interview) "Our database cluster has one node in each of two data centers and the DCS has two members in DC1 and one in DC2. What happens when the link between the data centers fails, and when DC1 loses power completely?" Hint: Count votes on each side in both scenarios. Where would you place a third site, and why must it be independent of both?

Virtual IPs and Service Discovery

After a failover the primary is a different machine with a different IP address. Applications configured with host=10.0.1.10 keep trying the dead server. Service discovery is how clients find the current primary (and the replicas) without configuration changes. There are several approaches, and they differ in speed, infrastructure requirements, and how they handle existing connections.

Options at a glance

Approach	How clients find the primary	Switch time	Requirements
Virtual IP (VIP)	A floating IP moves to the new primary	Seconds	Nodes in the same L2 network, or a cloud API for secondary IPs
DNS	A name (for example, primary.shop.service.consul) resolves to the current primary	DNS TTL + client caching	Consul or a DNS updated by the HA tool
Proxy (HAProxy, PgBouncer)	Clients connect to a stable proxy that health-checks backends	Health check interval	The proxy itself must be highly available
Client multi-host (libpq, JDBC)	Client tries a list of hosts and keeps the one with the right role	On each new connection	Driver support; full host list in the configuration
Kubernetes Service	A Service selects the pod labeled as primary	Label update + endpoints propagation	An operator that relabels pods

VIP with vip-manager (Patroni). vip-manager watches the leader key in the DCS and adds the VIP to whichever node holds it:

# /etc/default/vip-manager.yml on every database node
ip: 10.0.1.100                       # the address applications connect to
netmask: 24
interface: eth0
trigger-key: "/service/shop/leader"  # Patroni's leader key
trigger-value: "db1"                 # this node's Patroni name (db2, db3 on the others)
dcs-type: etcd
dcs-endpoints:
  - http://10.0.9.1:2379
  - http://10.0.9.2:2379
  - http://10.0.9.3:2379

Because the VIP follows the DCS leader key, it cannot be on two nodes at once as long as the DCS is consistent. A classic alternative is keepalived with VRRP and a check script that calls Patroni's /primary endpoint; it works but has its own election, which can disagree with Patroni.

Proxy with HAProxy. HAProxy health-checks each node's Patroni REST API and forwards traffic only to nodes that pass:

# /etc/haproxy/haproxy.cfg
global
    maxconn 4000

defaults
    mode tcp                                  # PostgreSQL protocol is passed through untouched
    timeout connect 5s
    timeout client  30m
    timeout server  30m

listen primary
    bind *:5000                               # applications write through port 5000
    option httpchk GET /primary               # 200 only on the current leader
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions   # cut connections to a demoted node
    server db1 10.0.1.10:5432 check port 8008
    server db2 10.0.1.11:5432 check port 8008
    server db3 10.0.2.11:5432 check port 8008

listen replicas
    bind *:5001                               # read-only traffic through port 5001
    balance roundrobin
    option httpchk GET /replica?lag=16MB      # exclude replicas more than 16 MB behind
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server db1 10.0.1.10:5432 check port 8008
    server db2 10.0.1.11:5432 check port 8008
    server db3 10.0.2.11:5432 check port 8008

on-marked-down shutdown-sessions matters: without it, connections opened before the failover stay attached to the old primary.

Client-side multi-host connection strings. No extra infrastructure is needed; the driver tries hosts in order and checks each one's role after connecting:

# libpq and libpq-based drivers (psql, psycopg, and others)
psql 'postgresql://app@db1:5432,db2:5432,db3:5432/shop?target_session_attrs=read-write&connect_timeout=3'

// JDBC (pgJDBC)
String url = "jdbc:postgresql://db1:5432,db2:5432,db3:5432/shop"
           + "?targetServerType=primary"      // only accept a writable server
           + "&connectTimeout=3";             // seconds per host before trying the next

target_session_attrs	Accepts
any (default)	First host that connects
read-write	A session that accepts writes (transaction_read_only is off)
primary	A server that is not in recovery (PG 14+)
read-only	A read-only session (a standby, or a primary with default_transaction_read_only = on)
standby	A server in recovery (PG 14+)
prefer-standby	A standby if one is reachable, otherwise any server (PG 14+)

The weakness: discovery happens only when a connection is opened. Long-lived pooled connections to the old primary fail on their next query, and the application must reconnect and retry (Handling Connection Failures and Retries).

DNS caveats. DNS is universal but slow to converge: resolvers and clients cache records, and some runtimes (older JVMs, for example) cache them far longer than the TTL. Use short TTLs (5 to 30 seconds), verify your client's caching behavior, and still close stale connections.

Key takeaways

• Clients need a stable way to reach the current primary: a VIP, DNS, a proxy, multi-host connection strings, or a Kubernetes Service.
• Tie the discovery mechanism to the HA tool's view of the leader (vip-manager on the DCS key, HAProxy checks on Patroni's /primary) so the two cannot disagree.
• Existing connections to a demoted node must be cut (shutdown-sessions) and applications must reconnect and retry.
• target_session_attrs=read-write makes libpq clients skip non-writable hosts; JDBC uses targetServerType=primary.
• DNS-based discovery depends on TTLs and client caching; test it end to end.

🧪 Practice

1. (Beginner) Using two local servers (primary on 5432, standby on 5433), connect with psql 'host=localhost,localhost port=5433,5432 target_session_attrs=read-write' and confirm which port you reached with SHOW port. Promote the standby, stop the old primary, and connect again.
2. (Intermediate) Put HAProxy in front of a Patroni cluster with the configuration above. Run a client that writes a row every 100 ms through port 5000, perform a switchover, and measure how many writes failed and for how long.
3. (Interview) "After a failover the application kept failing for 15 minutes even though the new primary was up in 30 seconds. What could cause that?" Hint: DNS caching in the runtime, a connection pool that keeps dead connections, a VIP that did not move, missing retries, or a proxy that kept sessions to the old node.

Load Balancing Reads

Most applications read far more than they write. Since every standby already has a full copy of the data, sending read-only queries to standbys spreads the load and keeps the primary's CPU and I/O for writes. The difficulty is not routing itself but consistency: replicas lag, and a user who just saved something expects to see it.

Where to route

                  +--------------------------+
 application ---> | router                   | --- writes and "fresh" reads ---> primary
                  | (app code, proxy, driver)| --- tolerant reads -------------> replica 1
                  +--------------------------+ --- tolerant reads -------------> replica 2

Router	How it decides	Strengths	Weaknesses
Application code	Two connection pools: write_db and read_db	Full control; the code knows which reads tolerate lag	Every query path must choose correctly
HAProxy	Separate ports for primary and replicas (previous topic)	Simple; lag-aware via Patroni ?lag=	Application still chooses the port
Driver	load_balance_hosts=random (libpq, PG 16+) with target_session_attrs=prefer-standby	No extra component	Per connection, not per query; no lag awareness
Pgpool-II	Parses each SQL statement; sends SELECTs to replicas	Transparent to the application	Must guess whether functions write; extra layer to operate; can hurt latency

# libpq 16+: spread read-only connections randomly across standbys, fall back to the primary
psql 'host=db1,db2,db3 dbname=shop user=report target_session_attrs=prefer-standby load_balance_hosts=random'

What to send to replicas

Good candidates	Keep on the primary
Reports, dashboards, analytics	Reads in the same transaction as writes
Search pages, product catalogs, public content	Reads that immediately follow a user's own write
Background exports, batch reads	SELECT ... FOR UPDATE, functions that write
Read-only API endpoints that tolerate seconds of staleness	Uniqueness or balance checks that decide a write

Read-your-writes with LSN tracking. After a write, the application records the primary's WAL position. A later read goes to a replica only if the replica has replayed past that position; otherwise it waits briefly or falls back to the primary:

import time
import psycopg

primary = psycopg.connect("host=db1 dbname=shop user=app", autocommit=True)
replica = psycopg.connect("host=db2 dbname=shop user=app", autocommit=True)

def save_profile(user_id: int, name: str) -> str:
    primary.execute("UPDATE profiles SET name = %s WHERE id = %s", (name, user_id))
    # Position after our commit; store it in the user's session or a cookie
    return primary.execute("SELECT pg_current_wal_lsn()::text").fetchone()[0]

def load_profile(user_id: int, min_lsn: str | None, max_wait: float = 0.2):
    conn = replica
    if min_lsn is not None:
        deadline = time.monotonic() + max_wait
        while True:
            caught_up = replica.execute(
                "SELECT pg_last_wal_replay_lsn() >= %s::pg_lsn", (min_lsn,)
            ).fetchone()[0]
            if caught_up:
                break
            if time.monotonic() > deadline:
                conn = primary            # replica too far behind: read from the primary instead
                break
            time.sleep(0.01)
    return conn.execute("SELECT * FROM profiles WHERE id = %s", (user_id,)).fetchone()

Simpler alternatives: send all reads of a user to the primary for a few seconds after that user writes ("sticky after write"), or use synchronous_commit = remote_apply for the writes whose results must be immediately visible on replicas, at the cost of commit latency.

Capacity planning. Replicas scale reads, not writes: every replica replays every write, so a write-saturated primary cannot be helped by adding replicas. Also make sure that the remaining nodes can carry the load if one replica fails. With three replicas each at 80% CPU, losing one pushes the other two to 120%.

Key takeaways

• Route reads that tolerate staleness to replicas; keep reads that must see the latest writes on the primary.
• Routing can live in the application (most precise), a proxy, the driver (load_balance_hosts, PG 16+), or Pgpool-II (transparent but complex).
• Exclude lagging replicas automatically, for example with Patroni's /replica?lag= health check.
• Solve read-your-writes with LSN tracking, sticky-after-write routing, or remote_apply for specific writes.
• Replicas do not scale writes; size the replica pool so it survives losing a member.

🧪 Practice

1. (Beginner) Classify ten endpoints of an online shop (product page, cart, checkout, order history, admin report, and so on) as replica-safe or primary-only, with a one-line reason each.
2. (Intermediate) Implement the LSN-based read-your-writes function above, add artificial replica lag with recovery_min_apply_delay = '2s', and measure how often reads fall back to the primary for different max_wait values.
3. (Interview) "After introducing read replicas, users sometimes see their just-placed order missing from the 'My orders' page. Propose a fix and explain how you would know it worked." Hint: Session-level stickiness, LSN tokens, remote_apply, and a metric for stale reads or fallback rate.

Availability Targets and Trade-offs

"Highly available" means nothing until it is quantified. An availability target states what fraction of time the service must work, usually as a number of nines, and every architectural choice in this chapter buys some availability at a cost in money, complexity, latency, or durability. Choosing the target first prevents both over-engineering (a five-node multi-region cluster for an internal tool) and under-engineering (a single server behind a revenue-critical checkout).

What the nines allow

Availability	Downtime per year	Downtime per month (average)	Realistic with
99%	3.65 days	7.3 hours	Single server, good backups, manual restore
99.9%	8.76 hours	43.8 minutes	Primary + standby, manual or automatic failover
99.95%	4.38 hours	21.9 minutes	Automatic failover, tested runbooks, rolling maintenance
99.99%	52.6 minutes	4.4 minutes	Automatic failover in seconds, zero-downtime upgrades, multi-zone, mature operations
99.999%	5.3 minutes	26 seconds	Multi-region, application-level resilience; very rarely achieved for a stateful database

Availability is not only failover. Downtime comes from many sources, and failover addresses only some:

Source	Handled by HA failover?	Other mitigation
Server, disk, or zone failure	Yes	Multi-zone placement
Minor version upgrade, OS patching	Yes, via rolling switchover	Patroni switchover
Major version upgrade	No	Logical replication upgrade (Logical Replication Upgrades)
Long lock from a migration	No	lock_timeout, safe migration patterns (11.5)
Accidental DELETE or DROP	No, replicas copy it instantly	Delayed standby, PITR (11.3)
Full disk, wraparound, runaway query	No, standbys often have the same problem	Monitoring, alerting, limits
Application or network outage	No	End-to-end design

Composite availability. A request that needs several components in series is only as available as their product:

 load balancer (99.99%) x connection pooler (99.95%) x database cluster (99.95%) x app (99.9%)
 = 0.9999 x 0.9995 x 0.9995 x 0.999 = 0.9979   ->  about 18 hours of downtime per year

Each layer must therefore be redundant itself, and a highly available database behind a single PgBouncer instance is not highly available.

Architectures and their trade-offs

Architecture	RPO	RTO	Cost and complexity	Notes
Single server + PITR backups	Minutes (archive lag)	Hours (restore)	Lowest	Fine for internal or non-critical systems
Primary + async standby, manual failover	Seconds	Minutes to an hour (human response)	Low	Needs an on-call person and a tested runbook
3-node Patroni, async	Seconds (bounded by maximum_lag_on_failover)	~30-60 s	Medium	The common production default
3-node Patroni, synchronous_mode	Zero for acknowledged commits	~30-60 s	Medium, plus commit latency	Use quorum across zones in one region
Above + async standby cluster in another region	Seconds to minutes for a regional disaster	Minutes (often a manual decision)	High	Disaster recovery, not everyday HA (Disaster Recovery Planning)

The fundamental trade-off. During a network partition, a replicated database must choose between staying available for writes and guaranteeing that no acknowledged write is lost (the CAP theorem). Even without partitions, it trades latency against consistency (PACELC). In PostgreSQL terms:

• Asynchronous replication favors availability and low latency, accepting a small RPO.
• Synchronous replication favors durability, accepting commit latency and blocked writes if too few standbys respond.
• Quorum synchronous replication (ANY 1 (a, b)) is the usual compromise: zero loss as long as at most one standby is unavailable.

Making the target real

• Write down the SLO, RPO, and RTO, and get the business to agree on them (Recovery Objectives RPO and RTO).
• Track an error budget: at 99.95%, about 22 minutes per month. A planned switchover that causes 20 seconds of errors spends part of it.
• Test failover regularly (game days, or a monthly scheduled switchover). An untested failover path is a hope, not a capability.
• Measure availability from the client side (synthetic transactions), not only from database metrics.

Key takeaways

• Choose an availability target in nines and translate it into allowed downtime per month before choosing an architecture.
• Failover handles hardware and node failures; human errors, upgrades, and resource exhaustion need other mitigations.
• Availability of components in series multiplies; every layer in the request path needs redundancy.
• Asynchronous, synchronous, and quorum replication represent different points on the latency, durability, and availability trade-off.
• Test failover routinely and measure availability as clients experience it.

🧪 Practice

1. (Beginner) Your SLO is 99.95% monthly. Last month had one 7-minute failover and a 12-minute migration lock. Compute the remaining error budget and say whether the SLO was met.
2. (Intermediate) Design the database architecture for a payment service that requires RPO = 0 within a region and RTO under one minute, and must survive the loss of one availability zone. Specify nodes per zone, synchronous settings, the DCS placement, and how clients connect.
3. (Interview) "The CEO asks for 'five nines' on the main database. How do you respond?" Hint: Translate it to minutes per year, list what would have to be true (multi-region, zero-downtime upgrades, application design, staffing), estimate the cost, and ask what the business impact of downtime actually is.

<a id="124-scaling-out"></a>

12.4 Scaling Out

When one PostgreSQL server can no longer keep up, the options range from buying a bigger machine to spreading reads, data, and writes across many servers; this subchapter explains how to tell which kind of scaling a workload needs, the patterns for scaling reads, how sharding works in general and with Citus, how foreign data wrappers let one server query others, and why the number of connections is often the first limit you hit.

Vertical vs. Horizontal Scaling

Every growing system eventually asks "how do we handle more?". There are two fundamental answers. Vertical scaling (scaling up) moves the database to a bigger machine: more CPU cores, more memory, faster storage. Horizontal scaling (scaling out) adds more machines and divides the work among them. Vertical scaling keeps the architecture simple but has a ceiling; horizontal scaling has a much higher ceiling but makes every query, transaction, and operation more complicated.

Analogy. A restaurant that is too busy can move to a bigger kitchen with more stoves (vertical), or open more branches (horizontal). A bigger kitchen changes nothing about how the restaurant works. More branches need a way to decide which customer goes where, and a customer who wants a dish from two branches at once has a problem.

Comparison

	Vertical	Horizontal: read replicas	Horizontal: sharding
Scales	Everything, up to the largest machine	Reads only	Reads, writes, and data size
Application changes	None	Route reads; handle replica lag	Shard-aware queries, keys, and transactions
Consistency	Full ACID on one node	Replicas are eventually consistent	Cross-shard transactions and queries are harder
Operations	One server (plus HA standbys)	More servers, same data	N clusters, each with HA, backups, upgrades
Limit	Largest available hardware; cost grows faster than capacity	Write throughput of the primary	Practically very high
Failure impact	Whole database	Lose read capacity	Lose a fraction of the data's availability

Do not underestimate a single server. A well-tuned PostgreSQL server on modern hardware (dozens of cores, hundreds of gigabytes of RAM, NVMe storage) handles tens of thousands of transactions per second and multi-terabyte data sets. Most applications that "need sharding" first need better indexes, fewer queries per request, or a connection pooler.

Find the bottleneck before scaling

-- Where does database time go? (requires pg_stat_statements, 7.5)
SELECT left(query, 60) AS query,
       calls,
       round(total_exec_time / 1000) AS total_s,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(100 * total_exec_time / sum(total_exec_time) OVER (), 1) AS pct_time,
       shared_blks_read                                        -- blocks that missed the buffer cache
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- What are sessions waiting on right now? CPU (no wait event), I/O, locks, or WAL?
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY 1, 2
ORDER BY 3 DESC;

A typical escalation path

 1. Fix the workload        indexes, query rewrites, N+1 queries, caching          (chapter 7)
 2. Pool connections        PgBouncer                                               (Connection Scaling Limits)
 3. Scale up                more CPU / RAM / faster disks                           cheap in engineering time
 4. Offload reads           replicas, caches, analytics to a separate store         (Read Scaling Patterns)
 5. Reduce data per node    partitioning, archiving old data, splitting by service  (6.4, functional split)
 6. Shard                   Citus or application-level sharding                      (Sharding Strategies)
       each step is more complex and harder to undo than the previous one

Functional partitioning (splitting by domain) is a horizontal step that is often overlooked: the billing service gets its own database, the catalog another. Each database stays small and independently scalable, at the cost of cross-domain joins becoming API calls or federated queries (Federated Query Patterns).

Key takeaways

• Vertical scaling is simple and surprisingly far-reaching; horizontal scaling raises the ceiling at a large cost in complexity.
• Read replicas scale reads only; sharding scales writes and data size but complicates queries and transactions.
• Identify the actual bottleneck with pg_stat_statements and wait events before choosing a scaling strategy.
• Follow an escalation path: fix the workload, pool, scale up, offload reads, reduce data, and shard last.

🧪 Practice

1. (Beginner) For a database you have access to, list the top five queries by total execution time and the dominant wait events under load. Decide whether the bottleneck is CPU, I/O, locks, or connections.
2. (Intermediate) Run pgbench with a read-only and a read-write workload on a 4-core and an 8-core VM (or with taskset limiting CPUs). Plot TPS against clients and explain where each configuration stops scaling.
3. (Interview) "Our startup's database is at 70% CPU and the team wants to shard. What do you ask before agreeing?" Hint: Growth rate, top queries, read/write ratio, current hardware size and headroom, whether replicas or caching would solve it, and the ongoing cost of sharding.

Read Scaling Patterns

When reads dominate, there are several ways to serve more of them without touching the primary's write path. Read replicas (Load Balancing Reads) are the most direct, but they copy everything and still run the full query each time. Other patterns avoid repeated work altogether or move specific read workloads to systems better suited to them.

Analogy. A library with a queue at the reference desk can hire more librarians (replicas), print a handout for the ten most asked questions (cache and precomputed results), or send researchers to a specialized archive (a separate read store).

Pattern overview

Pattern	What it does	Staleness	Best for
Read replicas	Identical copies serving queries	Milliseconds to seconds	General read traffic
Application cache (Redis, Memcached)	Stores computed results by key	Until expiry or invalidation	Hot keys, expensive objects, sessions
Materialized views and summary tables	Precomputed aggregates inside PostgreSQL	Until refresh	Dashboards, counters, leaderboards
Separate read store (CQRS) via CDC	Search index, analytics warehouse, denormalized views	Seconds	Full-text search, analytics, different access patterns
Geo-distributed replicas	Replicas near users in other regions	Cross-region replication lag	Latency-sensitive global reads

Cache-aside. The application checks the cache first and queries the database only on a miss:

import json
import psycopg
import redis

cache = redis.Redis(host="cache1")
db = psycopg.connect("host=db-replicas port=5001 dbname=shop user=app", autocommit=True)

def get_product(product_id: int) -> dict:
    key = f"product:{product_id}"
    cached = cache.get(key)
    if cached is not None:
        return json.loads(cached)                       # hit: no database work at all
    row = db.execute(
        "SELECT id, name, price, stock FROM products WHERE id = %s", (product_id,)
    ).fetchone()
    product = {"id": row[0], "name": row[1], "price": float(row[2]), "stock": row[3]}
    cache.set(key, json.dumps(product), ex=300)         # expire after 5 minutes as a safety net
    return product

def update_price(product_id: int, price: float) -> None:
    with psycopg.connect("host=db-primary dbname=shop user=app") as conn:
        conn.execute("UPDATE products SET price = %s WHERE id = %s", (price, product_id))
    cache.delete(f"product:{product_id}")               # invalidate after the commit

Invalidation is the hard part: if the delete fails or another code path writes the table, the cache serves stale data until expiry. Driving invalidation from the database's own change stream (CDC, Change Data Capture Pipelines, or LISTEN/NOTIFY from a trigger, 9.5) covers every writer.

Precomputing inside PostgreSQL. A dashboard that aggregates 200 million order rows on every page load does not need replicas; it needs the aggregate computed once:

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at)::date AS day,
       region,
       count(*)   AS orders,
       sum(total) AS revenue
FROM orders
GROUP BY 1, 2;

CREATE UNIQUE INDEX ON daily_sales (day, region);    -- required for CONCURRENTLY

-- Refresh every 10 minutes (cron, pg_cron); readers are not blocked during the refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

For data that must be fresher, maintain a summary table incrementally with triggers or in the same transaction as the write, at the cost of extra work on every write.

CQRS with a separate read model. Command Query Responsibility Segregation separates the model used for writes (normalized PostgreSQL tables) from models optimized for reads. CDC streams changes into a search engine for text search, a columnar warehouse for analytics, or a denormalized PostgreSQL database built by logical replication. The primary is relieved of whole classes of queries, and each read store is shaped for its queries.

                 +---------------------+   CDC / logical replication   +----------------------------+
 writes -------> | PostgreSQL primary  | ----------------------------> | search index (product text)|
                 | normalized tables   | ----------------------------> | warehouse (analytics)      |
                 +---------------------+ ----------------------------> | reporting Postgres         |
                           ^                                           +----------------------------+
 transactional reads ------+                        reads that tolerate seconds of lag ----^

Key takeaways

• Replicas copy everything and repeat the full work of each query; caches and precomputation avoid the work entirely.
• Cache-aside is simple, but invalidation must cover every writer; database-driven invalidation (CDC, NOTIFY) is more robust.
• Materialized views with REFRESH ... CONCURRENTLY and summary tables serve expensive aggregates cheaply.
• CQRS with CDC moves search and analytics to stores built for them.
• Every pattern trades freshness for capacity; agree on acceptable staleness per use case.

🧪 Practice

1. (Beginner) Create a materialized view over a large orders table for a daily revenue dashboard. Compare the query time of the dashboard query against the base table and the view, and refresh the view concurrently while another session reads it.
2. (Intermediate) Implement cache-aside for a product lookup and drive invalidation from a trigger that calls pg_notify('product_changed', id::text) with a listener that deletes the cache key. Show that a direct UPDATE in psql also invalidates the cache.
3. (Interview) "Our read replicas are at 90% CPU running the same few expensive dashboard queries. Adding more replicas is the plan. What else would you consider?" Hint: Precomputation, caching, moving analytics off OLTP replicas, query tuning, and what each costs in freshness.

Sharding Strategies

Sharding splits one logical data set across several independent database servers (shards), each holding a subset of the rows. It is the only way to scale writes and total data size beyond one machine, because each shard handles only its part. It is also a permanent architectural commitment: every query, transaction, migration, and backup now has to account for data living in several places.

Analogy. A national phone directory too large for one book is split into volumes: A-F, G-M, and so on. Looking up one name means opening one volume. Asking "how many people named Lee live in each city?" means reading every volume and combining the answers.

The shard key is the most important decision. Every row is assigned to a shard by the value of a shard key (also called a distribution column). A good shard key:

• Has many distinct values and spreads rows and load evenly.
• Appears in most queries, so most queries touch exactly one shard.
• Co-locates related data: all rows that are joined or updated together share the same key value, so joins and transactions stay on one shard.

For multi-tenant SaaS, tenant_id (or account_id) is almost always right: every query is scoped to a tenant, and a tenant's orders, items, and users live together.

Strategies

Strategy	Assignment	Pros	Cons
Hash	hash(key) mod N or hash ranges	Even distribution; no hotspots from sequential keys	Range queries span all shards; changing N moves data
Range	Key ranges (2025-01..2025-06 on shard 1)	Efficient range scans; easy archiving	Hotspots: newest range takes all inserts
Directory (lookup)	A table maps each key to a shard	Full flexibility; move one tenant at a time	Extra lookup; the directory is critical infrastructure
Geographic / tenant placement	By region or customer tier	Data residency; latency; isolate large tenants	Uneven sizes; cross-region queries

 Hash sharding on tenant_id                      Directory sharding
 tenant 17 -> hash -> 0x3A.. -> shard 1          lookup: tenant 17   -> shard 1
 tenant 18 -> hash -> 0xC1.. -> shard 3                  tenant 18   -> shard 3
 tenant 19 -> hash -> 0x07.. -> shard 0                  tenant 9001 -> shard 7 (dedicated, very large tenant)

Logical shards for painless growth. Hashing directly onto physical servers means that adding a server changes mod N for almost every key. The standard fix is to hash onto many logical shards (for example 1024) and map those onto a few physical nodes. Growing means moving whole logical shards to a new node, without rehashing any keys.

import hashlib

LOGICAL_SHARDS = 1024

# Mapping of logical shard ranges to physical clusters (kept in config or a directory table)
SHARD_MAP = [
    (range(0, 256),    "postgresql://app@shard-a/shop"),
    (range(256, 512),  "postgresql://app@shard-b/shop"),
    (range(512, 768),  "postgresql://app@shard-c/shop"),
    (range(768, 1024), "postgresql://app@shard-d/shop"),
]

def logical_shard(tenant_id: int) -> int:
    # Stable hash; Python's built-in hash() is randomized per process and must not be used
    digest = hashlib.sha256(str(tenant_id).encode()).digest()
    return int.from_bytes(digest[:8], "big") % LOGICAL_SHARDS

def dsn_for_tenant(tenant_id: int) -> str:
    shard = logical_shard(tenant_id)
    for shard_range, dsn in SHARD_MAP:
        if shard in shard_range:
            return dsn
    raise LookupError(shard)

# Adding shard-e later: move logical shards 896-1023 from shard-d to shard-e, then update SHARD_MAP.

What becomes hard

Problem	Why	Typical answer
Cross-shard queries	Data for a report is on every shard	Scatter-gather in the application or a distributed engine (Citus); or a separate analytics store fed by CDC
Cross-shard transactions	No single node can commit atomically for all	Avoid by co-locating; otherwise two-phase commit (PREPARE TRANSACTION) or sagas
Global uniqueness	Each shard's sequences and unique indexes are local	UUIDs (uuidv7() in PG 18), or keys that include the shard key
Hotspots	One very large or very busy tenant	Dedicated shard via directory, or a finer shard key for that tenant
Resharding	Data must move while the system runs	Logical shards; move with logical replication, then switch routing
Schema changes	N databases must change consistently	Migration tooling that applies to every shard, idempotent migrations
Backups and restore	Each shard has its own backup timeline	Accept per-shard consistency, or coordinate restore points (pg_create_restore_point)

Key takeaways

• Sharding scales writes and data size by giving each server a subset of rows; it is a long-term architectural commitment.
• Choose a shard key that is high-cardinality, present in most queries, and co-locates data that is joined or updated together; tenant_id fits most SaaS.
• Hash sharding spreads load evenly, range sharding suits time-ordered data, and directories give per-tenant control.
• Use many logical shards mapped to few physical nodes so that growth moves shards instead of rehashing keys.
• Plan for cross-shard queries, transactions, uniqueness, hotspots, resharding, and multi-shard migrations from the start.

🧪 Practice

1. (Beginner) For a ride-sharing app with tables riders, drivers, trips, and payments, propose a shard key, and list which common queries become cross-shard.
2. (Intermediate) Implement the logical-shard router above with four local PostgreSQL databases. Load 100,000 tenants, measure rows per shard, then add a fifth database and move a range of logical shards to it using pg_dump with a WHERE-filtered COPY or logical replication with a row filter.
3. (Interview) "Design sharding for a multi-tenant SaaS where 1% of tenants hold 60% of the data." Hint: Directory-based placement, dedicated shards for large tenants, logical shards for the long tail, tenant moves, and how reporting across all tenants works.

Citus for Distributed Postgres

Building sharding into an application means writing routing, scatter-gather queries, rebalancing, and multi-shard migrations yourself. Citus is an open-source PostgreSQL extension that does this inside the database: it turns a group of PostgreSQL servers into one distributed database. The application connects to a coordinator and runs ordinary SQL; Citus routes each query to the right shards on worker nodes, or runs it in parallel across all of them and combines the results.

Analogy. Instead of each customer having to know which branch has their records, they talk to a front desk (the coordinator) that knows where everything is, sends clerks to the right branches, and hands back one combined answer.

Architecture

                          application (normal PostgreSQL drivers)
                                          |
                                          v
                        +-----------------------------------+
                        | coordinator                       |
                        |  metadata: shard -> worker map    |
                        |  distributed planner and executor |
                        +-----------------------------------+
                          /                 |                 \
             +---------------+     +---------------+     +---------------+
             | worker 1      |     | worker 2      |     | worker 3      |
             | orders_102008 |     | orders_102009 |     | orders_102010 |
             | orders_102011 |     | orders_102012 |     | orders_102013 |   <- shards are ordinary
             | countries     |     | countries     |     | countries     |      PostgreSQL tables
             +---------------+     +---------------+     +---------------+
             (each node is a normal PostgreSQL server with its own standbys for HA)

Table types

Type	Created with	Stored as	Use for
Distributed	create_distributed_table(table, column)	Hash-sharded across workers (32 shards by default)	Large tables: orders, events, items
Reference	create_reference_table(table)	Full copy on every node	Small lookup tables joined with everything: countries, plans
Local	(default)	On the coordinator only	Small admin tables

Setting up

# postgresql.conf on every node
shared_preload_libraries = 'citus'

-- On every node
CREATE EXTENSION citus;

-- On the coordinator: register the cluster
SELECT citus_set_coordinator_host('coord1', 5432);
SELECT citus_add_node('worker1', 5432);
SELECT citus_add_node('worker2', 5432);
SELECT citus_add_node('worker3', 5432);

-- Schema: the distribution column must be part of primary keys and unique constraints
CREATE TABLE orders (
  tenant_id  bigint NOT NULL,
  id         bigint GENERATED ALWAYS AS IDENTITY,
  status     text   NOT NULL,
  total      numeric(12,2),
  created_at timestamptz DEFAULT now(),
  PRIMARY KEY (tenant_id, id)
);
CREATE TABLE order_items (
  tenant_id  bigint NOT NULL,
  order_id   bigint NOT NULL,
  product_id bigint NOT NULL,
  qty        int    NOT NULL,
  PRIMARY KEY (tenant_id, order_id, product_id),
  FOREIGN KEY (tenant_id, order_id) REFERENCES orders (tenant_id, id)   -- allowed: co-located
);
CREATE TABLE countries (code char(2) PRIMARY KEY, name text);

SELECT create_distributed_table('orders', 'tenant_id');
SELECT create_distributed_table('order_items', 'tenant_id', colocate_with => 'orders');
SELECT create_reference_table('countries');

Co-location is what makes Citus efficient: rows of orders and order_items with the same tenant_id hash to the same shard group on the same worker, so joins and foreign keys on tenant_id run locally on one worker.

How queries run

-- Router query: filters on the distribution column -> exactly one shard, one worker
EXPLAIN SELECT o.id, sum(i.qty)
FROM orders o JOIN order_items i ON i.tenant_id = o.tenant_id AND i.order_id = o.id
WHERE o.tenant_id = 42
GROUP BY o.id;
--  Custom Scan (Citus Adaptive)
--    Task Count: 1                                   <- one shard
--    Tasks Shown: All
--    ->  Task
--          Node: host=worker2 port=5432 dbname=shop
--          ->  HashAggregate
--                ->  Hash Join ... on orders_102041 o ... order_items_102073 i ...

-- Multi-shard query: runs on all shards in parallel, coordinator merges partial results
EXPLAIN SELECT status, count(*) FROM orders GROUP BY status;
--  HashAggregate                                     <- final aggregation on the coordinator
--    ->  Custom Scan (Citus Adaptive)
--          Task Count: 32                            <- one task per shard, run in parallel

Growing the cluster

SELECT citus_add_node('worker4', 5432);
SELECT citus_rebalance_start();         -- moves shards in the background using logical replication
SELECT * FROM citus_rebalance_status();

Schema-based sharding. Since Citus 12, each tenant can instead have its own schema, and each schema is placed on a node as a unit. This suits applications that already use a schema per tenant:

SET citus.enable_schema_based_sharding TO on;
CREATE SCHEMA tenant_acme;               -- becomes a distributed schema, placed on one node
CREATE TABLE tenant_acme.orders (        -- no distribution column needed
  id    bigint PRIMARY KEY,
  total numeric(12,2)
);

Limits and trade-offs

• Distribution columns must be part of unique constraints and primary keys; foreign keys between distributed tables require co-location.
• Queries that do not filter or join on the distribution column fan out to all shards; very frequent fan-out of small queries adds coordinator overhead.
• Some SQL features are restricted or slower across shards (for example, certain correlated subqueries and cross-shard transactions, which use two-phase commit).
• Each node is a full PostgreSQL server that needs its own HA (Patroni or similar), monitoring, and backups.
• Citus is open source (AGPLv3) and available as a managed service (Azure Cosmos DB for PostgreSQL).

Key takeaways

• Citus shards tables across worker nodes while applications use one coordinator and normal SQL.
• Distributed tables are hash-sharded by a distribution column, reference tables are copied to every node, and co-location keeps related rows together.
• Queries filtering on the distribution column are routed to one shard; others run in parallel across all shards.
• Add nodes and move shards online with citus_rebalance_start(); schema-based sharding fits schema-per-tenant designs.
• Design the schema around the distribution column, and plan HA and backups for every node.

🧪 Practice

1. (Beginner) Start a Citus cluster with a coordinator and two workers (the official Docker images make this easy), distribute an events table by tenant_id, and use EXPLAIN to compare a query with and without a tenant_id filter.
2. (Intermediate) Distribute orders and order_items without colocate_with, then try to add the foreign key and run a join. Recreate them co-located and compare the plans and timings.
3. (Interview) "When is Citus a poor fit, even for a large database?" Hint: No natural distribution column, many queries that join on different keys, heavy cross-shard transactions, or a data size that a single well-provisioned server still handles.

Foreign Data Wrappers and postgres_fdw

Data rarely lives in one database. A reporting query may need customer data from the CRM database, archived orders from a cold-storage server, and today's orders locally. Copying everything everywhere is wasteful and stale. Foreign data wrappers (FDWs), an implementation of the SQL/MED standard, let PostgreSQL present external data as foreign tables that can be queried, and often modified, with ordinary SQL. postgres_fdw, shipped with PostgreSQL, connects to other PostgreSQL servers.

Analogy. A foreign table is like a shortcut on your desktop to a file on a network drive. It looks local and opens with the same tools, but every access goes over the network, and if the drive is offline, the shortcut is useless.

Setting up postgres_fdw

-- On the local (querying) database
CREATE EXTENSION postgres_fdw;

-- 1. Describe the remote server
CREATE SERVER crm_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'crm-db.internal', port '5432', dbname 'crm',
           use_remote_estimate 'true',      -- ask the remote planner for row estimates
           fetch_size '1000',               -- rows per network round trip (default 100)
           async_capable 'true');           -- allow parallel scans of several foreign tables (PG 14+)

-- 2. Map local users to remote credentials
CREATE USER MAPPING FOR report_user
  SERVER crm_server
  OPTIONS (user 'crm_reader', password 'change-me');

-- 3. Create foreign tables, one by one or a whole schema at once
CREATE SCHEMA crm;
IMPORT FOREIGN SCHEMA public
  LIMIT TO (customers, contracts)
  FROM SERVER crm_server INTO crm;

-- 4. Query as if local
SELECT c.name, sum(o.total) AS revenue
FROM crm.customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.segment = 'enterprise'
GROUP BY c.name;

Pushdown: what runs where. The most important performance question is how much work postgres_fdw sends to the remote server (pushdown) instead of fetching rows and processing them locally. EXPLAIN VERBOSE shows the exact remote SQL:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT segment, count(*) FROM crm.customers WHERE country = 'DE' GROUP BY segment;
--  Foreign Scan
--    Output: segment, (count(*))
--    Relations: Aggregate on (crm.customers)
--    Remote SQL: SELECT segment, count(*) FROM public.customers WHERE ((country = 'DE')) GROUP BY 1
--                                                    ^ filter and aggregate both run remotely: one row per segment comes back

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM crm.customers WHERE my_normalize(email) = 'ada@example.com';
--  Foreign Scan on crm.customers
--    Filter: (my_normalize(customers.email) = 'ada@example.com'::text)    <- evaluated LOCALLY
--    Remote SQL: SELECT id, name, email, ... FROM public.customers          <- fetches the whole table!

Pushed down (when safe)	Not pushed down
WHERE with built-in immutable operators and functions	User-defined functions (unless their extension is listed in the server's extensions option)
Joins between foreign tables on the same server	Joins between a foreign table and a local table, or tables on different servers
Aggregates, GROUP BY, HAVING	Non-immutable functions such as now() and random()
ORDER BY, LIMIT	Expressions with collations that might differ remotely

When a join with a local table is unavoidable, the planner may use a parameterized remote scan (one remote query per local row); with use_remote_estimate it can at least estimate this properly. Small local filter sets are often better passed explicitly, for example as WHERE id = ANY($1) with an array.

Writes and transactions

INSERT INTO crm.contracts (customer_id, plan) VALUES (17, 'pro');   -- runs remotely
ALTER SERVER crm_server OPTIONS (ADD batch_size '500');            -- batch bulk inserts (PG 14+)

• postgres_fdw opens a remote transaction alongside the local one, using REPEATABLE READ (or SERIALIZABLE if the local transaction is serializable) so all scans in one query see a consistent remote snapshot.
• The remote transaction commits when the local one commits, but this is not an atomic distributed commit: if the remote commit fails after the local one succeeded, the two can disagree. Use it for reads and non-critical writes, or add application-level reconciliation.
• Connections are cached per session; inspect them with postgres_fdw_get_connections() and close them with postgres_fdw_disconnect_all() (PG 14+).

Security

• Only superusers can create user mappings without a password; non-superusers must use password authentication to the remote side (password_required), preventing them from using the local server's own credentials or trust rules.
• The password in a user mapping is stored in the catalog and visible to privileged users through pg_user_mappings. Use a dedicated, read-only remote role.

Other wrappers. The same interface exists for many sources: file_fdw (CSV files, in contrib), mysql_fdw, oracle_fdw, tds_fdw (SQL Server), and wrappers for object storage and columnar formats. Their pushdown capabilities vary widely; always check EXPLAIN VERBOSE.

Key takeaways

• FDWs expose external data as foreign tables; postgres_fdw connects to other PostgreSQL servers.
• Setup is server, user mapping, and foreign tables (IMPORT FOREIGN SCHEMA creates them in bulk).
• Performance depends on pushdown; verify the remote SQL with EXPLAIN VERBOSE and avoid local-only expressions on large foreign tables.
• Remote commits are not atomic with local ones; treat cross-server writes with care.
• Use dedicated, least-privileged remote roles; tune fetch_size, batch_size, use_remote_estimate, and async_capable.

🧪 Practice

1. (Beginner) Create two databases on one server, link them with postgres_fdw, import a schema, and run a query joining a local and a foreign table. Inspect the plan with EXPLAIN VERBOSE.
2. (Intermediate) Write three queries against a 5-million-row foreign table: one whose filter and aggregate are fully pushed down, one where a user-defined function prevents pushdown, and one joining two foreign tables on the same server. Measure runtimes and network bytes (pg_stat_statements on the remote side helps).
3. (Interview) "A report joining a local table with a foreign table takes 20 minutes. How do you investigate?" Hint: EXPLAIN (VERBOSE, ANALYZE), the remote SQL, rows fetched, use_remote_estimate, fetch_size, functions blocking pushdown, and whether to materialize the remote data locally.

Federated Query Patterns

Once foreign tables exist, they become building blocks for larger designs: spreading one table across several servers, combining data from separate services, tiering old data onto cheaper hardware, or bridging during a migration. These federated patterns are useful, but each one couples the querying database to the availability and performance of the remote ones.

Pattern 1: Partitions on other servers. A partitioned table (6.4) can have foreign tables as partitions. Partition pruning sends each query only to servers that hold relevant data, and with async_capable the scans of several remote partitions run concurrently. This is a simple form of sharding and a common way to tier data:

-- Local server holds recent data; an archive server on cheaper storage holds old years
CREATE TABLE measurements (
  sensor_id  int         NOT NULL,
  taken_at   timestamptz NOT NULL,
  value      double precision
) PARTITION BY RANGE (taken_at);

-- Recent partition: a normal local table
CREATE TABLE measurements_2026 PARTITION OF measurements
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Older partitions: foreign tables on the archive server (tables must exist there)
CREATE FOREIGN TABLE measurements_2024 PARTITION OF measurements
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
  SERVER archive_server OPTIONS (table_name 'measurements_2024');
CREATE FOREIGN TABLE measurements_2025 PARTITION OF measurements
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')
  SERVER archive_server OPTIONS (table_name 'measurements_2025');

-- Only touches the local partition: the archive server is not contacted at all
SELECT avg(value) FROM measurements WHERE taken_at >= '2026-09-01';

-- Touches all three; with async_capable the two remote scans run concurrently
SELECT date_trunc('year', taken_at), avg(value) FROM measurements GROUP BY 1;

Limitations: no global unique indexes across partitions, inserts routed to a foreign partition go over the network, and UPDATEs that move a row between a local and a foreign partition are restricted.

Pattern 2: Cross-service reporting. In a system split by domain, a reporting database imports the schemas it needs from each service's database and defines views on top:

CREATE VIEW customer_360 AS
SELECT c.id, c.name, c.segment,                 -- from the CRM service (foreign)
       b.open_invoices, b.overdue_amount,       -- from the billing service (foreign)
       s.tickets_last_30d                       -- from the support service (foreign)
FROM crm.customers c
LEFT JOIN billing.customer_balances b ON b.customer_id = c.id
LEFT JOIN support.ticket_stats      s ON s.customer_id = c.id;

These joins cross servers, so they run locally after fetching rows from each source. Keep them for modest data volumes or pre-aggregated remote views.

Pattern 3: Materialize instead of querying live. If the remote system is slow, sometimes unavailable, or queried heavily, copy what you need on a schedule:

CREATE MATERIALIZED VIEW crm_customers_snapshot AS
SELECT id, name, segment, country FROM crm.customers;
CREATE UNIQUE INDEX ON crm_customers_snapshot (id);

REFRESH MATERIALIZED VIEW CONCURRENTLY crm_customers_snapshot;   -- hourly via pg_cron

Pattern 4: Migration bridge. While moving a table from one database to another, a foreign table with the old name lets existing queries keep working until all callers have moved.

postgres_fdw vs. dblink. The older dblink extension runs arbitrary SQL strings on a remote server and returns records:

SELECT * FROM dblink('host=crm-db.internal dbname=crm user=crm_reader',
                     'SELECT id, name FROM customers WHERE segment = ''enterprise''')
         AS t(id bigint, name text);         -- the caller must declare the result columns

	postgres_fdw	dblink
Interface	Foreign tables, normal SQL	Function call with a SQL string
Planner integration	Pushdown, cost estimates, joins	None; returns whatever the string produces
Writes	INSERT/UPDATE/DELETE on foreign tables	Any statement via dblink_exec
Best for	Ongoing, structured access	Ad hoc commands, remote DDL, asynchronous remote calls

Choosing an integration approach

Need	Live FDW query	Materialized copy	Logical replication	CDC to a warehouse
Freshness	Real time	As of last refresh	Seconds	Seconds to minutes
Remote must be up for queries	Yes	No	No	No
Load on the source	Per query	Per refresh	Continuous, low	Continuous, low
Large analytical joins	Poor	Good	Good	Best

Key takeaways

• Foreign partitions combine partitioning with postgres_fdw for simple sharding or for tiering old data to another server; pruning avoids contacting irrelevant servers.
• Federated views can join data from several services, but cross-server joins are fetched and joined locally; keep volumes small.
• Materialize remote data when freshness can lag and the remote should not be in the query path.
• postgres_fdw is for structured, ongoing access; dblink is for ad hoc remote statements.
• Every live federated query depends on the remote server's availability and network latency.

🧪 Practice

1. (Beginner) Build the measurements example with one local and two foreign partitions. Use EXPLAIN to show which partitions each of three queries touches.
2. (Intermediate) Stop the archive server and run queries that do and do not need it. Then add a materialized snapshot of the archive data and discuss how this changes availability and freshness.
3. (Interview) "We split our monolith into five services with five databases. The finance team still needs joined reports across all of them. Compare federated queries, logical replication into a reporting database, and CDC into a warehouse." Hint: Freshness, load on service databases, coupling and failure isolation, schema ownership, and query performance.

Connection Scaling Limits

Scaling out the application tier is easy: add more containers. Each one opens its own pool of database connections, and the database quickly becomes the bottleneck, not because of queries but because of connections themselves. PostgreSQL uses one operating system process per connection, so connections are far more expensive than in thread-based or event-driven servers.

Analogy. Each connection is a dedicated waiter assigned to one table for as long as the guests stay, even if they order nothing for an hour. A restaurant with 2,000 waiters for 2,000 tables, of which only 30 are actually ordering, spends its money and floor space on idle waiters, and the kitchen (the CPUs) can still only cook 30 dishes at once.

Why many connections hurt

Cost	Explanation
Memory	Each backend uses several megabytes of private memory at baseline, plus work_mem per sort or hash operation, plus caches that grow with the number of tables touched
CPU scheduling	Hundreds of active processes on a few dozen cores spend time context switching
Snapshot and lock overhead	Every query's snapshot scans the list of running transactions; much improved in PG 14, but not free
Connection setup	Process fork, authentication, TLS handshake: milliseconds per new connection
Hard limit	max_connections is fixed at server start; when it is reached, new clients are rejected

FATAL:  sorry, too many clients already
FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

The multiplication problem

 40 application pods x 3 worker processes x pool size 10  =  1,200 connections
 + 8 background job workers x pool size 20                 =    160
 + cron jobs, migrations, admin sessions                   =    ~40
                                                              -------
                                                              ~1,400 connections, of which perhaps 40 are active

How many active connections are useful? Throughput peaks when active connections are a small multiple of CPU cores; beyond that, queries just wait for each other. Two rules of thumb help size pools:

 Little's law:   connections needed = throughput x time per transaction
                 5,000 transactions/s x 0.004 s = 20 busy connections on average

 Starting point: pool size ~ (CPU cores x 2) + effective number of disks   (from the HikariCP guidance)
                 16 cores -> roughly 32-40 active connections, then tune by measurement

Measuring the situation

-- How many connections are doing what?
SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count(*) DESC;
--         state        | count
-- ---------------------+-------
--  idle                |  1180        <- held by application pools, doing nothing
--  active              |    37
--  idle in transaction |    12        <- holding snapshots and locks; investigate

-- Who holds the most connections?
SELECT usename, application_name, client_addr, count(*)
FROM pg_stat_activity
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 10;

-- Headroom
SELECT count(*) AS used, current_setting('max_connections')::int AS max
FROM pg_stat_activity;

Solutions

1. Put a pooler in front of the database. PgBouncer in transaction mode maps thousands of client connections onto a few dozen server connections (Connection Pooling with PgBouncer). Run it either next to the database (one pool per node, shared by all clients) or as a sidecar (simpler, but the total still multiplies). A single PgBouncer process uses one CPU core; run several with so_reuseport or put alternatives such as PgCat, Odyssey, or Supavisor in front of large fleets.
2. Shrink application pools. Most application pools are far larger than needed; start with small pools and grow only if measurements show waiting.
3. Time out idle sessions. Limit the damage of leaked connections and forgotten transactions:

# postgresql.conf
idle_in_transaction_session_timeout = '60s'   # end sessions that sit in an open transaction
idle_session_timeout = '30min'                # PG 14+; be careful behind poolers, which may not expect closed connections
superuser_reserved_connections = 3            # always leave room for an administrator
reserved_connections = 5                      # PG 16+: slots for roles granted pg_use_reserved_connections

4. Scale per node. With replicas, each node has its own max_connections; read pools on replicas take pressure off the primary's connection budget. In sharded systems (Citus), the coordinator's connections to workers also need pooling and limits (citus.max_shared_pool_size).

Raising max_connections is rarely the fix. It requires a restart, increases per-connection overhead, and turns a clear error ("too many clients") into a slow, overloaded server. Raise it modestly only when a pooler is already in place and measurements show the pool, not the CPU, is the limit.

Key takeaways

• PostgreSQL uses a process per connection; idle connections still cost memory and add overhead, and max_connections is a hard limit set at startup.
• Horizontal scaling of the application multiplies connections; plan connection budgets explicitly.
• Useful concurrency is a small multiple of CPU cores; size pools with Little's law and measurement.
• A connection pooler (PgBouncer or alternatives), smaller application pools, and idle timeouts are the main remedies.
• Keep reserved connections for administrators so you can always log in during a connection storm.

🧪 Practice

1. (Beginner) Run the pg_stat_activity queries on a real or test system. What fraction of connections is idle, and which application holds the most?
2. (Intermediate) Benchmark pgbench -S with 16, 64, 256, and 1,000 clients directly against PostgreSQL, then through PgBouncer in transaction mode with a server pool of 32. Plot TPS and average latency for both.
3. (Interview) "After moving to Kubernetes with autoscaling, the database started rejecting connections during traffic peaks, even though CPU was only at 40%. Explain what is happening and how you would fix it permanently." Hint: Pods times pool size, autoscaling multiplying connections, idle connections, a central pooler, pool sizing, and reserved connections for operators.

<a id="13-extensions-and-the-postgres-ecosystem"></a>