1、前言介绍
PostgreSQL数据库，由于其自身特有的MVCC机制，它没有自己的Undo Log。更新操作相当于一个删除叠加一个插入操作，反映在表数据文件当中就是内部的一条新增记录，加上原有记录的删除。那些已被删除的元组(死元组)所占的空间如果得不到及时的回收，那么表数据文件所用空间将会不断膨胀，查询及其它CUD操作也会变慢，直到系统不可承受。这就是AutoVaccum存在的原因。
这也是PostgreSQL MVCC背后的基本思想之一,因为它允许更大并发,在不同的进程之间最小的锁定.这个MVCC实现的缺点是留下了已删除的元组,即使在所有可能看到这些版本的事务完成之后也是如此.可以想见，为何PostgreSQL在并发读写的测试中，很多场景下为何性能异常优秀。
如果是一名DB Ops或DBA，对这种空间膨胀缺乏必要的异常监控，一旦出问题，系统也将变得容易出问题，有时候会酿成大问题。
2、并发控制中的增删改
为了方便理解空间的膨胀以及死元组，我们可以用小实验来简单体会一下。
我们建一张两个字段的表，并插入一行值：
CREATE TABLE t1 (id int, col2 varchar(32));
insert into t1 values(1, 'wang');
查询一下它的基本情况，包括一些常规的内置字段：
mydb=# select tableoid, ctid, xmin, xmax, cmin, cmax, * from t1;
tableoid | ctid | xmin | xmax | cmin | cmax | id | col2
----------+-------+------+------+------+------+----+------
21377 | (0,1) | 1395 | 0 | 0 | 0 | 1 | wang
(1 row)
看一下，会有点小吃惊，多达6个内置字段。回想，我们在PG中有400多个keyword. (用SELECT word FROM pg_get_keywords()可以得，略过），但是那些keyword里并不包含tableoid之类的。但是你如果想建表，字段名称与上边的内置字段相重，建表会失败的。如果遇到别的数据库，刚好有这几个字段中的几个，移植过来，就得想办法了。我们来看看这6个字段的含义：
-
tableoid：指的是table t1的对象标识id (OID)的值，它在表对象一级是全局唯一的。使用下边的查询一样可以得到它的值。并且顺便得到table对应的文件路径(base/21371/21465)：
mydb=# select oid, pg_relation_filepath(oid), relname, relkind, relpages from pg_class where relname = 't1';
oid | pg_relation_filepath | relname | relkind | relpages
-------+----------------------+---------+---------+----------
21377 | base/21371/21465 | t1 | r | 0
ctid: 元组的id, 它相当于一个坐标值(pair), 第一个元素描述的是页号，
第二个元素描述的是元组在页内的序号。
-
xmin, xmax: 最小最大事务号, 这里有一定的规则。我们任意时刻都可以
使用txid_current()得到当前的事务号。
-
cmin, cmax: 最小最大command id，记录的是事务当中的第几个命令和命令范围。
大致明白了这些字段含义，我们可以进一步往下处理。这里要用到pageinspect
内置插件。大家可以自行安装：(create extension pageinspect)，需要
用到函数：heap_page_items以及get_raw_page。
做一次删除操作：
mydb=*# select txid_current();
txid_current
--------------
1400
(1 row)
mydb=*# delete from t1;
DELETE 1
mydb=*# select tableoid, ctid, xmin, xmax, cmin, cmax, * from t1;
tableoid | ctid | xmin | xmax | cmin | cmax | id | col2
----------+------+------+------+------+------+----+------
(0 rows)
mydb=*# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data
FROM heap_page_items(get_raw_page('t1', 0));
tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------
1 | 1395 | 1400 | 0 | (0,1) | \x010000000b77616e67
(1 row)
我们会发现，t_xmax的值变了，变成1400了。它是新的txid。比t_xmin大。再插入一条新记录，然后更新一下那条记录看下：
mydb=# begin;
BEGIN
mydb=*# select pg_current_xact_id(), txid_current();
pg_current_xact_id | txid_current
--------------------+--------------
1401 | 1401
(1 row)
mydb=*# delete from t1;
DELETE 1
mydb=*# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data
FROM heap_page_items(get_raw_page('t1', 0));
tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------
1 | 1395 | 1401 | 0 | (0,1) | \x010000000b77616e67
(1 row)
mydb=*# insert into t1 values(2, 'aaaa');
INSERT 0 1
mydb=*# select tableoid, ctid, xmin, xmax, cmin, cmax, * from t1;
tableoid | ctid | xmin | xmax | cmin | cmax | id | col2
----------+-------+------+------+------+------+----+------
21377 | (0,2) | 1401 | 0 | 1 | 1 | 2 | aaaa
(1 row)
mydb=*# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data
mydb-*# FROM heap_page_items(get_raw_page('t1', 0));
tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------
1 | 1395 | 1401 | 0 | (0,1) | \x010000000b77616e67
2 | 1401 | 0 | 1 | (0,2) | \x020000000b61616161
(2 rows)
mydb=*# update t1 set col2='bbbb';
UPDATE 1
mydb=*# select tableoid, ctid, xmin, xmax, cmin, cmax, * from t1;
tableoid | ctid | xmin | xmax | cmin | cmax | id | col2
----------+-------+------+------+------+------+----+------
21377 | (0,3) | 1401 | 0 | 2 | 2 | 2 | bbbb
(1 row)
mydb=*# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data
FROM heap_page_items(get_raw_page('t1', 0));
tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------
1 | 1395 | 1401 | 0 | (0,1) | \x010000000b77616e67
2 | 1401 | 1401 | 0 | (0,3) | \x020000000b61616161
3 | 1401 | 0 | 2 | (0,3) | \x020000000b62626262
(3 rows)
mydb=*# commit;
COMMIT
开启新的事务后，事务id变为1401。插入一条记录后，新记录的xmin为1401. 然后再更新这条记录，它会先将(0,2)这一行的ctid更新一下，变为(0,3)，同时把
xmax置为当前txid，与xmin一样。最后再插入一条新的记录。这时command id
也变更为2了。意味着commit command有两条命令了。因为上边是连续两条SQL命令，所以
是这个效果。
如果另启一个事务，再更新那条记录，则得到下边的结果：
mydb=# begin;
BEGIN
mydb=*# select pg_current_xact_id(), txid_current();
pg_current_xact_id | txid_current
--------------------+--------------
1402 | 1402
(1 row)
mydb=*# update t1 set col2='bbbb';
UPDATE 1
mydb=*# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data
mydb-*# FROM heap_page_items(get_raw_page('t1', 0));
tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------
1 | 1395 | 1401 | 0 | (0,1) | \x010000000b77616e67
2 | 1401 | 1401 | 0 | (0,3) | \x020000000b61616161
3 | 1401 | 1402 | 0 | (0,4) | \x020000000b62626262
4 | 1402 | 0 | 0 | (0,4) | \x020000000b62626262
(4 rows)
mydb=*# commit;
COMMIT
这里xmax的值是按照当前的事务来设置的。
至于t_data的值，我们可以看到形如：\x020000000b61616161 和 \x020000000b62626262基本上也能猜得到：\x02000000 是 整数字段值, 而末尾的:61616161和
62626262就是'aaaa'和'bbbb'的二进制表示。那么中间的0b表示的是啥？它
应该与两个字段的Nullable和各自长度有关，有时间可以分析一下源码来看下。
至此，我们的基本目的也达到了。
3 AutoVacuum的使用
PostgreSQL对AutoVacuum的使用，也提供了一些重要的配置参数，用于控制对死元组的清理。
在生产环境中，可千万不要将autovacuum给禁用，它默认就是开启的。就相当于定时帮你回收
死元组所所占的空间，虽然有些时候，它的效果达不到预期。
完全禁用autovacuum,请不要这样做，除非你真的知道你在做什么,并且需要定期清理脚本.否则当问题发生时你将不得不处理花费大量的时间处理,甚至可能需要停库、停机。
我们来看下，死元组与活元组占表中数据行的比例，简单来讲，可以用下边的SQL来描述。
select relname, n_dead_tup as "deads",
(
case when n_live_tup > 0 then n_dead_tup::float8/n_live_tup::float8
else 0 end
) as "dead/live_percent"
from pg_stat_all_tables where n_dead_tup>0 and schemaname='public';
下边还是针对前边的表t1做一次模拟，插入100000条数据，然后分别删除90000和9999条
看看相关结果：
mydb=# truncate table t1;
TRUNCATE TABLE
mydb=# insert into t1 select n, 'test'||n from generate_series(1, 100000) as n;
INSERT 0 100000
mydb=# select relname, n_dead_tup as "deads",
(
case when n_live_tup > 0 then n_dead_tup::float8/n_live_tup::float8
else 0 end
) as "dead/live_percent"
from pg_stat_all_tables where n_dead_tup>0 and schemaname='public';
relname | deads | dead/live_percent
---------+-------+-------------------
(0 rows)
mydb=# delete from t1 where id < 90001;
DELETE 90000
mydb=# select relname, n_dead_tup as "deads",
(
case when n_live_tup > 0 then n_dead_tup::float8/n_live_tup::float8
else 0 end
) as "dead/live_percent"
from pg_stat_all_tables where n_dead_tup>0 and schemaname='public';
relname | deads | dead/live_percent
---------+-------+-------------------
t1 | 90000 | 9
(1 row)
mydb=# delete from t1 where id < 100000;
DELETE 9999
mydb=# select relname, n_dead_tup as "deads",
(
case when n_live_tup > 0 then n_dead_tup::float8/n_live_tup::float8
else 0 end
) as "dead/live_percent"
from pg_stat_all_tables where n_dead_tup>0 and schemaname='public';
relname | deads | dead/live_percent
---------+-------+-------------------
t1 | 9999 | 9999
(1 row)
这个说明死元组所占比例已经相当高了。该vacuum要下手处理了。如果系统中发现一些表
这个比例值一直偏高，那么说明autovacuum没有处理或者处理的就不怎么样。
我们可以将这个SQL统计语句稍做改进，改为dead元组占(live+dead)整个的粗略百分
比，那样看起来可能更直观些。
select relname, n_dead_tup as "deads",
(
case when n_live_tup > 0 then
round(100*n_dead_tup::numeric(19)/(n_live_tup + n_dead_tup),2)
else 0 end
) as "dead/live_percent"
from pg_stat_all_tables where n_dead_tup>0 and schemaname='public';
我们再来模拟一下，
mydb=# truncate t1;
TRUNCATE TABLE
mydb=# insert into t1 select n, 'test'||n from generate_series(1, 100000) as n;
INSERT 0 100000
mydb=# delete from t1 where id < 90001;
DELETE 90000
mydb=# select relname, n_dead_tup as "deads",
(
case when n_live_tup > 0 then
round(100*n_dead_tup::numeric(19)/(n_live_tup + n_dead_tup),2)
else 0 end
) as "dead/live_percent"
from pg_stat_all_tables where n_dead_tup>0 and schemaname='public';
relname | deads | dead/live_percent
---------+-------+-------------------
t1 | 90000 | 90.00
(1 row)
这下我们能看到90%占比。
结合表所占的磁盘空间，再改造一下基本上可以得到一个可用的监控语句了。
SELECTtable_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size,
(case when n_live_tup > 0 then
round(100*n_dead_tup::numeric(19)/n_live_tup,2)
else 0 end) as dead_percent,
last_autovacuum, last_vacuum, n_dead_tup, n_live_tup, n_tup_ins, n_tup_upd, n_tup_del, vacuum_count, autovacuum_count
FROM information_schema.tables, pg_stat_all_tables stat
WHERE table_schema = 'public' and stat.schemaname = table_schema and stat.relname = table_name and stat.n_dead_tup>0
ORDER BY
dead_percent, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') desc
limit 100;
-[ RECORD 1 ]----+----------
table_full_name | public.t1
size | 4352 kB
dead_percent | 900.00
last_autovacuum |
last_vacuum |
n_dead_tup | 90000
n_live_tup | 10000
n_tup_ins | 100000
n_tup_upd | 0
n_tup_del | 90000
vacuum_count | 0
autovacuum_count | 0
这里我们能明确的看到死元组，活元组的数量关系。
说明：为了得到效果，我们要把aotuvacuum临时 关闭一下：
mydb=# alter system set autovacuum = off;
ALTER SYSTEM
mydb=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
mydb=# show autovacuum;
autovacuum
------------
off
我们再把autovacuum的开，并观察一段时间看看情况：
postgres=# alter system set autovacuum = on;
ALTER SYSTEM
postgres=# select pg_reload_conf(), pg_sleep(10);
-[ RECORD 1 ]--+--
pg_reload_conf | t
pg_sleep |
postgres=# SELECT
postgres-# table_schema || '.' || table_name AS table_full_name,
postgres-# pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size,
postgres-# (case when n_live_tup > 0 then
postgres(# round(100*n_dead_tup::numeric(19)/n_live_tup,2)
postgres(# else 0 end) as dead_percent,
postgres-# last_autovacuum, last_vacuum, n_dead_tup, n_live_tup, n_tup_ins, n_tup_upd, n_tup_del, vacuum_count, autovacuum_count
postgres-# FROM information_schema.tables, pg_stat_all_tables stat
postgres-# WHERE table_schema = 'public' and stat.schemaname = table_schema and stat.relname = table_name and stat.n_dead_tup>=0
postgres-# ORDER BY
postgres-# dead_percent, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') desc
postgres-# limit 100;
-[ RECORD 1 ]----+----------
table_full_name | public.t1
size | 4352 kB
dead_percent | 900.00
last_autovacuum |
last_vacuum |
n_dead_tup | 90000
n_live_tup | 10000
n_tup_ins | 100000
n_tup_upd | 0
n_tup_del | 90000
vacuum_count | 0
autovacuum_count | 0
似乎还没有执行：因为last_autovacuum还没有值。再过一会儿，查询一下：
postgres=# SELECT
postgres-# table_schema || '.' || table_name AS table_full_name,
postgres-# pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size,
postgres-# (case when n_live_tup > 0 then
postgres(# round(100*n_dead_tup::numeric(19)/n_live_tup,2)
postgres(# else 0 end) as dead_percent,
postgres-# last_autovacuum, last_vacuum, n_dead_tup, n_live_tup, n_tup_ins, n_tup_upd, n_tup_del, vacuum_count, autovacuum_count
postgres-# FROM information_schema.tables, pg_stat_all_tables stat
postgres-# WHERE table_schema = 'public' and stat.schemaname = table_schema and stat.relname = table_name and stat.n_dead_tup>=0
postgres-# ORDER BY
postgres-# dead_percent, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') desc
postgres-# limit 100;
-[ RECORD 1 ]----+-----------------------------
table_full_name | public.t1
size | 4360 kB
dead_percent | 0.00
last_autovacuum | 2023-03-12 00:31:11.13097+00
last_vacuum |
n_dead_tup | 0
n_live_tup | 10000
n_tup_ins | 100000
n_tup_upd | 0
n_tup_del | 90000
vacuum_count | 0
autovacuum_count | 1
这下我们看到了结果。dead_tup变为0了。但是size部分从4352->4360，没什么长进。这意味着好算dead tuple"清除“了，空间也未能能回收。主要是因为autovacuum/vacuum
只能对数据文件尾部连续的空闲空间进行回收，如果不符合这个条件，那就还是空在那个地方被后一煌
操作重复利用了。
所以，这个东西要一分为二的看，一方面，如果能回收，最好。确保dead_tup真的被清
理掉。另一方面，即算空间没有被收回，但也要防止空间不断的往上涨。
4 AutoVacuum的相关参数
直接从settings中可以得到几乎所有vacuum相关的参数：
postgres=# SELECT name, setting FROM pg_settings WHERE name ilike '%vacuum%';
name | setting
---------------------------------------+------------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 3
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 60
autovacuum_vacuum_cost_delay | 2
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_insert_scale_factor | 0.2
autovacuum_vacuum_insert_threshold | 1000
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50
autovacuum_work_mem | -1
log_autovacuum_min_duration | -1
vacuum_cost_delay | 0
vacuum_cost_limit | 200
vacuum_cost_page_dirty | 20
vacuum_cost_page_hit | 1
vacuum_cost_page_miss | 2
vacuum_defer_cleanup_age | 0
vacuum_failsafe_age | 1600000000
vacuum_freeze_min_age | 50000000
vacuum_freeze_table_age | 150000000
vacuum_multixact_failsafe_age | 1600000000
vacuum_multixact_freeze_min_age | 5000000
vacuum_multixact_freeze_table_age | 150000000
(27 rows)
name |
setting |
description |
|---|---|---|
name |
setting |
description |
| autovacuum | on | Starts the autovacuum subprocess. |
| autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. |
| autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze. |
| autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound. |
| autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes. |
| autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound. |
| autovacuum_naptime | 60s | Time to sleep between autovacuum runs. |
| autovacuum_vacuum_cost_delay | 2ms | Vacuum cost delay in milliseconds, for autovacuum. |
| autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum. |
| autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. |
| autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum. |
| autovacuum_work_mem | -1 | Sets the maximum memory to be used by each autovacuum worker process. |
| vacuum_cleanup_index_scale_factor | 0.1 | Number of tuple inserts prior to index cleanup as a fraction of reltuples. |
| vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds. |
| vacuum_cost_limit | 200 | Vacuum cost amount available before napping. |
| vacuum_cost_page_dirty | 20 | Vacuum cost for a page dirtied by vacuum. |
| vacuum_cost_page_hit | 1 | Vacuum cost for a page found in the buffer cache. |
| vacuum_cost_page_miss | 2 | Vacuum cost for a page not found in the buffer cache. |
| vacuum_defer_cleanup_age | 0 | Number of transactions by which VACUUM and HOT cleanup should be deferred, if any. |
| vacuum_freeze_min_age | 50000000 | Minimum age at which VACUUM should freeze a table row. |
| vacuum_freeze_table_age | 150000000 | Age at which VACUUM should scan whole table to freeze tuples. |
| vacuum_multixact_freeze_min_age | 5000000 | Minimum age at which VACUUM should freeze a MultiXactId in a table row. |
| vacuum_multixact_freeze_table_age | 150000000 | Multixact age at which VACUUM should scan whole table to freeze tuples. |
有三到四个参数需要重点关注 :
autovacuum_naptime: 60s, 指的是每两次autovacuum之间要sleep多长时间，意味着要idle 60秒的时间。
autovacuum_vacuum_scale_factor: 在vacuum执行之前，更新或删除的元组的数量在总的元组数量中的百分比.
autovacuum_vacuum_threshold: vacuum之前，update或者delete的元组的绝对数量。
autovacuum_vacuum_cost_delay: 当workpoint达到cost_limit时，就会延迟一段时间（如2ms)
autovacuum_vacuum_cost_limit: 是一个cost limit，autocommit工作时，会执行统计。如果它的值是-1，则取vacuum_cost_limit的值。在一些IO处理比较快的服务器中，可以把delay调小，而cost limit调的大一点。
5 AutoVacuum的触发
当满足下边的条件的时候，autovacuum会被自动触发：n_dead_tup>n_live_tup*autovacuum_vacuum_scale_factor + autovacuum_vacuum_threshold
看起来就是live元组的20% 加上 默认的50条，超过这么多条死元组，就会触发autovacuum进行清理。
看看简单示例：
1) 插入1250条记录，删除200条。没有达到条件，因此不会触发.能看到1250条记录，删掉200条以后，live的有1050条.
postgres=# truncate t1;
TRUNCATE TABLE
postgres=# insert into t1 select n, 'test'||n from generate_series(1, 1250) as n;
INSERT 0 1250
postgres=# delete from t1 where id<=200;
DELETE 200
postgres=# SELECT
table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size,
(case when n_live_tup > 0 then
round(100*n_dead_tup::numeric(19)/n_live_tup,2)
else 0 end) as dead_percent,
last_autovacuum, last_vacuum, n_dead_tup, n_live_tup, n_tup_ins, n_tup_upd, n_tup_del, vacuum_count, autovacuum_count
FROM information_schema.tables, pg_stat_all_tables stat
WHERE table_schema = 'public' and stat.schemaname = table_schema and stat.relname = table_name and stat.n_dead_tup>=0
ORDER BY
dead_percent, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') desc
limit 100;
table_full_name | size | dead_percent | last_autovacuum | last_vacuum | n_dead_tup | n_live_tup | n_tup_ins | n_tup_upd
| n_tup_del | vacuum_count | autovacuum_count
-----------------+-------+--------------+-------------------------------+-------------+------------+------------+-----------+-----------
+-----------+--------------+------------------
public.t1 | 80 kB | 19.05 | 2023-03-12 01:19:11.530273+00 | | 200 | 1050 | 104749 | 0
| 90948 | 0 | 4
(1 row)
2) 接上边，再删除52条。再看看, 应该能触发. 同时要考虑上边的naptime (60s) ，live的还有大概998， dead的数量已经超过200 + 50了，定能触发。
postgres=# delete from t1 where id<=252;
DELETE 52
postgres=# SELECT
table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size,
(case when n_live_tup > 0 then
round(100*n_dead_tup::numeric(19)/n_live_tup,2)
else 0 end) as dead_percent,
last_autovacuum, last_vacuum, n_dead_tup, n_live_tup, n_tup_ins, n_tup_upd, n_tup_del, vacuum_count, autovacuum_count
FROM information_schema.tables, pg_stat_all_tables stat
WHERE table_schema = 'public' and stat.schemaname = table_schema and stat.relname = table_name and stat.n_dead_tup>=0
ORDER BY
dead_percent, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') desc
limit 100;
table_full_name | size | dead_percent | last_autovacuum | last_vacuum | n_dead_tup | n_live_tup | n_tup_ins | n_tup_upd
| n_tup_del | vacuum_count | autovacuum_count
-----------------+-------+--------------+-------------------------------+-------------+------------+------------+-----------+-----------
+-----------+--------------+------------------
public.t1 | 88 kB | 5.21 | 2023-03-12 01:23:11.559591+00 | | 52 | 998 | 104749 | 0
| 91000 | 0 | 5
(1 row)
看看，前边的200个死元组应该是被清掉了。现在的52个达不到条件，就跳过去了。
如果我们再删掉200个元组，看看啥情况，它应该会将live_tup除到798个，又会触发一次，请看：
delete from t1 where id<=450;
postgres=# SELECT
table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size,
(case when n_live_tup > 0 then
round(100*n_dead_tup::numeric(19)/n_live_tup,2)
else 0 end) as dead_percent,
last_autovacuum, last_vacuum, n_dead_tup, n_live_tup, n_tup_ins, n_tup_upd, n_tup_del, vacuum_count, autovacuum_count
FROM information_schema.tables, pg_stat_all_tables stat
WHERE table_schema = 'public' and stat.schemaname = table_schema and stat.relname = table_name and stat.n_dead_tup>=0
ORDER BY
dead_percent, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') desc
limit 100;
table_full_name | size | dead_percent | last_autovacuum | last_vacuum | n_dead_tup | n_live_tup | n_tup_ins | n_tup_upd
| n_tup_del | vacuum_count | autovacuum_count
-----------------+-------+--------------+-------------------------------+-------------+------------+------------+-----------+-----------
+-----------+--------------+------------------
public.t1 | 88 kB | 31.25 | 2023-03-12 01:23:11.559591+00 | | 250 | 800 | 104749 | 0
| 91198 | 0 | 5
(1 row)
再过一小段时间，查一次：
会发现n_dead_tup变为0。
在实际的生产环境中，可能autovacuum_vacuum_scale_factor的值0.2太大了。而autovacuum_vacuum_threshold的值50，又显得没什么意义。
针对一些表，可以做一些定制，尤其是那些大表。
比如，干脆让scale_factor为0，而autovacuum_vacuum_threshold的值超过200000
绝对数量就让它vacuum.
同时还可以对cost_delay和cost_limit做调整。
ALTER table bigtable set (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold=200000);
ALTER table bigtable set (autovacuum_vacuum_cost_delay = 1, autovacuum_vacuum_cost_limit=2000);
6 AutoVacuum无法回收空间的解决方案
对于一些空间始张无法回收的表，有的是使用相对比较安全的: pg_repack直接进行回收，
它的最终效果基本上与vacuum full类似，但不会加上排它锁，阻止业务运行。在某些
环境中，这个pg_repack无法使用，得想想其它的办法。
很多表既然是要定期清除数据，一般都有清除数据的字段特征，这个时候，可以将表进行
分区。到了一定的时候，把要删除的数据从某个分区里彻底truncate掉就行了，而且非
常高效。truncate式的回收是彻底的重新生成数据文件，对应的表文件所占空间完全释放。
CREATE TABLE BIG (
ID INT8 NOT NULL,
COL2 VARCHAR(64) NULL,
CONTENT VARCHAR(255) NULL,
TS TIMESTAMP NOT NULL,
) PARTITION BY LIST (MOD(FLOOR(EXTRACT(EPOCH FROM TS)/3600/24)::int, 8));
CREATE INDEX idx_big_ts ON BIG(TS);
CREATE TABLE BIG_P0 PARTITION OF BIG FOR VALUES IN (0);
CREATE TABLE BIG_P1 PARTITION OF BIG FOR VALUES IN (1);
CREATE TABLE BIG_P2 PARTITION OF BIG FOR VALUES IN (2);
CREATE TABLE BIG_P3 PARTITION OF BIG FOR VALUES IN (3);
CREATE TABLE BIG_P4 PARTITION OF BIG FOR VALUES IN (4);
CREATE TABLE BIG_P5 PARTITION OF BIG FOR VALUES IN (5);
CREATE TABLE BIG_P6 PARTITION OF BIG FOR VALUES IN (6);
CREATE TABLE BIG_P7 PARTITION OF BIG FOR VALUES IN (7);
上边的示例则是可以以8天为一个时钟周期去存储数据，然后如果每次都是只存7天的
数据，总是可以将最末一天的数据全部truncate掉，空间完全释放，待删除的表名是
可以计算出来的。
在实际的环境中，我们还遇到一张表数据，全部delete掉以后，它居然还没有长期
没被回收掉(那就不正常了)。利用分区表以及数据被清理的基本特征，在很多情况下是可以做到数据
空间的有效回收的。
对于高频U(update)和D(delete)操作的表，可以设置set fillfactor，以给它
们预留空间，一方面提供较快的IO，另一方面，也能某种程度上缓解膨胀的速度。
postgres=# alter table t1 set (fillfactor=75);
ALTER TABLE
6 ---END---
其实，空间肿胀，大部分数据库系统都存在。各有各的解决或缓解的方案。合理的
膨胀不是问题，就怕是出乎预期的急剧膨胀，那就是事故了。
原文作者：数据库杂记；如有侵权，请联系管理员删除。
上一篇：
内存池：如何提升内存分配的效率
下一篇：
《太极拳论》注解
请登录后发表评论
登录
全部评论
最新文章
- PostgreSQL Internals之路 Part-II 第10章 WAL日志
- 从Oracle过渡到PostgreSQL: 理解模式的概念
- PostgreSQL是Oracle的一个可行的替代方案——原因如下
- PostgreSQL Internals之路Part II: Buffer Cache和WAL第9章 Buffer Cache
- 天纵奇才 - "林诗栋"
- 没有PostgreSQL样例数据库,不用担心, 帮你搞定！
- 推荐几本数据库技术图书
- 由浅入深高可用：HAProxy + PostgreSQL配置逐步详解(二)
- PostgreSQL Internals之路 Part-I 第7,8章 冻结、重建表和索引
- 乒坛最新消息引发巨震 - 世界第一王楚钦惨败林诗栋，王曼昱0-4遭横扫，还有孙颖莎4-2稳胜张本美和
