The monthly report took two seconds a year ago and now takes four minutes, sometimes timing out entirely. Nothing about the query changed - the data grew. Transactional schemas are optimised for reading and writing individual records, and aggregating millions of rows is a fundamentally different access pattern that gets worse in proportion to volume.
01Two workloads with opposite requirements
Transactional work touches few rows and needs low latency per operation. Analytical work scans large ranges and aggregates them. A schema and index strategy tuned for the first is close to worst-case for the second, and indexes cannot rescue a query that legitimately must read most of a table.
This is why the problem appears gradually and then suddenly. Below a threshold the aggregation fits in memory and completes acceptably; above it, the work spills to disk and the time increases sharply.
| Approach | Suits | Cost |
|---|---|---|
| Query the transactional tables | Small volumes, live accuracy | Degrades with growth |
| Indexes for the reporting query | Selective filters | Slows writes, limited ceiling |
| Materialised views | Repeated aggregations | Data is as fresh as the refresh |
| Summary tables updated incrementally | Predictable metrics | Extra write path to maintain |
| Read replica for reporting | Isolating load | Replication lag |
| Separate analytical store | Large volumes, complex analysis | Pipeline to build and operate |
02Stop recomputing history
Most reports recalculate everything from the beginning of time on every run, despite the great majority of that data being immutable. Last year's monthly totals will not change, yet they are recomputed whenever someone opens the dashboard.
Pre-aggregate into summary tables at the granularity reports actually use - daily or monthly totals per dimension. The report then reads a few thousand pre-computed rows rather than scanning millions, which changes minutes into milliseconds.
03Agree how fresh reports need to be
Users often assume reports are live and are rarely inconvenienced when they are not. Establishing that a dashboard reflects data as of an hour ago, displayed clearly on the page, opens up caching and pre-aggregation strategies that live queries preclude.
Where genuinely current figures are required, restrict that to the small number of metrics that need it rather than treating the entire report as real-time. The expensive requirement is usually attached to far fewer numbers than the initial specification suggests.
| Pattern | Problem |
|---|---|
| SELECT * then aggregate in application code | Transfers and holds enormous result sets |
| No date bound on the query | Scans all history every run |
| Correlated subquery per row | Executes once per result row |
| Joining several large tables unfiltered | Intermediate results exceed memory |
| DISTINCT over a wide result | Expensive sorting and deduplication |
| Counting exact totals for pagination | Full scan just for a page count |
04Keep reporting load off the primary
A heavy report competes with transactional traffic for the same resources, so the checkout gets slower whenever somebody opens the analytics page. Routing reporting queries to a read replica isolates that, and it is usually a configuration change rather than an application rewrite.
Set a statement timeout on the reporting connection. A single unbounded query should not be able to consume resources indefinitely, and a clear timeout error is better feedback than a page that hangs until the browser gives up.
05Know when to move to a separate store
Beyond a certain volume and complexity, a columnar analytical database is the correct answer. It scans and aggregates far more efficiently by storing data column-wise and compressing aggressively, and the difference is orders of magnitude rather than percentages.
That step introduces a pipeline, a synchronisation delay and another system to operate, so it should follow exhausting the simpler options. Most products get years of adequate performance from summary tables and a read replica, and reach for a warehouse when analysis itself becomes a core part of the product.
Topics
Priya Iyer
Staff Engineer · SyncTrix
Writes about the engineering decisions behind production systems - architecture, delivery and the trade-offs that only show up at scale.
Building something like this?
SyncTrix engineers AI, SaaS, platform and cloud systems for enterprises and high-growth teams. Tell us what you're shipping and we'll scope it with you.
Talk to an engineer