การขยายการอ่านด้วย Hot Standby และการกระจายโหลด

A single primary can become a bottleneck when read-heavy traffic grows. By sending SELECTs to read replicas, you free the primary to handle writes and scale reads horizontally by adding more standbys.

A hot standby is a streaming replica that accepts read-only queries while it continuously applies changes received from the primary. It stays nearly in sync via the write-ahead log (WAL).

On the standby, hot_standby must be on (the default in modern versions) so it answers queries instead of just replaying WAL silently.

Your application can ask any node whether it is a read-only standby. A true result means do not send writes here.

Standbys apply WAL slightly behind the primary, so a read may not see the very latest write. This gap is replication lag. For most read traffic it is harmless, but read-after-write flows need care.

On a standby, compare how far replay is behind the last received WAL position to estimate lag in time.

The simplest pattern keeps two connection pools: one to the primary for writes, one to replicas for reads. The app chooses based on the operation.

Right after a user writes data, reading from a lagging replica may show stale results. Mitigations:

• Route that user's next reads to the primary briefly
• Wait until the replica catches up to the write's WAL position

A pooler or proxy such as PgBouncer, Pgpool-II, or HAProxy can distribute read connections across several standbys, spreading load and providing failover if one replica goes down.

Read replicas are powerful but not magic:

• They do not scale write throughput
• Lag means eventual, not immediate, consistency on replicas
• Long queries on a standby can conflict with WAL replay

Plan routing around these realities.

The primary exposes every connected standby in a stats view, including how far behind each one is. Watch this to catch a replica falling dangerously behind.

You learned read scaling:

• Hot standbys serve read-only queries while replaying WAL
• pg_is_in_recovery() identifies a standby
• Replication lag means replicas can be slightly stale
• Route writes to primary, reads to replicas; handle read-after-write
• Use a proxy to load-balance and fail over across replicas