postgres error: REFRESH MATERIALIZED VIEW CONCURRENTLY failed - how to fix

Not specified by the original author.

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.

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:

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:

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:

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:

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:

-- 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

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

Thread query: postgres materialized view refresh without locking

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.

Not specified by the original author.

TL;DR

[adsehgal (commenter)]: To migrate a Zitadel instance from CockroachDB to PostgreSQL, run the init command first, then the setup command, then mirror with the --replace flag. 23.2.16, migrating to Postgres 16/17

Steps

1. [adsehgal (commenter)]: To migrate a Zitadel instance from CockroachDB to PostgreSQL, run the init command first, then the setup command, then mirror with the --replace flag. Two reporters confirmed the migration worked with no issues.

When to use

You are seeing this: Issue zitadel/zitadel#9042 (closed, 26 comments): ### Preflight Checklist - [x] I could not find a solution in the documentation, the existing issues or discussions - [ ] I have joined the ZITADEL chat Use this skill when you run into "Error while migrating from Cockroach to Postgres".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

Versions mentioned in the source: 2.65.1, 23.2.16. If you are on something much newer or older, the details may have shifted.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Sources

• https://zitadel.com/chat
• https://zitadel.com/docs/self-hosting/manage/cli/mirror
• https://github.com/zitadel/zitadel/issues/9042

In gh:zitadel/zitadel#9042, the reporter ran into: Issue zitadel/zitadel#9042 (closed, 26 comments): ### Preflight Checklist

• [x] I could not find a solution in the documentation, the existing issues or discussions
• [ ] I have joined the ZITADEL chat

Environment

Self-hosted

Version

2.65.1

Database

CockroachDB

Database Version

23.2.16, migrating to Postgres 16/17

Describe the problem caused by this bug

There seems to be an issue when migrating from CockroachDB to Postgres on this version.

Following the instruction from here (https://zitadel.com/docs/self-hosting/manage/cli/mirror)... The verified answer was: [adsehgal (commenter)]: To migrate a Zitadel instance from CockroachDB to PostgreSQL, run the init command first, then the setup command, then mirror with the --replace flag. Two reporters confirmed the migration worked with no issues. Source issue: https://github.com/zitadel/zitadel/issues/9042

Not specified by the original author.

TL;DR

Use this checklist when an application release must preserve existing tables, public identifiers, and read paths. Record a read-before-write fixture using an existing public ID.

Steps

1. Compare the expected catalog against the currently deployed schema.
2. Review the migration for additive operations and an explicit transaction boundary.
3. Validate new constraints without replacing existing identifiers.
4. Record a read-before-write fixture using an existing public ID.
5. Read the existing fixture through the public API.
6. Create a fresh record and read it back through an independent path.
7. Confirm idempotent retries return the original committed result.
8. Verify the old permalink still resolves.
9. Promote only the exact candidate that passed these checks.
10. Do not promote if a catalog difference is unexplained, an existing public ID changes, or a retry can create a second logical record.

When to use

You are seeing this: Compare the expected catalog against the currently deployed schema. Use this skill when you run into "Additive Postgres migration release gate".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Derived from the linked public conversation and verified against the current canonical production release workflow.