Plain-language companion: v0.35.0.md

v0.35.0 — Temporal IVM & Columnar Materialization

Status: Planned. Derived from plans/PLAN_OVERALL_ASSESSMENT_3.md §10.2, §10.7.

Release Theme v0.35.0 unlocks two analytic workload patterns that the current streaming engine cannot serve. Temporal IVM lets a stream table maintain a rolling history of how its rows have changed over time — providing first-class SCD-Type-2 semantics without external ETL or separate audit tables. Columnar materialization lets a stream table store its result set in Citus columnar storage (or pg_mooncake), dramatically reducing storage footprint and query I/O for analytic consumers that scan the materialised result set but never write to it. Together they close the OLTP→OLAP bridging gap and make pg_trickle viable as the materialization layer for dashboards and reporting queries.

Correctness

ID Title Effort Priority
CORR-1 Temporal IVM: two-dimensional frontier (LSN, timestamp) L P0
CORR-2 Columnar: verify differential MERGE compatibility with columnar storage M P0

CORR-1 — Temporal IVM requires extending the frontier model from a one-dimensional LSN cursor to a two-dimensional (frontier_lsn, valid_from_ts) pair. Each row carries a __pgt_valid_from TIMESTAMPTZ and an optional __pgt_valid_to TIMESTAMPTZ. Rows are never physically deleted; instead a “close” delta sets valid_to. Queries against the stream table with AS OF TIMESTAMP $1 resolve to the materialised row version valid at that timestamp. Schema change: Yes — new temporal_mode BOOLEAN column on pgtrickle.pgt_stream_tables; new __pgt_valid_from/__pgt_valid_to columns auto-added to the storage table when temporal_mode = true.

CORR-2 — Citus columnar and pg_mooncake use append-only storage models. Verify that the differential MERGE (MERGE INTO storage USING delta) is compatible with append-only semantics (likely requires DELETE + INSERT merge strategy for columnar targets). Add a storage_backend column to pgtrickle.pgt_stream_tables and route merge codegen accordingly.

Ease of Use

ID Title Effort Priority
UX-1 create_stream_table(…, temporal := true) parameter M P0
UX-2 AS OF TIMESTAMP query rewrite in DVM parser L P1
UX-3 create_stream_table(…, storage_backend := 'columnar') parameter M P1
UX-4 Automatic delete_insert strategy for columnar backends S P1
UX-5 Documentation: temporal IVM tutorial + SCD-Type-2 worked example M P1
UX-6 Documentation: columnar backend setup guide (Citus + pg_mooncake) S P1

Test Coverage

ID Title Effort Priority
TEST-1 Integration: temporal stream table AS OF TIMESTAMP round-trip L P0
TEST-2 Integration: SCD-Type-2 dimension pattern end-to-end M P1
TEST-3 Integration: columnar stream table MERGE parity with heap M P0
TEST-4 Integration: temporal + columnar combined (temporal columnar ST) M P2

Conflicts & Risks

  • CORR-1 requires a non-trivial DVM engine extension (two-dimensional frontier). Spike before committing to the milestone; do not commit until the spike proves the approach is sound.
  • CORR-2 depends on the Citus columnar or pg_mooncake extension being present; CI must add a Testcontainers image with one of these available. Gate columnar support behind pg_trickle.columnar_backend GUC (default none); the CI matrix should test at least one columnar provider.
  • The combination of temporal mode and columnar storage is a P2 stretch goal; do not block the release on it.

Exit Criteria

  • [ ] CORR-1/UX-1: create_stream_table(…, temporal := true) creates storage table with __pgt_valid_from/__pgt_valid_to; rows never physically deleted
  • [ ] UX-2: AS OF TIMESTAMP $1 query rewrites resolve against the materialised history
  • [ ] TEST-1: Temporal round-trip test passes: insert → update → delete, query at t₀/t₁/t₂ returns correct historical rows
  • [ ] TEST-2: SCD-Type-2 dimension pattern: slowly-changing customer table materialised with full history, current-version query using WHERE valid_to IS NULL
  • [ ] CORR-2/UX-3: Columnar stream table creates; delete_insert strategy used automatically; columnar MERGE E2E parity test passes
  • [ ] Extension upgrade path tested (0.32.0 → 0.33.0)
  • [ ] just check-version-sync passes