Skip to content

Using ColdFront in Decoupled Mode

Before you begin, configure PostgreSQL, Lakekeeper, and your object store by following Configuring ColdFront, Configuring Lakekeeper, and Configuring your Object Store; every row of a decoupled table lives in the cold tier those guides set up, from the first row. This page walks through creating a decoupled table with coldfront.create_iceberg_table(), changing its columns, and adopting a table that another engine already wrote into the catalog.

A single call creates a decoupled table:

SELECT coldfront.create_iceberg_table(
    p_schema  => 'public',
    p_table   => 'events',
    p_columns => '[
      {"name":"id",     "type":"bigint"},
      {"name":"ts",     "type":"timestamptz"},
      {"name":"status", "type":"text"},
      {"name":"data",   "type":"jsonb"}
    ]'::jsonb,
    p_partition_cols => '{month(ts)}'
);

p_partition_cols partitions the Iceberg table, here by the month of ts: each month's rows land in their own data files, and a query with a time filter skips the months outside it before reading anything. Each element is one term as DuckDB's PARTITIONED BY takes it: a column name, year(col), month(col) or day(col) on a timestamp or date column, hour(col) on a timestamp column, bucket(N, col) or truncate(W, col); '{month(ts), region}' partitions by both. The argument is a PostgreSQL array literal, so a term that contains a comma is double-quoted inside it, the comma being the array's delimiter: '{"bucket(16, id)"}'. A column name that itself needs double quotes has them backslashed inside such an element: '{"month(\"Event Time\")"}'. Leave the argument out for an unpartitioned table.

That single statement provisions:

  • ice.public.events on the attached Iceberg catalog.

  • a PG-side wrapper view public.events with proper PG-typed columns.

  • a coldfront.tiered_views registry row, so that the coldfront C hook intercepts every INSERT, UPDATE, DELETE, and MERGE on the view and rewrites each one to a single duckdb.raw_query(...) against ice.public.events.

In a mesh one node provisions: Spock's ddl_sql repset replicates the CREATE VIEW, and the default repset replicates the name-keyed registry row, which enables the write hook on every peer. Each node also needs the one-time setup call coldfront.ensure_replicated() - see Configuring a Distributed Deployment.

Changing a Decoupled Table's Columns

An ALTER TABLE on the wrapper view adds, drops, renames or widens a column of the Iceberg table, and the view follows, with its owner and table-level grants kept; a column-level grant on the view is not kept. Only the view's owner, or a role holding its privileges, can change its columns, and that role needs the cold access grant_app_access gives, since the Iceberg change runs as that role. The following statements change the columns of the table created above:

ALTER TABLE public.events ADD COLUMN qty integer;
ALTER TABLE public.events ALTER COLUMN qty TYPE bigint;
ALTER TABLE public.events RENAME COLUMN qty TO quantity;
ALTER TABLE public.events DROP COLUMN quantity;

A column takes the same type map as at creation, though an array column cannot be added, because duckdb-iceberg does not add a nested column to an Iceberg table. A type change is limited to the widening Iceberg accepts: integer to bigint, real to double precision, date to timestamp, and a wider numeric precision. An added column takes a name and a type only: a default, a constraint, a collation, or a storage or compression option is refused, as is a USING or COLLATE clause on a type change, and a column change shares an ALTER TABLE only with other column changes. A vector column is neither added nor changed, a table adopted read-only refuses every column change, and an object built on the view, such as another view, makes a column change fail. In a mesh, the change runs on one node: the Iceberg table is shared, and the rebuilt view and registry row replicate.

Adopting a Table That Already Exists in the Catalog

A table another engine wrote needs no provisioning, only a wrapper view and a registry row. coldfront.adopt_iceberg_table() reads the schema from the catalog and builds both, so the call names the relation and nothing else:

SELECT coldfront.adopt_iceberg_table(
    p_schema    => 'public',
    p_table     => 'orders',
    p_namespace => 'lake'
);

p_schema is the PostgreSQL schema the wrapper view goes in, and it must exist. p_namespace is the Iceberg namespace the table lives in, which need not exist as a PostgreSQL schema; it defaults to p_schema when omitted. The view takes the table's name.

Adoption is read-only by default, so reading someone else's lake table cannot become writing it by accident. Passing p_writable => true enables the same INSERT, UPDATE, DELETE and MERGE rewrite a created table gets:

SELECT coldfront.adopt_iceberg_table('public', 'orders', 'lake',
                                     p_writable => true);

Because Iceberg records no PostgreSQL type, an adopted column reads as whatever its storage type maps to; a jsonb column comes back as text. Pass p_types to restore one, provided the override maps to the type the catalog already stores:

SELECT coldfront.adopt_iceberg_table('public', 'orders', 'lake',
                                     p_writable => true,
                                     p_types    => '{"meta":"jsonb"}'::jsonb);

Adoption binds the name once. A second call under a registered name is refused whatever its arguments; to enable writes or change an override, release the table with coldfront.release_iceberg_table() and adopt it again. A non-superuser deployment runs coldfront.grant_app_access() after adopting, because the grant reads the registry at call time.

In a mesh one node adopts. The wrapper view replicates through Spock's ddl_sql repset and the registry row through the default repset (see Configuring a Distributed Deployment), so every peer reads and writes the table under the same name, and a peer's own adopt is refused as already registered. Adoption takes the table's bakery claim around that check and the registry write, so two nodes adopting the same table at the same moment are serialized: the second waits, then reads the first's row and refuses, rather than both registering the one table. A writer outside ColdFront is outside the bakery and can still collide at Lakekeeper.

One relation can be registered per Iceberg table. A namespace that needs quoting, such as Lake-EU, works.

Handing an Adopted Table Back

An adopted table is released rather than dropped, because ColdFront does not own it. coldfront.release_iceberg_table() removes the wrapper view, the registry row and the relation's vector configuration, and performs no Iceberg I/O, so the table keeps every row and stays in the catalog:

SELECT coldfront.release_iceberg_table('public', 'orders');

Release refuses a tiered registration, because removing one would leave its cold rows unreachable while the hot table returned under the relation's name. drop_iceberg_table() refuses a relation adopted read-only, for the mirrored reason: read access gives no authority to destroy.