合 PG或GreenPlum中的vacuum full空间不释放的原因及过程模拟
Tags： PGGreenPlumPostgreSQL整理自网络vacuum表膨胀膨胀pg_attribute空间不释放
👉 本文共约2324个字，系统预计阅读时间或需9分钟。
目录
简介
vacuum full本质上是创建了一张新的表，会创建该表的一个新拷贝，并且在操作完成之前都不会释放旧的拷贝。因此在进行vacuum full操作的时候是会加上一个ACCESS EXCLUSIVE级别的锁，所以一般只有当我们需要从表中回收大量磁盘空间的，即膨胀率很高的表才会去做vacuum full的操作。
vacuum full后的空间还是没有释放的原因有哪些？
"vacuum full" 是一种在 PostgreSQL 数据库中执行的命令，它将尝试释放未使用的磁盘空间并优化表的性能。如果执行完 "vacuum full" 后空间没有被释放，则可能有以下原因：
- 表上有活动的长事务：如果有活动的事务（如未提交的事务）在表中运行，那么 "vacuum full" 命令就无法释放该表使用的空间。因为 PostgreSQL 为了保证事务的隔离性，需要将事务执行的数据保留在数据库中，直到事务结束。
- 表上有长时间运行的操作：如果在执行 "vacuum full" 命令期间有其他长时间运行的操作（如查询、备份、复制等），则该操作可能会锁定表，并防止 "vacuum full" 命令释放空间。
- 内存不足：如果服务器的内存不足，则 "vacuum full" 命令可能无法释放空间。因为它需要足够的内存来处理表的索引和数据结构。
- 版本问题：某些 PostgreSQL 版本可能会存在 bug，导致 "vacuum full" 命令无法释放空间。如果是这种情况，建议升级到最新版本或者寻求帮助。
- 表上有大量删除的行：如果表上有大量已删除的行，则 "vacuum full" 命令可能需要花费更长的时间来释放空间。这种情况下，可以尝试使用 "vacuum" 命令来替代 "vacuum full" 命令。
请注意，在执行 "vacuum full" 命令前，请务必备份您的数据。
VACUUM和VACUUM FULL的区别
模拟vacuum full空间不释放问题
长事务
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | db1=# select version(); version ----------------------------------------------------------------------------------------------------------------------------- PostgreSQL 14.5 (Debian 14.5-2.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 row) db1=# db1=# CREATE TABLE t_hash AS db1-# SELECT id, md5(id::text) db1-# FROM generate_series(1, 2000000) AS id; SELECT 2000000 db1=# \dt+ t_hash List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+--------+-------+----------+-------------+---------------+--------+------------- public | t_hash | table | postgres | permanent | heap | 130 MB | (1 row) db1=# select * from pg_stat_all_tables where relname = 't_hash'; relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count -------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+--------------------+-------------+-----------------+--------------+------------------+--------------+------------------+---------------+------------------- 16460 | public | t_hash | 0 | 0 | | | 2000000 | 0 | 0 | 0 | 2000000 | 0 | 2000000 | 2000000 | | | | | 0 | 0 | 0 | 0 (1 row) db1=# \x Expanded display is on. db1=# select * from pg_stat_all_tables where relname = 't_hash'; -[ RECORD 1 ]-------+-------- relid | 16460 schemaname | public relname | t_hash seq_scan | 0 seq_tup_read | 0 idx_scan | idx_tup_fetch | n_tup_ins | 2000000 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 2000000 n_dead_tup | 0 n_mod_since_analyze | 2000000 n_ins_since_vacuum | 2000000 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 db1=# db1=# VACUUM (verbose,analyze,skip_locked,parallel 4) t_hash; INFO: vacuuming "public.t_hash" INFO: table "t_hash": found 0 removable, 80 nonremovable row versions in 1 out of 16667 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 760 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: vacuuming "pg_toast.pg_toast_16460" INFO: table "pg_toast_16460": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 760 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: analyzing "public.t_hash" INFO: "t_hash": scanned 16667 of 16667 pages, containing 2000000 live rows and 0 dead rows; 30000 rows in sample, 2000000 estimated total rows VACUUM db1=# select * from pg_stat_all_tables where relname = 't_hash'; -[ RECORD 1 ]-------+------------------------------ relid | 16460 schemaname | public relname | t_hash seq_scan | 0 seq_tup_read | 0 idx_scan | idx_tup_fetch | n_tup_ins | 2000000 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 2000000 n_dead_tup | 0 n_mod_since_analyze | 0 n_ins_since_vacuum | 0 last_vacuum | 2023-04-11 09:12:53.198607+08 last_autovacuum | 2023-04-11 09:10:19.707567+08 last_analyze | 2023-04-11 09:12:53.457643+08 last_autoanalyze | 2023-04-11 09:10:20.352277+08 vacuum_count | 1 autovacuum_count | 1 analyze_count | 1 autoanalyze_count | 1 db1=# begin ; BEGIN db1=*# delete from t_hash where id<=1000000; DELETE 1000000 |
再开一个窗口执行：
1 2 | db1=# vacuum full t_hash; |
此时会卡住。。。。
查询会话：
可以看到执行vacuum full的窗口在等待锁资源，而该锁就是被idle in tracsaction会话锁住没有释放。
在窗口1做提交后，窗口2立马返回结果。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | db1=# vacuum full t_hash; VACUUM db1=# \dtS+ t_hash List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+--------+-------+----------+-------------+---------------+--------+------------- public | t_hash | table | postgres | permanent | heap | 130 MB | (1 row) db1=# db1=# select * from pg_stat_all_tables where relname = 't_hash'; -[ RECORD 1 ]-------+------------------------------ relid | 16460 schemaname | public relname | t_hash seq_scan | 2 seq_tup_read | 4000000 idx_scan | idx_tup_fetch | n_tup_ins | 2000000 n_tup_upd | 0 n_tup_del | 1000000 n_tup_hot_upd | 0 n_live_tup | 1000000 n_dead_tup | 0 n_mod_since_analyze | 0 n_ins_since_vacuum | 0 last_vacuum | 2023-04-11 09:12:53.198607+08 last_autovacuum | 2023-04-11 09:22:20.742867+08 last_analyze | 2023-04-11 09:12:53.457643+08 last_autoanalyze | 2023-04-11 09:22:21.396793+08 vacuum_count | 1 autovacuum_count | 2 analyze_count | 1 autoanalyze_count | 2 |
但是，此时空间仍然没有释放，需要我们再做一次vacuum full，空间才能释放。
1 2 3 4 5 6 7 8 9 | db1=# vacuum full t_hash; VACUUM db1=# \dtS+ t_hash List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+--------+-------+----------+-------------+---------------+-------+------------- public | t_hash | table | postgres | permanent | heap | 65 MB | (1 row) |
统计信息不更新
这里有个需要注意的地方，vacuum full是不会去更新统计信息的！也就是说如果你执行完vacuum full后去查看pg_stat_all_tables，会发现n_dead_tup仍然没变化，但实际上你的表大小已经降了下来。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
