PG--分区表

PG-分区表的使用

PostgreSQL 内部通过表继承来实现分区表，10.X之前的版本只能拿通过表继承来实现分区表，10.X开始提供了相应的DDL语句直接创建分区表，称为声明式分区。

一、先说一下表继承的用法。表继承是PostgreSQL特有的。

那既然提到继承，那肯定就是有父子关系。

举例：

假设有一张人员表 persons

CREATE TABLE persons (

name varchar(20),

age int,

sex boolean

);

再加一个学生表students，学生表比人员表多一个班级编号的字段class_no .

CREATE TABLE students (

class_no int

)INHERITS (persons);

查看一下两个表的表结构信息。

Persons为父表，students为子表。

向子表中添加两行数据

Insert into students(name,age,sex,class_No) values('张三',12,true,1);

Insert into students(name,age,sex,class_No) values('李四',13,false,2);

查询一下子表和父表的数据

说明通过查询父表是可以查询到插入到子表中的数据。

再向父表插入一条数据。

Insert into persons(name,age,sex) values('王五',14,false);

查询父表和子表中的数据

说明向父表中插入数据，只能在父表中能查询到，在子表中是查询不到的。

那如果只想查询父表中独有的数据，不包含的子表的数据，只需在表名前加only关键字。

父表增加约束，然后查看父表与子表的表结构

alter table persons add constraint persons_check_age check (age>0 and age<150);

子表会继承父表的检查约束。

父表创建一个唯一索引，一个普通索引。然后查看父表与子表的表结构

create index idx_age on persons(age);

create unique index idx_name on persons(name);

子表并不会继承父表的普通索引和唯一索引，也说明子表不继承父表的唯一约束，此外子表也不会继承父表的主键和外键约束。

还有子表还可以继承多个父表，它将拥有所有父表字段的总和，当然也包含自己本身定义的字段，这样相同的字段会出现融合，因为相同的字段名在一个表只能出现一个，这就要求融合的字段必须有相同的数据类型，否则就会报错。用起来比较复杂，不知道实际业务中是否会用到了这一特性，想想都是给自己在制造麻烦，但是如果设计者如果心细且使用合理的话，倒是可以避免相同的字段在不同表中定义的类型不一致的问题，因为这通常是造成表关联时发生隐式转换的原因所在。

总结一下表继承的特点：

1. 查询父表可以查询到子表的数据，如果向只查询父表自己的数据，需要在父表的表名加only关键字。
2. 子表可以继承父表的检查和非空约束，修改父表的检查和非空约束，子表也会随之被修改。
3. 子表不会继承父表的唯一、主键、外键约束，也不会继承索引。
4. 子表可以继承多个父表，相同字段会融合，所以要求相同字段类型要一致，否则会报错。

二、再说一下如何通过表继承实现的分区表。

建分区表的步骤：

1. 创建父表，父表不存储数据，父表上不需要创建索引、或主键、唯一约束，因为这些子表继承不了。非空约束或检查约束如果需要约束所有的分区，则可以建。
2. 创建子表，从父表继承，不添加任何字段，将这些子表称为分区，其实就是普通的表。
3. 给每个分区表增加约束，定义每个分区允许的键值，也就是分区键在每个分区上的条件。
4. 给每个分区创建索引，有必要时创建主键唯一键。
5. 定义一个规则或触发器，将对主表的数据插入重定向到合适的分区表中。一般是选择触发器的，使用规则是有缺点的，比如插入数据是规则之外的则会把数据插入到父表中，比如使用COPY插入数据不触发“规则”等。
6. 参数constraint_exclusion 要设置为partition，这也是9.X版本以后的默认值，这样可以做到分区裁剪，即根据分区键条件会排除扫描不必要的分区。设置为off，则会对全分区进行扫描。

postgres=# show constraint_exclusion;

constraint_exclusion

----------------------

partition

(1 row)

创建一个range分区表举例：

比如创建一个销售明细表，按销售日期sale_date作为分区键，每月一个分区。

如下为父表：

CREATE TABLE sales_detail (

product_id int not null,

price numeric(12,2),

amount int not null,

sale_date date not null,

buyer varchar(40),

buyer_contact text);

通过子表创建2024-01-01~2024-12-31 一年的分区。

CREATE TABLE sales_detail_202401 (CHECK (sale_date >=DATE '2024-01-01' AND sale_date < DATE '2014-02-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202402 (CHECK (sale_date >=DATE '2024-02-01' AND sale_date < DATE '2014-03-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202403 (CHECK (sale_date >=DATE '2024-03-01' AND sale_date < DATE '2014-04-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202404 (CHECK (sale_date >=DATE '2024-04-01' AND sale_date < DATE '2014-05-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202405 (CHECK (sale_date >=DATE '2024-05-01' AND sale_date < DATE '2014-06-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202406 (CHECK (sale_date >=DATE '2024-06-01' AND sale_date < DATE '2014-07-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202407 (CHECK (sale_date >=DATE '2024-07-01' AND sale_date < DATE '2014-08-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202408 (CHECK (sale_date >=DATE '2024-08-01' AND sale_date < DATE '2014-09-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202409 (CHECK (sale_date >=DATE '2024-09-01' AND sale_date < DATE '2014-10-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202410 (CHECK (sale_date >=DATE '2024-10-01' AND sale_date < DATE '2014-11-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202411 (CHECK (sale_date >=DATE '2024-11-01' AND sale_date < DATE '2014-12-01' ) )

INHERITS (sales_detail);

CREATE TABLE sales_detail_202412 (CHECK (sale_date >=DATE '2024-12-01' AND sale_date < DATE '2015-01-01' ) )

INHERITS (sales_detail);

给各分区创建索引

CREATE INDEX sale_detail_202401_sale_date ON sales_detail_202401(sale_date);

CREATE INDEX sale_detail_202402_sale_date ON sales_detail_202402(sale_date);

CREATE INDEX sale_detail_202403_sale_date ON sales_detail_202403(sale_date);

CREATE INDEX sale_detail_202404_sale_date ON sales_detail_202404(sale_date);

CREATE INDEX sale_detail_202405_sale_date ON sales_detail_202405(sale_date);

CREATE INDEX sale_detail_202406_sale_date ON sales_detail_202406(sale_date);

CREATE INDEX sale_detail_202407_sale_date ON sales_detail_202407(sale_date);

CREATE INDEX sale_detail_202408_sale_date ON sales_detail_202408(sale_date);

CREATE INDEX sale_detail_202409_sale_date ON sales_detail_202409(sale_date);

CREATE INDEX sale_detail_202410_sale_date ON sales_detail_202410(sale_date);

CREATE INDEX sale_detail_202411_sale_date ON sales_detail_202411(sale_date);

CREATE INDEX sale_detail_202412_sale_date ON sales_detail_202412(sale_date);

创建触发器，实现插入父表的数据重定向到合适的分区中。

CREATE OR REPLACE FUNCTION sale_detail_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.sale_date >= DATE '2024-01-01' AND NEW. sale_date < DATE '2024-02-01' ) THEN

INSERT INTO sales_detail_202401 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2024-02-01' AND NEW.sale_date < DATE '2024-03-01' ) THEN

INSERT INTO sales_detail_202402 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2024-03-01' AND NEW.sale_date < DATE '2024-04-01' ) THEN

INSERT INTO sales_detail_202403 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2024-04-01' AND NEW.sale_date < DATE '2024-05-01' ) THEN

INSERT INTO sales_detail_202404 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2024-05-01' AND NEW.sale_date < DATE '2024-06-01' ) THEN

INSERT INTO sales_detail_202405 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2024-06-01' AND NEW.sale_date < DATE '2024-07-01' ) THEN

INSERT INTO sales_detail_202406 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2024-07-01' AND NEW.sale_date < DATE '2024-08-01' ) THEN

INSERT INTO sales_detail_202407 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2024-08-01' AND NEW.sale_date < DATE '2024-09-01' ) THEN

INSERT INTO sales_detail_202408 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2024-09-01' AND NEW.sale_date < DATE '2024-10-01' ) THEN

INSERT INTO sales_detail_202409 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2024-10-01' AND NEW.sale_date < DATE '2024-11-01' ) THEN

INSERT INTO sales_detail_202410 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2024-11-01' AND NEW.sale_date < DATE '2024-12-01' ) THEN

INSERT INTO sales_detail_202411 VALUES (NEW.*);

ELSIF ( NEW.sale_date >= DATE '2014-12-01' AND NEW.sale_date < DATE '2015-01-01' ) THEN

INSERT INTO sales_detail_202412 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'Date out of range. Fix the

sale_detail_insert_trigger () function!';

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE TRIGGER insert_sale_detail_trigger

BEFORE INSERT ON sales_detail

FOR EACH ROW EXECUTE PROCEDURE

sale_detail_insert_trigger ();

看上去比Oracle或MySQL中建分区表的语法冗余好多，但大概后台原理都差不多，只不过开放给用户的命令更简洁一些。

1. 声明式分区

到PG10.X开始就有了类似Oracle和MySQL那种创建分区表的语法，但其原理还是使用的表继承。举例说明一下：

还是像前面一样创建销售明细表sales_detail1，先创建父表。

CREATE TABLE sales_detail1 (

product_id int not null,

price numeric(12,2),

amount int not null,

sale_date date not null,

buyer varchar(40),

buyer_contact text) partition by range(sale_date);

可以看出有了partition by range这种语法，但是跟oracle还是有点不同，分区还是要以子表的形式来创建。加了partition of 。。 for values from .. to ..这种语法，这种方法不用再手动创建触发器来实现数据重定向了。

CREATE TABLE sales_detail1_202401 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE sales_detail1_202402 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE sales_detail1_202403 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

CREATE TABLE sales_detail1_202404 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

CREATE TABLE sales_detail1_202405 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-05-01') TO ('2024-06-01');

CREATE TABLE sales_detail1_202406 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');

CREATE TABLE sales_detail1_202407 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');

CREATE TABLE sales_detail1_202408 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-08-01') TO ('2024-09-01');

CREATE TABLE sales_detail1_202409 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');

CREATE TABLE sales_detail1_202410 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');

CREATE TABLE sales_detail1_202411 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');

CREATE TABLE sales_detail1_202412 PARTITION OF sales_detail1 FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

查看一下表结构信息：

分区表还是可以当成独立的表来查询。这一点跟Oracle和MySQL又有一点不同，不是使用select * from table partition (partition_name) 这种方法。

postgres=# select * from sales_detail1_202410;

product_id | price | amount | sale_date | buyer | buyer_contact

------------+-------+--------+------------+-------+---------------

1 | 12.20 | 10 | 2024-10-31 | aaa | ddd

(1 row)

总结：PG的分区表早期版本创建方式展示了其内部实现原理（继承+触发器），写起来有点啰嗦，现在的语法比以前简洁了，命令进行了封装。语法格式上跟Oracle和MySQL还是有些许不同，大同小异吧。