Paging Through a Parquet File in DuckDB: File_row_number or Offset?

# Paging Through a Parquet File in DuckDB: file_row_number or OFFSET? — Rusty Conover Source: [https://rusty.today/blog/paging-parquet-duckdb-file-row-number-vs-offset/](https://rusty.today/blog/paging-parquet-duckdb-file-row-number-vs-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](https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-limits.html)for a synchronous request or response, and[Cloud Run caps an HTTP/1 response at 32 MiB](https://docs.cloud.google.com/run/quotas)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`](https://duckdb.org/docs/stable/sql/query_syntax/limit), 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`](https://duckdb.org/docs/stable/data/parquet/overview)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 needWHERE file\_row\_number \>= $lo AND file\_row\_number < $hiskipped, never decompressedthe rows you asked foralso skippedeach 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](https://parquet.apache.org/docs/file-format/)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`](https://duckdb.org/docs/stable/data/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\. 1 row group 2M rows 1\.25× 17 row groups 2M rows 1\.75× 163 row groups 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 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](https://duckdb.org/docs/stable/guides/performance/file_formats)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\. Run`EXPLAIN`on a paging query and you get something unexpected: ``` HASH_JOIN (SEMI) on file_index = file_index and file_row_number = file_row_number ├─ READ_PARQUET id, k, name, ... └─ 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`](https://github.com/duckdb/duckdb/blob/main/src/optimizer/late_materialization.cpp), 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: `late\_materialization\_max\_rows`rewrite fires up to50 \(the default\)1,000,000 rows200,0001,000,000 rows2,000,0002,000,000 rowsSo raising it below a million does nothing, and raising it above a million moves the cliff\. 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](https://duckdb.org/docs/stable/sql/dialect/order_preservation)by default\. That’s a documented[performance knob](https://duckdb.org/docs/stable/configuration/overview), 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: runrows missingrows duplicatedmax copies1**6,131,712**4,943,87252**6,408,192**5,221,6325Some rows never appear, others appear five times, and it lands differently on every run\. Nothing raises an error\. The row count is perfect\. About 30% of the data is not\. **So don’t validate an export by counting rows\.**A count can’t see this, because the drops and the duplicates cancel each other out\. Six million rows go missing, five million get sent twice, and the total still lands on exactly 20,000,000\. Hash the rows instead\. The[crypto extension](https://query.farm/duckdb_extension_crypto)has`crypto\_hash\_agg`, which digests a whole result set into one value: ``` INSTALL crypto FROM community; LOAD crypto; SELECT crypto_hash_agg( 'blake3', hash((id, k, name, ts)) ORDER BY hash((id, k, name, ts))) FROM read_parquet('data.parquet'); ``` Digest the file, digest what the client received, compare two hex strings\. Both come out`94894c4eef00ea72\.\.\.`here, and one missing or doubled row changes it\. Wrapping the row in`hash\(\)`first runs 2\.8x faster than casting it to text, 1\.4 s against 4\.0 s over 20 million rows, since blake3 then gets eight bytes per row instead of a formatted string\.`hash\(\)`is 64\-bit, so two genuinely different rows collide about once in 90,000 files this size, which is fine for an integrity check\. The`ORDER BY`inside the aggregate is required, and the extension errors if you leave it out\. Sorting there is what makes the digest a function of the row multiset rather than of arrival order, so pages can come back in any order and still agree\. This is what caught the bug\. Row counts sailed straight past it\. The corruption needs both conditions: insertion order off*and*more than one thread\. Single\-threaded,`OFFSET`paging is fine\. The combination is narrow enough that you might never hit it, which is what makes it unpleasant\. You are one settings change away from silently corrupting an export, and nothing in the query text says so\. `file\_row\_number`can’t do this\. A row’s position in the file is a fact about the file, so`\[lo, hi\)`ranges tile it exactly regardless of thread count or settings\. I ran all four combinations of threads and insertion order rather than assume\. One related trap I did get wrong at first\. Row order*inside*a page is also unguaranteed, and any page spanning more than one row group comes back shuffled under those same settings\. My first test said everything was fine, because I’d only tested single\-row\-group pages, which can’t reorder: they only ever get one thread\. If order within a page matters to you, add`ORDER BY file\_row\_number`\(about 26% slower at 122,880\-row pages, 42% at million\-row pages, and it buffers the whole page\) or return the column and let the client sort\. ## How deep your users page changes the answer 2\.53x assumes every page gets read exactly once\. Real traffic rarely looks like that, and the two approaches have completely different shapes\.`file\_row\_number`costs the same on page 1 as page 163\.`OFFSET`climbs about a quarter of a millisecond per page, all the way down\. Where your users are matters Same file, same two queries\. Only which pages get read changes\. first page only 1\.77× first 10 pages 1\.80× first 25% of pages 1\.93× uniform over all pages 2\.43× last 25% of pages 2\.91× last page only 3\.07× Front page only, about 1\.8×\. Drain the whole file and it is 2\.43×\. Down at the deep end, 3\.07×\. The problem for an API is not the average — it is that`OFFSET`gets slower the further someone goes\.If most people load the first page and leave, you get the small number; drain the file and you get the big one\. Either way, the shape matters more than the ratio:`OFFSET`gets slower the further a user goes, which is backwards from what you want out of pagination\. ## Page size, and a prediction I got wrong I assumed sloppy page sizes would wreck this, since a page that straddles a row\-group boundary makes DuckDB decompress two groups to serve one page\. So I swept page sizes from 30,000 to 122,880 rows: rows/pagepagesrow rangeOFFSETfaster122,8801634\.01 s9\.70 s**2\.45x**100,0002004\.02 s9\.44 s**2\.27x**61,4403265\.02 s13\.02 s**2\.56x**50,0004006\.46 s15\.95 s**2\.44x**30,00066710\.33 s24\.77 s**2\.68x**The ratio holds between 2\.27x and 2\.68x no matter what I picked, because a badly sized page costs*both*queries more\. What it wrecks is your absolute latency: 122,880\-row pages do the file in 4\.01 s, 30,000\-row pages take 10\.33 s for exactly the same 20 million rows\. Two things I had wrong here\. First, alignment is the wrong idea: what matters is page size against row\-group size\. A 61,440\-row page never straddles a boundary, since it divides 122,880 evenly, and it*still*reads a whole 122,880\-row group to give you half of it\. Only a page equal to the row\-group size reads exactly what it needs\. Second, I predicted the cost from the footer arithmetic instead of measuring it\. The arithmetic said 100,000\-row pages should cost 2\.2x extra\. On the clock they cost nothing measurable\. That 2,048\-row filter is doing more work than the footer math knows about\. Predicting cost from the file footer is not the same as measuring it\. I did the first and believed it\. Get your page boundaries from`parquet\_metadata\(\)`and match them to row groups\. Don’t compute them as`page \* 122880`\. The last row group in my file holds 93,440 rows, and anything written by Spark or Arrow sizes row groups by bytes, so the counts vary\. ## What the stateless requirement costs you The framing at the top ruled out anything that keeps state between requests, and that decision has a price\. Here is the same 20 million rows read every way I tried: approachwall clockpyarrow`read\_row\_group\(n\)`**0\.42 s**pyarrow`iter\_batches`0\.43 sDuckDB`to\_arrow\_reader`\(one streaming query\)1\.94 s`WHERE id \>= ? AND id < ?`\(sorted column\)2\.85 s`file\_row\_number`, page = row group2\.90 s`LIMIT`/`OFFSET`7\.05 sA single streaming query with DuckDB’s[`to\_arrow\_reader`](https://duckdb.org/docs/stable/clients/python/relational_api)reads the file once instead of 163 times, so it comes in 1\.5x faster than the best paged approach\.[pyarrow’s`ParquetFile`](https://arrow.apache.org/docs/python/generated/pyarrow.parquet.ParquetFile.html)is faster still, and it can address a row group by index directly, which DuckDB has no syntax for\. **Paging is a 1\.5x tax against streaming and a 7x tax against just handing over the file\.**That is what a stateless endpoint costs, and for an API it is usually worth paying: you get resumability, bounded memory at both ends, and any worker can serve any request\. It is still a real number, so if the size ceiling is the*only*reason you’re paging, check two things before you build the loop: - **Can the response stream?**Chunked transfer encoding, or an Arrow IPC stream over a single long\-lived response, gets you one request and one scan\. Read the ceiling that forced you into paging before you accept it: Cloud Run’s 32 MiB applies[only if you are not using`Transfer\-Encoding: chunked`](https://docs.cloud.google.com/run/quotas), and Lambda’s 6 MB becomes uncapped for the first 6 MB under[response streaming](https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-limits.html)\. These are usually limits on a buffered body, not on total bytes\. - **Can the client read the file itself?**A presigned URL and thirty seconds of pyarrow beats anything on this list, and takes your service out of the data path completely\. If neither is available, page\. For a public REST API, usually neither is\. The advantage survives real concurrency: with separate worker processes rather than threads,`file\_row\_number`stayed 2\.24x to 2\.37x ahead from one worker up to sixteen\. There is one other option\. If your table has a sorted key, plain`WHERE id \>= ? AND id < ?`tied`file\_row\_number`at 2\.85 s\. Row\-group statistics on a sorted column prune just as well, and a cursor on a real key survives the file being rewritten, which a row number does not\. That only works if the column is genuinely clustered\. Mine was perfectly sorted because I generated it, which is not a property real data owes you\. ## How I measured it A single page read takes 15\-30 ms and moves around by 6\-9% run to run, which is too noisy to hang an argument on\. So the unit is one complete trip through all 163 pages, timed 40 times with the first 3 thrown away\. The two queries take turns in random order inside each round and are compared against each other within that round, so a busy moment on the machine hits both\. The part that convinced me was racing`file\_row\_number`against*itself*as a control\. If the harness were inventing differences, that would show a gap too\. It came out at 1\.05x\. Every round, both comparisons 37 rounds\. Gold is`OFFSET`divided by`file\_row\_number`in the same round\. Green is`file\_row\_number`raced against itself\. 1× 2× 3× 4× round 1round 37 Individual rounds are messy in both arms — gold runs 1\.54× to 3\.64×, and even the control wandered from 0\.83× to 2\.14×\. That is why the headline number comes from all the rounds together rather than any one of them\.An earlier version of this analysis reported a much tighter error bar, and it was wrong\. I had bootstrapped a median over 15 runs, which can’t land anywhere except on one of the 15 numbers you already have, so the neat\-looking interval was decoration\. I’d also described a confidence interval on a median as a “noise floor,” which it isn’t\. The run\-to\-run wobble is around 5%, not the 1\.5% I first claimed\. More rounds and an honest spread fixed it\. The answer barely moved, from 2\.52x to 2\.53x, but the old error bars weren’t worth the ink\. ## What I’d do Use`file\_row\_number`with page boundaries pulled from`parquet\_metadata\(\)`and matched to row groups\. Confirm your row\-group count first, because on a single\-row\-group file none of this buys you much\. Keep the`LIMIT`under a million if you ever fall back to`OFFSET`\. Before writing the loop at all, check whether your response can stream: the ceiling that forces paging is often on the buffered body, not the total bytes\. The 2\.53x is not why I’d reach for it\.`OFFSET`will hand you a plausible\-looking result set with 30% of the rows wrong, and the only thing standing between you and that is a performance setting somebody might flip\. ## Further reading - [Reading Parquet files](https://duckdb.org/docs/stable/data/parquet/overview): every`read\_parquet`option, including`file\_row\_number` - [Parquet metadata functions](https://duckdb.org/docs/stable/data/parquet/metadata):`parquet\_metadata`, which is where your page boundaries should come from - [Order preservation](https://duckdb.org/docs/stable/sql/dialect/order_preservation): what DuckDB does and does not promise about row order - [Configuration reference](https://duckdb.org/docs/stable/configuration/overview):`preserve\_insertion\_order`,`late\_materialization\_max\_rows`,`parquet\_metadata\_cache` - [Performance guide: file formats](https://duckdb.org/docs/stable/guides/performance/file_formats): row group sizing from the other direction, as a writer - [Parquet file format](https://parquet.apache.org/docs/file-format/): what a row group actually is, if the term is new