postgresql 本地分区实践

  项目需要使用分区表，看了下资料，postgresql 分为本地分区和异地分区，这里使用本地分区。postgresql不支持分区表，通过继承表的概念来实现数据分区表的查询,更新,删除,插入数据的逻辑。

      数据分区的好处

? 分区后, 单个分区表的索引和表都变小了, 可以保持在内存里面, 适合把热数据从大表拆分出来的场景.

? 对于大范围的查询, 大表可以通过索引来避免全表扫描. 但是如果分区了的话, 可以使用分区的全表扫描. 适合经常要做大

范围扫描的场景, 按照范围分区(分区后采用全表扫描), 减少索引带来的随机BLOCK扫描.

? 大批量的数据导入或删除, 对于大表来说, 删除大量的数据使用DELETE的话会带来大量的VACUUM操作负担.而使用分

区表的话可以直接DROP分区, 或者脱离子表和父表的继承关系.

? 使用分区表, 还有一个好处是, 可以把不常用的分区放到便宜的存储上.

? 因为每个表只能放在一个表空间上, 表空间和目录对应, 表的大小受到表空间大小的限制. 使用分区表则更加灵活.

数据库分区表分类

? 范围分区

? 根据字段存储的值取值范围进行分区, 例如日志表的时间字段, 用户表的ID范围等等.

? 哈希分区

? 根据字段存储值HASH再做和分区数做比特运算得到一个唯一的分区ID.

? 或者取模也行.

? 例如mod(hashtext(name),16), 对16个分区的场景.

? list分区

? 与哈希分区类似, 但是直接使用字段值作为分区条件. 适合KEY值比较少并且比较均匀的场景.

? 例如按性别字段作为分区字段. 那么就分成了2个区.

我们要求按时间分区，使用范围分区

主表：

CREATE TABLE "public"."msg_statistical" (

    "id" int4 NOT NULL DEFAULT nextval('msg_statistical_id_seq'::regclass),

    ''''''

    "created_at" timestamp(6) NOT NULL,

    CONSTRAINT "msg_statistical_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE

)

WITH (OIDS=FALSE);

分区子表：

create table msg_statistical_partition_201501 (

check ( created_at >= timestamp  '2015-01-01 00:00:00' AND created_at < timestamp  '2015-02-01 00:00:00' )       

) INHERITS (msg_statistical);

create table msg_statistical_partition_201502 (

check ( created_at >= timestamp  '2015-02-01 00:00:00' AND created_at < timestamp  '2015-03-01 00:00:00' )       

) INHERITS (msg_statistical);

create table msg_statistical_partition_201503 (

check ( created_at >= timestamp  '2015-03-01 00:00:00' AND created_at < timestamp  '2015-04-01 00:00:00' )       

) INHERITS (msg_statistical);

'''''''

建立索引：

create index msg_statistical_partition_201501_index on msg_statistical_partition_201501 (created_at);

create index msg_statistical_partition_201502_index on msg_statistical_partition_201502 (created_at);

create index msg_statistical_partition_201503_index on msg_statistical_partition_201503 (created_at);

'''''''

postgresql 在进行查询、更新、删除表操作时会自动对该表的子表进行相应操作，但是插入数据的时候不操作子表，所以必须建立触发器，在插入数据到主表时按照时间分别插入相应的子表。

CREATE OR REPLACE FUNCTION msg_statistical_partition_insert_trigger()                      

RETURNS TRIGGER AS $$  

BEGIN  

    IF ( NEW.created_at >= timestamp '2014-12-01 00:00:00' AND    

         NEW.created_at < timestamp '2015-01-01 00:00:00' ) THEN  

        INSERT INTO msg_statistical_partition_201412 VALUES (NEW.*);  

    ELSIF ( NEW.created_at >= timestamp '2015-01-01 00:00:00' AND  

            NEW.created_at < timestamp '2015-02-01 00:00:00' ) THEN  

        INSERT INTO msg_statistical_partition_201501 VALUES (NEW.*);  

    ELSIF ( NEW.created_at >= timestamp '2015-02-01 00:00:00' AND  

            NEW.created_at < timestamp '2015-03-01 00:00:00' ) THEN  

        INSERT INTO msg_statistical_partition_201502 VALUES (NEW.*);  

    ELSIF ( NEW.created_at >= timestamp '2015-03-01 00:00:00' AND  

            NEW.created_at < timestamp '2015-04-01 00:00:00' ) THEN  

        INSERT INTO msg_statistical_partition_201503 VALUES (NEW.*);  

    '''''''

    ELSE  

        RAISE EXCEPTION 'Date out of range. Fix the msg_statistical_partition_insert_trigger() function!';  

    END IF;  

    RETURN NULL;  

END;  

$$  

LANGUAGE plpgsql;

而且必须在每次插入主表的时候执行这个触发器。

CREATE TRIGGER insert_msg_statistical_partition_trigger

    BEFORE INSERT ON msg_statistical

    FOR EACH ROW EXECUTE PROCEDURE msg_statistical_partition_insert_trigger();

至此分区表建好了，但是每次插入都用触发器，肯定会影响性能，这个需要注意。