ERP Reporting Architecture: Read Models That Save Your OLTP

The support ticket that taught me this lesson said: invoices take 30 seconds to save every morning around 8. The cause was nowhere near the invoice code. It was the executive dashboard — six widgets, each running a five-table aggregation over a year of transactions, refreshed every time a manager opened the page with their morning coffee. The same PostgreSQL instance that processed invoice postings was being flattened by GROUP BY queries.

This is the defining architecture problem of ERP reporting: transactional work and analytical work have opposite access patterns, and a database tuned for one is mediocre at the other. The fix is not a bigger server. It is read models — precomputed, query-shaped copies of your data that dashboards hit instead of the live tables. Here is the architecture I use on NestJS and PostgreSQL, from the first materialized view to knowing when you have outgrown them.

Why Reporting Kills Your OLTP Database

OLTP workloads — posting an invoice, issuing stock, approving a PO — touch a handful of rows each, demand millisecond latency, and run thousands of times a day. Analytical queries scan millions of rows, join half the schema, and aggregate over months. Running both on one database hurts in three specific ways:

• Cache eviction — one full-table scan for a yearly sales chart can flush the working set of hot rows (open invoices, active items) out of shared buffers, so every transaction afterwards pays the disk-read tax.
• Resource starvation — a dashboard aggregation pinning CPU and I/O for eight seconds delays the WAL flushes and index updates that transactional commits are waiting on.
• Lock and bloat interactions — long-running report transactions hold back vacuum on busy tables, dead tuples accumulate, and your once-fast point queries quietly degrade week over week.

The instinct is to add indexes for the reports. That makes the reads slightly faster while making every transactional write slower — each extra index is a write amplification cost paid on every INSERT. After a few rounds you have an OLTP schema wearing an OLAP costume, bad at both jobs.

The Read-Model Ladder: Four Stages of Separation

You do not need a data warehouse on day one. There is a ladder of separation, and each rung buys headroom at a cost. Climb only when measurements say you need the next rung:

Stage One Done Right: Materialized Views in PostgreSQL

For most custom ERPs serving tens to hundreds of users, stage one carries you for years — if you build it deliberately. A materialized view in PostgreSQL persists the result of a query as a physical table you can index; the docs show four-orders-of-magnitude speedups when an indexed materialized view replaces repeated computation. Here is the shape of a real one, an AR aging widget:

-- Read model for the AR aging dashboard widget
CREATE MATERIALIZED VIEW mv_ar_aging AS
SELECT
  c.id            AS customer_id,
  c.name          AS customer_name,
  SUM(i.open_amount) FILTER (WHERE age_days(i) <= 30)
                  AS bucket_current,
  SUM(i.open_amount) FILTER (WHERE age_days(i) BETWEEN 31 AND 60)
                  AS bucket_31_60,
  SUM(i.open_amount) FILTER (WHERE age_days(i) BETWEEN 61 AND 90)
                  AS bucket_61_90,
  SUM(i.open_amount) FILTER (WHERE age_days(i) > 90)
                  AS bucket_over_90,
  now()           AS refreshed_at
FROM ar_invoice i
JOIN customer c ON c.id = i.customer_id
WHERE i.status = 'OPEN'
GROUP BY c.id, c.name;

-- Unique index makes CONCURRENTLY possible
CREATE UNIQUE INDEX ON mv_ar_aging (customer_id);

-- Refresh WITHOUT blocking dashboard readers
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_ar_aging;

Two details in that snippet do the heavy lifting. The unique index is what allows REFRESH MATERIALIZED VIEW CONCURRENTLY, which rebuilds the view without taking the exclusive lock that would freeze every dashboard mid-refresh. And the refreshed_at column surfaces data freshness to the UI — more on why that matters below.

Designing the Read Models Themselves

The mistake I made first: creating one giant view per report page. Views should be shaped like widgets, not pages — one materialized view per question, each small, independently refreshable, and owned by a named consumer. When the sales summary breaks, you refresh or rebuild one view, not the world.

This is CQRS thinking applied at the database level, and Microsoft's architecture guidance says it plainly: read models serve queries and carry no business logic, while the write model guards consistency. Four rules keep ERP read models honest:

Freshness Is a Product Decision, Not a Technical One

Every read model is stale by definition — the only question is by how much, and whether users know. In ERP this is sensitive: a warehouse supervisor checking stock before promising a delivery needs live truth; a director reviewing monthly revenue trend does not care about the last ten minutes. So freshness budgets must come from users, widget by widget, not from engineering defaults.

My defaults, calibrated by years of finance teams telling me what they actually check: operational widgets (open approvals, today's receipts, low stock) come from live queries against indexed OLTP tables, kept deliberately tiny. Tactical widgets (aging, monthly sales, budget burn) refresh every 15 to 60 minutes. Strategic widgets (yearly trends, margin analysis) refresh nightly. Nobody has ever complained that a year-over-year trend chart was four hours old.

Retrofit Plan: From Slow Dashboard to Read Models

If your ERP dashboard is already hurting production, this is the sequence I follow — it has rescued more than one deployment without a rewrite:

1. Measure first: enable pg_stat_statements, rank queries by total execution time, and confirm dashboards are actually the offenders. Sometimes the villain is an unindexed foreign key instead.
2. Move the top three offenders into materialized views with unique indexes; point the API at them behind the same response shape so the frontend does not change.
3. Add refresh jobs with logging and a freshness column; surface the timestamp in the UI in the same release.
4. Set statement_timeout for the reporting database role, so no future ad-hoc query can hold the OLTP hostage for minutes again.
5. Negotiate freshness budgets per widget with the actual users — write them down; they are now requirements, not accidents.
6. Re-measure after two weeks. Only if refresh load itself is now the problem do you climb to a replica or an event-fed store.

The Takeaway

Reporting load is not an optimization problem inside your OLTP database — it is a different workload that deserves its own data structures. Read models give it those structures at a cost you control: a few summary tables, a refresh schedule, and a visible timestamp. Every rung up the ladder after that is a measured response to real load, not architecture astronautics.

The dashboard from that 30-second-invoice ticket runs on eleven materialized views today, refreshed every 30 minutes, on the same database instance. Invoices save in 200 milliseconds at 8 in the morning. Separation, not hardware, was the answer — it usually is.