Paging Through a Parquet File in DuckDB: file_row_number or OFFSET?

Published: July 30, 2026

Imagine you are managing a massive Parquet file and an API designed to serve its contents. Because API responses have strict size ceilings, you cannot dump the whole file at once; instead, the client must page through the data.

The intuitive approach is to use LIMIT and OFFSET. This is the wrong way to do it.

In my benchmarks, using file_row_number was 2.53x faster than OFFSET on a file containing 163 row groups. Furthermore, OFFSET can occasionally return incorrect rows without alerting the user.

The Architectural Constraint

When dealing with twenty million rows, you hit physical limits quickly:

• AWS Lambda: 6 MB limit for synchronous responses.
• Cloud Run: 32 MiB cap for HTTP/1 responses (unless streaming).
• Client-side: The consuming application has finite memory.

Because the service sits behind a load balancer with multiple workers, the system is stateless. There is no server-side cursor to track progress. Every request must be self-contained, meaning the "next page" must be reconstructible from the request parameters alone.

The Performance Hypothesis

The standard pattern is: SELECT * FROM table LIMIT 1000 OFFSET 19000000;

The fear here is that OFFSET 19,000,000 forces the engine to count through nineteen million rows before reaching the target page, potentially turning a full file scan into a $\mathcal{O}(n^2)$ (quadratic) operation.

To avoid this, I tested DuckDB's read_parquet function using the file_row_number option, which exposes the physical position of each row.

The Optimized Query

Instead of using LIMIT and OFFSET, I used a row-range filter:

SELECT id, k, name, category, value, payload, ts 
FROM read_parquet('path/to/file.parquet', file_row_number => true) 
WHERE file_row_number >= $lo AND file_row_number < $hi;

How it Works: Row Group Skipping

Parquet files are organized into blocks called row groups. DuckDB reads the file's footer to determine which blocks contain the specific row numbers requested.

By using file_row_number, DuckDB skips directly to the relevant block. The rows preceding your page are never decompressed, meaning the cost of accessing a page remains constant regardless of how deep you are in the file.

Key Insight: The "gold path" is the predicate. It allows the engine to ignore irrelevant blocks entirely.

The "Single Row Group" Trap

This optimization relies entirely on the existence of multiple row groups. If a Parquet file is written as one giant block, there is nothing to skip.

You can verify your file's structure using parquet_metadata:

SELECT 
    count(DISTINCT row_group_id) AS row_groups, 
    min(row_group_num_rows) AS smallest, 
    max(row_group_num_rows) AS largest 
FROM parquet_metadata('yourfile.parquet');

If row_groups returns 1, stop reading; these optimizations won't help you.

Performance Comparison

I tested two million rows with varying ROW_GROUP_SIZE settings to see the impact:

Note: The 1 vs 17 comparison is the most direct "apples-to-apples" test. The 163-group result demonstrates that the performance benefit scales with the number of groups.

Even with a single group, DuckDB discards rows in batches of 2,048, so a narrow window is still slightly faster than a full scan. However, the real bottleneck is the writer. If you control the file creation, ensure you aren't creating giant row groups. DuckDB's default is 122,880 rows per group, which is generally efficient.

What is DuckDB actually doing with OFFSET?

I used EXPLAIN to see why OFFSET wasn't as slow as I expected.

HASH_JOIN (SEMI) on file_index = file_index and file_row_number = file_row_number
├─ READ_PARQUET id, k, name, ...
└─ STREAMING_LIMIT 
   └─ READ_PARQUET

Surprisingly, DuckDB rewrites your OFFSET into a row-number lookup and semi-joins it back to the data. It is essentially using file_row_number under the hood.

So why is it slower?

1. Extra Pass: You pay a penalty for the internal pass that calculates which row numbers you requested.
2. Scaling: This overhead increases as you page deeper into the file.
3. The Threshold: There is a limit. If you ask for a page larger than 1,000,000 rows, the rewrite disappears, and DuckDB reverts to decompressing everything from the start. In these cases, the performance gap widens to $5\text{--}6\times$.

Summary Checklist for Paging

• Check Metadata: Use parquet_metadata to ensure you have multiple row groups.
• Avoid OFFSET: Use file_row_number => true with a WHERE clause for range filtering.
• Optimize Writer: Ensure ROW_GROUP_SIZE is not set to a single massive block.
• Monitor Page Size: Be aware that very large pages may trigger slower execution paths in DuckDB.