Postgresql之分区表

参考：

http://www.jasongj.com/2015/12/13/SQL3_partition/

https://www.postgresql.org/docs/9.1/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION

 

创建步骤（注意：以下针对10.0版本之前的用法，新版本见：https://postgres.fun/20170521123452.html

1、创建主表。不用为该表定义任何检查限制，除非需要将该限制应用到所有的分区表中。同样也无需为该表创建任何索引和唯一限制。

CREATE TABLE almart
(
	date_key date,
	hour_key smallint,
	client_key integer,
	item_key integer,
	account integer,
	expense numeric
);

2、创建多个分区表。每个分区表必须继承自主表，并且正常情况下都不要为这些分区表添加任何新的列。

CREATE TABLE almart_2015_12_10 () inherits (almart);

CREATE TABLE almart_2015_12_11 () inherits (almart);

CREATE TABLE almart_2015_12_12 () inherits (almart);

CREATE TABLE almart_2015_12_13 () inherits (almart);

3、为分区表添加限制。这些限制决定了该表所能允许保存的数据集范围。这里必须保证各个分区表之间的限制不能有重叠。

ALTER TABLE almart_2015_12_10
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-10'::date);

ALTER TABLE almart_2015_12_11
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-11'::date);

ALTER TABLE almart_2015_12_12
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-12'::date);

ALTER TABLE almart_2015_12_13
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-13'::date);

4、为每一个分区表，在主要的列上创建索引。

ALTER TABLE almart_2015_12_10
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-10'::date);

ALTER TABLE almart_2015_12_11
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-11'::date);

ALTER TABLE almart_2015_12_12
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-12'::date);

ALTER TABLE almart_2015_12_13
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-13'::date);

5、定义一个trigger或者rule把对主表的数据插入操作重定向到对应的分区表。

--创建分区函数
CREATE OR REPLACE FUNCTION almart_partition_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.date_key = DATE '2015-12-10'
    THEN
        INSERT INTO almart_2015_12_10 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-11'
    THEN
        INSERT INTO almart_2015_12_11 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-12'
    THEN
        INSERT INTO almart_2015_12_12 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-13'
    THEN
        INSERT INTO almart_2015_12_13 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-14'
    THEN
        INSERT INTO almart_2015_12_14 VALUES (NEW.*);
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

--挂载分区Trigger
CREATE TRIGGER insert_almart_partition_trigger
BEFORE INSERT ON almart
FOR EACH ROW EXECUTE PROCEDURE almart_partition_trigger();

需要注意的是：

我们需要确保postgresql.conf中的constraint_exclusion配置项没有被disable。

思考：表分区是如何加速查询优化的呢？

当constraint_exclusion为on或者partition时，查询计划期会跟据分区表的检查限制将对主表的查询限制在符合检查限制条件的分区表上，直接避免了对不符合条件的分区表的扫描。