Part 1: What a read replica is, and what it costs

Adding a read replica is the first scaling move most PostgreSQL installations make, and it is usually the right one. It is also the point at which a team learns that a replica is a database in permanent recovery that happens to answer queries. Nearly every surprising thing a replica does follows from that fact.

What a replica is

A binary replica is a second PostgreSQL server that begins life as a physical copy of the primary’s data directory (a base backup, usually taken with pg_basebackup) and then stays current by applying every change the primary makes, in the order the primary made them. The result is a block-for-block physical copy. It has the same databases, the same tables and indexes, the same roles, and the same bloat. What a query sees on a replica cannot differ from the primary in any respect except how far behind it is.

The vocabulary is inconsistent, so it is best settled early. The server that accepts writes is the primary. A server that keeps itself current by applying the primary’s changes is a standby, and a standby that accepts read-only queries while doing so is a hot standby; those are the terms the PostgreSQL documentation uses. “Read replica,” “binary replica,” “physical replica,” “streaming replica,” and “secondary” all mean a hot standby in practice. I use replica here, and “binary” where it has to be distinguished from the other kind.

The other kind is logical replication, which decodes the same stream of changes into row-level inserts, updates, and deletes and applies them to an independent, writable database. Logical replication can copy a subset of tables and can cross major versions. Binary replication can do neither. In exchange, binary replication is simpler to operate, replicates everything (schema changes included) without being told to, and puts very little load on the primary. For scaling reads in a conventional environment, binary replication is the default choice, and it is the subject here.

The write-ahead log, briefly

Every change PostgreSQL makes to a table or index is first described in a record appended to the write-ahead log (WAL). The rule is that the WAL record must reach durable storage before the changed data page does. A commit is a flush of the WAL; the data files themselves are brought up to date later. If the server crashes, it starts from the most recent checkpoint and replays WAL forward until it runs out, at which point the data files reflect every committed transaction. (Unlogged tables are the exception, by design. Their contents are not written to the WAL, they are not replicated, and a query against one on a replica fails.)

WAL replay does not care where the WAL came from. Give a PostgreSQL server a base backup and a supply of WAL from another server, and it will replay that WAL for as long as the supply lasts. That is all a replica is. A file named standby.signal in the data directory tells the server, at startup, to stay in recovery when it reaches the end of the WAL it has and wait for more.

Three properties of WAL account for most of a replica’s behavior.

WAL is physical. A record says, in effect, “change these bytes on block 1,204 of this file.” It does not say UPDATE orders SET .... A replica can therefore apply WAL only to files laid out exactly as the primary’s are. This is why the primary and its replicas must run the same major version of PostgreSQL on the same hardware architecture, and why a replica is always a copy of the entire cluster. (In PostgreSQL’s vocabulary a “cluster” is one server instance and all of its databases, not a group of servers.) There is no way to replicate one database, or one table, or everything except the audit log.

WAL has positions. Every record has a log sequence number (LSN), a 64-bit byte offset into the WAL stream. Replication progress is always expressed in LSNs: how far the primary has written, how far the replica has received, how far the replica has replayed. Replication lag is the distance between two of them.

WAL moves in two ways. It is stored in segment files, 16MB each by default, and the older delivery mechanism is to copy each completed segment to an archive, from which a replica fetches it with restore_command. The newer one is streaming replication: a walreceiver process on the replica connects to a walsender process on the primary, and records are sent as they are generated, without waiting for a segment to fill. With streaming, a healthy replica is typically less than a second behind. Most production installations use both, with streaming for currency and the archive as the fallback for a replica that has been disconnected so long that the primary no longer has the WAL it needs.

On the replica, two processes divide the work. The walreceiver writes incoming WAL to the replica’s own disk. The startup process (the same process that performs crash recovery) reads that WAL and applies it to the data files. Receiving and replaying are independent of each other: a replica can have received and safely stored WAL that it has not yet applied. That distinction matters when replay stalls.

The two server settings all of this depends on are already at the right values in a stock installation. wal_level defaults to replica, which writes enough information to the WAL to support replicas, and hot_standby defaults to on, which allows a replica to accept queries while it replays.

The primary must keep WAL until every replica has it. An archive takes care of that. So does a replication slot, which is a record on the primary of how far a particular replica has progressed, and behind which the primary will not discard WAL. Slots are convenient and dangerous in equal measure: a slot belonging to a replica that has gone away will retain WAL until the primary’s disk is full, unless max_slot_wal_keep_size sets a limit.

Streaming replication is asynchronous by default. The primary does not wait for any replica before reporting a commit to the client. PostgreSQL can be configured to wait (synchronous replication), at the cost of commit latency and of making commits on the primary depend on the health of a replica. Everything here assumes the asynchronous default unless stated otherwise.

What a replica is good for

Read scaling is the obvious use. Queries that do not write can be sent to a replica, and there can be many replicas. A replica can also feed other replicas (cascading replication), which keeps down the number of direct connections to the primary.

High availability is the other. A replica is a complete, nearly current copy of the database that can be promoted to a primary within moments, using pg_ctl promote or pg_promote(). Most installations that have a replica at all have it for this reason first and for read traffic second. Because replication is asynchronous, a promoted replica may be missing the last transactions the old primary committed; how many depends on how far behind it was when the primary failed.

A replica is also a place to put work that would hurt the primary: reporting queries, exports, pg_dump, base backups. And a replica can be deliberately held behind the primary with recovery_min_apply_delay, which gives an operator a window in which to recover from a mistaken DELETE before the replica applies it too.

What a replica cannot do

It cannot write. A session on a replica is never assigned a transaction ID and cannot generate WAL, so the prohibition goes well past INSERT, UPDATE, and DELETE. Temporary tables cannot be created. Sequences cannot be advanced with nextval(). SELECT ... FOR UPDATE and FOR SHARE fail, because taking a row lock modifies the row. LISTEN and NOTIFY are unavailable, as is the serializable isolation level. Applications described by their owners as read-only frequently turn out to do one of these things: a reporting tool that stages results in a temporary table, or a web framework that updates a last-seen timestamp on every request. Find out before routing the traffic.

It cannot have its own indexes or its own statistics. An index that would help only the reporting queries on the replica has to be created on the primary, where it will be maintained on every write and copied to every replica. ANALYZE cannot be run on a replica; the planner statistics it uses are the primary’s. Autovacuum does not run there either. All maintenance happens on the primary and arrives through the WAL.

It does not scale writes. Every replica has to apply every change the primary makes, so adding replicas adds read capacity and nothing else. Replay is also performed by a single process, where the primary generated that WAL with as many concurrent sessions as it had clients. A primary under sustained heavy write load can produce WAL faster than a replica can apply it, and a replica in that state falls further behind until the load eases.

It cannot run a different major version, which rules out a binary replica as a means of performing a major version upgrade. (Minor versions can differ briefly. When applying a minor release, update the replicas first.)

It must be configured at least as generously as the primary in a few specific respects. max_connections, max_prepared_transactions, max_locks_per_transaction, max_wal_senders, and max_worker_processes size shared memory structures that replay depends on. A replica on which any of them is lower than on the primary will pause replay until the setting is corrected and the replica restarted. When raising one of these, change the replicas first.

It is behind. Usually by well under a second, sometimes by much more, and the application cannot assume which. The standard failure is read-your-writes: a user saves a change, the write goes to the primary, the page reloads from a replica that has not yet replayed the commit, and the change appears to have been lost. There are three responses. The application can send reads that must see a recent write to the primary. The primary can be configured for synchronous replication with synchronous_commit = remote_apply, so that a commit does not return until the synchronous replicas have replayed it; this is expensive, and it covers only the replicas the primary is waiting on. Or, starting with PostgreSQL 19 (in beta at the time of writing), a session on the replica can issue WAIT FOR LSN with an LSN taken on the primary after the commit, and proceed once the replica has replayed that far.

The last limitation is the least obvious one. A query running on a replica can be cancelled, through no fault of its own, by replication.

Query conflicts

PostgreSQL implements concurrency by keeping multiple versions of rows (MVCC). An UPDATE does not overwrite a row; it writes a new version and marks the old one as superseded. A DELETE only marks. The old versions stay in the table so that transactions which began before the change can go on seeing the data as it was. Once no running transaction could possibly need an old version, it is dead, and VACUUM (or the incremental page cleanup that ordinary queries perform as they go) removes it.

“No running transaction” is where replicas come in. The primary decides what is dead by looking at its own running transactions. By default it knows nothing about the queries running on its replicas.

Consider a query that begins on a replica and takes a snapshot. While it runs, rows it can see are updated on the primary, the old versions become dead as far as the primary can tell, and vacuum removes them. The WAL record describing that removal arrives at the replica. The replica’s query still needs those row versions. On a single server this situation cannot arise, because vacuum would have seen the query and left the rows alone. On the replica it has already happened: the WAL record describes a change the primary has made, and the replica is not permitted to decline it.

The replica has two options. It can stop replaying WAL until the query finishes, or it can cancel the query. PostgreSQL does the first for a bounded time and then the second.

Row cleanup is the most common source of conflicts. It is not the only one:

  • Snapshot conflicts. Replay of a cleanup record that removes row versions a replica query might still be able to see. The test is coarse. It compares the age of the query’s snapshot with the age of the rows being removed, for every query in the same database, and it does not ask whether the query reads the table being cleaned. A long report can be cancelled by cleanup of a table it never touches. Marking a page all-visible in the visibility map counts as well, so VACUUM can cause these even on a table with nothing to remove.
  • Lock conflicts. Replay of an ACCESS EXCLUSIVE lock taken on the primary, on a table a replica query is using. DROP TABLE, TRUNCATE, and many forms of ALTER TABLE take one. So, less obviously, does VACUUM when it truncates empty pages from the end of a table.
  • Buffer pin conflicts. Replay of a cleanup record needs exclusive access to a page that a replica query is holding, whether or not the query can see the rows being removed.
  • Dropped objects. A database dropped on the primary disconnects every session connected to it on the replica, at once, whatever the delay settings say. A tablespace dropped while replica queries have temporary files in it cancels every active query on the replica, since replay does not work out which of them the files belong to.

The replica counts cancellations by type, per database, in pg_stat_database_conflicts (confl_snapshot, confl_lock, confl_bufferpin, and so on). Look at that view, on the replica, before changing any settings. The right remedy depends on which counter is moving.

A cancelled query receives this:

1ERROR:  canceling statement due to conflict with recovery


2DETAIL:  User query might have needed to see row versions that must be removed.

The DETAIL line varies with the type of conflict (and the “might” in this one is meant literally). The SQLSTATE is 40001, the serialization failure code that PostgreSQL uses elsewhere to mean “there was nothing wrong with the request; try it again,” and trying again is correct. The query may be rerun immediately, in a new transaction, and will often succeed. Applications that read from replicas need retry logic for this error, with a limit on the number of attempts.

In most cases only the statement is cancelled. Some sessions are disconnected outright: one that is sitting idle inside a transaction while holding the conflicting lock or snapshot, one that was inside a subtransaction when the conflict was resolved (a savepoint, a PL/pgSQL block with an EXCEPTION clause, or what most ORMs call a nested transaction), and every session connected to a database that has been dropped. The application has to be prepared to reconnect as well as to retry.

The delay settings

How long replay will wait before cancelling is set on the replica: max_standby_streaming_delay for WAL arriving by streaming, and max_standby_archive_delay for WAL read from the archive (or from the replica’s own pg_wal directory, as after a restart). Both default to 30 seconds. A value of -1 means wait forever. A value of 0 means cancel conflicting queries immediately.

These settings are widely misread as “a query on the replica gets 30 seconds.” That is not what they mean. The clock does not start when the query starts, or when the conflict is found. It starts when the replica received the WAL it is trying to apply. For streaming, it is reset only once replay has caught up with everything received; for archived WAL, it restarts with each segment file. The streaming setting therefore limits how far conflicts may hold replay as a whole behind, and it is one allowance shared by every query on the replica.

Suppose a replica is fully caught up. Query A conflicts with a WAL record, and replay stops to wait for it. Thirty seconds later A has not finished, so it is cancelled, and replay resumes 30 seconds behind. A moment later query B conflicts with another record. B gets no grace period. Replay has not caught up, so the clock has not been reset and the allowance is already spent. B is cancelled at once, and so is every conflicting query after it until replay catches up. A replica that is behind for unrelated reasons (a burst of writes on the primary, for instance) is in the same position.

The documentation’s own summary is the right one: when tables on the primary are updated regularly and heavily, a finite max_standby_streaming_delay behaves like a statement_timeout for long queries on the replica.

Raising the delay protects queries at the expense of currency, and the expense is larger than it first appears. While the startup process waits on a conflict, it applies nothing. Replay is serial, so one conflict on one table stops replay for every table in every database on that replica. Every other session on the replica sees data that grows staler by the second. With max_standby_streaming_delay = -1 and a report that runs for six hours, the replica can be six hours behind when the report finishes, and everyone else who queried it in the meantime got answers up to six hours old without being told.

WAL continues to arrive during the wait. The walreceiver keeps writing it to the replica’s disk, so nothing is lost and the primary is not held up. It also accumulates there, because a replica cannot recycle WAL it has not replayed, and a long enough stall can fill the replica’s disk. The backlog still has to be replayed, and a replica that is promoted replays the WAL it has before it becomes a primary. A failover that should take moments takes as long as the backlog does.

That is the trade: replication lag against query cancellation. Because the settings live on the replica, the trade can be made differently for each replica, and it should be. A replica kept for failover wants a short delay, so that it stays close to the primary. A replica kept for long reports can tolerate a long one. Asking one replica to be both produces either cancelled reports or a stale failover target.

To see when replay is being held up, set log_recovery_conflict_waits = on on the replica. It logs each time the startup process waits on a conflict for longer than deadlock_timeout. On the primary, the replay_lag column of pg_stat_replication approximates how long recent transactions took to become visible on each replica.

hot_standby_feedback

The conflict arises because the primary does not know what the replica’s queries need. hot_standby_feedback tells it. With the setting on, the replica periodically reports to the primary the oldest snapshot any of its queries is using (its xmin), and vacuum on the primary treats that as if a transaction of the same age were open on the primary itself. Row versions the replica’s queries might need are not removed, no conflicting cleanup record is generated, and, for as long as the replica stays connected, snapshot conflicts stop.

The setting is off by default and is turned on at the replica. Reports travel over the replica’s existing connection to the primary and are sent no more often than once per wal_receiver_status_interval (10 seconds by default; setting that interval to 0 silently disables feedback). In a cascade, each replica that has feedback on relays the report upstream, so it reaches the primary only if every replica along the way has feedback on too. The xmin each replica is reporting can be seen on the primary in pg_stat_replication.backend_xmin, or in pg_replication_slots.xmin if the replica uses a slot.

The case for it is the one the documentation makes: the primary is no worse off than if those queries had been run on the primary directly, and the work of executing them has still been moved elsewhere. For replicas serving short queries that is a good bargain, and turning it on makes the most common class of cancellation go away.

The case against it takes longer to state.

The cost moves to the primary

A long-running query on a replica with feedback enabled is, as far as vacuum on the primary is concerned, a long-running transaction on the primary. Dead rows accumulate behind it. They accumulate in every table in every database, not only in the tables the query reads, because the reported xmin holds back cleanup for the whole server. (On this point the documentation’s reassurance is too generous. A transaction on the primary holds back cleanup only in its own database.) A six-hour report on the replica is six hours’ worth of dead rows on the primary that vacuum cannot remove. Tables and indexes grow, the queries on the primary that scan them slow down, and the primary is the server the replica was supposed to be protecting.

This is also hard to see. The usual check for whatever is holding back vacuum is to look for old transactions in pg_stat_activity on the primary, and that check comes up empty, because the transaction is on another machine. The places to look are the two columns named above:

1SELECT application_name, backend_xmin, age(backend_xmin) AS xmin_age


2  FROM pg_stat_replication;


3


4SELECT slot_name, active, xmin, age(xmin) AS xmin_age


5  FROM pg_replication_slots;

A large and growing xmin_age means a replica is holding back cleanup, and the next step is to find the query responsible on that replica.

It does not prevent every conflict

Feedback addresses snapshot conflicts and nothing else. Lock conflicts are untouched: DDL on the primary will still cancel replica queries on the affected table once the delay expires. So will vacuum’s truncation step. When VACUUM finds enough empty pages at the end of a table (1,000 of them, or a sixteenth of the table), it takes a brief ACCESS EXCLUSIVE lock in order to return them to the operating system; that lock is replayed on the replica and conflicts with any query reading the table. This is the usual explanation when cancellations continue after hot_standby_feedback has been turned on, and confl_lock will be the counter that is rising. The remedy is to disable truncation, either per table with the vacuum_truncate storage parameter or, from PostgreSQL 18, for the whole server with the vacuum_truncate setting. Buffer pin conflicts are likewise unaffected by feedback.

It works only while the replica is connected

Feedback is a property of the live connection. If the replica disconnects (a network interruption, a restart of the primary), the primary stops holding rows on its behalf, and vacuum proceeds without it. Queries in flight on the replica when it reconnects are exposed to exactly the conflicts feedback was meant to prevent. A replication slot closes that gap, because the slot retains the replica’s xmin on the primary while the replica is away.

The slot then creates the opposite hazard. A replica that is decommissioned, or that fails and is never brought back, leaves behind a slot that goes on holding both its WAL position and its xmin. The primary accumulates WAL until the disk fills, and dead rows until someone notices. max_slot_wal_keep_size caps the WAL a slot may retain, and PostgreSQL 18 added idle_replication_slot_timeout, which invalidates slots that have been inactive for too long. Set one or both, and alert on inactive slots regardless.

Choosing

The cost of running long queries against replicated data has to land somewhere, and hot_standby_feedback chooses where. With it off, the replica pays, in cancelled queries or in lag, according to the delay settings. With it on, the primary pays, in bloat. The question to ask of each replica is which of those it can best afford, and the answer follows from what the replica is for.

A replica that exists to be promoted should run nothing long. Keep reporting queries off it entirely and give it a short max_standby_streaming_delay, so that it stays current.

A replica serving the application’s ordinary read traffic, where queries finish in milliseconds or seconds, should have hot_standby_feedback = on and the default delays. Set a statement_timeout and an idle_in_transaction_session_timeout in that replica’s configuration, so that a single runaway query or abandoned session cannot hold back cleanup on the primary for longer than you have decided to allow. This is the configuration I recommend as the starting point for most installations.

A replica for reporting and analytics, where queries run for minutes or hours, should have hot_standby_feedback = off and a long max_standby_streaming_delay, up to -1. The cost then lands on that replica as lag, where the only people affected are the ones running the reports. It should not be a failover candidate, and nothing that needs current data should read from it. Bloat on the primary is paid for by every user of the database. Lag on a reporting replica is paid for by the reports.

These are three different servers. A replica that is asked to do all three jobs will do each of them badly, and the failover job is the one that will suffer at the worst possible moment.

(I consult on PostgreSQL through PGX Inc..)