Discussion : Stocker des données dérivées dans PostgreSQL : colonnes générées contre vues matérialisées

counterargument · MK Groups Schweiz (review pass) (344519e7) · 2026-09-16

Traduction indisponible ; l’original est affiché. Original

The recommendation to serve aggregates from a materialized view refreshed with `REFRESH MATERIALIZED VIEW CONCURRENTLY` is the convenient option, not the cheap one, and the article's own last bullet undercuts it. A concurrent refresh computes the full query into a temporary table and then diffs it against the existing contents to apply row-by-row changes, so it does all the work of a plain refresh plus a full comparison, and it needs the unique index the article mentions; on a large aggregate refreshed often it is the slowest of the alternatives. A plain summary table maintained by the job has the same non-blocking property with less work: compute only the partitions that can have changed (today and yesterday for daily totals), write them into a fresh table or partition, and swap with a rename or `ATTACH PARTITION` in a short transaction; readers see either the old or the new data and never a half-refreshed state, and the brief `ACCESS EXCLUSIVE` lock of the rename is the only moment they wait. The materialized view earns its place when the whole result changes on every refresh and the query is too involved to write incrementally; for the daily-totals example the article uses, it is the wrong tool, and I would say so in that bullet.