8 重建表与索引

PostgreSQL 18 · 基于俄文 PG18 原版的简体中文译本

简体中文 · PG14 English · PG14简体中文 · PG18Русский · PG18

8.1. 完全清理

为什么需要完全清理

常规清理释放的空间比页内剪枝更多，但即使如此，也不一定能完全解决问题。

如果表或索引文件因某种原因大幅膨胀，常规清理可以释放现有页面中的空间，但大多数情况下无法减少页面数量。只有在文件末尾形成完全空的页面时，清理才能把空间返还给操作系统，而这种情况并不常见。（第 133 页）

空间过大会带来一些令人不快的后果：

全表（或全索引）扫描会变慢；
可能需要更大的缓冲区缓存（缓存的是页面，而页面中有用信息的密度会下降）；
B-树中可能出现额外层级，从而减慢索引访问；
磁盘和备份中的空间使用不够合理。

如果文件中有用信息的比例降到某个合理阈值以下，管理员可以执行完全清理¹，运行 VACUUM FULL 命令。它会先重建表，再从头重建表的所有索引，并尽可能紧凑地组织数据（考虑 fillfactor 参数）。重建对象期间，磁盘上会同时保存旧文件和新文件²，因此完全清理可能需要相当多的可用空间。（第 111 页）

需要注意，整个重建期间，表都会被完全锁定，既不能写入，也不能读取。

评估信息密度

为了举例说明，我们先关闭自动清理，再向表中插入一些行：

=> TRUNCATE vac;
=> ALTER TABLE vac SET (autovacuum_enabled = off);
=> 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         | 416328
free_percent       | 0.59

该函数会读取整张表，并输出文件中空间分布的统计信息。tuple_percent 字段显示有用数据（行版本）占用的空间比例。由于页面内部的管理信息会产生开销，这个值必然小于 100%，但在本例中仍然相当高。

索引输出的信息有所不同，但 avg_leaf_density 字段的含义相同：它表示有用信息在叶页面中所占的比例。

=> 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.83
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)

但信息密度大约降低了十倍：

=> 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.66
(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/16526
vac_s_filepath | base/16391/16527

现在检查完全清理后的结果。

=> VACUUM FULL vac;

命令运行期间，可以在 pg_stat_progress_cluster 视图中跟踪执行进度；该视图与普通清理使用的 pg_stat_progress_vacuum 视图类似。（v. 12）

=> SELECT * FROM pg_stat_progress_cluster \gx
-[ RECORD 1 ]-------+------------------
pid                 | 53523
datid               | 16391
datname             | internals
relid               | 16486
command             | VACUUM FULL
phase               | seq scanning heap
cluster_index_relid | 0
heap_tuples_scanned | 43088
heap_tuples_written | 43088
heap_blks_total     | 8621
heap_blks_scanned   | 7429
index_rebuild_count | 0

VACUUM

当然，完全清理的执行阶段⁴与常规清理不同。

完全清理后，文件已经换成了新的文件：

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

表和索引的大小都明显减小了：

=> 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.03
(1 row)

冻结

重建表时，行版本会被冻结，因为与其余工作量相比，这几乎不增加成本：

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

不过，页面既不会在可见性映射中标记，也不会在冻结映射中标记，页头中也不会设置可见性标志（使用带 FREEZE 参数的 COPY 命令时会设置该标志）：（第 161 页）

=> 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 命令（或触发自动清理）后，情况才会得到修正：

=> 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. 其他重建方式

完全清理的替代方式

除完全清理外，还有几条命令可以完整重建表和索引。它们都会以独占方式锁定表上的操作，也都会删除旧数据文件并创建新文件。

CLUSTER 命令与 VACUUM FULL 基本相同，但还会根据现有索引之一对文件中的行版本重新排序。这使规划器在某些情况下能够更高效地使用索引访问。不过必须明白，聚簇效果不会持续维护：表后续发生修改后，行版本的物理顺序就会被破坏。（第 409 页）（第 415 页）

从代码实现看，VACUUM FULL 只是 CLUSTER 的一个特例，不需要重新排列行版本⁵。

REINDEX 命令用于重建一个或多个索引⁶。VACUUM FULL 和 CLUSTER 在处理完表、转而处理索引时，实际上会使用这条命令的机制。

TRUNCATE 命令⁷会删除表中的所有行，在逻辑上相当于执行不带 WHERE 条件的 DELETE。但 DELETE 只是把行版本标记为已删除，后续还需要清理；而 TRUNCATE 会直接创建一个新的空文件，通常快得多。（第 87 页）

无需长时间锁定的重建

完全清理不适合定期使用，因为它在整个运行期间都会完全阻塞对表的访问（包括执行查询）。对于高可用系统，这通常无法接受。（第 261 页）

有一些扩展（例如 pg_repack⁸）可以在几乎不中断服务的情况下重建表和索引。表的独占锁仍然不可避免，但只需在开始和结束时短暂持有。其实现更加复杂：重建期间，触发器会保存对原表的数据修改；随后，工具将这些修改应用到新表，并在系统目录中用新表替换原表。

pgcompacttable 工具⁹提供了一种有意思的解决方案。它反复对表中的行执行不改变数据的伪更新，使当前行版本逐渐在文件内向前移动。每轮更新之间都会运行清理，删除过期行版本并逐步截断文件。这种方法耗时和耗费资源都多得多，但不需要额外空间重建表，也不会造成负载峰值。截断表时同样会短暂获取独占锁，但清理对这种锁的处理相当温和。（第 133 页）

8.3. 预防措施

只读查询

文件膨胀的原因之一，是长时间运行的事务在数据持续更新的同时保持着数据库视界。（第 106 页）

长时间运行的（只读）事务本身不会造成问题。因此，常见的做法是把负载分配到不同的机器上：在副本上执行 OLAP 事务，把快速的 OLTP 事务留在主服务器上。当然，这会让系统更复杂、更昂贵，但有时可能是必要的。

有时，长时间运行的事务并非出于必要，而是应用程序或驱动程序的错误所致。如果无法妥善解决问题，管理员可以使用两个参数：idle_in_transaction_session_timeout 用于限制空闲事务的最长生存时间，transaction_timeout 用于限制事务的总持续时间。经过指定的时间后（如果该值不为零），事务就会被中止。（默认值： 0ms）（默认值： 0ms）（v. 17）

数据更新

膨胀的另一个原因，是一次性修改大量行。更新表中的所有行可能使行版本数量增加一倍，而清理来不及介入。页内剪枝可以缓解这个问题，但也仅此而已。

向表中添加 processed 列，用它标记已经处理的行：

=> 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 идентификатор
FROM таблица
WHERE фильтрация уже обработанных строк
LIMIT размер пакета
FOR UPDATE SKIP LOCKED

这段代码会选出不超过指定大小的一组行，并立即将它们锁定。已经被其他事务锁定的行会被跳过，下次会进入另一批。这个方法相当通用且方便，可以轻松调整批大小，并在发生故障时重新处理。现在重置处理标志，并执行完全清理，以恢复表的原始大小：（第 285 页）

=> 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)