# 步骤1：修改全局配置
ALTER SYSTEM SET autovacuum = ‘on’;
ALTER SYSTEM SET autovacuum_max_workers = ‘4’;
ALTER SYSTEM SET autovacuum_naptime = ‘5min’;
ALTER SYSTEM SET autovacuum_vacuum_threshold = ’50’;
ALTER SYSTEM SET autovacuum_analyze_threshold = ’50’;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = ‘0.05’;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = ‘0.025’;
ALTER SYSTEM SET autovacuum_freeze_max_age = ‘150000000’;
SELECT pg_reload_conf();

# 步骤2：表级配置
ALTER TABLE fgedu_fgedus SET (
autovacuum_enabled = true,
autovacuum_vacuum_threshold = 50,
autovacuum_analyze_threshold = 50,
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.025,
autovacuum_freeze_max_age = 150000000
);

# 步骤3：验证配置
SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;

# 结果示例
autovacuum
————
on

autovacuum_max_workers
———————-
4

autovacuum_naptime
——————-
5min

# 步骤1：创建测试表
CREATE TABLE fgedu_mvcc_test (
id SERIAL PRIMARY KEY,
value INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 步骤2：插入测试数据
INSERT INTO fgedu_mvcc_test (value) SELECT generate_series(1, 100000);

# 步骤3：执行并发操作
— 事务1：读取数据
BEGIN;
SELECT * FROM fgedu_mvcc_test WHERE id = 1;
— 等待
PERFORM pg_sleep(5);
SELECT * FROM fgedu_mvcc_test WHERE id = 1;
COMMIT;

— 事务2：更新数据
BEGIN;
UPDATE fgedu_mvcc_test SET value = value + 1 WHERE id = 1;
COMMIT;

# 步骤4：查看结果
— 事务1第一次查询
value
—–
1

— 事务1第二次查询
value
—–
1 — 可重复读隔离级别

— 事务2执行后
value
—–
2

# 策略1：减少长事务
– 缩短事务长度
– 避免长时间打开事务
– 设置idle_in_transaction_session_timeout

# 策略2：合理使用隔离级别
– 读已提交：大多数场景
– 可重复读：需要一致性读取的场景
– 可串行化：需要最高一致性的场景

# 策略3：批量操作优化
– 分批处理数据
– 减少单次操作的锁范围

# 示例：事务管理策略
— 设置空闲事务超时
ALTER SYSTEM SET idle_in_transaction_session_timeout = ‘60000’;

— 合理使用隔离级别
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
— 执行操作
COMMIT;

— 分批处理数据
DO $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT id FROM fgedu_fgedus WHERE updated_at < '2024-01-01' LIMIT 1000 LOOP UPDATE fgedu_fgedus SET updated_at = NOW() WHERE id = r.id; COMMIT; BEGIN; END LOOP; END $$;

# 策略1：定期执行VACUUM
– 执行VACUUM ANALYZE
– 执行VACUUM FULL（必要时）
– 执行VACUUM FREEZE

# 策略2：优化自动清理
– 调整autovacuum参数
– 表级autovacuum配置
– 监控自动清理执行情况

# 策略3：监控冻结年龄
– 定期检查冻结年龄
– 执行VACUUM FREEZE（当冻结年龄接近阈值时）

# 示例：清理策略
— 定期执行VACUUM
VACUUM ANALYZE fgedu_fgedus;

— 执行VACUUM FREEZE
VACUUM FREEZE fgedu_fgedus;

— 监控冻结年龄
SELECT
relname,
age(relfrozenxid)
FROM pg_class
WHERE relkind = ‘r’ AND relname LIKE ‘fgedu_%’;

# 共享内存
buffer_pool_size = 8GB # 共享缓冲区大小

# 工作内存
work_mem = 8MB # 每个操作的工作内存

# 维护工作内存
maintenance_work_mem = 1GB # 维护操作的工作内存

# 示例：内存调优
ALTER SYSTEM SET shared_buffers = ‘8GB’;
ALTER SYSTEM SET work_mem = ‘8MB’;
ALTER SYSTEM SET maintenance_work_mem = ‘1GB’;
SELECT pg_reload_conf();

# 填充因子
ALTER TABLE fgedu_fgedus SET (fillfactor = 80);

# 表空间
CREATE TABLESPACE fgedu_data LOCATION ‘/postgresql/fgedutbss/fgedu_data’;
ALTER TABLE fgedu_fgedus SET TABLESPACE fgedu_data;

# 分区表
CREATE TABLE fgedu_fgfgfgsales (
id SERIAL,
sale_date DATE NOT NULL,
amount NUMERIC(10,2) NOT NULL
) PARTITION BY RANGE (sale_date);

# 示例：存储调优
— 设置填充因子
ALTER TABLE fgedu_fgedus SET (fillfactor = 80);

— 创建分区表
CREATE TABLE fgedu_fgfgfgsales_2024_q1 PARTITION OF fgedu_fgfgfgsales
FOR VALUES FROM (‘2024-01-01’) TO (‘2024-04-01’);