【译】PostgreSQL的并行

PostgreSQL 是最好的对象关系数据库之一，它的架构是基于进程而不是基于线程的。虽然几乎所有当前的数据库系统都使用线程进行并行处理，但 PostgreSQL 的基于进程的架构是在 POSIX 线程之前实现的。PostgreSQL 在启动时启动一个进程“postmaster”，之后每当有新客户端连接到 PostgreSQL 时都会生成新进程。

在版本 10 之前，单个连接中没有并行性。诚然，来自不同客户端的多个查询由于进程架构而具有并行性，但它们无法从彼此中获得任何性能优势。换句话说，单个查询串行运行并且没有并行性。这是一个巨大的限制，因为单个查询不能利用多核。PostgreSQL 中的并行性是从 9.6 版开始引入的。从某种意义上说，并行性是单个进程可以有多个线程来查询系统并利用系统中的多核。这为 PostgreSQL 提供了查询内并行性。

PostgreSQL 中的并行性是作为涵盖顺序扫描、聚合和连接的多个功能的一部分实现的。

PostgreSQL 中的并行组件

PostgreSQL 中有三个重要的并行性组件。这些是过程本身process 、聚合gather和单个进程workers。如果没有并行性，进程本身会处理所有数据，但是，当规划器认为查询或其一部分可以并行化时，它会在执行计划的可并行化部分中添加一个 Gather 节点，并生成该子树的一个 Gather 根节点。 查询执行从进程（领导者）级别开始，计划的所有串行部分都由领导者运行。当然，如果对查询的任何部分（或全部）启用并允许并行性，则为它分配带有一组worker的gather node。worker 是与需要并行化的部分树（部分计划）并行运行的线程。blocks的关系由线程决定，以保持顺序性。启用的线程数由 PostgreSQL 配置文件中的设置控制。worker之间使用共享内存（shared memory）进行协调/通信，worker完成工作后，将结果传递给leader进行汇总。

并行顺序扫描

在 PostgreSQL 9.6 中，添加了对并行顺序扫描的支持。顺序扫描是对表的扫描，依次扫描块。就其本质而言，这允许并行性。因此，这是第一个实现并行性的自然候选者。在这种情况下，整个表在多个工作线程中被顺序扫描。这是一个简单的查询，我们查询 pgbench_accounts 表行 ( 63165 )，它有1500000000个元组。总执行时间为4343080ms 。由于没有定义索引，因此使用顺序扫描。整个表在单个进程中扫描。因此，无论有多少内核可用，都使用 CPU 的单核。

db=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE abalance > 0; QUERY PLAN----------------------------------------------------------------------

Seq Scan on pgbench_accounts (cost=0.00..73708261.04 rows=1 width=97)

(actual time=6868.238..4343052.233 rows=63165 loops=1)

Filter: (abalance > 0)

Rows Removed by Filter: 1499936835

Planning Time: 1.155 ms

Execution Time: 4343080.557 ms(5 rows)

如果这1,500,000,000行在一个进程中使用“10”个worker并行扫描会怎样？它将大大减少执行时间。

db=# EXPLAIN ANALYZE select * from pgbench_accounts where abalance > 0; QUERY PLAN ----------------------------------------------------------------------

Gather (cost=1000.00..45010087.20 rows=1 width=97)

​ (actual time=14356.160..1628287.828 rows=63165 loops=1)

​ Workers Planned: 10

​ Workers Launched: 10

​ -> Parallel Seq Scan on pgbench_accounts

​ (cost=0.00..45009087.10 rows=1 width=97)

​ (actual time=43694.076..1628068.096 rows=5742 loops=11)

​ Filter: (abalance > 0)

​ Rows Removed by Filter: 136357894

Planning Time: 37.714 ms

Execution Time: 1628295.442 ms(8 rows)

现在总执行时间是1628295ms；使用 10 个工作线程进行扫描时，这提高了266%。

用于Benchmark的查询： SELECT * FROM pgbench_accounts WHERE abalance > 0;

表大小：426GB

表中的总行数： 1500000000

用于测试的系统：

CPU : 2 Intel(R) Xeon(R) CPU E5-2643 v2 @ 3.50GHz

内存：256GB DDR3 1600

磁盘：ST3000NM0033

上图清楚地显示了并行性如何提高顺序扫描的性能。单个worker时，性能会降低，这是可以理解的，因为没有获得并行性，创建额外的gather节点和单个worker也会增加开销。但是，使用多个工作线程时，性能会显着提高。此外，重要的是要注意性能不会以线性或指数方式增加。它会逐渐改善，直到添加更多worker不会带来任何性能提升；有点像接近水平渐近线。该测试是在 64 核机器上执行的，很明显，拥有 10 个以上的worker不会带来任何显着的性能提升。

并行聚合

在数据库中，计算聚合是非常昂贵的操作。在单个过程中进行评估时，这些需要相当长的时间。在 PostgreSQL 9.6 中，通过简单地将它们分成块（分而治之的策略）添加了并行计算这些的能力。这允许多个worker各自计算之后再聚合给leader。从技术上讲，PartialAggregate 节点被添加到计划树中，每个 PartialAggregate 节点从一个worker那里获取输出。然后将这些输出发送到 FinalizeAggregate 节点，该节点组合来自多个（所有）PartialAggregate 节点的聚合。如此有效地，并行部分计划包括根处的 FinalizeAggregate 节点和将 PartialAggregate 节点作为子节点的 Gather 节点。

db=# EXPLAIN ANALYZE SELECT count(*) from pgbench_accounts;

QUERY PLAN ----------------------------------------------------------------------

Aggregate (cost=73708261.04..73708261.05 rows=1 width=8)

(actual time=2025408.357..2025408.358 rows=1 loops=1)

-> Seq Scan on pgbench_accounts (cost=0.00..67330666.83 rows=2551037683 width=0) (actual time=8.162..1963979.618 rows=1500000000 loops=1)

Planning Time: 54.295 ms Execution Time: 2025419.744 ms(4 rows)

以下是要并行评估聚合时的计划示例。您可以在这里清楚地看到性能改进。

db=# EXPLAIN ANALYZE SELECT count(*) from pgbench_accounts;

QUERY PLAN ----------------------------------------------------------------------

Finalize Aggregate (cost=45010088.14..45010088.15 rows=1 width=8) (actual time=1737802.625..1737802.625 rows=1 loops=1)

-> Gather (cost=45010087.10..45010088.11 rows=10 width=8) (actual time=1737791.426..1737808.572 rows=11 loops=1)

Workers Planned: 10

Workers Launched: 10

-> Partial Aggregate

(cost=45009087.10..45009087.11 rows=1 width=8)

​ (actual time=1737752.333..1737752.334 rows=1 loops=11)

-> Parallel Seq Scan on pgbench_accounts

(cost=0.00..44371327.68 rows=255103768 width=0) (actual time=7.037..1731083.005 rows=136363636 loops=11) Planning Time: 46.031 ms Execution Time: 1737817.346 ms(8 rows)

使用并行聚合，在这种特殊情况下，当涉及 10 个并行worker时，由于 2025419.744的执行时间减少到1737817.346 ，我们的性能提升了 16% 以上。

用于Benchmark的查询： SELECT count(*) FROM pgbench_accounts WHERE abalance > 0;

表大小：426GB

表中的总行数： 1500000000

用于Benchmark测试的系统：

CPU : 2 Intel(R) Xeon(R) CPU E5-2643 v2 @ 3.50GHz

内存：256GB DDR3 1600

磁盘：ST3000NM0033

并行索引（B-Tree）扫描

对 B-Tree 索引的并行支持意味着索引页面是并行扫描的。B-Tree 索引是 PostgreSQL 中最常用的索引之一。在 B-Tree 的并行版本中，worker 扫描 B-Tree，当它到达其叶节点时，它会扫描块并触发阻塞等待 worker 扫描下一个块。

有点困惑？让我们看一个例子。假设我们有一个包含 id 和 name 列的表 foo，有 18 行数据。我们在表 foo 的 id 列上创建一个索引。系统字段 CTID 附加到表的每一行，用于标识该行的物理位置。CTID 列中有两个值：块号和偏移量。

postgres=# SELECT ctid, id FROM foo;

ctid \| id

--------+-----

(0,55) | 200

(0,56) | 300

(0,57) | 210

(0,58) | 220

(0,59) | 230

(0,60) | 203

(0,61) | 204

(0,62) | 300

(0,63) | 301

(0,64) | 302

(0,65) | 301

(0,66) | 302

(1,31) | 100

(1,32) | 101

(1,33) | 102 (1,34) | 103

(1,35) | 104

(1,36) | 105

(18 rows)

让我们在该表的 id 列上创建 B-Tree 索引。

CREATE INDEX foo_idx ON foo(id)

假设我们要选择 id <= 200 且有 2 个worker的值。Worker-0 将从根节点开始，一直扫描到叶子节点 200。它将节点 105 下的下一个块移交给处于阻塞等待状态的 Worker-1。如果还有其他worker，则将块划分为worker。重复类似的模式，直到扫描完成。

并行位图扫描

要并行化位图堆扫描，我们需要能够以非常类似于并行顺序扫描的方式在worker之间划分块。为此，对一个或多个索引进行扫描，并创建指示要访问哪些块的位图。这是由leader进程完成的，即这部分扫描是按顺序运行的。但是，当将识别出的块传递给worker时，并行性就会启动，这与并行顺序扫描中的方式相同。

并行join

merge joins中的并行性也是此版本中添加的最热门功能之一。在这种情况下，一个表与其他表的 inner loop hash 或merge连接。任何情况下， inner loop不支持并行性。将整个循环作为一个整体进行扫描，当每个worker作为一个整体执行内部循环时，就会发生并行。每个连接发送到聚集的结果累积并产生最终结果。

概括

从我们在本博客中已经讨论过的内容中可以明显看出，并行性可以显着提升某些性能，而对另一些情况则略有提升，并且在某些情况下可能会导致性能下降。确保正确设置了 =parallel_setup_cost 或 parallel_tuple_cost，以使查询计划程序能够选择并行计划。即使为这些 GUI 设置了较低的值，如果没有生成并行计划，请参阅 PostgreSQL 并行性文档以获取详细信息。

对于并行执行计划，您可以获得每个计划节点的每个worker的统计信息，以了解负载在worker之间的分布情况。您可以通过 EXPLAIN (ANALYZE, VERBOSE) 做到这一点。与任何其他性能特性一样，没有适用于所有情况的规则。应根据需要配置是否开启并行，并且您必须确保获得性能的概率明显高于性能下降的概率。

https://www.percona.com/blog/2019/07/30/parallelism-in-postgresql/