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.

Start this project - freeLive cloud workspace in your browser · lands on your portfolio
Data Engineerpostgresqlsqlmaterialized-view

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:

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 - free

What you'll practice

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

SQL Query Examples: Top-5 Revenue ReportData projectFix a SQL COUNT GROUP BY Double-Counting BugData projectHow to Build a Kimball Star SchemaData projectData roadmapStep by step to hiredData interview questionsSTAR answersAll Data projectsProjects hub

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
Start this project - free