Monthly Revenue in Postgres: Materialized View vs Trigger vs Live Query
A dashboard needs revenue per month from a table that will hold a billion rows. A live GROUP BY, a trigger-maintained summary table and a nightly materialized view all return the right number, and only one fits a weekly read, a busy insert path and day-old data.
Three ways to serve the same number
| Approach | Read cost | Write cost | Freshness |
|---|---|---|---|
Live GROUP BY on every read |
Scans the table (seconds or more) | None | Real time |
| Trigger updates a summary table on every insert | Instant | Extra work on every insert | Real time |
| Materialized view, refreshed nightly | Instant | None | Up to a day old |
A materialized view in Postgres
CREATE MATERIALIZED VIEW revenue_by_month AS
SELECT date_trunc('month', created_at)::date AS month,
sum(amount) AS total_amount
FROM orders
GROUP BY 1;
-- nightly
REFRESH MATERIALIZED VIEW revenue_by_month;
It stores the result like a table and recomputes it only on refresh. Add a
unique index and use REFRESH ... CONCURRENTLY so readers are not blocked
while it rebuilds.
How to decide
Start from the workload, not the technique:
- How often is it read? Weekly reads do not justify per-insert work.
- What is the hot path? If inserts are the hot path, a trigger taxes every one of them.
- How fresh must it be? If a day old is acceptable, real-time machinery is wasted cost.
The same reasoning picks between summary tables, dbt incremental models and streaming aggregates in a warehouse.
What you do in the project
You read three working implementations, choose the one that fits the stated
workload, apply it to Postgres, and check its monthly totals add up to the
whole orders table.
Do this project yourself. It's ready in a live cloud workspace in your browser - when you finish, it lands on your portfolio.
Start this project - freeWhat you'll practice
- Comparing read cost, write cost and freshness for an aggregate
- Creating and refreshing a Postgres materialized view
- Checking an aggregate's totals against its source table
FAQ
What is a materialized view in PostgreSQL?
A materialized view stores the result of a query as a table. Reads are fast, and the data is only updated when you run REFRESH MATERIALIZED VIEW.
Materialized view vs summary table with triggers - which is better?
A trigger-maintained table is always current but adds work to every write. A materialized view adds no write cost but is only as fresh as its last refresh. Choose based on how often it is read and how fresh it must be.
Can I refresh a materialized view without blocking reads?
Yes. Create a unique index on the view and use REFRESH MATERIALIZED VIEW CONCURRENTLY.
Keep learning
Do this project yourself. It's ready in a live cloud workspace in your browser - when you finish, it lands on your portfolio.
Start this project - free