Exploring Tiered Storage

Tiered storage is a brownfield retrofit: you begin with a plain PostgreSQL database full of data, add ColdFront to it, and let the archiver relocate the cold majority to object storage - without migrating to a new database or changing a line of application SQL.

Run the steps in Setting Up the Stack before you start this demo.

The following table shows the eleven steps this demo covers:

Starting the Stack

Setup starts the infrastructure: PostgreSQL (your database), plus Lakekeeper and SeaweedFS. The cold-storage side sits idle until you point ColdFront at it in Step 5. Run the docker compose up command shown in Setting Up the Stack. Then confirm that the db, lakekeeper-db, and lakekeeper services report healthy:

docker compose -f examples/walkthrough/docker-compose.yml ps

PostgreSQL is your existing database. The catalog and object store are also running - that is the cold-storage side, unused until Step 5. Locally the store is SeaweedFS; in production it is AWS S3, GCS, or Azure ADLS Gen2.

Creating a Table and Loading Months of History

This step stands in for the database you already run: an ordinary range-partitioned PostgreSQL table, filled with months of accumulated data. Nothing here is ColdFront-specific.

If you are running the cells from this page, skip ahead - every SQL cell below opens its own connection. Following along in a local shell instead, connect once with the walkthrough credentials (notices suppressed so the output stays clean) and paste each SQL block into the session:

PGOPTIONS='-c client_min_messages=warning' \
  psql -h localhost -p 5432 -U coldfront -d coldfront

Create the partitioned table with 25 monthly partitions covering about two years of history plus the current month:

SET search_path = public;

CREATE TABLE events (
    id     bigint GENERATED BY DEFAULT AS IDENTITY,
    ts     timestamptz NOT NULL,
    status text,
    data   jsonb,
    PRIMARY KEY (id, ts)
) PARTITION BY RANGE (ts);

DO $do$
DECLARE m date;
BEGIN
  FOR i IN 0..24 LOOP
    m := (
      date_trunc('month', now())
      - make_interval(months => 24 - i)
    )::date;
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I'
      ' PARTITION OF events'
      ' FOR VALUES FROM (%L) TO (%L)',
      'events_p_' || to_char(m, 'YYYY_MM'),
      m,
      (m + interval '1 month')
    );
  END LOOP;
END $do$;

The loop creates 25 monthly partitions spanning about two years. Months older than 30 days will tier to cold; the last month or two stays hot.

Insert approximately one million rows spread evenly across the partition window (roughly two years, 730 days, from now()):

INSERT INTO events (id, ts, status, data)
SELECT i,
       now() - ((1000000 - i) * (interval '730 days' / 1000000)),
       (ARRAY['ok','warn','error'])[1 + i % 3],
       jsonb_build_object('level', (ARRAY['info','warn','error'])[1 + i % 3],
                          'region', 'us-east-1', 'latency_ms', (i % 500))
FROM generate_series(1, 1000000) i;

The interactive guide asks for the row count instead of loading one million rows. It offers a suggested size that fits Docker's free disk with headroom (the default), 1M, 10M, or 50M rows, or a custom count. When a size would not fit, the guide warns and asks again.

Confirm all rows landed:

SELECT count(*) FROM events;

The count confirms that all rows landed:

  count
---------
 1000000

Months of data now live in an ordinary PostgreSQL table - no ColdFront involved yet.

Seeing the Problem

This step measures how much hot Postgres storage that data occupies and confirms that none of it is in lower-cost storage yet. This is the baseline you will compare against after tiering in Step 8.

pg_total_relation_size() on a partitioned parent counts only the empty parent itself and reports zero. Sum across pg_partition_tree to get the true heap size:

SELECT pg_size_pretty(
    pg_total_relation_size('events') +
    COALESCE((
        SELECT sum(pg_total_relation_size(relid))
        FROM pg_partition_tree('events')
        WHERE relid <> 'events'::regclass
    ), 0)
) AS hot_size;

The query returns:

 hot_size
----------
 152 MB

Every row - all one million - occupies hot, expensive primary storage, and the table only grows. Remember this figure; Step 8 shows where it goes after tiering.

Enabling the Extensions

This step retrofits tiering onto the database you already have, using two extensions. pg_duckdb gives PostgreSQL an in-process engine that can read Parquet in object storage. coldfront adds the layer that routes each query to the right tier and rewrites DML. There is no migration and no new database - these install onto the running one.

Run the following SQL to create both extensions:

CREATE EXTENSION IF NOT EXISTS pg_duckdb;
CREATE EXTENSION IF NOT EXISTS coldfront;

Confirm both are installed:

\dx

The output lists both:

                                                            List of installed extensions
   Name    | Version | Default version |   Schema   |                                          Description
-----------+---------+-----------------+------------+------------------------------------------------------------------------------------------------
 coldfront | 1.0     | 1.0             | coldfront  | Transparent tiered storage: route DML on tiered views across hot (PG) and cold (Iceberg) tiers
 pg_duckdb | 1.1.0   | 1.1.0           | public     | DuckDB Embedded in Postgres
 plpgsql   | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
(3 rows)

Two extensions - that is the entire ColdFront install. There is no sidecar, no proxy, and no data movement yet.

Pointing ColdFront at the Object Store

This step tells ColdFront where cold data goes and how to authenticate to it. The credentials below are throwaway values for the local SeaweedFS emulator. In production, pass your real bucket's key, secret, and endpoint here - application SQL is unchanged when you swap stores.

Register the local SeaweedFS credentials:

SELECT coldfront.set_storage_secret(
  'admin', 'adminsecret', 'seaweedfs:8333'
);

With the credential stored, confirm the warehouse that setup created (wh) exists:

curl -s localhost:8181/management/v1/warehouse \
  | grep -o '"name":"wh"'

The response confirms that the warehouse exists:

"name":"wh"

Showing the Archiver Policy

The archiver policy is one rule in a YAML file: data older than 30 days belongs in low-cost object storage; the most recent data stays hot in PostgreSQL. Nothing moves yet - this step shows the boundary.

Inspect the archiver configuration:

cat examples/walkthrough/config/archiver.yaml

The file reads:

# ColdFront walkthrough archiver config: Demo 1 (tiered).
#
# An import input: `docker compose run --rm --no-deps archiver import --config
# /config/archiver.yaml` writes it into the server once, the table into
# coldfront.partition_config and the store into coldfront.storage_secret. The
# archiver then runs with no file, connecting from the PG* environment the
# compose service sets. Endpoints are compose SERVICE NAMES, since the archiver
# runs inside the compose network. hot_period "30 days" keeps the current month
# hot and tiers the older months to Iceberg/S3, deterministic because the
# walkthrough seeds now()-relative timestamps.
s3:
    endpoint: "seaweedfs:8333"
    region: "us-east-1"
    access_key: "admin"
    secret_key: "adminsecret"
    use_ssl: false
    url_style: "path"
archiver:
    tables:
        - source_table: events
          partition_period: monthly
          hot_period: "30 days"

The hot_period: 30 days value is the hot/cold line. Any partition whose data is entirely older than 30 days will move to object storage when the archiver runs.

Registering the Table, Then Running the Archiver

The archiver reads its configuration from the server. import writes the YAML into it once: the table into coldfront.partition_config and the store into coldfront.storage_secret. Then run the archiver, which connects from the PG* environment the compose service sets and needs no file: it moves every partition older than 30 days out of the PostgreSQL heap into Parquet files in object storage and rebuilds events as a unified view over the hot remainder and the cold data.

The following commands import the table and run the archiver:

docker compose \
  -f examples/walkthrough/docker-compose.yml \
  run --rm --no-deps archiver import --config /config/archiver.yaml

docker compose \
  -f examples/walkthrough/docker-compose.yml \
  run --rm --no-deps archiver

The --no-deps flag reuses the already-running, data-loaded db container; without it, docker compose run re-evaluates depends_on and can recreate db from its config hash, restarting the database in the middle of the demo (the pgdata volume keeps the rows).

The archiver connects inside the Compose network (service name db, not localhost), detaches the partitions older than 30 days from PostgreSQL, exports them to Iceberg via pg_duckdb, and replaces the events table with a unified view that queries both tiers.

Proving the Cold Rows Are Really in S3 as Parquet

The iceberg_metadata() table function resolves its argument as a filesystem path, so a REST-catalog table cannot be addressed by name directly. Resolve the table's metadata.json S3 location from the Lakekeeper catalog first, then point iceberg_metadata() at that path:

# Resolve the warehouse id and the table's metadata location.
WH_ID=$(curl -s http://localhost:8181/management/v1/warehouse \
  | grep -o '"warehouse-id":"[^"]*"' \
  | head -1 | cut -d'"' -f4)

META_LOC=$(curl -s \
  "http://localhost:8181/catalog/v1/${WH_ID}/namespaces/public/tables/events" \
  -H 'accept: application/json' \
  | grep -o '"metadata-location":"[^"]*"' \
  | head -1 | cut -d'"' -f4)

echo "$META_LOC"

# Query the Parquet data files registered in that Iceberg snapshot.
psql "postgresql://coldfront@localhost:5432/coldfront" \
  -v ON_ERROR_STOP=1 -P pager=off <<SQL
SELECT file_path
FROM iceberg_metadata('${META_LOC}')
WHERE file_path LIKE '%.parquet'
LIMIT 3;
SQL

The query returns:

s3://iceberg/<table-uuid>/metadata/00023-<uuid>.gz.metadata.json

 file_path
-----------------------------------------------------------------
 s3://iceberg/.../data/month_ts_2=656/01a0ec3d-8abb-....parquet
 s3://iceberg/.../data/month_ts_2=657/01a0ec3d-8b52-....parquet
 s3://iceberg/.../data/month_ts_2=658/01a0ec3d-8bd4-....parquet

Real .parquet objects are in the bucket. The cold rows are no longer in PostgreSQL - they are objects in object storage. Each month's files sit under their own month_ts_<n>= directory: the cold table is partitioned by month, the way the hot table is.

Proving the Table Changed Shape

Inspect the relation type:

\d events

events is now a view. The _events table holds only the hot remainder. Run the following query to see the recorded hot/cold cutoff:

SELECT * FROM coldfront.archive_watermark;

Checking Where the Data Lives Now

This step accounts for every row after tiering: how many are still hot in PostgreSQL, how many are now cold in object storage, and how much space each tier uses. This is the direct payoff against the Step 3 baseline.

Count the hot rows still in the PostgreSQL heap:

SELECT count(*) AS hot_rows FROM _events;

Measure the hot heap size (sum over the partition tree, as in Step 3):

SELECT pg_size_pretty(
    pg_total_relation_size('_events') +
    COALESCE((
        SELECT sum(pg_total_relation_size(relid))
        FROM pg_partition_tree('_events')
        WHERE relid <> '_events'::regclass
    ), 0)
) AS hot_size;

Count the total rows across both tiers and derive the cold count:

SELECT count(*) AS total_rows FROM events;

Together, the three queries give the following hot/cold split:

  Tier                    Rows         Postgres heap
  ----------------------  -----------  ----------------
  Hot  (Postgres)             ~42,000  ~6.6 MB
  Cold (Parquet in S3)       ~958,000  0 bytes in PG
  ----------------------  -----------  ----------------
  Total                     1,000,000

The hot share depends on the day you run the demo. The archiver moves a monthly partition to cold storage once its end is at least 30 days in the past, so the previous month stays hot until the 31st. A run on the 1st or the 31st keeps about 42,000 rows hot, and a run late in any other month about 83,000. Before tiering (Step 3), the table held one million rows, all hot, in approximately 152 MB of Postgres heap. After tiering, only the last month or two remains in PostgreSQL; over 90% of the hot storage is gone while the total row count is unchanged.

Querying Across Tiers

This step runs one ordinary query against events that spans both tiers, then queries the hot-only table for contrast. The application issuing this query cannot tell which rows came from the heap and which came from object storage.

Query the whole table - hot and cold together:

SELECT count(*) AS total FROM events;

Query only the hot heap:

SELECT count(*) AS hot FROM _events;

Retrieve specific rows from a cold month:

SELECT id, ts, status
FROM events
WHERE ts < date_trunc('month', now()) - interval '3 months'
ORDER BY ts
LIMIT 3;

The query returns:

 id |              ts               | status
----+-------------------------------+--------
  1 | 2024-10-01 14:44:24.687844+00 | warn
  2 | 2024-10-01 14:45:27.759844+00 | error
  3 | 2024-10-01 14:46:30.831844+00 | ok
(3 rows)

The timestamps track your load: the rows are 63.072 seconds apart (730 days divided by one million), and row 1 sits one such step short of 730 days before the load. It is one table and one query, and no application change is required.

Writing to Cold Data

This step takes a specific row from a cold (archived) month and updates it through the same events view. Watch for what does not happen: no rehydration of the partition back into PostgreSQL, no ETL job, no restore from archive, and no second tool.

Capture a cold row's id in a separate query. A sub-select over the tiered view inside the same DML statement is rejected by the extension, because the rewrite retargets the leading reference:

SELECT id, ts, status
FROM events
WHERE ts < date_trunc('month', now()) - interval '2 months'
ORDER BY ts
LIMIT 1;

The query returns:

 id |              ts               | status
----+-------------------------------+--------
  1 | 2024-10-01 14:44:24.687844+00 | warn

Update the archived row through the same table using the captured id:

UPDATE events SET status = 'corrected' WHERE id = 1;

Read the row back immediately:

SELECT id, ts, status FROM events WHERE id = 1;

The updated row reads:

 id |              ts               |  status
----+-------------------------------+-----------
  1 | 2024-10-01 14:44:24.687844+00 | corrected

The row's status flipped warn to corrected. That row is still sitting in object storage - ColdFront wrote through to it directly.

Confirming the Edit Persisted

This step opens a fresh psql connection (nothing cached from the session that did the write) and re-checks the row, the total row count, and the hot heap size. This confirms that the cold edit is durable persistent state and that the data did not quietly return to PostgreSQL to make the edit possible.

Each runnable cell on this page already opens a fresh connection, so cell-runners can run the checks below. In a local shell, open a new terminal and connect with a fresh session first:

PGOPTIONS='-c client_min_messages=warning' \
  psql -h localhost -p 5432 -U coldfront -d coldfront

Confirm the archived row is still corrected:

SELECT id, status FROM events WHERE id = 1;

The row reads:

 id |  status
----+-----------
  1 | corrected
(1 row)

Confirm the total row count is unchanged:

SELECT count(*) AS total_rows FROM events;

The count confirms the total is unchanged:

 total_rows
------------
    1000000

Confirm the hot heap is still small (the data did not rehydrate):

SELECT pg_size_pretty(
    pg_total_relation_size('_events') +
    COALESCE((
        SELECT sum(pg_total_relation_size(relid))
        FROM pg_partition_tree('_events')
        WHERE relid <> '_events'::regclass
    ), 0)
) AS hot_size;

The query returns:

 hot_size
----------
 6616 kB
(1 row)

In the fresh connection, the archived row is still corrected, all one million rows are present, and the hot heap is still small. The data never came back to PostgreSQL. Over 90% of the original storage now lives in object storage as Parquet, and that data is still a normal, writeable part of the table - corrected in place, no rehydration, no separate system.

Next Steps

To go further with ColdFront, consult the following guides:

• The Decoupled Mode Demo stores a table in Iceberg from the first row and adopts a table that another engine wrote.
• The Object Store Setup guide takes you from an empty bucket to a working cold tier on AWS S3.
• The Architecture overview explains the shared mechanics and links to the per-mode deep dives.
• The Tearing Down the Stack section describes how to stop the stack and remove its data.