Skip to content
hgryoo · Docs

PostgreSQL Materialized Views — Storage and REFRESH (incl. CONCURRENTLY)

Contents:

Theoretical Background

Section titled “Theoretical Background”

A view is a named query. When the SQL engine sees the view in a FROM clause it substitutes the view’s defining query — the view is virtual, recomputed on every reference. A materialized view trades that freshness for speed: the query result is computed once and stored as a physical table, so subsequent reads scan precomputed rows instead of re-running a possibly expensive join-and-aggregate.

Database System Concepts (Silberschatz, 7th ed., §4.2.3 “Materialized Views” and §16.5) frames the materialized view as a classic space-for-time and freshness-for-time trade. The textbook names the three maintenance regimes a system must choose among:

  1. Eager (immediate) maintenance — the materialized view is updated inside the same transaction that modifies a base table, so it is never stale. Cost: every base-table write pays a maintenance tax, and the maintenance logic must be derivable from the view definition.

  2. Lazy (deferred / on-demand) maintenance — the view is refreshed only when explicitly asked, or when first read after the base data changed. Between refreshes the view is stale: it reflects a past snapshot of the base tables.

  3. Periodic / snapshot maintenance — the view is recomputed on a schedule (nightly, hourly). A special case of lazy maintenance where the trigger is a clock rather than a read.

Orthogonal to when is how much to recompute:

  • Full recomputation — throw away the stored result and re-run the defining query from scratch. Always correct, oblivious to the view definition’s algebra, but O(result size) every time.

  • Incremental view maintenance (IVM) — given the delta applied to base tables, compute the corresponding delta to the view using the relational algebra of the view definition (the counting algorithm of Gupta–Mumick–Subrahmanian 1993, or differential rules per operator). Cheap when changes are small, but requires the engine to understand and differentiate the view’s algebra, and to track multiplicities.

PostgreSQL deliberately sits in the lazy + full-recomputation corner of this design space, with one important refinement. A PostgreSQL materialized view is never automatically maintained: it goes stale the instant a base table changes, and is only ever brought current by an explicit REFRESH MATERIALIZED VIEW. That refresh always recomputes the entire defining query (PostgreSQL has no built-in IVM). The refinement is that the application of the recomputed result to the stored relation has two strategies — a wholesale physical file swap, or a row-level diff-and-merge that lets concurrent readers keep scanning the old contents until the new contents are atomically committed. That second strategy, REFRESH ... CONCURRENTLY, is the most interesting piece of machinery in matview.c and the focus of much of this document.

A fourth concept the textbook raises is query rewriting using materialized views: an optimizer that recognizes a query whose answer is contained in a materialized view can transparently substitute the view. PostgreSQL does not do this — a matview is only used when named explicitly. The matview is a stored relation with a remembered query, not an optimizer hint.

The state-tracking idea that ties it together: a stored relation that may or may not currently hold a valid result needs a populated bit. A freshly created (WITH NO DATA) matview, or one truncated by REFRESH ... WITH NO DATA, holds no valid result and must not be silently scanned as empty — that would return wrong answers. PostgreSQL records this in pg_class.relispopulated and refuses to scan an unpopulated matview.

Common DBMS Design

Section titled “Common DBMS Design”

Materialized-view implementations across systems converge on a small set of engineering conventions. Naming them here makes PostgreSQL’s specific choices read as one set of selections within a shared playbook.

Storage as an ordinary table

Section titled “Storage as an ordinary table”

A materialized view’s contents must live somewhere scannable. The universal choice is to back the matview with the same on-disk structure the system uses for ordinary tables — a heap, a clustered B-tree, a column segment file — so that all the existing scan, index, statistics, and vacuum machinery applies unchanged. The matview is “a table that remembers the query that filled it.” PostgreSQL backs a matview with a plain heap (relkind = RELKIND_MATVIEW, but a real relfilenode and the table access method of the user’s choice), so heap scans, btree indexes, ANALYZE, and autovacuum all just work.

The defining query is stored, not just the rows

Section titled “The defining query is stored, not just the rows”

To refresh, the system must remember how the rows were produced. Systems store the parsed/normalized defining query in the catalog. PostgreSQL reuses its existing rule infrastructure: a matview carries a single SELECT INSTEAD rewrite rule whose action list is the defining Query tree — the very same representation a regular VIEW uses. Refresh pulls that Query out of the relcache rule and re-plans it.

Atomic swap-in of recomputed contents

Section titled “Atomic swap-in of recomputed contents”

A full recompute produces a complete new result set. Exposing it must be atomic: readers should see either the entire old contents or the entire new contents, never a half-loaded relation. Two standard techniques:

  • Pointer/file swap — build the new contents in a side relation, then flip a single catalog pointer (relfilenode, segment manifest) so the matview suddenly refers to the new files. O(1) cutover, but it requires an exclusive lock because the old file is yanked out from under any in-flight scan.

  • Set-based DELETE/INSERT diff — compute which rows changed and apply only those changes as ordinary DML inside a transaction, so MVCC gives concurrent readers a consistent snapshot of the old contents until commit. Slower (it touches rows, runs joins, maintains indexes) but needs only a weaker lock that permits concurrent reads.

PostgreSQL implements both: the file swap is the default (refresh_by_heap_swap), and the diff is REFRESH ... CONCURRENTLY (refresh_by_match_merge).

A “freshness / valid” flag

Section titled “A “freshness / valid” flag”

Because a matview can exist without valid contents, systems carry a flag distinguishing “populated with a valid result” from “defined but empty/ stale-beyond-use.” PostgreSQL uses pg_class.relispopulated; scanning an unpopulated matview is an error, not an empty result.

flowchart TD
  A["CREATE MATERIALIZED VIEW foo AS SELECT ..."] --> B{"WITH DATA<br/>(default) or<br/>WITH NO DATA?"}
  B -->|WITH NO DATA| C["relation created<br/>relispopulated = false<br/>scan raises error"]
  B -->|WITH DATA| D["create relation (skipData),<br/>then REFRESH path<br/>relispopulated = true"]
  C -->|"REFRESH MATERIALIZED VIEW foo"| E["full recompute"]
  D --> F["scannable"]
  E --> G{"CONCURRENTLY?"}
  G -->|no| H["refresh_by_heap_swap<br/>AccessExclusiveLock<br/>swap relfilenode"]
  G -->|yes| I["refresh_by_match_merge<br/>ExclusiveLock<br/>diff + DELETE/INSERT"]
  H --> F
  I --> F

PostgreSQL’s Approach

Section titled “PostgreSQL’s Approach”

PostgreSQL’s materialized views are implemented almost entirely in src/backend/commands/matview.c, with the create-side scaffolding in createas.c and the physical heap-swap primitive shared with CLUSTER / VACUUM FULL in cluster.c. The design has four moving parts:

  1. Storage — a RELKIND_MATVIEW relation with a real heap and a SELECT INSTEAD rule holding the defining Query.
  2. Create — always WITH NO DATA at relation-creation time, then the REFRESH path is reused to populate.
  3. Refresh — recompute into a transient heap via a custom DestReceiver, then either heap-swap or diff-merge.
  4. State — pg_class.relispopulated, flipped by SetMatViewPopulatedState, gating scannability.

Storage: a heap plus a rule

Section titled “Storage: a heap plus a rule”

Structurally a matview is the union of a table and a view. It has a heap (so it can be scanned and indexed) and it has the same single-action SELECT INSTEAD rewrite rule a VIEW has (so the engine remembers the defining query). RefreshMatViewByOid reaches into the relcache and pulls the Query straight out of that rule — note the strict validation that the rule is exactly one SELECT INSTEAD action:

// RefreshMatViewByOid — src/backend/commands/matview.c
if (matviewRel->rd_rel->relhasrules == false ||
    matviewRel->rd_rules->numLocks < 1)
    elog(ERROR, "materialized view \"%s\" is missing rewrite information", ...);
if (matviewRel->rd_rules->numLocks > 1)
    elog(ERROR, "materialized view \"%s\" has too many rules", ...);


rule = matviewRel->rd_rules->rules[0];
if (rule->event != CMD_SELECT || !(rule->isInstead))
    elog(ERROR, "the rule for materialized view \"%s\" is not a SELECT INSTEAD OF rule", ...);


actions = rule->actions;
if (list_length(actions) != 1)
    elog(ERROR, "the rule for materialized view \"%s\" is not a single action", ...);
/* The stored query was rewritten at MV definition time, not planner-scribbled. */
dataQuery = linitial_node(Query, actions);

The matview’s heap is a first-class relation: it can carry indexes (including the unique index CONCURRENTLY needs), reloptions, a table access method (relam), a tablespace, and TOAST storage. The matview’s own relkind is RELKIND_MATVIEW, but everything below the catalog is heap mechanics shared with ordinary tables — see postgres-heap-am.md.

Create always defers data to REFRESH

Section titled “Create always defers data to REFRESH”

CREATE MATERIALIZED VIEW shares the CREATE TABLE AS executor in createas.c. The key decision: a matview is always created WITH NO DATA at relation-creation time, and the actual population is delegated to the refresh path. This avoids running the planner/executor before all dependencies of the new relation are wired up:

// ExecCreateTableAs / create path — src/backend/commands/createas.c
is_matview = (into->viewQuery != NULL);
...
/* For materialized views, always skip data during table creation,
 * and use REFRESH instead (see below). */
if (is_matview)
{
    do_refresh = !into->skipData;   /* WITH DATA -> refresh after create */
    into->skipData = true;
}


if (into->skipData)
{
    address = create_ctas_nodata(query->targetList, into);
    /* For materialized views, reuse the REFRESH logic, which locks down
     * security-restricted operations and restricts the search_path. */
    if (do_refresh)
        RefreshMatViewByOid(address.objectId, true /* is_create */, false,
                            false, pstate->p_sourcetext, qc);
}

So CREATE MATERIALIZED VIEW ... WITH DATA is mechanically create an empty matview, then REFRESH it. The is_create flag threads through RefreshMatViewByOid mostly to pick the right completion tag (CMDTAG_SELECT for create, CMDTAG_REFRESH_MATERIALIZED_VIEW for refresh) and to skip the CONCURRENTLY path (a brand-new matview cannot be refreshed concurrently — there is nothing to diff against).

Refresh: recompute into a transient heap

Section titled “Refresh: recompute into a transient heap”

Regardless of strategy, the first half of every refresh is identical: build a brand-new heap and fill it by running the stored query, sending result tuples to a custom DestReceiver that bulk-inserts them frozen. ExecRefreshMatView is the SQL entry point; it chooses the lock strength up front based on CONCURRENTLY:

// ExecRefreshMatView — src/backend/commands/matview.c
/* Determine strength of lock needed. */
lockmode = stmt->concurrent ? ExclusiveLock : AccessExclusiveLock;


matviewOid = RangeVarGetRelidExtended(stmt->relation, lockmode, 0,
                                      RangeVarCallbackMaintainsTable, NULL);


return RefreshMatViewByOid(matviewOid, false, stmt->skipData,
                           stmt->concurrent, queryString, qc);

The lock choice is the whole point of CONCURRENTLY: the default takes AccessExclusiveLock (blocks readers), while CONCURRENTLY takes only ExclusiveLock (blocks writers and other refreshes, but allows AccessShareLock readers — ordinary SELECTs — to keep scanning the old contents).

RefreshMatViewByOid then validates options, tentatively flips the populated bit, builds the new heap with make_new_heap (the same primitive CLUSTER uses), runs the query into it, and dispatches to one of the two apply strategies:

// RefreshMatViewByOid — src/backend/commands/matview.c
/* Tentatively mark the matview as populated or not (rolls back on failure). */
SetMatViewPopulatedState(matviewRel, !skipData);


/* Concurrent refresh builds new data in temp tablespace, and does diff. */
if (concurrent) {
    tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP, false);
    relpersistence = RELPERSISTENCE_TEMP;
} else {
    tableSpace = matviewRel->rd_rel->reltablespace;
    relpersistence = matviewRel->rd_rel->relpersistence;
}


OIDNewHeap = make_new_heap(matviewOid, tableSpace, matviewRel->rd_rel->relam,
                           relpersistence, ExclusiveLock);


if (!skipData) {
    DestReceiver *dest = CreateTransientRelDestReceiver(OIDNewHeap);
    processed = refresh_matview_datafill(dest, dataQuery, queryString, is_create);
}


if (concurrent) {
    refresh_by_match_merge(matviewOid, OIDNewHeap, relowner, save_sec_context);
} else {
    refresh_by_heap_swap(matviewOid, OIDNewHeap, relpersistence);
    pgstat_count_truncate(matviewRel);
    if (!skipData)
        pgstat_count_heap_insert(matviewRel, processed);
}

Note the asymmetry: the non-concurrent path explicitly tells the cumulative-stats system “I truncated and re-inserted,” because the heap swap is invisible to the per-row stats counters; the concurrent path’s DELETE/INSERT DML gets counted by lower-level code automatically.

The data-fill itself is an ordinary query execution redirected to the transient relation — re-plan the stored Query, push a snapshot with a bumped command id, and run the plan into the DestReceiver:

// refresh_matview_datafill — src/backend/commands/matview.c
copied_query = copyObject(query);
AcquireRewriteLocks(copied_query, true, false);
rewritten = QueryRewrite(copied_query);
query = (Query *) linitial(rewritten);


plan = pg_plan_query(query, queryString, CURSOR_OPT_PARALLEL_OK, NULL);


PushCopiedSnapshot(GetActiveSnapshot());
UpdateActiveSnapshotCommandId();
queryDesc = CreateQueryDesc(plan, queryString, GetActiveSnapshot(),
                            InvalidSnapshot, dest, NULL, NULL, 0);
ExecutorStart(queryDesc, 0);
ExecutorRun(queryDesc, ForwardScanDirection, 0);
processed = queryDesc->estate->es_processed;

The transient-relation DestReceiver

Section titled “The transient-relation DestReceiver”

Tuples produced by the query do not flow to the client; they are redirected into the new heap by DR_transientrel, a DestReceiver whose receiveSlot callback inserts each tuple. Two performance options make the load fast and crash-cheap: TABLE_INSERT_SKIP_FSM (don’t bother updating the free-space map for a relation we’re bulk-loading) and TABLE_INSERT_FROZEN (insert rows pre-frozen, since the new heap is private to this transaction and will be swapped/committed atomically):

// transientrel_startup — src/backend/commands/matview.c
myState->transientrel = transientrel;
myState->output_cid = GetCurrentCommandId(true);
myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN;
myState->bistate = GetBulkInsertState();
// transientrel_receive — src/backend/commands/matview.c
table_tuple_insert(myState->transientrel, slot, myState->output_cid,
                   myState->ti_options, myState->bistate);
/* We know this is a newly created relation, so there are no indexes */
return true;

The “no indexes” comment is the rationale for the comment at the top of RefreshMatViewByOid: indexes are rebuilt afterward (via the REINDEX folded into finish_heap_swap), because bulk-loading a heap and then building the index once is cheaper than maintaining the index per-row. TABLE_INSERT_FROZEN is safe precisely because CheckTableNotInUse guarantees no open scan can see these rows before the swap commits.

State: relispopulated and scannability

Section titled “State: relispopulated and scannability”

The populated bit lives in pg_class.relispopulated. SetMatViewPopulatedState updates that single column and — crucially — the catalog update sends a shared-invalidation message so every backend (including this one) rebuilds its relcache entry and sees the new state:

// SetMatViewPopulatedState — src/backend/commands/matview.c
Assert(relation->rd_rel->relkind == RELKIND_MATVIEW);
pgrel = table_open(RelationRelationId, RowExclusiveLock);
tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(RelationGetRelid(relation)));
((Form_pg_class) GETSTRUCT(tuple))->relispopulated = newstate;
CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);
table_close(pgrel, RowExclusiveLock);
/* Advance command counter so the updated pg_class row is locally visible. */
CommandCounterIncrement();

The flag is read through two relcache macros — today they are the same field, but the header notes the names are kept distinct so populated-vs- scannable could diverge later:

// RelationIsScannable / RelationIsPopulated — src/include/utils/rel.h
#define RelationIsScannable(relation) ((relation)->rd_rel->relispopulated)
#define RelationIsPopulated(relation) ((relation)->rd_rel->relispopulated)

The scannability check is enforced in the executor when a scan node opens its relation. Scanning an unpopulated matview is a hard error, not an empty result — except when the query won’t actually run (EXPLAIN-only, or a WITH NO DATA create that is just defining shape):

// ExecOpenScanRelation — src/backend/executor/execUtils.c
if ((eflags & (EXEC_FLAG_EXPLAIN_ONLY | EXEC_FLAG_WITH_NO_DATA)) == 0 &&
    !RelationIsScannable(rel))
    ereport(ERROR,
            (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
             errmsg("materialized view \"%s\" has not been populated",
                    RelationGetRelationName(rel)),
             errhint("Use the REFRESH MATERIALIZED VIEW command.")));

This is why CONCURRENTLY refuses to run against an unpopulated matview: there is no valid old contents to diff against. RefreshMatViewByOid rejects it explicitly:

// RefreshMatViewByOid — src/backend/commands/matview.c
if (concurrent && !RelationIsPopulated(matviewRel))
    ereport(ERROR,
            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
             errmsg("CONCURRENTLY cannot be used when the materialized view is not populated")));
if (concurrent && skipData)
    ereport(ERROR,
            (errcode(ERRCODE_SYNTAX_ERROR),
             errmsg("%s and %s options cannot be used together",
                    "CONCURRENTLY", "WITH NO DATA")));

Source Walkthrough

Section titled “Source Walkthrough”

This section walks the two refresh strategies symbol-by-symbol, then the state and helper machinery. All symbols are in matview.c unless noted.

Entry points and dispatch

Section titled “Entry points and dispatch”

ExecRefreshMatView is the utility-command handler for REFRESH MATERIALIZED VIEW. It resolves the relation under the right lock (ExclusiveLock for CONCURRENTLY, else AccessExclusiveLock) and calls RefreshMatViewByOid. The same RefreshMatViewByOid is invoked from createas.c with is_create = true to populate a freshly created WITH DATA matview. RefreshMatViewByOid does the heavy lifting: validate relkind/options, extract dataQuery from the rule, CheckTableNotInUse, SetMatViewPopulatedState, make_new_heap, refresh_matview_datafill, then dispatch to refresh_by_heap_swap or refresh_by_match_merge.

Non-concurrent: full heap swap

Section titled “Non-concurrent: full heap swap”

refresh_by_heap_swap is a one-liner wrapper over the shared finish_heap_swap primitive in cluster.c — the exact mechanism CLUSTER and VACUUM FULL use to atomically replace a relation’s storage:

// refresh_by_heap_swap — src/backend/commands/matview.c
static void
refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersistence)
{
    finish_heap_swap(matviewOid, OIDNewHeap, false, false, true, true,
                     RecentXmin, ReadNextMultiXactId(), relpersistence);
}

finish_heap_swap swaps the relfilenode (and TOAST relation, and toast index) of the matview with those of the transient heap inside the catalog, then rebuilds the matview’s indexes (REINDEX) over the new files and drops the now-orphaned old files. Because the swap is a catalog pointer change, it is O(1) regardless of result size — but it yanks the old heap file out from under any scanner, which is why the default path holds AccessExclusiveLock. The matview’s OID is preserved across the swap, so GRANTs, dependencies, and references survive; only the underlying relfilenode changes. See postgres-heap-am.md for the storage-manager details of relfilenode lifecycle.

Concurrent: match-merge diff

Section titled “Concurrent: match-merge diff”

refresh_by_match_merge is the heart of REFRESH ... CONCURRENTLY. It runs entirely through SPI (server-side SQL), under cover of the ExclusiveLock already held on the matview, in a sequence of steps:

Step 1 — ANALYZE the new data. The freshly filled transient table has no statistics; the planner needs them to choose a sane join for the diff:

// refresh_by_match_merge — src/backend/commands/matview.c
appendStringInfo(&querybuf, "ANALYZE %s", tempname);
if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
    elog(ERROR, "SPI_exec failed: %s", querybuf.data);

Step 2 — reject duplicate non-NULL rows. The diff relies on a FULL JOIN that cannot distinguish two identical rows. The check uses the row-comparison operator OPERATOR(pg_catalog.*=) and the ctid system column to find any two distinct physical rows that are wholly equal and non-NULL:

// refresh_by_match_merge — duplicate check, src/backend/commands/matview.c
appendStringInfo(&querybuf,
    "SELECT newdata.*::%s FROM %s newdata "
    "WHERE newdata.* IS NOT NULL AND EXISTS "
    "(SELECT 1 FROM %s newdata2 WHERE newdata2.* IS NOT NULL "
    "AND newdata2.* OPERATOR(pg_catalog.*=) newdata.* "
    "AND newdata2.ctid OPERATOR(pg_catalog.<>) newdata.ctid)",
    tempname, tempname, tempname);
if (SPI_execute(querybuf.data, false, 1) != SPI_OK_SELECT) ...
if (SPI_processed > 0)
    ereport(ERROR,
            (errcode(ERRCODE_CARDINALITY_VIOLATION),
             errmsg("new data for materialized view \"%s\" contains duplicate rows without any null columns", ...),
             errdetail("Row: %s", ...)));

The tablename.*::tablerowtype cast is a deliberate hack (noted in the source) to keep .* from being expanded into separate columns — it forces the row to stay a single composite value so it can be compared whole.

Step 3 — build the diff table. A temp table holding (tid tid, newdata <temprowtype>). The composite column is added in a second step, after switching security context, because temp tables cannot be created inside a SECURITY_RESTRICTED_OPERATION context:

// refresh_by_match_merge — diff table creation, src/backend/commands/matview.c
SetUserIdAndSecContext(relowner, save_sec_context | SECURITY_LOCAL_USERID_CHANGE);
appendStringInfo(&querybuf, "CREATE TEMP TABLE %s (tid pg_catalog.tid)", diffname);
SPI_exec(querybuf.data, 0);
SetUserIdAndSecContext(relowner, save_sec_context | SECURITY_RESTRICTED_OPERATION);
appendStringInfo(&querybuf, "ALTER TABLE %s ADD COLUMN newdata %s", diffname, tempname);
SPI_exec(querybuf.data, 0);

Step 4 — populate the diff via a keyed FULL JOIN. The join condition is built from the columns of every usable unique index on the matview. For each index column the code looks up the equality operator from the column’s opclass and emits an equality qual. The closing condition keeps only rows that exist on exactly one side (a genuine change):

// refresh_by_match_merge — diff INSERT skeleton, src/backend/commands/matview.c
appendStringInfo(&querybuf,
    "INSERT INTO %s "
    "SELECT mv.ctid AS tid, newdata.*::%s AS newdata "
    "FROM %s mv FULL JOIN %s newdata ON (",
    diffname, tempname, matviewname, tempname);
/* ... per unique-index-column equality quals appended here ... */
appendStringInfoString(&querybuf,
    " AND newdata.* OPERATOR(pg_catalog.*=) mv.*) "
    "WHERE newdata.* IS NULL OR mv.* IS NULL "
    "ORDER BY tid");

The per-column equality operator is resolved through the opclass / opfamily catalogs — this is what makes the join use the unique index’s own notion of equality rather than a generic one:

// refresh_by_match_merge — equality operator lookup, src/backend/commands/matview.c
cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass));
cla_tup = (Form_pg_opclass) GETSTRUCT(cla_ht);
opfamily = cla_tup->opcfamily;
opcintype = cla_tup->opcintype;
op = get_opfamily_member_for_cmptype(opfamily, opcintype, opcintype, COMPARE_EQ);
if (!OidIsValid(op))
    elog(ERROR, "missing equality operator for (%u,%u) in opfamily %u", ...);

Step 5 — apply: DELETE then INSERT. Maintenance is opened so DML against the matview is permitted, then deletes (rows present in old but not new) precede inserts (rows in new but not old):

// refresh_by_match_merge — apply, src/backend/commands/matview.c
OpenMatViewIncrementalMaintenance();
/* Deletes must come before inserts; do them first. */
appendStringInfo(&querybuf,
    "DELETE FROM %s mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
    "(SELECT diff.tid FROM %s diff WHERE diff.tid IS NOT NULL "
    "AND diff.newdata IS NULL)", matviewname, diffname);
SPI_exec(querybuf.data, 0);
/* Inserts go last. */
appendStringInfo(&querybuf,
    "INSERT INTO %s SELECT (diff.newdata).* FROM %s diff WHERE tid IS NULL",
    matviewname, diffname);
SPI_exec(querybuf.data, 0);
CloseMatViewIncrementalMaintenance();

Because the DELETE/INSERT run as ordinary MVCC DML inside the refreshing transaction, concurrent SELECTs holding AccessShareLock see the old contents (their snapshot predates the commit) until the refresh commits, at which point they see the new contents — atomically. This is the entire payoff of CONCURRENTLY.

The usable-unique-index gate

Section titled “The usable-unique-index gate”

Both the up-front validation in RefreshMatViewByOid and the join-builder in refresh_by_match_merge call is_usable_unique_index. A usable index must be unique, immediate, valid, non-partial, and defined only over plain user columns (no expressions, no system columns):

// is_usable_unique_index — src/backend/commands/matview.c
if (indexStruct->indisunique &&
    indexStruct->indimmediate &&
    indexStruct->indisvalid &&
    RelationGetIndexPredicate(indexRel) == NIL &&
    indexStruct->indnatts > 0)
{
    int numatts = indexStruct->indnatts, i;
    for (i = 0; i < numatts; i++) {
        int attnum = indexStruct->indkey.values[i];
        if (attnum <= 0)         /* reject system columns / expressions */
            return false;
    }
    return true;
}
return false;

If no such index exists, CONCURRENTLY fails early with a hint to create one. The unique index is what gives the diff its “match” key — without it the FULL JOIN has no row identity to merge on.

Maintenance-depth guard

Section titled “Maintenance-depth guard”

OpenMatViewIncrementalMaintenance / CloseMatViewIncrementalMaintenance bump a static counter; MatViewIncrementalMaintenanceIsEnabled reports whether the backend is inside matview maintenance. This gates whether direct DML against a matview is allowed — normally forbidden, but permitted while the refresh machinery is running its own DELETE/INSERT:

// matview maintenance depth — src/backend/commands/matview.c
static int matview_maintenance_depth = 0;
bool MatViewIncrementalMaintenanceIsEnabled(void) { return matview_maintenance_depth > 0; }
static void OpenMatViewIncrementalMaintenance(void)  { matview_maintenance_depth++; }
static void CloseMatViewIncrementalMaintenance(void) { matview_maintenance_depth--; Assert(matview_maintenance_depth >= 0); }

The PG_TRY/PG_CATCH around refresh_by_match_merge in RefreshMatViewByOid exists to restore the saved depth on error, so a failed concurrent refresh does not leave the backend stuck “in maintenance.”

End-to-end flow

Section titled “End-to-end flow”
flowchart TD
  A["REFRESH MATERIALIZED VIEW [CONCURRENTLY] foo"] --> B["ExecRefreshMatView<br/>pick lock: AEL or EL"]
  B --> C["RefreshMatViewByOid"]
  C --> D["validate relkind + rule<br/>extract dataQuery"]
  D --> E["CheckTableNotInUse<br/>SetMatViewPopulatedState"]
  E --> F["make_new_heap (transient)"]
  F --> G["refresh_matview_datafill<br/>DR_transientrel: frozen bulk insert"]
  G --> H{"concurrent?"}
  H -->|no| I["refresh_by_heap_swap"]
  I --> J["finish_heap_swap<br/>swap relfilenode + REINDEX"]
  H -->|yes| K["refresh_by_match_merge"]
  K --> L["ANALYZE temp;<br/>dup-row check"]
  L --> M["build diff temp table<br/>FULL JOIN on unique-index keys"]
  M --> N["DELETE removed rows<br/>INSERT added rows"]
  N --> O["DROP temp tables"]
  J --> P["commit: new contents visible"]
  O --> P

Position hints (as of 2026-06-05, REL_18 273fe94)

Section titled “Position hints (as of 2026-06-05, REL_18 273fe94)”
SymbolFileLine
DR_transientrel (struct)src/backend/commands/matview.c45
matview_maintenance_depthsrc/backend/commands/matview.c56
SetMatViewPopulatedStatesrc/backend/commands/matview.c78
ExecRefreshMatViewsrc/backend/commands/matview.c120
RefreshMatViewByOidsrc/backend/commands/matview.c164
concurrent/skipData option checkssrc/backend/commands/matview.c205
rule extraction (dataQuery)src/backend/commands/matview.c221
make_new_heap callsrc/backend/commands/matview.c319
dispatch heap-swap vs match-mergesrc/backend/commands/matview.c335
refresh_matview_datafillsrc/backend/commands/matview.c404
CreateTransientRelDestReceiversrc/backend/commands/matview.c464
transientrel_startupsrc/backend/commands/matview.c482
transientrel_receivesrc/backend/commands/matview.c508
make_temptable_name_nsrc/backend/commands/matview.c570
refresh_by_match_mergesrc/backend/commands/matview.c613
duplicate-row check SQLsrc/backend/commands/matview.c660
diff INSERT skeletonsrc/backend/commands/matview.c715
equality-operator lookupsrc/backend/commands/matview.c772
DELETE / INSERT applysrc/backend/commands/matview.c863
refresh_by_heap_swapsrc/backend/commands/matview.c904
is_usable_unique_indexsrc/backend/commands/matview.c914
MatViewIncrementalMaintenanceIsEnabledsrc/backend/commands/matview.c963
Open/CloseMatViewIncrementalMaintenancesrc/backend/commands/matview.c969
matview create defers datasrc/backend/commands/createas.c274
make_new_heap (definition)src/backend/commands/cluster.c705
RelationIsScannable / RelationIsPopulatedsrc/include/utils/rel.h689
scannability errorsrc/backend/executor/execUtils.c754

(Line numbers are hints as of the updated: date; the canonical anchor is the symbol name. RefreshMatViewByOid begins at its return-type line 164 and its signature line 165, etc. — grep the symbol, do not trust the row.)

Source verification (as of 2026-06-06)

Section titled “Source verification (as of 2026-06-06)”

Every claim below was re-checked against REL_18_STABLE at commit 273fe94852b (2026-06-05) by reading the named symbol in the named file.

Verified facts

Section titled “Verified facts”

  • A materialized view is a RELKIND_MATVIEW relation carrying exactly one SELECT INSTEAD rewrite rule whose single action is the defining Query. Verified in RefreshMatViewByOid (matview.c): the code rejects relhasrules == false / numLocks < 1 (“missing rewrite information”), numLocks > 1 (“too many rules”), a non-CMD_SELECT/non-isInstead rule (“not a SELECT INSTEAD OF rule”), and list_length(actions) != 1 (“not a single action”), then pulls dataQuery = linitial_node(Query, actions).

  • CREATE MATERIALIZED VIEW always creates the relation WITH NO DATA, then reuses the REFRESH path to populate. Verified in createas.c (ExecCreateTableAs path): is_matview = (into->viewQuery != NULL), then if (is_matview) { do_refresh = !into->skipData; into->skipData = true; }, and after create_ctas_nodata it calls RefreshMatViewByOid(address.objectId, true, false, false, ...) with is_create = true. Confirmed at lines 274/277/296.

  • The refresh lock strength is chosen up front from the CONCURRENTLY flag: ExclusiveLock if concurrent, else AccessExclusiveLock. Verified in ExecRefreshMatView (matview.c ~line 128): lockmode = stmt->concurrent ? ExclusiveLock : AccessExclusiveLock; — this single ternary is the entire reader-blocking-vs-not distinction. The lock is then taken by RangeVarGetRelidExtended(..., RangeVarCallbackMaintainsTable, ...).

  • CONCURRENTLY is rejected against an unpopulated matview, and CONCURRENTLY + WITH NO DATA is rejected as a contradiction. Verified in RefreshMatViewByOid (matview.c lines 208/214): the first raises ERRCODE_FEATURE_NOT_SUPPORTED “CONCURRENTLY cannot be used when the materialized view is not populated”, the second raises ERRCODE_SYNTAX_ERROR “CONCURRENTLY and WITH NO DATA options cannot be used together”.

  • The transient heap is filled with frozen, FSM-skipping bulk inserts. Verified in transientrel_startup (matview.c line 495): myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN; and the surrounding comment at line 290 (“data using TABLE_INSERT_FROZEN”). transientrel_receive calls table_tuple_insert(...) with those options and the comment “We know this is a newly created relation, so there are no indexes.”

  • The non-concurrent apply is a wholesale storage swap via finish_heap_swap, the same primitive CLUSTER/VACUUM FULL use. Verified in refresh_by_heap_swap (matview.c line 907): finish_heap_swap(matviewOid, OIDNewHeap, false, false, true, true, RecentXmin, ReadNextMultiXactId(), relpersistence);. The true, true are check_constraints / is_internal-style flags that drive the post-swap REINDEX. make_new_heap is defined in cluster.c (not matview.c).

  • The concurrent apply is a row-level diff: ANALYZE the temp table, reject whole-row duplicates, build a (tid, newdata) diff via a FULL JOIN keyed on a usable unique index, then DELETE removed rows and INSERT added rows. Verified end-to-end in refresh_by_match_merge (matview.c line 614): ANALYZE %s; the duplicate check raising ERRCODE_CARDINALITY_VIOLATION “contains duplicate rows without any null columns” (line 682); the diff CREATE TEMP TABLE ... (tid pg_catalog.tid) then ALTER TABLE ... ADD COLUMN newdata; the INSERT INTO ... FULL JOIN ... WHERE newdata.* IS NULL OR mv.* IS NULL; and the DELETE ... WHERE ctid = ANY (...) before INSERT INTO ... SELECT (diff.newdata).*.

  • The diff join equality is the unique index’s own opclass equality, looked up through the opfamily catalogs. Verified in refresh_by_match_merge: SearchSysCache1(CLAOID, ...) → opcfamily/opcintype → get_opfamily_member_for_cmptype(opfamily, opcintype, opcintype, COMPARE_EQ), with an elog(ERROR, "missing equality operator ...") guard.

  • A “usable” unique index must be unique, immediate, valid, non-partial, and over plain user columns only. Verified in is_usable_unique_index (matview.c line 915): the conjunction indisunique && indimmediate && indisvalid && RelationGetIndexPredicate(indexRel) == NIL && indnatts > 0, with the per-column loop rejecting any indkey.values[i] <= 0 (system column or expression).

  • Direct DML against a matview is gated by a maintenance-depth counter that the refresh machinery opens around its own DELETE/INSERT. Verified: static int matview_maintenance_depth = 0; (line 56); MatViewIncrementalMaintenanceIsEnabled returns depth > 0 (line 966); Open/CloseMatViewIncrementalMaintenance ++/— it (lines 970/976); and RefreshMatViewByOid saves/restores old_depth in a PG_TRY/PG_CATCH (lines 337/346/350) so a failed refresh cannot leave the backend “in maintenance.”

  • relispopulated is the single gating bit; it is read through two macros that are presently identical. Verified in rel.h lines 689/697: RelationIsScannable and RelationIsPopulated both expand to ((relation)->rd_rel->relispopulated). SetMatViewPopulatedState (matview.c line 79) updates that one pg_class column via CatalogTupleUpdate and CommandCounterIncrement()s so the new state is locally visible.

  • Scanning an unpopulated matview is a hard error, suppressed only for EXPLAIN-only or WITH-NO-DATA executions. Verified in ExecOpenScanRelation (execUtils.c lines 754-758): the guard (eflags & (EXEC_FLAG_EXPLAIN_ONLY | EXEC_FLAG_WITH_NO_DATA)) == 0 && !RelationIsScannable(rel) raises ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE “materialized view “%s” has not been populated” with the hint to run REFRESH.

Open questions / intentional non-coverage

Section titled “Open questions / intentional non-coverage”

  • No incremental view maintenance. Every REFRESH is a full recompute of the defining query; PostgreSQL core has no IVM. The pgsql-hackers “Incremental View Maintenance (IVM)” patch series (Nagata et al.) has lived out-of-tree for years and is not in REL_18. This doc asserts only the full-recompute reality.

  • make_new_heap / finish_heap_swap internals are deferred to postgres-heap-am.md and the CLUSTER path; this doc treats them as the storage-swap primitive and does not re-walk relfilenode lifecycle.

  • DDL execution and CREATE TABLE AS plumbing are deferred to postgres-ddl-execution.md; this doc enters createas.c only at the is_matview branch that defers data to REFRESH.

  • The exact post-swap REINDEX path (how finish_heap_swap’s last two boolean args drive index rebuild) is summarized, not traced; it belongs with the CLUSTER/REINDEX analysis.

Beyond PostgreSQL — Comparative Designs & Research Frontiers

Section titled “Beyond PostgreSQL — Comparative Designs & Research Frontiers”

  • Incremental view maintenance (the counting algorithm). The canonical alternative to full recompute is to differentiate the view’s relational algebra against base-table deltas. Gupta, Mumick & Subrahmanian 1993 (“Maintaining Views Incrementally,” SIGMOD) give the counting algorithm: store a multiplicity with each view row, push insert/delete deltas through per-operator differential rules, and add/subtract counts. PostgreSQL’s refresh_by_match_merge is a degenerate IVM — it computes the full new result and then diffs it against the stored result, paying O(result) to recompute but only O(changed rows) to apply. A true IVM would never materialize the full new result. Mapping refresh_by_match_merge’s FULL JOIN diff onto the counting algorithm’s delta rules is the natural theory bridge.

  • Eager / immediate maintenance via triggers. Oracle (FAST REFRESH with materialized-view logs), SQL Server (indexed views, maintained synchronously inside the writing transaction), and DB2 (REFRESH IMMEDIATE MQTs) all offer eager maintenance: the view is kept current as base tables change, at the cost of a write-path tax and restrictions on the view algebra. PostgreSQL offers none of this in core — it is strictly lazy + on-demand. A user wanting eager maintenance writes their own AFTER triggers (see postgres-triggers.md) or uses an extension. Contrasting Oracle’s MV-log delta capture against PostgreSQL’s “recompute everything then diff” sharpens the trade.

  • Query rewrite using materialized views. Oracle and SQL Server optimizers can transparently answer a query from a matching materialized view the user never named. PostgreSQL does not — a matview is used only when referenced explicitly. The textbook (Silberschatz 7e §16.5) treats this view-based rewrite as a first-class optimizer technique; PostgreSQL’s deliberate absence of it keeps the matview a pure stored-relation-with-a-remembered-query, never a planner hint.

  • Columnar / OLAP materialization. In analytic engines the materialized result is often the point of the storage format — C-Store / Vertica projections (see dbms-papers/cstore.md, dbms-papers/vertica-7-years.md) are pre-sorted, pre-joined, compressed column segments that function as always-maintained materialized views, refreshed by the tuple-mover rather than a user REFRESH. PostgreSQL’s heap-backed matview is row-store and refresh-on-demand; the column-store comparison (dbms-papers/column-vs-row.md) frames what a row-store matview gives up for OLAP scan speed.

  • Concurrent refresh vs. snapshot-isolation “instant swap.” PostgreSQL’s CONCURRENTLY keeps old readers consistent by doing MVCC DML under ExclusiveLock; an alternative used by some systems is a versioned/double- buffered relation where a refresh writes a shadow copy and an atomic catalog flip switches versions with no row-level DML — closer to the non-concurrent heap swap but without yanking the file from in-flight scans. PostgreSQL chose diff-merge instead because its swap primitive (finish_heap_swap) is inherently exclusive. A note on why versioned relations were not adopted (no per-relation MVCC version chain at the storage layer) would be the frontier.

  • Streaming / differential dataflow. Materialize (the database) and systems built on differential dataflow maintain views continuously over streams, recomputing only the affected deltas with sub-second latency. They are the modern realization of the IVM dream that PostgreSQL core declined. Positioning PostgreSQL’s batch REFRESH against continuous differential maintenance frames the whole lazy-vs-eager axis at its extreme.

Sources

Section titled “Sources”

In-tree source files (REL_18_STABLE, commit 273fe94)

Section titled “In-tree source files (REL_18_STABLE, commit 273fe94)”
  • src/backend/commands/matview.c — the entire feature: ExecRefreshMatView, RefreshMatViewByOid, refresh_matview_datafill, the DR_transientrel DestReceiver (transientrel_startup/_receive/_shutdown/_destroy), refresh_by_heap_swap, refresh_by_match_merge, is_usable_unique_index, SetMatViewPopulatedState, make_temptable_name_n, and the matview_maintenance_depth guard with its Open/Close/...IsEnabled API.
  • src/backend/commands/createas.c — ExecCreateTableAs and the is_matview branch that forces into->skipData = true and delegates population to RefreshMatViewByOid(..., is_create = true, ...).
  • src/backend/commands/cluster.c — make_new_heap (builds the transient heap) and finish_heap_swap (the relfilenode swap + REINDEX shared with CLUSTER and VACUUM FULL).
  • src/backend/executor/execUtils.c — ExecOpenScanRelation, where RelationIsScannable is enforced and the “has not been populated” error is raised.
  • src/include/commands/matview.h — ExecRefreshMatView, RefreshMatViewByOid, and MatViewIncrementalMaintenanceIsEnabled prototypes.
  • src/include/utils/rel.h — RelationIsScannable / RelationIsPopulated macros over rd_rel->relispopulated.
  • src/include/catalog/pg_class.h — the relispopulated, relkind (RELKIND_MATVIEW), relam, and relfilenode columns the feature relies on.

Papers and textbook chapters

Section titled “Papers and textbook chapters”
  • Gupta, A., Mumick, I. S. & Subrahmanian, V. S. (1993). “Maintaining Views Incrementally.” SIGMOD — the counting algorithm for incremental view maintenance; the theoretical alternative PostgreSQL’s full-recompute path deliberately forgoes.
  • Database System Concepts (Silberschatz, Korth, Sudarshan, 7e), §4.2.3 “Materialized Views” and §16.5 — the eager/lazy/periodic maintenance taxonomy, full-vs-incremental recomputation, and query rewrite using materialized views (knowledge/research/dbms-general/database-system-concepts.md).
  • Database Internals (Petrov 2019) — storage-manager and MVCC framing for the heap-swap and diff-merge apply strategies (knowledge/research/dbms-general/database-internals.md).
  • C-Store / Vertica (knowledge/research/dbms-papers/cstore.md, vertica-7-years.md) and column-vs-row (column-vs-row.md) — columnar projections as always-maintained materializations, the OLAP contrast to a row-store on-demand matview.

Sibling docs (cross-references — mechanism owned there, not duplicated here)

Section titled “Sibling docs (cross-references — mechanism owned there, not duplicated here)”
  • postgres-heap-am.md — heap tuple insertion (table_tuple_insert), the relfilenode lifecycle, and the storage-manager details behind make_new_heap / finish_heap_swap.
  • postgres-ddl-execution.md — utility-command dispatch and the CREATE TABLE AS / ExecCreateTableAs plumbing this doc enters only at the is_matview branch.
  • postgres-rewriter.md — the rule system that stores the matview’s defining Query as a SELECT INSTEAD action.
  • postgres-triggers.md — the AFTER triggers a user would hand-roll to approximate eager maintenance PostgreSQL core does not provide.
  • postgres-mvcc-snapshots.md — why concurrent readers under AccessShareLock see the old contents until the CONCURRENTLY refresh commits.
  • postgres-system-catalogs.md / postgres-relcache.md — pg_class, relispopulated, and the shared-invalidation that propagates the populated bit.