List PostgreSQL Materialized Views with pg_matviews

List PostgreSQL Materialized Views with pg_matviews

A materialized view carries no built-in warning label for staleness. Nothing about the row itself changes when the underlying tables mutate underneath it — a sales dashboard or a cached report join keeps returning last week's numbers with the same confidence as this morning's, and a plain SELECT * FROM the view never flags the difference. pg_matviews is where that gap gets closed: a system view that lists every materialized view in the current database with its population state, index coverage, and defining query, one row per view.

Purpose and Overview

A materialized view is not the same object as a regular view. A plain view stores only a query; PostgreSQL re-runs that query every time the view is referenced. A materialized view, created with CREATE MATERIALIZED VIEW ... AS SELECT, actually executes the query and persists the result as a physical, table-like object — similar to CREATE TABLE AS, except PostgreSQL also remembers the defining query so the data can be refreshed later with REFRESH MATERIALIZED VIEW. Because the data is copied rather than recomputed on every read, access is typically far faster than querying the underlying tables directly — but the tradeoff is that the view's contents are frozen as of the last refresh, not the last write to the source tables.

pg_matviews is the inventory tool for that tradeoff. Each row covers one materialized view visible to the current role: schemaname and matviewname identify it, matviewowner resolves to the owning role, tablespace shows where it's stored (null when it sits in the database's default tablespace), hasindexes flags whether the view carries any index, ispopulated reports whether it currently holds data at all, and definition returns the reconstructed SELECT query PostgreSQL will re-run on the next refresh. No other single query surfaces all of that at once.

The two columns that matter most for day-to-day operations are ispopulated and hasindexes. A materialized view created with WITH NO DATA — or refreshed with WITH NO DATA — sits in an unscannable state until the next full refresh; querying it directly raises an error rather than returning stale-but-usable rows. ispopulated catches that state in a single scan of the database instead of testing each view by hand. hasindexes is the first signal for a different question: whether a view is even eligible for REFRESH MATERIALIZED VIEW ... CONCURRENTLY, the lock-avoiding refresh mode most production schedulers eventually want.

Sample Code

 1SELECT
 2    schemaname,
 3    matviewname,
 4    matviewowner,
 5    ispopulated,
 6    hasindexes,
 7    tablespace
 8FROM
 9    pg_matviews
10ORDER BY
11    schemaname,
12    matviewname;

Filtered variant, for views that are not currently populated:

 1SELECT
 2    schemaname,
 3    matviewname,
 4    matviewowner,
 5    hasindexes
 6FROM
 7    pg_matviews
 8WHERE
 9    NOT ispopulated
10ORDER BY
11    schemaname,
12    matviewname;

Notes: pg_matviews has been available since PostgreSQL 9.3, when materialized views were added to the server. tablespace is NULL for any view stored in the database's default tablespace rather than a dedicated one. hasindexes reports whether any index exists on the view — it does not confirm that a qualifying unique index exists for CONCURRENTLY, which has its own narrower requirement covered below.

Code Breakdown

The base query is a full scan of the catalog view; the filtered variant narrows it to the rows that need attention first.

Reading ispopulated for Refresh State

1ispopulated

This column is true if the materialized view is currently populated. A view created with WITH NO DATA is flagged as unscannable and cannot be queried until REFRESH MATERIALIZED VIEW runs against it — so ispopulated = false is not a warning sign by itself, but it is a hard stop for anything downstream that expects to SELECT from the view.

Filtering with NOT ispopulated

1WHERE NOT ispopulated

This finds every view left in the unscannable state, whether that happened at creation time with WITH NO DATA or from a later REFRESH MATERIALIZED VIEW ... WITH NO DATA that was never followed by a full refresh. Any application code or report that queries one of these views directly will fail with an error rather than return outdated rows — the filtered query catches that before a user does.

hasindexes as a Pre-Flight Signal, Not Proof

1hasindexes

hasindexes is true if the materialized view has, or recently had, any index at all. That is useful as a first pass, but it does not confirm the specific condition REFRESH MATERIALIZED VIEW CONCURRENTLY actually requires — at least one UNIQUE index built on plain column names, covering every row, with no expression and no WHERE clause. A view with hasindexes = true can still fail a CONCURRENTLY refresh if its only index doesn't meet that shape; confirming eligibility means checking the actual index definition, not just this boolean.

Key Materialized View Refresh Mechanics

WITH [NO] DATA at Creation

CREATE MATERIALIZED VIEW populates the view immediately by default, running the backing query at creation time. Specifying WITH NO DATA skips that step and leaves the view unscannable until the first REFRESH MATERIALIZED VIEW. This is a common pattern when the initial population would be expensive and needs to happen in a controlled maintenance window rather than inline with a deploy.

Blocking Refresh: Plain REFRESH MATERIALIZED VIEW

The default REFRESH MATERIALIZED VIEW name completely replaces the view's contents by re-running its backing query and discarding the old data. A refresh that affects a lot of rows tends to use fewer resources and finish faster this way, but it can block other connections trying to read from the view while it runs.

REFRESH MATERIALIZED VIEW CONCURRENTLY and the Unique Index Requirement

Adding CONCURRENTLY refreshes the view without locking out concurrent selects against it. The option is only allowed when the view already has at least one UNIQUE index that uses plain column names and covers all rows — no expression index, no partial index with a WHERE clause — and it can only be used once the view is already populated. Even with CONCURRENTLY, only one REFRESH can run against a given materialized view at a time. The example in PostgreSQL's own materialized-view documentation shows the shape a qualifying index takes: CREATE UNIQUE INDEX sales_summary_seller ON sales_summary (seller_no, invoice_date); — a plain, full-row unique index on the summary columns, exactly the kind CONCURRENTLY checks for.

The definition Column

pg_matviews.definition returns a reconstructed SELECT query — the same query PostgreSQL stores and re-runs on every refresh, the way a regular view's query is stored. Reading this column directly from the catalog confirms exactly what a given materialized view will recompute the next time it's refreshed, without needing to track down the original migration or DDL script that created it.

Practical Applications

Reading pg_matviews on a schedule turns materialized-view maintenance from a per-object memory exercise into a single repeatable query.

Catching Views Created but Never Populated

A deployment that runs CREATE MATERIALIZED VIEW ... WITH NO DATA to skip a slow initial build depends on a follow-up refresh step actually running. When that step gets skipped or fails silently in a migration pipeline, the view sits unscannable in production. The NOT ispopulated query catches this in one pass across the whole database, before a report or API call hits the view and surfaces the failure as a user-facing error.

Pre-Flight Check Before Adding CONCURRENTLY to a Refresh Job

Before rewriting a nightly refresh script to add CONCURRENTLY — usually to stop a long refresh from blocking dashboard reads — cross-reference the hasindexes rows against the actual index definitions to confirm a genuinely qualifying unique index exists on each target view. Skipping this check means the first concurrent refresh attempt fails at runtime instead of at review time.

Auditing Reporting Views Before a Stakeholder Notices

A summary view built for a sales dashboard — the kind of view PostgreSQL's own documentation demonstrates, aggregating an invoice table into a sales_summary view for graphing — has no self-reported staleness indicator. Listing every matviewname from pg_matviews and checking it against a job scheduler's run history confirms every reporting view in production actually has a corresponding refresh job attached to it, rather than relying on someone remembering to set one up.

Reviewing Foreign-Data-Backed Materialized Views After a Migration

Materialized views are also used to cache data pulled across a foreign data wrapper, where the local, indexed copy is dramatically faster than repeated remote access. After a restore or a server migration, checking tablespace and hasindexes for these views confirms the cached copy retained the indexing that made it fast in the first place — a foreign-data-backed view without its index is functionally no faster than querying the remote source directly.

Version Compatibility

Materialized views, CREATE MATERIALIZED VIEW, and pg_matviews were all introduced together in PostgreSQL 9.3. Before that release, PostgreSQL had no built-in materialized view support at all; teams approximated the behavior with CREATE TABLE AS snapshots refreshed manually, or with trigger-based change tracking, according to the PostgreSQL wiki's history of the feature.

REFRESH MATERIALIZED VIEW CONCURRENTLY is a later addition: PostgreSQL 9.3's REFRESH MATERIALIZED VIEW syntax had no CONCURRENTLY option at all, and every refresh was a blocking replace. PostgreSQL 9.4 added the CONCURRENTLY option along with its unique-index requirement, giving DBAs the lock-avoiding refresh path for the first time.

The privilege required to run REFRESH MATERIALIZED VIEW also changed more recently. On PostgreSQL 16 and earlier, only the owner of the materialized view could refresh it. PostgreSQL 17 replaced that with the MAINTAIN privilege — a role now needs MAINTAIN on the view, which an admin can grant without transferring ownership, making it possible to hand a scheduled job's service role refresh access without also giving it ownership of the object.

Best Practices

• Query ispopulated right after any deploy that creates matviews WITH NO DATA — an unscannable materialized view left in production surfaces as an application error, not a database error.
• Verify CONCURRENTLY eligibility with the actual index definition, not just hasindexes — hasindexes is true for any index; CONCURRENTLY needs a specific full-row, column-only unique index.
• Treat "one REFRESH at a time per view" as a scheduling constraint — even CONCURRENTLY refreshes serialize per materialized view, so don't schedule overlapping jobs against the same one.
• Grant MAINTAIN narrowly on PostgreSQL 17 and later — it lets a scheduled job's role refresh a view without owning it, tightening the privilege surface compared to the old owner-only model.
• Read the definition column before altering a source table — it shows the exact query PostgreSQL will re-run on the next refresh, so a planned column rename or type change can be checked against it first.
• Pair the audit query with your job scheduler's run history — a matview listed in pg_matviews with no corresponding scheduled refresh is a candidate for either a new job or removal.

References

• PostgreSQL Documentation — pg_matviews
• PostgreSQL Documentation — Materialized Views (Rule System chapter)
• PostgreSQL Documentation — REFRESH MATERIALIZED VIEW
• PostgreSQL Wiki — Materialized Views