Materialized Views and Partitioning

1. Introduction

Materialized Views pre-compute and store the result of a complex query as a physical table, dramatically speeding up read-heavy workloads. Table Partitioning (PG 10+ declarative) splits a large table into smaller physical pieces based on a partition key (range, list, or hash), improving query performance, maintenance operations, and data lifecycle management. Both are essential tools for scaling PostgreSQL to billions of rows.

2. Why It Matters

A dashboard query joining 10 tables with aggregations might take 30 seconds on raw data but 50ms from a materialized view. A 500 GB table scanned with partition pruning might only read 2 GB of relevant data. Without these tools, large datasets force expensive full-table scans and slow aggregation queries that can't meet SLA requirements.

3. Real-World Analogy

A materialized view is like a printed report that you generate once and hand out — readers get instant answers, but the data becomes stale until you reprint (REFRESH). Partitioning is like organizing a warehouse by month — when someone asks for "March orders," you only walk to the March aisle instead of searching every shelf.

4. How It Works

• Materialized Views: Created with CREATE MATERIALIZED VIEW. The query is executed once and results stored on disk. Refreshed via REFRESH MATERIALIZED VIEW (full rewrite) or CONCURRENTLY (requires a UNIQUE index, doesn't block reads).
• Partitioning strategies: Range (dates, IDs), List (categories, regions), Hash (even distribution). The planner uses partition pruning to skip irrelevant partitions at query time.
• Declarative syntax (PG 10+): PARTITION BY RANGE (created_at) on the parent table, then CREATE TABLE ... PARTITION OF ... FOR VALUES FROM ... TO ....

5. Internal Architecture

Partitions are separate tables linked to the parent via inheritance metadata. Each partition has its own indexes, statistics, and storage. The planner inspects the WHERE clause against partition bounds and eliminates non-matching partitions before execution (static pruning). Some joins and subqueries enable dynamic pruning at execution time. Materialized views are stored as regular heap tables with their own indexes — they don't auto-update when underlying data changes.

6. Visual Explanation

The diagram shows a parent orders table partitioned by month, with queries using partition pruning to access only relevant partitions, plus a materialized view caching aggregated dashboard data.

7. Practical Example

8. Common Mistakes

• Forgetting to refresh materialized views: Data becomes stale. Schedule refreshes via pg_cron or application schedulers.
• Querying partitioned table without the partition key in WHERE: The planner can't prune and scans all partitions — same as an unpartitioned table.
• Missing indexes on partitions: Indexes on the parent don't auto-create on partitions. Create indexes on each partition or use CREATE INDEX ON parent(...) which propagates.
• Over-partitioning: Too many partitions (thousands) slow down the planner due to partition metadata overhead.

9. Quick Quiz

10. Scenario-Based Challenge

You have a 2 TB events table queried mostly by date ranges. Design a partitioning scheme (monthly partitions), create a materialized view for daily active user counts, set up a pg_cron job to refresh the view every hour, and write a migration to add a new future partition automatically.

11. Debugging Exercise

12. Interview Questions

• Q: What's the difference between a VIEW and a MATERIALIZED VIEW?
  A: A VIEW is a stored query that executes on every access (always fresh, slower). A MATERIALIZED VIEW stores the query result on disk (fast reads, but stale until refreshed).
• Q: When would you choose hash partitioning over range?
  A: Hash partitioning is ideal when there's no natural range/list key and you need even data distribution (e.g., by user_id). Range is better for time-series data where queries filter by date ranges.

13. Production Considerations

• Partition maintenance: Create future partitions in advance via pg_cron. Detach old partitions with ALTER TABLE ... DETACH PARTITION for archival.
• REFRESH CONCURRENTLY: Always use this in production — non-concurrent refresh takes an ACCESS EXCLUSIVE lock.
• Foreign keys: PG 12+ supports FK references to partitioned tables. Earlier versions don't.
• Partition count limit: Keep partitions under ~1,000 for optimal planner performance. Use sub-partitioning if needed.