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:

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:

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

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

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

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:

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:

A single streaming query with DuckDB’s to_arrow_reader reads the file once instead of 163 times, so it comes in 1.5x faster than the best paged approach. pyarrow’s ParquetFile 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, and Lambda’s 6 MB becomes uncapped for the first 6 MB under response streaming. 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.

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: every read_parquet option, including file_row_number
• Parquet metadata functions: parquet_metadata, which is where your page boundaries should come from
• Order preservation: what DuckDB does and does not promise about row order
• Configuration reference: preserve_insertion_order, late_materialization_max_rows, parquet_metadata_cache
• Performance guide: file formats: row group sizing from the other direction, as a writer
• Parquet file format: what a row group actually is, if the term is new