PostgreSQL Performance & Query Optimization · 课时

卸载读取与分析工作负载

将繁重的报表流量路由到逻辑副本，保护主库 OLTP 延迟。

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

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

Why Offload Reads at All?

On a busy PostgreSQL primary, the same instance serves OLTP traffic (small, fast, latency-sensitive writes and point reads) and analytic traffic (large sequential scans, aggregations, reporting joins).

The problem: a single heavy reporting query can saturate shared buffers, evict hot OLTP pages, and inflate I/O queue depth. Your p99 checkout latency suddenly doubles because someone ran a quarterly revenue report.

Goal: keep the primary lean for transactional work.
Strategy: route heavy read and analytic queries to a replica that holds a copy of the data.

This lesson focuses on using logical replication to build purpose-shaped read replicas for analytics.

Physical vs Logical Replication

PostgreSQL offers two replication models, and choosing correctly is the key decision for read offloading.

Physical (streaming) replication: ships WAL byte-for-byte. The replica is an exact block-level clone — same schema, same indexes, same bloat. Great for HA and read scaling of identical queries.
Logical replication: decodes WAL into row-level changes (INSERT/UPDATE/DELETE) and replays them via SQL. The subscriber is an independent database — you can add different indexes, extra columns, materialized rollups, or even a different major version.

For analytic offload, logical replication shines: the analytics node can carry heavy reporting indexes the primary should never pay to maintain.

Setting Up a Publication

On the primary (the publisher), you must set wal_level = logical and create a publication — a named set of tables whose changes will be streamed.

You can publish all tables, a subset, or even filter rows and columns (PostgreSQL 15+). For an analytics offload you typically publish exactly the fact and dimension tables the reports need.

-- On the primary: declare what gets replicated
-- (requires wal_level = logical in postgresql.conf)
CREATE PUBLICATION analytics_pub
  FOR TABLE orders, order_items, customers
  WITH (publish = 'insert, update, delete');

-- Inspect existing publications
SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete
FROM pg_publication;

Creating the Subscriber

On a separate host — your analytics replica — create a matching schema (at least the published tables) and then a subscription. The subscription connects to the publisher and starts an initial data copy followed by continuous streaming.

Because the subscriber is independent, give it the configuration analytics needs: more work_mem, more maintenance_work_mem, and parallel-friendly settings — without touching the OLTP primary.

-- On the analytics node: subscribe to the primary's publication
CREATE SUBSCRIPTION analytics_sub
  CONNECTION 'host=primary.db port=5432 dbname=shop user=repl password=secret'
  PUBLICATION analytics_pub
  WITH (copy_data = true, streaming = on);

-- Watch initial sync + streaming status
SELECT subname, srrelid::regclass AS rel, srsubstate
FROM pg_subscription_rel
JOIN pg_subscription ON oid = srsubid;

Add Analytic-Only Indexes on the Replica

Here is the payoff of logical replication. The primary keeps only the lean indexes OLTP needs. The analytics subscriber adds wide, expensive indexes that would slow every write on the primary but make reports fast on the replica.

BRIN indexes for huge append-only time-series fact tables.
Covering / partial indexes tailored to dashboard queries.
Expression indexes for report-specific predicates.

Maintaining these here costs the analytics node write amplification — but the primary never pays for it.

-- These indexes live ONLY on the analytics subscriber
CREATE INDEX brin_orders_created
  ON orders USING brin (created_at) WITH (pages_per_range = 32);

CREATE INDEX idx_orders_report
  ON orders (customer_id, created_at)
  INCLUDE (total_amount, status)
  WHERE status <> 'cancelled';

Routing Reads at the Application Layer

Offloading only helps if traffic actually reaches the replica. You route at the application or proxy layer:

Connection-level split: a read/write pool to the primary, a read-only pool to the analytics node.
Proxy-based: PgBouncer/Pgpool or a service mesh directs SELECT reporting traffic by user, schema, or query tag.

A clean pattern is a dedicated analytics_ro database role with statement timeouts and lower priority, so a runaway report can never threaten transactional SLAs.

-- Give reporting sessions a safety harness on the replica
ALTER ROLE analytics_ro SET statement_timeout = '120s';
ALTER ROLE analytics_ro SET work_mem = '256MB';
ALTER ROLE analytics_ro SET default_transaction_read_only = on;

-- Confirm where a session actually landed (run on each node)
SELECT pg_is_in_recovery() AS is_replica, current_setting('work_mem');

Pre-Aggregating with Materialized Views

The replica is the perfect home for materialized views that pre-compute expensive rollups. Dashboards then read a tiny summarized table instead of scanning millions of fact rows on every load.

Refresh them on the analytics node on a schedule. Using REFRESH MATERIALIZED VIEW CONCURRENTLY avoids locking readers out during the rebuild (it requires a unique index on the view).

-- Lives on the analytics replica only
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       count(*)            AS order_count,
       sum(total_amount)   AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1;

CREATE UNIQUE INDEX ON daily_revenue (day);

-- Scheduled refresh that does not block dashboard readers
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Understanding Replication Lag

Logical replication is asynchronous by default: the subscriber trails the primary by some lag. For analytics this is almost always fine — a report on data that is two seconds old is acceptable. For "read-your-own-write" UI flows it is not.

Decision rule: send eventually-consistent, aggregate-heavy reads to the replica; keep read-after-write and exact-current-state reads on the primary.

Monitor lag from the publisher's replication slot — a stalled subscriber causes WAL to accumulate and can fill the primary's disk.

-- On the primary: how far behind is each logical slot?
SELECT slot_name,
       active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
       ) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Filtering Down to What Analytics Needs

You rarely need every column or row on the analytics node. PostgreSQL 15+ lets a publication apply row filters and column lists, shrinking the replicated dataset and the WAL decoding cost.

Row filter: replicate only rows that matter to reporting (e.g. non-archived orders).
Column list: exclude wide or sensitive columns (PII, large JSON blobs) the reports never touch.

Smaller replicated footprint means faster initial sync and less network/decoding overhead.

-- Publish only relevant rows and a subset of columns (PG 15+)
CREATE PUBLICATION analytics_pub
  FOR TABLE orders (id, customer_id, created_at, total_amount, status)
    WHERE (status <> 'draft' AND created_at >= '2024-01-01');

Verifying the Plan Uses the Replica's Indexes

After building analytic indexes and materialized views on the subscriber, confirm the planner actually uses them. Run EXPLAIN (ANALYZE, BUFFERS) on the analytics node for your real reporting queries.

You want to see index scans / bitmap scans on the report-specific indexes and, ideally, the dashboard query hitting the materialized view instead of the raw fact table. Compare shared read buffers before and after — that drop is the I/O you removed from the primary.

-- Run on the analytics replica, not the primary
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT day, revenue
FROM daily_revenue
WHERE day >= current_date - interval '30 days'
ORDER BY day;

Operational Guardrails

A read-offload architecture is only as good as its failure handling. Build in guardrails:

Lag alerting: page when retained_wal or apply lag crosses a threshold — a dead subscriber can fill the primary's disk via the slot.
Sequences & DDL are not replicated: logical replication copies row data, not sequence values or schema changes. Manage DDL deliberately on both sides.
Conflict awareness: never let applications write to replicated tables on the subscriber, or apply will break.
Capacity: the analytics node can be smaller in connections but needs RAM and disk for its extra indexes and materialized views.

Quick Check: Choosing the Offload Target

A dashboard runs a 9-second aggregation over 200M order rows every time a manager opens it, and it is starving OLTP checkout latency on the primary. The report tolerates data that is a few seconds stale. What is the best move?

Recap: Offloading Reads with Logical Replication

You learned how to protect primary OLTP latency by routing heavy reporting traffic to a purpose-built logical replica.

Logical over physical when the analytics node needs its own indexes, columns, or rollups.
Publish exactly the tables (and rows/columns) reports need with a PUBLICATION; consume them with a SUBSCRIPTION.
Add analytic-only indexes and materialized views on the subscriber so the primary never pays for reporting structures.
Route reads via pools/proxies and a constrained read-only role with statement timeouts.
Send only stale-tolerant, aggregate reads to the replica; keep read-after-write on the primary.
Monitor replication slot lag — a stalled subscriber can fill the primary's disk.

The result: dashboards get fast, dedicated infrastructure, and your transactional SLAs stay safe.

常见问题解答

「卸载读取与分析工作负载」课时是免费的吗？

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

「卸载读取与分析工作负载」这节课中我会学到什么？

将繁重的报表流量路由到逻辑副本，保护主库 OLTP 延迟。你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

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

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

「卸载读取与分析工作负载」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 PostgreSQL Performance & Query Optimization