Feature Request: pin a table to the catalog, and push filters down to inlined data

[tanejagagan]

Tested with: DuckDB v1.5.5, ducklake extension d8a1881e, catalogs on both a
local DuckDB file and PostgreSQL 16.

Data inlining makes small writes land in the catalog database instead of creating tiny
Parquet files. For a workload of many small, frequent writes this is exactly the right
behaviour, and we would like to rely on it for a table that is intended to stay
catalog-resident: a per-producer high-water-mark table used for ingestion de-duplication.
It is small (one row per producer), read on every batch, and updated constantly.

Two things prevent us from using it that way.

---

1. There is no supported way to keep a table inlined

Today inlined data stays inlined indefinitely, but only because nothing currently flushes it
— and that is a bug, not a contract. The documentation states that CHECKPOINT should flush
inlined data, and #1368 tracks the fact that it does not. When #1368 is fixed, any design
that relies on data staying inlined will silently start losing that property.

That is exactly why an explicit opt-out is needed: the behaviour we would like to depend on
is currently indistinguishable from a defect that is scheduled to be repaired.

We confirmed that none of the maintenance routines move inlined data at present:

Called	Effect on Parquet	Inlined rows
ducklake_merge_adjacent_files	3 files merged to 1	unchanged
ducklake_expire_snapshots	—	unchanged
ducklake_cleanup_old_files	removed 2 orphaned files	unchanged
ducklake_rewrite_data_files	no-op on this fixture	unchanged
CHECKPOINT	—	unchanged

Only ducklake_flush_inlined_data moves it.

Nor is there an option to express the intent. data_inlining_row_limit is documented as
"Maximum amount of rows to inline in a single insert" — it governs the write path and says
nothing about residency. Every name we probed for a residency option is rejected with
Unsupported option:

data_inlining_permanent   keep_inlined            data_inlining_keep
inline_permanently        data_inlining_mode      data_inlining_enabled
auto_flush_inlined_data   flush_inlined_data

(For contrast, data_inlining_row_limit, parquet_compression, parquet_row_group_size,
target_file_size and parquet_version are all accepted, so the probe distinguishes real
option names from invented ones.)

So a design that depends on rows staying in the catalog is depending on a defect. Once
CHECKPOINT flushes as documented, that design breaks with no warning and no way to opt out.

Request

A persisted option, e.g. data_inlining_pinned, settable at GLOBAL and TABLE scope
through the existing mechanism:

CALL my_lake.set_option('data_inlining_pinned', true, table_name => 'watermark');

Semantics: data for a pinned table is never flushed to Parquet implicitly. An explicit
ducklake_flush_inlined_data could either refuse or require a force flag.

Option storage already supports what is needed — ducklake_options persists values with
GLOBAL/TABLE scope and they survive re-ATTACH — so this is mechanically small. The
substantive part is that it becomes a documented guarantee other DuckLake implementations
must honour, since a writer unaware of the flag would otherwise flush the table.

---

2. Filters are not pushed down to inlined data

Projection pushdown already works — this is only about filters. Capturing the SQL
DuckLake issues against a PostgreSQL catalog (log_statement=all), for
SELECT last_batch_id FROM dl.wide WHERE producer_id = 'prod-42' on a table with columns
producer_id, last_batch_id, c1, c2, c3, c4:

COPY (SELECT "begin_snapshot", "end_snapshot", "producer_id", "last_batch_id", "row_id"
      FROM "public"."ducklake_inlined_data_1_1"
      WHERE "begin_snapshot" <= '2')
TO STDOUT (FORMAT "binary");

c1 through c4 are correctly omitted, and the projection tracks the query — a second
query, SELECT c3 FROM dl.wide WHERE c4 = 84, sends "c4", "c3" and drops the rest. That
part behaves well.

The filter is the gap. producer_id = 'prod-42' never reaches PostgreSQL; the only
predicate pushed is the snapshot visibility bound begin_snapshot <= '2'. Every row of the
inlined table is streamed to DuckDB and filtered there.

The consequence at scale: a point lookup reads the whole inlined table.

Setup: 100,000 rows inlined in a PostgreSQL catalog, with an index created on the inlined
table:

CREATE INDEX idx_wm ON ducklake_inlined_data_1_1(producer_id);
ANALYZE ducklake_inlined_data_1_1;

50 point lookups issued through DuckLake (SELECT last_batch_id FROM dl.wm WHERE producer_id = 'prod-…'), measured with pg_stat_user_tables:

seq_tup_read | idx_scan | idx_tup_fetch
   5,000,000 |        0 |             0

5,000,000 = 50 x 100,000. The entire table is read for every lookup and filtered in
DuckDB; the index is never consulted.

The same 50 lookups issued directly to PostgreSQL, as a control:

seq_tup_read | idx_scan | idx_tup_fetch
           0 |       50 |            50

Index Scan using idx_wm on ducklake_inlined_data_1_1
  (actual time=0.009..0.009 rows=1)
  Index Cond: (producer_id = '\x70726f642d3432'::bytea)

So the index is perfectly usable — the predicate simply never reaches PostgreSQL. This is
not a query-formulation problem: expressing the lookup through a DuckDB variable
(SET VARIABLE pid = 'prod-42' then getvariable('pid')) produces the identical full
scan, seq_tup_read=100000, idx_scan=0.

Request

Push equality/range predicates on inlined data down to the catalog database when the
catalog is a real DBMS. Failing that, some documented way to make a point lookup on an
inlined table better than O(table).

#859 addressed a structurally similar problem — a full table scan of
ducklake_file_column_stats on every query against a PostgreSQL metadata backend — so there
is precedent for pushing work into the catalog rather than pulling rows out of it.

Why this belongs with (1)

Pinning without pushdown gives a guarantee that cannot be used: the table stays in the
catalog forever and every lookup remains a full scan. Pushdown without pinning gives fast
lookups that may evaporate the moment a flush policy is introduced. Together they make an
inlined table usable as what it physically already is — a table in a database.

---

Workaround we are using, and its cost

Partitioning the table by hash(key) % N with inlining disabled does prune on read:

WHERE bucket = 10 AND producer_id = 'prod-42'   ->  Total Files Read: 1
WHERE producer_id = 'prod-42'                    ->  Total Files Read: 16

partitioned : 8.3 ms/lookup
inlined     : 30.0 ms/lookup

This works and is supported, but it forces us out of inlining entirely for exactly the
table whose access pattern inlining suits best — many tiny writes — and it puts the bucket
computation in the application.

---

Minor observations from the same investigation

• Inlined tables are named ducklake_inlined_data_<table_id>_<schema_version>, registered
  in ducklake_inlined_data_tables. Schema evolution creates an additional inlined table
  per schema version, so one logical table accumulates several. Any index created by hand
  on one of them is not carried to the next version.
• On a PostgreSQL catalog, VARCHAR columns are stored as bytea (visible in the
  Index Cond above). Worth documenting for anyone reading catalog tables directly.