Logo

Menu

张彤
张彤
Published on 2025-05-09 / 4 Visits
0
0

Postgresql应急手册-膨胀

#postgresql

Postgresql应急手册结构性内容来自xiongcc的pg应急一图

PostgreSQL 膨胀问题技术指南

摘要： PostgreSQL 数据库在长时间运行、频繁更新的情况下，容易出现“膨胀（Bloat）”问题。膨胀指的是表、索引、WAL 日志或系统目录占用了不必要的大量空间，可能导致性能下降。本文将分别讨论 表膨胀、索引膨胀、WAL 日志膨胀 和 系统表膨胀，分析成因、检测方法，并提供解决方案和示例脚本。通过合理的监控和维护，可有效控制膨胀现象，提高数据库性能和稳定性。

1. 表膨胀（Table Bloat）

产生原因

PostgreSQL 使用 多版本并发控制 (MVCC) 机制来处理数据修改，这意味着当更新或删除一行时，旧版本的数据不会立即物理删除，而是被标记为“过时”或“死元组” (dead tuple) (How to Reduce Bloat in Large PostgreSQL Tables | Timescale)。随着事务的进行，这些死元组会残留在表中，占据空间却不再有用，从而导致表文件大小持续增长 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。产生表膨胀的主要原因包括：

  • 频繁的 UPDATE/DELETE 操作： 每次 UPDATE 实际上会在表中插入新版本的数据，并将旧版本标记为删除；DELETE 也只标记行删除而不立即收回磁盘空间 (How to Reduce Bloat in Large PostgreSQL Tables | Timescale)。如果这些过时版本没有及时清理，表中会充满大量无效数据。

  • 事务未及时提交或长事务： 长期运行的事务会阻止早期产生的死元组被清理（因为这些旧版本可能对并发事务仍可见）。如果频繁更新但迟迟不提交，旧数据版本大量累积，导致膨胀。

  • 自动清理不及时： PostgreSQL 的 autovacuum（自动清理进程） 会定期回收死元组的空间。但如果 autovacuum 配置不当（例如阈值过高或频率过低）或一段时间未能运行，死元组得不到及时清理，表文件会越积越大。

正常情况下，活跃数据占用的表空间应该与实际数据量相称，而适量的死元组是正常现象（因为 MVCC 特性） (How to Reduce Bloat in Large PostgreSQL Tables | Timescale)。只有当死元组累积到使“活动数据仅占很小一部分空间”时，就出现了明显的表膨胀，需要引起关注 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。

检测方法

检测表膨胀通常需要了解表中死元组的比例以及表实际文件大小与有效数据大小的差距。以下是常用的检测方法：

  • pgstattuple 扩展： PostgreSQL 提供 pgstattuple 扩展来统计表的空间使用情况 (How to Reduce Bloat in Large PostgreSQL Tables | Timescale)。安装扩展后，可以使用 SQL 函数获取指定表的详细统计信息，例如表长度、元组总数、死元组数量及所占空间等 (How to Reduce Bloat in Large PostgreSQL Tables | Timescale) (How to Reduce Bloat in Large PostgreSQL Tables | Timescale)。示例：

    -- 安装扩展（在数据库中执行一次）
CREATE EXTENSION pgstattuple;
-- 查看名为 mytable 的表的膨胀情况
SELECT * 
FROM pgstattuple('public.mytable');

    上述查询返回诸如 table_len（表大小）、tuple_count（行数）、dead_tuple_count（死元组数）、dead_tuple_len（死元组占用空间）等字段。通过计算 dead_tuple_len / table_len * 100% 可以得出该表的大致膨胀百分比 (How to Reduce Bloat in Large PostgreSQL Tables | Timescale)。

    pgstattuple插件 仅支持PG13以及以上版本使用

  • 估算页面法： 利用 PostgreSQL 收集的统计信息估算膨胀程度。例如，通过 ANALYZE 收集的平均行长度和行数，可以计算表理论上需要的页数，与实际占用页数比较差异 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。PostgreSQL 官方 Wiki 提供了一些 SQL 脚本，通过对 pg_class 等系统表的数据进行计算，估算每个表和索引的膨胀率。

  • 自定义查询和监控： 查询视图如 pg_stat_all_tables 来监控死元组数量（n_dead_tup）与现存元组数量的比率。如果某表的 n_dead_tup 持续较高，占比远超 n_live_tup，则可能存在膨胀问题。此外，也可以定期记录表大小（pg_relation_size）的增长和实际业务数据量的变化做对比。

  • 图形化工具： 一些管理工具（如 pgAdmin）和第三方监控平台提供可视化的膨胀监控功能，或有现成脚本（例如 check_postgres 工具中的 bloat 检查选项）帮助列出膨胀最严重的表和索引 (How to Reduce Bloat in Large PostgreSQL Tables | Timescale)。

下面是一个使用 pgstattuple 检查所有表膨胀的示例脚本，它遍历数据库中每个普通表并输出膨胀相关信息：

-- 列出当前数据库中每个表的膨胀统计
SELECT relname AS table_name, 
       (pgstattuple(oid)).dead_tuple_percent AS dead_pct, 
       pg_size_pretty(pg_relation_size(oid)) AS table_size 
FROM pg_class 
WHERE relkind = 'r'   -- 仅选择普通表（heap relation）
ORDER BY dead_pct DESC 
LIMIT 10;

上述查询通过对 pg_class 中每个表调用 pgstattuple，计算死元组比例，并按比例排序列出前10的表，以帮助我们找出“膨胀”最严重的对象 (How to monitor and fix Database bloats in PostgreSQL? | Netdata ) (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。

处理方式

对于已经发生的表膨胀，以及预防未来膨胀恶化，PostgreSQL 提供了多种维护手段。常见的处理方式包括：

  1. 定期 VACUUM： 执行 VACUUM 命令可以清理表中的死元组，将其占据的空间标记为可重用。普通 VACUUM 不会缩小表文件的物理大小，但会将腾出的空间放回表的自由空间映射（FSM）中，以供后续插入使用 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。对更新频繁的表，建议每天甚至更高频率进行 vacuum（或者确保 autovacuum 足够频繁）以防止膨胀积累 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。例如：

    -- 对单个表执行真空
VACUUM mytable;
-- 对整个数据库的所有表执行真空
VACUUM;

    真空操作可以在数据库在线时进行，不会阻塞读取和写入（但进行 VACUUM 时不能对表做DDL操作，如结构修改）。

  2. AUTOVACUUM 调优： 确保启用了 autovacuum（默认开启）并根据表的更新模式进行调优。可调整参数如 autovacuum_vacuum_threshold（最小触发行数）和 autovacuum_vacuum_scale_factor（触发清理的表规模比例）。对于高更新频率的表，可适当降低阈值或比例，使 autovacuum 更及时地运行。这样自动清理机制能更快回收死元组空间，减缓膨胀速度。

  3. VACUUM FULL： 如果表已经膨胀严重，普通 VACUUM 只能回收空间但无法归还操作系统，这时可以使用 VACUUM FULL。VACUUM FULL 会重新构建表，把有效数据压缩到新文件并释放原来的大文件空间给操作系统 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。它可以最大程度地消除膨胀，但代价较高：需要对表加 ACCESS EXCLUSIVE 锁，在操作期间会阻塞对该表的所有读写 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。此外，大表执行 VACUUM FULL 可能耗时很长且会产生大量 WAL 日志。因此，VACUUM FULL 一般在维护时间窗口执行，并在小表或迫不得已时使用 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。用法示例：

    VACUUM FULL mytable;

    执行后，mytable 的物理文件将缩小到仅包含现存行所需的大小，其关联索引也会自动重建。

  4. CLUSTER 重组表： CLUSTER 命令可以按照某个索引的顺序重写表。这实际上会创建表的一个有序副本，从而消除碎片和膨胀，并同时基于索引顺序优化数据的物理排列。和 VACUUM FULL 类似，CLUSTER 需要对表加独占锁并重写整个表，因此也应安排在维护时间执行。示例：

    CLUSTER mytable USING mytable_someindex;

    这会依据索引 mytable_someindex 的顺序重写 mytable，达到整理和紧凑数据的效果。

  5. 重建表（重建/导出导入）： 另一种处理膨胀的方法是手动重建表。例如，可以新建一个临时表，将旧表的数据全量复制过去，然后用新表替换旧表。这种方法与 VACUUM FULL 思路类似，但可以通过逻辑导出/导入来避免长时间锁定：

    • 将表数据通过 CREATE TABLE newtable AS SELECT * FROM oldtable; 或外部工具导出。

    • 重命名或丢弃膨胀的旧表，再将新表改名为旧表名称。

    • 重建原有的索引和约束。

    这种方式复杂且需要足够的额外空间，但在某些情况下可以绕开对生产表长时间加锁（代价是需要短暂中断切换）。

  6. 使用在线重组工具： 除了上述内置命令，社区还提供了工具如 pg_repack、pg_squeeze 等，可以在线重组表和索引以消除膨胀，而且对业务的锁影响较小（pg_repack 使用逻辑复制原理在线重建表）。这些工具需要单独安装扩展或程序，并应仔细测试后使用，但为严重膨胀的关键表提供了接近无停机的维护手段 (How to Reduce Bloat in Large PostgreSQL Tables - Timescale)。

小贴士： 在对严重膨胀的表进行维护前，最好先运行一次普通 VACUUM。如果表非常膨胀，直接执行 ANALYZE 可能由于样本包含大量空页而产生误导性的统计信息；而 VACUUM 可以先清理掉大部分死元组，再 ANALYZE 更新统计更准确 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。

2. 索引膨胀（Index Bloat）

成因

索引膨胀与表膨胀常常相伴而生。当表中的行被更新或删除时，关联索引中的条目也会随之失效。PostgreSQL 的 B-tree 等索引结构在删除索引项时，会留下未使用的空间，索引页面可能变得稀疏、低效。这些“空洞”长期累积，就形成了索引膨胀 (How to Reduce Bloat in Large PostgreSQL Tables | Timescale)。具体原因包括：

  • 更新/删除导致的碎片： 索引在指向的数据行失效后并不会立即重用或压缩空间，而只是标记可用。大量更新造成索引页内出现许多无效指针或空闲槽位，降低了索引页的利用率 (How to Reduce Bloat in Large PostgreSQL Tables | Timescale)。

  • 页面拆分遗留： 对于 B-tree 索引，大量插入可能导致节点拆分，如果后续删除数据，拆分后留下的空页不会自动合并。这使索引文件大小减少不了，内部有不少空页或半空页。

  • 填充因子 (Fillfactor)： 每个索引在创建时可以设置填充因子，表示预留的空闲空间比例。默认 B-tree 索引 fillfactor 为 90，意味着留出 10% 空间以减少频繁页拆分。如果大量更新没有用完这些预留空间，反而导致更多空间未使用，也会表现出一定的膨胀（不过一般fillfactor机制对膨胀的影响相对可控）。

索引膨胀会导致索引文件庞大，查询时需要读取更多的索引页。当索引变得臃肿时，索引扫描的效率下降，进而影响查询性能 (How to Reduce Bloat in Large PostgreSQL Tables | Timescale)。此外，膨胀的索引在维护（如 VACUUM、备份）时也需要更多时间处理。

识别方法

识别索引膨胀可以通过监控索引大小及其实际有效数据比例来进行：

  • pgstattuple/pgstatindex： 和表类似，可以利用 pgstattuple('索引名') 查看索引的空闲空间比例。例如：

    SELECT indexrelid::regclass AS index_name,
       (pgstattuple(indexrelid)).dead_tuple_percent AS dead_pct,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_index
WHERE indrelid = 'schema.mytable'::regclass;

    上述查询对某一表的每个索引调用 pgstattuple，得出死元组（对于索引来说指已删除或无效的索引项）比例和索引大小，以判断索引是否膨胀严重。

    除了 pgstattuple，pgstattuple 扩展还提供了 pgstatindex() 函数（仅适用于 B-tree 索引），可以返回索引的统计信息，如平均每页存储密度 (avg_leaf_density) 等。据此可估计索引的空闲空间百分比，例如 bloat_pct ≈ 100 - avg_leaf_density。如果 avg_leaf_density 明显低于 50%，表明索引页有大量空闲。

  • 对比大小： 使用函数 pg_relation_size() 和 pg_indexes_size() 来了解索引大小相对于表大小是否异常。例如：

    SELECT 
  pg_size_pretty(pg_relation_size('myindex'::regclass)) AS index_size,
  pg_size_pretty(pg_relation_size('mytable'::regclass)) AS table_size,
  pg_size_pretty(pg_indexes_size('mytable'::regclass)) AS total_index_size;

    如果某个索引的大小远超其对应表所含数据量的预期，可能存在膨胀。（当然，某些大型索引本身可能比表大，比如索引包含多个列或重复值较少，这种情况需结合业务判断。）

  • 统计视图： pg_stat_all_indexes 提供每个索引的扫描次数等，但不直接包含膨胀信息。不过可以结合 pg_class 中的 relpages（页数）和 reltuples（元组数）估算平均每元组的索引大小，间接推测是否偏大。例如计算某索引的 字节/元组，与预期的索引记录大小对比。

  • 第三方工具： 如前文提到的 check_postgres 脚本的 bloat 检查，或者社区的 pg_bloat_check 工具 (keithf4/pg_bloat_check: Bloat check script for PostgreSQL - GitHub)。它们通常综合分析表和索引的统计信息，给出膨胀估算报告。

解决方法

与表不同，VACUUM 不会收缩索引文件的物理大小。即使 VACUUM 清除了索引中已失效的项，那些空间也只是变成索引的可重用空间，索引文件本身不会缩小。因此，索引膨胀通常需要通过重建索引来解决 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。主要方法包括：

  • REINDEX 重建索引： REINDEX 命令可重建指定索引或表的所有索引。重建过程会创建一个新的索引文件，填充有效数据，从而丢弃原有索引的空洞和碎片 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。用法示例：

    -- 重建单个索引
REINDEX INDEX myindex;
-- 重建整个表的所有索引
REINDEX TABLE mytable;

    重建索引需要独占锁定目标索引/表：对索引重建会阻塞对该索引的查询，对表重建所有索引则相当于短暂锁定表的索引访问。因此要注意对业务的影响。重建完成后，可以运行 ANALYZE 更新索引统计信息，因为 REINDEX 会将 reltuples 等统计计数重置为0 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。

  • REINDEX CONCURRENTLY： 在 PostgreSQL 12+中，可以使用 REINDEX INDEX ... CONCURRENTLY 以非阻塞的方式重建索引。它通过创建临时索引并逐步填充的方式，在整个过程中允许读写。不过 CONCURRENTLY 模式耗时更长，也需要额外的磁盘空间，但非常适合在生产环境对大型索引做维护，因为不会长时间锁表。

  • 删除并重建索引： 对于非关键索引，可以选择删除索引再重建。效果与 REINDEX 相同，但过程更灵活：例如可以先 DROP INDEX，等待一段时间降低峰值压力后再 CREATE INDEX。当索引重建对线上查询影响不大时，这种方式也可考虑。不过需要确保在索引不存在的窗口内，查询性能或功能不受致命影响（比如唯一约束索引删除后要小心数据完整性）。

  • 定期维护策略： 防患于未然，可以针对高更新率的表制定索引维护计划。例如每隔若干个月定期运行一次 REINDEX（或在数据库低峰期运行）。另外，监控索引的大小变化，发现某索引增长异常且查询变慢时，提前安排重建。一般情况下，如果 autovacuum 对表及时清理，那么索引中的无效项也会被标记可用，短期内索引膨胀对性能的影响可能不明显。但长久来看，还是需要通过重建来彻底清理。所以定期的索引重建是大型OLTP系统的一项重要维护任务。

  • 调整填充因子： 在重建索引时，可以考虑设置合适的 FILLFACTOR。默认90意味着每页留10%空闲。对于经常更新的索引，可以降低 fillfactor（比如 70-80），给每页留更多余地，减少频繁更新导致的页面拆分，从而延缓膨胀发生。但 fillfactor 过低会导致索引初始大小变大，需折中考虑。

示例：重建整个数据库的所有索引可以使用命令行工具 reindexdb。或者要重建系统中所有数据库的索引，可以编写脚本遍历数据库执行 REINDEX DATABASE dbname。但需要注意一次重建大量索引对系统的冲击，应评估后执行。

总之，索引膨胀的治理核心在于重建。在规划停机维护时，不妨将重点表的索引重建列为常规项。此外，也可以借助在线工具（如 pg_repack 也能重建索引）在不停机的情况下处理索引膨胀。

3. WAL 日志膨胀（Write-Ahead Log Bloat）

持续增长的原因

WAL（预写日志）记录了数据库事务用于恢复和复制的重要信息。正常情况下，旧的 WAL 日志段在完成归档或不再需要后会被回收或删除。然而，有些情况会导致 pg_wal 目录下的 WAL 文件持续累积，空间占用不断增大：

简而言之，WAL 日志膨胀往往归因于不能及时删除旧 WAL。当出现 pg_wal 目录占用异常增长时，需重点检查 复制/归档是否正常 以及 参数设置 (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support)。正如有经验的管理员指出的：“如果 pg_wal 过度膨胀，首先检查归档是否卡住、复制槽是否落后，以及 wal_keep_size 是否设置过高” (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support)。

监控方法

为了及时发现 WAL 日志的异常增长，管理员应对 WAL 的生成和回收进行监控。PostgreSQL 提供了一些视图和工具：

  • pg_stat_wal： 从 PostgreSQL 14 起提供的视图，展示 WAL 写入统计信息（如记录条数、字节数）。通过定期采样 pg_stat_wal，可以知道 WAL 的生成速率。例如：

    SELECT wal_records, wal_fpi, wal_bytes, 
       pg_size_pretty(wal_bytes) AS wal_bytes_pretty
FROM pg_stat_wal;

    这可以显示自上次统计重置以来写入了多少 WAL 记录和字节。如果观察到 wal_bytes 持续高速增长，要结合业务操作确认是否正常（如批量加载数据会在短时间内产生大量 WAL）。

  • pg_stat_archiver： 监控 WAL 归档行为的视图。关键字段包括 last_archived_wal（最后成功归档的 WAL 文件名）、last_failed_wal（最后一个归档失败的 WAL 文件名）和对应的时间戳等。如果 last_failed_wal 有值且时间更新，说明归档出现错误。可以用查询检查归档是否卡壳： (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support)

    -- 检查是否有 WAL 归档失败且尚未成功的情况
SELECT last_failed_wal, last_failed_time 
FROM pg_stat_archiver 
WHERE last_failed_time > COALESCE(last_archived_time, '-infinity');

    如果返回结果，表示存在归档失败。应立即检查数据库日志中相应错误信息并修复问题 (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support。另外，pg_stat_archiver 的计数器如 failed_count、archived_count 也能反映归档成功/失败次数。

  • pg_stat_replication / pg_replication_slots： 在主库上，可通过 pg_stat_replication 查看所有流复制从库的当前 WAL 同步位置和滞后字节数 (sent_lsn vs write_lsn/flush_lsn 差距)。pg_replication_slots 列出复制槽的 restart_lsn 及 active 状态、wal_status 等信息 (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support) (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support)。例如：

    SELECT slot_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS bytes_behind,
       active, wal_status
FROM pg_replication_slots;

    这个查询可以显示每个复制槽落后主流的字节数 (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support) (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support)。如果某槽落后非常多且处于非活跃状态（比如从库已不再连接），那么这个复制槽就是 WAL 堆积的罪魁祸首。应考虑使用 pg_drop_replication_slot('<slot_name>') 删掉无用的槽，使该槽占用的 WAL 得以释放 (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support) (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support)。

  • 操作系统监控： 直接监控 $PGDATA/pg_wal 目录大小也是最简单有效的方法。可以写脚本定期统计文件大小，超过阈值则报警。

  • 日志监控 (log_checkpoints)： 将参数 log_checkpoints 设为 on，可以在数据库日志中记录每次检查点的信息，包括写了多少 WAL、回收了多少个 WAL 文件等。通过日志可以分析检查点频率和WAL回收状况，及时调整。

限制与优化

针对 WAL 日志持续增长的问题，需要从配置和架构上双管齐下，既要消除导致 WAL 保留的原因，又要优化 WAL 产生的速率。以下是几种措施：

  • 调整 wal_keep_size： 如果不是非常必要，不要将 wal_keep_size（或旧版本的 wal_keep_segments）设置得过大。仅保留足够让备用服务器在短暂断线后重新连接的 WAL 即可。例如，备用库能够承受5分钟重连，则根据主库每分钟 WAL 产生量设置 wal_keep_size 为若干MB即可。如果此前设置过高，可以降低该值，调整后 PostgreSQL 会在下次检查点后删除多余的旧 WAL (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support)。

  • 管理复制槽： 定期检查 pg_replication_slots，及时清理不再使用或滞后过多的复制槽。对于长时间离线的备用节点，可考虑暂时停用复制槽或使用临时复制槽（不使用持久槽）。PostgreSQL 13起提供参数 max_slot_wal_keep_size，可设定每个复制槽最多保留的 WAL 容量。例如：

    ALTER SYSTEM SET max_slot_wal_keep_size = '1GB';

    这样即使从库掉线，主库也只保留最后 1GB 的 WAL，超出部分旧 WAL 将被丢弃（从库再连接时需要重新basebackup）。此参数可以防止单个复制槽无限制地导致 WAL 膨胀 (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support)。

  • 确保归档畅通： 对于启用 WAL 归档的系统，保证 archive_command 的可靠性和效率。检查数据库日志中是否有归档失败错误 (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support，如有则立即处理（例如修正路径、权限或确保目标介质有足够空间）。如果归档速度跟不上 WAL 产生速度（例如压缩过慢或网络带宽不足），可考虑改进归档方式（换用更快的压缩算法或增加带宽） (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support)。参数方面，可以调高 archive_timeout（定期归档超时）以强制及时归档未满段的WAL。

  • 优化检查点设置： 调整 max_wal_size 和检查点间隔，使检查点既不至于过于频繁影响性能，也不要过于稀疏导致 WAL 长时间积压 (Five Reasons Why WAL Segments Accumulate in the pg_wal Directory in PostgreSQL)。例如，如果发现 WAL 积压严重，可以适当降低 max_wal_size，迫使更早触发检查点来回收 WAL。当然要平衡此举带来的额外I/O开销。启用 log_checkpoints 并观察日志，可了解当前检查点策略是否合理。

  • 监控并限制事务规模： 避免单个事务修改数据量过于巨大，否则会一次性产生海量 WAL。将大型事务拆分为多个小事务，可以在间歇期间让检查点有机会执行，避免 WAL 激增。另外，长事务尽量及时提交，缩短运行时间。如果不得不进行大批量更新操作，可以考虑使用 UNLOGGED 表（非持久化，不记录 WAL）或逻辑导入的方法，再与增量方式合并，降低 WAL 写入。

  • 手动触发检查点： 在非常紧急的情况下（例如磁盘空间告急且确定归档/复制问题已修复），可以手动执行 CHECKPOINT，使数据库尽快删除不需要的 WAL 文件 (Why does my pg_wal keep growing? | CYBERTEC PostgreSQL | Services & Support)。但这只是缓解措施，根本解决还需消除导致膨胀的原因。

通过上述调整，大多数 WAL 膨胀问题都可迎刃而解。最后要强调切勿直接手动删除 pg_wal 下的 WAL 文件！所有 WAL 管理应通过 PostgreSQL 提供的机制进行，否则可能破坏数据库的完整性 (Five Reasons Why WAL Segments Accumulate in the pg_wal Directory in PostgreSQL)。

4. 系统表膨胀（System Catalog Bloat）

问题说明

PostgreSQL 的系统目录（又称系统表，位于 pg_catalog 模式中）存储了数据库的元数据，如表定义 (pg_class)、列定义 (pg_attribute)、索引定义 (pg_index)、权限 (pg_authid) 等等。这些系统表本质上也是普通的堆表 (heap table)，因而也会受到 MVCC 机制的影响。如果对数据库执行大量的 DDL 操作（创建、修改、删除表/index/schema等对象），系统目录表中就会产生许多过时的元组（例如删除了一个表会在 pg_class 留下一个死元组） (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。随着时间推移，系统表可能出现膨胀，带来以下问题：

  • 性能下降： 系统目录在查询规划和元数据操作中被频繁访问（例如每次查询优化都会查 pg_statistic、pg_class 等）。如果这些表膨胀严重，查询它们需要扫描更多页，直接导致 SQL 解析和优化阶段变慢 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。典型现象包括大量查询突然规划时间变长、EXPLAIN 分析开销增加等。

  • 元数据操作变慢： 操作系统表本身的DDL如创建新表、删除表、创建索引等，如果目标系统目录膨胀，也可能变慢甚至出现锁等待。（例如创建一个新表涉及插入 pg_class 和 pg_attribute，若这两个表有大量无效元组，插入时可能花更多时间或者引发频繁 autovacuum）。

  • 事务ID 尾随风险： 系统表通常更新频繁，但又往往较小。若膨胀严重且autovacuum一时无法及时清理，还可能出现系统表的 冻结事务ID 问题（不过 PostgreSQL 针对系统表会强制比较激进地真空以避免 wraparound）。简单来说，还是要定期清理系统表膨胀以保持系统稳健。

导致系统目录膨胀的 典型场景 有：

  • 频繁创建和删除临时表/普通表： 临时表的元数据也存放在系统目录中（会标记为临时对象）。频繁的创建/销毁会让 pg_class, pg_attribute 等急剧膨胀。

  • 大量对象反复变更： 比如批量创建索引又删除、反复创建删除模式(schema)、不断增加删除用户等，都可能让相关系统表累积大量死元组。

  • 大规模批量导入元数据： 比如通过工具一次性创建成百上千个表，然后又删除，会对系统表产生冲击。

  • 升级或重构： 某些升级脚本大量创建并替换对象（比如创建新表迁移数据再删旧表）也会在短时间制造系统库膨胀。

检测方法

检测系统表膨胀的方法和用户表类似，可以：

  • 查询系统表大小和行数： 通过 pg_class 查询系统表本身的大小和元组计数。例如：

    SELECT relname, n_live_tup, n_dead_tup, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_stat_all_tables
WHERE schemaname = 'pg_catalog' AND n_dead_tup > 0
ORDER BY n_dead_tup DESC;

    这将列出死元组数量较多的系统表。如果某些系统表（如 pg_attribute）n_dead_tup 很大，则表明膨胀明显。

  • pgstattuple 分析： 针对可疑的系统表（比如 pg_attribute、pg_class），可以使用 pgstattuple('pg_catalog.pg_attribute') 查看具体的空间使用情况，包括死元组百分比。

  • 观察元数据操作延迟： 如果注意到数据库在执行大量 DDL 后，简单的操作（如 CREATE TABLE）变慢，或经常看到 autovacuum 针对系统库运行，那可能已经膨胀。开启 log_autovacuum_min_duration 可以记录哪些表经常被 autovacuum，对系统表也适用。

一般来说，pg_attribute 因为保存每个表的列信息，往往是最大的系统表之一 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。尤其在频繁创建删除列或表的情况下，它很容易膨胀，可以作为检测重点。

处理方式

维护系统目录需要小心，因为这些表对数据库的正常运行至关重要。在安排维护时，应尽量选择业务低谷或者停机窗口。常见的处理和预防措施如下：

  1. 定期 VACUUM 系统表： 建议对系统库中的表进行定期真空。可以通过脚本每日夜间针对 pg_catalog 模式执行 VACUUM（无需 FULL），以清理死元组 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。PostgreSQL 默认 autovacuum 会覆盖系统表，但自行安排额外的 vacuum 能在高DDL活动的场景下提供更及时的清理。

    -- 真空整个系统目录模式的所有表
VACUUM pg_catalog.*;
-- 或者真空特定的系统表
VACUUM pg_catalog.pg_class;
VACUUM pg_catalog.pg_attribute;

    注意，如果一些系统表膨胀特别严重，一次普通 VACUUM 可能无法有效回收空间（因为文件过大但大部分空洞）。这种情况下需要更强力的方法（见下）。

  2. REINDEX SYSTEM 重建系统表索引： 系统表的索引如果膨胀，也会拖慢操作。PostgreSQL 提供了 REINDEX SYSTEM 命令，一次性重建指定数据库所有系统表的索引 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。示例：

    REINDEX SYSTEM mydatabase;

    这会重建 mydatabase 数据库中 pg_catalog 模式下所有系统表的索引。重建索引可以提升这些表的查询性能，也有助于稍后执行 VACUUM 时更快。需要注意，REINDEX SYSTEM 会对系统表索引加独占锁，在操作期间可能影响正在运行的查询 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。因此，推荐在低流量时段进行，或者在只读维护模式下进行。如果无法停机，可考虑逐个对关键系统表使用 REINDEX INDEX 重建索引以减小锁定范围。

    脚本提示： 可以使用命令行工具 reindexdb -s mydatabase 达到同样效果，其中 -s 参数表示重建系统索引 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。

  3. VACUUM FULL 系统表： 当系统表膨胀到影响系统性能且普通 VACUUM 无法缓解时，只能考虑 VACUUM FULL。由于 VACUUM FULL 会重写整张表并锁定，在系统表上执行更需谨慎。例如要对高度膨胀的 pg_attribute 做 VACUUM FULL，必须在停机维护窗口执行，并停止一切其它数据库活动 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。用法：

    VACUUM FULL pg_catalog.pg_attribute;

    由于 pg_attribute 在查询规划中经常被访问，单独对它执行 VACUUM FULL 可能耗时较长 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。可以先确保其它系统表和索引已经通过上面的步骤整理过，以减少锁竞争。只有在万不得已且确认维护窗口足够时，才进行系统表的 VACUUM FULL。

  4. 更新统计信息： 无论进行了 VACUUM、REINDEX 还是 VACUUM FULL，都应在结束后运行 ANALYZE 更新系统表的统计数据 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。例如：

    ANALYZE pg_catalog.pg_class;
ANALYZE pg_catalog.pg_attribute;

    这确保查询优化器对系统表的行数估计是准确的，避免因为统计偏差而降低计划效率。

  5. 优化 DDL 模式： 从根本上讲，减少不必要的对象创建和销毁是避免系统表膨胀的最好办法。对于频繁使用的临时数据，尽量使用临时表而非反复创建删除普通表（临时表元数据虽也在系统表，但生命周期短且自动清理）。或者利用连接池保持临时表存活，避免每次新建。对于批量创建删除场景，可以考虑合并操作、减少单个事务中的反复DDL。

  6. 调优 autovacuum 针对系统表： 可以为系统表设置更积极的 autovacuum 参数（Storage Parameters）。例如：

    ALTER TABLE pg_catalog.pg_class 
  SET (autovacuum_vacuum_scale_factor = 0.0, autovacuum_vacuum_threshold = 50);

    这将使 pg_class 每增加50行更新就触发 autovacuum，而不按比例。这种微调可用于那些增长很快的系统表，使得 autovacuum 更及时地清理它们。不过修改系统表参数需谨慎，一般了解了特定瓶颈才做调整。

总之，系统表膨胀的处理需要在保持系统运行与进行必要维护之间取得平衡。 (How to monitor and fix Database bloats in PostgreSQL? | Netdata )给出了一个推荐的流程：首先停止大部分操作，执行系统索引的 REINDEX，然后 VACUUM 系统表，最后 ANALYZE。如果仍有严重膨胀，再在停机时进行 VACUUM FULL (How to monitor and fix Database bloats in PostgreSQL? | Netdata )。日常则以预防为主，定期vacuum 并关注系统表大小，避免膨胀积重难返。

参考资料：

书籍，文档，有趣的网站
Postgresql应急手册-事务...

Comment