在DuckDB中分页读取Parquet文件：使用file_row_number还是Offset？

# 在 DuckDB 中分页读取 Parquet 文件：file_row_number 还是 OFFSET？—— Rusty Conover 来源：https://rusty.today/blog/paging-parquet-duckdb-file-row-number-vs-offset/ 我有一个大型 Parquet 文件和一个需要返回其内容的服务。返回两千万行很容易超过 API 响应的最大大小，而且无论你部署在什么平台上，都有一个上限：Lambda 对同步请求或响应限制为 6 MB (https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-limits.html)，Cloud Run 对 HTTP/1 响应限制为 32 MiB (https://docs.cloud.google.com/run/quotas)，除非你使用流式传输。即使没有平台限制，客户端也必须保存你发送的内容。 因此，内容一次返回一页，调用者不断请求直到获取所有内容。影响其他一切的是**每个请求都必须独立**。当负载均衡器后面有多个工作进程时，没有服务器端的状态可以从中恢复，因此“下一页”必须能够仅通过请求本身由任何工作进程随时重建。 显而易见的写法是使用 `LIMIT` 和 `OFFSET` (https://duckdb.org/docs/stable/sql/query_syntax/limit)，但令人担忧的是 `OFFSET 19000000` 必须计数超过一千九百万行才能找到你的页面，这会使整个文件扫描变成平方级复杂度。DuckDB 的 `read_parquet` (https://duckdb.org/docs/stable/data/parquet/overview) 有一个 `file_row_number` 选项，它提供每一行的物理位置，因此我可以直接按行范围过滤。契约保持不变——客户端仍然发回一个小值，服务器据此重建页面——但无需计数。 我原本预期证明 `OFFSET` 会重新读取页面之前的所有内容。但它没有，最终发现关键点根本不是速度。 ## 简而言之 对于一个有 163 个行组、两千万行的文件，基于行范围的方法在整个文件上比 `OFFSET` 快 **2.53 倍**。这在全部 37 次运行中都成立。 `` -- 替代 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 `` 这个行范围做了特定的事情。Parquet 文件以称为行组的块序列存储，DuckDB 可以从文件的页脚计算出给定行号范围所在的行组。页面之前的所有内容都会被跳过，而无需解压： 直接跳到所需行组WHERE file\_row\_number \>= $lo AND file\_row\_number < $hi已跳过，未解压你请求的行也已跳过每个块是一个行组 · 示意图，比例不精确 黄金路径是你的谓词。它到达包含你的行的块，而不触及前面的块——这就是为什么页面的成本不会随着你翻页越深而增长。在您采用这个数字之前，有两个注意事项。它完全依赖于你的文件有很多行组。速度也是支持行范围的较弱论据；更强的论据比性能问题更严重。 ## 你的文件有多少个行组？ DuckDB 一次跳过一个行组 (https://parquet.apache.org/docs/file-format/)。如果一个 Parquet 文件写入为单个巨大的行组，则没有东西可跳过，所以这种方法毫无帮助。在使用它进行规划之前，请使用 `parquet_metadata` (https://duckdb.org/docs/stable/data/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'); `` 如果返回 `1`，你可以停止阅读了。为了了解这有多重要，我两次写入相同的两百万行数据，相同的模式和数据，仅改变 `ROW_GROUP_SIZE`： 行组越多，优势越大 相同的 200 万行以两种方式写入，加上大文件作为参考。虚线表示持平。 1 个行组 200 万行 1.25× 17 个行组 200 万行 1.75× 163 个行组 2000 万行 2.53× 1 对 17 的对比是诚实的比较：相同的数据，仅 `ROW_GROUP_SIZE` 改变。163 的柱状图来自更大的文件，因此应解读为“趋势持续”，而不是同一曲线上的第三个点。当只有一个行组时，优势下降到 1.25 倍。它并没有消失，因为 DuckDB 还会在行组*内部*以 2048 行为一批丢弃行，因此一个窄窗口仍然会读取少于整个组的数据。只是你失去了大部分优势。 该图表中更有趣的数字是时钟时间而不是比率。相同的工作从 0.49 秒增加到 2.12 秒，**两种**方法都慢了 3 到 4 倍。如果你控制写入端，在优化其他任何东西之前先修复这个问题。DuckDB 自己的写入器默认每个组 122,880 行，这没问题。许多其他工具并非如此，DuckDB 的文件格式性能指南 (https://duckdb.org/docs/stable/guides/performance/file_formats) 对选择大小有自己的说明。 无论使用哪种查询，一个巨大的行组都是坏主意。 ## DuckDB 实际对你的 OFFSET 做了什么 我的平方级假设在这里被推翻了。对分页查询运行 `EXPLAIN` 会得到意想不到的结果： `` 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 将你的 `OFFSET` 重写为行号查找，并通过半连接将其与数据匹配。它为你代劳使用了 `file_row_number`，并以与手写版本相同的方式跳过行组。使用 `OFFSET` 分页不是平方级的。你付出的代价是额外的传递来计算出你请求的行号，并且这个传递在你翻页越深时变得更昂贵。 无论你是否显式写出 `file_row_number`，你已经在使用它了。唯一的问题是你是否控制它。 我最初的测量尝试假设了平方级模型，计时了 40 页，并拟合斜率来推断其余部分。结果斜率**为负**。负斜率说明模型有问题，而不是机器，因此我放弃了推断，直接测量了所有 163 页。 **重写存在一个悬崖。**它仅在 `LIMIT` 为 1,000,000 行或更少时触发。这是一个固定行数，而不是文件的一部分，对 50 万行文件和 2000 万行文件都一样。请求 1,000,000 行会触发重写；请求 1,000,001 行则不会，此时你真的在解压页面之前的所有内容。添加任何 `WHERE` 子句也会关闭它。当使用百万行页面时，两种方法之间的差距扩大到大约 5-6 倍。 这个数字在优化器中是常量 `LIMIT_MAX_VAL` (https://github.com/duckdb/duckdb/blob/main/src/optimizer/late_materialization.cpp)，但这并不是全部。还有一个设置 `late_materialization_max_rows`，有效截止点是两者中较大的： `late_materialization_max_rows` 重写触发上限为50（默认）1,000,000 行200,0001,000,000 行2,000,0002,000,000 行因此，将其提高到 100 万以下无效，提高到 100 万以上则移动悬崖。如果你确实需要百万以上的页面并希望继续使用 `OFFSET`，这就是调节旋钮。我仍然倾向于编写行范围，而不是依赖一个我看不到查询文本的优化器重写。 我还想证明跳过行组，而不是通过秒表推断，所以我在行组 0 的中间写了 128 字节的垃圾，使其无法解码。对整个文件进行全扫描会崩溃，对*行组 0 内部*的页面也是如此。而对行组 100 的页面返回的结果逐字节正确。它从未触及那些损坏的字节。 ## 应该让你担心的地方 `LIMIT`/`OFFSET` 没有 `ORDER BY`，因此它不保证*哪些*行会被返回。它今天能正常工作是因为 DuckDB 默认保持插入顺序 (https://duckdb.org/docs/stable/sql/dialect/order_preservation)。这是一个文档化的性能设置 (https://duckdb.org/docs/stable/configuration/overview)，人们会关闭它。 所以我关闭了它，启动了多个线程，并遍历了整个文件。两次运行都正好返回了 20,000,000 行： 运行缺失行数重复行数最大副本数16,131,7124,943,872526,408,1925,221,6325某些行从未出现，其他行出现五次，并且每次运行结果都不同。没有报错。 行数完美。大约 30% 的数据却不正确。 **因此，不要通过计数行来验证导出。**计数看不到这个问题，因为缺失和重复相互抵消。六百万行消失，五百万行被发送两次，但总数仍然恰好是 20,000,000。 改为对行进行哈希。crypto 扩展 (https://query.farm/duckdb_extension_crypto) 提供了 `crypto_hash_agg`，可以将整个结果集摘要为一个值： `` 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'); `` 对文件进行摘要，对客户端接收的内容进行摘要，比较两个十六进制字符串。这里两者都是 `94894c4eef00ea72...`，任何缺失或重复的行都会改变它。 先将行包装在 `hash()` 中，运行速度比转换为文本快 2.8 倍（20 万行时 1.4 秒 vs 4.0 秒），因为 blake3 每行只处理 8 个字节而不是格式化字符串。`hash()` 是 64 位的，因此在此大小的文件中，两个真正不同的行大约每 90,000 个文件碰撞一次，这对于完整性检查来说是可以接受的。 聚合内部的 `ORDER BY` 是必需的，如果没有，扩展会报错。排序使得摘要成为行多重集的函数，而不是到达顺序的函数，因此页面可以按任意顺序返回并保持一致。 这就是捕获错误的原因。行计数直接通过了它。 损坏需要两个条件：插入顺序关闭*并且*线程数超过一个。单线程下，`OFFSET` 分页没问题。这个组合足够狭窄，你可能永远不会遇到它，这正是它令人不悦之处。你离静默破坏导出只差一个设置更改的距离，而查询文本中没有任何提示。 `file_row_number` 不会发生这种情况。行在文件中的位置是关于文件的事实，因此 `[lo, hi)` 范围无论线程数或设置如何都能精确平铺。我测试了所有四种线程和插入顺序的组合，而不是假设。 有一个我最初确实犯错了的相关陷阱。*页面内部*的行顺序也不受保证，并且任何跨越多个行组的页面在这些设置下都会乱序返回。我的第一个测试说一切正常，因为我只测试了单个行组的页面，它们无法重新排序：它们始终只获取一个线程。如果页面内的顺序对你很重要，请添加 `ORDER BY file_row_number`（在 122,880 行页面时慢约 26%，百万行页面时慢 42%，并且会缓冲整个页面），或者返回该列让客户端排序。 ## 用户翻页的深度如何改变答案 2.53 倍假设每个页面只读取一次。真实流量很少如此，两种方法具有完全不同的曲线。`file_row_number` 在第 1 页和第 163 页的成本相同。`OFFSET` 每页大约增加四分之一毫秒，一直到底。 用户的分布很重要 相同的文件，相同的两个查询。只有读取的页面发生变化。 仅第一页 1.77× 前 10 页 1.80× 前 25% 的页面 1.93× 均匀分布所有页面 2.43× 后 25% 的页面 2.91× 仅最后一页 3.07× 仅第一页，约 1.8×。遍历整个文件则为 2.43×。在深处，则为 3.07×。对于 API 来说，问题不在于平均值——而在于 `OFFSET` 会随着用户深入而变得越慢。如果大多数人加载第一页后离开，你得到小数字；如果遍历文件，你得到大数字。无论哪种情况，曲线形状比比率更重要：`OFFSET` 随着用户深入而变慢，这与分页的期望正好相反。 ## 页面大小，以及一个我搞错的预测 我假设不合适的页面大小会破坏这种方法，因为跨越行组边界的页面会迫使 DuckDB 解压两个行组来服务一个页面。所以我测试了从 30,000 到 122,880 行的页面大小： 行数/页页数行范围OFFSET加速122,8801634.01 s9.70 s**2.45x**100,0002004.02 s9.44 s**2.27x**61,4403265.02 s13.02 s**2.56x**50,0004006.46 s15.95 s**2.44x**30,00066710.33 s24.77 s**2.68x**无论我选择什么，比率都保持在 2.27x 到 2.68x 之间，因为一个大小不合适的页面会使*两种*查询的成本都增加。它影响的是你的绝对延迟：122,880 行页面完成文件需要 4.01 秒，而 30,000 行页面需要 10.33 秒来读取完全相同的 2000 万行。 这里我搞错了两个事情。首先，对齐是错误的概念：重要的是页面大小与行组大小的关系。61,440 行的页面永远不会跨越边界，因为它能整除 122,880，但它*仍然*会读取整个 122,880 行的行组只给你一半。只有等于行组大小的页面才精确读取所需内容。 其次，我根据页脚算术预测成本，而不是实际测量。算术说 100,000 行页面应该多花 2.2 倍的成本。实际时钟显示几乎没有可测量的成本。那个 2,048 行过滤器正在做页脚算术不知道的更多工作。 根据文件页脚预测成本不等于测量成本。我先做了前者并相信了它。 从 `parquet_metadata()` 获取你的页面边界，并与行组匹配。不要将它们计算为 `page * 122880`。我文件中的最后一个行组有 93,440 行，任何由 Spark 或 Arrow 写入的内容都会按字节大小设定行组，因此计数会变化。 ## 无状态要求的代价 开头的框架排除了任何在请求之间保持状态的方法，这个决定是有代价的。以下是我尝试读取相同 2000 万行的所有方法： 方法时钟时间pyarrow `read_row_group(n)`**0.42 s**pyarrow `iter_batches`0.43 sDuckDB `to_arrow_reader`（单个流式查询）1.94 s`WHERE id >= ? AND id < ?`（排序列）2.85 s`file_row_number`，页面 = 行组2.90 s`LIMIT`/`OFFSET`7.05 s使用 DuckDB 的[`to_arrow_reader`](https://duckdb.org/docs/stable/clients/python/relational_api)进行单个流式查询会读取文件一次而不是 163 次，因此它比最佳分页方法快 1.5 倍。pyarrow 的 `ParquetFile` (https://arrow.apache.org/docs/python/generated/pyarrow.parquet.ParquetFile.html) 更快，而且它可以直接按索引寻址行组，DuckDB 没有相应的语法。 **分页相对于流式传输是 1.5 倍的代价，相对于直接交递文件是 7 倍的代价。**这就是无状态端点的成本，对于 API 来说通常值得支付：你可以获得可恢复性、两端有限的内存，以及任何工作进程都可以服务任何请求。这仍然是一个真实的数字，因此如果大小上限是*唯一*你分页的原因，在构建循环之前检查两件事： - **响应可以流式传输吗？**分块传输编码，或者通过单个长生命周期响应的 Arrow IPC 流，只需要一个请求和一次扫描。在接受迫使你分页的限制之前先检查：Cloud Run 的 32 MiB 限制仅当你不使用 `Transfer-Encoding: chunked` (https://docs.cloud.google.com/run/quotas) 时才适用；Lambda 的 6 MB 在响应流式传输 (https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-limits.html) 下前 6 MB 无上限。这些通常是缓冲体的限制，而不是总字节数。 - **客户端可以自己读取文件吗？**预签名 URL 和 30 秒的 pyarrow 胜过此列表中的任何方法，并且完全将你的服务从数据路径中移除。 如果两者都不可用，就进行分页。对于公共 REST API，通常两者都不可用。优势在真实并发下仍然存在：