HN
Today

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

This post dives deep into optimizing paging large Parquet files using DuckDB, contrasting LIMIT/OFFSET with file_row_number for API efficiency. It uncovers surprising optimizer behaviors, crucial performance cliffs, and a silent data corruption bug inherent in common OFFSET usage. The article offers practical, hard-won lessons for data engineers grappling with performant, robust data delivery in stateless environments, making it a compelling read for the Hacker News crowd.

9
Score
0
Comments
#9
Highest Rank
5h
on Front Page
First Seen
Jul 30, 3:00 PM
Last Seen
Jul 30, 7:00 PM
Rank Over Time
911182127

The Lowdown

The author explores the challenge of paging through massive Parquet files with DuckDB to serve API requests, where response size limits necessitate fetching data in chunks. The core dilemma revolves around whether to use SQL's standard LIMIT and OFFSET clauses or DuckDB's specialized file_row_number for range-based filtering.

  • Performance Comparison: Initial tests on a 20-million-row file showed file_row_number to be 2.53x faster than OFFSET for processing the entire file, primarily because it leverages Parquet's row groups for efficient skipping.
  • Optimizer's Role: Surprisingly, DuckDB's optimizer rewrites OFFSET queries to use file_row_number internally, preventing a quadratic slowdown for OFFSET under certain conditions. However, this rewrite has a cliff, activating only for LIMIT values up to 1,000,000 rows (or late_materialization_max_rows if larger), beyond which OFFSET becomes significantly slower.
  • Row Group Importance: The performance benefits of file_row_number heavily depend on the Parquet file having multiple row groups; a single large row group negates most advantages.
  • Silent Data Corruption: The most critical finding is that LIMIT/OFFSET without an explicit ORDER BY clause can lead to silently corrupted results (missing or duplicated rows) if DuckDB's preserve_insertion_order setting is disabled and multiple threads are used. This makes standard row counts unreliable for integrity checks.
  • Integrity Check Solution: The author recommends using crypto_hash_agg with a consistent ORDER BY to generate a hash of the result set, providing a robust method for verifying data integrity.
  • Paging Depth & Page Size: OFFSET performance degrades as users page deeper, while file_row_number maintains consistent speed. Page size alignment with Parquet's row group structure also influences absolute latency.
  • Stateless Tax: Paging itself introduces a performance overhead (1.5x to 7x slower) compared to streaming responses or allowing clients to read files directly, a cost often justified by stateless API requirements like resumability and bounded memory.

In conclusion, the article strongly advocates for using file_row_number with page boundaries derived from parquet_metadata() due to its superior performance characteristics and, crucially, its inherent resilience against the data integrity issues that plague LIMIT/OFFSET when preserve_insertion_order is not guaranteed.