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

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

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

简介: 标签 PostgreSQL , 分区表 , native partition , 唯一 , 非分区键唯一 , 组合唯一 , insert into on conflict , upsert , merge insert 背景 PG 11开始支持HASH分区,10的分区如果要支持hash分区,可以通过枚举绕道实现。 《PostgreSQL 9.x, 10, 11 hash分区表 用法举例

标签

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;  
$$;  

分区表支持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;  

压测

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分区表 用法举例》

 

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机

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

分享:
+ 订阅

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

其他文章