Monolith Read Replica Reporting: Route Cold-Path Queries Safely

What cold path extraction is and who should do it

In most monoliths, two kinds of database traffic share one primary. The hot path is the request-serving work: short reads and writes against a few rows, where latency directly affects users. The cold path is everything else that reads heavily but rarely changes data. That includes dashboards, CSV exports, admin reports and scheduled summaries. These queries scan large ranges, join many tables, sort and aggregate, and can run for seconds or minutes.

The two kinds of traffic compete for the same CPU, buffer cache and disk IO. A single export can push hot index pages out of memory or saturate IO, and checkout latency degrades for reasons that never show up in the checkout code. Cold path extraction moves that reporting load to a streaming read replica so that it consumes the replica's resources and leaves the primary's alone.

Adding a replica is the easy part. The real work is routing and consistency. You have to decide which queries may go to the replica, make that choice explicit in code, and accept that the replica lags the primary by some amount of time you must measure.

At a high level there are three parts:

• The primary keeps all writes and latency-sensitive reads.
• The streaming replica applies the primary's changes continuously and serves read-only queries.
• The application holds two named connection pools, such as primary and reporting. Code paths opt in to the reporting pool deliberately and never reach it by accident.

This approach fits monolith teams whose primary is under measurable pressure from reporting load, where that load is read-heavy and the reports can tolerate data that is seconds old. If a report must reflect a write the user just made, or the pressure on the primary comes from writes, a replica will not solve the problem.

Copy+----------------------------------------------------------------------+
|                        Application (monolith)                        |
|                                                                      |
|  Hot path: requests, writes          Cold path: dashboards, exports  |
|  short reads, latency-bound          admin reports, scheduled jobs   |
|            |                                       |                 |
|            v                                       v                 |
|   +------------------+                  +----------------------+     |
|   |  pool: primary   |                  |  pool: reporting     |     |
|   |  default         |                  |  explicit opt-in     |     |
|   +------------------+                  +----------------------+     |
+------------|---------------------------------------|-----------------+
             | reads + writes                        | read-only
             v                                       v
   +--------------------+                 +----------------------+
   |      Primary       |   streaming     |  Streaming replica   |
   |  all writes +      |---------------->|  read-only queries   |
   |  latency-sensitive |   replication   |  applies changes     |
   |  reads             |   (lag: t sec)  |  continuously        |
   +--------------------+                 +----------------------+
   own CPU, cache, IO                       own CPU, cache, IO

The application routes hot-path traffic through the default primary pool and sends cold-path reporting queries through an explicitly chosen reporting pool to a streaming replica, which runs behind the primary by a lag you must measure.

Finding and classifying reporting queries

Start with pg_stat_statements, which aggregates execution statistics per normalized query. Sort by total execution time, not mean time. A 40-second month-end export that runs once a day matters less than a 300 ms dashboard aggregate called thousands of times an hour, because the primary pays for the cumulative cost. Also check the block counters: high shared_blks_read means the query pulls pages from disk and evicts the hot working set from shared buffers, and high temp_blks_written means sorts or hashes spill to disk. Both slow down unrelated OLTP traffic. pg_stat_statements does not record lock waits, so enable log_lock_waits and use log_min_duration_statement to catch long-running reads that hold snapshots open or block DDL and vacuum.

For each candidate, record how stale its data is allowed to be:

Write this classification down as part of the query's contract, for example as an annotation on the repository method or in the module that owns the query. Don't leave it in a spreadsheet. The routing layer reads this label to decide where the query goes, so it has to live next to the code, where reviewers will see it when someone changes the query's semantics.

Copy-- Rank reporting-query candidates by cumulative cost on the primary.
-- Requires the pg_stat_statements extension (column names as of PostgreSQL 13+).
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric / 1000, 1) AS total_sec,   -- what the primary actually pays
  round(mean_exec_time::numeric, 1)         AS mean_ms,     -- shown for context, not for ranking
  shared_blks_read,                                         -- pages pulled from disk/OS cache: evicts hot working set
  round(100.0 * shared_blks_hit
        / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS buffer_hit_pct,
  temp_blks_written,                                        -- sorts/hashes spilling to disk
  left(query, 80)                           AS query_head
FROM pg_stat_statements
WHERE query ILIKE 'select%'                                 -- reads only; writes stay on the primary
ORDER BY total_exec_time DESC                               -- cumulative cost, not single-run duration
LIMIT 25;

-- pg_stat_statements has no lock-wait data. Enable logging so long reads that
-- hold snapshots open or block DDL and vacuum show up in the server log:
ALTER SYSTEM SET log_lock_waits = on;                       -- logs waits longer than deadlock_timeout
ALTER SYSTEM SET log_min_duration_statement = '2s';         -- placeholder threshold; tune per workload
SELECT pg_reload_conf();

A pg_stat_statements query that ranks read statements by total execution time alongside disk-read and temp-spill counters, plus the logging settings that capture the lock waits and long-running reads pg_stat_statements does not record.

Implementing explicit routing with a second connection

Routing is the next step, and it should be explicit. Add a second, named connection pool for the replica and have code opt into it: reporting service objects, background jobs that build exports, and admin endpoints that run aggregates. Everything else stays on the primary.

Automatic read/write splitting looks simpler but fails in a predictable way. A request that inserts an order and then reads it back to render a confirmation sends that read to the replica, which may not have the row yet. With explicit routing, the only queries that hit the replica are ones someone labelled as tolerant of stale data, and the label is visible in review.

Make the replica connection safe by default. Connect as a dedicated role that has only SELECT grants. Set default_transaction_read_only and a reporting-specific statement_timeout on that role with ALTER ROLE ... SET, so the settings apply no matter which client connects. A standby already rejects writes. These settings matter when the pool is misconfigured, for example when a local environment points it at the primary, or after a failover promotes the replica. In those cases a runaway report is cancelled instead of competing with production traffic.

Size the replica pool separately from the primary pool. Reporting queries run longer and less often, so a few connections are usually enough, and capping the pool limits how many heavy scans run at once. Avoid raising work_mem globally, because it applies per sort or hash operation and can multiply across connections. Instead, use SET LOCAL work_mem inside the transaction for the specific queries that sort or aggregate large result sets.

Copy-- Run on the primary. Roles, grants and role settings replicate to the standby.
-- CONNECTION LIMIT is a server-side backstop; the app's replica pool size is the real cap.
CREATE ROLE reporting_ro LOGIN PASSWORD 'change-me-placeholder' CONNECTION LIMIT 5;

GRANT CONNECT ON DATABASE app_production TO reporting_ro;
GRANT USAGE ON SCHEMA public TO reporting_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_ro;

-- Cover tables created later by the migration role (assumed here to be app_owner).
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO reporting_ro;

-- Safe by default for any client that connects as this role,
-- even if the pool is pointed at the primary or the replica is promoted.
ALTER ROLE reporting_ro SET default_transaction_read_only = on;
ALTER ROLE reporting_ro SET statement_timeout = '60s';

-- In a reporting code path: raise work_mem only for this transaction.
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT date_trunc('month', created_at) AS month,
       customer_id,
       sum(total_cents) AS revenue_cents
FROM orders
WHERE created_at >= now() - interval '12 months'
GROUP BY 1, 2
ORDER BY revenue_cents DESC;
COMMIT;  -- work_mem reverts to the server default here

Creates a SELECT-only reporting role with read-only transactions, a reporting statement_timeout and a connection cap applied through ALTER ROLE, then raises work_mem with SET LOCAL for a single large aggregate.

Measuring and enforcing replication lag

Before routing anything, you need a lag number you trust. On the primary, pg_stat_replication exposes write_lag, flush_lag and replay_lag per standby. These show how long WAL takes to be written, flushed to disk and applied on the replica. replay_lag is the one that matters for reporting, because only replayed changes are visible to queries.

The application usually connects only to the replica, so it measures from there with now() - pg_last_xact_replay_timestamp(). That expression reports how long ago the last replayed transaction was committed on the primary. When the primary is idle, no new transactions arrive, so the value keeps growing even though the replica is fully caught up. To fix this, have a scheduled job update a single-row heartbeat table on the primary every few seconds. The replica-side measure then stays bounded by the heartbeat interval, and a large value reliably means real lag.

Enforce a lag budget per query class in the routing layer:

Cap fallbacks with a concurrency limit or token bucket. Lag spikes often happen because the primary is already saturated. Uncapped fallback would send the extracted load back to the primary at the worst moment. When the cap is reached, downgrade those queries to the stale-with-timestamp or retry path.

Copy-- 1) On the primary: per-standby lag as seen by the sender.
--    replay_lag is what reporting cares about: only replayed WAL is visible.
SELECT application_name,
       client_addr,
       state,
       write_lag,
       flush_lag,
       replay_lag
FROM pg_stat_replication;

-- 2) On the primary: a single-row heartbeat table.
CREATE TABLE IF NOT EXISTS replication_heartbeat (
    id         int PRIMARY KEY CHECK (id = 1),
    updated_at timestamptz NOT NULL
);

-- Run this from a scheduler every few seconds (e.g. every 5s).
-- Each run commits a transaction, so the replica always has a recent
-- commit to replay even when application traffic is idle.
INSERT INTO replication_heartbeat (id, updated_at)
VALUES (1, now())
ON CONFLICT (id) DO UPDATE SET updated_at = EXCLUDED.updated_at;

-- 3) On the replica: the lag number the routing layer reads.
--    With the heartbeat running, an idle primary shows at most about one
--    heartbeat interval here, so a large value means real replay lag.
SELECT pg_is_in_recovery() AS is_replica,
       now() - pg_last_xact_replay_timestamp() AS replay_lag,
       (SELECT updated_at FROM replication_heartbeat WHERE id = 1)
           AS data_as_of;  -- show this on dashboards as "data as of"

SQL to read write_lag, flush_lag and replay_lag on the primary, add a heartbeat upsert that keeps the replica's replay timestamp fresh, and read replica-side lag plus a "data as of" timestamp for the routing layer.

Read-your-writes and hot standby query conflicts

Reporting reads break in a specific way: a user edits an invoice, opens the revenue report, and sees the old total because the replica has not replayed that commit yet. There are three ways to handle this, from crude to precise:

• Keep write-adjacent reports on the primary. This works for the few reports users open right after saving, such as "my recent orders."
• Pin the session to the primary for a fixed window after any write. The window must exceed your normal lag. It is simple, but it sends reads to the primary even when the replica has already caught up.
• Compare LSNs. After the write commits, record pg_current_wal_lsn() from the primary in the session. Before routing a read to the replica, check that its pg_last_wal_replay_lsn() has reached that position. If it has not, wait briefly or read from the primary.

Long reports fail for a different reason. Vacuum on the primary removes dead row versions. When the replica replays that cleanup, a report on the replica may still need those rows for its snapshot. Postgres waits up to a configured limit and then cancels the query with a "conflict with recovery" error.

Rolling out incrementally and knowing when to avoid it

Move one query at a time. Give each reporting query its own feature flag that decides which connection it uses, so a misbehaving report can go back to the primary without affecting the others. Before a query starts serving users from the replica, shadow-run it on a sample of requests. Execute it on both connections, compare row counts or a checksum of the result, and log the differences. Some mismatch is expected because of lag. A mismatch that persists after the replica catches up usually means the query depends on something the replica doesn't have, such as a temporary table, a session setting, or a write earlier in the same transaction.

While each flag is ramping up, put primary CPU and IO on the same dashboard as replica lag. The point of the work is lower load on the primary. If those numbers don't drop, you have added a moving part without getting anything back. Also keep one global switch that overrides every per-query flag and sends all reads to the primary. Test that switch before you need it, because it is your response when lag spikes or the replica goes down.

A replica is the wrong tool in these cases:

• The query must see its own writes. If most of a query's calls end up falling back to the primary anyway, routing it to the replica only adds complexity.
• The schema causes the slowness. A missing index or a bad join is just as slow on the replica. Fix it where it is.
• The only replica is your failover standby. Long reports there can delay replay, which increases data loss on promotion. They also compete for resources right when the standby needs to take over. Use a separate replica for reporting.

Alternatives and impact on cost and engineering time

A replica is often not the cheapest way to take reporting load off the primary. Compare it with the alternatives before committing.

Tune first. If a few queries cause most of the load, an index costs less than a server. A replica is a second database to operate. It needs its own connection limits, and its lag alerts need an owner who responds. Its capacity must be planned for report peaks rather than app peaks. You also need a tested rebuild from base backup for when it breaks or falls too far behind.

Illustrative estimate (assumed numbers, not a benchmark): monthly cost = instance cost + (ops hours × hourly rate)

Assume a $400 instance and 6 ops hours at $100/hour: $400 + 6 × $100 = $1,000/month.

Explicit routing and documented staleness contracts reduce that ops time. Every call site that uses the replica declares the maximum staleness it accepts. When a user reports a stale number, the contract shows whether the system behaved as designed. You avoid chasing an intermittent bug that depends on lag at that moment.

Later, moving a report to a summary table or an analytics store is a matter of changing its target. The contract already tells you whether a pipeline delay is acceptable for that report.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to monolith read replica reporting, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.