## TL;DR
Use `REFRESH MATERIALIZED VIEW CONCURRENTLY`, which requires a unique index on the view. It rebuilds into a temp table and swaps, so readers never block. Plain refresh takes an exclusive lock for the whole rebuild.

```text
postgres materialized view refresh without locking
```

## Use this when
- Refreshing a materialized view blocks reader queries
- You need the view available during refresh
- Refreshes take minutes and readers cannot wait

## Not for this skill when
- Autovacuum is falling behind on tables
- You need upsert syntax
- JSONB queries ignore the GIN index

## Steps

1. Add the unique index that CONCURRENTLY requires. One is enough, on whatever column(s) are unique:

```sql
CREATE UNIQUE INDEX ON sales_daily (day, region);
```
Expected output: the index builds. Without it, `REFRESH ... CONCURRENTLY` errors immediately. The index also serves point lookups on the view.

2. Refresh concurrently:

```sql
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_daily;
```
Expected output: `REFRESH MATERIALIZED VIEW` on success. Readers keep querying the old data while the new version builds, then see the fresh data atomically.

3. Understand the tradeoff you just accepted:

```text
Concurrent refresh: no reader blocking, but slower (it diffs old vs new)
and needs the unique index.
Plain refresh: faster rebuild, but ACCESS EXCLUSIVE lock blocks
all reads until it finishes.
```
Expected output: correct expectations. Concurrent refresh can take significantly longer on wide views because it computes the delta.

4. If the view has no unique column combination, you cannot use CONCURRENTLY directly. The workaround is a table swap:

```sql
CREATE TABLE sales_daily_new AS SELECT ...;  -- same query as the view
BEGIN;
ALTER TABLE sales_daily RENAME TO sales_daily_old;
ALTER TABLE sales_daily_new RENAME TO sales_daily;
DROP TABLE sales_daily_old;
COMMIT;
```
Expected output: readers see either the old or the new table, never a half-built one. The rename is atomic. You lose the materialized-view bookkeeping, so only do this when CONCURRENTLY is truly unavailable.

5. Schedule refreshes with the staleness your readers actually tolerate:

```sql
-- in your scheduler, not in Postgres itself (no built-in scheduling):
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_daily;
```
Expected output: fresh-enough data on a cadence. Postgres has no native refresh scheduling; use cron, pg_cron, or your orchestrator, and alert when a refresh fails so the view does not go silently stale.

## Variant phrasings

### refresh materialized view concurrently requires unique index
Correct, that is the hard requirement (step 1). The unique index is how the concurrent refresh matches old rows to new rows for the diff.

### postgres materialized view blocking reads
Plain REFRESH takes ACCESS EXCLUSIVE. Switch to CONCURRENTLY (step 2) or the swap pattern (step 4).

### materialized view refresh taking too long
Concurrent refresh diffs, which is slower than a plain rebuild. If the view is huge and readers can tolerate a brief lock, plain refresh is faster. Otherwise, make the underlying query cheaper.

## Why it happens
A materialized view is a physical table. Plain refresh truncates and repopulates it under an exclusive lock, so every reader waits. CONCURRENTLY builds the new contents aside and swaps, using the unique index to compute which rows changed, which is why the index is mandatory.

## Edge cases
- CONCURRENTLY still takes a brief lock at the swap moment; it is milliseconds, not minutes.
- If the refresh query itself is slow, concurrent refresh does not fix that, it only fixes the blocking.
- Views with `SELECT *` from changing source schemas break refreshes; list columns explicitly.
- Two concurrent refreshes of the same view conflict; serialize them in the scheduler.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_eRjK1H52mPpj3qgUN9hA3w