Diagnosing a Slow Analytics Replica
Your company runs a Postgres OLTP database for the production
application. To avoid buying a warehouse, analysts have been pointed
at a read replica of that same Postgres database and write their
dashboard queries directly against it. At first this worked fine, but
as the orders table crossed 200 million rows, a dashboard query like:
SELECT region, DATE_TRUNC('month', order_date) AS month,
SUM(revenue), COUNT(*)
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY region, month;
went from 2 seconds to over 90 seconds, and it's now also slowing down the production replica enough that other read traffic is affected.
- Explain, mechanistically, why this specific query degrades so badly
on a row-oriented OLTP engine as the table grows, even with an
index on
order_date. - Propose an architecture that fixes this properly, and explain why it fixes the mechanism you identified in part 1, not just the symptom.
- A teammate suggests "just add more indexes on the replica" as a faster fix than standing up a new system. Evaluate that proposal.
1. Why this degrades on a row-oriented engine
An index on order_date helps narrow which rows qualify, but this
query still needs to read region, order_date, and revenue for
every one of the (large, and growing) number of qualifying rows to
compute the aggregation — and in a row-oriented store, reading any
column of a row means the engine pulls the entire row (every column,
including ones this query never references, like customer_id,
shipping_address, etc.) off disk, because rows are stored contiguously
rather than by column. As the table grows, the absolute number of
qualifying rows grows too (most dashboards ask for "this year" or
"this quarter," a growing window against a growing table), so the
wasted I/O on unused columns grows linearly with table size — the
query was never actually bottlenecked by the index lookup, it's
bottlenecked by scanning and aggregating a large, wide row set. This
is also why it's now affecting other replica traffic: it's saturating
shared I/O and buffer cache with data the query doesn't need, evicting
pages other, unrelated OLTP queries needed cached.
2. The right architecture
Stand up a proper OLAP system (a warehouse like Snowflake/BigQuery/
Redshift, or a lakehouse if there's a broader multi-engine need) and
replicate/pipeline the orders data into it via a CDC or batch
ETL/ELT process (covered in etl-vs-elt-and-pipeline-design,
orchestrated with something like airflow-and-workflow-orchestration),
then point the dashboards at the OLAP system instead of the OLTP
replica. This fixes the actual mechanism, not just performance
symptoms: a columnar warehouse stores region, order_date, and
revenue as separate column stripes, so this query reads only those
three columns' worth of data instead of every column of every
qualifying row, and it applies compression and vectorized execution on
top of that — the aggregate becomes proportional to the columns
actually used, not the row width. It also completely removes analytics
load from the OLTP system, fixing the "slowing down production" problem
structurally rather than by tuning around it, since the two workloads
no longer share the same engine or hardware at all.
3. Evaluating "just add more indexes"
This does not address the root cause and has real costs. More indexes can help point lookups and narrow filters, but this query's cost is dominated by wide-row I/O during a large aggregate scan, which no number of B-tree indexes fixes — you can't index your way out of needing to read whole rows in a row store for a broad aggregate. Worse, every additional index adds write overhead on the primary database (the replica has to apply the same index maintenance the primary does), which directly hurts the OLTP workload's write throughput and latency — the exact workload the production system exists to serve well. It also does nothing about queries getting wider or covering more history over time, since the fundamental row-oriented I/O cost per qualifying row doesn't change. The teammate's proposal treats this as a missing-index problem when it's actually a wrong-engine-for-the-workload problem — the fix in part 2 addresses the actual cause; adding indexes is a local patch that risks harming the system it's meant to protect.
Share this question