- LIMIT/OFFSET is not optimal for paging through large Parquet files; file_row_number is 2.53x faster and more reliable.
- OFFSET can return wrong rows (missing/duplicate) when preserve_insertion_order is off and multiple threads are used.
- Use file_row_number with page boundaries from parquet_metadata to skip row groups efficiently.
- Performance benefit depends on row group count; single row group files show little improvement.
- DuckDB internally rewrites OFFSET into a row-number lookup with semi-join for pages ≤ 1,000,000 rows, but cost still climbs.
- For sorted keys, WHERE id >= ? AND id < ? can be as fast as file_row_number.
- Validate exports using hash aggregation (crypto_hash_agg) instead of row counts to catch missing/duplicate rows.
- Consider streaming or presigned URLs before pagination, as paging adds overhead.
- Key settings: preserve_insertion_order, late_materialization_max_rows, row group size.