● Covered by 1 source · 1 reportLow impact1 neutral
Paging Through Parquet Files in DuckDB: file_row_number vs. OFFSET
Key points
- Paging large Parquet files is necessary to avoid API response limits.
- DuckDB's `file_row_number` option allows filtering by physical row position.
- Using `file_row_number` was 2.53x faster than `OFFSET` in tests.
- Performance gains depend on the Parquet file having multiple row groups.
The Challenge of Paging Large Datasets
Returning large datasets from a service can exceed API response size limits, such as Lambda's 6 MB or Cloud Run's 32 MiB. To address this, data is often paginated, with clients requesting content one page at a time. Each request must be self-contained, as workers behind a load balancer cannot maintain server-side state for resuming a session.
Comparing OFFSET and file_row_number
The common approach for pagination is using `LIMIT` and `OFFSET`. However, `OFFSET` can be inefficient for large offsets, as it may require counting past many rows to find the desired page. An alternative in DuckDB is to use the `file_row_number` option with `read_parquet`, which provides each row's physical position, allowing filtering on a specific row range.
Performance Differences
Tests on a 20-million-row Parquet file with 163 row groups showed that the `file_row_number` method completed 2.53 times faster than using `OFFSET` across the entire file. This performance improvement is due to DuckDB's ability to use the file's footer to identify and skip decompression of row groups that do not contain the requested row range.
Importance of Row Groups
The efficiency of the `file_row_number` approach is directly tied to the number of row groups within the Parquet file. If a Parquet file is written as a single large row group, DuckDB cannot skip work, and the performance benefits of `file_row_number` are negated. Users should check the number of row groups in their files before implementing this optimization.
✨ This summary was generated by AI from the outlets' reporting listed below. It is not independently verified and may contain errors — check the original sources. How BrevFeed works →
The daily brief
One email each morning: the day's tech stories, clustered across outlets and summarized. No account needed.
One email a day. Unsubscribe in one click, any time.
Today's brief
Spend a few minutes, get the whole day. Every topic's top stories in one hands-free rundown — listen, watch, or read the transcript.
▶ Play today's brief
New every morning, and the back catalogue is archived by date.
Primary sources
GitHub duckdb/duckdbReporting from
This article compares two methods for paging through large Parquet files in DuckDB: using `OFFSET` and `LIMIT` versus filtering by `file_row_number`. It finds that `file_row_number` can be significantly faster for files with many row groups because DuckDB can skip decompression of irrelevant data blocks. This matters for services that need to return paginated data efficiently from large files without exceeding API response size limits.