PostgreSQL 11 新特性之分區(qū)索引

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄

        分區(qū)自動(dòng)索引
        分區(qū)表唯一約束

在 PostgreSQL 10 中，分區(qū)上的索引需要基于各個(gè)分區(qū)手動(dòng)創(chuàng)建，而不能基于分區(qū)的父表創(chuàng)建索引。PostgreSQL 11 可以基于分區(qū)表創(chuàng)建索引。分區(qū)表上的索引并不會(huì)創(chuàng)建一個(gè)物理上的索引，而是為每個(gè)分區(qū)上的索引創(chuàng)建一個(gè)模板。
分區(qū)自動(dòng)索引

如果在分區(qū)表上創(chuàng)建了一個(gè)索引，PostgreSQL 自動(dòng)為每個(gè)分區(qū)創(chuàng)建具有相同屬性的索引。

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2018 PARTITION OF measurement
    FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
CREATE TABLE measurement_y2019 PARTITION OF measurement
    FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');

CREATE INDEX idx_measurement_peaktemp ON measurement(peaktemp);

\d measurement
              Table "public.measurement"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 city_id   | integer |           | not null |
 logdate   | date    |           | not null |
 peaktemp  | integer |           |          |
 unitsales | integer |           |          |
Partition key: RANGE (logdate)
Indexes:
    "idx_measurement_peaktemp" btree (peaktemp)
Number of partitions: 2 (Use \d+ to list them.)

measurement 表上創(chuàng)建了一個(gè)索引 idx_measurement_peaktemp，因此該表上的分區(qū)也會(huì)自動(dòng)創(chuàng)建相應(yīng)的索引。

\d measurement_y2018
           Table "public.measurement_y2018"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 city_id   | integer |           | not null |
 logdate   | date    |           | not null |
 peaktemp  | integer |           |          |
 unitsales | integer |           |          |
Partition of: measurement FOR VALUES FROM ('2018-01-01') TO ('2019-01-01')
Indexes:
    "measurement_y2018_peaktemp_idx" btree (peaktemp)

自動(dòng)創(chuàng)建的索引，名稱按照 “{partition name}_{column name}_idx” 的模式定義。多個(gè)字段的復(fù)合索引使用下劃線（_）連接字段名稱。如果索引名稱已經(jīng)存在，在名稱的最后添加一個(gè)數(shù)字。如果名稱過(guò)長(zhǎng)，使用縮寫(xiě)。

隨后新增的分區(qū)或者通過(guò) ATTACH PARTITION 掛載的分區(qū)都會(huì)自動(dòng)創(chuàng)建相應(yīng)的索引。

CREATE TABLE measurement_y2020 (LIKE measurement);
ALTER TABLE measurement ATTACH PARTITION measurement_y2020
  FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

\d measurement_y2020
           Table "public.measurement_y2020"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 city_id   | integer |           | not null |
 logdate   | date    |           | not null |
 peaktemp  | integer |           |          |
 unitsales | integer |           |          |
Partition of: measurement FOR VALUES FROM ('2020-01-01') TO ('2021-01-01')
Indexes:
    "measurement_y2020_peaktemp_idx" btree (peaktemp)

    
自動(dòng)創(chuàng)建的索引不能單獨(dú)刪除，可以通過(guò)分區(qū)表統(tǒng)一刪除。

DROP INDEX measurement_y2020_peaktemp_idx;
ERROR:  cannot drop index measurement_y2020_peaktemp_idx because index idx_measurement_peaktemp requires it
HINT:  You can drop index idx_measurement_peaktemp instead.

DROP INDEX idx_measurement_peaktemp;

   
分區(qū)表唯一約束

對(duì)于 PostgreSQL 10，只能基于分區(qū)創(chuàng)建唯一約束（PRIMARY KEY 和 UNIQUE KEY），而不能針對(duì)分區(qū)的父表創(chuàng)建唯一約束。PostgreSQL 11 支持分區(qū)表上的唯一約束。

CREATE TABLE rtable(c1 INT, c2 VARCHAR(10)) PARTITION BY RANGE(c1);
ALTER TABLE rtable ADD CONSTRAINT pk_rtable PRIMARY KEY(c1);
\d rtable
                      Table "public.rtable"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | character varying(10) |           |          |
Partition key: RANGE (c1)
Indexes:
    "pk_rtable" PRIMARY KEY, btree (c1)
Number of partitions: 0

   
添加分區(qū)或者加載（ATTACH）分區(qū)時(shí)自動(dòng)創(chuàng)建相應(yīng)的主鍵：

CREATE TABLE rtable100 PARTITION OF rtable FOR VALUES FROM (1) TO (100);
\d rtable100
                    Table "public.rtable100"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | character varying(10) |           |          |
Partition of: rtable FOR VALUES FROM (1) TO (100)
Indexes:
    "rtable100_pkey" PRIMARY KEY, btree (c1)

    
如果在分區(qū)表上創(chuàng)建了唯一約束，無(wú)法再創(chuàng)建基于外部表（FOREIGN TABLE）的分區(qū)。因?yàn)闊o(wú)法為外部表創(chuàng)建唯一約束。

CREATE FOREIGN TABLE rtable200 PARTITION OF rtable FOR VALUES FROM (101) TO (200) SERVER remote1;
ERROR:  cannot create index on foreign table "rtable200"

 
主鍵約束或唯一約束必須包含分區(qū)字段。這樣才能確保整個(gè)分區(qū)表內(nèi)的唯一性，因?yàn)槊總€(gè)分區(qū)上的唯一約束只維護(hù)自身的唯一性。

CREATE TABLE rtable1(c1 INT, c2 VARCHAR(10)) PARTITION BY RANGE(c1);
ALTER TABLE rtable1 ADD CONSTRAINT pk_table1 PRIMARY KEY(c2);
ERROR:  insufficient columns in PRIMARY KEY constraint definition
DETAIL:  PRIMARY KEY constraint on table "rtable1" lacks column "c1" which is part of the partition key.

新的索引修改語(yǔ)句 ALTER INDEX ATTACH PARTITION 可以將分區(qū)上的已有索引掛載到分區(qū)表上的索引。