0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Offloading Read and Analytic Workloads

Route heavy reporting traffic to logical replicas to protect primary OLTP latency.

Offloading Read and Analytic Workloads is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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.

Frequently asked questions

Is the “Offloading Read and Analytic Workloads” lesson free?

Yes — the full text of “Offloading Read and Analytic Workloads” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Offloading Read and Analytic Workloads”?

Route heavy reporting traffic to logical replicas to protect primary OLTP latency. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Offloading Read and Analytic Workloads” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Publications, Subscriptions, and Replica Identity
  2. Offloading Read and Analytic Workloads
  3. Near-Zero-Downtime Major Version Upgrades
  4. Monitoring Replication Lag and Slot Bloat
← Back to PostgreSQL Performance & Query Optimization