PostgreSQL 表膨胀深度解析：成因、影响与终极解决方案

PostgreSQL 表膨胀深度解析：成因、影响与终极解决方案

大家好，我是你们的数据库老伙计“波斯猫” (PostgreSQL 的谐音，有趣吧？)。今天咱们来聊聊 PostgreSQL 里一个让 DBA 和开发者都头疼的问题——表膨胀 (Table Bloat)。别担心，我会用大白话，结合实际案例，把这事儿掰开了揉碎了，让你彻底搞明白。

什么是表膨胀？

想象一下，你有一个气球，不断地往里面吹气 (插入数据)，然后又放气 (删除数据)，再吹气，再放气…… 如此反复，即使气球里的气体不多了 (实际数据量不大)，但气球本身已经被撑大了 (表的物理文件大小远超实际数据量)。这就是表膨胀。

更技术一点的解释：PostgreSQL 为了实现 MVCC (多版本并发控制)，在更新或删除数据时，并不会立即物理删除旧版本的数据行，而是标记为“死亡元组”(Dead Tuples)。这些死亡元组仍然占用磁盘空间，导致表的物理文件大小持续增长，即使有效数据量并没有增加，这就是表膨胀。

为什么会发生表膨胀？

罪魁祸首就是 PostgreSQL 的 MVCC 机制。当然，还有一些操作会加剧膨胀：

1. 频繁的 UPDATE 和 DELETE 操作： 这是最主要的原因。每次 UPDATE 实际上是插入新版本行，然后将旧版本行标记为死亡元组。DELETE 也是将行标记为死亡元组。这些操作越多，死亡元组积累得越快。
2. 长时间运行的事务： 长时间运行的事务会阻止 VACUUM 清理死亡元组。因为这些事务可能还需要访问旧版本的数据，VACUUM 不能贸然清理。
3. 大量的批量导入/删除操作： 比如你一次性导入了几百万条数据，然后又删除了其中大部分，这会导致大量的死亡元组产生。
4. 不合理的 VACUUM 设置： PostgreSQL 的 autovacuum 进程负责自动清理死亡元组。如果 autovacuum 设置不合理，比如触发阈值过高，或者运行频率过低，会导致死亡元组不能及时清理。
5. FULL VACUUM 使用不当： 虽然 VACUUM FULL 可以彻底清理死亡元组并回收空间，但是它会锁定整个表，导致数据库在该期间无法提供服务。如果频繁使用，会严重影响数据库的可用性。所以说VACUUM FULL是重量级武器，非必要不使用。

表膨胀有什么危害？

表膨胀可不是闹着玩的，它会带来一系列问题：

1. 磁盘空间浪费： 这是最直观的危害。大量的死亡元组占用了宝贵的磁盘空间，导致存储成本上升。
2. 查询性能下降： 即使你只查询少量数据，PostgreSQL 也可能需要扫描大量的死亡元组才能找到你需要的数据。这会导致查询变慢，响应时间延长。
3. 索引效率降低： 表膨胀也会影响索引的效率。因为索引中也会包含指向死亡元组的指针，这会导致索引扫描变慢。
4. 备份和恢复时间延长： 表膨胀会导致备份文件变大，从而延长备份和恢复的时间。
5. 数据库性能整体下降： 严重的表膨胀会导致数据库整体性能下降，甚至导致数据库崩溃。

如何诊断表膨胀？

“知己知彼，百战不殆”。要解决表膨胀，首先要学会诊断它。这里介绍几种常用的方法：

1. 使用 pg_stat_all_tables 视图： 这个视图提供了表的统计信息，包括死亡元组的数量 (n_dead_tup)。你可以通过比较 n_live_tup (存活元组数量) 和 n_dead_tup 来判断表是否膨胀。

   SELECT relname, n_live_tup, n_dead_tup, (n_dead_tup * 100 / (n_live_tup + n_dead_tup))::float AS dead_ratio
   FROM pg_stat_all_tables
   WHERE schemaname = 'public'  -- 替换成你的 schema
   AND n_live_tup > 0
   ORDER BY dead_ratio DESC;

   一般来说，dead_ratio 超过 20% 就需要关注了。

2. 使用 pgstattuple 扩展： 这个扩展提供了更详细的表和索引的统计信息，包括表的膨胀率。安装好扩展后，你可以这样查询：

   SELECT * FROM pgstattuple('your_table_name');
   

3. 使用 check_postgres 脚本： 这是 bucardo 项目提供的一个 Perl 脚本，可以用来检查 PostgreSQL 数据库的各种健康指标，包括表膨胀。使用方法参见官方文档。

4. pg_bloat_check 脚本：
   这是一个自定义的 SQL 脚本，原理与 pg_stat_all_tables 类似，但是提供了更友好的输出和估算的膨胀大小。

WITH constants AS (
    -- define some constants for sizes of things
    -- for reference, a tuple header is 23 bytes, a pointer is 4 bytes, an index entry is 8 bytes
    SELECT current_setting('block_size')::numeric AS bs, 23 AS th, 4 AS ta, 8 AS ia
),
no_stats AS (
    -- screen out table who have attributes
    -- which dont have stats, such as JSON
    SELECT table_schema, table_name
    FROM information_schema.columns
    LEFT OUTER JOIN pg_stats ON table_schema = schemaname AND table_name = tablename AND column_name = attname
    WHERE attname IS NULL
),
null_headers AS (
    -- calculate null header sizes
    -- omitting tables which dont have complete stats
    -- see the section about NULL definitions
    SELECT
        hdr+1+(sum(case when null_frac=0 then 0 else 1 end)/8) as nullhdr, --Size of the null bitmap
        SUM((1-null_frac)*avg_width) as datawidth, -- Space required by valid columns
        MAX(null_frac) as maxfracsum, -- Largest null fraction
        schemaname, tablename, hdr, ma, bs
    FROM pg_stats CROSS JOIN constants
    LEFT OUTER JOIN no_stats ON schemaname = no_stats.table_schema AND tablename = no_stats.table_name
    WHERE no_stats.table_name IS NULL AND attname IS NOT NULL
    GROUP BY schemaname, tablename, hdr, ma, bs
),
headers AS (
    -- estimate header and row size
    SELECT
        ma, bs, hdr, schemaname, tablename,
        (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, -- Actual size of the header + data
        (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 -- Actual size of the null header
    FROM null_headers
),
rows AS (
    -- calculate real row size
    SELECT schemaname, tablename, bs, ma, hdr, datahdr, nullhdr2,
        ceil((datahdr+nullhdr2)/ma) * ma AS otta -- "Overlay To Tuple Alignment"
    FROM headers
),
estimates AS (
    SELECT
        schemaname, tablename, bs,
        pg_class.reltuples,
        pg_class.relpages,
        otta,
        (CEIL((pg_class.reltuples*otta)/bs)) AS expected_blocks -- expected number of blocks based on row size
    FROM rows
    JOIN pg_class ON tablename = relname AND schemaname = pg_namespace.nspname
    JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    WHERE pg_class.relkind = 'r'
)
-- and finally the output
SELECT
    schemaname,
    tablename,
    reltuples::bigint,
    relpages::bigint,
    bs,
    otta,
    expected_blocks::bigint,
    relpages::bigint - expected_blocks::bigint AS bloat_blocks, -- difference between real and expected
    CASE WHEN relpages > expected_blocks THEN
        (relpages::bigint - expected_blocks::bigint)::numeric * bs
    ELSE 0 END AS bloat_size
    -- approximate size of bloat
FROM estimates
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY bloat_size DESC;

5. 直观感受： 如果你发现某个表的查询速度明显变慢，或者备份文件异常增大，而这个表又经常进行 UPDATE 和 DELETE 操作，那么很可能就是发生了表膨胀。

如何解决表膨胀？

既然找到了病因，接下来就是对症下药了。这里提供几种常用的解决方案：

1. 定期 VACUUM： 这是最基本也是最重要的措施。PostgreSQL 的 autovacuum 进程会自动执行 VACUUM，但你需要根据实际情况调整 autovacuum 的参数，比如：

   • autovacuum_vacuum_threshold：触发 autovacuum 的基本阈值，默认是 50。
   • autovacuum_vacuum_scale_factor：触发 autovacuum 的比例因子，默认是 0.2 (20%)。当死亡元组数量超过 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples 时，就会触发 autovacuum。
   • autovacuum_analyze_threshold 和 autovacuum_analyze_scale_factor：控制 autovacuum 执行 ANALYZE 的阈值。ANALYZE 用于收集表的统计信息，对查询优化器非常重要。
   • autovacuum_naptime：autovacuum 进程的休眠时间.建议根据负载情况调整.

   你可以通过修改 postgresql.conf 文件来调整这些参数，也可以针对单个表进行设置：

   ALTER TABLE your_table_name SET (autovacuum_vacuum_scale_factor = 0.1);
   

   此外，你也可以手动执行 VACUUM：

   VACUUM your_table_name;
   

2. 使用 VACUUM FULL (谨慎使用)： 前面提到，VACUUM FULL 会锁定整个表。所以，只有在你能接受数据库停机的情况下，才可以使用它。一般建议在业务低峰期执行，或者在维护窗口期间执行。

   VACUUM FULL your_table_name;
   

3. 使用 pg_repack 扩展： 这是解决表膨胀的终极武器。pg_repack 可以在不锁定表的情况下重建表和索引，从而彻底消除膨胀。它通过创建一张新表，然后将旧表的数据复制到新表，最后切换表名来实现。这个过程对应用是透明的，几乎不会影响数据库的正常运行。

   安装好 pg_repack 后，你可以这样使用：

   pg_repack -d your_database_name -t your_table_name
   

   pg_repack 也支持在线重建索引：

   pg_repack -d your_database_name -i your_index_name
   

4. 优化应用逻辑：

   • 避免频繁的 UPDATE 和 DELETE 操作。如果可能，尽量使用 INSERT 代替 UPDATE，或者将多次 UPDATE 合并成一次。
   • 尽量缩短事务的运行时间。长时间运行的事务会阻止 VACUUM 清理死亡元组。
   • 对于批量导入/删除操作，可以考虑先将数据导入到临时表，然后进行处理，最后再将结果插入到目标表。或者分批次导入和删除。
   • 避免在高峰时段执行大批量更新或删除操作。

5. 使用分区表： 如果你的表非常大，可以考虑使用分区表。将大表拆分成多个小表，可以提高查询性能，也可以更方便地管理表膨胀。你可以定期清理旧分区，或者将旧分区归档。

6. 合理使用临时表：
   对于一些中间计算结果，可以使用临时表存储，避免对主表进行频繁的更新和删除操作。

总结

表膨胀是 PostgreSQL 中一个常见的问题，但只要我们了解了它的成因、危害和解决方法，就能有效地应对它。记住这几点：

• 定期 VACUUM： 这是最基本也是最重要的措施。
• 谨慎使用 VACUUM FULL： 只有在你能接受数据库停机的情况下才使用。
• pg_repack 是终极武器： 它可以在不锁定表的情况下重建表和索引。
• 优化应用逻辑： 避免频繁的 UPDATE 和 DELETE 操作，缩短事务运行时间。
• 分区表和大表拆分： 将大表拆分成多个小表。
• 监控和预警： 建立完善的监控体系,及早发现并处理表膨胀问题。

希望这篇文章能帮助你更好地理解和解决 PostgreSQL 表膨胀问题。如果你还有其他问题，欢迎随时向我提问。记住，我是你的数据库老伙计“波斯猫”，我们下次再见！