PostgreSQL 表膨胀

什么是表膨胀

表膨胀是指表的数据和索引所占文件系统的空间，在有效数据量并未发生大的变化的情况下，不断增大。久而久之，关系文件被大量空洞填满，浪费了大量的磁盘空间。甚至某些特殊场景下，一个表中只有一条简单的数据，但是表对应的物理文件可能已经达到M级甚至G级。

1. 空间持续上涨，到达某一个点后，需要执行一个高额代价的vacuum full(或者cluster等可以重组表的命令)，但vacuum full又是AccessExclusiveLock，8级锁，会阻塞一切访问，意味着在完成清理重组之前，都无法访问该表。同时最多会使用两倍表空间的存储大小。

2. 扫描的效率变低，即使所有记录都是dead状态，PostgreSQL的顺序扫描也会扫描对象所有的老版本，直到执行vacuum将dead的记录删除。

表膨胀优化建议

1. 一定要开启autovacuum。
2. 提高系统的IO能力，越高越好。
3. 设置idle_in_transaction_session_timeout参数，控制长事务的存活时间。
4. 对于大表，建议使用分区，可以加快vacuum的速度。
5. 应用程序设计时，避免使用大批量的更新，删除操作，可以切分为多个事务进行。

查询表膨胀

启用插件

默认情况下，只有超级用户可以访问pgstattuple函数; 可以通过将pg_stat_scan_tables角色授予非超级用户来授予访问权限。

grant pg_stat_scan_tables to test_user;

create extension if not exists pgstattuple;

查询表膨胀语句

pgstattuple函数

select *,1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('table_name');

字段含义

示例-数据准备

postgres=# create table tmp(id text primary key, content text);
CREATE TABLE
Time: 31.711 ms
postgres=# insert into tmp select gen_random_uuid() id, 'text'::varchar(100) content from generate_series(1,100000);
INSERT 0 100000
Time: 5300.654 ms (00:05.301)

示例-查询膨胀

写入数据后表膨胀为13.8%。

postgres=# select *,1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('tmp');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent |         bloat          
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+------------------------
   7659520 |      100000 |   6600000 |         86.17 |                0 |              0 |                  0 |      33340 |         0.44 | 0.13832720588235294118
(1 row)

Time: 60.619 ms

示例-更新数据

执行update（或delete）后，表膨胀到56.3%。

postgres=# update tmp set content = 'text2';
UPDATE 100000
Time: 5781.090 ms (00:05.781)
postgres=# select *,1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('tmp');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent |         bloat          
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+------------------------
  15319040 |      100000 |   6700000 |         43.74 |           100000 |        6600000 |              43.08 |      66680 |         0.44 | 0.56263577874331550802
(1 row)

Time: 169.184 ms

示例-vacuum full

vacuum full后膨胀缩小到12.5%。

postgres=# vacuum full;
VACUUM
Time: 3030.211 ms (00:03.030)
postgres=# select *,1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('tmp');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent |         bloat          
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+------------------------
   7659520 |      100000 |   6700000 |         87.47 |                0 |              0 |                  0 |      33340 |         0.44 | 0.12527155748663101604
(1 row)

Time: 67.065 ms

查询所有表的膨胀情况

语句（过滤掉了没有数据的表）

pg_stat_user_tables表中可以看到最后一次vacuum和auto vacuum的时间

select schemaname,
       relname,
       case
           when (pgstattuple(relid)).table_len = 0
               then 0
           else 1 - (pgstattuple(relid)).tuple_len::numeric / (pgstattuple(relid)).table_len
           end as bloat,
       last_vacuum,
       last_autovacuum,
       (pgstattuple(relid)).*
from pg_stat_user_tables
where (pgstattuple(relid)).table_len > 8192
order by bloat desc;

输出

postgres=# select schemaname,
postgres-#        relname,
postgres-#        case
postgres-#            when (pgstattuple(relid)).table_len = 0
postgres-#                then 0
postgres-#            else 1 - (pgstattuple(relid)).tuple_len::numeric / (pgstattuple(relid)).table_len
postgres-#            end as bloat,
postgres-#        last_vacuum,
postgres-#        last_autovacuum,
postgres-#        (pgstattuple(relid)).*
postgres-# from pg_stat_user_tables
postgres-# where (pgstattuple(relid)).table_len > 8192
postgres-# order by bloat desc;
-[ RECORD 1 ]------+------------------------------
schemaname         | public
relname            | tmp
bloat              | 0.12527155748663101604
last_vacuum        | 
last_autovacuum    | 2025-02-06 06:44:39.766174+00
table_len          | 7659520
tuple_count        | 100000
tuple_len          | 6700000
tuple_percent      | 87.47
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 33340
free_percent       | 0.44

Time: 713.994 ms

占用的磁盘页数

pg_relpages用于快速估算表或索引的磁盘页数

select pg_relpages('table_name');

查看表占用的磁盘页数（没有执行vacuum full前）

postgres=# select pg_relpages('tmp');
 pg_relpages 
-------------
        1870
(1 row)

Time: 3.151 ms

查看表占用的磁盘页数（执行vacuum full后）

postgres=# select pg_relpages('tmp');
 pg_relpages 
-------------
         935
(1 row)

Time: 5.891 ms

补充

pgstattuple_approx 函数可以查看大致的统计信息，pgstattuple_approx不使用全表扫描。

索引膨胀文章

PostgreSQL 索引膨胀

官方文档

https://www.postgresql.org/docs/17/pgstattuple.html