dim_time
PK time_keydate · month · quarter1DATA SYSTEMS · INTERACTIVE LAB
See, run and compare the physical work behind PostgreSQL, DuckDB and ClickHouse.
The useful question is not “which database wins?” It is “what work did the system avoid, defer, store or repeat?”
Checking isolated teaching runtimes…
ROWS10K
100K
1Mone deterministic sales model
100K
1Mone deterministic sales model
34-SLIDE TEACHING DECK
From Views to Incremental Analytics
Begin with the conceptual journey before running the laboratories: VIEW, materialization, row versus column storage, incremental view maintenance, and the physical choices behind modern OLAP.
VIEWMaterialized ViewRow vs ColumnIVM · ΔThree Labs
CONCEPTUAL ER DIAGRAM
The business model behind the flat teaching table
Each dimension describes many sale events. The laboratories intentionally embed these attributes in sales so every engine receives one portable physical contract.
dim_product
PK product_keycategory · product1dim_customer
PK customer_idcustomer attributes1dim_geography
PK geography_keyregion · province1dim_channel
PK channel_keychannel1fact_sales
PK sale_idFK time · product · customerFK geography · channelquantity · unit_price · discountrevenue · cost · profitN
Conceptual relationship · 1:N
Physical lab · one denormalized sales table
ONE QUESTION · THREE COMPUTATION PLACEMENTS
Choose the boundary before choosing the product.
PostgreSQLrows + reusable SQL + stored snapshot
↔DuckDBembedded process + local files
↔ClickHousecolumnar parts + incremental states
THREE LABS · THREE DIFFERENT NEEDS
Begin with the work you need the data system to perform.
01 · TRANSACTIONAL FOUNDATION
PostgreSQL
We want one governed definition that applications and teams can trust.
- Input
- Mutable rows, constraints and operational transactions.
- Learn
- VIEW → Materialized View → staleness → REFRESH
- Outcome
- A decision rule for logical reuse versus stored results.
DuckDB
We want analytical SQL on local files without operating another server.
- Input
- CSV, Parquet and a bounded local process.
- Learn
- pushdown → window → rollup → precompute
- Outcome
- The boundary between file format, scan cost and reusable local results.
ClickHouse
We want repeated aggregations to remain fast as event volume grows.
- Input
- Append-oriented events and known analytical access patterns.
- Learn
- MergeTree → column pruning → skipping → incremental MV
- Outcome
- Physical-design judgement for order, columns and incremental states.
COMMON DATA CONTRACT
One sales event, one grain, one meaning—implemented in three engines
A fair comparison needs more than similar column names. Every engine receives the same row meaning, deterministic values, measures and analytical question. Storage types change only where an engine requires its native equivalent.
FACT TABLEsalesone row = one completed sale event
PRIMARY IDENTIFIERsale_idunique event; PostgreSQL enforces the PK
EVENT TIMEsale_timestampbusiness time used for daily rollups
DATA VOLUME10K · 100K · 1Mthe same rules at each bounded scale
Identity & time
sale_id · BIGINT / UInt64sale_timestamp · TIMESTAMP / DateTimeDescriptive dimensions
region · provincecategory · productchannel · customer_idAtomic inputs
quantity · unit_price · discountDerived measures
revenue · cost · profitStable semantics
Region, category and channel mean the same thing in every lab; performance cannot be “won” by silently changing the question.
Comparable workload
The baseline groups sales by region and category and computes order count and revenue. Engine-specific experiments then reveal their strengths.
Controlled synthetic data
No student or production record is used. Deterministic formulas make every reset reproducible.
This is intentionally a compact teaching schema, not a full enterprise model. In production, customer, product and geography commonly become governed dimensions with explicit history, quality and privacy rules.
MASTER COMPARISON
What each design makes cheap—and what it asks you to maintain
| System | Computation location | Physical reuse | Freshness model | Best teaching question |
|---|---|---|---|---|
| PostgreSQL | database server | materialized rows | explicit refresh | logic vs stored result |
| DuckDB | web process | Parquet + local table | file/table replacement | serverless analytical scan |
| ClickHouse | isolated OLAP server | sorted column parts + aggregate states | new inserted blocks | layout and incremental work |