Production Patterns — Size, Monitoring and When to Abandon Mat Views

Materialized views in production need monitoring: how large are they, how long do refreshes take, and when does the overhead outweigh the benefit?

-- ❌
-- One-time setup table for refresh-duration tracking
CREATE TABLE IF NOT EXISTS mv_refresh_log (
    id          BIGSERIAL PRIMARY KEY,
    view_name   TEXT NOT NULL,
    refreshed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    duration_ms INT,
    rows_changed BIGINT
);

-- ❌
-- Size of all materialized views vs their base tables
SELECT
    mv.schemaname,
    mv.matviewname,
    pg_size_pretty(pg_total_relation_size(
        (mv.schemaname||'.'||mv.matviewname)::regclass
    ))                                              AS mv_size,
    pg_size_pretty(pg_total_relation_size(
        (mv.schemaname||'.'||mv.matviewname)::regclass
    ) - pg_relation_size(
        (mv.schemaname||'.'||mv.matviewname)::regclass
    ))                                              AS index_size,
    mv.ispopulated,
    mv.hasindexes
FROM pg_matviews mv
WHERE mv.schemaname = 'absolutelearning'
ORDER BY pg_total_relation_size(
    (mv.schemaname||'.'||mv.matviewname)::regclass
) DESC;

-- Refresh duration monitoring (from our refresh log)
SELECT
    view_name,
    COUNT(*)                                        AS refresh_count,
    ROUND(AVG(duration_ms))                         AS avg_ms,
    MAX(duration_ms)                                AS max_ms,
    ROUND(AVG(row_count))                           AS avg_rows,
    SUM(CASE WHEN NOT success THEN 1 ELSE 0 END)    AS failures
FROM mv_refresh_log
WHERE refreshed_at >= NOW() - INTERVAL '7 days'
GROUP BY view_name
ORDER BY avg_ms DESC;