在DuckDB中处理大型Parquet文件时，选择合适的分页方法至关重要。一项测试分析发现，使用file_row_number按行范围查询相比传统的LIMIT/OFFSET方法性能明显更优，且后者存在严重的数据完整性风险。[1]
在包含163行组、共2000万行的Parquet文件上，file_row_number方法的查询速度快2.53倍，这一优势在全部37次运行中保持一致。[1]更令人担忧的是，OFFSET在关闭插入顺序保存并启用多线程并发的条件下，会导致约30%的数据出现错误——包括600万行数据缺失和500万行数据重复。[1]这类错误容易被忽视，因为行计数检测无法发现问题所在：缺失行与重复行相互抵消，总行数仍保持在2000万行。[1]
DuckDB优化器在处理此类问题时存在阈值限制。当LIMIT不超过100万时，优化器会自动将OFFSET查询改写为基于行号的查询；超过此阈值则执行完全文件扫描。[1]值得注意的是，在单行组Parquet文件中，file_row_number仅快1.25倍，性能收益主要源于多行组场景的优化。[1]为确保分页导出的数据完整性，建议使用crypto_hash_agg('blake3', hash())进行验证。[1]
A detailed analysis of pagination techniques for large Parquet files in DuckDB reveals significant performance and correctness differences between two common approaches. Using file_row_number for range-based queries proves substantially faster than the LIMIT/OFFSET method, demonstrating a 2.53x speed advantage on a 20 million-row file organized in 163 row groups across all 37 test runs.[1] Beyond raw performance, the offset-based approach presents a critical data integrity issue: when insertion order preservation is disabled under multi-threaded conditions, approximately 30 percent of returned data becomes corrupted, with 6 million rows missing and 5 million rows duplicated.[1] This corruption goes undetected by simple row count verification, since missing and duplicate rows arithmetically offset each other, maintaining the total of 20 million rows.[1]
The underlying cause relates to DuckDB's query optimization strategy. The optimizer automatically rewrites OFFSET queries to row-number-based queries when the LIMIT value does not exceed one million, but resorts to full table scans for larger thresholds.[1] Performance benefits from file_row_number are most pronounced in multi-row-group Parquet scenarios; in single-row-group files, the speedup diminishes to only 1.25 times.[1] To ensure data integrity when exporting paginated results, the analysis recommends verifying completeness using crypto_hash_agg with blake3 hashing.[1]