PostgreSQL 大宽表,全列索引,高并发合并写入(insert into on conflict, upsert, merge insert) - 实时adhoc query-阿里云开发者社区

开发者社区> 数据库> 正文

PostgreSQL 大宽表,全列索引,高并发合并写入(insert into on conflict, upsert, merge insert) - 实时adhoc query

简介: 标签 PostgreSQL , 全列索引 , 大宽表 , 写测试 , insert on conflict , upsert , merge insert , adhoc query 背景 OLAP系统中,adhoc query非常场景(任意维度查询分析)。 adhoc query,通常来说,可以加GIN倒排,或者每一列都加一个索引来实现。 《PostgreSQL 设计优化case

标签

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虚拟机

版权声明:如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developerteam@list.alibaba-inc.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章