谈谈PostgreSQL数据库的表膨胀

发表于2024-11-27|更新于2025-03-13|科技·Tech数据库技术·DatabasesPostgreSQL

|浏览量:

谈谈PostgreSQL数据库的表膨胀

什么是PostgreSQL数据库的表膨胀？

PostgreSQL 数据库的表膨胀，简单来说，就是表的数据存储空间变得比实际需要的要大。膨胀现象发生时，虽然表中的数据量没有显著增加，但数据库占用的磁盘空间却比实际数据量大很多。这种现象会影响性能，导致查询速度变慢，并且浪费磁盘空间。

为什么会产生表膨胀？

表膨胀的主要原因通常有以下几个：

更新和删除操作导致的“死元组”： PostgreSQL 在执行更新（UPDATE）或删除（DELETE）操作时，不是直接修改或删除原来的数据，而是将旧数据标记为“死元组”，然后在新位置插入新数据。这意味着，原来数据的位置还是被保留在磁盘上，直到进行清理（VACUUM）。如果没有及时执行 VACUUM 操作，死元组就会不断积累，导致表膨胀。
MVCC（多版本并发控制）： PostgreSQL 使用多版本并发控制（MVCC）来处理事务。每个事务都会为每一行数据创建一个版本（tuple），并保持旧版本的数据直到事务完全结束。由于事务未提交或者被回滚时，旧版本不会立即清理，造成空间浪费。
索引膨胀：索引在插入、删除、更新数据时，也会产生膨胀。特别是在大量数据删除或更新后，索引结构没有及时整理，导致索引占用更多空间。
自增列：对于包含自增列（如 serial 或 bigserial）的表，删除某些数据后，自增的序列号并不会回收，这可能会导致空间浪费。

检查表膨胀情况的两种方法

方法一查询pg_stat_user_tables、pg_total_relation_size表

SELECT 
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS extra_size
FROM 
    pg_stat_user_tables;

优势

全方位空间使用情况：
pg_total_relation_size 不仅包括表的数据部分（由 pg_relation_size 提供），还包括索引、TOAST 表（用于存储大字段，如 TEXT、BYTEA）等的空间。因此，通过这一方法，你可以看到表占用的总空间，并能知道膨胀的具体来源（是表数据部分，还是索引，或者 TOAST 表）。
这种方式的优势在于，它提供了表的总体空间利用情况，能够识别出索引和 TOAST 的膨胀问题，尤其是在有大量大字段数据时，表膨胀的情况可能会集中在这些地方。

直观的空间差异

通过计算 pg_total_relation_size 和 pg_relation_size 的差异，你能直观地看到有多少空间被“浪费”或未被有效使用，进而推测出是否存在膨胀问题。

限制

这种方法提供的是空间层面的信息，更多关注的是表和索引所占用的物理空间，但它并没有直接告诉你膨胀背后的根本原因（比如死元组的数量）。即使空间看起来膨胀，背后的原因也可能是多样的：可能是死元组、索引膨胀，或者 TOAST 表的膨胀。

方法二查询pg_stat_all_tables表的n_dead_tup、n_live_tup字段

SELECT 
    relname AS table_name,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples
FROM 
    pg_stat_all_tables
WHERE 
    n_dead_tup > 0;

优势

死元组的数量：
通过查看 n_dead_tup（死元组数），你可以知道表中有多少数据行实际上已经被删除或者更新，但由于 PostgreSQL 使用 MVCC（多版本并发控制）机制，这些死元组并没有被及时清理，导致了表空间的浪费。
n_dead_tup 能够直接反映出死元组的积累情况，是判断表是否膨胀的一个非常重要的指标。通常，死元组越多，表的膨胀程度就越高。

反映表的实际使用情况：
n_live_tup 提供的是表中的“活跃”元组数，它代表实际被有效使用的记录数。通过 n_dead_tup 和 n_live_tup 的比例，你可以评估表中死元组的比例，以及是否需要执行 VACUUM 操作来清理这些死元组。

简单直接：
这种方法直接反映出数据库表的实际“健康”状况，特别是死元组的积累情况，适合用于判断是否存在性能问题。可以帮助你判断是否需要进行 VACUUM 或 VACUUM FULL 操作。

限制

不能反映空间的总体使用情况：
这种方式只能反映死元组的数量，并无法告诉你表的总空间使用情况或是否存在索引膨胀等其他问题。它主要关注的是表的“逻辑”大小，而不涉及表的物理存储空间。

两种方法的对比总结

特性	方式一（`pg_stat_user_tables` + `pg_total_relation_size`）	方式二（`pg_stat_all_tables` + `n_dead_tup`）
关注点	表和索引的实际存储空间占用，特别关注表的物理空间膨胀情况。	死元组的数量，反映表的“健康”状态，是否需要清理。
能否检测死元组	不直接提供死元组的数量，但可以通过空间差异间接判断。	直接显示死元组的数量，判断表的膨胀程度。
能否检测索引膨胀	能检测索引的空间占用，通过差异来判断索引膨胀情况。	无法直接检测索引膨胀情况。
优缺点	提供整体的空间占用视图，能够识别空间浪费的具体位置（表、索引、TOAST等）。	专注于死元组，提供的是逻辑上的膨胀信息，更简洁直观，但无法反映索引和其他因素。
是否适合用于定期监控	是，可以定期检查表的空间占用情况，发现物理空间膨胀。	是，特别适用于检查死元组积累，判断是否需要清理。

最佳实践

两种方式可以结合使用，互为补充：

使用 pg_stat_user_tables 和 pg_total_relation_size 来定期检查表的总体空间占用情况，确保没有由于索引膨胀或其他原因导致表占用过多的空间。
使用 pg_stat_all_tables 的 n_dead_tup 和 n_live_tup 来专注于检查死元组的积累情况，及时发现是否需要执行 VACUUM 或 VACUUM FULL 操作。

其它参考SQL

查看表膨胀（对所有表进行膨胀率排序），取前10个

SELECT
    schemaname||'.'||relname as table_name,
    pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio
FROM
    pg_stat_all_tables
WHERE
    n_dead_tup >= 1000
ORDER BY dead_tup_ratio DESC
LIMIT 10;

查看单张表的膨胀率

SELECT
    schemaname||'.'||relname as table_name,
    pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio
FROM
    pg_stat_all_tables
WHERE
    relname = 'table_name';