← All notes

Answer: A materialized view stores a query's results in a table-like form, so reads are fast but data is only as fresh as the last REFRESH MATERIALIZED VIEW. Use `REFRESH ...

CREATE MATERIALIZED VIEW sales_summary AS
  SELECT seller_no, invoice_date, sum(invoice_amt) AS sales_amt
  FROM invoice GROUP BY seller_no, invoice_date;

CREATE UNIQUE INDEX ON sales_summary (seller_no, invoice_date);

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Refresh rules

• CONCURRENTLY needs at least one UNIQUE index using only column names, with no WHERE clause, and the view must already be populated.
• Only one refresh at a time runs against a given view.
• REFRESH MATERIALIZED VIEW is a PostgreSQL extension to SQL.

Claims

• id: C1 claim: Materialized views persist results in a table-like form. source: S1 evidence: "but persist the results in a table-like form."
• id: C2 claim: If periodic updates are tolerable, the performance benefit can be substantial. source: S1 evidence: "If you can tolerate periodic update of the remote data to the local database"
• id: C3 claim: CONCURRENTLY and WITH NO DATA may not be specified together. source: S2 evidence: "CONCURRENTLY and WITH NO DATA may not be specified together."
• id: C4 claim: Only one REFRESH at a time may run against a view. source: S2 evidence: "Even with this option only one REFRESH at a time may run against any one"
• id: C5 claim: REFRESH MATERIALIZED VIEW is a PostgreSQL extension. source: S2 evidence: "REFRESH MATERIALIZED VIEW is a PostgreSQL extension."

Sources

• [S1] PostgreSQL: Documentation: 18: 39.3. Materialized Views. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/rules-materializedviews.html . License: PostgreSQL License. Accessed 2026-11-04.
• [S2] PostgreSQL: Documentation: 18: REFRESH MATERIALIZED VIEW. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html . License: PostgreSQL License. Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

@misc{iil_note_e66q42pr,
  author = {{iinventedlight}},
  title = {{PostgreSQL materialized views}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-materialized-views/},
  note = {Permanent ID iil:note:e66q42pr. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-materialized-views.md.