Binary read replicas in PostgreSQL, Part 2

Part 2: A read replica cookbook

Part 1 described what a replica is and where its costs land. This part is recipes: the common ways replicas are put to work, and what each one asks of the replica doing it.

One rule runs under all of them. A replica can be tuned for one job. The delay settings, hot_standby_feedback, the memory settings, and the queries it is sent all pull in different directions for different jobs, and a replica given two jobs is tuned for neither. Where a recipe below says “a replica,” read “a replica that has this job and no other.”

Offloading read traffic

This is the standard use. The application sends some of its reads to replicas, and the primary gets its CPU and its cache back for writes.

Decide in the application

Whether a query can go to a replica depends on whether its caller can tolerate data that is slightly old, and only the application knows that. The reliable arrangement is two connection pools, one for the primary and one for the replicas, with the code choosing between them per request or per query. Most frameworks support this directly.

The alternative is middleware that parses each statement and sends whatever looks like a read to a replica (Pgpool-II is the usual example). I do not recommend it. A SELECT can call a function that writes, a read that follows a write has to see that write, and neither fact is visible in the text of the statement. Pgpool-II has answers for both (it looks up function volatility, and it can keep a session on the primary after a write), but they are guesses about what the application meant. Tuning them moves the decision into the middleware’s configuration, where it is harder to see and harder to test than it would be in the code.

Start with the reads whose staleness nobody will notice: search, listings, dashboards, anything already served from a cache. Keep on the primary the reads that are part of a write (check a balance, then debit it) and the reads that immediately follow one. For that second group the usual technique is stickiness: after a session writes, its reads go to the primary for a few seconds, comfortably longer than normal lag. (WAIT FOR LSN in PostgreSQL 19, described in Part 1, is the precise version of the same idea.)

Route by role, not by hostname

Replicas get promoted, rebuilt, and replaced, so a connection string that names one host is a future outage. There are two common ways to avoid writing one.

The client library can choose. libpq accepts a list of hosts, and two parameters select among them:

1
host=db1,db2,db3 dbname=app target_session_attrs=standby load_balance_hosts=random

target_session_attrs=standby accepts only a server that is in hot standby, and load_balance_hosts=random tries the hosts in random order. The first arrived in PostgreSQL 14 and the second in 16, but both are features of the client library, so the version that matters is the libpq the application is linked against. Drivers built on libpq inherit them. The JDBC driver has its own spelling: targetServerType=secondary&loadBalanceHosts=true.

Two cautions. The choice is made when a connection is opened and never again. With a connection pool, the distribution is only as even as the pool’s turnover, and a replica that returns to service receives nothing until connections are recycled, so set a maximum connection lifetime in the pool. And prefer-standby (or preferSecondary) falls back to the primary when no replica is available. Decide whether you want that. If the primary cannot carry the read traffic the replicas were built to absorb, the fallback turns the loss of the replicas into the loss of everything. standby fails the reads and leaves the writes alone.

Or a load balancer can choose: HAProxy or its equivalent in front of the replicas, with a health check that asks each server what it is. Where Patroni manages the cluster, its REST API is the thing to ask: GET /replica returns 200 only from a running replica. Checking that the port answers is not enough, because a replica that has been promoted still answers on it.

Either way, put a connection pooler in front of each replica as you would in front of the primary. (PgBouncer pools connections. It does not look at queries to decide where they go.)

Take lagging replicas out of rotation

A replica that is an hour behind is still up, still passes a connection check, and still answers every query without hesitation. The health check has to include lag. Patroni’s endpoint takes a threshold (GET /replica?lag=16MB) and returns 503 from a replica further behind than that. The threshold is in bytes of WAL, not in time, so it is a coarse test when the primary is quiet. If you write your own check, the queries are in the last section. Take the threshold from what the application’s readers were promised, and remember that removing a lagging replica moves its traffic onto the others.

Plan for losing one

Once replicas carry read traffic they are production capacity, and the arithmetic is the same as for any other tier. If three replicas each run at 70 percent, losing one overloads the two that remain. Size for one more than the load requires. Count failover in that arithmetic as well: when the primary fails and a replica is promoted, the read pool is one server smaller at the moment everything else is also going wrong.

Classes of query, classes of replica

Replicas are identical in their data and in nothing else. Each one has its own memory, its own configuration file, and its own hardware, and each can be given its own kind of work. This is the most underused property of binary replication.

Each replica has its own cache

A replica’s shared_buffers and its operating system’s file cache hold the pages its own queries have read, along with the pages replay has recently changed. Send every kind of query to every replica and each cache is the same compromise: the customer-facing lookups and the nightly aggregation evict one another’s pages everywhere. Divide the traffic by class and each cache converges on the working set of its class. The replica that serves order lookups keeps the recent end of the orders table and its indexes in memory. The replica that serves reports can scan last year’s data all day without pushing a single page out of the cache the customers depend on.

The division that pays most often is interactive traffic against analytic traffic. Beyond that, divide wherever two kinds of traffic read different data: by product area, by tenant group, by the service issuing the queries. Latency-sensitive traffic gains the most, because for a short query a cache miss is most of the cost.

Each replica has its own configuration

postgresql.conf is not replicated. (A new replica starts with a copy of the configuration files in the data directory it was cloned from, postgresql.auto.conf included. After that, nothing keeps them in step.) Apart from the handful of settings that must be at least as large as the primary’s (Part 1), every setting can differ from one replica to the next, and the ones that describe a workload should:

ALTER SYSTEM works on a replica and writes to that replica’s own postgresql.auto.conf, so per-replica settings need no special mechanism (although configuration management is the better home for them).

Settings attached to a role or a database with ALTER ROLE ... SET and ALTER DATABASE ... SET are a different matter. Those are stored in the catalogs, so they are made on the primary and arrive on every replica. That is useful in its own way. Give each class of traffic its own role, and the role carries its work_mem and statement_timeout to whichever server it connects to.

Hardware can differ too, within the same architecture. A reporting replica may want more memory and more storage throughput than the primary has. An interactive one may need less of both.

What cannot differ

Indexes cannot, and neither can planner statistics, because both are part of the data. An index that only the reporting replica needs is built on the primary, maintained by every write, and stored on every replica. When that cost is too high, the workload has outgrown binary replication, and a logical replica, which can have its own indexes, is the next step.

The replica you intend to promote should not differ from the primary at all. It needs the primary’s hardware and the primary’s settings, because it may become the primary without notice.

Query statistics are per server as well. pg_stat_statements on the primary knows nothing about queries that ran on a replica. Load it on every replica and collect from each.

The coffin corner

Pilots of high-altitude aircraft know the coffin corner: the altitude at which the speed below which the aircraft stalls and the speed above which it loses control have nearly converged, leaving almost no margin between them. Replicas have one.

Part 1 described three places the cost of a long query on a replica can land. With a short max_standby_streaming_delay, the query is cancelled. With a long one, replay waits and the replica falls behind. With hot_standby_feedback on, neither happens, and the primary accumulates dead rows. Tuning a replica means choosing which cost to pay and how much of it. The coffin corner is the workload for which every choice is unacceptable.

It can be stated as arithmetic. Call the longest query the replica must complete Q. Call the staleness its readers will tolerate L. Call the length of time the primary can go without removing dead rows, before its own performance suffers, B. On a busy primary, cleanup records that conflict with an old snapshot arrive more or less continuously, so:

• Without feedback, a query survives only if replay waits for it, and replay may have to wait for nearly as long as the query runs. The replica will be about Q behind by the time the query finishes, and further behind if long queries overlap. That is acceptable only if Q is less than L.
• With feedback, the primary holds back cleanup for as long as the query runs. That is acceptable only if Q is less than B.

If Q is greater than both, none of the three choices works. The typical case is a twenty-minute query, on a replica whose other readers expect data a few seconds old, against a primary with a queue table that degrades within minutes when vacuum is held back.

The sign that you are there is in the change history. The replica’s settings have been changed three times. Each change fixed the complaint that prompted it and produced a different complaint from someone else: cancelled reports, then stale reads, then bloat on the primary, then cancelled reports again.

No setting gets you out. Every way out changes one of the three quantities.

Separate the readers

Most often L and Q belong to different people. The twenty-minute query rarely needs data from the last second, and the readers who need fresh data are rarely running twenty-minute queries. They are in conflict only because they share a replica. Give the long queries a replica of their own, with feedback off and a long delay, and both requirements are met. This is the previous section’s advice reached from the other direction, and it resolves most cases.

Shorten the query

Q is the quantity most within your control. A report that reads a year of rows in one statement can often read a month at a time, in separate transactions, if it does not need a single consistent snapshot across the whole year. A query that is slow for want of an index can have the index. An aggregate that is recomputed on every run can be maintained on the primary and merely read on the replica.

Check for accidental length too. A session on the replica that opens a REPEATABLE READ transaction and then sits idle holds its snapshot exactly as a long query would. An idle_in_transaction_session_timeout on the replica puts a limit on it.

Put a ceiling on feedback

If queries run long only occasionally, turn feedback on and limit how long any snapshot on the replica can live: statement_timeout, transaction_timeout (PostgreSQL 17 and later), and idle_in_transaction_session_timeout, each set no higher than B. Queries under the limit run without snapshot conflicts. Queries over it fail at a known point, where before they failed at an unpredictable one. The primary’s exposure has a ceiling, as long as the application leaves those limits alone.

Freeze the replica on a schedule

For batch work that runs at known times, a dedicated replica can simply stop replaying:

1
SELECT pg_wal_replay_pause();
2
-- run the batch
3
SELECT pg_wal_replay_resume();

While replay is paused, nothing changes on the replica, every query sees the same data, and no conflicts are generated. This is max_standby_streaming_delay = -1 done on purpose, with a beginning and an end. The pause is a request, so a careful script waits for pg_get_wal_replay_pause_state() to return paused before it starts work. Both functions are restricted to superusers unless EXECUTE on them has been granted. The replica goes on receiving WAL while paused and stores all of it, so watch its disk.

Leave binary replication

When the long queries also need fresh data, or indexes of their own, they need a different kind of copy. A logical replica is an ordinary read-write server. It runs its own vacuum, so a long query there holds back cleanup only on that server, and it has no WAL replay for a query to conflict with. It costs more to operate (schema changes are not replicated, among other things), which is why it is the last exit and not the first.

One caution covers all of the above. The arithmetic concerns snapshot conflicts. Lock conflicts, from DDL and from vacuum truncation, are a separate matter and are dealt with at the source: vacuum_truncate off for the tables involved, and schema changes scheduled away from long reports.

Backups and other bulk work

Base backups

A base backup reads every file in the cluster. On the primary, that I/O competes with production. Taking the backup from a replica moves it. pg_basebackup can connect to a replica exactly as it does to a primary, and pgBackRest has a backup-standby option that copies most of the files from a replica (the backup is still started and stopped on the primary, so both have to be configured).

A base backup is not a query. It holds no snapshot and takes no table locks, so replay does not cancel it and it does not hold up replay. None of the conflict machinery applies. What does apply, for pg_basebackup:

• full_page_writes must be on at the primary. It is by default.
• If the replica is promoted while the backup is running, the backup fails.
• A replica cannot force a switch to a new WAL segment at the end of the backup. A backup taken with -X none is not usable until the primary has archived the last segment it needs, which on a quiet primary can take a long time (pg_switch_wal() on the primary forces it). Unless the replica runs archive_mode = always, pg_basebackup does not wait for that to happen.
• An incremental backup from a replica can fail if there has been very little activity since the backup it is based on.

By default pg_basebackup streams the WAL generated during the backup along with the files (-X stream), which makes the result restorable without anything else. Recovering to any later point still needs the WAL archive, and archiving normally runs on the primary. (A replica can archive as well, with archive_mode = always.)

Take backups from a replica that is neither the failover candidate nor serving latency-sensitive reads. Reading the whole cluster does that replica’s cache no good.

Dumps and extracts

pg_dump, a large COPY ... TO, and an ETL extract are queries, and they are the worst-behaved queries a replica will see. pg_dump holds one snapshot for its entire duration, and an ACCESS SHARE lock on every table it dumps. Hours of snapshot are hours of exposure to snapshot conflicts, and the locks conflict with any ACCESS EXCLUSIVE lock replayed in the meantime: most forms of ALTER TABLE, DROP TABLE, TRUNCATE, and vacuum truncation.

Feedback is the wrong answer here, because hours of dump become hours of dead rows on the primary. Run the dump on a dedicated replica and pause replay around it:

1
#!/bin/bash
2
set -e
3
trap 'psql -d app -c "SELECT pg_wal_replay_resume()"' EXIT
4

5
psql -d app -c "SELECT pg_wal_replay_pause()"
6
until [ "$(psql -d app -Atc 'SELECT pg_get_wal_replay_pause_state()')" = paused ]; do
7
    sleep 1
8
done
9

10
pg_dump -j 8 -Fd --lock-wait-timeout=60s -f /backups/app app

The trap resumes replay however the script ends. The --lock-wait-timeout is there because replay can be paused while it is holding an ACCESS EXCLUSIVE lock for a transaction whose commit it has not yet replayed. Without the timeout, pg_dump would wait for that lock until replay resumed, and replay would not resume until pg_dump finished.

Parallel dumps (-j) work against a replica. The contents of unlogged tables are left out, since a replica does not have them.

Change data capture

Since PostgreSQL 16, a logical replication slot can be created on a replica, and logical decoding can run there. That moves the CPU cost of decoding, and the consumers’ connections, off the primary. It requires wal_level = logical on both the primary and the replica. In practice it also needs hot_standby_feedback = on at that replica and a physical replication slot between the two; without them the primary can remove catalog rows the decoder still needs, and the logical slot is invalidated. That makes it a feedback replica, with everything Part 1 said about those. Keep long queries off it.

Index verification

bt_index_check, from the amcheck extension, can run on a replica (its stricter sibling, bt_index_parent_check, cannot). Running it there moves the load, and it also catches a class of problem that exists only on replicas, described under “One operating system” below.

Cascading replicas

A replica can take its WAL from another replica. The upstream replica runs walsender processes just as a primary does, and the downstream replica’s primary_conninfo points at it instead of at the primary.

A second site

This is the strongest case. Three replicas in a remote region, each streaming from the primary, send the same WAL across the expensive link three times. One replica streaming from the primary and feeding the other two sends it once.

Fan-out

Each replica attached directly to the primary costs it a walsender process, a connection, and the network traffic. For a few replicas that is negligible, and I would not cascade to avoid it. For dozens (a large read pool, or a reporting replica for every team), two or three upstream replicas feeding the rest keep the primary’s share constant.

A tier for replicas that come and go

Staging refreshes, experiments, and short-lived reporting copies can be built from a base backup of an upstream replica and attached to it. They never touch the primary’s pg_hba.conf, its replication slots, or its max_wal_senders.

What to know before building a tree

Cascading is always asynchronous. Synchronous replication on the primary covers only the replicas connected directly to it, and pg_stat_replication on the primary shows only those. To see the whole tree, query every server that has a downstream.

While it is streaming, an upstream replica forwards WAL as soon as it has written it to disk, without waiting to replay it. A replica whose replay is waiting on a long query, or has been paused, goes on feeding its downstream replicas at full speed, and its replay lag is its own. WAL that the upstream replica is restoring from the archive is different. That is passed on only as it is replayed.

Feedback is relayed upstream by each replica that itself has hot_standby_feedback = on. Through a chain of such replicas, a long query at the bottom holds back vacuum on the primary however many hops away it is, and cascading does not insulate the primary from it. An intermediate replica with feedback off drops the report, and the replicas below it are then unprotected whatever their own setting.

If an upstream replica fails, everything below it stops receiving WAL. A restore_command on each downstream replica lets it carry on from the archive in the meantime. To reattach it, change primary_conninfo and reload; no restart is needed. A replication slot for a downstream replica is created on its upstream replica and exists only there, so rebuilding the upstream replica means recreating its slots.

When an upstream replica is promoted, its downstream replicas follow it onto the new timeline without intervention, as long as recovery_target_timeline is left at its default of latest.

Smaller recipes

The delayed replica

recovery_min_apply_delay = '4h' on a dedicated replica makes it replay each commit only once that commit is four hours old. Its purpose is operator error: the DELETE without a WHERE, the deployment that corrupted a column. When that happens, pause replay on the delayed replica at once (pg_wal_replay_pause()), which stops the clock, and then copy the lost rows out at leisure. On a delayed replica, pg_get_wal_replay_pause_state() can go on reporting pause requested for as long as replay is waiting out the delay on a commit. The pause still takes effect before that commit is applied, so a script that waits for paused here may wait for hours to no purpose.

The replica receives WAL at the normal rate and stores it until it is due, so its disk has to hold a delay’s worth of WAL. Keep hot_standby_feedback off on it and keep application reads away from it. It does not replace backups and point-in-time recovery. It is faster than they are for a mistake noticed inside the window, and no help at all for one noticed after.

Synchronous replicas and read traffic

A synchronous replica under the default synchronous_commit = on acknowledges a commit when the WAL is flushed to its disk, which does not depend on replay. Queries on that replica can delay replay without delaying commits on the primary. Under remote_apply, the acknowledgment waits for replay, and a query that holds up replay on that replica holds up every commit on the primary that is waiting on it, for as long as max_standby_streaming_delay allows. A remote_apply replica should run short queries only, with feedback on and a short delay. For the same reason, a delayed replica must never be a synchronous one.

Keeping the failover candidate ready

Give it the primary’s hardware and settings, keep long queries off it, and consider sending it a small share of the ordinary short read traffic. (This is the one deliberate exception to the one-job rule.) A replica that serves no queries has in its cache what replay has touched and little of what the application only reads, and after a promotion it will serve the first minutes of production from disk. (pg_prewarm’s autoprewarm worker runs on replicas too, and restores a replica’s cache after that replica restarts.)

One operating system

Indexes on text are ordered by collation rules that, by default, come from the operating system’s C library, and those rules change between operating system releases. (ICU collations have the same exposure, through the version of the ICU library.) A replica running a different release from its primary can disagree with the primary about how strings sort. Its indexes, which are block-for-block copies of the primary’s, are then wrong on the replica and sound on the primary: queries on the replica miss rows that are there. Keep the operating system release identical across a primary and all of its replicas, and treat an operating system upgrade as a change to the whole set.

What to watch

On the primary, for each replica connected to it:

1
SELECT application_name, state, sync_state,
2
       pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS not_yet_on_disk,
3
       pg_wal_lsn_diff(flush_lsn, replay_lsn)           AS on_disk_not_replayed,
4
       replay_lag
5
  FROM pg_stat_replication;

The two byte counts mean different things. When the first grows, WAL is not reaching the replica’s disk: look at the network, and at the replica’s storage. When the second grows while the first stays small, the WAL has arrived and replay is not applying it, because it is waiting on a query, because it cannot keep up, or because you paused or delayed it. A replica missing from the view altogether is disconnected. (On an upstream replica, pg_current_wal_lsn() is not available. Use pg_last_wal_receive_lsn() in its place.)

On each replica:

1
SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
2
            THEN interval '0'
3
            ELSE now() - pg_last_xact_replay_timestamp()
4
       END AS replay_delay;

The bare now() - pg_last_xact_replay_timestamp() found in many monitoring scripts reports steadily growing lag whenever the primary is idle, because the timestamp belongs to the last transaction replayed and there has not been another. The CASE fixes that and has a blind spot of its own: a replica cut off from the primary has replayed everything it received, and reports zero. Use both queries. On a replica that restores from the archive without streaming, pg_last_wal_receive_lsn() is null and the expression behaves like the bare form. The comparison also involves both servers’ clocks, so clock skew shows up as lag.

Alert on:

• Replay delay beyond what that replica’s readers were promised.
• A replica absent from pg_stat_replication on its upstream server.
• The rate of change of the counters in pg_stat_database_conflicts on each replica.
• On the primary, the age of each replica’s xmin (the queries are in Part 1), and any row in pg_replication_slots where active is false or wal_status is unreserved or lost.
• Free space for pg_wal on every server: on the primary because of slots, and on replicas because of stalled, paused, or delayed replay.

Set the thresholds per replica. A reporting replica that is two hours behind is working as designed. A failover candidate that is two minutes behind is an incident.