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

I had a large Parquet file and a service that had to hand back its contents. Returning twenty million rows can easily exceed the maximum size of an API response, and whatever you are deployed on has a ceiling: Lambda gives you 6 MB for a synchronous request or response, and Cloud Run caps an HTTP/1 response at 32 MiB 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 everything. What shapes everything else is that each request has to stand on its own. With several workers behind a load balancer there is no server-side position to resume from, so “the next page” has to be reconstructible from the request itself, by any worker, every time. The obvious way to write that is LIMIT and OFFSET, and the worry is that OFFSET 19000000 has to count past nineteen million rows to find your page, which would make a full pass through the file quadratic. DuckDB’s read_parquet has a file_row_number option that hands you each row’s physical position, so I could filter on a row range instead. The contract stays the same, since the client still sends back 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 faster than OFFSET across the whole file. That held in 37 out of 37 runs. -- instead of LIMIT $n OFFSET $offset SELECT id, k, name, category, value, payload, ts FROM read_parquet($path, file_row_number => true) WHERE file_row_number >= $lo AND file_row_number < $hi That row range is doing something specific. Parquet files are stored as a sequence of blocks called row groups, and DuckDB can work out from the file’s footer which blocks a given range of row numbers lives in. Everything before your page gets skipped without ever being decompressed:

Skipping straight to the row group you need WHERE file_row_number >= $lo AND file_row_number < $hi skipped, never decompressed the rows you asked for also skipped 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 having many row groups. Speed is also the weaker of the two arguments for a row range; the 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. A Parquet file written as a single enormous row group has nothing to skip, so none of this helps. Check before you plan around it, 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'); Get back 1 and you can stop reading. To see how much this matters I wrote the same two 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.

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 rows in batches of 2,048 within a row group, so a narrow window still reads less than 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 goes from 0.49 s to 2.12 s, and both approaches slow down by three to four times. If you control the writer, fix that before you optimize anything else. DuckDB’s own writer defaults to 122,880 rows per group, which is fine. Plenty of other tools are not, and DuckDB’s file format performance guide 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 does with your OFFSET Here’s where my quadratic assumption fell over.

└─ STREAMING_LIMIT └─ READ_PARQUET DuckDB rewrites your OFFSET into a row-number lookup and semi-joins it back against the data. It reaches for file_row_number on your behalf, and it skips row groups the same way the hand-written version does. Paging with OFFSET is not quadratic. You pay instead for the extra pass that works out which row numbers you asked for, and that pass gets more expensive the deeper you page. You are already using file_row_number whether you typed it or not. The only question is whether you control it. My first attempt at measuring this assumed the quadratic model, timed 40 pages, and fitted a slope to extrapolate the rest. The slope came out negative. A negative slope says the model is broken, not the machine, so I threw out the extrapolation and measured all 163 pages directly. The rewrite has a cliff. It only fires when the LIMIT is 1,000,000 rows or fewer. That’s a flat row count rather than a fraction of the file, identical on a 500k-row file and a 20-million-row one. Ask for 1,000,000 rows and you get the rewrite; ask for 1,000,001 and it’s gone, and now you really are decompressing everything in front of your page. Adding any WHERE clause turns it off too. With million-row pages the gap between the two approaches opened up to roughly 5-6x. That number is a constant in the optimizer, LIMIT_MAX_VAL, but it isn’t the whole story. There’s also a setting, late_materialization_max_rows, and the effective cutoff is whichever of the two is larger:

If you genuinely need million-plus pages and want to keep using OFFSET, that’s the knob. I’d still rather write the row range and not depend on an optimizer rewrite I can’t see from the query text. I also wanted to prove the row-group skipping rather than infer it from a stopwatch, so I wrote 128 bytes of garbage into the middle of row group 0 to make it undecodable. A full scan of the file blew up, as did a page inside row group 0. A page over row group 100 came back byte-for-byte correct. It never touched the broken bytes. The part that should worry you LIMIT/OFFSET has no ORDER BY, so it makes no promise about which rows you get. It behaves today because DuckDB preserves insertion order by default. That’s a documented performance knob, and people turn it off. So I turned it off, ran more than one thread, and paged through the whole file. Both runs handed back exactly 20,000,000 rows: