Offloading Read and Analytic Workloads

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.