DATA SYSTEMS · INTERACTIVE LAB
Learn logical views, physical summaries, staleness and explicit refresh.
The useful question is not “which database wins?” It is “what work did the system avoid, defer, store or repeat?”
100K
1Mone deterministic sales model
START WITH ONE QUESTION
Does reusable SQL also mean reusable computation?
Recommended sequence
- Track A · Select 100,000 rows and run Raw aggregate. Record the result and observed latency as the baseline.
- Keep 100,000 rows selected. Run Logical VIEW, then Stored Materialized View. Ask which one reuses SQL and which one reuses computed rows.
- Run Insert without refresh. Compare the base table with the stored snapshot to observe staleness, then run Full REFRESH to make the snapshot current again.
- Track B · Select an IVM experiment. The interface switches to the separate 10,000-row pg_ivm dataset automatically; it does not append IVM to the earlier 100,000 rows.
- Run Read pg_ivm IMMV, then Insert + incremental maintenance. One new sale event is inserted and pg_ivm updates only its affected day–region–category aggregate group.
- Run IVM delta vs full REFRESH. Compare the work scope: one affected delta/group versus rereading the complete 10,000-row IVM base table.
Main PostgreSQL dataset: Raw → VIEW → Materialized View → staleness → full refresh
Separate pg_ivm dataset: start with 10,000 rows, then insert one new event and maintain only the affected aggregate group
What happens to the 100,000 rows? Nothing is added to or removed from that Track A dataset. Selecting IVM changes the active laboratory to its separate bounded dataset. The “+1” is a new sale inserted into the 10,000-row IVM base table, producing 10,001 base rows—not 100,001.
This lab uses a separate PostgreSQL 17 + pg_ivm 1.14 runtime. The MADlib PostgreSQL database remains unchanged.
The displayed value is observed lab latency, not a universal database benchmark. PostgreSQL reuses an open PDO connection; DuckDB starts a subprocess for each request; ClickHouse communicates over HTTP and reports its own engine elapsed time. At only 10K–1M rows, much of the dataset may also remain in cache. Compare query plans, rows and bytes read, freshness and maintenance cost before comparing milliseconds.
CONCEPT
Logical interface or physical answer?
A normal VIEW saves a query definition. It improves consistency and governance, but usually repeats the underlying computation. A materialized view stores rows produced by that query. The read becomes smaller; freshness becomes your responsibility.
BOUNDED EXPERIMENT
Whitelist only · 25 s timeout · 2 CPU / 2 GB ClickHouse ceiling · one operation at a time
SQL
Select a bounded experiment.Result
Explain / query plan
The physical plan appears after execution.Run an experiment, then connect the measurement to physical work.
View, materialization and the cost of being current
VIEW
Use it as a named data contract: centralize joins, calculations, masking and business meaning. It is excellent at preventing every team from rewriting the same logic differently. It is not automatically a cache.
Materialized View
Use it when the repeated transformation is expensive and a controlled freshness lag is acceptable. Reads become predictable because they touch summarized rows, but refresh consumes compute and may need orchestration.
pg_ivm · IMMV
The isolated PostgreSQL 17 runtime uses pg_ivm 1.14. Its IMMV is updated inside the same transaction as changes to the base table. The lab contrasts this write-time delta maintenance with a full snapshot REFRESH at 1,000 and 10,000 rows.