ColdFront - Tiered operating mode

Tiered mode keeps recent rows in the PostgreSQL heap and archives older rows to Apache Iceberg, presenting both as one table through a UNION ALL view. An archiver moves rows hot→cold on a cron.

This document covers the tiered-specific design. The shared mechanics - pg_duckdb Iceberg I/O, the rewrite hook, the bakery protocol, the registry, DDL handling, infrastructure - live in architecture.md; the all-Iceberg alternative is architecture_decoupled.md.

Contents

This document is organized into the following sections:

• Data flow
• Archiver Workflow
• Transparent INSERT
• Transparent UPDATE/DELETE
• Write modes: strict vs permissive
• Tiered tables in a Spock mesh
• Partition Scheme Compatibility
• Tiered-specific limitations

Data flow

The following diagram shows how data moves between PostgreSQL, the Iceberg catalog, the object store, and the archiver:

┌──────────────────────────────────────────────────────────┐
│  PostgreSQL 16/17/18 + pg_duckdb + coldfront extensions   │
│                                                           │
│  _events (renamed partitioned table, hot data)            │
│  ├── p_2026_03  (hot, native heap)                        │
│  ├── p_2026_04  (hot, native heap)                        │
│  └── ...                                                  │
│                                                           │
│  events VIEW (replaces original table — hot + cold)       │
│  + INSTEAD OF INSERT trigger (fallback when hook isn't    │
│                                loaded; bypassed otherwise)│
│  + archive_watermark table (cutoff boundary)              │
│  + coldfront.tiered_views (catalog of rewrite targets)    │
│                                                           │
│  coldfront extension: post_parse_analyze_hook             │
│  ├── INSERT: splits hot/cold by partition_col vs cutoff;  │
│  │     hot side is plain set-based PG INSERT into _events,│
│  │     cold side is one duckdb.raw_query (or plpgsql      │
│  │     cursor loop when an IDENTITY column is omitted)    │
│  ├── UPDATE/DELETE: classifies WHERE against the watermark│
│  │     and rewrites to target one tier or both            │
│  └── errors on ambiguous predicates in strict mode        │
│                                                           │
│  pg_duckdb: DuckDB runs in-process inside PostgreSQL      │
│  ├── view reads cold data via iceberg_scan()              │
│  └── Archiver + coldfront write via duckdb.raw_query()    │
└──────────────┬───────────────────────────────────────────┘
               │
┌──────────────▼───────────────────────────────────────────┐
│  Lakekeeper (Rust binary, REST catalog on :8181)          │
│  Backed by its own dedicated PostgreSQL instance          │
│  Manages Iceberg metadata, snapshots, concurrency         │
└──────────────┬───────────────────────────────────────────┘
               │
┌──────────────▼───────────────────────────────────────────┐
│  S3-compatible object store (SeaweedFS, MinIO, GCS, etc.)   │
│  Stores Parquet data files + Iceberg metadata files       │
└──────────────────────────────────────────────────────────┘
               │
┌──────────────▼───────────────────────────────────────────┐
│  Archiver (Go binary, invoked by cron)                    │
│  Executes SQL against PG.                                 │
└──────────────────────────────────────────────────────────┘

Archiver Workflow

A single Go binary runs via cron. It converts an existing partitioned table into a tiered table on first run, then manages the ongoing lifecycle. The archiver is a thin SQL orchestrator - no DuckDB/Iceberg/Arrow Go libraries; all Iceberg I/O goes through pg_duckdb (see architecture.md → Core Mechanics).

Prerequisites

The archiver requires the following before its first run:

1. PostgreSQL 16+ with pg_duckdb, Lakekeeper bootstrapped with a warehouse
2. Persistent S3 secret configured (see architecture.md → Session setup)
3. An existing range-partitioned table

First run: conversion

The archiver auto-detects the partition column from pg_get_partkeydef() and column types from information_schema.columns.

A run that finds partitions past the hot window (older than hot_period) starts with an idempotent bootstrap, executed once before the per-partition loop: rename the source table, recreate the unified view with the current watermark as its cutoff, and register the view in coldfront.tiered_views:

ALTER TABLE events RENAME TO _events;

CREATE OR REPLACE VIEW events AS
  SELECT "id", "ts", "status", "data"::text FROM _events
  WHERE "ts" >= '2026-03-01'::timestamptz
  UNION ALL
  SELECT r['id']::bigint, r['ts']::timestamptz, r['status']::text, r['data']::text
  FROM iceberg_scan('ice.default.events') r
  WHERE r['ts'] < '2026-03-01'::timestamptz;

The rename is conditional, so it converts the table on the first run and no-ops afterwards; the CREATE OR REPLACE VIEW keeps the view's OID across runs. With the extension loaded, the C hook rewrites an INSERT on the view by the watermark cutoff, into a hot INSERT of the at/after-cutoff rows into _events and a cold duckdb.raw_query INSERT of the older rows. The view also carries an INSTEAD OF INSERT trigger that does the same routing; it is the fallback that fires only when the extension is not loaded.

The archive pipeline

Each partition past the hot window then moves to Iceberg through a six-phase pipeline:

0. Idempotent Iceberg-range wipe - deletes any Iceberg rows already in the partition's range (a previous cycle may have exported without cutting over), so the re-export cannot duplicate rows.

1. Install capture - installs a capture trigger and an UNLOGGED delta table on the partition, so writes that land during the export are recorded for replay.

2. Bulk export - exports the partition PG → Iceberg under a captured snapshot, using the temp table bridge (see architecture.md → Temp table bridge) and a single bakery-claimed Iceberg INSERT. On the very first export, creates the Iceberg namespace and table.

3. Delta replay - applies the delta rows the export snapshot did not see to Iceberg in batched commits, with no lock on the partition - concurrent writers keep going and keep adding to the delta.

4. Atomic cutover (cutover_archive) - a single transaction updates coldfront.archive_watermark to the partition's upper bound (derived from pg_catalog, not MAX(ts)), takes the bakery claim on the Iceberg table, takes ACCESS EXCLUSIVE on the parent and the partition under a 100 ms lock_timeout circuit breaker, re-issues the view DDL with the new cutoff, and detaches the partition with a plain transactional DETACH PARTITION - all of it commits atomically or rolls back whole. On a lock timeout, phases 3-4 are retried (10 attempts, exponential backoff from 100 ms to 51.2 s); any other error fails the cycle immediately.

5. Cleanup (cutover_cleanup) - drains stragglers that landed between phase 3's last commit and phase 4's lock, then drops the detached partition, the capture trigger, and the delta table.

Subsequent runs

Every run executes the same cycle - the conversion above is just the first cycle's bootstrap actually renaming the table. In order:

1. Create future partitions (default: 3) and self-heal the partition covering now
2. Run the bootstrap, then the six-phase pipeline for each partition past the hot window
3. Delete cold rows older than retention_period (when configured)

A run with nothing past the hot window and no retention_period set is a no-op.

Crash recovery

The watermark is the single source of truth, and phase 4 is the only step that changes it - the watermark, view, and DETACH commit together or not at all, so no intermediate state between them can exist. The following table shows how the archiver recovers from a crash at each point in the pipeline:

Transparent INSERT

The post_parse_analyze_hook (see architecture.md → Application Interface) intercepts INSERT on a registered tiered view and rewrites it into a single statement that splits the input by the partition-column watermark:

INSERT INTO events (ts, status, data) SELECT ts, status, data FROM staging;

-- Rewritten by the hook to (schematically):
WITH hot_ins AS MATERIALIZED (
  INSERT INTO _events (ts, status, data)
  SELECT ts, status, data FROM (<source>) AS s(ts, status, data)
  WHERE ts >= '<cutoff>'::timestamptz
  RETURNING 1
),
cold_call AS MATERIALIZED (
  SELECT duckdb.raw_query(
    'INSERT INTO ice.default.events
     SELECT id, ts, status, data FROM (<source-pglocal-prefixed>) ...
     WHERE ts < ''<cutoff>'''
  )
)
SELECT (SELECT count(*) FROM hot_ins) AS hot_rows,
       (SELECT count(*) FROM cold_call) AS cold_rows;

The following table describes the two cold-side write paths, when each applies, and its cost:

The hot half is always plain set-based INSERT INTO _events - IDENTITY auto-allocates server-side, full PG speed regardless of row count.

A watermark-split INSERT cannot use RETURNING - see Tiered-specific limitations #1.

Transparent UPDATE/DELETE

The hook inspects every UPDATE/DELETE whose target is a registered tiered view. It looks at the WHERE clause and the archive watermark, classifies the predicate into one of three tiers, and rewrites the Query accordingly. The following table maps each predicate shape to its tier and rewrite:

The classifier understands Var <op> Const (both operand orders), AND of those, OR of those when all arms prove the same tier, BETWEEN (via its desugaring to AND), and ts IN (...) (ScalarArrayOpExpr). Subqueries, UDF calls, and expressions on the partition column are AMBIGUOUS.

The tier classification above applies to the WHERE clause. The SET clause carries a separate rule for the partition column itself, because changing it can move a row across the cutoff. An in-place per-tier rewrite would leave such a row in its old tier where the view's tier predicate then hides it, so the hook handles a partition-column SET separately by the coldfront.allow_mixed_writes GUC:

• Permissive (on, default): the hook rewrites the UPDATE to SELECT coldfront._cross_tier_move(...), which RELOCATES each matched row between tiers. The function reads the affected rows once, then applies four disjoint cases by current tier and new value: stay-hot (in-place heap UPDATE), stay-cold (re-add to Iceberg with the new value), hot to cold (heap DELETE plus Iceberg INSERT), and cold to hot (heap INSERT plus Iceberg DELETE). The hot side is plain PG; the cold side is one duckdb.raw_query (DELETE plus INSERT, one Iceberg snapshot) under one bakery claim. A target value with no covering hot partition is rejected naming the view; the move is not supported inside a function or DO block, with bound parameters, with a VOLATILE new value, with a bare NULL new value, with a new value referencing other columns, or alongside a SET of other columns.
• Strict (off): the hook rejects the partition-column SET. To change the partition column, delete the row and re-insert it with the new value.

Before anything is archived (no cutoff) every row is hot, so a partition-column UPDATE is a plain hot UPDATE in either mode.

Write modes: strict vs permissive (allow_mixed_writes)

When the predicate is AMBIGUOUS the hook picks one of two behaviours from the coldfront.allow_mixed_writes GUC (USERSET, default on).

Permissive (on, default). The hook emits a dual-tier CTE:

WITH hot AS (UPDATE _events SET ... WHERE ... RETURNING *)
   , cold AS (SELECT duckdb.raw_query('UPDATE ice.default.events SET ... WHERE ...'))
SELECT h.* FROM hot h CROSS JOIN cold c;

The CROSS JOIN forces PG to execute the cold CTE (a pure-SELECT CTE that isn't otherwise referenced would be pruned even with MATERIALIZED). The hook also sets duckdb.unsafe_allow_mixed_transactions = on LOCAL for the current transaction to clear pg_duckdb's pre-commit mixed-write check. pg_duckdb's XactCallback ties DuckDB's transaction to PG's, so ROLLBACK undoes both tiers - but the path is not crash-safe: a backend crash between the Iceberg upload and the PG commit can leave orphaned object-storage files referenced by an uncommitted snapshot. Iceberg housekeeping (orphan-file expiry) reclaims them. Strict mode avoids this path entirely.

Strict (off). The hook raises an error with a hint pointing at the partition column and the accepted predicate shapes; nothing is written. Use strict mode to guarantee every write is unambiguously attributable to one tier, at the cost of requiring applications to supply a tier-deterministic WHERE clause.

Tiered tables in a Spock mesh

The bakery protocol that serialises cold writes cluster-wide is described in architecture.md → Concurrency. This section covers what is specific to a tiered table across a mesh.

A tiered table provisioned on one node becomes usable on every peer, but the pieces arrive by different routes:

So alongside the bakery substrate (coldfront.claims / coldfront.claim_acks), both coldfront.tiered_views and coldfront.archive_watermark are added to the Spock replication set when a mesh runs in tiered mode. The archiver runs on one node, so a peer only gets these rows by replication; without tiered_views a peer can read and INSERT but UPDATE/DELETE/DDL-blocking stop recognising the view.

Both tables are name-keyed - tiered_views by (schema_name, relname), archive_watermark by table_name - so each row replicates verbatim and correct on every node, with no OID divergence to reason about. See architecture.md → Registry keying.

Partition Scheme Compatibility

The archiver supports single-column RANGE partitioning on a time-like column. Other shapes are rejected at archiver startup.

Supported: single-column RANGE

The archiver accepts a single-column RANGE-partitioned table such as the following:

CREATE TABLE events (id bigint GENERATED ALWAYS AS IDENTITY,
                     ts timestamptz NOT NULL, ...)
  PARTITION BY RANGE (ts);
CREATE TABLE p_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

The partition column, primary-key columns, and any GENERATED ALWAYS AS IDENTITY columns are auto-detected from pg_catalog - no assumptions about naming or arity.

Not supported: composite partition keys

The archiver rejects composite partition keys such as PARTITION BY RANGE (tenant_id, ts) and similar. The archiver uses a single scalar watermark per table; a composite key would need one watermark per non-time dimension value.

Not supported: multi-level (sub-partitioned) tables at the top level

The archiver also rejects multi-level (sub-partitioned) tables at the top level, such as the following:

CREATE TABLE events (...) PARTITION BY LIST (branch_id);
CREATE TABLE events_branch_1 PARTITION OF events
  FOR VALUES IN (1) PARTITION BY RANGE (ts);

A multi-level top table cannot be archived directly. The workaround is to tier each sub-partition independently - one register call per branch (or the equivalent YAML list fed to archiver import):

archiver register --config cf.yaml --table events_branch_1 \
    --period monthly --hot-period "1 month" --retention "3 months"
archiver register --config cf.yaml --table events_branch_2 \
    --period monthly --hot-period "1 month" --retention "3 months"

Each events_branch_N is a valid single-level range-partitioned table and is tiered independently. After conversion, each becomes a view; applications query the branch views directly rather than the top-level events.

Performance note: partition pruning after the swap

A query through the events view routes via pg_duckdb's takeover path (iceberg_scan is present, so pg_duckdb converts the whole query to DuckDB SQL, which issues a postgres_scan on _events where PG applies partition pruning natively). Pruning works, but hot-only queries pay pg_duckdb's roundtrip overhead; users who know their query hits only hot data can query _events directly for fully native PG with no pg_duckdb involvement:

-- Transparent (hot + cold via pg_duckdb):
SELECT * FROM events WHERE ts = '2026-04-15';

-- Zero-overhead hot-only (native PG partition pruning only):
SELECT * FROM _events WHERE ts = '2026-04-15';

This is a read-path detail; writes are unaffected.

Tiered-specific limitations

These are specific to the dual-tier model. Cross-cutting limitations (the planner-level takeover, jsonb-as-json, single-node execution, S3 compatibility, one-time secret setup) are in architecture.md → Known Limitations.

The dual-tier model carries the following limitations:

1. Cold RETURNING - any write that touches the cold tier (a cold-only UPDATE/DELETE, a permissive dual-tier UPDATE/DELETE, or a watermark-split INSERT) rejects RETURNING with a clear error rather than returning a partial result. The cold tier genuinely cannot return affected rows: duckdb-iceberg's binder refuses RETURNING on Iceberg writes and pg_duckdb's row-returning entry point is SELECT-only. Hot-only DML keeps RETURNING (it is plain PG DML).

2. Command tag - an ambiguous dual-tier UPDATE returns SELECT n rather than UPDATE n, because the rewrite produces a SELECT wrapper around a DML CTE. The row count reflects hot rows only.

3. Self-join / multiple references - an UPDATE/DELETE that references the same tiered view more than once - a self-join (UPDATE events ... FROM events e2), DELETE ... USING events, or a sub-select (... WHERE id IN (SELECT ... FROM events)) - is rejected with a clear error. The rewrite swaps only the leading result-relation reference, so a second one cannot be retargeted; reference the view once.

4. Crash-safety of permissive writes - a backend crash mid-commit can leave orphaned S3 objects; see Write modes.

5. Partitioned tables only - the source table must already be range-partitioned.

6. No Iceberg partition spec on the cold tier - Iceberg tables are created without a partition-spec (partition-specs[0].fields = []), because duckdb-iceberg rejects writes to a partitioned table. Cold-tier predicate pruning therefore relies on per-file manifest min/max statistics, which DuckDB-iceberg uses to skip data files whose range doesn't intersect a query's WHERE clause. Writing one Iceberg snapshot per source partition keeps each file's min(ts)/max(ts) tight, which is what makes that pruning effective.

7. Cutover blocked by autovacuum on freshly-loaded partitions - Phase 4 of archivePartition takes ACCESS EXCLUSIVE on the partition under a 100 ms lock_timeout circuit breaker. Autovacuum's SHARE UPDATE EXCLUSIVE on the partition conflicts with that request, so when a vacuum is running the cutover fails cleanly with ERROR: canceling statement due to lock timeout and leaves the trigger + delta in place for the next cycle to retry.

Mitigation: disable autovacuum on the soon-to-be-archived partition (ALTER TABLE <part> SET (autovacuum_enabled = false); - the setting goes with the partition when it is detached and dropped), or schedule the archive cycle so partitions have already settled.