Collect min/max rowid stats per data file to enable pruning on the rowid meta column

[gtaneja-sentrinox]

Problem

A predicate on the rowid meta column prunes nothing — every query with a rowid filter lists and
opens every data file in the table. rowid is registered as a virtual column
(DuckLakeTableEntry::GetVirtualColumns()), and DuckLakeMultiFileList::AddFilterToPushdownInfo()
returns early for virtual columns, so a rowid filter never reaches the metadata-level pruning path.
The filter is still applied correctly, it just skips nothing.

Nothing in the metadata catalog records which row ids a data file contains, so there is nothing to
prune against. ducklake_data_file has row_id_start but no end, and ducklake_file_column_stats
holds rows only for user columns — I confirmed there is no row for the internal row id column.

Why this matters

rowid is the only stable physical row identifier DuckLake offers, so it is the natural key for a
family of access patterns that are all full scans today: point and range lookups from an application
holding materialized rowid pointers (a secondary or inverted index, an embedding store, a
resumable-processing bookmark), re-reading a row set after a SELECT rowid, ... pass
(WHERE rowid IN (...)), chunked processing via WHERE rowid >= $lo AND rowid < $hi, and semi-joins
against a delta table keyed by rowid. Row ids are broadly assigned in append order, so they are
exceptionally well clustered per file — close to a best case for zone-map pruning.

The part that makes this non-trivial

The obvious derivation — [row_id_start, row_id_start + record_count - 1] — is exact for ordinary
files, but wrong for files carrying an embedded _ducklake_internal_row_id column, which the reader
prefers when present (flushed inlined data, MERGE/UPDATE rewrites, compaction of non-adjacent
files). MERGE ... UPDATE preserves row ids, so the rewritten file's ids are unrelated to its
assigned row_id_start. On ducklake v1.5.2:

ATTACH 'ducklake:meta.db' AS dl (DATA_PATH 'data', METADATA_CATALOG 'm', DATA_INLINING_ROW_LIMIT 0);
CREATE TABLE dl.t(i INTEGER, j INTEGER);
INSERT INTO dl.t VALUES (1,5),(2,5);
MERGE INTO dl.t USING (SELECT 1 AS i, 5 AS j) u ON dl.t.j = u.j WHEN MATCHED THEN UPDATE;

SELECT rowid FROM dl.t ORDER BY rowid;   -- 0, 1  (row ids preserved)

metadata (live file):  record_count=2, row_id_start=2, partial_max=NULL, mapping_id=NULL  → implies [2,3]
parquet footer:        _ducklake_internal_row_id  min=0  max=1                            → actually {0,1}

So a pruning predicate built on row_id_start + record_count would prune the only live file for
WHERE rowid = 0 and return zero rows. And no existing metadata column distinguishes the two kinds
of file, so the derivation cannot even be applied selectively — whether a file has embedded row ids
is only discoverable by opening its Parquet footer, which is exactly the cost catalog-level pruning
exists to avoid. The range therefore needs to be captured at write time (where the Parquet writer
already computes it for embedded files, and where it is trivially derivable for the rest) and stored
in the metadata catalog.

Possible directions

Two approaches that both keep the pruning decision inside the catalog query: store the row id min/max
as a row in ducklake_file_column_stats under a reserved column_id, which needs no DDL or
migration and reuses the existing stats-pruning code path as-is; or add explicit row_id_min /
row_id_max columns to ducklake_data_file alongside the row_id_start they generalize. Happy to
leave the choice and the design to the maintainers — I have a more detailed analysis of the write
paths involved if it's useful.

[gtaneja-sentrinox]

Follow-up: the same gap applies to the snapshot_id meta column, and compaction removes the only file skipping either column gets today.

Tested on DuckDB 1.5.5 (ducklake d8a1881e); same behaviour on 1.5.2 and 2.0.0-alpha41839.

Before compaction a snapshot_id predicate does skip files, but not through the catalog. Every file in the list is still opened; DuckDB's multi-file reader then evaluates the filter against the per-file constant snapshot_id and closes the file without reading data (MultiFileColumnMapper::EvaluateConstantFilters → SKIP_READING_FILE, reported as "Total Files Skipped"). So the footer read is paid for every file and only the data pages are saved.

After compaction even that goes away. ducklake_merge_adjacent_files writes both meta columns into the merged file as real columns:

compacted file columns : [customer_id, v, _ducklake_internal_row_id, _ducklake_internal_snapshot_id]
parquet row-group stats: _ducklake_internal_row_id min=0 max=899, _ducklake_internal_snapshot_id min=3 max=5
catalog row            : begin_snapshot=3, partial_max=5, row_id_start=NULL

Because the column is now present in the file, the constant-filter path no longer applies and the predicate is evaluated per row group instead. Same table, same query:

query	before compaction (12 files)	after compaction (4 files)
WHERE snapshot_id = 4	4 read, 8 skipped	4 read, 0 skipped
WHERE snapshot_id = 99 (matches nothing)	0 read, 12 skipped	4 read, 0 skipped

So after compaction both meta columns are in the same position: correct results, every file opened.

snapshot_id is the easier of the two, because the range is already in the catalog. ducklake_data_file.begin_snapshot is the file's minimum and partial_max (NULL for a single-snapshot file) its maximum; GetTableInsertions already relies on exactly this pair to pick files for a snapshot range. What is missing is only that the file-list query maps a user predicate on the snapshot_id virtual column onto begin_snapshot / COALESCE(partial_max, begin_snapshot), the same way ducklake_file_column_stats min/max are used for user columns. No writer change and no new metadata would be needed.

For rowid the write-time capture described above is still required, since row_id_start is NULL for compacted files and the only range lives in the Parquet footer. If the reserved-column_id row in ducklake_file_column_stats route is taken, storing both internal columns that way would let the existing stats-pruning code treat them exactly like user columns.

Repro (DuckDB 1.5.5):

ATTACH 'ducklake:meta.ducklake' AS lake (DATA_PATH 'data/', DATA_INLINING_ROW_LIMIT 0);
CREATE TABLE lake.main.t (customer_id INT, v INT);
ALTER TABLE lake.main.t SET PARTITIONED BY (customer_id);
INSERT INTO lake.main.t SELECT (i % 4)+1, i FROM range(400) r(i);   -- run 3 times
EXPLAIN ANALYZE SELECT count(*) FROM lake.main.t WHERE snapshot_id = 99;  -- 0 files read, 12 skipped
CALL ducklake_merge_adjacent_files('lake');
EXPLAIN ANALYZE SELECT count(*) FROM lake.main.t WHERE snapshot_id = 99;  -- 4 files read, 0 skipped
SELECT name FROM parquet_schema('data/main/t/customer_id=1/*.parquet');   -- shows _ducklake_internal_snapshot_id in the merged file

[gtaneja-sentrinox]

Third meta column with the same gap: filename. This one is the cheapest to fix, since the catalog already stores the exact value per file.

Same setup as above (DuckDB 1.5.5, ducklake d8a1881e), a table partitioned by customer_id with 63 data files and 6 delete files. Measured with CALL enable_logging('FileSystem', storage => 'memory') so the numbers are actual opens, not the plan's "Total Files Read" counter (which only counts files whose data pages were read).

table scan	distinct files opened	read calls	bytes read
WHERE filename = '<one file>'	69	93	29,379
WHERE filename IN (<3 files>)	69	93	29,379
WHERE customer_id = 10 (for comparison)	6	30	4,734

A filename predicate opens every data file and every delete file in the table and reads each footer before deciding to skip it. The plan reports Total Files Read: 1 for the first query, which is true for data pages only. The customer_id predicate, by contrast, prunes in the catalog query and opens just the 3 matching data files plus their 3 delete files.

Same on the change feed: ducklake_table_insertions(...) WHERE filename IN (<3 of 60 files>) opens all 60 files (60 opens, 60 footer reads), reports 57 skipped, and reads data pages from 3. Joining on filename instead of an IN list behaves identically, since the join-produced dynamic filter goes through the same per-file constant check.

The mechanism is the one already described for rowid: DuckLakeMultiFileList::GetFilterNode discards filters on virtual columns before the file-list query is built, and filename is one. The predicate only survives as a per-file constant check in DuckDB's MultiFileColumnMapper::EvaluateConstantFilters, which runs inside InitializeReader, after CreateReader has opened the file and fetched the footer.

Unlike rowid and snapshot_id, no stats are needed here. ducklake_data_file.path (plus path_is_relative and the data path / table prefix the reader already resolves) is the value the virtual column returns, so a filename predicate can be rendered directly against it in the file-list query — equality, IN, and prefix/LIKE all map onto plain string conditions. Together with the begin_snapshot / partial_max pair for snapshot_id, that would make two of the three meta columns prunable from existing metadata alone, leaving only rowid needing write-time capture.

Why this matters in practice: a consumer that already knows which files it wants — from ducklake_list_files, from the metadata tables, or from a previous scan's filename output — has no way to hand that list back to DuckLake without paying one open plus one footer GET for every file it does not want. read_parquet(list) avoids that but drops delete files, column mapping, inlined data and encryption.