PostgreSQL 分区表如何支持多列唯一约束 - 枚举、hash哈希 分区, 多列唯一, insert into on conflict, update, upsert, merge insert
digoal
2019-01-11
PostgreSQL , 分区表 , native partition , 唯一 , 非分区键唯一 , 组合唯一 , insert into on conflict , upsert , merge insert
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));
对于这个业务逻辑，分区表应该如何建立？
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;
$$;
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;
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
如果where条件只有id，没有带sid，不同SID中的同一个ID都会被查询或更新条件捕获。
《PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量》
《PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量》
《PostgreSQL 9.x, 10, 11 hash分区表 用法举例》
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
