PostgreSQL中的数据膨胀由来及对策

PostgreSQL是一款MVCC数据库，它的实现机制不同于Oracle和MySQL中的MVCC实现。PG中的实现机制好处是更新和删除效率高，速度快; 负面的效果是需要定期进行数据清理，不然会有数据大量的数据膨胀。

原理

create extension pageinspect;
create table person(name varchar(64), age int);
insert into person values('andrew', 22);
select xmin, xmax, cmin, cmax, * from person;

当插入一条记录到PostgreSQL表中，系统字段xmax为0, 如果对应的记录被删除或修改，那么该字段为非0,写入的是执行该操作的transaction id, 借助于pageinspect模块，我们可以看到物理页中的真正内容。

create extension if not exists pageinspect;
SELECT t_xmin, t_xmax, tuple_data_split('scott.employee'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('scott.employee', 0));

借助于pageinspect, 可以做一个简单试验，看修改记录后，表的物理页中真正的记录形式如何。

mydb=# insert into person values('andrew', 24);
INSERT 0 1
mydb=# SELECT t_xmin, t_xmax, tuple_data_split('person'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('person', 0));
 t_xmin  | t_xmax |          tuple_data_split           
---------+--------+-------------------------------------
 7397512 |      0 | {"\\x0f616e64726577","\\x18000000"}
(1 行记录)

mydb=# update person set age = 25 where name = 'andrew';
UPDATE 1
mydb=# SELECT t_xmin, t_xmax, tuple_data_split('person'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('person', 0));
 t_xmin  | t_xmax  |          tuple_data_split           
---------+---------+-------------------------------------
 7397512 | 7397513 | {"\\x0f616e64726577","\\x18000000"}
 7397513 |       0 | {"\\x0f616e64726577","\\x19000000"}
(2 行记录)

输出结果表明，update操作不会在原有记录上进行修改，而是将原有记录置为无效(xmax设置为非零值)，然后重新写入一条全新记录。

监控

查看用户表中垃圾记录的数量，并计算和有效记录的比值。

select relname, n_live_tup, n_dead_tup, round(n_dead_tup*1.0/n_live_tup, 2) as dead_ratio from pg_stat_user_tables where n_live_tup > 0

mydb-# ;
       relname        | n_live_tup | n_dead_tup | dead_ratio 
----------------------+------------+------------+------------
 db_sync_cfg_database |          1 |          0 |       0.00
 db_sync_cfg_table    |          3 |          1 |       0.33
(2 行记录)

调优

触发autovacuum的条件

pg_stat_user_tables.n_dead_tup > (threshold + pg_class.reltuples * scale_factor)

为了尽早触发autovacuum, 可以针对表级别，修改autovacuum配置

alter table demo set (autovacuum_vacuum_threshold_size=0);
alter table demo set (autovacuum_vacuum_scale_factor=0.02);

autovacuum_vacuum_cost_limit autovacuum会带来额外的i/o开销，提升系统负载，对数据库系统的稳定性带来潜在影响，所以PostgreSQL针对autovacuum是有相应的限流设置, 相关的参数有两个

autovacuum_vacuum_cost_delay
autovacuum_vacuum_cost_limit

一般不建议更改 autovacuum_vacuum_cost_delay, 可以把默认的 autovacuum_vacuum_cost_limit 更改为大一点的值，允许触发和执行更多的autovacuum活动。