PostgreSQL Performance & Query Optimization · 课时

使用热备与负载均衡扩展读取能力

学习如何将读取流量转移到流式复制的备用服务器，理解复制延迟，并在主服务器与副本之间路由查询以实现横向读取扩展

使用热备与负载均衡扩展读取能力 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。这是第 4 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Why Scale Reads?

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.

Hot Standby Basics

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

Enabling Read Queries on a Standby

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

SHOW hot_standby;

Detecting Recovery Mode

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

SELECT pg_is_in_recovery();

Understanding Replication Lag

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.

Measuring Lag

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

SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;

Routing in the Application

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.

Read-After-Write Consistency

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

Load Balancing Across Replicas

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.

Trade-offs and Limits

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.

Watching Replication from the Primary

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.

SELECT client_addr, state,
       replay_lag
FROM pg_stat_replication;

Quick Check

Test your read-scaling knowledge.

Recap

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

常见问题解答

「使用热备与负载均衡扩展读取能力」课时是免费的吗？

是的 — 「使用热备与负载均衡扩展读取能力」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「使用热备与负载均衡扩展读取能力」这节课中我会学到什么？

学习如何将读取流量转移到流式复制的备用服务器，理解复制延迟，并在主服务器与副本之间路由查询以实现横向读取扩展你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 4 节课，共 4 节。

「使用热备与负载均衡扩展读取能力」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 PostgreSQL Performance & Query Optimization