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的区别

参考：https://www.xmmup.com/greenplumguanlishujukubiaohesuoyindepengzhangjivacuummingling.html#VACUUM_heVACUUM_FULL_de_qu_bie

模拟vacuum full空间不释放问题

长事务

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

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

--------+--------+-------+----------+-------------+---------------+--------+-------------

(1 row)

db1=# select * from pg_stat_all_tables where relname = 't_hash';

-------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+--------------------+-------------+-----------------+--------------+------------------+--------------+------------------+---------------+-------------------

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

再开一个窗口执行：

db1=# vacuum full t_hash;

1 2	db1=# vacuum full t_hash;

此时会卡住。。。。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(2)

标签： PG GreenPlum PostgreSQL vacuum 膨胀表膨胀 pg_attribute 空间不释放

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2024年 4月
一	二	三	四	五	六	日
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

嘿，我是小麦，需要帮助随时找我哦。

18509239930
个人微信
DB宝
个人邮箱
点击加入QQ群
个人微店
回到顶部

合 vacuum full空间不释放的原因及过程模拟

简介

vacuum full后的空间还是没有释放的原因有哪些？

VACUUM和VACUUM FULL的区别

模拟vacuum full空间不释放问题

长事务

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

合 vacuum full空间不释放的原因及过程模拟

简介

vacuum full后的空间还是没有释放的原因有哪些？

VACUUM和VACUUM FULL的区别

模拟vacuum full空间不释放问题

长事务

相关文章

您可能还喜欢...

大页HugePages对PostgreSQL的性能影响

Greenplum使用gpstate查看数据库系统的状态

PG不能启动，报错PANIC replication checkpoint has wrong magic 1714437491 instead of 307747550

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复