內(nèi)置分區(qū)表

在 PostgreSQL10 以前，都是通過表的繼承和觸發(fā)器來完成分區(qū)功能，在 PostgreSQL10 中提供對內(nèi)置分區(qū)表的支持，目前只支持范圍分區(qū)表和列表分區(qū)表。

范圍分區(qū)：

范圍分區(qū)，適用于針對某一字段數(shù)據(jù)的一定范圍(比如時間)進(jìn)行分區(qū)：

• 創(chuàng)建表：

-- 父表
create table public.user_per(
user_id int4,
user_name text,
create_time date
) partition by range (create_time);
-- 分區(qū)表
create table 
public.user_per_201501 partition of user_per 
  for values from (minvalue) to ('2015-01-01');
-- 分區(qū)表
create table 
public.user_per_201601 partition of user_per 
  for values from ('2015-01-01') to ('2016-01-01');
-- 分區(qū)表
create table public.user_per_201701 
partition of user_per 
  for values from ('2016-01-01') to ('2017-01-01');

• 插入數(shù)據(jù)

insert into 
user_per(user_id, user_name, create_time) 
values 
     (1001, 'aladdin', '2000-10-15'), 
     (1002, 'bilib', '2016-04-01');

• 查詢數(shù)據(jù)

select * from user_per; -- + where create_time ...
-- 驗(yàn)證是否已被分區(qū)
select * from user_per_201501;
select * from user_per_201701;

• 刪除分區(qū)

-- 將數(shù)據(jù)一起刪除的暴力方法
drop table user_per_201501;
-- 溫和的方式是將分區(qū)與父分區(qū)進(jìn)行解綁，分區(qū)和數(shù)據(jù)都沒有被刪除，只是對 user_per 不在適用
alter table user_per detach partition user_per_201701;
-- 將分區(qū)重新綁定到父分區(qū)
alter table 
user_per attach partition user_per_201701 
  for values from ('2016-01-01') to ('2017-01-01');

• 創(chuàng)建索引

-- 分區(qū)索引
create index 
on user_per_201501 
  using btree(user_name);

列表分區(qū)：

列表分區(qū)，是針對字段數(shù)據(jù)的某幾個值(比如地區(qū))進(jìn)行分區(qū)：

• 創(chuàng)建表：

-- 父表
create table user_per(
user_id int4,
user_province text
) partition by list(user_province);
-- 單值分區(qū)表
create table 
user_per_ln partition of user_per 
  for values in ('遼寧');
-- 多值分區(qū)表
create table 
user_per_hz partition of user_per 
  for values in ('河南', '湖北', '湖南');

• 插入數(shù)據(jù)

insert into
user_per(user_id, user_province) 
VALUES 
       (1001, '遼寧'), 
       (1002, '河南');

• 其他操作

  參考范圍分區(qū)...

關(guān)于 constraint_exclusion 參數(shù)：

內(nèi)置分區(qū)表的查詢計劃受 constraint_exclusion 參數(shù)的影響，不啟動 constraint_exclusion，分區(qū)表的查詢計劃將會對所有分區(qū)都進(jìn)行查詢(全表掃描)，失去了分區(qū)剪枝的功能。

如果啟動 constraint_exclusion，對所有表都會啟動，將會增大服務(wù)器壓力，一般(PG 實(shí)戰(zhàn) / 網(wǎng)上)都推薦將 constraint_exclusion 參數(shù)設(shè)置為 partition：

set constraint_exclusion = partiton;
-- 其它配置
set constraint_exclusion = on;
set constraint_exclusion = off;

內(nèi)置分區(qū)表的性能

在 《PostgreSQL 實(shí)戰(zhàn)》中，作者對啟動內(nèi)置分區(qū)表和不啟動分區(qū)進(jìn)行了測試。測試結(jié)果是，啟動內(nèi)置分區(qū)表的性能比沒有提高，相比較不啟動分區(qū)性能好像還有點(diǎn)下降。

但是不啟動分區(qū)，無法直接對類似于 "user_per_ln" 這樣的子表進(jìn)行查詢，而是需要全表掃描數(shù)據(jù)，各有利弊而已，真正使用需要根據(jù)實(shí)際情況進(jìn)行衡量。

PostgreSQL 的繼承

PostgreSQL 傳統(tǒng)的 (10版本之前) 都是通過表繼承+觸發(fā)器等操作來完成的，雖然 10 版本字后有了改變，但是對于沒有接觸過表繼承相關(guān)概念的人來說，很難理解這個過程的實(shí)現(xiàn)細(xì)節(jié)：

-- 父表
create table tbl_user(
  id int4,
  name text
);
-- 子表
create table tbl_user_ln (tags text[]) inherits (tbl_user); -- 繼承 tbl_user 的字段，也可以新增字段

插入數(shù)據(jù)：

insert into 
  tbl_user_ln (id, name, tags) 
  values 
         (1001, 'aladdin', '{"Smoking", "Drinking"}');

查詢：

-- 通過查詢父表，可以查到數(shù)據(jù)
select * from tbl_user;
-- 也可以通過查詢字表查詢數(shù)據(jù)
select * from tbl_user_ln;