hesela.devMaterialized view
A materialized view stores the result of a query for later reads instead of recomputing that result for each read.
Stored result: PostgreSQL 18 retains both the query and its materialized rows. Reads use those rows, which may lag the underlying sources; this differs from an ordinary view evaluated on access. [1]
Refresh: In PostgreSQL 18, REFRESH MATERIALIZED VIEW with data replaces the stored result by running its defining query. WITH NO DATA instead leaves the view unscannable. CONCURRENTLY requires an already populated view and a UNIQUE index over plain columns that covers every row, without an expression index or WHERE predicate. It cannot be combined with WITH NO DATA. These are PostgreSQL rules, not promises for every engine. [2]
Recovery: A stored result is not automatically an independent recovery copy. If it must be rebuilt, identify the surviving source state and verify the reconstructed output against the required endpoint. If that rebuild blocks accepted service, include it in recovery timing. These are Hesela engineering inferences from source-dependent recovery planning. [3]
Relations
Limitations
- PostgreSQL 18 pg_dump -t selects a materialized-view definition, not its cached rows, and does not automatically include its dependencies. In ordinary PostgreSQL 18.0 logical dumps, when included, the data item for a populated materialized view is a REFRESH command. This does not describe physical backups or binary-upgrade mode. [5, 6]
- Preserving base tables and a query is not always enough to reproduce an older result. The documented sales-summary query uses CURRENT_DATE, which depends on transaction start. A later refresh can therefore change the selection even with unchanged base rows. [1, 7]
Citations
- PostgreSQL 18: Materialized Views, section 39.3
- PostgreSQL 18: REFRESH MATERIALIZED VIEW, population and concurrent-refresh requirements
- AWS Well-Architected REL13-BP01: source-dependent reconstruction, implementation guidance question 4
- PostgreSQL 18: pg_dump, -t selection and dependency limits
- PostgreSQL 18.0 pg_dump.c: refreshMatViewData and dumpTableSchema
- PostgreSQL 18: Date/Time Functions, section 9.9.5, transaction-relative current time
Further reading
Sources & caveats
- Refresh and population details describe PostgreSQL 18, not a universal SQL interface. Recovery recommendations are engineering inferences; no database recovery experiment or performance measurement was performed. [1] [2] [3]
- Historical-result preservation and evaluation-time dependencies are engineering inferences from the documented query and pinned logical-dump implementation, not a restore experiment. [1] [7] [6]