当前位置: 首页 > postgresql > 正文

postgresql的分区表实现方式

如下:可以在postgresql中建立分区表

  1. 建立主表,其他的将会继承该主表。
  2. 主表中不会包含任何的数据,所以,不要在该表上定义任何check,除非,需要在后续的任何子表上包含该check。
  3. 建立继承自主表的子表(全部字段来自主表,不需要额外的其他字段)。后面,会将所有的子表作为partition来处理。
  4. 在每个partition(子表)上添加约束:like this:
    CHECK ( x = 1 )
    CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
    CHECK ( outletID >= 100 AND outletID < 200 )

    一定要保证在不同分区之间的key值中不要出现交叠,常见的边界错误如下:

    CHECK ( outletID BETWEEN 100 AND 200 )
    CHECK ( outletID BETWEEN 200 AND 300 )

    这玩意将会导致系统无法判断200将会处于哪一个分区.

  5. 为每一个分区建立独立的索引(同时,还可以建立其他索引哦)
  6. 建立triiger或者rule来将数据进行重定向,使其进入分区子表
  7. 保证  constraint_exclusion 参数要开启哦,不然查询解析的结果可能不是希望的

我们建立一个表(冰欺凌工厂)

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

我们只保存最近三年的数据,每月开始,我们会移出最远month的数据;此时,我们可以使用分区表来实现这个需求:

  1. 主表,上面已经建立好了
  2. 接着,每个月建立一个分区表:
    CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
    CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
    ...
    CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
    CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
    CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);

    第一个问题:删除最远月份的数据:我们需要使用drop table语句来删除最远月份的分区表,并且,使用create table建立最新月份的分区表

  3. 不像一开始建表那样,我们还需要使用check来保证数据的正确性(没有交叠):
    CREATE TABLE measurement_y2006m02 (
        CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
    ) INHERITS (measurement);
    CREATE TABLE measurement_y2006m03 (
        CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
    ) INHERITS (measurement);
    ...
    CREATE TABLE measurement_y2007m11 (
        CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
    ) INHERITS (measurement);
    CREATE TABLE measurement_y2007m12 (
        CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
    ) INHERITS (measurement);
    CREATE TABLE measurement_y2008m01 (
        CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
    ) INHERITS (measurement);
  4. 我们还需要在分区主列上建立index:
    CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
    CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
    ...
    CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
    CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
    CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
  5. 让我们的程序可以插入数据,我们需要这样…数据重定向到合适的分区表,我们使用trigger实现。比如:将数据单纯的插入最后一个分区的函数可以如下:
    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;

    建立好函数后,可以使用trigger来调用:

    CREATE TRIGGER insert_measurement_trigger
        BEFORE INSERT ON measurement
        FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

    每个月,我们都需要重新定义这个函数(改变具体插入数据的分区表),使数据进入正确的分区,当然,触发器是不需要改变的

    使用稍微复杂一些的逻辑,我们可以让服务器自己来判断数据该进入那个分区:

    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        IF ( NEW.logdate >= DATE '2006-02-01' AND
             NEW.logdate < DATE '2006-03-01' ) THEN
            INSERT INTO measurement_y2006m02 VALUES (NEW.*);
        ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
                NEW.logdate < DATE '2006-04-01' ) THEN
            INSERT INTO measurement_y2006m03 VALUES (NEW.*);
        ...
        ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
                NEW.logdate < DATE '2008-02-01' ) THEN
            INSERT INTO measurement_y2008m01 VALUES (NEW.*);
        ELSE
            RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
        END IF;
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;

    函数基本定义和之前的差不多,只是对数据进行了判断,从而决定进入的分区

    这个函数显然比单月份的复杂一些,所以,我们就不需要经常性地修改函数定义了。只要根据需要添加if分支即可

    Note: 写函数的时候,先check最新的月份,可以提高数据插入的性能

管理分区

删除旧分区数据的简单方式就是删除具体的分区表:

DROP TABLE measurement_y2006m02;

这种操作可以在短时间内删除百万级别的数据(根据pg的设计原理,它不会真的去擦除每条记录)

还有可能的方式是:不真的删除表,而是将子表从基表中脱离:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

这样,在删除旧数据前,我们还可以进行其他的操作。比如,我们可以使用copy,pg_dump来备份历史数据。这些数据可以用来制作报表

同样,我们可以建立新的分区表来存储新的数据,就像之前建立其他分区表的方式一样:

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

作为选择,我们可以在分区表之外建立一个表,将这个表适当调整后,加入该分区表(比如将之前删除的分区表子分区又还原回来):

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;

分区和查询剪支

分区剪支Constraint exclusion 可以对上面建立的分区表进行查询优化.如:

SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

不进行剪支,上面的查询会扫描每一个子分区来获取需要的数据。进行剪支后,查询优化器可以根据where语句来判断哪些子分区需要检索。

使用explain来查看是用和不使用剪支时的查询计划:

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

也许,所有的分区可以使用index scan来排除不需要的记录,加速检索。但是,我们此时关注的是能否排除那些不相关的分区:

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

如上,剪支使用check约束来排除分区,而不是index。 即,我们不需要定义索引来达到剪支的效果。是否在分区上建立所以的原则是分区数据量的大小。如果是大的数据,索引是有益的,但是小的分区,索引可能不会起到什么效果(比如,分区小到直接可以放在内存中)

其他分区方式

肯能的分区方式,还有rule:

CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);

rule的方式会更底层一些,但是,trigger的方式可能性能更好一些.

小心COPY这个玩意, 执行copy的时候会忽略任何约束,所以,不能直接copy进入主表,而是要copy进入正确的分区表

rule的另外一个缺点是:无法使用简单的方式处理没有想到的数据,它们会偷偷的进入主表:

分区表也可以使用union all来合并多个子表实现:

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2006m02
UNION ALL SELECT * FROM measurement_y2006m03
...
UNION ALL SELECT * FROM measurement_y2007m11
UNION ALL SELECT * FROM measurement_y2007m12
UNION ALL SELECT * FROM measurement_y2008m01;

可惜的是:当我们删除或者添加新的分区时,需要不断的重建view。

    分享到:

本文固定链接: http://klwang.info/postgresql-partition-table-explain/ | 数据库|Linux|软件开发

该日志由 klwang 于2013年03月21日发表在 postgresql 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: postgresql的分区表实现方式 | 数据库|Linux|软件开发

postgresql的分区表实现方式:等您坐沙发呢!

发表评论

*
快捷键:Ctrl+Enter