第 8 章：重建表与索引

Docs

第 8 章：重建表与索引

8.1 完全清理

8.1.1 为什么例行清理不够？

与页剪枝相比，例行清理可以释放更多空间，但有时这可能仍然不够。

如果表或索引文件的大小增加了，VACUUM 可以清理一些页内空间，但很少能减少页面的数量。只有当文件末尾出现若干空页面时，回收的空间才能返还给操作系统，但这种情况并不常见。

大小过大会导致诸多不良影响：

全表 (或索引) 扫描将花费更长时间。
可能需要更大的缓冲区缓存 (页面作为一个整体被缓存，因此数据密度降低)。
B 树会有额外的层级，这会减慢索引访问的速度。
文件在磁盘上和备份中占用额外空间。

如果文件中有用数据的比例下降至某个合理水平以下，管理员可以通过运行 VACUUM FULL 命令 ¹ 来执行完全清理。在此情况下，表和其所有索引都将从头重建，并且数据会被尽可能密集地组织在一起 (同时考虑到 fillfactor 参数)。

当执行完全清理时，PostgreSQL 首先会完全重建表，然后重建它的每一个索引。在重建期间，新旧文件都需要存储在磁盘上 ²，因此该过程可能需要大量的空闲空间。

你还应该记住，此操作会完全阻塞对表的访问，包括读取和写入。

8.1.2 评估数据密度

为了举例说明，让我们在表中插入一些行：

=> TRUNCATE vac;
=> INSERT INTO vac(id,s)
SELECT id, id::text FROM generate_series(1,500000) id;

存储密度可以使用 pgstattuple 扩展来评估：

=> CREATE EXTENSION pgstattuple;
=> SELECT * FROM pgstattuple('vac') \gx
−[ RECORD 1 ]−−−−−−+−−−−−−−−−
table_len          | 70623232
tuple_count        | 500000
tuple_len          | 64500000
tuple_percent      | 91.33
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 381844
free_percent       | 0.54

此函数读取整个表并显示其文件中空间分布的统计数据。tuple_percent 字段显示了有用数据 (堆元组) 占用空间的百分比。由于页面内有各种元数据，这个值不可避免地会小于 100%，但在这个例子中仍然相当高。

对于索引，显示的信息略有不同，但 avg_leaf_density 字段含义相同：它显示了有用数据的百分比 (在 B 树叶子页面中)。

=> SELECT * FROM pgstatindex('vac_s') \gx
−[ RECORD 1 ]−−−−−−+−−−−−−−−−−
version            | 4
tree_level         | 3
index_size         | 114302976
root_block_no      | 2825
internal_pages     | 376
leaf_pages         | 13576
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 53.88
leaf_fragmentation | 10.59

之前使用的 pgstattuple 函数会完整读取表或索引以获得精确的统计数据。对于大型对象，这可能会耗费太多资源，因此该扩展还提供了另一个名为 pgstattuple_approx 的函数，该函数会跳过可见性映射中跟踪的页面，以显示近似数据。

一种更快但精度更低的方式是使用系统表粗略估算数据量与文件大小之间的比例 ³。

以下是表及其索引的当前大小：

=> SELECT pg_size_pretty(pg_table_size('vac')) AS table_size,
        pg_size_pretty(pg_indexes_size('vac')) AS index_size;
 table_size | index_size
−−−−−−−−−−−−+−−−−−−−−−−−−
 67 MB      | 109 MB
(1 row)

现在让我们删除 90% 的行：

=> DELETE FROM vac WHERE id % 10 != 0;
DELETE 450000

例行清理不会缩减文件大小，因为文件末尾没有空页面：

=> VACUUM vac;
=> SELECT pg_size_pretty(pg_table_size('vac')) AS table_size,
				pg_size_pretty(pg_indexes_size('vac')) AS index_size;
 table_size | index_size
−−−−−−−−−−−−+−−−−−−−−−−−−
 67 MB      | 109 MB
(1 row)

但是，数据密度下降了大约 10 倍：

=> SELECT vac.tuple_percent, vac_s.avg_leaf_density
FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;
 tuple_percent | avg_leaf_density
−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−
          9.13 |             6.71
(1 row)

表和索引当前位于以下文件中：

=> SELECT pg_relation_filepath('vac') AS vac_filepath,
        pg_relation_filepath('vac_s') AS vac_s_filepath \gx
−[ RECORD 1 ]−−+−−−−−−−−−−−−−−−−−
vac_filepath   | base/16391/16514
vac_s_filepath | base/16391/16515

让我们检查一下在 VACUUM FULL 之后会发生什么。当该命令运行时，可以在 pg_stat_progress_cluster 视图中跟踪其进度 (与为 VACUUM 提供的 pg_stat_progress_vacuum 视图类似)：

=> VACUUM FULL vac;

=> SELECT * FROM pg_stat_progress_cluster \gx
−[ RECORD 1 ]−−−−−−−+−−−−−−−−−−−−−−−−−
pid                 | 19488
datid               | 16391
datname             | internals
relid               | 16479
command             | VACUUM FULL
phase               | rebuilding index
cluster_index_relid | 0
heap_tuples_scanned | 50000
heap_tuples_written | 50000
heap_blks_total     | 8621
heap_blks_scanned 	| 8621
index_rebuild_count | 0

如预期那样， VACUUM FULL 的各个阶段 ⁴ 与常规清理有所不同。

完全清理已用新文件替换了旧文件：

=> SELECT pg_relation_filepath('vac') AS vac_filepath,
        pg_relation_filepath('vac_s') AS vac_s_filepath \gx
−[ RECORD 1 ]−−+−−−−−−−−−−−−−−−−−
vac_filepath   | base/16391/16526
vac_s_filepath | base/16391/16529

现在索引和表的大小都小多了：

=> SELECT pg_size_pretty(pg_table_size('vac')) AS table_size,
        pg_size_pretty(pg_indexes_size('vac')) AS index_size;
 table_size | index_size
−−−−−−−−−−−−+−−−−−−−−−−−−
 6904 kB    | 6504 kB
(1 row)

结果，数据密度也增加了。对于索引，数据密度甚至比原来的还要高：基于现有数据从头开始创建一棵 B 树比将条目逐行插入到已经存在的索引中更加有效：

=> SELECT vac.tuple_percent,
          vac_s.avg_leaf_density
FROM pgstattuple('vac') vac,
     pgstatindex('vac_s') vac_s;
 tuple_percent | avg_leaf_density
−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−
         91.23 |            91.08
(1 row)

8.1.3 冻结

当表被重建时，PostgreSQL 会冻结其元组，因为与剩余工作相比，这个操作的成本几乎可以忽略不计：

=> SELECT * FROM heap_page('vac',0,0) LIMIT 5;
 ctid  | state  | xmin  | xmin_age | xmax
−−−−−−−+−−−−−−−−+−−−−−−−+−−−−−−−−−−+−−−−−−
 (0,1) | normal | 861 f |        5 | 0 a
 (0,2) | normal | 861 f |        5 | 0 a
 (0,3) | normal | 861 f |        5 | 0 a
 (0,4) | normal | 861 f |        5 | 0 a
 (0,5) | normal | 861 f |        5 | 0 a
(5 rows)

但是页面既没有在可见性映射中标记，也没有在冻结映射中标记，而且页头也没有可见性属性 (这在使用带有 FREEZE 选项的 COPY 命令时是会发生的)：

=> SELECT * FROM pg_visibility_map('vac',0);
 all_visible | all_frozen
−−−−−−−−−−−−−+−−−−−−−−−−−−
 f           | f
(1 row)
=> SELECT flags & 4 > 0 all_visible
FROM page_header(get_raw_page('vac',0));
 all_visible
−−−−−−−−−−−−−
 f
(1 row)

仅在调用 VACUUM (或触发 autovacuum) 之后，情况才有所改善：

=> VACUUM vac;
=> SELECT * FROM pg_visibility_map('vac',0);
 all_visible | all_frozen
−−−−−−−−−−−−−+−−−−−−−−−−−−
 t           | t
(1 row)
=> SELECT flags & 4 > 0 AS all_visible
FROM page_header(get_raw_page('vac',0));
 all_visible
−−−−−−−−−−−−−
 t
(1 row)

这基本上意味着，即使页面中的所有元组都超出了数据库的视界，这样的页面仍然需要被重写。

8.2 其他重建方式

8.2.1 完全清理的替代方式

除了 VACUUM FULL 之外，还有其他几个可以完全重建表和索引的命令。这些命令都会以排它方式锁定表，删除旧的数据文件并重建。

CLUSTER 命令与 VACUUM FULL 完全类似，但它还会根据可用索引之一对文件中的元组进行重新排序。在某些情况下，它可以帮助规划器更加有效地使用索引扫描。但是你应该记住，PostgreSQL 不支持聚簇：所有后续的表更新都会破坏元组的物理顺序。

从编程角度来看，VACUUM FULL 只是 CLUSTER 命令的一个特例，不需要对元组重新排序 ⁵。

REINDEX 命令用于重建一个或多个索引 ⁶。实际上，VACUUM FULL 和 CLUSTER 在重建索引时底层会使用这个命令。

TRUNCATE 命令 ⁷ 会删除表中所有的行；它与运行不带有 WHERE 子句的 DELETE 命令的逻辑等效。但是， DELETE 只是将堆元组标记为已删除 (因此它们仍然需要被清理)，而 TRUNCATE 则是创建一个新的空文件，这通常更快。

8.2.2 减少重建期间的停机时间

VACUUM FULL 并不意味着要定期运行，因为它会在整个操作过程期间对表进行排它锁定 (对查询也是如此)。对于高可用系统来说，这通常不是一个选项。

有几个扩展 (例如 pg_repack ⁸) 可以在几乎零停机的情况下重建表和索引。排它锁仍然是必需的，但只在重建的开始与结束时刻，而且只需要很短的时间。这是通过更复杂的机制来实现的：在重建原始表时，所有对原始表所做的更改都由触发器保存，然后应用于新表。最后阶段，pg_repack 会用一个系统目录中的表替换原表。

pgcompacttable 工具 ⁹ 提供了一个非传统的解决方案。它执行多次伪行更新 (不更改任何数据)，使得当前行版本逐渐移向文件的开头。

在这些更新操作期间，清理进程会移除过期的元组，并一点一点截断文件。这种方法需要更多的时间和资源，但它不需要额外的空间来重建表并且不会导致负载高峰。在截断表时，仍然会获取短暂的排它锁，但清理操作处理起来相当平滑。

8.3 预防措施

8.3.1 只读查询

文件膨胀的原因之一是执行长时间运行的事务，这些事务在密集更新数据的同时保持着数据库视界。

长时间运行 (只读) 事务并不会导致任何问题。因此，一种常见的方式是在不同系统之间分配负载：在主库上执行快速的 OLTP 查询，并将所有 OLAP 事务转移到备库。尽管这使得解决方案变得更加昂贵和复杂，但这些措施可能是不可或缺的。

在某些情况下，长事务是应用程序或驱动 BUG 的结果，而不是必需的。如果问题无法以优雅的方式解决，管理员可以使用以下两个参数：

old_snapshot_threshold 参数定义了快照的最大生命周期。一旦到达此时间，服务器便有权移除过期的元组；如果长时间运行的事务仍然需要它们，那么便会出现 “snapshot too old” 的错误。
idle_in_trasaction_session_timeout 参数限制了空闲事务的生命周期。事务一旦达到此阈值后，便会被中止。

8.3.2 数据更新

膨胀的另一个原因是同时修改了大量元组。如果所有行都更新了，元组的数量可能会翻倍，而清理操作可能没有足够的时间进行干预。页剪枝可以减缓这个问题，但并不能完全解决它。

让我们扩展输出，增加另一列以跟踪处理过的行：

=> ALTER TABLE vac ADD processed boolean DEFAULT false;
=> SELECT pg_size_pretty(pg_table_size('vac'));
 pg_size_pretty
−−−−−−−−−−−−−−−−
 6936 kB
(1 row)

一旦所有的行都更新了，表的大小几乎增加了一倍：

=> UPDATE vac SET processed = true;
UPDATE 50000
=> SELECT pg_size_pretty(pg_table_size('vac'));
 pg_size_pretty
−−−−−−−−−−−−−−−−
 14 MB
(1 row)

为了解决这种情况，你可以减少单个事务执行的更改数量，将它们分散到不同的时间点上；然后清理操作便可以移除过期的元组，并在现有页面中为新元组腾出一些空间。假设每个行更新可以单独提交，我们可以使用以下查询作为模板，该查询选择一批指定大小的行：

SELECT ID
FROM table
WHERE filtering the already processed rows
LIMIT batch size
FOR UPDATE SKIP LOCKED

这段代码选择并立即锁定一组不超过指定大小的行。已被其他事务锁定的行将被跳过：下次它们会进入另一个批次。这是一个相当灵活和方便的解决方案，允许你轻松更改批次大小，并在故障发生时重新开始操作。让我们恢复 processed 属性，并执行完全清理以恢复表的原始大小：

=> UPDATE vac SET processed = false;
=> VACUUM FULL vac;

第一批次更新之后，表的大小会略有增长：

=> WITH batch AS (
  SELECT id FROM vac WHERE NOT processed LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
UPDATE vac SET processed = true
WHERE id IN (SELECT id FROM batch);
UPDATE 1000
=> SELECT pg_size_pretty(pg_table_size('vac'));
 pg_size_pretty
−−−−−−−−−−−−−−−−
 7064 kB
(1 row)

但从现在开始，表的大小几乎保持不变，因为新的元组替换了被移除的元组：

=> VACUUM vac;
=> WITH batch AS (
	SELECT id FROM vac WHERE NOT processed LIMIT 1000
	FOR UPDATE SKIP LOCKED
)
UPDATE vac SET processed = true
WHERE id IN (SELECT id FROM batch);
UPDATE 1000
=> SELECT pg_size_pretty(pg_table_size('vac'));
 pg_size_pretty
−−−−−−−−−−−−−−−−
 7072 kB
(1 row)

postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY ↩︎
backend/commands/cluster.c ↩︎
wiki.postgresql.org/wiki/Show_database_bloat ↩︎
postgresql.org/docs/14/progress-reporting.html#CLUSTER-PHASES ↩︎
backend/commands/cluster.c ↩︎
backend/commands/indexcmds.c ↩︎
backend/commands/tablecmds.c, ExecuteTruncate function ↩︎
github.com/reorg/pg_repack ↩︎
github.com/dataegret/pgcompacttable ↩︎