Database Views & Materialised Views
debt(d7/e5/b5/t7)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints indicate no automated detection, and tools like mysql-workbench and pgadmin are manual inspection tools rather than automated scanners. Identifying whether a regular view vs materialised view is appropriate, or spotting repeated complex JOINs that should be views, requires careful review of query patterns and performance profiling.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix suggests encapsulating complex joins into views, which typically means modifying multiple PHP query locations to use the new view, creating the view definition, and potentially setting up refresh schedules for materialised views. Not a one-line fix, but contained to the database layer and its consumers.
Closest to 'persistent productivity tax' (b5). Views and materialised views create ongoing maintenance burden: refresh schedules must be managed, SELECT * views can break when tables change, and the abstraction layer between PHP code and raw tables adds complexity. Applies broadly across web and cli contexts per applies_to, affecting multiple work streams that touch reporting or complex queries.
Closest to 'serious trap' (t7). The misconception explicitly states developers believe 'views improve query performance' when regular views provide no caching at all — they re-execute the full query every time. This contradicts how similar concepts work (e.g., caching layers, indexes) where the name implies performance benefit. The common_mistakes compound this with stale materialised view data being served silently.
Also Known As
TL;DR
Explanation
Regular views execute the underlying query every time they are accessed — no storage, always current, but no performance benefit over the query itself. Materialised views (PostgreSQL, Oracle) store the result set and must be refreshed (REFRESH MATERIALIZED VIEW) — O(1) reads at the cost of staleness and storage. Use views for: readable query aliases, hiding complexity, column-level security. Use materialised views for: expensive aggregations, reporting queries, denormalised read models that can tolerate some staleness.
Common Misconception
Why It Matters
Common Mistakes
- Expecting regular views to cache results — they re-execute the full query on every access.
- Not refreshing materialised views after data changes — stale data silently served to users.
- REFRESH MATERIALIZED VIEW blocking reads — use REFRESH MATERIALIZED VIEW CONCURRENTLY in PostgreSQL to avoid locks.
- Views with SELECT * — underlying table changes (added columns) change the view's output unexpectedly.
Code Examples
-- Regular view used for performance — no benefit:
CREATE VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', created_at) AS month, SUM(total)
FROM orders GROUP BY 1;
-- SELECT * FROM monthly_revenue still scans millions of orders every time-- Materialised view — computed once, queried fast:
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', created_at) AS month, SUM(total)
FROM orders GROUP BY 1;
CREATE UNIQUE INDEX ON monthly_revenue(month);
-- Refresh after order changes (or on schedule):
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
-- Now: SELECT * FROM monthly_revenue — sub-millisecond