How to fix cannot refresh materialized view concurrently in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 307 views

This error occurs when attempting to use REFRESH MATERIALIZED VIEW CONCURRENTLY without a required unique index on the materialized view, or when the index includes a WHERE clause. Concurrent refresh requires a unique, unconditional index to safely perform the operation without locking the view.

Quick fix

Fixes cannot refresh materialized view concurrently

-- Replace 'my_materialized_view' with your actual view name
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'my_materialized_view'
  AND schemaname = 'public';
  1. Check if the materialized view has a unique index
  2. Create a unique index on the materialized view
  3. Verify the index has no WHERE clause
  4. Retry the concurrent refresh
postgresql — error
-- Replace 'my_materialized_view' with your actual view name
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'my_materialized_view'
  AND schemaname = 'public';
cannot refresh materialized view concurrently

What this error means

The REFRESH MATERIALIZED VIEW CONCURRENTLY statement allows PostgreSQL to refresh a materialized view while keeping it accessible for concurrent read queries. This is more complex than a standard refresh because PostgreSQL must perform an outer join between the new materialized data and the existing view to detect changes without blocking readers. To ensure this operation can succeed without duplicate row conflicts during the join, PostgreSQL requires a unique index on the materialized view with no conditions or expressions. When you attempt concurrent refresh without this index, or if the index has restrictions, PostgreSQL cannot proceed safely and raises the "cannot refresh materialized view concurrently" error.

Typical symptoms

  • REFRESH MATERIALIZED VIEW CONCURRENTLY fails with error 55000
  • Standard REFRESH works, but adding CONCURRENTLY causes the error
  • Hint message suggests creating a unique index on the materialized view
  • The materialized view has a unique index with a WHERE clause or expression
  • Concurrent refresh worked on an older PostgreSQL version but fails after an upgrade

Common causes

  1. No unique index exists on the materialized view at all
  2. The unique index includes a WHERE clause (partial index)
  3. The unique index is built on an expression rather than plain columns
  4. The unique index was dropped or missing after a schema change
  5. Using CONCURRENTLY with WITH NO DATA together, which is not allowed
  6. A bug in PostgreSQL versions before 10.18 where certain column names conflicted with internal aliases

How to fix "cannot refresh materialized view concurrently"

1Check if the materialized view has a unique index

Query the PostgreSQL system catalog to list all indexes on your materialized view:

sql
-- Replace 'my_materialized_view' with your actual view name
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'my_materialized_view'
  AND schemaname = 'public';

Look for an index that is marked as unique. If no index appears, or if the only index is not unique, you need to create one.

2Create a unique index on the materialized view

Create a unique index with NO WHERE clause on one or more columns of the materialized view. Choose columns that have unique or near-unique values:

sql
-- Example: if the materialized view has an id column
CREATE UNIQUE INDEX idx_my_mv_unique ON my_materialized_view(id);

-- Or use multiple columns if needed
CREATE UNIQUE INDEX idx_my_mv_unique ON my_materialized_view(schema_id, object_id);

The index MUST:
- Be declared UNIQUE
- Contain no WHERE clause (no partial indexes)
- Not be built on expressions like LOWER(name) or id + 1
- Only include plain column references

3Verify the index has no WHERE clause

Confirm the index you created is truly unconditional:

sql
SELECT indexdef
FROM pg_indexes
WHERE tablename = 'my_materialized_view'
  AND schemaname = 'public'
  AND indexname = 'idx_my_mv_unique';

The indexdef should output something like:

bash
CREATE UNIQUE INDEX idx_my_mv_unique ON public.my_materialized_view(id)

If it contains a WHERE clause or function calls, drop it and recreate it without those restrictions.

4Retry the concurrent refresh

Now that a valid unique index exists, the concurrent refresh should succeed:

sql
REFRESH MATERIALIZED VIEW CONCURRENTLY my_materialized_view;

If you still encounter an error, check that:
- The view definition hasn't changed
- The index still exists and is marked UNIQUE
- You are not combining CONCURRENTLY with WITH NO DATA (use one or the other)
- Your PostgreSQL version is 9.4 or later (when CONCURRENTLY was introduced)

5Understand when concurrent refresh is slower

Concurrent refresh is more resource-intensive than a standard refresh because it:
- Creates a temporary table with the new materialized data
- Performs a full outer join to detect changes
- Avoids locks that would block read queries

If your materialized view is small or updates are infrequent, a non-concurrent refresh may be faster:

sql
-- Standard refresh (locks the view, but faster for small views)
REFRESH MATERIALIZED VIEW my_materialized_view;

-- Concurrent refresh (slower, keeps view readable)
REFRESH MATERIALIZED VIEW CONCURRENTLY my_materialized_view;

Choose based on your availability requirements and view size.

Advanced notes

Concurrent refresh was introduced in PostgreSQL 9.4 (2014). A bug in versions before 10.18 prevented concurrent refresh if the materialized view contained columns named "mv" or "newdata" because those names conflicted with internal aliases PostgreSQL generated. If you are on PostgreSQL 10.x and encounter this error despite having a valid unique index, upgrade to 10.18+ or use non-concurrent refresh. Materialized views are useful for denormalizing expensive queries, but keep in mind that the data is a snapshot; use regular refresh (with CONCURRENTLY for large views) in your background jobs or triggers to keep the view in sync with source tables. Always test the refresh performance in your environment because concurrent refresh may not be faster for all view sizes.

Frequently asked questions

What does "cannot refresh materialized view concurrently" mean?
The REFRESH MATERIALIZED VIEW CONCURRENTLY statement allows PostgreSQL to refresh a materialized view while keeping it accessible for concurrent read queries. This is more complex than a standard refresh because PostgreSQL must perform an outer join between the new materialized data and the existing view to detect changes without blocking readers. To ensure this operation can succeed without duplicate row conflicts during the join, PostgreSQL requires a unique index on the materialized view with no conditions or expressions.
How do I fix "cannot refresh materialized view concurrently"?
This error occurs when attempting to use REFRESH MATERIALIZED VIEW CONCURRENTLY without a required unique index on the materialized view, or when the index includes a WHERE clause. Concurrent refresh requires a unique, unconditional index to safely perform the operation without locking the view.
What causes "cannot refresh materialized view concurrently"?
No unique index exists on the materialized view at all The unique index includes a WHERE clause (partial index) The unique index is built on an expression rather than plain columns
Is the "cannot refresh materialized view concurrently" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading