Beginner LevelPostgreSQL
What is the difference between a standard View and a Materialized View?
❓ Interview Prompt: Compare standard Views (virtual queries executed on read) vs Materialized Views (persisted query results on disk, REFRESH CONCURRENTLY).
1. Standard View ('CREATE VIEW'):
•
A virtual query definition. Executes the underlying query on every read.
•
Takes 0 disk space; always returns real-time fresh data.
2. Materialized View ('CREATE MATERIALIZED VIEW'):
•
Executes the query ONCE and persists the result set to disk.
•
Queries read instantly from disk pages.
•
Must be refreshed manually: 'REFRESH MATERIALIZED VIEW CONCURRENTLY view_name'.
Code Implementation Example
sql
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT date_trunc('month', created_at) AS month, SUM(total) AS total_sales
FROM orders
GROUP BY 1;
-- Create unique index required for CONCURRENTLY refresh
CREATE UNIQUE INDEX idx_monthly_sales_month ON monthly_sales_summary (month);
-- Refresh view concurrently without blocking reads!
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;💡Creating and concurrently refreshing Materialized Views.
🎯 Interview Key Takeaway
Use Materialized Views to cache complex aggregation queries to disk, refreshing them periodically with CONCURRENTLY.
#Views#Materialized Views#REFRESH#Caching#Beginner
Back to all PostgreSQL Questions→