从 DuckDB 直接查询 Postgres 表

Hannes Mühleisen

2022-09-30 · 14 分钟

介绍

PostgreSQL 是世界上最先进的开源数据库（自称）。从其作为学术 DBMS 的有趣开端，它在过去 30 年中已发展成为我们数字环境中的基本主力。

PostgreSQL 专为传统的事务处理用例，“OLTP”而设计，其中表的行被并发地创建、更新和删除，并且它在这方面表现出色。但这一设计决定使得 PostgreSQL 远不适合分析用例，“OLAP”，在 OLAP 中需要读取大量表块以创建存储数据的摘要。然而，许多用例中事务处理和分析都至关重要，例如在试图从事务数据中获取最新的商业智能洞察时。

曾有一些尝试构建在两种工作负载上都表现良好的数据库管理系统，“HTAP”，但总的来说，OLTP 和 OLAP 系统之间的许多设计决策都是艰难的权衡，这使得这项工作变得困难。鉴于一种尺寸毕竟不适合所有场景，系统通常是分离的，事务性应用程序数据存储在像 PostgreSQL 这样的专用系统中，而数据副本则存储在完全不同的 DBMS 中。使用专用分析系统可将分析查询速度提高几个数量级。

不幸的是，为分析目的维护数据副本可能会带来问题：随着新事务的处理，副本会立即过时，这需要一个复杂且不简单的同步设置。存储两个数据库副本也需要两倍的存储空间。例如，像 PostgreSQL 这样的 OLTP 系统传统上使用基于行的数据表示，而 OLAP 系统倾向于分块列式数据表示。如果不维护数据副本，你无法同时拥有这两种表示形式，并且会带来所有相关问题。此外，你所使用的 OLAP 系统与 Postgres 之间的 SQL 语法可能存在显著差异。

但设计空间并非像看起来那样非黑即白。例如，像 DuckDB 这样的系统中 OLAP 性能的提升不仅来自于分块列式磁盘数据表示。DuckDB 的大部分性能来自于其专为分析查询定制的矢量化查询处理引擎。如果 DuckDB 能够以某种方式读取存储在 PostgreSQL 中的数据呢？虽然这看起来令人望而生畏，但我们已经着手实现这一目标。

为了实现对 Postgres 数据库的快速且一致的分析读取，我们设计并实现了“Postgres 扫描器”。该扫描器利用 Postgres 客户端-服务器协议的二进制传输模式（有关更多详细信息，请参阅实现部分），使我们能够有效地转换数据并在 DuckDB 中直接使用。

除此之外，DuckDB 的设计与传统数据管理系统不同，因为 DuckDB 的查询处理引擎可以在几乎任意数据源上运行，而无需将数据复制到其自身的存储格式中。例如，DuckDB 目前可以直接查询Parquet 文件、CSV 文件、SQLite 文件、Pandas、R 和Julia 数据帧，以及Apache Arrow 源。这个新扩展增加了从 DuckDB 直接查询 PostgreSQL 表的能力。

用法

Postgres 扫描器 DuckDB 扩展的源代码可在 GitHub 上找到，但它可以通过 DuckDB 新的二进制扩展安装机制直接安装。要安装，只需运行以下 SQL 查询一次：

INSTALL postgres_scanner;

然后，每当你想使用该扩展时，你需要首先加载它：

LOAD postgres_scanner;

要使 DuckDB 能够访问 Postgres 数据库，请使用POSTGRES_ATTACH命令：

CALL postgres_attach('dbname=myshinydb');

postgres_attach 接受一个必需的字符串参数，即 libpq 连接字符串。例如，你可以传递 'dbname=myshinydb' 来选择不同的数据库名称。在最简单的情况下，参数只是 ''。该函数还有三个额外的命名参数：

• source_schema 要从中获取表的 Postgres 中非标准模式的名称。默认为 public。
• overwrite 是否应覆盖目标模式中现有视图，默认为 false。
• filter_pushdown DuckDB 从查询中派生的过滤谓词是否应转发到 Postgres，默认为 false。有关此参数控制内容的讨论，请参阅下文。

数据库中的表在 DuckDB 中注册为视图，你可以使用以下命令列出它们：

PRAGMA show_tables;

然后你可以像往常一样使用 SQL 查询这些视图。同样，没有数据被复制，这只是你 Postgres 数据库中表的虚拟视图。

如果你不想附加所有表，而只是查询单个表，可以使用 POSTGRES_SCAN 和 POSTGRES_SCAN_PUSHDOWN 表生成函数直接完成，例如：

SELECT * FROM postgres_scan('dbname=myshinydb', 'public', 'mytable');
SELECT * FROM postgres_scan_pushdown('dbname=myshinydb', 'public', 'mytable');

这两个函数都接受三个匿名字符串参数：libpq 连接字符串（见上文）、Postgres 模式名称和表名称。模式名称通常是 public。正如名称所示，带有“pushdown”变体将执行下文所述的选择下推。

Postgres 扫描器只能读取实际的表，不支持视图。但是，你当然可以在 DuckDB 中重新创建这些视图，语法应该完全相同！

实现

从架构角度来看，Postgres 扫描器作为 DuckDB 的插件扩展实现，在 DuckDB 中提供了一个所谓的表扫描函数（postgres_scan）。DuckDB 和其扩展中有许多此类函数，例如 Parquet 和 CSV 读取器、Arrow 读取器等。

Postgres 扫描器使用标准的 libpq 库，并对其进行静态链接。讽刺的是，这使得 Postgres 扫描器比其他 Postgres 客户端更容易安装。然而，Postgres 的常规客户端-服务器协议相当慢，因此我们花费了大量时间对其进行优化。值得注意的是，DuckDB 的SQLite 扫描器没有这个问题，因为 SQLite 也是一个进程内数据库。

我们实际上实现了一个用于 Postgres 数据库文件的原型直接读取器，但尽管性能很好，存在一个问题是已提交但尚未检查点的数据可能尚未存储在堆文件中。此外，如果检查点正在运行，我们的读取器会频繁地超越检查点器，导致额外的不一致性。我们放弃了这种方法，因为我们希望能够查询一个正在活跃使用的 Postgres 数据库，并且相信一致性很重要。另一个架构选项是为 Postgres 实现一个 DuckDB 外部数据封装器 (FDW)，类似于 duckdb_fdw，但这虽然可以改善协议状况，但在生产服务器上部署 Postgres 扩展的风险相当大，因此我们预计很少有人能够这样做。

相反，我们使用了 Postgres 客户端-服务器协议中不常用的二进制传输模式。这种格式与 Postgres 数据文件的磁盘表示非常相似，并避免了其他昂贵的字符串转换和从字符串转换。例如，要从协议消息中读取一个普通的 int32，我们所需要做的就是交换字节顺序（ntohl）。

Postgres 扫描器连接到 PostgreSQL，并使用二进制协议发出查询以读取特定表。在最简单的情况下（参见下面的优化），要读取名为 lineitem 的表，我们内部运行以下查询：

COPY (SELECT * FROM lineitem) TO STDOUT (FORMAT binary);

此查询将开始读取 lineitem 的内容，并以二进制格式直接写入协议流。

并行化

DuckDB 通过管道并行化支持自动查询内并行化，因此我们也希望并行化对 Postgres 表的扫描：我们的扫描操作符会打开多个到 Postgres 的连接，并从每个连接读取表的子集。为了有效地拆分表读取，我们使用了 Postgres 相对隐秘的 TID 扫描（元组 ID）操作符，它允许查询精确地从表中读取指定范围的元组 ID。元组 ID 的形式为 (page, tuple)。我们基于以 TID 表示的数据库页范围来并行化对 Postgres 表的扫描。每个扫描任务当前读取 1000 页。例如，要读取一个由 2500 页组成的表，我们将启动三个扫描任务，其 TID 范围分别为 [(0,0),(999,0)]、[(1000,0),(1999,0)] 和 [(2000,0),(UINT32_MAX,0)]。最后一个范围具有开放边界很重要，因为 pg_class 表中表的页数（relpages）只是一个估计值。对于给定的页范围 (P_MIN, P_MAX)，我们上面的查询因此扩展为如下所示：

COPY (
   SELECT 
     * 
   FROM lineitem 
   WHERE 
     ctid BETWEEN '(P_MIN,0)'::tid AND '(P_MAX,0)'::tid
   ) TO STDOUT (FORMAT binary);

这样，我们可以在不依赖任何模式的情况下高效地并行扫描表。由于 Postgres 中的页面大小是固定的，这还额外带来了一个好处：无论每行有多少列，读取页面子集的开销都是均等的。

“等等！” 你会说，根据文档，元组 ID 不稳定，可能会因 VACUUM ALL 等操作而改变。你怎么能用它来同步并行扫描呢？这是真的，也可能带来问题，但我们找到了解决方案：

事务同步

当然，像 Postgres 这样的事务型数据库在我们的分析表扫描运行时会执行事务。因此，我们需要处理对并行扫描的表的并发更改。我们通过首先在 DuckDB 的绑定阶段（即查询规划发生的地方）创建一个新的只读事务来解决这个问题。我们让这个事务一直运行，直到我们完全读取完表。我们使用了另一个鲜为人知的 Postgres 特性，pg_export_snapshot()，它允许我们在一个连接中获取当前的事务上下文，然后使用 SET TRANSACTION SNAPSHOT ... 将其导入到我们的并行读取连接中。这样，与单个表扫描相关的所有连接将在整个可能漫长的读取过程中，看到表状态与扫描开始时完全一致。

投影和选择下推

DuckDB 的查询优化器将选择（行上的过滤器）和投影（删除未使用的列）尽可能地向下推到查询计划的最低层（下推），甚至指示最底层的扫描操作符（如果它们支持的话）执行这些操作。对于 Postgres 扫描器，我们已经实现了这两种下推变体。投影相对简单——我们可以立即指示 Postgres 只检索查询使用的列。这当然也减少了需要传输的字节数，从而加快了查询速度。对于选择，我们从下推的过滤器构造一个 SQL 过滤表达式。例如，如果我们通过 Postgres 扫描器运行类似 SELECT l_returnflag, l_linestatus FROM lineitem WHERE l_shipdate < '1998-09-02' 的查询，它将运行以下查询：

COPY (
  SELECT 
    "l_returnflag",
    "l_linestatus" 
  FROM "public"."lineitem" 
  WHERE 
    ctid BETWEEN '(0,0)'::tid AND '(1000,0)'::tid AND 
    ("l_shipdate" < '1998-09-02' AND "l_shipdate" IS NOT NULL)
  ) TO STDOUT (FORMAT BINARY);
-- and so on

如你所见，投影和选择下推相应地扩展了针对 Postgres 运行的查询。使用选择下推是可选的。在某些情况下，在 Postgres 中运行过滤器实际上可能比传输数据并在 DuckDB 中运行过滤器更慢，例如当过滤器选择性不高时（许多行匹配）。

性能

为了研究 Postgres 扫描器的性能，我们使用其内部存储格式在 DuckDB 上运行了著名的 TPC-H 基准测试，也在 Postgres 上使用其内部格式运行，并使用新的 Postgres 扫描器从 Postgres 读取数据。我们使用了 DuckDB 0.5.1 和 Postgres 14.5，所有实验都在一台配备 M1 Max CPU 的 MacBook Pro 上运行。实验脚本可在此处获取。我们运行了 TPCH 的“扩展因子”1，创建了一个大约 1 GB 的数据集，其中最大表 lineitem 包含约 600 万行。22 个 TPC-H 基准测试查询中的每个都运行了 5 次，我们报告了以秒为单位的中位数运行时间。时间分解如下表所示。

普通版 Postgres 无法在一分钟超时内完成查询 17 和 20，因为其中包含对 lineitem 表的关联子查询。对于其他查询，我们可以看到带有 Postgres 扫描器的 DuckDB 不仅完成了所有查询，而且在大约一半的查询上比普通版 Postgres 更快，考虑到 DuckDB 必须如上所述通过客户端/服务器协议从 Postgres 读取其输入数据，这令人惊讶。当然，普通版 DuckDB 在使用其自身存储时仍然快 10 倍，但正如本文开头所讨论的，这首先需要将数据导入。

其他用例

Postgres 扫描器还可以用于以创新方式将实时 Postgres 数据与预缓存数据结合。这在处理只追加表时特别有效，但如果存在修改日期列，也可以使用。考虑以下 SQL 模板：

INSERT INTO my_table_duckdb_cache
SELECT * FROM postgres_scan('dbname=myshinydb', 'public', 'my_table') 
WHERE incrementing_id_column > (SELECT max(incrementing_id_column) FROM my_table_duckdb_cache);

SELECT * FROM my_table_duckdb_cache;

这在数据重复的代价下，提供了更快的查询性能和完全最新的查询结果。它还避免了复杂的数据复制技术。

DuckDB 内置支持将查询结果写入 Parquet 文件。Postgres 扫描器提供了一种相当简单的方法将 Postgres 表写入 Parquet 文件，如果需要甚至可以直接写入 S3。例如：

COPY (SELECT * FROM postgres_scan('dbname=myshinydb', 'public', 'lineitem')) TO 'lineitem.parquet' (FORMAT parquet);

结论

DuckDB 新的 Postgres 扫描器扩展可以在 PostgreSQL 运行时读取其表，并计算复杂 OLAP SQL 查询的结果，通常比 PostgreSQL 本身更快，而无需复制数据。Postgres 扫描器目前处于预览阶段，我们很想听听你的想法。如果你发现 Postgres 扫描器有任何问题，请在此报告。