In DuckLake the catalog database is the lake, so run it like one

Operating notes for DuckLake 1.0: the Postgres catalog holds rows the bucket never sees, so deleted files have to outlive the oldest backup you'd restore.

Ingest writers Catalog database Parquet files Query readers Catalog backup Restore drill Maintenance job Delayed cleanup snapshots, file list, inlined rows in the bucket checks files exist flush, expire, merge
Writers, catalog database, Parquet files and readers on one line; backup and restore drill branch up, maintenance and cleanup branch down.
On this page
  1. What only the catalog knows
  2. Crumbline’s first drill
  3. The rule, and the schedule under it
  4. Proving the catalog and the bucket agree
  5. The drill on one page

By default, DuckLake’s CHECKPOINT waits two days before it removes a data file that has been scheduled for deletion. A sensible Postgres backup policy reaches back a week or two. A lake can lose tables in the gap between those two numbers, and the first place it shows is a restore drill.

DuckLake reached 1.0 on April 13 with a promise of backward compatibility. Last July I wrote an early look at the design and ended it by saying the metadata database is the thing you can’t lose. Now that the format is stable enough to run, these are my notes on what running it asks of you. They come from reading the 1.0 maintenance docs closely and working them through on an invented lake, so treat the numbers as illustration.

What only the catalog knows

The early look covered why losing the catalog loses the lake. At 1.0 there’s a second reason: the catalog now holds data too.

Inlining is on by default: an insert, update or delete of 10 rows or fewer goes into a table in the catalog, and no Parquet file gets written at all. I think that’s a good default. It also ends the idea that the catalog is “just metadata”. Until something flushes those rows, the catalog database is their only home, and bucket versioning can’t help with rows the bucket never received.

So the lake’s runbook is a database runbook: backups with point-in-time recovery, and restore drills you actually run. The one new piece is keeping the database and the bucket in agreement across a restore.

Crumbline’s first drill

Crumbline is a bakery-delivery cooperative I made up, and every figure here is mine. It runs 90 tables and 220 GB of Parquet, with the catalog on a 2-vCPU Postgres. Oven sensors write 6 rows every 20 seconds, which is under the inlining threshold, and nobody ever scheduled a flush. The catalog has grown to 3 GB, and 1.1 GB of that is sensor rows that exist nowhere else.

The maintenance job looked reasonable. Snapshots expired after 7 days, small files were merged nightly, and files were removed two days after being scheduled for deletion. Postgres was dumped every night and the dumps were kept for 14 days.

Then the team ran its first restore drill and picked the dump from 10 days back. The restore was clean. The restored catalog listed 48,900 files, and 312 of them were no longer in the bucket. Seventeen tables failed to read.

The restore did exactly what it was asked to do. A catalog from 10 days ago describes the lake as it was 10 days ago, including small files that were merged the following night and deleted two days after that. As I read the docs, a merge doesn’t wait for snapshot expiry. The merged file carries the history of the files it replaces, so the originals are scheduled for deletion right away. The 7-day snapshot window protected nothing here. Only the cleanup delay did, and it was 2 days against a 14-day backup window.

The nightly dump had a second problem. Restoring from it would lose up to a day of oven readings, with no Parquet to replay them from.

The rule, and the schedule under it

Deleted files must outlive the oldest catalog backup you would restore. I’d write that above the schedule itself, so nobody tunes the delay down later without seeing it.

-- RULE: M >= catalog backup window + 1 day
--   M = days a file waits between "scheduled for deletion" and deleted
--   Catalog backups reach back 14 days, so M = 15. Change them together.
--   N = 7 is snapshot retention (time travel). It is not a safety margin.

INSTALL ducklake;
INSTALL postgres;

-- S3 credentials come from a DuckDB secret created elsewhere.
ATTACH 'ducklake:postgres:dbname=lake_catalog host=catalog-pg.internal' AS lake
  (DATA_PATH 's3://crumbline-lake/');

-- Run once. A bare CHECKPOINT then follows the same rule
-- (delete_older_than defaults to 2 days).
CALL lake.set_option('expire_older_than', '7 days');
CALL lake.set_option('delete_older_than', '15 days');

-- Every 15 minutes: move inlined rows out of the catalog into Parquet.
CALL ducklake_flush_inlined_data('lake');

-- Nightly, in this order.
CALL ducklake_expire_snapshots('lake', older_than => now() - INTERVAL '7 days');
CALL ducklake_merge_adjacent_files('lake');
CALL ducklake_cleanup_old_files('lake', older_than => now() - INTERVAL '15 days');

Crumbline’s fix was three changes. Cleanup waits 15 days, which costs 6% more storage, about 13 GB. The flush runs every 15 minutes, so only a few hundred sensor rows live solely in the catalog at any moment. And the nightly dump gave way to continuous archiving, which turns any second of the last 14 days into a restore point.

Give this schedule to one process. Maintenance commits snapshots like any other writer, and the argument for one writer per table covers it too.

Proving the catalog and the bucket agree

A restore that completes tells you Postgres is fine. It says nothing about the lake. This query does. It attaches a catalog as a plain Postgres database, lists the bucket, and returns every file the catalog references that isn’t there.

-- Point this at the restored copy in a drill, or at production as a weekly check.
ATTACH 'dbname=lake_catalog host=drill-pg.internal' AS cat (TYPE postgres, READ_ONLY);

WITH in_bucket AS (
  SELECT parse_filename(file) AS file_name
  FROM glob('s3://crumbline-lake/**/*')
),
referenced AS (
  SELECT table_id, path, 'data' AS kind FROM cat.public.ducklake_data_file
  UNION ALL
  SELECT table_id, path, 'delete' AS kind FROM cat.public.ducklake_delete_file
)
SELECT coalesce(t.table_name, '(dropped table)') AS table_name,
       r.kind,
       r.path
FROM referenced AS r
LEFT JOIN cat.public.ducklake_table AS t
  ON t.table_id = r.table_id AND t.end_snapshot IS NULL
WHERE NOT EXISTS (
  SELECT 1 FROM in_bucket AS b WHERE b.file_name = parse_filename(r.path)
)
ORDER BY table_name, r.path;

-- Step 4 of the drill: the same restored catalog, attached as a lake.
ATTACH 'ducklake:postgres:dbname=lake_catalog host=drill-pg.internal' AS drill (READ_ONLY);

It matches on file name because DuckLake puts a UUID in every file name, and a stored path can be relative or absolute. The name is the part that’s the same either way. Zero rows is a pass. I wrote the query from the catalog spec and haven’t pointed it at a broken lake, so check the column names against your own catalog before you trust an empty result.

The drill on one page

  1. Pick a restore point near the far end of the backup window. Yesterday proves very little.
  2. Restore the catalog to that time on a scratch Postgres instance, never over production.
  3. Run the check query against the restored copy and the real bucket. Expect zero rows.
  4. Attach the restored copy as a read-only lake and run SELECT count(*) on every table.
  5. Write down how long the restore took, how many rows the check returned, and the newest sensor reading the restored catalog holds.
  6. Drop the scratch instance.

A real restore needs a step zero: stop the maintenance job first. Files written after the restore point are unknown to the restored catalog, and orphan cleanup would treat them as garbage. Decide what to do with them before anything gets the chance to delete them.

Crumbline runs the drill monthly now, and it passes. I’d still turn on bucket versioning as a second net, with old versions kept at least as long as the cleanup delay. Just don’t mistake it for the first one. Versioning can bring a deleted file back, but only the catalog can tell you which files a table needs.