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

DuckDB &middot; July 30, 2026<br>Paging Through a Parquet File in DuckDB: file_row_number or OFFSET?<br>You have a large Parquet file and an API that has to return its contents, but responses have a size ceiling, so the caller pages through it. LIMIT/OFFSET is the obvious way to write that and it is the wrong one. I measured file_row_number against it: 2.53x faster on a file with 163 row groups. Speed is the boring half of the answer. OFFSET will also hand back the wrong rows without telling you.

I had a large Parquet file and a service that had to hand back its contents. Returning<br>twenty million rows can easily exceed the maximum size of an API response, and whatever you<br>are deployed on has a ceiling:<br>Lambda gives you 6 MB<br>for a synchronous request or response, and<br>Cloud Run caps an HTTP/1 response at 32 MiB<br>unless you stream it. Even without a platform limit, the client has to hold what you send.

So the contents go back a page at a time and the caller keeps asking until it has<br>everything. What shapes everything else is that each request has to stand on its own .<br>With several workers behind a load balancer there is no server-side position to resume from,<br>so “the next page” has to be reconstructible from the request itself, by any worker, every<br>time.

The obvious way to write that is LIMIT and OFFSET,<br>and the worry is that OFFSET 19000000 has to count past nineteen million rows to find your<br>page, which would make a full pass through the file quadratic. DuckDB’s<br>read_parquet has a<br>file_row_number option that hands you each row’s physical position, so I could filter on<br>a row range instead. The contract stays the same, since the client still sends back<br>something small and the server rebuilds the page from it, but nothing gets counted.

I expected to prove that OFFSET re-reads everything in front of your page. It doesn't, and what turned out to matter wasn't speed at all.

The short version

On a 20-million-row file with 163 row groups, the row-range version finished 2.53x<br>faster than OFFSET across the whole file. That held in 37 out of 37 runs.

-- instead of LIMIT $n OFFSET $offset<br>SELECT id, k, name, category, value, payload, ts<br>FROM read_parquet($path, file_row_number => true)<br>WHERE file_row_number >= $lo AND file_row_number $hi<br>That row range is doing something specific. Parquet files are stored as a sequence of<br>blocks called row groups, and DuckDB can work out from the file’s footer which blocks a<br>given range of row numbers lives in. Everything before your page gets skipped without ever<br>being decompressed:

Skipping straight to the row group you need<br>WHERE file_row_number >= $lo AND file_row_number

skipped, never decompressed

the rows you asked for

also skipped<br>each block is one row group · schematic, not to scale

The gold path is your predicate. It arrives at the blocks holding your rows without touching the ones in front of them — which is why the cost of a page doesn't grow as you page deeper.

Two caveats before you go anywhere with that number. It depends entirely on your file<br>having many row groups. Speed is also the weaker of the two arguments for a row range; the<br>stronger one is worse than a performance problem.

How many row groups does your file have?

DuckDB skips work one row group at a time.<br>A Parquet file written as a single enormous row group has nothing to skip, so none of this<br>helps. Check before you plan around it, using<br>parquet_metadata:

SELECT count(DISTINCT row_group_id) AS row_groups,<br>min(row_group_num_rows) AS smallest,<br>max(row_group_num_rows) AS largest<br>FROM parquet_metadata('yourfile.parquet');<br>Get back 1 and you can stop reading. To see how much this matters I wrote the same two<br>million rows twice, identical schema and data, changing only ROW_GROUP_SIZE:

More row groups, bigger win

The same 2 million rows written two ways, plus the big file for reference. Dotted line is a tie.

1 row group<br>2M rows

1.25×

17 row groups<br>2M rows

1.75×

163 row groups<br>20M rows

2.53×

The 1-vs-17 pair is the honest comparison: identical data, only ROW_GROUP_SIZE changed. The 163 bar is from the bigger file, so read it as “the trend keeps going,” not as a third point on one curve.

At one row group the win drops to 1.25x. It doesn’t vanish, because DuckDB also discards<br>rows in batches of 2,048 within a row group, so a narrow window still reads less than<br>the whole group. You just lose most of the benefit.

The more interesting number in that chart is the clock rather than the ratio. The same work<br>goes from 0.49 s to 2.12 s, and both approaches slow down by three to four times. If you<br>control the writer, fix that before you optimize anything else. DuckDB’s own writer defaults<br>to 122,880 rows per group, which is fine. Plenty of other tools are not, and DuckDB’s<br>file format performance guide<br>has its own notes on picking a size.

One giant row group is a bad idea no matter which query you write.

What DuckDB actually...