2000元阿里云代金券免费领取，2核4G云服务器仅664元/3年，新老用户都有优惠，立即抢购>>>
阿里云采购季（云主机223元/3年）活动入口：请点击进入>>>，
阿里云学生服务器（9.5元/月）购买入口：请点击进入>>>，
标签
PostgreSQL , 全列索引 , 大宽表 , 写测试 , insert on conflict , upsert , merge insert , adhoc query
背景
OLAP系统中，adhoc query非常场景（任意维度查询分析）。
adhoc query，通常来说，可以加GIN倒排，或者每一列都加一个索引来实现。
《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》
加索引和不加索引，实时的upsert(有则更新，无则写入)，性能差别如何呢？每列都有索引时是否可以支撑实时的合并写入呢？
测试
测试数据upsert合并写入
100个字段不含索引
do language plpgsql $$
declare
sql text := 'create table tbl_test1( id int primary key,';
begin
for i in 1..100 loop
sql := sql||' c'||i||' int default random()*10000000,';
end loop;
sql := rtrim(sql,',');
sql := sql || ')';
execute sql;
end;
$$;
vi test1.sql
\set id random(1,100000000)
\set c1 random(1,20000)
insert into tbl_test1 (id, c1) values (:id,:c1) on conflict(id) do update set c1=excluded.c1;
pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 32 -j 32 -T 120
transaction type: ./test1.sql
scaling factor: 1
query mode: prepared
number of clients: 32
number of threads: 32
duration: 120 s
number of transactions actually processed: 14433112
latency average = 0.266 ms
latency stddev = 3.919 ms
tps = 120275.287837 (including connections establishing)
tps = 120303.256409 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set id random(1,100000000)
0.000 \set c1 random(1,20000)
0.265 insert into tbl_test1 (id, c1) values (:id,:c1) on conflict(id) do update set c1=excluded.c1;
100个字段含索引
do language plpgsql $$
declare
sql text := 'create table tbl_test2( id int primary key,';
begin
for i in 1..100 loop
sql := sql||' c'||i||' int default random()*10000000,';
end loop;
sql := rtrim(sql,',');
sql := sql || ')';
execute sql;
for i in 1..100 loop
execute 'create index idx_tbl_test2_'||i||' on tbl_test2 (c'||i||')';
end loop;
end;
$$;
vi test2.sql
\set id random(1,100000000)
\set c1 random(1,20000)
insert into tbl_test2 (id, c1) values (:id,:c1) on conflict(id) do update set c1=excluded.c1;
pgbench -M prepared -n -r -P 1 -f ./test2.sql -c 32 -j 32 -T 120
分区表大宽表，全索引测试
do language plpgsql $$
declare
sql text := 'create table tbl_test3(sid int, id int,';
begin
for i in 1..100 loop
sql := sql||' c'||i||' int default random()*10000000,';
end loop;
sql := rtrim(sql,',');
sql := sql || ') partition by list (sid)';
execute sql;
end;
$$;
创建索引和唯一约束
alter table tbl_test3 add constraint uk_tbl_test3 unique (sid,id);
do language plpgsql $$
declare
begin
for i in 1..100 loop
execute 'create index idx_tbl_test3_'||i||' on tbl_test3 (c'||i||')';
end loop;
end;
$$;
创建分区
do language plpgsql $$
declare
begin
for sid in 1..60 loop
execute format('create table tbl_test3_%s partition of tbl_test3 for values in (%s)', sid, sid);
end loop;
end;
$$;
合并写的语法
insert into tbl_test3 values (1,1)
on conflict (sid,id)
do update
set c1=excluded.c1;
create or replace function upsert_tbl_test3 (v_sid int, v_id int) returns void as $$
declare
begin
execute format('execute p_%s (%s, %s)', v_sid, v_sid, v_id);
exception when others then
execute format('prepare p_%s (int,int) as insert into tbl_test3_%s(sid,id) values ($1,$2) on conflict (sid,id) do update set c1=excluded.c1', v_sid, v_sid);
execute format('execute p_%s (%s, %s)', v_sid, v_sid, v_id);
end;
$$ language plpgsql strict;
vi test.sql
\set id random(1,2000000000)
\set sid random(1,60)
select upsert_tbl_test3(:sid,:id);
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 28 -j 28 -T 120
优化
《PostgreSQL 分区表如何支持多列唯一 - 枚举、hash哈希 分区, 多列唯一, insert into on conflict, update, upsert, merge insert》
性能对比
| 场景 | 合并写入QPS |
|---|---|
| 1个索引 | 120303 |
| 101个索引 | 19485 |
小结：
1、瓶颈分析，多个索引时，注意瓶颈在索引的RT上面，为提升性能，可以使用分区表，将RT打平，提升整体写入吞吐。
2、频繁更新可能导致索引膨胀，如果发现索引膨胀严重，在空闲期间需要对索引进行rebuild操作（是一样concurrently并发创建，不影响DML）。
《PostgreSQL CREATE INDEX CONCURRENTLY 的原理以及哪些操作可能堵塞索引的创建》
《PostgreSQL 垃圾回收原理以及如何预防膨胀 - How to prevent object bloat in PostgreSQL》
参考
《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》
《PostgreSQL 快速给指定表每个字段创建索引 - 2 (近乎完美)》
《PostgreSQL 快速给指定表每个字段创建索引 - 1 (不够完美)》
免费领取阿里云RDS PostgreSQL实例、ECS虚拟机
