Materialized Views in Production: The CONCURRENTLY Keyword That Saved Us

We had a materialized view that took 3 minutes to refresh. During those 3 minutes, every query against it hung. In production. During business hours.

The fix was one word: CONCURRENTLY.

The Setup

Our job search platform uses a materialized view that pre-joins jobs with user match scores:

CREATE MATERIALIZED VIEW mv_job_search AS
SELECT j.id, j.title, j.company, j.location,
       m.match_percentage, m.user_id
FROM jobs j
JOIN job_matches m ON j.id = m.job_id
WHERE j.is_active = true;

This view powers our search. It’s refreshed every 30 minutes. The problem? REFRESH MATERIALIZED VIEW takes an exclusive lock.

The Problem

-- This blocks ALL reads for 2-3 minutes
REFRESH MATERIALIZED VIEW mv_job_search;

Every user searching for jobs during a refresh sees a spinner. For 2-3 minutes. Multiple times per hour.

The Fix

-- Step 1: Add a unique index (required for CONCURRENTLY)
CREATE UNIQUE INDEX mv_job_search_user_job_idx
ON mv_job_search (user_id, job_id);

-- Step 2: Use CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_job_search;

CONCURRENTLY builds the new data alongside the old, then swaps them atomically. Reads are never blocked.

The Gotcha

The unique index requirement trips people up. If your MV doesn’t have a natural unique key, you might need to add a synthetic one. But the cost of maintaining that index is tiny compared to the cost of blocking all reads for minutes at a time.

Bonus: False Alerts

Our monitoring flagged REFRESH MATERIALIZED VIEW as a “slow query” (it takes 138 seconds). We had to explicitly exclude it from slow query detection:

// SystemHealthMonitor.ts
const isRefresh = query.toLowerCase().includes('refresh materialized view');
if (isRefresh) return; // Not a real slow query

This runs at MisuJob, where materialized views power real-time job search across 1M+ listings.