postgresql 本地分区实践

落日映苍穹つ 2022-07-14 03:26 119阅读 0赞

项目需要使用分区表，看了下资料，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();

![postgresql 本地分区 - guotengfei19880205 - guotengfei19880205的博客][postgresql _ - guotengfei19880205 - guotengfei19880205]至此分区表建好了，但是每次插入都用触发器，肯定会影响性能，这个需要注意。

[postgresql _ - guotengfei19880205 - guotengfei19880205]: /images/20220714/51a3b38b52ce4a79b9aab92f14bfef7b.png

postgresql 本地分区实践

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 PostgreSQL高效分区表实现-pg_pathman

相关 MySQL分区表最佳实践

相关 PostgreSQL分区表（partitioning）应用实例

相关 postgresql 本地分区实践

相关【最佳实践】SequoiaDB数据分区实践与经验

相关 mysql(5)-分区实践

相关 PostgreSQL分区表（Table Partitioning）应用

相关 mysql分区实践

相关 PostgreSQL 10.0 内置分区表

相关 Oracle分区表实践