REFRESH MATERIALIZED VIEW CONCURRENTLY

What REFRESH CONCURRENTLY does

A standard REFRESH MATERIALIZED VIEW takes the blunt approach: drop all the existing data, re-execute the query, and write the new results. Effective, but it acquires an ACCESS EXCLUSIVE lock on the view for the entire duration. Any query that tries to read the view while the refresh is running will block and wait. One might call this the "closed for renovation" strategy.

REFRESH CONCURRENTLY is more considerate. It executes the underlying query into a temporary staging area, then compares the new result set against the existing data row by row. It identifies which rows are new, which were removed, and which changed. Then it applies only the differences — inserting new rows, deleting removed ones, and updating changed ones. Readers continue to see the old data throughout this process. Only at the very end, when the diff has been fully applied, does PostgreSQL take a brief lock to finalize the swap.

The result: zero read downtime. Queries against the materialized view never return an error or block during a concurrent refresh. They see either the old data or the new data, never a partial state. The guests are served without interruption while the kitchen restocks.

Requirements

If I may, two conditions must be met before REFRESH CONCURRENTLY will work:

• A unique index on the materialized view — PostgreSQL needs row identity to compute the diff. Without a unique index, it cannot match rows in the old version to rows in the new version. Any unique index will do: single-column, composite, or expression-based. If the unique index is missing, the command fails with an error.
• The view must have been populated at least once — if you created the view with WITH NO DATA, you must run a standard REFRESH first. A concurrent refresh against an unpopulated view fails because there is no existing data to diff against.

-- Create a materialized view
CREATE MATERIALIZED VIEW order_totals AS
SELECT
  customer_id,
  count(*) AS order_count,
  sum(amount) AS total_amount
FROM orders
GROUP BY customer_id;

-- A unique index is mandatory for CONCURRENTLY
CREATE UNIQUE INDEX ON order_totals (customer_id);

-- Now concurrent refresh will work
REFRESH MATERIALIZED VIEW CONCURRENTLY order_totals;

REFRESH vs REFRESH CONCURRENTLY

-- Standard: fast, but readers are locked out
-- Takes an ACCESS EXCLUSIVE lock on the view
REFRESH MATERIALIZED VIEW order_totals;

-- Concurrent: slower, but readers are never blocked
-- Takes an EXCLUSIVE lock only briefly at the end to swap data
REFRESH MATERIALIZED VIEW CONCURRENTLY order_totals;

The guidance here is straightforward. Use standard REFRESH when the view has no readers during the refresh window — maintenance windows, batch jobs, or views that only serve periodic reports. Use CONCURRENTLY when the view backs a live application and read availability matters. If anyone is reading, they deserve not to be interrupted.

Scheduling refreshes

PostgreSQL does not refresh materialized views automatically. Left to its own judgment, a materialized view will serve last week's data with perfect confidence and never mention the discrepancy. You choose when and how often to refresh. The most common approach is pg_cron, which runs scheduled jobs directly inside the database.

-- Refresh every 10 minutes using pg_cron
SELECT cron.schedule(
  'refresh-order-totals',
  '*/10 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY order_totals'
);

-- Check refresh history
SELECT jobid, runid, job_status, start_time, end_time
FROM cron.job_run_details
WHERE command LIKE '%order_totals%'
ORDER BY start_time DESC
LIMIT 5;

When choosing a refresh interval, consider three things:

• How stale can the data be? — a dashboard refreshed every 15 minutes is 15 minutes stale in the worst case. Decide what your users can tolerate.
• How long does the refresh take? — if the refresh takes 8 minutes and you schedule it every 10 minutes, you have 2 minutes of headroom. That is fragile. Leave a comfortable margin.
• What is the cost of each refresh? — every refresh re-executes the full underlying query. On a busy database, frequent refreshes of expensive views can compete with production traffic for CPU and I/O.

I should note: monitoring refresh duration over time is not optional. As the underlying data grows, refresh time grows with it. A schedule that works comfortably today may be running late in six months, and a late refresh is a silent one — no alarms, just stale data served to unsuspecting guests.

-- Check when a materialized view was last refreshed
-- (no built-in column — use pg_stat_user_tables as a proxy)
SELECT
  relname,
  last_vacuum,
  last_analyze,
  n_tup_ins AS rows
FROM pg_stat_user_tables
WHERE relname = 'order_totals';

-- Or track refresh times in a log table
CREATE TABLE matview_refresh_log (
  view_name text,
  refreshed_at timestamptz DEFAULT now(),
  duration interval
);

How Gold Lapel relates

Gold Lapel creates materialized views automatically based on observed query patterns and manages the refresh lifecycle — including this particular decision. When to use CONCURRENTLY vs standard REFRESH? Gold Lapel settles the question by observing whether queries are actively hitting the view at refresh time. Live traffic means concurrent refresh. No readers means the faster standard path. It also sets refresh intervals based on how quickly the underlying data changes, and adjusts those schedules as workload patterns shift.

The CONCURRENTLY decision is straightforward in principle but easy to neglect in practice — the wrong choice either locks out your readers or wastes resources on diff computation that nobody needed. Gold Lapel handles the prerequisite as well, ensuring the unique index exists before attempting a concurrent refresh. One less thing for you to manage, which is rather the point.