直接从 DuckDB 查询 Postgres 表

介绍

PostgreSQL 是世界上最先进的开源数据库（自称^[1]）。从它作为学术 DBMS 开始^[2]，在过去 30 年中已经发展成为我们数字环境的重要主力。

PostgreSQL 专为传统事务用例 OLTP^[3]而设计，其中表中的行同时创建、更新和删除，它在这方面表现出色。但是这种设计决策使得 PostgreSQL 远不适合分析用例 OLAP^[4]，在这种情况下，要读取大块的表来创建存储数据的摘要。然而，在许多用例中，交易用例和分析用例都很重要，例如，当试图获得对交易数据的最新商业智能洞察力时。

已经有人尝试构建在两种工作负载 HTAP 上都表现出色的数据库管理系统^[5]，但总的来说，OLTP 和 OLAP 系统之间的许多设计决策都是艰难的权衡，这使得这项工作变得很困难。我们得接受一种方法终究不能适用于所有人的事实，系统通常是分开的，事务应用程序数据存在于像 PostgreSQL 这样的专用系统中，而数据的副本存储在完全不同的 DBMS 中。使用专门构建的分析系统可将分析查询速度提高几个数量级。

不幸的是，出于分析目的维护数据副本可能会出现问题：随着新事务的处理，副本将立即过时，需要复杂且重要的同步设置。存储数据库的两个副本也将需要两倍的存储空间。例如，像 PostgreSQL 这样的 OLTP 系统传统上使用基于行的数据表示，而 OLAP 系统倾向于支持分块列数据表示。如果不维护数据副本以及随之而来的所有问题，您就无法同时拥有这两者。此外，您使用的任何 OLAP 系统与 Postgres 之间的 SQL 语法可能会有很大差异。

但设计空间并不像看起来那样黑白分明。例如，像 DuckDB 这样的系统中的 OLAP 性能不仅来自分块列式磁盘数据表示。DuckDB 的大部分性能来自其针对分析查询进行定制调整的 矢量化查询处理引擎。如果 DuckDB 能够以某种方式读取存储在 PostgreSQL 中的数据会怎样？虽然这似乎令人生畏，但我们已经开始寻求使这成为可能。

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

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

用法

Postgres Scanner DuckDB 扩展源代码 可在 GitHub^[14] 获得，但可以通过 DuckDB 新的二进制扩展安装机制直接安装（上个星期我刚自己编译体验过，过程比较繁琐）。要安装，只需运行以下 SQL 查询一次（第一次安装时间有点长，耐心点）：

INSTALL postgres_scanner;

然后，无论何时要使用扩展，都需要先加载它：

LOAD postgres_scanner;

要使 DuckDB 可以访问 Postgres 数据库，请使用以下 POSTGRES_ATTACH 命令：

CALL postgres_attach('dbname=myshinydb');

postgres_attach 接受一个必需的字符串参数，即 libpq 连接字符串^[15]。例如，您可以通过 '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 Scanner 只能读取实际的表，不支持视图。但是，您当然可以在 DuckDB 中重新创建此类视图，语法应该完全相同！

执行

从架构的角度来看，Postgres Scanner 是作为 DuckDB 的插件扩展实现的，它在 DuckDB 中提供了所谓的表扫描功能 ( postgres_scan)。DuckDB 扩展中有很多这样的功能，例如 Parquet 和 CSV 阅读器、Arrow 阅读器等。

Postgres Scanner 使用 libpq 静态链接的标准库。具有讽刺意味的是，这使得 Postgres Scanner 比其他 Postgres 客户端更易于安装。但是，Postgres 的普通客户端-服务器协议非常慢^[16]，所以我们花了很多时间优化它。需要注意的是，DuckDB 的 SQLite Scanner^[17] 不会遇到这个问题，因为 SQLite 也是一个进程内数据库。

我们实际上为 Postgres 的数据库文件实现了一个原型直接读取器，但是虽然性能很好，但存在已提交但尚未检查点的数据不会存储在堆文件中的问题。此外，如果检查点当前正在运行，我们的阅读器会经常超过检查点，从而导致额外的不一致。我们放弃了这种方法，因为我们觉得保证和 Postgres 数据一致性很重要。另一种架构选择是为 Postgres 实现一个 DuckDB 外部数据包装器 (FDW)，类似于 duckdb_fdw^[18]（鄙人开发），但是虽然这可以改善协议情况，但在生产服务器上部署 postgres 扩展是非常危险的，所以我们预计很少有人能够这样做（被当成了反面教材？😭）。

相反，我们使用 Postgres 客户端-服务器协议中很少使用的 二进制传输模式 。这种格式与 Postgres 数据文件的磁盘表示非常相似，并且避免了一些原本昂贵的 to-string 和 from-string 转换。例如，要从协议消息中读取一个普通的 int32 ，我们需要做的只是交换字节顺序（ ntohl^[19]）。

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

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

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

并行化

DuckDB 通过管道并行支持自动查询内并行化，因此我们还希望对 Postgres 表进行并行扫描：我们的扫描运算符打开到 Postgres 的多个连接，并从每个连接中读取表的子集。为了有效地拆分读取表，我们使用 Postgres 相当晦涩的 TID Scan (Tuple ID) 运算符，它允许查询从表中手术式地读取指定范围的元组 ID。元组 ID 的形式为 (page, tuple)。我们基于以 TID 表示的数据库页面范围并行化对 Postgres 表的扫描。每个扫描任务当前读取 1000 页。例如，要读取包含 2500 页的表，我们将启动三个具有 TID 范围的扫描任务 [(0,0),(999,0)]，[(1000,0),(1999,0)] 并且 [(2000,0),(UINT32_MAX,0)].  对最后一个范围有一个开放界限很重要，因为表中的页数 ( relpages)pg_class 只是一个估计值。对于给定的页面范围（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 Scanner，我们已经实现了两种下推变体。投影相当简单——我们可以立即指示 Postgres 只检索查询正在使用的列。这当然也减少了需要传输的字节数，从而加快了查询速度。对于选择，我们从下推过滤器构造一个 SQL 过滤器表达式。例如，如果我们 SELECT l_returnflag, l_linestatus FROM lineitem WHERE l_shipdate < '1998-09-02' 通过 Postgres 扫描程序运行查询，它将运行以下查询：

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 Scanner 的性能，我们在 DuckDB 上使用其内部存储格式运行了著名的 TPC-H 基准测试，在 Postgres 上也使用其内部格式并使用新的 Postgres Scanner 从 Postgres 读取 DuckDB。我们使用 DuckDB 0.5.1 和 Postgres 14.5，所有实验均在配备 M1 Max CPU 的 MacBook Pro 上运行。实验脚本可用^[20]。我们运行 TPCH 的“比例因子” 为 1，创建了一个大约 1 GB 的数据集。最大表中有 6 M 行，lineitem. 22 个 TPC-H 基准查询中的每一个都运行了 5 次，我们以秒为单位报告运行时间的中值。下表给出了时间细分对比。

Query	DuckDB	Postgres_scanner	Postgres
1	0.03	0.74	1.12
2	0.01	0.20	0.18
3	0.02	0.55	0.21
4	0.03	0.52	0.11
5	0.02	0.70	0.13
6	0.01	0.24	0.21
7	0.04	0.56	0.20
8	0.02	0.74	0.18
9	0.05	1.34	0.61
10	0.04	0.41	0.35
11	0.01	0.15	0.07
12	0.01	0.27	0.36
13	0.04	0.18	0.32
14	0.01	0.19	0.21
15	0.03	0.36	0.46
16	0.03	0.09	0.12
17	0.05	0.75	> 60.00
18	0.08	0.97	1.05
19	0.03	0.32	0.31
20	0.05	0.37	> 60.00
21	0.09	1.53	0.35
22	0.03	0.15	0.15

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

其他用例

Postgres Scanner 还可用于以创造性的方式将实时 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 Scanner 提供了一种将 Postgres 表写入 Parquet 文件的相当简单的方法，如果需要，它甚至可以直接写入 S3。例如，

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

结论

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

参考资料

[1]

自称: https://www.postgresql.org/

[2]

作为学术 DBMS 开始: https://dsf.berkeley.edu/papers/ERL-M90-34.pdf

[3]

事务用例 OLTP: https://en.wikipedia.org/wiki/Online_transaction_processing

[4]

分析用例 OLAP: https://en.wikipedia.org/wiki/Online_analytical_processing

[5]

构建在两种工作负载 HTAP 上都表现出色的数据库管理系统: https://en.wikipedia.org/wiki/Hybrid_transactional/analytical_processing

[6]

实现部分: https://duckdb.org/2022/09/30/postgres-scanner.html#Implementation

[7]

Parquet 文件: https://duckdb.org/docs/data/parquet

[8]

CSV 文件: https://duckdb.org/docs/data/csv

[9]

SQLite 文件: https://github.com/duckdblabs/sqlite_scanner

[10]

Pandas: https://duckdb.org/docs/guides/python/sql_on_pandas

[11]

R: https://duckdb.org/docs/api/r#efficient-transfer

[12]

Julia: https://duckdb.org/docs/api/julia#scanning-dataframes

[13]

Apache Arrow 源: https://duckdb.org/docs/guides/python/sql_on_arrow

[14]

GitHub: https://github.com/duckdblabs/postgresscanner

[15]

libpq 连接字符串: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

[16]

非常慢: https://ir.cwi.nl/pub/26415/p852-muehleisen.pdf

[17]

SQLite Scanner: https://github.com/duckdblabs/sqlite_scanner

[18]

duckdb_fdw: https://github.com/alitrack/duckdb_fdw

[19]

ntohl: https://linux.die.net/man/3/ntohl

[20]

可用: https://gist.github.com/hannes/d2f0914a8e0ed0fb235040b9981c58a7

[21]

报告: https://github.com/duckdblabs/postgresscanner/issues

原文：https://duckdb.org/2022/09/30/postgres-scanner.html