Postgres Hot Standby means you can run read queries against your replica databases without them complaining about conflicting writes.
Let’s see it in action. Imagine you have a primary PostgreSQL server handling all your writes, and a Hot Standby replica that’s a near real-time copy.
Primary Server (pg_primary):
-- On pg_primary
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (username) VALUES ('alice'), ('bob');
Replica Server (pg_replica):
Initially, this replica is just receiving WAL (Write-Ahead Logging) from pg_primary.
Now, let’s try a read query on the replica while pg_primary is still accepting writes.
-- On pg_replica
SELECT * FROM users WHERE username = 'alice';
This query will succeed. The replica is designed to allow SELECT statements to run concurrently with replication. It achieves this by using a mechanism called "snapshot isolation." When a read query starts on the replica, it gets a consistent snapshot of the database as it existed at that moment. Even if new data is being written to the primary and replicated over, the replica’s read query won’t be blocked or see inconsistent data. It effectively "freezes" its view of the data for the duration of the query.
The primary problem Hot Standby solves is read scaling. Without it, any read query hitting a replica would often be blocked by ongoing write operations or potentially see uncommitted data, making it unreliable for production workloads. Hot Standby allows you to offload read traffic from your primary, improving its performance and availability for writes, while still getting up-to-date data on your replicas.
To enable Hot Standby, you need a few key configurations on your primary and replica.
On the primary:
wal_level = replica(orlogical): This ensures that enough information is written to the WAL to allow for replication.max_wal_senders = 10: This sets the maximum number of concurrent WAL sender processes. You need at least one for each replica.archive_mode = onandarchive_command = 'cp %p /path/to/your/wal_archive/%f': Archiving WAL segments is crucial for PITR (Point-In-Time Recovery) and for replicas to catch up if they fall too far behind.archive_commandspecifies how to copy WAL files to an archive location.
On the replica:
hot_standby = on: This is the core setting that enables read queries on the replica.primary_conninfo = 'host=pg_primary port=5432 user=repl_user password=repl_password': This tells the replica how to connect to the primary to receive WAL.restore_command = 'cp /path/to/your/wal_archive/%f %p': This command tells the replica how to retrieve WAL files from the archive if it needs to catch up.
After configuring and restarting both servers, you’d typically initialize the replica using pg_basebackup from the primary.
pg_basebackup -h pg_primary -U repl_user -D /var/lib/postgresql/data_replica -Fp -Xs -P
The -Xs flag ensures that WAL is streamed during the base backup, and -P shows progress.
Once the replica is running with hot_standby = on, you can connect to it and run SELECT queries. You can even monitor replication lag:
-- On pg_replica
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
Comparing these LSNs (Log Sequence Numbers) with pg_current_wal_lsn() on the primary gives you an idea of how far behind the replica is.
The most surprising thing about Hot Standby is how it manages transaction visibility. A SELECT query on a Hot Standby replica sees the database state as of the start of the query. This means it’s immune to concurrent INSERT, UPDATE, or DELETE operations happening on the primary after the SELECT began. It doesn’t block writers, and writers don’t block it. This is achieved through Postgres’s MVCC (Multi-Version Concurrency Control) and the replica’s ability to apply WAL records while serving reads from its own transaction-ID horizon.
One subtle point is that while reads are generally non-blocking, certain DDL operations (like ALTER TABLE) on the primary can cause brief blocking on the replica if they conflict with an ongoing read. Also, vacuuming on the replica is necessary to reclaim space from dead tuples that are no longer visible to any active queries. If vacuum doesn’t run, you can experience bloat even on a read-only replica.
The next thing you’ll likely run into is managing replication lag and ensuring your read replicas are sufficiently up-to-date for your application’s needs, especially if you have write-heavy workloads.