GoKawiilYou 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 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.
... continue reading