PostgreSQL中MVCC 机制的实现

select xmin, xmax, cmin, cmax, ctid, * from your_table;

-- 事务1
begin;
insert into test values (1, 'data');
-- 此时xmin=当前事务id, xmax=0
commit;

-- 事务2
begin;
update test set value = 'new' where id = 1;
-- 原行xmax设置为事务2的id
-- 新行xmin=事务2的id, xmax=0
commit;

-- 事务3
begin;
delete from test where id = 1;
-- 行xmax设置为事务3的id
commit;

-- 查看当前事务快照
select pg_current_snapshot();
-- 输出示例: 100:100:
-- 格式为 xmin:xmax:xip_list

-- 常规vacuum(不锁表)
vacuum [verbose] [analyze] table_name;

-- 全量vacuum(需要锁)
vacuum full [verbose] table_name;

-- 查看自动vacuum设置
select name, setting from pg_settings where name like 'autovacuum%';

-- 重要参数
autovacuum = on                     -- 是否启用
autovacuum_vacuum_threshold = 50    -- 触发vacuum的更新/删除元组阈值
autovacuum_analyze_threshold = 50   -- 触发analyze的更新/删除元组阈值
autovacuum_vacuum_scale_factor = 0.2-- 表大小的缩放因子

-- 对大表调整autovacuum参数
alter table large_table set (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 10000
);

-- 查看表膨胀情况
select 
  schemaname, relname,
  pg_size_pretty(pg_relation_size(relid)) as size,
  n_dead_tup,
  n_live_tup
from pg_stat_user_tables
order by n_dead_tup desc;

-- 对大表定期手动vacuum
vacuum (verbose, analyze) large_table;

-- 在低峰期执行vacuum full
vacuum full verbose table_name;