Materialized Views in DuckDB
DuckDB is a fast, embeddable OLAP database, but it had no native materialized view support — meaning every repeated analytical query recomputed from scratch. I extended its parser, planner, and execution engine to add CREATE/REFRESH MATERIALIZED VIEW, bringing it closer to parity with PostgreSQL and Snowflake.
Architecture
Materialized views inside DuckDB
- 01
Parse
CREATE / REFRESH MATERIALIZED VIEW grammar
- 02
Track
MatView objects record query and base tables
- 03
Rewrite
Planner prefers a matching view over recomputing
- 04
Store
Native columnar table, indexed and compressed
- 05
Invalidate
Locking and dependencies flag stale views
Key decisions
DECISION 01
Store views as ordinary physical columnar tables
They inherit DuckDB's indexing and compression with no separate storage path.
→ Reuses existing engine features for free
DECISION 02
Persist results at creation instead of plain views
Repeated analytical queries were recomputing joins and aggregations.
→ Retrieval up to 100x faster for ~25% more storage
DECISION 03
Exclude recursive queries and non-deterministic functions
Random or time-dependent results cannot be meaningfully cached.
→ Cached results stay consistent
Implementation
Parser: extended the SQL grammar to recognize CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW, with syntax validation and descriptive error handling for cases like refreshing a view that no longer exists.
Logical planner: introduced MatView objects that track the defining query, base-table dependencies, storage format, and indexing metadata. The planner automatically invalidates a view when its base tables are dropped or modified, and prefers an existing materialized view over recomputation whenever a query matches it.
Execution engine: materialized views are stored as physical tables in DuckDB's native columnar format, so they inherit its existing indexing and compression rather than needing a separate storage path.
Concurrency & Consistency
Added locking and transaction management so a view can be refreshed and queried concurrently without exposing inconsistent state, plus dependency tracking that flags a view stale the moment its base table changes.
Benchmarks
Tested against TPC-H queries Q1–Q5 (revenue aggregation, quantity-per-part, shipping-priority joins — representative OLAP workloads with heavy joins and aggregation).
Creating a materialized view costs more upfront than a standard view (~0.3s vs ~0.01s) since it actually persists results — but that's a one-time cost. Retrieval is where it pays off: query time dropped from ~0.3s to ~0.003–0.004s, up to 100x faster, at roughly 25% additional storage.
Validated the design scales to datasets up to 2TB with stable performance and safe concurrent access under load.
Limitations
Refresh is manual only — no incremental updates yet, so real-time-changing base tables mean the view can drift stale between refreshes.
Advanced SQL constructs like recursive queries or non-deterministic functions (random values, time-dependent results) aren't supported, since their results can't be meaningfully cached.
Group project at USC with 2 teammates (Minho Jang, Prachiti Bapat).