The arc of this lecture: from a stored query, to a stored result, to a physically reorganized store, to a store that only ever processes what changed.
01
Part 1 — Why View?
Why Does the Same SQL Become Fast or Slow?
sales dataset — used throughout this lecture
SELECT region, SUM(revenue)
FROM sales
GROUP BY region;
A VIEW only names a query — it does not itself choose a physical strategy. The four rows below are a separate decision, made whether or not a view sits in front of the SQL.
The question stays the same. What changes is the physical strategy underneath.
What is stored?
VIEW = Stored Query, Not Stored Result
CREATE VIEW sales_by_region AS
SELECT
region,
SUM(revenue) AS revenue
FROM sales
GROUP BY region;
VIEW stores the query definition, not the result.
Base data changes → the view reflects the current value.
The definition is folded into query planning every time the view is read — no result of the view's own is ever stored.
A view is a name attached to a query. Reading it re-runs the query against the base table — nothing is cached along the way.
What is a view for?
VIEW is an Abstraction Boundary
Complex SQL
A multi-join, multi-filter query collapses into one reusable name.
Security
Expose only selected columns or rows; hide the rest of the base table.
Logical model
Present a clean schema while the physical tables stay whatever they need to be.
Stable interface
Change the implementation underneath without changing what callers write.
VIEW is not primarily a performance mechanism. It is a logical abstraction mechanism.
The trap
Abstraction ≠ Precomputation
SELECT region, SUM(revenue)
FROM sales
GROUP BY region;
SELECT *
FROM sales_summary_view;
The view makes the query shorter to write. It does not make the aggregation cheaper to run — the execution plan underneath is the same scan and aggregate.
Nuance
The planner can still rewrite what happens inside that box — reorder joins, push a filter down, pick a different access path. That is query optimization, not precomputation: it changes how the scan-and-aggregate runs, not whether it runs. A view earns no exemption from that cost.
02
Part 2 — From View to OLAP
Repeated Analytics Changes the Trade-off
Rows
100,000,000
Consumer
dashboard query
Query
same aggregation
Frequency
every few seconds
Every refresh of the dashboard triggers the full 100M-row scan again — even though the aggregate rarely differs much between refreshes.
Why recompute the same aggregate again and again?
Before the mechanisms — what is OLAP asking for?
OLAP Is a Workload, Not a Materialized View
Dimension
A "by what" to group or filter on — region, product, time.
Measure
The "what" being summarized — revenue, count, quantity.
Grain
What one row represents — an order line, a daily total, a monthly total.
Slice / dice / drill-down / roll-up
The motions an analyst performs over dimensions and grain: filter to one slice, pivot to another cut, zoom into finer detail, zoom out to a coarser summary.
OLAP names a workload — read-heavy, dimensional, summarizing over many rows — not a storage format or a precomputation technique.
Distinction
Materialized views and columnar storage — the two mechanisms this lecture spends the most time on — are ways to make OLAP workloads fast. Neither one is OLAP, and OLAP does not require either: you can run a genuinely OLAP-shaped query directly against a plain row-oriented table, slowly. The rest of this lecture is about the "slowly" part.
CREATE MATERIALIZED VIEW sales_by_region_mv AS
SELECT
region,
SUM(revenue) AS revenue
FROM sales
GROUP BY region;
Compute now, or compute before?
Compute Now or Compute Before?
View
Materialized view
Stores query
Yes
Yes
Stores result
No
Yes
Freshness
Current
Can be stale
Query cost
Higher
Lower
Storage cost
Low
Higher
Refresh required
No
Yes
Every materialized view is a trade of storage and freshness for query speed — never free.
The idea behind OLAP
OLAP Often Trades Redundancy for Query Speed
raw transactions
Region Product Amount
North A 100
North B 200
South A 150
Redundancy can be intentional. OLAP is not sloppy design — it is a deliberate trade.
Distinction
OLAP does not mean materializing everything. Choosing which grains to store, and which to compute on demand, is itself the design problem — and it is separate from whether those grains sit in a materialized view. SQL's CUBE, ROLLUP, and GROUPING SETS compute several of these grains in one pass, on demand — Lab 2 revisits this.
The new problem
Fast Reads Create a New Problem: Maintenance
REFRESH MATERIALIZED VIEW sales_by_region_mv;
Before refresh, the view shows an outdated aggregate. After refresh, it is correct again — but every refresh has its own cost.
The full-refresh problem
Why Recompute N Rows for One Changed Row?
N
100,000,000 rows
Δ
1 changed row
Full refresh: scan 100,000,000 rows again — because of one changed row.
Can we update only what changed?
03
Part 3 — Row Store vs Column Store
Logical Table ≠ Physical Layout
logical schema
ID | Region | Product | Revenue | Cost
Row store's job
Row Store: Fetch and Modify Whole Records
OLTP workloads
Point lookup by key
INSERT / UPDATE of a single record
Transactions touching one entity
Retrieving most columns of a small number of rows
Nuance
"Targeted access" is doing real work: an index probe, a heap-page read, an MVCC visibility check, possibly a buffer-cache miss. Row store wins because that work stays proportional to the rows requested — not because it collapses to a single disk seek.
Column store's job
Column Store: Scan Few Columns Across Many Rows
SELECT region, SUM(revenue)
FROM sales
GROUP BY region;
Analytics often touches many rows but few columns.
More than one trick
Columnar Speed Comes From More Than One Trick
Column pruning
Read only the columns the query actually references.
Compression
Values within a column are often similar and compress well.
Vectorized execution
Operators pass batches of values between them, cutting per-row interpretation overhead and opening the door to cache- and SIMD-friendly code.
Data skipping
Skip whole blocks known not to match the predicate.
Scope
Vectorization is not exclusive to column stores — it is a query-execution technique. It happens to pair naturally with columnar layout, since a column is already a contiguous batch of same-typed values ready to hand to a vectorized operator.
A workload decision
There is No Universally Better Layout
typical tendency, not a guarantee — a specific engine can narrow or erase a gap
Row store
Column store
Point lookup
Strong fit
Engine-dependent
Frequent UPDATE
Strong fit
Usually costly
Full row access
Strong fit
Engine-dependent
Large aggregation
Usually costly
Strong fit
Compression
Engine-dependent
Strong fit
Analytical scan
Usually costly
Strong fit
Physical design follows workload.
Distinction
Columnar storage and materialized views solve different problems: one is about how bytes are laid out on disk; the other is about whether a result is computed now or in advance. Either can exist without the other.
Caveat
These are tendencies of the layout, not laws of physics. A column store with a well-chosen sort/order key can serve point lookups reasonably; a row store that fits in memory, or is well-indexed, can serve modest aggregations. The table is a starting intuition, not a benchmark result.
04
Part 4 — IVM
IVM = Maintain Δ, Not Recompute Everything
IVM keeps the stored result correct by processing only the change, not by re-deriving the whole result from the changed base tables.
Distinction
IVM is not just a cache. A cache reuses a previously computed answer and needs its own validity policy — invalidation, a TTL, a version check. IVM instead derives the exact change to the maintained state directly from the change in the base data, so the result stays correct by construction, not by policy.
"O(Δ)" is the aspiration, not a guarantee. What actually gets touched is Δ plus whatever state is needed to derive the new result from it — for SUM/COUNT that state is tiny, but a join delta must still probe the other side of the join (Slide 21), and some aggregates — MIN/MAX under deletion, DISTINCT, percentiles (Slide 19) — can cost far more than |Δ| without extra bookkeeping. Query structure, indexes, and grouping all affect where a given query really falls between O(Δ) and O(N).
Which aggregates are easy?
Some Aggregates Are Naturally Incremental — Others Are Not
easy: update from a fixed, small piece of extra state
SUM
old total = 10,000
new row = +50
-----------------
new total = 10,050
COUNT
INSERT → +1
DELETE → -1
AVG
Cannot be maintained safely from AVG alone.
store: SUM + COUNT
AVG = SUM / COUNT
hard: the shortcut breaks, especially on DELETE
MIN / MAX
INSERT is a cheap compare. DELETE of the current min/max forces a search for the next one — unless a heap or multiset of candidates was kept on the side.
COUNT DISTINCT
Needs to know whether the deleted value still occurs elsewhere. Exact maintenance means tracking per-value counts, not just one number.
MEDIAN / PERCENTILE
Not decomposable from a fixed-size summary at all. Practical systems keep a sketch (e.g. t-digest) and accept an approximate answer.
The lesson generalizes: what must be stored to maintain an aggregate incrementally is not always the aggregate's own output — and for some aggregates, no fixed-size state suffices at all.
A useful reframing
Think in Deltas: UPDATE is DELETE + INSERT
amount: 100 → 130
Joins are where it gets interesting
Incremental JOIN: Join the Delta, Not Everything
Only the new or changed rows in orders are joined against customers. The result is a delta to the join output, which propagates onward — not a full re-join.
general case — either side may change
Δ(R ⋈ S) = (ΔR ⋈ S) ∪ (R ⋈ ΔS) ∪ (ΔR ⋈ ΔS)
Assumption
This picture only shows the ΔR ⋈ S term, for a delta on the orders side. In general both sides can change, and every term above carries signed rows (insertions and deletions, per Slide 17) — deletions on one side must cancel matching rows out of the maintained result, not just add negative-looking rows to it. The join key also needs an index; without one, even "join the delta" degenerates into a scan of customers.
Beyond speed
Incremental Processing is About More Than Speed
Latency
Results reflect new data within a shorter window.
Freshness
Stored state stays close to the true current state.
Compute cost
Work scales with change volume, not table size.
Energy / resources
Less redundant scanning means less wasted computation.
If changes are small compared with total state, recomputation wastes work.
05
Part 5 — Three Labs
Three Labs, One Question
Where should computation happen, and when?
PostgreSQL
Materialize the result — a general-purpose client/server DBMS extended with VIEW → MATERIALIZED VIEW.
DuckDB
Compute close to the files — an embedded, in-process analytical engine.
ClickHouse
Organize storage columnarly and incrementally aggregate analytical data on write.
Before you run the labs
If You Only Measure Query Latency, Materialized Views Always Win
hold constant across all three engines
Same row count and same sales schema
Same query, written as equivalently as each engine allows
Warm-cache and cold-cache runs, measured separately
Several repetitions per condition — report median and p95, not one run
measure, not just query latency
Query latency
Bytes read
Storage size on disk
Refresh cost / insert cost
Freshness lag after a source change
Correctness after INSERT, UPDATE, and DELETE
A materialized view or an incremental MV looks unbeatable if the bill for refresh, insert, and storage never shows up in the same chart as query latency.
Lab 1 · general-purpose DBMS
Lab 1 — PostgreSQL: From VIEW to Materialization
experiments
Raw aggregation
CREATE VIEW
EXPLAIN ANALYZE
CREATE MATERIALIZED VIEW
Compare query latency
UPDATE / INSERT the source
Observe the stale MV
REFRESH, then compare cost
Optional: pg_ivm extension
What can go wrong
Plain REFRESH MATERIALIZED VIEW takes an exclusive lock — readers are blocked for the duration. REFRESH ... CONCURRENTLY keeps the view readable during refresh, but requires a UNIQUE index on the view first, and it is still a full recompute-and-swap under the hood — CONCURRENTLY means non-blocking, not incremental. pg_ivm is the extension that gets closer to true incremental refresh, but only for a supported subset of SQL — check its documentation against the query before relying on it.
Lab 2 · embedded analytical engine
Lab 2 — DuckDB: Compute Near the Data
experiments
Direct Parquet query
CSV vs Parquet
Projection pushdown
Predicate pushdown
Window analytics
CUBE / ROLLUP
Precomputed table vs compute-on-demand
How much data actually needs to move or be read?
Scope
DuckDB is an embedded, single-process analytical engine — it runs inside your application, not as a shared server many clients connect to. That is precisely why it can skip network round trips, but it is also why it is not a drop-in replacement for a multi-user server like PostgreSQL or ClickHouse in production.
ClickHouse's incremental materialized view fires as a trigger on INSERT into the source table. It is not a general relational IVM: it does not automatically re-derive correctness under arbitrary UPDATE/DELETE the way a true incremental-maintenance engine does.
Traps
The MV only sees rows inserted after it was created — existing data needs an explicit backfill. UPDATE/DELETE on the source do not retroactively touch the target: the aggregate silently drifts unless the pipeline stays insert-only by design. And a dimension table joined inside the MV's SELECT is read once, at insert time — a later change to that dimension table does not reprocess earlier target rows.
Engine detail
The SELECT runs once per inserted block, so the target table's engine matters: SummingMergeTree or AggregatingMergeTree (with -State/-Merge combinators) folds each block's partial result correctly; a plain MergeTree just accumulates rows that still need re-aggregating on read. ClickHouse also has a separate refreshable materialized view — re-runs its query on a schedule, not on insert. This lab uses the incremental kind.
Bringing it together
Same SQL Idea. Different Physical Execution.
SELECT region, category, SUM(revenue)
FROM sales
GROUP BY region, category;
The two one-box lanes (PostgreSQL MV, ClickHouse MV) look cheapest here because their aggregate step already happened — at the last REFRESH, or at the last INSERT. That cost was not eliminated; it moved off the read path onto the write/refresh path, which is exactly the trade this lecture has been building toward.
The core trade-off
There Is No Free Performance
Every point inside the triangle is a real system design. Move toward a corner and you gain that corner's property at the other two corners' expense — nothing sits at the centroid for free.
Performance is often the art of deciding what not to recompute.
Complex query, data must stay fresh query ซับซ้อนแต่ข้อมูลต้องสด
VIEW + indexes
Query repeats constantly, staleness is acceptable query ซ้ำมาก ยอม stale ได้
Materialized view
Scans many rows but touches few columns scan หลายแถวแต่ใช้ไม่กี่ columns
Columnar storage
Δ is small and must stay fresh Δ เล็กและต้องสด
IVM / incremental aggregation
Δ is nearly as large as N Δ ใหญ่เกือบเท่า N
batch refresh is likely the better fit
Heavy UPDATE / DELETE traffic UPDATE/DELETE มาก
be cautious with ClickHouse incremental MV
Caveat
This table is a starting intuition, not a rulebook — it collapses the eight questions from the previous slide and the storage/compute/freshness triangle from two slides back into quick pattern-matches. Run the fair-comparison protocol from Part 5 before trusting it for a real system.
Closing
Four Independent Questions, Not One Pipeline
Correction
Nothing in this lecture is a required sequence. These four mechanisms are separate axes you combine to fit a workload: a PostgreSQL materialized view can be stored row-oriented; a ClickHouse columnar table needs no materialized view at all; a plain VIEW can sit on top of a columnar table; IVM does not require columnar storage underneath it. Any subset, in any combination — not VIEW → MV → columnar → IVM in order.
Mechanism
Question it answers
คำถามที่ตอบ
VIEW
How do you hide and reuse query logic?
จะซ่อนและ reuse logic อย่างไร
Materialization
When do you compute — now, or in advance?
จะคำนวณเมื่อไร
Row / column layout
How do you lay out the bytes on disk?
จะจัดเก็บ bytes อย่างไร
IVM
When data changes, how much do you recompute?
เมื่อข้อมูลเปลี่ยน จะคำนวณใหม่แค่ไหน
Modern OLAP is not the sum of these four in sequence — it is picking a point on each axis for your workload, and revisiting that choice as the workload changes.
Data systems are not only about storing data. They are about organizing computation around data.
This deck simplifies for teaching. Where a slide states a specific mechanism or default behavior, check it against the current documentation for the version you are running — these systems change.