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

Jul 30, 2026 09:58 PM - 1 month ago 705

I had a ample Parquet record and a work that had to manus backmost its contents. Returning twenty cardinal rows tin easy transcend the maximum size of an API response, and immoderate you are deployed connected has a ceiling: Lambda gives you 6 MB for a synchronous petition aliases response, and Cloud Run caps an HTTP/1 consequence astatine 32 MiB unless you watercourse it. Even without a level limit, the customer has to clasp what you send.

So the contents spell backmost a page astatine a clip and the caller keeps asking until it has everything. What shapes everything other is that each petition has to guidelines connected its own. With respective workers down a load balancer location is nary server-side position to resume from, so “the adjacent page” has to beryllium reconstructible from the petition itself, by immoderate worker, every time.

The evident measurement to constitute that is LIMIT and OFFSET, and the interest is that OFFSET 19000000 has to count past nineteen cardinal rows to find your page, which would make a afloat walk done the record quadratic. DuckDB’s read_parquet has a file_row_number action that hands you each row’s beingness position, truthful I could select on a statement scope instead. The statement stays the same, since the customer still sends back something mini and the server rebuilds the page from it, but thing gets counted.

I expected to beryllium that OFFSET re-reads everything successful beforehand of your page. It doesn't, and what turned retired to matter wasn't velocity astatine all.

The short version

On a 20-million-row record pinch 163 statement groups, the row-range type vanished 2.53x faster than OFFSET crossed the full file. That held successful 37 retired of 37 runs.

-- alternatively 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 statement scope is doing thing specific. Parquet files are stored arsenic a series of blocks called statement groups, and DuckDB tin activity retired from the file’s footer which blocks a given scope of statement numbers lives in. Everything earlier your page gets skipped without ever being decompressed:

Skipping consecutive to the statement group you need WHERE file_row_number >= $lo AND file_row_number < $hi skipped, ne'er decompressed the rows you asked for besides skipped each artifact is 1 statement group · schematic, not to scale

The golden way is your predicate. It arrives astatine the blocks holding your rows without rubbing the ones successful beforehand of them — which is why the costs of a page doesn't turn arsenic you page deeper.

Two caveats earlier you spell anyplace pinch that number. It depends wholly connected your file having galore statement groups. Speed is besides the weaker of the 2 arguments for a statement range; the stronger 1 is worse than a capacity problem.

How galore statement groups does your record have?

DuckDB skips activity 1 row group astatine a time. A Parquet record written arsenic a azygous tremendous statement group has thing to skip, truthful nary of this helps. Check earlier you scheme astir 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 backmost 1 and you tin extremity reading. To spot really overmuch this matters I wrote the aforesaid two million rows twice, identical schema and data, changing only ROW_GROUP_SIZE:

More statement groups, bigger win

The aforesaid 2 cardinal rows written 2 ways, positive the large record for reference. Dotted statement is simply a tie.

1 statement group
2M rows

1.25×

17 statement groups
2M rows

1.75×

163 statement groups
20M rows

2.53×

The 1-vs-17 brace is the honorable comparison: identical data, only ROW_GROUP_SIZE changed. The 163 barroom is from the bigger file, truthful publication it arsenic “the inclination keeps going,” not arsenic a 3rd constituent connected 1 curve.

At 1 statement group the triumph drops to 1.25x. It doesn’t vanish, because DuckDB besides discards rows successful batches of 2,048 within a statement group, truthful a constrictive model still sounds little than the full group. You conscionable suffer astir of the benefit.

The much absorbing number successful that floor plan is the timepiece alternatively than the ratio. The aforesaid work goes from 0.49 s to 2.12 s, and both approaches slow down by 3 to 4 times. If you control the writer, hole that earlier you optimize thing else. DuckDB’s ain writer defaults to 122,880 rows per group, which is fine. Plenty of different devices are not, and DuckDB’s file format capacity guide has its ain notes connected picking a size.

One elephantine statement group is simply a bad thought nary matter which query you write.

What DuckDB really does pinch your OFFSET

Here’s wherever my quadratic presumption fell over. Run EXPLAIN connected a paging query and you get thing unexpected:

HASH_JOIN (SEMI) connected 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 backmost against the data. It reaches for file_row_number connected your behalf, and it skips statement groups the same way the hand-written type does. Paging pinch OFFSET is not quadratic. You salary instead for the other walk that useful retired which statement numbers you asked for, and that walk gets more expensive the deeper you page.

You are already utilizing file_row_number whether you typed it aliases not. The only mobility is whether you power it.

My first effort astatine measuring this assumed the quadratic model, timed 40 pages, and fitted a slope to extrapolate the rest. The slope came retired negative. A antagonistic slope says the model is broken, not the machine, truthful I threw retired the extrapolation and measured each 163 pages directly.

The rewrite has a cliff. It only fires erstwhile the LIMIT is 1,000,000 rows aliases fewer. That’s a level statement count alternatively than a fraction of the file, identical connected a 500k-row record and a 20-million-row one. Ask for 1,000,000 rows and you get the rewrite; inquire for 1,000,001 and it’s gone, and now you really are decompressing everything successful beforehand of your page. Adding any WHERE clause turns it disconnected too. With million-row pages the spread betwixt the two approaches opened up to astir 5-6x.

That number is simply a changeless successful the optimizer, LIMIT_MAX_VAL, but it isn’t the full story. There’s besides a setting, late_materialization_max_rows, and the effective cutoff is whichever of the 2 is larger:

late_materialization_max_rowsrewrite fires up to

50 (the default)	1,000,000 rows
200,000	1,000,000 rows
2,000,000	2,000,000 rows

So raising it beneath a cardinal does nothing, and raising it supra a cardinal moves the cliff. If you genuinely request million-plus pages and want to support utilizing OFFSET, that’s the knob. I’d still alternatively constitute the statement scope and not dangle connected an optimizer rewrite I can’t see from the query text.

I besides wanted to beryllium the row-group skipping alternatively than infer it from a stopwatch, truthful I wrote 128 bytes of garbage into the mediate of statement group 0 to make it undecodable. A full scan of the record blew up, arsenic did a page inside statement group 0. A page complete statement group 100 came back byte-for-byte correct. It ne'er touched the surgery bytes.

The portion that should interest you

LIMIT/OFFSET has nary ORDER BY, truthful it makes nary committedness astir which rows you get. It behaves coming because DuckDB preserves insertion order by default. That’s a documented performance knob, and group turn it off.

So I turned it off, ran much than 1 thread, and paged done the full file. Both runs handed backmost precisely 20,000,000 rows:

runrows missingrows duplicatedmax copies

1	6,131,712	4,943,872	5
2	6,408,192	5,221,632	5

Some rows ne'er appear, others look 5 times, and it lands otherwise connected each run. Nothing raises an error.

The statement count is perfect. About 30% of the information is not.

So don’t validate an export by counting rows. A count can’t spot this, because the drops and the duplicates cancel each different out. Six cardinal rows spell missing, 5 cardinal get sent twice, and the full still lands connected precisely 20,000,000.

Hash the rows instead. The crypto extension has crypto_hash_agg, which digests a full consequence group into 1 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 customer received, comparison 2 hex strings. Both travel out 94894c4eef00ea72... here, and 1 missing aliases doubled statement changes it.

Wrapping the statement successful hash() first runs 2.8x faster than casting it to text, 1.4 s against 4.0 s complete 20 cardinal rows, since blake3 past gets 8 bytes per statement alternatively of a formatted string. hash() is 64-bit, truthful 2 genuinely different rows collide astir erstwhile in 90,000 files this size, which is good for an integrity check.

The ORDER BY wrong the aggregate is required, and the hold errors if you time off it out. Sorting location is what makes the digest a usability of the statement multiset alternatively than of arrival order, truthful pages tin travel backmost successful immoderate bid and still agree.

This is what caught the bug. Row counts sailed consecutive past it.

The corruption needs some conditions: insertion bid disconnected and much than 1 thread. Single-threaded, OFFSET paging is fine. The operation is constrictive capable that you might never deed it, which is what makes it unpleasant. You are 1 settings alteration distant from silently corrupting an export, and thing successful the query matter says so.

file_row_number can’t do this. A row’s position successful the record is simply a truth astir the file, so [lo, hi) ranges tile it precisely sloppy of thread count aliases settings. I ran each four combinations of threads and insertion bid alternatively than assume.

One related trap I did get incorrect astatine first. Row bid inside a page is besides unguaranteed, and immoderate page spanning much than 1 statement group comes backmost shuffled nether those aforesaid settings. My first trial said everything was fine, because I’d only tested single-row-group pages, which can’t reorder: they only ever get 1 thread. If bid wrong a page matters to you, add ORDER BY file_row_number (about 26% slower astatine 122,880-row pages, 42% astatine million-row pages, and it buffers the full page) aliases return the file and fto the customer sort.

How heavy your users page changes the answer

2.53x assumes each page gets publication precisely once. Real postulation seldom looks for illustration that, and the 2 approaches person wholly different shapes. file_row_number costs the aforesaid on page 1 arsenic page 163. OFFSET climbs astir a 4th of a millisecond per page, each the way down.

Where your users are matters

Same file, aforesaid 2 queries. Only which pages get publication changes.

first page only

1.77×

first 10 pages

1.80×

first 25% of pages

1.93×

uniform complete each pages

2.43×

last 25% of pages

2.91×

last page only

3.07×

Front page only, astir 1.8×. Drain the full record and it is 2.43×. Down astatine the heavy end, 3.07×. The problem for an API is not the mean — it is that OFFSET gets slower the further personification goes.

If astir group load the first page and leave, you get the mini number; drain the record and you get the large one. Either way, the style matters much than the ratio: OFFSET gets slower the further a personification goes, which is backwards from what you want retired of pagination.

Page size, and a prediction I sewage wrong

I assumed sloppy page sizes would wreck this, since a page that straddles a row-group boundary makes DuckDB decompress 2 groups to service 1 page. So I swept page sizes from 30,000 to 122,880 rows:

rows/pagepagesrow rangeOFFSETfaster

122,880	163	4.01 s	9.70 s	2.45x
100,000	200	4.02 s	9.44 s	2.27x
61,440	326	5.02 s	13.02 s	2.56x
50,000	400	6.46 s	15.95 s	2.44x
30,000	667	10.33 s	24.77 s	2.68x

The ratio holds betwixt 2.27x and 2.68x nary matter what I picked, because a severely sized page costs both queries more. What it wrecks is your absolute latency: 122,880-row pages do the record successful 4.01 s, 30,000-row pages return 10.33 s for precisely the aforesaid 20 cardinal rows.

Two things I had incorrect here. First, alignment is the incorrect idea: what matters is page size against row-group size. A 61,440-row page ne'er straddles a boundary, since it divides 122,880 evenly, and it still sounds a full 122,880-row group to springiness you half of it. Only a page adjacent to the row-group size sounds precisely what it needs.

Second, I predicted the costs from the footer arithmetic alternatively of measuring it. The arithmetic said 100,000-row pages should costs 2.2x extra. On the timepiece they costs nothing measurable. That 2,048-row select is doing much activity than the footer mathematics knows about.

Predicting costs from the record footer is not the aforesaid arsenic measuring it. I did the first and believed it.

Get your page boundaries from parquet_metadata() and lucifer them to statement groups. Don’t compute them arsenic page * 122880. The past statement group successful my record holds 93,440 rows, and anything written by Spark aliases Arrow sizes statement groups by bytes, truthful the counts vary.

What the stateless request costs you

The framing astatine the apical ruled retired thing that keeps authorities betwixt requests, and that decision has a price. Here is the aforesaid 20 cardinal rows publication each measurement I tried:

approachwall clock

pyarrow read_row_group(n)	0.42 s
pyarrow iter_batches	0.43 s
DuckDB to_arrow_reader (one streaming query)	1.94 s
WHERE id >= ? AND id < ? (sorted column)	2.85 s
file_row_number, page = statement group	2.90 s
LIMIT / OFFSET	7.05 s

A azygous streaming query pinch DuckDB’s to_arrow_reader sounds the file erstwhile alternatively of 163 times, truthful it comes successful 1.5x faster than the champion paged approach. pyarrow’s ParquetFile is faster still, and it tin reside a statement group by scale directly, which DuckDB has nary syntax for.

Paging is simply a 1.5x taxation against streaming and a 7x taxation against conscionable handing complete the file. That is what a stateless endpoint costs, and for an API it is usually worthy paying: you get resumability, bounded representation astatine some ends, and immoderate worker tin service immoderate request. It is still a existent number, truthful if the size ceiling is the only logic you’re paging, cheque 2 things before you build the loop:

Can the consequence stream? Chunked transportation encoding, aliases an Arrow IPC watercourse complete a single long-lived response, gets you 1 petition and 1 scan. Read the ceiling that forced you into paging earlier you judge it: Cloud Run’s 32 MiB applies only if you are not utilizing Transfer-Encoding: chunked, and Lambda’s 6 MB becomes uncapped for the first 6 MB under response streaming. These are usually limits connected a buffered body, not connected full bytes.
Can the customer publication the record itself? A presigned URL and 30 seconds of pyarrow beats thing connected this list, and takes your work retired of the information way completely.

If neither is available, page. For a nationalist REST API, usually neither is. The advantage survives existent concurrency: pinch abstracted worker processes alternatively than threads, file_row_number stayed 2.24x to 2.37x up from 1 worker up to sixteen.

There is 1 different option. If your array has a sorted key, plain WHERE id >= ? AND id < ? tied file_row_number astatine 2.85 s. Row-group statistic connected a sorted file prune conscionable arsenic well, and a cursor connected a existent cardinal survives the record being rewritten, which a statement number does not. That only useful if the file is genuinely clustered. Mine was perfectly sorted because I generated it, which is not a spot existent information owes you.

How I measured it

A azygous page publication takes 15-30 sclerosis and moves astir by 6-9% tally to run, which is excessively noisy to hang an statement on. So the portion is 1 complete travel done each 163 pages, timed 40 times with the first 3 thrown away. The 2 queries return turns successful random bid wrong each round and are compared against each different wrong that round, truthful a engaged infinitesimal connected the instrumentality hits both.

The portion that convinced maine was racing file_row_number against itself arsenic a control. If the harness were inventing differences, that would show a spread too. It came retired astatine 1.05x.

Every round, some comparisons

37 rounds. Gold is OFFSET divided by file_row_number successful the aforesaid round. Green is file_row_number raced against itself.

1×

2×

3×

4×

round 1round 37

Individual rounds are messy successful some arms — golden runs 1.54× to 3.64×, and moreover the power wandered from 0.83× to 2.14×. That is why the header number comes from each the rounds together alternatively than immoderate 1 of them.

An earlier type of this study reported a overmuch tighter correction bar, and it was wrong. I had bootstrapped a median complete 15 runs, which can’t onshore anyplace isolated from connected 1 of the 15 numbers you already have, truthful the neat-looking interval was decoration. I’d besides described a confidence interval connected a median arsenic 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 honorable dispersed fixed it. The answer hardly moved, from 2.52x to 2.53x, but the aged correction bars weren’t worthy the ink.

What I’d do

Use file_row_number pinch page boundaries pulled from parquet_metadata() and matched to row groups. Confirm your row-group count first, because connected a single-row-group record nary of this buys you much. Keep the LIMIT nether a cardinal if you ever autumn backmost to OFFSET. Before penning the loop astatine all, cheque whether your consequence tin stream: the ceiling that forces paging is often connected the buffered body, not the full bytes.

The 2.53x is not why I’d scope for it. OFFSET will manus you a plausible-looking consequence set with 30% of the rows wrong, and the only point opinionated betwixt you and that is simply a performance setting personification mightiness flip.