PostgreSQL 事务子系统的关键问题和应对方案--肖玲峰（最新）.pdf

1、PostgreSQL中文社区第13届PostgreSQL中国技术大会聚焦云端创新汇聚智慧共享肖玲峰PostgreSQL 事务子系统的关键问题和应对方案PostgreSQL中文社区事务 ID 分配场景事务块中错误回滚场景03表数据更新场景0102PostgreSQL中文社区目录contents01表数据更新场景PostgreSQL中文社区PostgreSQL中文社区表数据更新场景:用例CREATE TABLE t_large(id integer,name text);CREATE INDEX large_idx ON t_large(id);INSERT INTO t_large(id,nam

2、e)SELECT i,xxx FROM generate_series(1,1000000)AS s(i);SELECT pg_size_pretty(pg_table_size(t_large);pg_size_pretty-35 MBUPDATE t_large SET id=id+1;UPDATE t_large SET name=yyy;SELECT pg_size_pretty(pg_table_size(t_large);pg_size_pretty-104 MBPostgreSQL中文社区表数据更新场景：内部原理xminxmax200204xxxPage.xminxmax2002

3、05xxxxminxmax200205yyyxminxmax200204xxxPage.UPDATE1.单条数据会有多个版本2.每个事务都有一个事务 ID，它有点像数据库系统的运行时间3.每行都有两个默认的隐藏属性 xmin 和 xmax，这两个属性分别保存了创建和更改它们的事务 ID关键要点PostgreSQL中文社区表数据更新场景:思考1.数据更新需要占用原来 2 倍的磁盘空间2.增加了垃圾数据清理和空间回收的工作量3.基于索引扫描和全表扫描的查询性能都会受到影响问题影响1.调整垃圾清理的调度策略，如触发条件、清理节奏、并行度2.调整表结构，将经常更新的列移动到单独的表中3.合并更新，将多

4、次更新合并成单次更新应对方案PostgreSQL中文社区只用索引扫描场景:用例EXPLAIN(analyze,buffers,costs off)SELECT count(*)FROM t_large;QUERY PLAN-Index Only Scan using large_idx on t_large Heap Fetches:0UPDATE t_large SET name=dummy WHERE mod(id,100)=1;DELETE FROM t_large WHERE mod(id,100)=50;EXPLAIN(analyze,buffers,costs off)SELECT

5、 count(*)FROM t_large;QUERY PLAN-Index Only Scan using large_idx on t_large Heap Fetches:4500PostgreSQL中文社区只用索引扫描场景:思考1.只用索引的扫描，需要依赖可见性映射表，检查对应堆页面的可见性状态标志2.基于索引扫描的执行计划和查询性能不稳定问题影响1.参照数据更新场景的应对方案2.在批量更新完表数据后，对表执行 VACUUM ANALYZE应对方案02事务 ID 分配场景PostgreSQL中文社区PostgreSQL中文社区事务 ID 快速分配的场景:用例CREATE TABLE f

6、oo(i int PRIMARY KEY);CREATE OR REPLACE FUNCTION f_base(v int)RETURNS void AS$BEGININSERT INTO foo(i)VALUES(v);EXCEPTIONWHEN UNIQUE_VIOLATION THENRAISE WARNING Exception for value:%,v;END;$LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION f_loop(b int,e int)RETURNS