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的更新/删除元组阈值
autovacuChina编程um_analyze_threshold = 50   -- 触发analyze的更新/删除元组阈值
autovacuum_vacuum_scale_factor = 0.2-- 表大小的缩放因子

-- 对大表调整autovacuum参数
ALTER TABLE large_ta编程ble SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 10000
);

-- 查看表膨胀情况
SELECT 
  schemaname, relnamandroide,
  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;