REFRESH MATERIALIZED VIEW — Full vs CONCURRENTLY

Refreshing a materialized view replaces its content with a fresh query result. Two modes: full (blocks all reads during refresh) and CONCURRENTLY (non-blocking but requires a unique index and runs slower).

-- Full refresh: locks the view for the duration (readers blocked)
REFRESH MATERIALIZED VIEW mv_user_stats;

-- CONCURRENTLY: builds new data alongside old, swaps atomically — no read lock
-- Requires a unique index on the mat view
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_stats;

-- Check when a mat view was last refreshed
-- (store computed_at inside the view itself)
SELECT MAX(computed_at) AS last_refresh FROM mv_user_stats;

How CONCURRENTLY works internally

1. Compute new result set into a temp staging area
2. Diff old vs new: find inserted, updated, deleted rows
3. Apply diff to the mat view in a single transaction
4. Readers see the old version until the swap completes

-- Practical refresh schedule: wrap in a function for error handling
CREATE OR REPLACE FUNCTION refresh_user_stats()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_stats;
    RAISE NOTICE 'mv_user_stats refreshed at %', NOW();
EXCEPTION WHEN OTHERS THEN
    RAISE WARNING 'Refresh failed: %', SQLERRM;
END;
$$;

SELECT refresh_user_stats();