pgEdge ColdFront - Architecture

ColdFront makes a single PostgreSQL table transparently span two storage tiers: recent rows stay in the PostgreSQL heap, older rows live in Apache Iceberg on object storage. Applications see one table (in fact a view); a C extension rewrites reads and writes to hit the right tier, and all Iceberg I/O goes through pg_duckdb running in-process inside PostgreSQL - no external query engine, no Go Iceberg libraries.

For mode-specific design, see architecture_tiered.md (hot PG + cold Iceberg) · architecture_decoupled.md (all-Iceberg) · architecture_vectors.md (vector storage).

Operating Modes and Topologies

Three independent axes describe any ColdFront deployment; they compose freely - e.g. tiered + mesh + permissive writes. The following table shows how each axis is selected:

Both storage modes coexist in one database and share one code path: the transparent view and read rewriter, the INSERT/UPDATE/DELETE/MERGE hook (emit_cold / emit_hot / emit_dual in extension/coldfront/src/coldfront.c), and the per-table claim (coldfront._take_iceberg_claim) that every cold write path in the hook takes, mostly through _exec_iceberg_with_claim. Decoupled mode always classifies as TIER_COLD and never reaches emit_hot; vanilla and mesh differ only in how that claim serializes cold writes. This document covers the shared mechanics and the tiered path; see architecture_decoupled.md for the decoupled mode's ACID model and distributed scaling story.

Read Target: Primary or Physical Standby

Orthogonal to the three axes above, any ColdFront node - vanilla or a mesh member - can have one or more physical (streaming) standbys that serve read-only cross-tier reads. The hot tier arrives by physical replication; the cold tier is read by DuckDB executing on the read-only backend. A base backup contains the coldfront catalog (tiered_views, archive_watermark, storage_secret) and the GUCs (in postgresql.conf, not ALTER SYSTEM), so a replica is byte-identical to its primary (same OIDs). The DuckDB persistent secret file sits under the OS user's home directory, outside PGDATA, so with static credentials run SELECT coldfront.materialize_storage_secret() once on the replica. Cold writes are refused on a standby: every cold write path in the hook calls coldfront._reject_on_standby, which raises when pg_is_in_recovery(), before it takes the claim, so a read replica can never become an uncoordinated writer to the shared Iceberg table (hot writes hit a PG heap and PG rejects them natively).

Standby reads are gated by ci/probe-standby.sh (the risk-first check that iceberg_scan runs on a read-only backend at all) and exercised in the journey by story_standby_reads (the ·standby matrix cells). Failover/promotion is delegated to Patroni and is out of scope for the test matrix - see ci/runbooks/failover-patroni.md.

System Overview

A ColdFront database is PostgreSQL with two extensions preloaded (pg_duckdb + coldfront), backed by an Iceberg REST catalog and an object store, as shown below:

┌──────────────────────────────────────────────────────────┐
│  PostgreSQL + pg_duckdb + coldfront                        │
│  • one transparent view per managed table                  │
│  • coldfront hooks: post_parse_analyze (DML rewrite),      │
│    ProcessUtility (DDL); the C hook lazily ATTACHes the    │
│    Iceberg catalog on the first query touching a view      │
│  • coldfront.tiered_views registry + bakery claims         │
│  • pg_duckdb runs DuckDB in-process:                       │
│      DuckDB reads cold data, duckdb.raw_query()            │
│      writes it                                             │
└──────────────┬───────────────────────────────────────────┘
               │
┌──────────────▼───────────────────────────────────────────┐
│  Lakekeeper - Iceberg REST catalog (own dedicated Postgres)│
│  Manages Iceberg metadata, snapshots, commit concurrency   │
└──────────────┬───────────────────────────────────────────┘
               │
┌──────────────▼───────────────────────────────────────────┐
│  S3-compatible object store (SeaweedFS, MinIO, GCS, …)     │
│  Parquet data files + Iceberg metadata files               │
└────────────────────────────────────────────────────────────┘

The following table describes each component, its role, and its license:

How rows move through this depends on the storage mode: the tiered hot heap + archiver + UNION ALL data-flow is in the Data Flow section of the Tiered Mode page; the all-Iceberg flow is in architecture_decoupled.md.

Core Mechanics: pg_duckdb

All Iceberg I/O goes through SQL executed against PostgreSQL. There are no Go DuckDB/Iceberg/Arrow libraries. DuckDB Iceberg writes require a REST catalog - Lakekeeper fills this role.

Session Setup

The cold-tier S3 secret is set once per cluster. A single call records the credentials and materializes a DuckDB persistent secret:

SELECT coldfront.set_storage_secret('<key>', '<secret>', '<endpoint>');
-- Azure ADLS Gen2 instead of an S3-compatible store:
SELECT coldfront.set_storage_secret_azure('<connection string>');

set_storage_secret does two things. (1) The function stores the secret in the coldfront.storage_secret table - an extension-member table, so its data is excluded from pg_dump by default, and coldfront.ensure_replicated() adds it to the Spock replication set, so the secret replicates by value to every mesh node with no per-node file syncing. (2) The function materializes a DuckDB persistent secret, which DuckDB loads automatically at instance init - so every backend, including the first fresh one, sees the secret at a committed timestamp before any query runs. set_storage_secret_azure takes one connection string holding the account name, account key and endpoint suffix, writes the same row, and materializes a TYPE azure persistent secret.

For deployments that must not store a credential at all, coldfront.set_storage_secret_vended() records a vended coldfront.storage_secret row that holds no credential and materializes no secret. The row's vended flag drives coldfront._attach_delegation_mode(), so ensure_attached() attaches the catalog with ACCESS_DELEGATION_MODE VENDED_CREDENTIALS: Lakekeeper issues short-lived per-table credentials (S3 STS, or Azure SAS) that duckdb-iceberg consumes directly. A static row attaches with ACCESS_DELEGATION_MODE NONE (the persistent secret supplies the credential); the vended path sidesteps the fresh-transaction limitation below because duckdb-iceberg re-creates the per-table secret inside the commit transaction.

For backup and restore (pg_dump), the durable tiering metadata - coldfront.tiered_views (registry), archive_watermark (cutoffs) and partition_config - is marked with pg_extension_config_dump, so a logical pg_dump includes it and a restore re-attaches to the same Iceberg cold tier with no re-provisioning. Two things are deliberately not dumped: the credential (coldfront.storage_secret above - re-run set_storage_secret (or set_storage_secret_azure or set_storage_secret_vended) once after restoring into a fresh instance) and the bakery's transient claim tables (claims / claim_acks / deferred_acks, per-node mesh state). Until the credential is re-established a restored node serves hot reads but fails cold I/O cleanly. ci/ops.sh Check 4 exercises exactly this.

The Iceberg catalog ATTACH is lazy: the coldfront C extension hook issues ATTACH IF NOT EXISTS against Lakekeeper - using the cluster's coldfront.warehouse and coldfront.lakekeeper_endpoint GUCs - on the first query that touches a tiered view (read or write), per DuckDB cached connection. There is no setup step and no per-session boilerplate: both reads (the view's duckdb.query) and writes (duckdb.raw_query) work on a fresh psql session. The hook attempts no ATTACH until a query touches a tiered view, so a missing warehouse never blocks a connection opened before bootstrap.

Non-Superuser App Roles (Least Privilege)

pg_duckdb force-disables DuckDB's LocalFileSystem for any role that lacks both pg_read_server_files and pg_write_server_files (see Upstream requests), which would block the side-loaded iceberg/postgres DuckDB extensions from loading on ATTACH. So coldfront.ensure_attached() / ensure_pg_attached() are SECURITY DEFINER with a pinned search_path: the extension load + ATTACH run elevated (gates key off GetUserId(), the effective user), and because the DuckDB instance is per-backend the attach persists for the session - every subsequent cold read / _exec_iceberg_with_claim then runs as the app role over S3/httpfs, never touching LocalFileSystem. The app role needs only duckdb.postgres_role membership, object grants, and SET on the superuser-only duckdb.unsafe_allow_execution_inside_functions parameter, which the cross-tier move needs; no superuser, no pg_{read,write}_server_files.

Because the attach helpers run elevated, the deployment-config GUCs they consume (coldfront.warehouse, coldfront.lakekeeper_endpoint, coldfront.local_pg_dsn) are registered PGC_SUSET (the last also GUC_SUPERUSER_ONLY) in _PG_init, so a non-superuser cannot redirect the elevated ATTACH at an attacker endpoint. coldfront.loopback_dsn, the DSN of the bakery's loopback, is PGC_SUSET as well, because the loopback runs claim statements as the user that DSN names. It stays readable by every role, since the invoker-rights coldfront._bakery_armed() reads it on every cold write. All four GUCs default to an empty string. Onboarding is one operator call, coldfront.grant_app_access(role) - idempotent, registry-derived (schemas, views, the hot heap + its identity sequence, the cold-path function EXECUTE allow-list), not PUBLIC-executable. The image defaults duckdb.postgres_role = coldfront_duckdb (env COLDFRONT_DUCKDB_ROLE) and creates the role, so the path needs no further setup.

In a Spock mesh the role and its grants replicate via Spock DDL - onboard once on any node. Mesh cold writes route through the bakery protocol; its coordination function _claim_iceberg_lock is itself SECURITY DEFINER (search_path-pinned, fully schema-qualified) so a non-superuser drives the cross-node serialization (pg_stat_replication liveness + the loopback claim) with the privilege it requires. _exec_iceberg_with_claim deliberately stays SECURITY INVOKER - it runs the caller's cold DML, which must execute as the caller. This SECURITY DEFINER setting is protocol-neutral: it changes the PG execution privilege, not the claim/ack/lock/ticket protocol, re-verified against the TLA+ model (all safe configs pass; the race config still violates NoLakekeeperConflict).

See README "Security"; the setting is asserted by the journey's story_app_privilege, ci/ops.sh check 3, and the privilege_model pg_regress test.

Temp Table Bridge: PG → Iceberg

duckdb.raw_query() cannot see PG tables directly. The bridge is a DuckDB temp table:

CREATE TEMP TABLE duck_stage USING duckdb AS
  SELECT * FROM public.p_2026_01;
SELECT duckdb.raw_query($$INSERT INTO ice.public.events
  SELECT * FROM pg_temp.duck_stage$$);
DROP TABLE duck_stage;

Cold-Side Column References

iceberg_scan() requires r['col']::type syntax:

SELECT r['id']::bigint, r['ts']::timestamptz, r['status']::text
FROM iceberg_scan('ice.public.events') r
WHERE r['ts'] < '2026-03-01'::timestamptz;

Application Interface

Applications use the transparent view exactly like a table. A post_parse_analyze_hook in the coldfront extension intercepts INSERT/UPDATE/DELETE/MERGE whose target is a registered relation - resolved in coldfront.tiered_views by name (schema_name, relname) - and rewrites the parsed Query so it lands in the correct tier; cold-side writes go through _exec_iceberg_with_claim (see Concurrency). A ProcessUtility_hook handles DDL on the same relations.

The same parse-analyze hook prepares a SELECT that DuckDB will run, wherever in the statement the view is named (a CTE, a sub-select, a set-operation branch): date_bin, the ::jsonb cast, jsonb_array_length and the JSON builders (jsonb_build_object, jsonb_agg and their json_ twins) are rewritten into spellings both engines accept (see the Supported Column Types section of the Using ColdFront guide). A planner_hook folds bound parameters into such a read before pg_duckdb plans it when a parameter sits where DuckDB cannot type a placeholder (a direct argument of a pg_duckdb function, any argument of a table function); the plan cache's generic-plan build, which has no values, gets a PostgreSQL plan priced above any custom plan, so under the plan cache's cost-based selection the read is planned from its values on every execution. plan_cache_mode = force_generic_plan bypasses that selection and picks the value-less generic plan, which fails with only works with DuckDB execution (see the Supported Column Types section of the Using ColdFront guide).

The following table maps each operation to its interface and routing path:

With duckdb.force_execution = true, hot-side queries are also accelerated by DuckDB's vectorized columnar engine.

How the hook splits a write is mode-specific:

• In tiered mode, the hook routes by the partition-column watermark - hot heap vs cold Iceberg, with dual-tier writes for ambiguous predicates. See Transparent INSERT and Transparent UPDATE/DELETE in the Tiered Mode page.
• In decoupled mode, the hook always classifies TIER_COLD, so every write is a single-tier Iceberg write. See architecture_decoupled.md.

Cold-Tier DML from Inside PL/pgSQL (Functions, DO Blocks, Triggers)

Cold-tier INSERT/UPDATE/DELETE work as top-level statements and from inside a plpgsql function / DO block / trigger, via two mechanisms:

• Parameters are emitted as a runtime format(<template>, $1, $2, …) call (cold_sql_arg), with the param types declared on the re-parse (parse_analyze_fixedparams). PG binds the values at execution, so DuckDB only ever sees finished literals. This applies everywhere - a driver's parameterized cold UPDATE at the top level is the same case.

• A cold call parsed inside plpgsql is wrapped as a DML over a permanent single-row carrier, coldfront._dummy_dml_target. plpgsql rejects a bare result-returning SELECT with no INTO/PERFORM ("query has no destination for result data") and the cold target is a DuckDB-attached object PG cannot tag a real DML against, so the call is reshaped as a no-row DML:

  UPDATE coldfront._dummy_dml_target SET anchor = anchor
   WHERE coldfront._exec_iceberg_with_claim(...) IS NULL
  

  The cold call runs exactly once in the WHERE qual; because _exec_iceberg_with_claim returns void and void IS NULL is always false, zero rows match - the carrier is never written: no dead rows, no WAL, no bloat. For dual-tier and tiered-INSERT the hot DML is the outer statement and this same UPDATE runs inside a data-modifying WITH-CTE. At the top level the rewrite keeps the plain SELECT coldfront._exec_iceberg_with_claim(...) shape.

"Inside plpgsql" is detected via pstate->p_post_columnref_hook != NULL (plpgsql installs that hook to resolve identifiers as variables; a top-level statement, including a parameterized one, never does) - a precise, stateless, side-effect-free signal that touches no PL/pgSQL plugin slot.

Concurrency and pgEdge Spock Deployments

ColdFront coordinates concurrent writes across the cluster as follows:

• The archiver runs on one node only (via cron). Its cold writes route through the same bakery claim as any other cold write, so catalog conflicts are prevented up front rather than retried; the only retry is the cutover's lock acquisition (10 attempts, exponential backoff from 100 ms to 51.2 s).
• Hot writes are replicated by Spock normally (standard PG DML).
• Cold writes via duckdb.raw_query() from multiple nodes are serialized PG-side by the bakery protocol in the coldfront extension - every iceberg-only INSERT/UPDATE/DELETE/MERGE wraps in coldfront._exec_iceberg_with_claim, which holds a globally-ordered Snowflake ticket via the Spock-replicated coldfront.claims table and waits for its turn before issuing the iceberg commit. There are no 409s and no app-level retry. See the Concurrency section of the Decoupled Mode page for the full design and benchmarks.

Cold-Write Strategy: Stock vs Patched duckdb-iceberg

Most cold writes run through _exec_iceberg_with_claim; the tiered INSERT's cold sink and the cross-tier move take the claim through _take_iceberg_claim themselves. What differs is when the bakery ticket is held. The async ordering is used only on a mesh node with the bakery enabled, and only when both coldfront.iceberg_async_parquet (default off) and the build marker coldfront.iceberg_bakery_patch (asserting the loaded duckdb-iceberg includes the patch) are on (coldfront._iceberg_async_active()); a vanilla node always takes its lock before uploading. The following table shows the two orderings:

The code path and the application-visible behavior are identical, so the GUCs are purely a performance knob. The patch relocates parent-snapshot stamping from upload time into PG's pre-commit phase (inside the bakery ticket, against a freshly-fetched table), so overlapping uploads cannot commit a stale parent. Async requested without the build marker downgrades safely to the stock ordering, noted once per session with a server LOG line - never a silent 409. The Docker image ships the patched binary and sets both GUCs on (docker/entrypoint.sh); bare-metal users on a stock binary leave both off and lose only the upload overlap. See docker/Dockerfile.duckdb15-base for the build.

Transparent DDL via coldfront

A ProcessUtility_hook in the coldfront extension intercepts DDL that targets a registered tiered table's hot heap or a decoupled table's wrapper view. A hot heap is matched by resolving the DDL target relation to an OID and comparing it with the OID of the registry's hot_table - never by string, so it is schema-agnostic - and a view by its schema and name, the registry's key. A view the hook rebuilds keeps the owner and the table-level grants the view had. The hook rebuilds the view and updates the registry as the extension's owner, once the ownership check PostgreSQL makes for the statement has passed. The role running the statement needs what PostgreSQL requires for the statement itself, USAGE on the schema of the relation it names included, but neither ownership of the view nor CREATE on its schema, and the hook's own registry lookups and updates need no privilege on the registry. The Iceberg change, which is cold I/O, runs as that role, so a column change also needs the cold access grant_app_access gives, SELECT on the registry included. The following table summarizes the DDL it handles:

The hook's view rebuild does DROP VIEW + CREATE VIEW (not CREATE OR REPLACE VIEW, which PG only allows for appending columns at the end); the archiver's cutover, which only moves the cutoff, instead uses CREATE OR REPLACE VIEW and keeps the view OID. The registry is keyed by the view's (schema_name, relname), which either rebuild leaves unchanged, so there is nothing to re-point. A column change is mirrored to Iceberg through ensure_attached() + the bakery, so it requires a configured coldfront.warehouse; a RENAME TABLE/VIEW touches no Iceberg schema and rebuilds the view regardless. Concurrent schema changes are serialized by the same bakery as cold DML.

In active-active deployments, Spock replicates only the top-level ALTER TABLE or ALTER VIEW. The image sets spock.allow_ddl_from_functions = on, which would also replicate the hook's SPI-issued view and trigger DDL, so the hook issues that DDL with spock.enable_ddl_replication off. The registry tables replicate for the archiver's writes, so the hook makes a rename's registry update in Spock's repair mode (spock.repair_mode), and each peer's hook makes the same update itself. A peer's apply worker re-runs the replicated statement; the hook rebuilds that peer's own local view, but coldfront._mirror_iceberg_alter skips the Iceberg ALTER there (it runs under session_replication_role = replica) because the originator already evolved the shared Lakekeeper catalog. Because the registry is name-keyed (see Registry Keying), the row is identical on every node: the rebuild needs no re-pointing. DROP and TRUNCATE are blocked on every node. What a tiered table additionally needs to be usable on a peer is covered next.

The tiered-specific cross-node behavior - what replicates so a tiered table is usable on every peer, and why both the registry and the watermark join the replication set - is in the Tiered Tables in a Spock Mesh section of the Tiered Mode page.

Registry Keying: By Name, Not OID

coldfront.tiered_views is keyed by (schema_name, relname) - the transparent view's qualified name. The C hook reads the whole registry, joined to the watermark, once per command, and matches each candidate relation's schema and name in memory; the hook already holds the target relid, from which the schema and name are an inexpensive syscache lookup.

The name is the right key because it is stable across the churn the system actually produces. The DDL-rebuild path does DROP+CREATE on the view, minting a new view OID each time, and the archiver's cutover replaces it with CREATE OR REPLACE VIEW (same OID) - in both cases the name is unchanged. An OID key would have to be re-pointed on every DDL rebuild; a name key is not. The one event that does change the name, ALTER VIEW … RENAME, migrates the registry row and the watermark to the new name in a single step (_rename_tiered_view), exactly as the watermark is name-keyed.

The name also replicates cleanly across a Spock mesh: it is node-independent, so the registry row is identical on every node and the replication set copies it by value (an OID is node-local and could not be). That is what makes cross-node tiered tables work with no per-node re-resolution - see the Tiered Tables in a Spock Mesh section of the Tiered Mode page.

Lower-level operations that genuinely need an OID - catalog lookups, the DDL hook matching the hot heap - resolve name→OID via to_regclass / get_rel_name at the point of use: names everywhere, OIDs only where required.

Per-Table Config: coldfront.partition_config

Which tables are managed and their lifecycle (hot_period, retention_period, partition_period, premake, mode, expiration_strategy) live in coldfront.partition_config - like tiered_views and archive_watermark, a name-keyed table (see Registry Keying). coldfront.partition_config is added to the default replication set on a Spock node by the binaries themselves and by coldfront.ensure_replicated() (a no-op on vanilla, where there is one node), so every node reads identical config with no per-node file syncing. hot_period and retention_period are native PostgreSQL interval columns - the column type validates each value on write, and expiry cutoffs are computed in-DB with calendar-accurate interval arithmetic (now() - period: real months, leap years), never an approximate fixed-day duration in Go. CHECK constraints encode the structural lifecycle rules (a destroy boundary is required; id mode forbids a hot tier - the cold tier is time-only; 2-level needs an explicit RANGE column; expiration_strategy is drop|detach, and detach - expire by detaching only, not dropping - is allowed partition-only), so an invalid row is rejected at write time. The one rule that is operator-config policy rather than a storage invariant - retention_period must exceed hot_period - is validated at the register/set CLI boundary and at binary startup (partition.ValidatePeriods, a calendar-aware interval comparison), deliberately not a CHECK. The standalone partitioner self-materializes the table on stock PostgreSQL via EnsureTable, needing no extension. Connection config (DSN, Iceberg/S3 credentials) is deliberately not stored here - it is per-node and must never be replicated.

The config replicates by value; the partition lifecycle DDL must also reach every node. CREATE … PARTITION OF … and DROP TABLE are ordinary transactional DDL, so Spock's DDL replication carries them automatically. The retention DETACH PARTITION … CONCURRENTLY is the exception: CONCURRENTLY cannot run in a transaction block, so Spock skips it (WARNING: This DDL statement will not be replicated) - left alone, a partition would stay attached on every peer while the origin detaches it. The partition manager therefore detaches locally (top-level, non-blocking) and then fans the identical concurrent detach to each peer itself: it enumerates the Spock nodes and re-runs the detach on each peer's own connection (skipping any node where the partition is already detached). The fan-out is gated on Spock being present, so on vanilla single-node PostgreSQL it is skipped entirely and the manager needs no extension. The archiver's cold-tiering cutover instead uses a plain, transactional DETACH inside its atomic watermark+view+detach commit, so that one replicates on its own. This is verified before any mesh partitioner run by story_mesh_partition_ddl in ci/journey.sh (an N×(N-1) probe: create from every node, detach, drop, asserting each lands on all nodes).

Both binaries read this table, each loading only the enabled rows of its own mode (the archiver those with a hot_period, the partitioner those without), and exit with an error when they find none; tables enter it via register, or from a YAML archiver.tables list fed to import. Both expose a management CLI - register / list / set / remove / import / export - documented in usage.md.

Known Limitations

These apply to both storage modes. Tiered-only limitations (cold RETURNING, dual-tier command tag, crash-safety of permissive writes, partition-scheme constraints, autovacuum-vs-cutover) are in the Tiered-Specific Limitations section of the Tiered Mode page.

The cross-cutting limitations are:

• The storage secret needs a one-time setup after warehouse bootstrap: after Lakekeeper is provisioned, an operator calls SELECT coldfront.set_storage_secret(...) once per cluster; the catalog ATTACH itself is lazy, so there is no per-session setup (see Session Setup).

• jsonb reads as json through the view, unless the read is rerouted to the hot heap: DuckDB has no native jsonb, and pg_duckdb takes over any query that references a DuckDB read (all-or-nothing plan takeover), so the cold branch cannot produce a PG jsonb directly. The view generator casts jsonb columns to DuckDB-safe json on both sides: hot emits "col"::json, cold emits r['col']::json, the UNION unifies on json. Standard JSON access (->>, ->, #>) works without any caller-side cast; jsonb-only operators (?, @>, containment, de-dup) need an explicit data::jsonb in the caller's query. Storage in Iceberg remains VARCHAR (Iceberg has no JSON type either). The cold sink sends the incoming value as its text form.

• Lakekeeper remote signing may not work with all S3-compatible stores; the workaround is ACCESS_DELEGATION_MODE NONE with a direct DuckDB S3 secret.

• Interception happens at the planner level, with no per-query decision engine: pg_duckdb decides whether to take over a query by inspecting the parse tree for signals (a pg_duckdb table function, the duckdb.force_execution GUC, DuckDB-only functions). Once pg_duckdb takes over, the whole statement runs in DuckDB; there is no cost-based hot-vs-cold split per predicate. The one exception is a single-table read whose WHERE proves ts >= cutoff: the hook rewrites it to read the hot heap in plain PostgreSQL, which returns native jsonb. Other hot-only queries can target _events directly (native PG, no pg_duckdb roundtrip); queries that need cross-tier semantics go through the view.

• Query execution is single-node: a query runs on the PG backend it landed on. pg_duckdb does not distribute the DuckDB plan across nodes. Replication (single- or multi-master via pgEdge Spock) is supported on the hot tier and transparent to the application; scaling read throughput requires more replicas rather than parallelizing one query. Those replicas, including read-only physical standbys, serve cross-tier reads (see Read Target).

• ColdFront supports Iceberg only, not Delta Lake. Adding Delta would require either a second writer path in pg_duckdb's Iceberg extension or a different analytical engine.

Infrastructure (Docker)

Three services run: PG+pg_duckdb+coldfront, Lakekeeper, and any S3-compatible store. Both extensions must be in shared_preload_libraries - coldfront installs its hook in _PG_init, which runs when the postmaster loads the library.

A two-layer image serves every deployment: a prebuilt base (docker/Dockerfile.duckdb15-base) including pg_duckdb (PR #1025) on DuckDB 1.5.4 + the patched duckdb-iceberg on a pgEdge *-spock5-minimal base (Spock + Snowflake), and a thin app layer (docker/Dockerfile.duckdb15, --build-arg PG_MAJOR=16|17|18) that compiles coldfront on top. The same image plays both topology roles - vanilla leaves spock/snowflake out of shared_preload_libraries; mesh loads them (MESH=on, set by the entrypoint).

The following compose file shows the three services - the database, Lakekeeper, and the S3-compatible store:

services:
  db:
    build:
      context: .
      dockerfile: docker/Dockerfile.duckdb15
      args: { PG_MAJOR: 18 }
    environment: { PG_MAJOR: 18, MESH: "off" }   # entrypoint configures preload + GUCs
  lakekeeper:
    image: quay.io/lakekeeper/catalog:latest
    command: serve
  seaweedfs:
    image: chrislusf/seaweedfs:latest
    command: "server -s3 -dir=/data -s3.config=/etc/seaweedfs/s3.json"

Lakekeeper needs the following: bootstrap (POST /management/v1/bootstrap) then warehouse creation (POST /management/v1/warehouse) with S3 credentials and sts-enabled: false, remote-signing-enabled: false.

Lakekeeper keeps its own catalog tables in a PostgreSQL database (and its schema migration needs the uuid-ossp contrib extension). Lakekeeper runs on its own dedicated Postgres - a separate lakekeeper-db container in the test stack, and a separate managed instance in production - never co-located on a ColdFront data node. Co-locating would couple the catalog's availability and load to a data node and would drag contrib into the ColdFront image for no reason; the dedicated store keeps the ColdFront image lean (core uuid type only, no uuid-ossp).

Upstream Requests

The behaviors in upstream projects that ColdFront works around are kept here as architectural notes: what is missing, the workaround in use today, and the shape of the upstream capability that would let ColdFront drop the workaround.

pg_duckdb: Non-Superuser LocalFileSystem Blocks Side-Loaded Extensions

pg_duckdb force-disables DuckDB's LocalFileSystem for any role that lacks both pg_read_server_files and pg_write_server_files, which also blocks loading a locally-installed DuckDB extension - and ColdFront side-loads a patched iceberg (and postgres) extension from disk, which DuckDB lazily loads on ATTACH, so a non-superuser's first cold query fails at the elevated load step.

The workaround in use today is the SECURITY DEFINER attach helpers described under Non-Superuser App Roles.

The upstream shape that would drop it is either a way to mark specific locally-installed extensions as loadable without LocalFileSystem access, or distinguishing extension-load file access from user-initiated raw file access in the non-superuser sandbox - so a non-superuser member of duckdb.postgres_role could ATTACH (TYPE ICEBERG, …) directly, without ColdFront's SECURITY DEFINER shim.

pg_duckdb: Native PG-Reader → Iceberg Streaming (No libpq Round-Trip)

pg_duckdb has a fully-native, in-process Postgres-table reader for analytics on PG heap data, but that machinery is not reachable from the write path into an attached Iceberg catalog.

As the workarounds in use today, a tiered INSERT renders its cold rows in the backend (coldfront._cold_sink) and writes them to Iceberg in batched duckdb.raw_query INSERTs, and a decoupled INSERT … SELECT from a PostgreSQL table reads it through the DuckDB postgres extension's pglocal.<schema>.<table> ATTACH, pipelining rows over libpq (loopback) → DuckDB executor → Iceberg writer → S3. Neither is the in-process reader: the first pays for the per-row rendering, the second for the libpq round-trip per row batch.

The desired end-state is a way to drive the native in-process reader straight into the Iceberg writer - e.g. a COPY form:

COPY (SELECT * FROM public.events_partition) TO ICEBERG 'ice.public.events';

That would make pg_duckdb the only place in the data path that touches the rows: PG executor (heap reader) → pg_duckdb vector format → Iceberg writer, one pass, in-process, with no libpq loopback and no temp-disk.

pg_duckdb: A Statement DuckDB Cannot Run Is Refused, Not Delegated

pg_duckdb's planner hook takes over any statement whose parse tree references a DuckDB item, and then requires that statement to be one DuckDB can execute: IsAllowedStatement runs with throw_error set, so a statement that needs DuckDB but modifies a PostgreSQL relation raises "DuckDB does not support modifying Postgres tables" rather than being handed back to the PostgreSQL planner. Two statement shapes reach that error through a view whose body reads a cold table:

• UPDATE/DELETE on a view with INSTEAD OF triggers. PostgreSQL expands the view to scan its rows and fire the trigger, the expanded body contains the DuckDB item, and the statement errors before any trigger runs.
• INSERT ... SELECT whose source needs DuckDB, even where the target's own INSTEAD OF INSERT trigger would have taken the row.

A plain INSERT ... VALUES through an INSTEAD OF INSERT trigger is unaffected, because that plan never expands the view, so no DuckDB item reaches the hook.

As the workaround in use today, ColdFront's post_parse_analyze_hook rewrites INSERT/UPDATE/DELETE/MERGE on a registered view into its hot, cold or dual emit before planning, so on the paths ColdFront owns pg_duckdb only ever sees a shape it accepts. What has no workaround is a view an application defines over cold data with its own INSTEAD OF UPDATE/DELETE triggers, or an INSERT ... SELECT drawing from such a view.

In the upstream shape that would drop it, where NeedsDuckdbExecution is true and IsAllowedStatement is false, pg_duckdb chains to the previous planner hook instead of throwing, so PostgreSQL plans the statement and its INSTEAD OF triggers fire. Every statement DuckDB can run stays on the DuckDB path, and the check that decides this already exists in a non-throwing form.

DuckDB: Spill Files Are Not Namespaced per Instance

DuckDB names a spill file from a counter that starts at zero in every instance (duckdb_temp_storage_<class>-<index>.tmp, duckdb_temp_block-<id>.block), and an instance being torn down deletes every duckdb_temp_* file in its temp directory. pg_duckdb gives each backend its own DuckDB instance but one shared duckdb.temporary_directory (default $PGDATA/pg_duckdb/temp), so concurrent backends that spill open the same paths, read each other's bytes, and delete each other's files. On the 1.5.4 stack, of four sessions spilling a 3M-group aggregate under a 150 MB memory limit, three died with IO Error: Could not read enough bytes. The defect is tracked as duckdb#15173 (open, reproduced); pg_duckdb#887 is an unmerged fix one layer up.

As the workaround in use today, each backend takes a subdirectory of the configured path named after its own PID, assigned by cf_own_duckdb_temp_dir from the parse-analyze hook on the session's first statement, which is ahead of pg_duckdb building its instance and reading the setting. The same hook reclaims the subdirectories whose PID no live backend holds, removing their spill files and the directory and reporting what that freed. That sweep runs once per session and costs single-digit milliseconds per spill file, so even a directory abandoned with 600 GB in it (about 600 files at DuckDB's file size) is a couple of seconds on the one statement that finds it. See the Tuning Knobs section of the Using ColdFront guide.

The PID is what makes that reclaim possible, and is why ColdFront does not apply pg_duckdb#887, which names the directory with a random uuid and removes it from an on_proc_exit hook. That hook does not run when a backend is killed by SIGKILL, an immediate shutdown or the OOM killer, which is precisely when spill files are left behind, and a random name cannot be attributed to an owner afterwards: nothing can decide whether the directory belongs to a live session or a dead one, so the disk is never reclaimed. A PID can be checked against the live backends (BackendPidGetProc), so any later session reclaims the orphans, whole gigabytes at a time. A reused PID resolves to a live backend and is left alone, as is any directory holding something other than spill files.

The upstream shape that would drop it is a spill filename that includes the instance's own identity, so instances sharing a temp directory cannot collide and teardown deletes only what it wrote. That belongs in DuckDB, where the naming is, rather than in each embedder.

duckdb-iceberg: Secret Visibility Under Fresh Transactions

A secret created with CREATE SECRET from a caller's still-active transaction is not visible to the fresh transaction duckdb-iceberg opens for its commit-time I/O, so a fresh PG backend's first cold-tier write would fail with HTTP 403 against any non-AWS S3-compatible endpoint.

The workaround in use today is the DuckDB persistent secret materialized by coldfront.set_storage_secret(...) (see Session Setup) - loaded at instance init, it already sits at a committed timestamp before any backend's first cold-tier write looks it up.

In the desired end-state, either IcebergTransaction::Commit runs its commit-time I/O under the caller's ClientContext (rather than a freshly-opened Connection), or any extension that synthesises CREATE SECRET from external state commits that transaction explicitly, so the secret sits at a committed timestamp before a consumer's fresh transaction looks it up. Either would let a per-session synthesized secret work without relying on the persistent-secret mechanism.

duckdb-iceberg: Append to the Manifest List Instead of Rebuilding It

At commit, duckdb-iceberg (v1.5) rebuilds the new snapshot's manifest list from every entry of the existing one (CreateFromEntries plus IcebergAddSnapshot), so the commit reads the whole existing manifest list. Under ColdFront's serialized cold-write protocol that read happens while the writer holds the bakery ticket, and its cost grows with the manifest-list length, so it inflates the serialized critical section (compaction only partly offsets it).

As the workaround in use today, the iceberg-bakery-aware-commit-refresh patch re-reads the manifest list from the freshly-loaded catalog head inside the ticket (RefreshExistingManifestList). The re-read is correct - it folds in any peer manifests committed since this writer staged its parquet - but it pays the full re-scan of the manifest list under the lock.

The upstream shape that would drop it is a manifest-list commit that appends the new manifest to the existing manifest-list file, referenced by path from the current catalog head, instead of rebuilding from all scanned entries. That keeps peer inclusion (the fresh head already points at peers' manifests) while removing the full re-scan from the commit, so the serialized section no longer grows with manifest-list length. That change is correctness-sensitive (manifest-list integrity, commit conflicts, silent data loss) and its throughput value is unmeasured and workload dependent, so it belongs upstream with fleet benchmarking rather than as a patch ColdFront maintains.

Next Steps

To go further with ColdFront, consult the following documents:

• The Tiered Mode deep dive describes the archive pipeline, transparent DML, and tiered tables in a Spock mesh.
• The Decoupled Mode deep dive describes iceberg-only tables, their ACID model, and the bakery protocol.
• The Vector Storage deep dive describes how embeddings are stored, assigned to clusters, and searched.
• The Using ColdFront guide covers day-to-day use of both modes.