v0.36.0 — Temporal IVM & Columnar Materialization

Full technical details: v0.36.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.36.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:

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.36.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 before committing this feature to the milestone is recommended.

Previous: v0.35.0 — Reactive Subscriptions & Zero-Downtime Operations