skip to content

View Security & Refresh

A PostgreSQL view runs with its owner's rights unless marked security_invoker, and a materialized view refreshes only on command. Interviewers probe REFRESH CONCURRENTLY and auto-updatable views.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PostgreSQL, what does REFRESH MATERIALIZED VIEW CONCURRENTLY require, and what does it cost compared with a plain refresh of the same view?

level: middleimportance: must knowfreq 55%

answer

  1. lock strength vs readers
  2. a unique index on plain columns
  3. temp table, diff, DELETE plus INSERT
  4. only after the first population

basics

~20 s

PostgreSQL's REFRESH MATERIALIZED VIEW CONCURRENTLY needs a populated view with a unique index on plain columns and no WHERE clause. It keeps readers unblocked by diffing new rows against old, at the cost of more work and dead tuples.

solid answer

~40 s

A plain `REFRESH MATERIALIZED VIEW` takes an `ACCESS EXCLUSIVE` lock, runs the query into a new heap and swaps it in, so every `SELECT` on the view waits until the refresh commits. `CONCURRENTLY` takes only an `EXCLUSIVE` lock, which still admits readers: it runs the query into a temporary table, full-outer-joins that against the current contents, and applies the difference as `DELETE` and `INSERT`. To pair rows it needs at least one `UNIQUE` index that uses only column names and has no `WHERE` clause, and the view must already be populated. The price is more CPU and temp space when many rows change, plus dead tuples in the materialized view for vacuum. Either way the whole query reruns, and only one refresh of a view runs at a time.

code

sql · 17 lines
sql
CREATE MATERIALIZED VIEW reporting.sales_leaderboard AS
SELECT s.seller_id,
       sum(s.amount) AS revenue_30d,
       count(*)      AS orders_30d
FROM   sales.orders s
WHERE  s.sold_at >= now() - interval '30 days'
GROUP  BY s.seller_id
WITH NO DATA;

CREATE UNIQUE INDEX sales_leaderboard_seller_uq
    ON reporting.sales_leaderboard (seller_id);

-- First fill: CONCURRENTLY is not allowed on an unpopulated view.
REFRESH MATERIALIZED VIEW reporting.sales_leaderboard;

-- Every later night: readers keep reading the old contents.
REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.sales_leaderboard;

go deeper

for a junior

Recall that a PostgreSQL materialized view changes only when refreshed, and that CONCURRENTLY needs a unique index and keeps readers unblocked.

for a middle

Explain the two lock modes, why the unique index must be on plain columns without a WHERE clause, and the temp-table diff applied as DELETE and INSERT.

for a senior

Show you weigh the trade: blocked readers versus extra CPU, temp space and dead tuples, and that a per-run timestamp column defeats the diff.

for a principal

Frame when a materialized view stops being the right tool at all, because every refresh reruns the full query however it is applied.

## What a refresh does in PostgreSQL A **materialized view** in PostgreSQL is a relation with real storage plus a remembered query. Its contents change only when someone runs `REFRESH MATERIALIZED VIEW`; core PostgreSQL does not track base-table changes and has no incremental refresh. Every refresh, in either form, **re-executes the full defining query**. What differs between the two forms is how the new result replaces the old one, and that decides who is blocked while it happens. ## Plain REFRESH: rebuild and swap Without options, `REFRESH MATERIALIZED VIEW sales_leaderboard`: 1. takes an `ACCESS EXCLUSIVE` lock on the materialized view and holds it until the transaction ends; 2. runs the defining query into a brand-new heap; 3. swaps the new heap, with rebuilt indexes, in place of the old one. `ACCESS EXCLUSIVE` conflicts with every lock mode, including the `ACCESS SHARE` lock that a plain `SELECT` takes, so dashboard queries wait for the whole refresh. The upside is efficiency: the result is a fresh, compact heap with no dead tuples, and it is the least total work when most rows change. ## CONCURRENTLY: build, diff, apply `REFRESH MATERIALIZED VIEW CONCURRENTLY` takes an `EXCLUSIVE` lock instead. That mode allows only `ACCESS SHARE` locks alongside it, so readers keep reading the old contents. The work is: - run the defining query into a **temporary table**; - **full-outer-join** it against the current contents, matching rows on the unique index columns and whole-row equality, to find rows that vanished and rows that are new or changed; - apply the difference with set-based `DELETE` and `INSERT` statements against the materialized view. A changed row is deleted and re-inserted, not updated in place. All of this runs in one transaction, so readers see the old contents until commit and the new contents after it, never a half-applied state. ## The unique-index requirement The diff must pair old and new rows reliably, so `CONCURRENTLY` is allowed only when the materialized view has **at least one `UNIQUE` index that uses only column names and covers all rows**. Without one the command fails with `cannot refresh materialized view "..." concurrently` and the hint *Create a unique index with no WHERE clause on one or more columns of the materialized view*. | Index on `sales_leaderboard` | Usable for CONCURRENTLY? | |---|---| | `UNIQUE (seller_id)` | Yes | | `UNIQUE (seller_id, revenue_30d)` | Yes | | `UNIQUE ((seller_id::text))`, an expression index | No | | `UNIQUE (seller_id) WHERE revenue_30d > 0`, a partial index | No | | a non-unique index on `(seller_id)` | No | Two more preconditions apply: - The materialized view must already be **populated**. A view created `WITH NO DATA` needs one plain refresh first; `CONCURRENTLY` on it fails, and `CONCURRENTLY` together with `WITH NO DATA` is rejected outright. - Refreshes are **serialized**. `EXCLUSIVE` conflicts with itself and with `ACCESS EXCLUSIVE`, so a second refresh of the same view waits for the first. ## Costs, and how to choose | | Plain | CONCURRENTLY | |---|---|---| | Lock taken | `ACCESS EXCLUSIVE` | `EXCLUSIVE` | | Readers during refresh | Blocked | Keep reading old contents | | Extra space | A new heap | A temp table plus the diff | | Dead tuples left behind | None | One per deleted or changed row | | Fastest when | Most rows change | Few rows change | - If most rows change every night and nobody reads at that hour, the plain form is cheaper and leaves no bloat. - If a dashboard reads all day, `CONCURRENTLY` keeps it serving, at the cost of extra CPU, temp space and vacuum work on the materialized view itself. - Neither form is incremental: on a huge source both still run the whole aggregation. ## Applied to a nightly sales leaderboard A leaderboard that sums 30 days of sales per seller has a natural key, `seller_id`, so a unique index on it makes `CONCURRENTLY` available from the second refresh onward. The first fill after `WITH NO DATA` must be a plain refresh. Watch one trap in the definition: a column whose value differs on every run, such as a refresh timestamp, makes every row differ, so the diff deletes and re-inserts the whole view and the concurrent form loses its point.

  • Why can a concurrent refresh take longer than a plain one on the same PostgreSQL materialized view?
    Both rerun the full query, but `CONCURRENTLY` then writes the result to a temp table, full-outer-joins it against the existing rows and applies `DELETE` and `INSERT` for every difference. When most rows change, that diff and the index maintenance cost more than simply writing a new heap and swapping it in. It wins only when a small share of rows changes.
  • After switching a PostgreSQL leaderboard refresh to CONCURRENTLY, the materialized view keeps growing on disk; why?
    A concurrent refresh changes rows by deleting and re-inserting them, so each changed row leaves a dead tuple in the materialized view, exactly as on a table. Autovacuum has to clean those up. A plain refresh writes a brand-new heap and swaps it in, so it never accumulates dead tuples.
  • Can two sessions refresh the same PostgreSQL materialized view at the same time?
    No. A concurrent refresh holds `EXCLUSIVE`, which conflicts with itself, and a plain refresh holds `ACCESS EXCLUSIVE`, which conflicts with everything. The second refresh waits until the first commits, so an overlapping scheduled job queues behind the previous run instead of running in parallel.

A plain refresh is closing a shop to restock: shelves are emptied and refilled while customers wait outside. A concurrent refresh restocks during opening hours by checking a fresh stock list against the labelled shelves and swapping only the items that differ; it needs every shelf to carry a unique label, and it is slower when almost everything changes.

  • Any index on the materialized view is enough for CONCURRENTLY.
  • A partial or expression unique index satisfies the CONCURRENTLY requirement.
  • A plain REFRESH lets SELECTs keep reading the old contents while it runs.
  • CONCURRENTLY applies only the base-table changes since the last refresh.
  • CONCURRENTLY works on a materialized view created WITH NO DATA.
  • Two concurrent refreshes of one materialized view run in parallel.
open as a page

In PostgreSQL, what happens to a view defined as SELECT * FROM a table when a column of that table is later added, retyped or dropped?

level: juniorimportance: should knowfreq 38%

basics

~20 s

PostgreSQL expands * at creation and stores the parsed query as the view's _RETURN rule, so a new table column stays invisible until the view is replaced, and retyping or dropping a column it uses fails.

open as a page

In PostgreSQL, what makes a view automatically updatable, and what stops an UPDATE through a region-filtered view from moving a row out of it?

level: middleimportance: should knowfreq 45%

basics

~20 s

A PostgreSQL view is auto-updatable when it reads one table or updatable view without WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET, set operations, aggregates, window or set-returning functions. WITH CHECK OPTION rejects rows the view could not see.

open as a page

A nightly job refreshes the PostgreSQL materialized view behind a sales leaderboard; which role should run it, and which failure modes must the job handle?

level: seniorimportance: should knowfreq 30%

basics

~20 s

Since PostgreSQL 17, grant the job role MAINTAIN on that one materialized view instead of ownership; the query still runs as the owner with search_path pinned. The job must handle lock waits, overlapping runs, timeouts and dependent views.

open as a page

In PostgreSQL, whose privileges and row-level security policies apply when a support agent queries a view, and what changes when the view sets security_invoker?

level: seniorimportance: should knowfreq 40%

basics

~20 s

By default a PostgreSQL view checks base-table privileges and applies row-level security as the view's owner. With security_invoker = true it uses the querying user's privileges and policies instead; how functions in the view run is unaffected.

open as a page