DuckDB's file_row_number() Solves Deep Pagination Bottlenecks in Parquet Files

Developers building data-heavy APIs with DuckDB can significantly reduce latency by replacing standard SQL OFFSET clauses with the `file_row_number()` function. This optimization enables efficient deep pagination of large Parquet files, preventing the linear performance degradation typically associated with traditional SQL paging.

Standard SQL OFFSET in DuckDB requires the engine to scan and discard every row preceding the requested offset. This creates a performance bottleneck where the time to retrieve a page increases linearly as the page number grows. In contrast, the `file_row_number()` function allows for efficient paging by filtering directly on the row index of the Parquet file. By leveraging the physical structure of columnar storage, DuckDB can jump to specific rows and avoid the exhaustive scan required by OFFSET.

The Serverless Constraint

This performance gap is critical in stateless, serverless architectures such as AWS Lambda or Google Cloud Run. These environments often impose strict response size limits—such as the 6MB limit for AWS Lambda—which make it impossible to return massive datasets in a single call. When dealing with datasets as large as 20 million rows, developers must implement a paging system where the caller requests data one page at a time until the full set is retrieved. Returning twenty million rows can easily exceed maximum API response sizes, necessitating a robust pagination strategy.

Industry Implications

For the broader data engineering community, this finding provides a concrete pattern for maintaining the scalability of stateless services. Traditional LIMIT/OFFSET pagination is a well-known inefficiency in relational databases, but the ability to use `file_row_number()` in DuckDB transforms deep pagination from a linear-time operation into a near-constant-time operation. This ensures that user interfaces and API consumers experience consistent responsiveness regardless of whether they are accessing the first page or the ten-thousandth page of a multi-million row dataset.

Future Considerations

While the use of `file_row_number()` offers a clear advantage for static Parquet files, developers should monitor how these patterns scale as dataset sizes grow further. The primary focus remains on avoiding full-table scans in environments where memory and execution time are billed by the millisecond. For those implementing this pattern, the key is to shift the filtering logic from the result set (OFFSET) to the source file index (`file_row_number()`).