PostgreSQL 分区表如何支持多列唯一约束 - 枚举、hash哈希 分区, 多列唯一, insert into on conflict, update, upsert, merge insert

标签

PostgreSQL , 分区表 , native partition , 唯一 , 非分区键唯一 , 组合唯一 , insert into on conflict , upsert , merge insert

https://github.com/digoal/blog/blob/master/201901/20190111_01.md#%E8%83%8C%E6%99%AF 背景

PG 11开始支持HASH分区，10的分区如果要支持hash分区，可以通过枚举绕道实现。

《PostgreSQL 9.x, 10, 11 hash分区表 用法举例》

分区表的唯一、主键约束，必须与分区字段一致（或包含分区字段），才能保证全局的唯一性，否则无法实现全局唯一的约束。

例如

create table test (id int8 primary key, gid int, info text, crt_time timestamp);  
           

这个表，经常要按gid查询，想按gid来进行分区，如何实现？

从业务逻辑上分析，id实际上并不需要全局唯一，只要保证gid与id组合唯一即可。

例如GID表示分销商，分销商内ID唯一，业务逻辑是这样的。

create table test (id int8, gid int, info text, crt_time timestamp,   
primary key(gid,id));  
           

对于这个业务逻辑，分区表应该如何建立？

https://github.com/digoal/blog/blob/master/201901/20190111_01.md#%E4%BE%8B%E5%AD%90 例子

1、创建枚举分区

create table p (sid int not null, id int8 not null, info text, crt_time timestamp)   
partition by list (sid);  
           

2、创建索引和唯一约束

alter table p add constraint uk unique (id,sid);  -- 建议把ID条件放前面，因为SID的条件直接命中分区，而ID条件无法命中分区，当只提供id条件查询时，这个索引也能被使用到  
  
create index idx_p_crt_time on p(crt_time);  
           

NOTE: 唯一约束必须包含分区键，如果不包含分区键，则无法创建唯一约束。

postgres=# create unique index xx on p(id);  
ERROR:  insufficient columns in UNIQUE constraint definition  
DETAIL:  UNIQUE constraint on table "p" lacks column "sid" which is part of the partition key.  
           

3、创建LIST分区

do language plpgsql $$  
declare  
begin  
  for sid in 1..60 loop  
    execute format('create table p%s partition of p for values in (%s)', sid, sid);    
  end loop;  
end;  
$$;  
           

https://github.com/digoal/blog/blob/master/201901/20190111_01.md#%E5%88%86%E5%8C%BA%E8%A1%A8%E6%94%AF%E6%8C%81insert-into-on-conflict%E8%AF%AD%E6%B3%95 分区表支持insert into on conflict语法

upsert, merge insert, insert on conflict 合并写的语法

insert into p values (1,1,'test',now())   
on conflict (sid,id)   
do update   
set info=excluded.info, crt_time=excluded.crt_time;  
           

https://github.com/digoal/blog/blob/master/201901/20190111_01.md#%E5%8E%8B%E6%B5%8B 压测

1、单条合并写测试

export PGHOST=数据库主机名  
export PGPORT=端口  
export PGDATABASE=  
export PGUSER=单条合并写测试  
export PGPASSWORD=  
           

vi test.sql  
\set id random(1,2000000000)  
\set sid random(1,60)  
insert into p (sid,id,info,crt_time) values (:sid,:id,md5(random()::text),now()) on conflict (sid,id) do update set info=excluded.info, crt_time=excluded.crt_time;  
           

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 1200  
           

2、多条（4条）合并写测试

vi test.sql  
\set id random(1,2000000000)  
\set sid random(1,60)  
insert into p (sid,id,info,crt_time) values (:sid,:id+1,md5(random()::text),now()),(:sid,:id+2,md5(random()::text),now()),(:sid,:id+3,md5(random()::text),now()),(:sid,:id+4,md5(random()::text),now()) on conflict (sid,id) do update set info=excluded.info, crt_time=excluded.crt_time;  
  
  
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 1200  
           

16核，多条合并写 16.8万/s

https://github.com/digoal/blog/blob/master/201901/20190111_01.md#%E6%B3%A8%E6%84%8F 注意

如果where条件只有id，没有带sid，不同SID中的同一个ID都会被查询或更新条件捕获。

https://github.com/digoal/blog/blob/master/201901/20190111_01.md#%E5%88%86%E5%8C%BA%E8%A1%A8%E7%9A%84%E5%86%99%E5%85%A5%E6%80%A7%E8%83%BD%E6%8F%90%E5%8D%87%E6%96%B9%E6%B3%95 分区表的写入性能提升方法

《PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量》

https://github.com/digoal/blog/blob/master/201901/20190111_01.md#%E5%8F%82%E8%80%83 参考

https://github.com/digoal/blog/blob/master/201901/20190111_01.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免费领取阿里云RDS PostgreSQL实例、ECS虚拟机