การถ่ายโอนภาระงานอ่านและวิเคราะห์

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.

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.

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

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.

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.

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

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.

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.

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.

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.

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?

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.