When handling large Parquet files for API responses, pagination is essential. Traditional LIMIT/OFFSET can be inefficient, requiring DuckDB to count through millions of rows. The article explores using DuckDB's `read_parquet` with `file_row_number` for more efficient row-range filtering.

Testing on a 20-million-row file with 163 row groups, the `file_row_number` approach was 2.53x faster than OFFSET. This speed advantage stems from DuckDB's ability to skip entire row groups that don't contain the requested data, avoiding decompression of unneeded rows. The effectiveness of this method heavily relies on the number of row groups within the Parquet file; files with only one row group offer minimal benefit.

Interestingly, DuckDB's OFFSET implementation is not truly quadratic. It internally rewrites OFFSET queries to use `file_row_number` and semi-joins, effectively skipping row groups. However, this rewrite has a cutoff of 1,000,000 rows or fewer per page. Exceeding this limit or adding a WHERE clause disables the optimization, leading to a significant performance drop. The article recommends explicitly using `file_row_number` for predictable and efficient pagination, especially when dealing with large datasets and multiple row groups.