DuckDB Parquet Paging: file_row_number vs OFFSET, and a Correctness Trap

For a stateless API that pages through a 20-million-row Parquet file across load-balanced workers, each page request has to be reconstructible without server-side state. This piece compares two DuckDB approaches: standard LIMIT/OFFSET and filtering on file_row_number ranges via read_parquet. On a file with 163 row groups, the row-range approach was 2.53x faster than OFFSET, consistently across 37 runs — but the benefit depends entirely on the file having many row groups; a single-row-group file sees almost no gain.

The surprising part is that DuckDB already rewrites OFFSET queries internally into a file_row_number-based hash join, giving similar row-group skipping — so OFFSET isn't quadratic as commonly assumed. That rewrite, however, only fires when LIMIT is under roughly 1,000,000 rows (governed by an internal constant and the late_materialization_max_rows setting); beyond that threshold, or with any added WHERE clause, the gap widens to 5-6x as DuckDB falls back to scanning everything before the page.

The more serious finding is correctness, not speed. LIMIT/OFFSET makes no ordering guarantee without ORDER BY. With insertion-order preservation disabled and multiple threads scanning, total row counts still landed exactly on 20,000,000 — yet roughly 30% of the actual data was either missing or duplicated, silently, with no errors thrown. The takeaway: row counts can't detect this kind of corruption; verifying exports requires hashing rows, not counting them.