# 重建表与索引 LLMS 索引： [llms.txt](/zh18/llms.txt) --- ## 8.1. 完全清理 {#internal-ru-section-8-1} ### 为什么需要完全清理 {#internal-ru-subsection-star-130} 常规清理释放的空间比页内剪枝更多，但即使如此，也不一定能完全解决问题。如果表或索引文件因某种原因大幅膨胀，常规清理可以释放现有页面中的空间，但大多数情况下无法减少页面数量。只有在文件末尾形成完全空的页面时，清理才能把空间返还给操作系统，而这种情况并不常见。（*第 [133](/zh18/06-vacuum-and-freezing/#internal-ru-page-134) 页*）空间过大会带来一些令人不快的后果： - 全表（或全索引）扫描会变慢； - 可能需要更大的缓冲区缓存（缓存的是页面，而页面中有用信息的密度会下降）； - B-树中可能出现额外层级，从而减慢索引访问； - 磁盘和备份中的空间使用不够合理。如果文件中有用信息的比例降到某个合理阈值以下，管理员可以执行*完全清理*[^internal-ru-172-1]，运行 `VACUUM FULL` 命令。它会先重建表，再从头重建表的所有索引，并尽可能紧凑地组织数据（考虑 *fillfactor* 参数）。重建对象期间，磁盘上会同时保存旧文件和新文件[^internal-ru-172-2]，因此完全清理可能需要相当多的可用空间。（*第 [111](/zh18/05-page-pruning-and-hot-updates/#internal-ru-page-112) 页*）需要注意，整个重建期间，表都会被完全锁定，既不能写入，也不能读取。 ### 评估信息密度 {#internal-ru-subsection-star-132} 为了举例说明，我们先关闭自动清理，再向表中插入一些行： ```text {label="psql"} => 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` 扩展方便地评估信息密度： ```text {label="psql" hl_lines="7"} => 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` 字段的含义相同：它表示有用信息在叶页面中所占的比例。 ```text {label="psql" hl_lines="11"} => 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` 函数；它会跳过可见性映射中标记的页面，并给出近似数值。一种更快但精度更低的方法，是根据系统目录粗略估算数据量与文件大小的比例[^internal-ru-173-1]。下面是表和索引占用的空间： ```text {label="psql"} => 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%： ```text {label="psql"} => DELETE FROM vac WHERE id % 10 != 0; DELETE 450000 ``` 执行常规清理后，文件大小没有变化，因为文件末尾没有空页面： ```text {label="psql"} => 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) ``` 但信息密度大约降低了十倍： ```text {label="psql"} => 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) ``` 现在，表和索引分别位于以下文件中： ```text {label="psql"} => 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 ``` 现在检查完全清理后的结果。 ```text {label="psql"} => VACUUM FULL vac; ``` 命令运行期间，可以在 `pg_stat_progress_cluster` 视图中跟踪执行进度；该视图与普通清理使用的 `pg_stat_progress_vacuum` 视图类似。（v. 12） ```text {label="psql"} => 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 ``` ```sql VACUUM ``` 当然，完全清理的执行阶段[^internal-ru-175-1]与常规清理不同。完全清理后，文件已经换成了新的文件： ```text {label="psql"} => 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 ``` 表和索引的大小都明显减小了： ```text {label="psql"} => 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-树索引通常比把数据逐行插入现有索引更划算： ```text {label="psql"} => 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) ``` ### 冻结 {#internal-ru-subsection-star-134} 重建表时，行版本会被冻结，因为与其余工作量相比，这几乎不增加成本： ```text {label="psql" hl_lines="4 5 6 7 8"} => 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](/zh18/07-autovacuum/#internal-ru-page-162) 页*） ```text {label="psql"} => 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` 命令（或触发自动清理）后，情况才会得到修正： ```text {label="psql"} => 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. 其他重建方式 {#internal-ru-section-8-2} ### 完全清理的替代方式 {#internal-ru-subsection-star-136} 除完全清理外，还有几条命令可以完整重建表和索引。它们都会以独占方式锁定表上的操作，也都会删除旧数据文件并创建新文件。 `CLUSTER` 命令与 `VACUUM FULL` 基本相同，但还会根据现有索引之一对文件中的行版本重新排序。这使规划器在某些情况下能够更高效地使用索引访问。不过必须明白，聚簇效果不会持续维护：表后续发生修改后，行版本的物理顺序就会被破坏。（*第 [409](/zh18/19-index-access-methods/#internal-ru-page-410) 页*）（*第 [415](/zh18/20-index-scans/#internal-ru-page-416) 页*）从代码实现看，`VACUUM FULL` 只是 `CLUSTER` 的一个特例，不需要重新排列行版本[^internal-ru-177-1]。 `REINDEX` 命令用于重建一个或多个索引[^internal-ru-178-1]。`VACUUM FULL` 和 `CLUSTER` 在处理完表、转而处理索引时，实际上会使用这条命令的机制。 `TRUNCATE` 命令[^internal-ru-178-2]会删除表中的所有行，在逻辑上相当于执行不带 `WHERE` 条件的 `DELETE`。但 `DELETE` 只是把行版本标记为已删除，后续还需要清理；而 `TRUNCATE` 会直接创建一个新的空文件，通常快得多。（*第 [87](/zh18/03-pages-and-tuples/#internal-ru-page-88) 页*） ### 无需长时间锁定的重建 {#internal-ru-subsection-star-138} 完全清理不适合定期使用，因为它在整个运行期间都会完全阻塞对表的访问（包括执行查询）。对于高可用系统，这通常无法接受。（*第 [261](/zh18/12-relation-level-locks/#internal-ru-page-262) 页*）有一些扩展（例如 `pg_repack`[^internal-ru-178-3]）可以在几乎不中断服务的情况下重建表和索引。表的独占锁仍然不可避免，但只需在开始和结束时短暂持有。其实现更加复杂：重建期间，触发器会保存对原表的数据修改；随后，工具将这些修改应用到新表，并在系统目录中用新表替换原表。 `pgcompacttable` 工具[^internal-ru-178-4]提供了一种有意思的解决方案。它反复对表中的行执行不改变数据的伪更新，使当前行版本逐渐在文件内向前移动。每轮更新之间都会运行清理，删除过期行版本并逐步截断文件。这种方法耗时和耗费资源都多得多，但不需要额外空间重建表，也不会造成负载峰值。截断表时同样会短暂获取独占锁，但清理对这种锁的处理相当温和。（*第 [133](/zh18/06-vacuum-and-freezing/#internal-ru-page-134) 页*） ## 8.3. 预防措施 {#internal-ru-section-8-3} ### 只读查询 {#internal-ru-subsection-star-140} 文件膨胀的原因之一，是长时间运行的事务在数据持续更新的同时保持着数据库视界。（*第 [106](/zh18/04-snapshots/#internal-ru-page-107) 页*）长时间运行的（只读）事务本身不会造成问题。因此，常见的做法是把负载分配到不同的机器上：在副本上执行 OLAP 事务，把快速的 OLTP 事务留在主服务器上。当然，这会让系统更复杂、更昂贵，但有时可能是必要的。有时，长时间运行的事务并非出于必要，而是应用程序或驱动程序的错误所致。如果无法妥善解决问题，管理员可以使用两个参数：*idle\_in\_transaction\_session\_timeout* 用于限制空闲事务的最长生存时间，*transaction\_timeout* 用于限制事务的总持续时间。经过指定的时间后（如果该值不为零），事务就会被中止。（*默认值：* `0ms`）（*默认值：* `0ms`）（v. 17） ### 数据更新 {#internal-ru-subsection-star-142} 膨胀的另一个原因，是一次性修改大量行。更新表中的所有行可能使行版本数量增加一倍，而清理来不及介入。页内剪枝可以缓解这个问题，但也仅此而已。向表中添加 `processed` 列，用它标记已经处理的行： ```text {label="psql"} => ALTER TABLE vac ADD processed boolean DEFAULT false; => SELECT pg_size_pretty(pg_table_size('vac')); pg_size_pretty ---------------- 6936 kB (1 row) ``` 更新所有行后，表的大小大约增加了一倍： ```text {label="psql"} => UPDATE vac SET processed = true; UPDATE 50000 => SELECT pg_size_pretty(pg_table_size('vac')); pg_size_pretty ---------------- 14 MB (1 row) ``` 解决办法是减少单个事务中执行的修改量，并把修改分散到一段时间内，以便清理能够删除不需要的行版本，并在现有页面中为新行版本腾出空间。如果假定每一行的更新都可以单独提交，就可以使用下面这个查询作为模板，从表中选出指定大小的一批行： ```sql SELECT идентификатор FROM таблица WHERE фильтрация уже обработанных строк LIMIT размер пакета FOR UPDATE SKIP LOCKED ``` 这段代码会选出不超过指定大小的一组行，并立即将它们锁定。已经被其他事务锁定的行会被跳过，下次会进入另一批。这个方法相当通用且方便，可以轻松调整批大小，并在发生故障时重新处理。现在重置处理标志，并执行完全清理，以恢复表的原始大小：（*第 [285](/zh18/13-row-level-locks/#internal-ru-page-286) 页*） ```text {label="psql"} => UPDATE vac SET processed = false; => VACUUM FULL vac; ``` 更新第一批后，表的大小会略有增加： ```text {label="psql"} => 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) ``` 但之后大小几乎不再增长，因为新的行版本会添加到已清理行版本留下的位置。 ```text {label="psql"} => 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) ``` [^internal-ru-172-1]: [postgrespro.ru/docs/postgresql/18/routine-vacuuming#VACUUM-FOR-SPACE-RECOVERY]()。 [^internal-ru-172-2]: [backend/commands/cluster.c]()。 [^internal-ru-173-1]: [wiki.postgresql.org/wiki/Show\_database\_bloat]()。 [^internal-ru-175-1]: [postgrespro.ru/docs/postgresql/18/progress-reporting#CLUSTER-PHASES]()。 [^internal-ru-177-1]: [backend/commands/cluster.c]()。 [^internal-ru-178-1]: [backend/commands/indexcmds.c]()。 [^internal-ru-178-2]: [backend/commands/tablecmds.c]()，函数 ExecuteTruncate。 [^internal-ru-178-3]: [github.com/reorg/pg\_repack]()。 [^internal-ru-178-4]: [github.com/dataegret/pgcompacttable]()。