18.1、可插拔的存储引擎

PostgreSQL使用的数据布局既不是唯一可能的，也不是所有加载类型的最佳布局。遵循可扩展性的理念，PostgreSQL允许创建和插入各种表访问方法(可插入存储引擎)，但目前只有一种可用的开箱即用方法:

=> SELECT amname, amhandler FROM pg_am WHERE amtype = 't';
amname  | amhandler
−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−
heap    | heap_tableam_handler
(1 row)

您可以指定在创建表时使用的引擎(CREATE TABLE ... USING); 否则，将应用堆default_table_access_method参数中列出的默认引擎。

为了使PostgreSQL内核以相同的方式与各种引擎一起工作，表访问方法必须实现一个特殊的接口^[1]在amhandler列中指定的函数返回包含核心所需的所有信息的接口结构^[2]。

下面的核心组件可以被所有的表访问方法使用:

• 事务管理器，包括ACID以及快照隔离的支持
• 缓冲管理器
• I/O子系统
• TOAST
• 优化器和执行器
• 索引支持

即使引擎不全部使用这些部件，这些部件也始终可以供引擎使用。

反过来，引擎定义:

• 元组格式和数据结构
• 表扫描实现和成本估计
• 增、删、改的实现以及锁的操作
• 可见性规则
• vacuum和分析过程

从历史上看，PostgreSQL使用单一的内置数据存储，没有任何适当的编程接口，所以现在很难想出一个好的设计，考虑到标准引擎的所有细节，而不影响其他方法。

例如，目前还不清楚如何处理WAL。新的访问方法可能需要记录内核不知道的自己的操作。现有的通用的WAL机制通常是一个糟糕的选择，因为它会带来太多的开销。您可以添加另一个接口来处理新类型的WAL条目，但是崩溃恢复将依赖于外部代码，这是非常不可取的。到目前为止，唯一可行的解决方案是为每个特定的引擎修补核心。

由于这个原因，我没有尽力提供表访问方法和核心之间的任何严格区别。本书前面部分描述的许多特性在形式上属于堆访问方法，而不是内核本身。这种方法可能永远是PostgreSQL的最终标准引擎，而其他方法将填补不同的生态，以解决特定负载类型的挑战。

在所有正在开发的新引擎中，我想提到以下几点:

Zheap^[3] 旨在对抗表的膨胀。它实现就地行更新，并将历史MVCC相关数据移动到单独的撤销存储中。这种引擎对于涉及频繁数据更新的负载非常有用。

对于Oracle用户来说，Zheap体系结构似乎很熟悉，尽管它确实有一些细微的差别(例如，索引访问方法的接口不允许创建带有自己版本的索引)。

Zedstore^[4] 实现了列式存储，这对于查询来说可能是最有效的。

存储的数据结构为元组ID的B-tree; 每个列都存储在与主列相关联的自己的B-树中。将来，可能会在一棵B-树中存储多个列，从而获得混合存储。

18.2、顺序扫描

存储引擎定义表数据的物理布局，并提供对它的访问方法。唯一支持的方法是顺序扫描，它完整地读取表主分支的文件(或多个文件)。在每个读页面中，检查每个元组的可见性; 那些不满足查询的元组将被过滤掉。

扫描过程通过缓冲区缓存; 为了确保大表不会占用有用的数据，使用了一个小的缓冲区环。其他正在扫描同一表的进程加入这个缓冲环，从而避免额外的磁盘读取; 这样的扫描称为同步扫描。因此，扫描并不总是必须从文件的开头开始。

顺序扫描是读取整个表或表的最佳部分的最有效方法。换句话说，当选择性较低时，顺序扫描带来的价值最大。(如果选择性很高，这意味着查询必须只选择几行，那么最好使用索引.)

成本估计

在查询执行计划中，顺序扫描由Seq scan节点表示:

=> EXPLAIN SELECT * FROM flights;
		QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Seq Scan on flights (cost=0.00..4772.67 rows=214867 width=63)
(1 row)

估计的行数是基本统计数据的一部分:

=> SELECT reltuples FROM pg_class WHERE relname = 'flights';
reltuples
−−−−−−−−−−−
214867
(1 row)

在估计成本时，优化器会考虑以下两个组件: 磁盘I/O和CPU资源。

I/O 成本 是通过将表中的页面数乘以读取单个页面的成本来计算的，假设页面是顺序读取的。当缓冲区管理器请求一个页面时，操作系统实际上从磁盘读取更多的数据，因此很可能在操作系统缓存中找到几个后续页面。由于这个原因，使用顺序扫描读取单个页面的成本(规划器估计为seq_page_cost) 低于随机访问成本(由random_page_cost值定义)。

默认设置对HDD有效; 如果您使用的是SSD，那么显著减少random_page_cost值(默认为4) 是有意义的 (seq_page_cost参数通常保持原样，作为一个参考值: 1)。由于这些参数之间的最佳比例取决于硬件，因此它们通常在表空间级别设置(ALTER TABLESPACE ... SET)．

=> SELECT relpages,
	current_setting('seq_page_cost') AS seq_page_cost,
	relpages * current_setting('seq_page_cost')::real AS total
FROM pg_class WHERE relname = 'flights';
  relpages | seq_page_cost  | total
  −−−−−−−−−−+−−−−−−−−−−−−−−−+−−−−−−−
	2624   | 1              | 2624
 (1 row)

这些计算清楚地显示了由于不及时的vacuum而导致的表膨胀的后果: 表的主分支越大，需要扫描的页面就越多，而不管它们包含的活动元组的数量是多少。

CPU资源估计 包含处理每个元组的成本(规划器估计为cpu_tuple_cost): 0.01

=> SELECT reltuples,
	current_setting('cpu_tuple_cost') AS cpu_tuple_cost,
	reltuples * current_setting('cpu_tuple_cost')::real AS total
   FROM pg_class WHERE relname = 'flights';
reltuples  | cpu_tuple_cost |total
−−−−−−−−−−−+−−−−−−−−−−−−−−−−+−−−−−−−−−
214867     | 0.01           | 2148.67
(1 row)

这两个估计的总和就是这个计划的总成本。启动成本为零，因为顺序扫描没有先决条件。

如果需要过滤扫描的表，则应用的过滤条件将出现在Seq Scan节点的filter部分下的计划中。估计的行数取决于这些条件的选择性，而成本估计包括相关的计算费用。

命令EXPLAIN ANALYZE显示实际返回的行数和已过滤掉的行数：

=> EXPLAIN (analyze, timing off, summary off) SELECT * FROM flights
		WHERE status = 'Scheduled';
				QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Seq Scan on flights
	(cost=0.00..5309.84 rows=15383 width=63)
	(actual rows=15383 loops=1)
	Filter: ((status)::text = 'Scheduled'::text)
	Rows Removed by Filter: 199484
(5 rows)

让我们来看一个使用聚合的更复杂的执行计划：

=> EXPLAIN SELECT count(*) FROM seats;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Aggregate (cost=24.74..24.75 rows=1 width=8)
	−> Seq Scan on seats (cost=0.00..21.39 rows=1339 width=0)
(2 rows)

该计划由两个节点组成：上层节点（Aggregate）计算计数函数，从下层节点（Seq Scan）提取数据，下层节点扫描表。

聚合节点的启动成本包括聚合本身：如果不从较低的节点获取所有行，就不可能返回第一行（这是本例中唯一的一行）。聚合成本是根据每个输入行的条件操作的执行成本（估计为0.0025 cpu_operator_cost）来估计的：

=> SELECT reltuples,
	current_setting('cpu_operator_cost') AS cpu_operator_cost,
	round((
		reltuples * current_setting('cpu_operator_cost')::real
	)::numeric, 2) AS cpu_cost
	FROM pg_class WHERE relname = 'seats';
reltuples  | cpu_operator_cost | cpu_cost
−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−
1339       | 0.0025            | 3.35
(1 row)

接收到的估计被添加到Seq Scan节点的总成本中。

Aggregate节点的总成本还包括处理要返回的一行的成本，估计为0.01 cpu_tuple_cost：

=> WITH t(cpu_cost) AS (
    SELECT round((
    reltuples * current_setting('cpu_operator_cost')::real
    )::numeric, 2)
    FROM pg_class WHERE relname = 'seats'
)
SELECT 21.39 + t.cpu_cost AS startup_cost,
    round((
    21.39 + t.cpu_cost +
    1 * current_setting('cpu_tuple_cost')::real
    )::numeric, 2) AS total_cost
FROM t;
startup_cost  | total_cost
−−−−−−−−−−−−−−+−−−−−−−−−−−−
        24.74 | 24.75
(1 row)

因此，成本估算依赖关系可以描绘如下：

18.3、并行计划

Postgre支持并行查询执行^[5]。执行查询的父进程（通过postmaster）生成几个工作进程，这些进程同时执行计划的一个并行部分。结果被传递给leader， leader将它们放在Gather节点^[6]中。当不接受数据时，领导者也可以参与计划并行部分的执行。

如果需要，您可以通过关闭parallel_leader_participation参数来禁止leader对并行计划执行的贡献。

当然，启动这些进程并在它们之间发送数据并不是免费的，所以到目前为止并不是所有的查询都应该并行化。

此外，即使允许并行执行，也不是计划的所有部分都可以并发处理。在顺序模式下，有些操作由leader进程单独执行。

Postgre不支持另一种并行计划执行的方法，这种方法是由几个worker组成一个装配线来执行数据处理（粗略地说，每个计划节点由一个单独的进程执行）；这种机制被PostgreSQL开发者认为是低效的。

18.4、并行顺序扫描

并行顺序扫描节点是为并行处理设计的节点之一，它执行并行顺序扫描。

这个名字听起来有点争议（扫描到底是顺序的还是并行的？），但是，它反映了操作的本质。如果我们看一下文件访问，就会发现表页是按顺序读取的，按照简单顺序扫描读取它们的顺序。然而，这个操作是由几个并发进程执行的。为了避免对同一个页面进行两次扫描，执行程序通过共享内存同步这些进程。

这里有一个微妙的方面，操作系统没有得到典型的顺序扫描的大画面；相反，它看到几个执行随机读取的进程。因此，通常加速顺序扫描的数据预取实际上变得毫无用处。为了尽量减少这种不愉快的影响，PostgreSQL为每个进程分配了几个连续的页面^[7]，而不是一个页面.

因此，并行扫描没有多大意义，因为通常的读取成本进一步增加了进程间数据传输所产生的开销。但是，如果工作线程对获取的行执行任何后处理（例如聚合），则总执行时间可能会短得多。

成本估计

让我们看一个在一个大表上执行聚合的简单查询。执行计划是并行化的：

=> EXPLAIN SELECT count(*) FROM bookings;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Finalize Aggregate (cost=25442.58..25442.59 rows=1 width=8)
−> Gather (cost=25442.36..25442.57 rows=2 width=8)
	Workers Planned: 2
		−> Partial Aggregate(cost=24442.36..24442.37 rows=1 width=8)
		−> Parallel Seq Scan on bookings (cost=0.00..22243.29 rows=879629 width=0)
(7 rows)

Gather下面的所有节点都属于计划的并行部分。它们由每个worker执行（这里计划了两个），也可能由leader进程执行（除非parallel_leader_participation参数关闭了此功能）。Gather节点本身及其上面的所有节点构成计划的顺序部分，并由leader进程单独执行。

Parallel Seq Scan节点表示并行堆扫描。rows字段显示单个进程要处理的估计平均行数。总而言之，执行必须由三个进程执行（一个leader和两个worker），但是leader进程将处理更少的行：它的份额随着worker数量的增加^[8]而变小在这种特殊情况下，因子是2.4。

=> SELECT reltuples::numeric, round(reltuples / 2.4) AS per_process
FROM pg_class WHERE relname = 'bookings';
 reltuples | per_process
−−−−−−−−−−−+−−−−−−−−−−−−−
2111110    | 879629
(1 row)

并行序列扫描的成本计算类似于顺序扫描。接收值更小，因为每个进程处理的行更少；I/O部分是完整的，因为整个表仍然需要逐页读取：

=> SELECT round((
	relpages * current_setting('seq_page_cost')::real +
	reltuples / 2.4 * current_setting('cpu_tuple_cost')::real
)::numeric, 2)
FROM pg_class WHERE relname = 'bookings';

round
−−−−−−−−−−
22243.29
(1 row)

接下来，Partial Aggregate节点对获取的数据执行聚合；在本例中，它计算行数。

聚合成本以常用的方式估计，并将其添加到表扫描的成本估计中：

=> WITH t(startup_cost)
AS (
    SELECT 22243.29 + round((
    reltuples / 2.4 * current_setting('cpu_operator_cost')::real
    )::numeric, 2)
    FROM pg_class
    WHERE relname = 'bookings'
)
SELECT startup_cost,
    startup_cost + round((
    1 * current_setting('cpu_tuple_cost')::real
    )::numeric, 2) AS total_cost
FROM t;
startup_cost  | total_cost
−−−−−−−−−−−−−−+−−−−−−−−−−−−
24442.36      | 24442.37
(1 row)

下一个节点（Gather）由领导进程执行。该节点负责启动worker并收集它们返回的数据。

为了进行规划，启动进程（不管它们的数量）的成本估计是由 parallel_setup_cost（默认1000）参数定义的，而进程之间的每一行传输的成本估计为 parallel_tuple_cost （默认为0.1)。

在本例中，启动成本（用于启动流程）占主导地位；这个值被添加到部分聚合节点的启动成本中。总成本还包括转移两行的成本；该值被添加到部分聚合节点^[9]的总开销中：

=> SELECT 24442.36 + round(
    current_setting('parallel_setup_cost')::numeric,
    2) AS setup_cost,
	24442.37 + round(
    current_setting('parallel_setup_cost')::numeric +
    2 * current_setting('parallel_tuple_cost')::numeric,
    2) AS total_cost;
setup_cost  | total_cost
−−−−−−−−−−−−+−−−−−−−−−−−−
25442.36    | 25442.57
(1 row)

最后但并非最不重要的一点是，Finalize Aggregate节点聚合Gather节点从并行进程接收到的所有部分结果。

最终的聚合就像其他任何聚合一样被估计。它的启动成本基于聚合三行的成本；这个值被添加到Gather的总成本中（因为计算结果需要所有行）。Finalize Aggregate的总成本还包括返回一行的成本。

=> WITH t(startup_cost) AS (
    SELECT 25442.57 + round((
    3 * current_setting('cpu_operator_cost')::real
    )::numeric, 2)
FROM pg_class WHERE relname = 'bookings'
)
SELECT startup_cost,
    startup_cost + round((
    1 * current_setting('cpu_tuple_cost')::real
    )::numeric, 2) AS total_cost
FROM t;
startup_cost  | total_cost
−−−−−−−−−−−−−−+−−−−−−−−−−−−
25442.58      | 25442.59
(1 row)

成本估算之间的依赖关系取决于节点在将结果传递给父节点之前是否必须累积数据。在获得所有输入行之前，聚合不能返回结果，因此它的启动成本基于较低节点的总成本。相反，Gather节点在获取行后立即开始向上游发送行。因此，该操作的启动成本取决于下节点的启动成本，而其总成本取决于下节点的总成本。

下边是相关的依赖图：

18.5、并行执行限制

后台worker进程数量

进程的数量由三个参数的层次结构控制。并发运行的后台工作进程程的最大数量由 max_worker_processes 值(默认为8)定义。

然而，并行查询执行并不是唯一需要后台工作者进程的操作。例如，它们还参与逻辑复制，并可由扩展使用。专门为并行计划执行分配的进程数被限制为max_parallel_workers值8。

在这个数量之外，最多可以有2个max_parallel_workers_per_gather进程为一个主进程服务。

这些参数值的选择取决于以下因素：

• 硬件能力：系统必须有专门用于并行执行的空闲内核。
• 表大小：数据库必须包含大表。
• 典型负载：必须有可能从并行执行中受益的查询。

这些标准通常由系统而不是单个系统来满足。

如果要读取的堆数据的估计容量不超过默认为8MB的min_parallel_table_scan_size值，那么规划器将根本不考虑并行执行。

除非在parallel_workers存储参数中显式指定了特定表的进程数，否则它将通过以下公式计算：

这意味着每当表增长三倍时，PostgreSQL就会多分配一个并行worker来处理它。默认设置给我们这些数字：

在任何情况下，并行工作线程的数量都不能超过max_parallel_workers_per_gather参数定义的限制。

如果我们查询一个大小为19M的小表，只有一个worker会被计划和启动：

=> EXPLAIN (analyze, costs off, timing off, summary off)
SELECT count(*) FROM flights;
						QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Finalize Aggregate (actual rows=1 loops=1)
	−> Gather (actual rows=2 loops=1)
		Workers Planned: 1
		Workers Launched: 1
		−> Partial Aggregate (actual rows=1 loops=2)
			−> Parallel Seq Scan on flights (actual rows=107434 lo...
(6 rows)

对一个105M的表的查询只能得到两个进程，因为它达到了2个max_parallel_workers_per_gather worker的限制：