Article URL: https://rusty.today/blog/paging-parquet-duckdb-file-row-number-vs-offset/ Comments URL: https://news.ycombinator.com/item?id=49111006 Points: 24 # Comments: 3

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 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. 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. 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: 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.