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虛拟機