在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
评论
还没有评论，欢迎留下第一条。