PostgreSQL分区表（postgresql多表取数更新至新表）

PostgreSQL是通过表继承来实现分区表的。表分区就是把逻辑上的一个大表分割成物理上的几个小块。分区可以提供若干好处:

• 删除历史数据时更快，如果是按时间分区的，在删除历史数据时，直接删除历史分区就可以了，如果没有分区，通过DELETE删除历史数据时会很慢，还容易导致VACUUM超载；
• 某些类型的查询性能可以得到极大提升，特别时表中访问率较高的行位于一个单独分区或少数几个分区上的情况下。如果在按时间分布的表中，大多数查询发生在时间最近的一个分区或几个 分区中，而较早时间分区比较少查询。那么，在建分区表后，各个分区表会有各自的索引，使用率较高的分区表的索引就可能完全缓存在内存中，这样效率就会高很多；
• 当查询或更新一个分区的大部分记录时，连续扫描那个分区而不是使用索引离散地访问整个表，可以获得巨大的性能提升；
• 很少用到的历史数据可以使用表空间的技术移动到便宜一些的慢速存储介质上。因为使用分区表可以将不同的分区安置在不同的物理介质上。

多大数据该使用分区表？一般取决于具体的应用，不过也有个基本的简单原则，即表的大小超过了数据库服务器的物理内存大小则应该使用。

在使用继承实现的分区表时，一般会让父表为空，数据都存储在子表中。

建分区表的步骤如下：

1.创建父表，所有分区都从它继承。这个表中没有数据，不要在这个表上定义任何检查约束，除非你希望约束所有分区。同意，在其上定义任何索引或唯一约束也没有意义。

2.创建几个子表，每个表都是从主表上继承。通常，这些表不会增加任何字段。我们把子表称作分区，实际上他们就是普通的PostgreSQL表。

3.给分区表增加约束，定义每个分区的键值。

4.对于每个分区，在关键字字段上创建一个索引，也可创建其他你想创建的索引。严格来说，关键字字段索引并非必须的，但是在大多数情况下它是很有帮助的。如果你希望关键字值是唯一的，那么应该总是给每个分区创建一个唯一约束或主键约束。

5.定义一个规则或者触发器，把对主表的数据插入重定向到合适的分区表。

6.确保constraint_exclusion里的配置参数postgresql.conf是打开的。打开后，如果查询中WHERE子句过滤条件与分区的约束条件匹配，那么这个查询会智能地只查询这个分区，而不会查询其他分区。

实现分区表的例子

假设有一张销售明细表，定义如下:
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        -- 买家联系方式
);

先建主表，主表就是上面的sales_detail表。现在按销售日期进行分区，每个月一个分区，建各个分区的语句如下:
create table sales_detail_y2021m01(CHECK (sale_date >= DATE '2021-01-01' AND sale_date < DATE '2021-02-01')) INHERITS(sales_detail);

create table sales_detail_y2021m02(CHECK (sale_date >= DATE '2021-02-01' AND sale_date < DATE '2021-03-01')) INHERITS(sales_detail);

create table sales_detail_y2021m03(CHECK (sale_date >= DATE '2021-03-01' AND sale_date < DATE '2021-04-01')) INHERITS(sales_detail);

create table sales_detail_y2021m04(CHECK (sale_date >= DATE '2021-04-01' AND sale_date < DATE '2021-05-01')) INHERITS(sales_detail);

每个分区实际上都是一张完整的表，只不过他们是从sales_detail表继承定义的。父表sales_detail中实际是不存数据的。以后若要删除旧数据，只需要删除最早月份的表即可。不知大家是否注意到了每个分区表中都加了一个约束，这表示只允许插入本月内的数据。

一般情况下，还可以在分区键"sale_date"上建索引:
CREATE INDEX sale_detail_y2021m01_sale_date ON sales_detail_y2021m01 (sale_date);
CREATE INDEX sale_detail_y2021m02_sale_date ON sales_detail_y2021m02 (sale_date);
CREATE INDEX sale_detail_y2021m03_sale_date ON sales_detail_y2021m03 (sale_date);
CREATE INDEX sale_detail_y2021m04_sale_date ON sales_detail_y2021m04 (sale_date);

当然如果有需要，还可以在其他字段上建索引。
目前还有一个插入数据的问题没有解决，在往sale_detail表中插入数据时，怎么能自动且正确地把数据插入到正确的分区呢？可能有人已经想到了，使用触发器，那么，接下来就建一个触发器：
CREATE OR REPLACE FUNCTION sale_detail_insert_trigger()
RETURNS TRIGGER AS $
BEGIN
    IF (NEW.sale_date >= DATE '2021-01-01' AND NEW.sale_date < DATE '2021-02-01') THEN
        INSERT INTO sales_detail_y2021m01 VALUES(NEW.*);
    ELSIF (NEW.sale_date >= DATE '2021-02-01' AND NEW.sale_date < DATE '2021-03-01') THEN
        INSERT INTO sales_detail_y2021m02 VALUES(NEW.*);
    ELSIF (NEW.sale_date >= DATE '2021-03-01' AND NEW.sale_date < DATE '2021-04-01') THEN
        INSERT INTO sales_detail_y2021m03 VALUES(NEW.*);
    ELSIF (NEW.sale_date >= DATE '2021-04-01' AND NEW.sale_date < DATE '2021-05-01') THEN
        INSERT INTO sales_detail_y2021m04 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();

至此分区表就建好了。

试试插入数据
insert into sales_detail values(1,43.12,1,date '2021-01-02','塞尔达','海拉鲁');
insert into sales_detail values(2,8.88,1,date '2021-03-07','林克','海拉鲁保安部');
insert into sales_detail values(3,28.88,1,date '2021-04-15','达尔克尔','咚鼓族');
testdb=# select * from sales_detail;
 product_id | price | amount | sale_date  |  buyer   | buyer_contact
------------+-------+--------+------------+----------+---------------
          1 | 43.12 |      1 | 2021-01-02 | 塞尔达   | 海拉鲁
          2 |  8.88 |      1 | 2021-03-07 | 林克     | 海拉鲁保安部
          3 | 28.88 |      1 | 2021-04-15 | 达尔克尔 | 咚鼓族
          
testdb=# insert into sales_detail values(4,7.99,1,date '2021-05-02','米法','卓尔领地');
ERROR:  Date out of range. Fix the sale_detail_insert_trigger () function!
CONTEXT:  PL/pgSQL function sale_detail_insert_trigger() line 12 at RAISE

不过需要注意的是，上面的分区表只包括2021年1月到4月的分区表，如果日期到达2021年5月1日，再往表sale_detail中插入数据，就会报错，所以在2021年5月之前，就应该把所有新的分区表加上去，同时还要改调触发器中的内容。在PostgreSQL中，删除表sales_detail_y2021m01并不会导致触发器函数失效。
验证如下:
drop table sales_detail_y2021m01;
insert into sales_detail values(4,7.99,1,date '2021-04-08','米法','卓尔领地');
可以看出，分区表是使用触发器来把插入的数据重新定位到相应的分区中，实际上可以使用PostgreSQL中的"规则”来实现上面的函数触发器功能。
连接一个新库chinese，然后创建主表sales_detail，以及子表sales_detail_y2021m01 sales_detail_y2021m02 sales_detail_y2021m03等。

CREATE RULE sales_detail_insert_y2021m01 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-01-01' AND  sale_date < DATE '2021-02-01') DO INSTEAD INSERT INTO sales_detail_y2021m01 VALUES(NEW.*);

CREATE RULE sales_detail_insert_y2021m02 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-02-01' AND  sale_date < DATE '2021-03-01') DO INSTEAD INSERT INTO sales_detail_y2021m02 VALUES(NEW.*);

CREATE RULE sales_detail_insert_y2021m03 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-03-01' AND  sale_date < DATE '2021-04-01') DO INSTEAD INSERT INTO sales_detail_y2021m03 VALUES(NEW.*);

CREATE RULE sales_detail_insert_y2021m04 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-04-01' AND  sale_date < DATE '2021-05-01') DO INSTEAD INSERT INTO sales_detail_y2021m04 VALUES(NEW.*);

但该规则有如下的缺点:
1.相比于触发器，该规则有显著的开销，而且每次检查时都会有此开销。不过，批量插入时只会有一次开销，所以再批量插入的情况下，其相对于触发器更有优势。然而再更多的情况下，触发器的方法更好一些；
2.如果想用COPY插入数据，由于COPY不会触发"规则"，因此先得把要复制得数据直接COPY到分区表(而不是主表)。不过，COPY时回触发触发器得，所以用触发器得方法就可以正常使用；
3.如果插入数据时在规则设置范围之外得，比如2021年5月1日之后得数据，那么将会插入到主表中。如果此时希望直接报错，而不是把数据插入到主表中，使用规则时无法实现的。

insert into sales_detail values(1,43.12,1,date '2021-01-02','塞尔达','海拉鲁');
insert into sales_detail values(2,8.88,1,date '2021-03-07','林克','海拉鲁保安部');
insert into sales_detail values(3,28.88,1,date '2021-04-15','达尔克尔','咚鼓族');
chinese=# select * from sales_detail;
 product_id | price | amount | sale_date  |  buyer   | buyer_contact
------------+-------+--------+------------+----------+---------------
          1 | 43.12 |      1 | 2021-01-02 | 塞尔达   | 海拉鲁
          2 |  8.88 |      1 | 2021-03-07 | 林克     | 海拉鲁保安部
          3 | 28.88 |      1 | 2021-04-15 | 达尔克尔 | 咚鼓族
          

insert into sales_detail values(4,7.99,1,date '2021-05-02','米法','卓尔领地');
chinese=# select * from sales_detail;
 product_id | price | amount | sale_date  |  buyer   | buyer_contact
------------+-------+--------+------------+----------+---------------
          4 |  7.99 |      1 | 2021-05-02 | 米法     | 卓尔领地
          1 | 43.12 |      1 | 2021-01-02 | 塞尔达   | 海拉鲁
          2 |  8.88 |      1 | 2021-03-07 | 林克     | 海拉鲁保安部
          3 | 28.88 |      1 | 2021-04-15 | 达尔克尔 | 咚鼓族
          
chinese=# select * from sales_detail_y2021m01;
 product_id | price | amount | sale_date  | buyer  | buyer_contact
------------+-------+--------+------------+--------+---------------
          1 | 43.12 |      1 | 2021-01-02 | 塞尔达 | 海拉鲁
(1 row)

chinese=# select * from sales_detail_y2021m02;
 product_id | price | amount | sale_date | buyer | buyer_contact
------------+-------+--------+-----------+-------+---------------
(0 rows)

chinese=# select * from sales_detail_y2021m03;
 product_id | price | amount | sale_date  | buyer | buyer_contact
------------+-------+--------+------------+-------+---------------
          2 |  8.88 |      1 | 2021-03-07 | 林克  | 海拉鲁保安部
(1 row)

chinese=# select * from sales_detail_y2021m04;
 product_id | price | amount | sale_date  |  buyer   | buyer_contact
------------+-------+--------+------------+----------+---------------
          3 | 28.88 |      1 | 2021-04-15 | 达尔克尔 | 咚鼓族
(1 row)
查看主表和子表，可以看出5月份的数据，实际是插入到了主表里。

分区优化的技巧

打开约束排除(constraint_exclusion)是一种查询优化技巧，它改进了用上面的方法定义的表分区的性能。在PostgreSQL9.2.4中，参数"constraint_exclusion"默认就是"partition",如果采用默认值，在SQL查询中将WHERE语句的过滤条件与表上的CHECK条件进行对比，就可得知不需要扫描的分区，从而跳过相应的分区表，性能也就得到了提高。

testdb=# explain select count(*) from sales_detail where sale_date >= DATE '2021-03-01';
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=32.90..32.91 rows=1 width=8)
   ->  Append  (cost=0.00..32.19 rows=287 width=0)
         ->  Seq Scan on sales_detail sales_detail_1  (cost=0.00..0.00 rows=1 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m03 sales_detail_2  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m04 sales_detail_3  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
(8 rows)
这里将WHERE条件中的"sale_date >= DATE '2021-03-01'"与各个分区子表上的CHECK条件进行对比，就知道了只需要扫描主表和sales_detail_y2021m03 sales_detail_y2021m04，而不需要扫描其他的分区子表。

如果把参数"constraint_exclusion"设置成off,则会扫描每张分区子表，如下:
testdb=# set constraint_exclusion='off';
SET
testdb=# explain select count(*) from sales_detail where sale_date >= DATE '2021-03-01';
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=67.43..67.44 rows=1 width=8)
   ->  Append  (cost=0.00..65.97 rows=586 width=0)
         ->  Seq Scan on sales_detail sales_detail_1  (cost=0.00..1.54 rows=14 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m01 sales_detail_2  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m02 sales_detail_3  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m03 sales_detail_4  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m04 sales_detail_5  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
(12 rows)