# v0.34.0 — Temporal IVM & Columnar Materialization

> **Full technical details:** [v0.34.0.md-full.md](v0.34.0.md-full.md)

**Status: Planned** | **Scope: Medium**

> Two new analytic workload patterns: time-travel queries over a stream
> table's full history, and columnar storage for dramatically faster
> analytics.

---

## What is this?

v0.34.0 opens two new classes of analytic workloads that pg_trickle cannot
currently serve.

---

## Temporal IVM — time-travel queries

Normally, a stream table shows the *current* state of the world. Temporal
mode changes this: the stream table maintains a full history of how every
row has changed over time. Rows are never physically deleted; instead, each
row carries a `valid_from` timestamp and an optional `valid_to` timestamp
that records when a version was replaced.

This enables queries like "what did this table look like at 3 PM on Tuesday?"
without any external audit log infrastructure. The pattern is known as
**SCD Type 2** (Slowly Changing Dimension Type 2) in data warehousing, and
it is used for:

- Customer history ("what address was on file when this order shipped?")
- Regulatory audit trails ("what were the account balances at quarter-end?")
- Slowly-changing dimension tables in analytics pipelines

Creating a temporal stream table is a single parameter:

```sql
SELECT pgtrickle.create_stream_table(
    'customer_history',
    query := 'SELECT id, name, address FROM customers',
    temporal := true
);
```

Queries against the stream table with `AS OF TIMESTAMP $1` automatically
resolve against the historical row versions.

---

## Columnar materialization

Stream tables currently store their results in standard PostgreSQL heap
storage — optimised for row-by-row reads and writes. Analytic queries that
scan millions of rows to compute aggregates are better served by *columnar*
storage, where all values for a single column are stored together on disk.
This dramatically reduces I/O for aggregate queries (summing a column, for
example, only reads that column, not the entire row).

The `storage_backend := 'columnar'` parameter to `create_stream_table()`
tells pg_trickle to store the materialised result in Citus columnar storage
or pg_mooncake. The differential refresh machinery continues to work —
pg_trickle automatically routes the MERGE to use the `delete_insert` strategy
that columnar storage requires, with no manual configuration.

The result: analytic dashboards and reporting queries that consume the
materialised stream table see dramatically lower I/O, smaller storage
footprint, and faster aggregate performance.

---

## Combined use

Temporal mode and columnar storage can be combined: a slowly-changing
dimension table stored in columnar format with full history, queryable at
any point in time. This is listed as a stretch goal and is not a hard
requirement for the release.

---

## Scope

v0.34.0 is a medium-sized release. The temporal IVM work requires extending
the core frontier model — the internal mechanism that tracks which changes
have been processed — from a single LSN cursor to a two-dimensional
`(LSN, timestamp)` pair. A design spike in v0.33.0 is a prerequisite before
committing this feature to the milestone.

---

*Previous: [v0.33.0 — Reactive Subscriptions & Zero-Downtime Operations](v0.33.0.md)*