Materialized Views
debt(d9/e5/b5/t7)
Closest to 'silent in production until users hit it' (d9). The detection_hints note 'automated: no' and tools like pganalyze and mysql-slow-query-log only surface the underlying slow queries — they cannot detect that a materialized view is serving stale data or that a refresh schedule is missing. Stale results silently reach users with no runtime warning; the bug only becomes apparent when users notice outdated figures.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix describes creating the view and scheduling REFRESH MATERIALIZED VIEW CONCURRENTLY, but the common_mistakes list reveals several interacting concerns: setting up a refresh schedule (cron/queue job), adding indexes to the materialized view, and auditing every consuming query for acceptable staleness. This goes beyond a single-line patch and touches application scheduling, database DDL, and query consumers — spanning multiple files and system layers.
Closest to 'persistent productivity tax' (b5). Applies to web and CLI contexts broadly. Once introduced, every future schema change to the underlying tables must account for the materialized view definition, refresh logic must be maintained, and developers must reason about data freshness whenever reading from the view. It imposes an ongoing tax on multiple work streams (schema migrations, deployment procedures, monitoring) without necessarily defining the entire system's shape.
Closest to 'serious trap' (t7). The misconception field directly states the canonical wrong belief: developers familiar with regular views naturally assume materialized views are equally up to date, since both are called 'views.' In reality they behave oppositely — storing physical snapshots that go stale. This contradicts how the analogous concept (regular views) works, earning t7. The name gives no indication of the staleness behaviour, and the failure mode (silently serving outdated data) is non-obvious.
Also Known As
TL;DR
Explanation
A materialized view stores the result of a complex query (heavy aggregations, multi-table joins) as a physical table that can be indexed and queried instantly. Unlike regular views which recompute on every access, materialized views are refreshed on a schedule or on-demand. PostgreSQL supports REFRESH MATERIALIZED VIEW CONCURRENTLY (non-blocking). MySQL lacks native materialized views — they are emulated as real tables populated by event schedulers or application code. Ideal for dashboards and reporting that aggregate millions of rows but can tolerate slightly stale data (last-refreshed 5 minutes ago).
Common Misconception
Why It Matters
Common Mistakes
- Not refreshing materialized views — stale data silently serves outdated results.
- Refreshing too frequently — REFRESH MATERIALIZED VIEW locks the view briefly; concurrent refresh avoids this.
- Using materialized views for frequently changing data where staleness is unacceptable.
- Not indexing materialized views — they are stored like tables and need indexes for fast access.
Code Examples
-- Slow query run on every dashboard load:
SELECT category, COUNT(*), SUM(revenue) FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY category; -- 3 seconds on 10M rows
-- Materialized view — pre-computed, instant:
CREATE MATERIALIZED VIEW order_stats AS (above query);
REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats; -- Run via cron-- PostgreSQL: pre-compute expensive aggregate
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue,
COUNT(*) AS order_count
FROM orders
WHERE status = 'paid'
GROUP BY 1;
CREATE INDEX ON monthly_revenue(month);
-- Query is now instant (reads pre-computed data)
SELECT * FROM monthly_revenue WHERE month >= '2024-01-01';
-- Refresh (schedule in cron or trigger)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
-- CONCURRENTLY allows reads during refresh (PostgreSQL 9.4+)