---
source: https://iinventedlight.com/library/notes/postgres-materialized-views.md
title: "PostgreSQL materialized views"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "PostgreSQL License; prose restates cited source lines"
attribution: "Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 39.3. Materialized Views, PostgreSQL License; PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: REFRESH MATERIALIZED VIEW, PostgreSQL License."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-04
as_of: 2026-11-04
confidence: medium
untested: "No code was run for this note; only the fetched documentation pages were read."
category: coding-references
topic_area: "code-ai-eng: PostgreSQL"
tags: ['postgresql', 'sql', 'materialized-views', 'performance']
sources:
  - id: S1
    title: "PostgreSQL: Documentation: 18: 39.3. Materialized Views"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/rules-materializedviews.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
  - id: S2
    title: "PostgreSQL: Documentation: 18: REFRESH MATERIALIZED VIEW"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
---
# PostgreSQL materialized views

**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 ...

```sql
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).