PostgreSQL 查看表膨胀情况的方法

最新推荐文章于 2025-05-02 17:25:23 发布

文牧之

最新推荐文章于 2025-05-02 17:25:23 发布

阅读量193

点赞数 1

分类专栏： postgresql 文章标签： postgresql 数据库运维

本文链接：https://blog.csdn.net/lee_vincent1/article/details/147668762

版权

postgresql 专栏收录该内容

214 篇文章

订阅专栏

PostgreSQL 查看表膨胀情况的方法

表膨胀(Table Bloat)是PostgreSQL中由于MVCC机制导致的一种常见现象，当大量数据被更新或删除后，表中会积累"死元组"(dead tuples)，这些死元组占据空间但不可见，导致表实际占用的磁盘空间远大于有效数据所需空间。

使用内置视图查看表膨胀

1 使用 `pg_stat_user_tables` 视图

SELECT
    schemaname || '.' || 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,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    round((n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100), 2) AS dead_tuple_percent,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_tuple_percent DESC;

输出示例：

  table_name  | total_size | data_size  | live_tuples | dead_tuples | dead_tuple_percent |        last_vacuum         |        last_autovacuum        
--------------+------------+------------+-------------+-------------+--------------------+----------------------------+-------------------------------
 yewu1.t3     | 8192 bytes | 8192 bytes |          10 |          90 |              90.00 |                            | 2025-04-04 00:59:57.62698-07
 yewu1.t1     | 712 kB     | 440 kB     |       10000 |           0 |               0.00 | 2025-05-02 00:09:40.147-07 | 2025-04-03 16:43:51.808336-07
 yewu1.test10 | 8192 bytes | 8192 bytes |           4 |           0 |               0.00 |                            | 
 yewu1.t2     | 472 kB     | 440 kB     |       10000 |           0 |               0.00 |                            | 2025-04-03 16:46:51.858759-07
(4 rows)

2. 使用 `pgstattuple` 扩展获取详细膨胀数据

-- 先创建扩展
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 查看特定表的膨胀情况
SELECT * FROM pgstattuple('schema_name.table_name');

-- 估算所有表的膨胀情况
SELECT
    schemaname || '.' || relname AS table,
    (pgstattuple(schemaname || '.' || relname)).*
FROM pg_stat_user_tables;

输出示例：

          table          | table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_
percent 
-------------------------+-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+------
--------
 yewu1.t1                |    450560 |       10000 |    378894 |         84.09 |                0 |              0 |                  0 |       9020 |      
      2
 yewu1.t2                |    450560 |       10000 |    378894 |         84.09 |                0 |              0 |                  0 |       9020 |      
      2
 public.pgbench_history  |  14024704 |      267227 |  12826896 |         91.46 |                0 |              0 |                  0 |       7488 |      
   0.05
 yewu1.test1             |      8192 |           3 |       161 |          1.97 |                0 |              0 |                  0 |       7984 |      
  97.46
 yewu1.test6             |         0 |           0 |         0 |             0 |                0 |              0 |                  0 |          0 |      
      0
 public.pgbench_tellers  |      8192 |         100 |      3600 |         43.95 |                0 |              0 |                  0 |       3764 |      
  45.95
 yewu2.t4                |      8192 |           1 |        28 |          0.34 |                0 |              0 |                  0 |       8128 |      
  99.22
 yewu1.test5             |         0 |           0 |         0 |             0 |                0 |              0 |                  0 |          0 |      
      0
 public.pgbench_branches |      8192 |          10 |       320 |          3.91 |                0 |              0 |                  0 |       7804 |      
  95.26
 yewu1.test7             | 513892352 |    10000000 | 408888897 |         79.57 |                0 |              0 |                  0 |     135876 |      
   0.03
 yewu1.test3             |         0 |           0 |         0 |             0 |                0 |              0 |                  0 |          0 |      
      0
 yewu1.test2             |         0 |           0 |         0 |             0 |                0 |              0 |                  0 |          0 |      
      0
 yewu1.t3                |      8192 |          10 |       351 |          4.28 |               90 |           3241 |              39.56 |       3764 |      
  45.95
 public.pgbench_accounts | 134479872 |     1000000 | 121000000 |         89.98 |                0 |              0 |                  0 |    1840616 |      
   1.37
 yewu1.test10            |      8192 |           4 |       136 |          1.66 |                0 |              0 |                  0 |       7988 |      
  97.51
 yewu1.test4             |         0 |           0 |         0 |             0 |                0 |              0 |                  0 |          0 |      
      0
 yewu1.test8             | 513892352 |    10000000 | 408888897 |         79.57 |                0 |              0 |                  0 |     135876 |      
   0.03
(17 rows)