Under the Hood

KoldStore keeps PostgreSQL authoritative for the hot tier and stores flushed history as open Parquet plus catalog metadata. This page explains the moving parts so API behavior makes sense.

End-to-end path

TEXT

1heap commit2  → logical slot (PK-only pgoutput)3  → database worker4  → koldstore.<schema>_<table>__cl  (latest-state mirror)5  → flush (policy or force)6  → Parquet segments + manifest7  → supported SELECT via KoldMergeScan (hot + cold winner resolution)

There is no trigger/strict capture path today. Foreground DML writes the heap only; the serialized WAL applier allocates authoritative seq values.

Hot tier

Ordinary heap table with a primary key
Application columns only (clean schema — no KoldStore columns on the user table)
Native indexes cover hot rows only after flush
Heap transactions, locking, MVCC, indexes, RLS, and permissions stay PostgreSQL-native for hot rows. Cold rows are custom-scan tuples and do not inherit heap row locks, system columns, SSI predicate locks, or native constraints.

Change-log mirror (`koldstore.<schema>_<table>__cl`)

One row per primary key (latest state), not an append-only event log:

Column	Meaning
PK columns	Same as the heap
`seq`	Snowflake effect id — ordering / flush cutoffs / change cursor
`op`	`1` = INSERT, `2` = UPDATE, `3` = DELETE

Tombstones (op = 3) remain until flush prunes them. seq is not a commit LSN and is not a commit-order guarantee beyond “allocated by the serialized applier.”

WAL capture consistency

Mode	Behavior
Normal	Worker polls (~100 ms) and applies available WAL
Committed-change fence	`koldstore.wait_for_async_mirror()` drains committed WAL to a captured boundary
Flush	Calls the same fence automatically

Lag is expected. The fence cannot see the caller’s uncommitted changes. Call it before comparing heap state to mirror/seq cursors and, for a fixed REPEATABLE READ or SERIALIZABLE snapshot, before opening the transaction.

Other capture rules:

Primary-key UPDATEs are rejected (BEFORE UPDATE OF pk guard)
TRUNCATE on async managed tables is rejected
Publication koldstore_async_mirror + one deterministic logical slot per database are auto-managed

Cold tier

Parquet segments under a registered storage backend
Manifest metadata in PostgreSQL catalogs (koldstore.manifest, koldstore.cold_segments, …)
Object storage is not WAL-protected — back up PostgreSQL and the cold prefix together

Default path templates:

Shared: {namespace}/{tableName}/
User-scoped (intended): {namespace}/{tableName}/{scopeId}/

Scoped cold folders and full per-user_id physical layout are still hardening; session koldstore.user_id is required for user-typed tables today.

Flush policy (intuition)

Example: hot_row_limit = 10000, min_flush_rows = 1000, max_rows_per_file = 1000

Mirror rows	Result
10,505	No flush (505 excess < min)
11,000	Flush 1,000 → 1 file
11,250	Flush 1,000, keep 10,250 hot

Age policy uses koldstore_move_after (for example '90 days') measured from mirror seq. It is mutually exclusive with hot_row_limit. koldstore_move_when is reserved and fails closed.

KoldMergeScan

TEXT

1KoldMergeScan2├── native hot child (Index / Bitmap / Seq Scan)3├── KoldParquetScan4└── MirrorOverlay (inserts / updates / tombstones)

Contracts that matter for operators:

Cold-capable predicates → KoldMergeScan with hot child + planned cold access
Hot-only / cold-proven-empty PK lookups may keep a native plan with no wrapper (intentional performance path)
koldstore.enable_merge_scan = off → ERROR at execution (never silent hot-only)
Unavailable cold storage → query fails (no partial hot-only results)

Catalog touchpoints

Useful relations/functions while debugging:

koldstore.schemas — managed table options
koldstore.jobs — migrate / flush progress
koldstore.table_status(...) — hot/mirror/cold counts, sizes, pending jobs
koldstore.async_mirror_status() — slot lag, retained bytes, health

Why a Mirror? — why __cl exists alongside WAL
Manage & Flush — how to drive policy and jobs
Change Feed — exclusive seq catch-up
SQL API Reference — full function list