Binary read replicas in PostgreSQL, Part 2

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.”

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.

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.)

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:

1host=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.)

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.

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.

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.

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.

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.

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.

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:

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.

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.

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.

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.

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

1SELECT pg_wal_replay_pause(); 2-- run the batch 3SELECT 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_s