> **Plain-language companion:** [v0.34.0.md](v0.34.0.md)

## v0.34.0 — Temporal IVM & Columnar Materialization

**Status: Planned.** Derived from [plans/PLAN_OVERALL_ASSESSMENT_3.md](plans/PLAN_OVERALL_ASSESSMENT_3.md) §10.2, §10.7.

> **Release Theme**
> v0.34.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 in v0.33.0 first; do not commit to the milestone 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

---