如何使用Autovacuum Postgresql获取空闲空间

Question

pgbootdb=# select pg_size_pretty(pg_database_size('pgbootdb'))
 pg_size_pretty
----------------
 804 MB

pgbootdb=# select pg_size_pretty(pg_relation_size('fruit_type'))
pgbootdb-# ;
 pg_size_pretty
----------------
 410 MB

pgbootdb=# update fruit_type set id = id+1;
UPDATE 6291450

pgbootdb=# delete from fruit_type where id between 1000 and 1200;
DELETE 1206
 
pgbootdb=# select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname  from pg_stat_all_tables where relname='fruit_type';

 n_dead_tup | last_vacuum | last_analyze | n_tup_upd | n_tup_del | n_tup_hot_upd |  relname   |
------------+-------------+--------------+-----------+-----------+---------------+------------+
     
 1206 |                   |                  |   6291450 |      1206 |          1066 | fruit_type |  

autovacuum = on                                               
log_autovacuum_min_duration = -1      
autovacuum_max_workers = 3              
autovacuum_naptime = 1min             
autovacuum_vacuum_threshold = 50        
autovacuum_analyze_threshold = 50       
autovacuum_vacuum_scale_factor = 0.2    
autovacuum_analyze_scale_factor = 0.1  
autovacuum_multixact_freeze_max_age = 400000000 
autovacuum_vacuum_cost_delay = 2ms     
autovacuum = on_vacuum_cost_limit = -1 

死元组和分析仍然存在。

Answer 1

如何计算自动吸尘器何时进行吸尘

为了让autovacuum对fruit_type表进行真空操作，您需要超过两个名为autovacuum_vacuum_threshold和autovacuum_vacuum_scale_factor的阈值。

您有autovacuum_vacuum_threshold = 50，这意味着需要更新或删除50行，还有autovacuum_vacuum_scale_factor = 0.2，这意味着在autovacuum对表进行真空之前，需要更新或删除表的20%。

表的20%是0.2 * 6,291,450 = 1,258,290，因此在autovacuum将对fruit_type表进行真空之前，您需要更新或删除1,258,290 + 50 = 1,258,340行。有关这两个参数的详细信息，请参阅PostgreSQL documentation

澄清对自动吸尘器的误解

由于这篇文章的标题是“如何使用autovacuum postgresql获得空闲空间”，因此有必要消除对autovacuum/vacuum如何工作的一些误解。VACUUM本身并不回收空间--它只是将失效的元组标记为可供将来的INSERT和UPDATE操作重用。但是，VACUUM FULL将回收空间，这与对磁盘进行碎片整理非常相似；请记住，VACUUM FULL将获得表上的独占锁，因此应谨慎使用，以免影响客户端应用程序的性能。在下面的示例中可以看到，即使n_dead_tup已缩减为0，VACUUM也不会回收空间

edb=# create table mytest (id int, name text);
CREATE TABLE
edb=# insert into mytest values (generate_series(1,1000000),md5(random()));
INSERT 0 1000000
edb=# select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd from pg_stat_user_tables where relname = 'mytest';
-[ RECORD 1 ]-+--
n_dead_tup    | 0
last_vacuum   | 
last_analyze  | 
n_tup_upd     | 0
n_tup_del     | 0
n_tup_hot_upd | 0

edb=# select pg_size_pretty( pg_relation_size('mytest'));
-[ RECORD 1 ]--+------
pg_size_pretty | 65 MB

edb=# delete from mytest where id % 3 = 0;
DELETE 333333
edb=# select pg_size_pretty( pg_relation_size('mytest'));
-[ RECORD 1 ]--+------
pg_size_pretty | 65 MB

edb=# select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd from pg_stat_user_tables where relname = 'mytest';
-[ RECORD 1 ]-+-------
n_dead_tup    | 333333
last_vacuum   | 
last_analyze  | 
n_tup_upd     | 0
n_tup_del     | 333333
n_tup_hot_upd | 0

edb=# vacuum mytest ;
VACUUM
edb=# select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd from pg_stat_user_tables where relname = 'mytest';
-[ RECORD 1 ]-+---------------------------------
n_dead_tup    | 0
last_vacuum   | 29-JUL-21 06:07:12.629155 +00:00
last_analyze  | 
n_tup_upd     | 0
n_tup_del     | 333333
n_tup_hot_upd | 0

edb=# select pg_size_pretty( pg_relation_size('mytest'));
-[ RECORD 1 ]--+------
pg_size_pretty | 65 MB

edb=# vacuum full mytest ;
VACUUM
edb=# select pg_size_pretty( pg_relation_size('mytest'));
-[ RECORD 1 ]--+------
pg_size_pretty | 43 MB