作者 Iris （PG深度爱好者）

业务背景

近期在支撑应用厂商从Oracle改造到PostgreSQL的改造工作，目前大部分O库业务表已经成功转化为PG表，应用厂商提出关于Oracle分区表迁移到PG分区表的改造方法和思路问题。由于应用业务数据每天产生大几千万数据，需要对每天的数据进行分区存储。业务表需要保留30天数据，30天之前的数据删除。

在原Oracle分区表设计中，以时间范围分区，区域为列表分区来创建混合分区。在分布式MYSQL数据库里可以通过时间范围分片规自动生成分片规则。在PG中分区表是通过表继承来实现的，创建一个空的主表，每个分区表按时间进行创建，去继承主表。

分区表介绍

数据库表分区把一个大的物理表分成若干个小的物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。

分区术语

· 主表 / 父表 该表是创建子表的模板。可以理解为一个标准模板表，正常情况下它并不储存任何数据。

· 子表 / 分区表 / Child Table / Partition Table　子表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系，一个主表包含多个分区表，而一个分区表只从属于一个主表

分区优势

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

· 对于大范围的查询, 大表可以通过索引来避免全表扫描. 但是如果分区了的话, 可以使用分区的全表扫描. 适合经常要做大 范围扫描的场景, 按照范围分区(分区后采用全表扫描), 减少索引带来的随机BLOCK扫描. ?

· 大批量的数据导入或删除, 对于大表来说, 删除大量的数据使用DELETE的话会带来大量的VACUUM操作负担.而使用分 区表的话可以直接DROP分区, 或者脱离子表和父表的继承关系. ?

· 使用分区表,可以把不常用的分区放到便宜的存储上. ?

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

本地分区

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

哈希分区：根据字段存储值HASH再做和分区数做比特运算得到一个唯一的分区ID. 或者取模.例如mod(hashtext(name),16), 对16个分区的场景。

list分区：与哈希分区类似, 但是直接使用字段值作为分区条件. 适合KEY值比较少并且比较均匀的场景.例如按性别字段作为分区字段. 那么就分成了2个区。

本地分区，子表继承表自动继承父表的约束, 非空约束. 但是不自动继承的是(uk,pk,fk,索引,存储参数等)。如果需要继承父表约束，需要添加like table_partition including all 参数。例如：CREATE TABLE tab_partition_2020_01_1 (like tab_partition including all) inherits (tab_partition);

创建分区表

创建本地分区表

创建父表

创建主标。限制应用数据1月份的数据插入到2020年1月份分区表中，也可以为主表创建约束条件和唯一键。

CREATE TABLE tab_partition

(

date_key date check(date_key>to_date('2020-01-01 00:00:01','yyyy-mm-dd hh24:mi:ss') and date_key<to_date('2020-01-31 00:00:01','yyyy-mm-dd hh24:mi:ss')),

hour_key smallint,

client_key integer,

item_key integer ,

account integer,

expense numeric);

创建子表

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

CREATE TABLE tab_partition_2020_01_01 (like tab_partition including all) inherits (tab_partition);CREATE TABLE tab_partition_2020_01_02 (like tab_partition including all) inherits (tab_partition);CREATE TABLE tab_partition_2020_01_03 (like tab_partition including all) inherits (tab_partition);CREATE TABLE tab_partition_2020_01_04 (like tab_partition including all) inherits (tab_partition);

CREATE TABLE tab_partition_2020_01_05 (like tab_partition including all) inherits (tab_partition);

1、 此建表用在测试，其他建表语句省略，实际在生产中建表需要在程序代码中，通过for循环建表

2、 字表会默认继承父表的约束关系

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

CREATE OR REPLACE FUNCTION tab_partition_partition_trigger()RETURNS TRIGGER AS $BEGIN

IF NEW.date_key = DATE '2020-01-01' THEN INSERT INTO tab_partition_2020_01_01 VALUES (NEW.*); ELSIF NEW.date_key = DATE '2020-01-11' THEN INSERT INTO tab_partition_2020_01_02 VALUES (NEW.*); ELSIF NEW.date_key = DATE '2020-01-02' THEN INSERT INTO tab_partition_2020_01_03 VALUES (NEW.*); ELSIF NEW.date_key = DATE '2020-01-03' THEN INSERT INTO tab_partition_2020_01_04 VALUES (NEW.*); ELSIF NEW.date_key = DATE '2020-01-04' THEN INSERT INTO tab_partition_2020_01_05 VALUES (NEW.*); END IF; RETURN NULL;END;$LANGUAGE plpgsql;

--挂载分区TriggerCREATE TRIGGER insert_tab_partition_partition_triggerBEFORE INSERT ON tab_partitionFOR EACH ROW EXECUTE PROCEDURE tab_partition_partition_trigger();

2. 确保postgresql.conf中的配置项没有被disable。这一点非常重要，如果该参数项被disable，则基于分区表的查询性能无法得到优化，甚至比不使用分区表直接使用索引性能更低。

查询优化

当constraint_exclusion为on或者partition时，查询计划器会根据分区表的检查限制将对主表的查询限制在符合检查限制条件的分区表上，直接避免了对不符合条件的分区表的扫描。　　为了验证分区表的优势，这里创建一个与上文创建的tab_partition结构一样的表tab_partition_all，并为其date_key创建索引，向tab_partition和tab_partition_all中插入同样的3亿条数据（数据的时间跨度为2020-01-01到2020-01-30）。

CREATE TABLE tab_partition_all

(

date_key date ,

hour_key smallint,

client_key integer,

item_key integer,

account integer,

expense numeric

);

插入随机测试数据到tab_partition_all

INSERT INTO tab_partition_allselect (select array_agg(i::date) from generate_series( '2020-1-01'::date, '2020-1-30'::date, '1 day'::interval) as t(i) )[floor(random()*4)+1] as date_key, floor(random()*24) as hour_key, floor(random()*1000000)+1 as client_key, floor(random()*100000)+1 as item_key, floor(random()*20)+1 as account, floor(random()*10000)+1 as expensefrom generate_series(1, 300000000,1);

插入同样的测试数据到tab_partition

INSERT INTO

tab_partition

select

(select

array_agg(i::date)

from

generate_series(

'2020-01-01'::date,

'2020-01-30'::date,

'1 day'::interval) as t(i)

)[floor(random()*4)+1] as date_key,

floor(random()*24) as hour_key,

floor(random()*1000000)+1 as client_key,

floor(random()*100000)+1 as item_key,

floor(random()*20)+1 as account,

floor(random()*10000)+1 as expense

from

generate_series(1, 300000000,1);

在tab_partition和tab_partition_all上执行同样的query，查询2020-1-1日的数据量。

\timing

explain analyze select * from tab_partition where date_key=date '2020-01-01';

\timing

explain analyze select * from tab_partition_all where date_key=date '2020-01-01';

　　由上可见，使用分区表时，所需时间为17.9秒，而不使用分区表时，耗时40.6秒。　　使用分区表，PostgreSQL跳过了除2020-01-01区表以外的分区表，只扫描2020-01-01分区表。而不使用分区表只使用索引时，数据库要使用索引扫描整个数据库。另一方面，使用分区表时，每个表的索引是独立的，即每个分区表的索引都只针对一个小的分区表。而不使用分区表时，索引是建立在整个大表上的。数据量越大，索引的速度相对越慢。

创建RANGE分区

创建RANGE分区父表

create table tmp_par_range (

id int8,

random_char varchar(100),

day_id varchar(8)

) partition by RANGE(day_id);

创建RANGE分区子表

CREATE TABLE tmp_par_range_p20171130_b PARTITION OF tmp_par_range FOR VALUES FROM (MINVALUE) TO ('20171130');

CREATE TABLE tmp_par_range_p20171201 PARTITION OF tmp_par_range FOR VALUES FROM ('20171130') TO ('20171201');

CREATE TABLE tmp_par_range_p20171202 PARTITION OF tmp_par_range FOR VALUES FROM ('20171201') TO ('20171202');

CREATE TABLE tmp_par_range_p20171203 PARTITION OF tmp_par_range FOR VALUES FROM ('20171202') TO ('20171203');

CREATE TABLE tmp_par_range_p20171203_a PARTITION OF tmp_par_range FOR VALUES FROM ('20171203') TO (MAXVALUE);

创建HASH分区

创建HASH分区父表

CREATE TABLE tab_par_hash( stuname text , ctime timestamp(6) without time zone) PARTITION BY HASH(stuname);

创建索引

CREATE INDEX idx_par_stuname on tab_par_hash using btree(stuname);

创建HASH分区子表

CREATE TABLE tab_par_hash_p1 PARTITION OF tab_par_hash FOR VALUES WITH(MODULUS 4, REMAINDER 0);CREATE TABLE tab_par_hash_p2 PARTITION OF tab_par_hash FOR VALUES WITH(MODULUS 4, REMAINDER 1);CREATE TABLE tab_par_hash_p3 PARTITION OF tab_par_hash FOR VALUES WITH(MODULUS 4, REMAINDER 2);CREATE TABLE tab_par_hash_p4 PARTITION OF tab_par_hash FOR VALUES WITH(MODULUS 4, REMAINDER 3);

创建LIST分区表

创建LIST分区父表

create table tmp_par_list (

id int8,

random_char varchar(100),

day_id varchar(8)

) partition by list(day_id);

创建LIST分区子表

CREATE TABLE tmp_par_list_p20171130 PARTITION OF tmp_par_list FOR VALUES in ('20171130');

CREATE TABLE tmp_par_list_p20171201 PARTITION OF tmp_par_list FOR VALUES in ('20171201');

CREATE TABLE tmp_par_list_p20171202 PARTITION OF tmp_par_list FOR VALUES in ('20171202');

CREATE TABLE tmp_par_list_p20171203 PARTITION OF tmp_par_list FOR VALUES in ('20171203');

管理分区

　　从上文分区表的创建过程可以看出，分区表必须在相关数据插入之前创建好。在生产环境中，很难保证所需的分区表都已经被提前创建好。同时为了不让分区表过多，影响数据库性能，不能创建过多无用的分区表。

周期性创建分区表

　　在生产环境中，经常需要周期性删除和创建一些分区表。一个经典的做法是使用定时任务。比如使用cronjob每天运行一次，将1年前的分区表删除，并创建第二天分区表（该表按天分区）。有时为了容错，会将之后一周的分区表全部创建出来。

动态创建分区表

上述周期性创建分区表的方法在绝大部分情况下有效，但也只能在一定程度上容错。另外，上文所使用的分区函数，使用IF语句对date_key进行判断，需要为每一个分区表准备一个IF语句。　　如插入date_key分别为2020-01-1到2020-01-5的6条记录，前面5条均可插入成功，因为相应的分区表已经存在，但最后一条数据因为相应的分区表不存在而插入失败。

INSERT INTO tab_partition(date_key) VALUES ('2020-01-01');INSERT 0 0INSERT INTO tab_partition(date_key) VALUES ('2020-01-02');INSERT 0 0INSERT INTO tab_partition(date_key) VALUES ('2020-01-03');INSERT 0 0INSERT INTO tab_partition(date_key) VALUES ('2020-01-04');INSERT 0 0INSERT INTO tab_partition(date_key) VALUES ('2020-01-05');

INSERT 0 0

INSERT INTO tab_partition(date_key) VALUES ('2020-01-06');ERROR: relation "tab_partition_2020_01_06" does not existLINE 1: INSERT INTO tab_partition_2020_01_06 VALUES (NEW.*) ^QUERY: INSERT INTO tab_partition_2020_01_06 VALUES (NEW.*)CONTEXT: PL/pgSQL function tab_partition_partition_trigger() line 17 at SQL statement

SELECT * FROM tab_partition;

date_key | hour_key | client_key | item_key | account | expense

------------+----------+------------+----------+---------+---------

2020-01-1 | | | | |

2020-01-2 | | | | |

2020-01-3 | | | | |

2020-01-4 | | | | |

2020-01-5 | | | | |

(5 rows)

针对该问题，可使用动态SQL的方式进行数据路由，并通过获取将数据插入不存在的分区表产生的异常消息并动态创建分区表的方式保证分区表的可用性。

CREATE OR REPLACE FUNCTION tab_partition_partition_trigger()RETURNS TRIGGER AS $DECLARE date_text TEXT;DECLARE insert_statement TEXT;BEGIN SELECT to_char(NEW.date_key, 'YYYY_MM_DD') INTO date_text; insert_statement := 'INSERT INTO tab_partition_' || date_text ||' VALUES ($1.*)'; EXECUTE insert_statement USING NEW; RETURN NULL; EXCEPTION WHEN UNDEFINED_TABLE THEN EXECUTE 'CREATE TABLE IF NOT EXISTS tab_partition_' || date_text || '(CHECK (date_key = ''' || date_text || ''')) INHERITS (tab_partition)'; RAISE NOTICE 'CREATE NON-EXISTANT TABLE tab_partition_%', date_text; EXECUTE 'CREATE INDEX tab_partition_date_key_' || date_text || ' ON tab_partition_' || date_text || '(date_key)'; EXECUTE insert_statement USING NEW; RETURN NULL;END;$LANGUAGE plpgsql;

使用该方法后，再次插入date_key为2020-01-5的记录时，对应的分区表不存在，但会被自动创建。

INSERT INTO tab_partition VALUES('2020-01-4'),('2020-01-5'),('2020-01-6');NOTICE: CREATE NON-EXISTANT TABLE tab_partition_2020_01_4NOTICE: CREATE NON-EXISTANT TABLE tab_partition_2020_01_5INSERT 0 0SELECT * FROM tab_partition; date_key | hour_key | client_key | item_key | account | expense------------+----------+------------+----------+---------+--------- 2020-01-1 | | | | | 2020-01-2 | | | | | 2020-01-3 | | | | | 2020-01-4 | | | | | 2020-01-5 | | | | | 2020-01-6 | | | | |(6 rows)

移除分区表

虽然如上文所述，分区表的使用可以跳过扫描不必要的分区表从而提高查询速度。但由于服务器磁盘的限制，不可能无限制存储所有数据，经常需要周期性删除过期数据，如删除5年前的数据。如果使用传统的DELETE，删除速度慢，并且由于DELETE只是将相应数据标记为删除状态，不会将数据从磁盘删除，需要使用VACUUM释放磁盘，从而引入额外负载。　　而在使用分区表的条件下，可以通过直接DROP过期分区表的方式快速方便地移除过期数据。如DROP TABLE tab_partition_2019_12_15;

另外，无论使用DELETE还是DROP，都会将数据完全删除，即使有需要也无法再次使用。因此还有另外一种方式，即更改过期的分区表，解除其与主表的继承关系，如。

ALTER TABLE tab_partition_2019_12_15 NO INHERIT tab_partition;

但该方法并未释放磁盘。此时可通过更改该分区表，使其属于其它，同时将该TABLESPACE的目录设置为其它磁盘分区上的目录，从而释放主表所在的磁盘。同时，如果之后还需要再次使用该"过期"数据，只需更改该分区表，使其再次与主表形成继承关系。

如果要完全删除全部的分区表，只需要drop 分表加cascade。

注意, 本地字段不能从主表删除, 但是继承字段随着主表删除该字段而自动删除. ? digoal=# alter table p drop column username; ? c1, c2, c3, c4, c5的username字段能不能自动删除, 完全取决于它对应的系统表pg_attribute.attislocal的值. False则可以字段删除. 否则不会自 动删除.

? 这些特点维护时必须加以注意. ? 正规的创建继承表应该使用这种方式 ? digoal=# create table c7() inherits(p); -- 这种方式不会自动创建与主表类似的索引 ? 或者用这种方式 ? digoal=# create table c7(like p including all) inherits(p); -- 现在的子表字段是被创建为本地字段的. ? digoal=# update pg_attribute set attislocal=false where attrelid='c7'::regclass and attnum>=0; -- 使用这个方法可以把他们更新为继承字段. ? digoal=# select attname,attislocal from pg_attribute where attrelid='c7'::regclass; ? attname | attislocal ? …… ? ctid | t ? id | f ? info | f ? crt_time | f ? (9 rows)

PostgreSQL

分区缺陷

目前PostgreSQL分区表实施方法的存在的缺陷 ?

· 性能缺陷：目前PostgreSQL不管是使用约束还是触发器来实现范围缩小到分区表的情况, 性能都不是非常的理想, 会损耗一些 CPU运算.

· 全局约束缺陷：数据分布到多个子表后, 就无法简单的实现全局唯一了. 除非约束字段是是分布列. ?

· 全局外键关联缺陷：全局外键也没有办法实现. 例如create table test (id int references p (id)), 如果p是主表的话, 是只能外键约束到主表 本身的, 无法延续到子表.

分区总结

· 如果要充分使用分区表的查询优势，必须使用分区时的字段作为过滤条件

· 分区字段被用作过滤条件时，WHERE语句只能包含常量而不能使用参数化的表达式，因为这些表达式只有在运行时才能确定其值，而planner在真正执行query之前无法判定哪些分区表应该被使用

· 跳过不符合条件分区表是通过planner根据分区表的检查限制条件实现的，而非通过索引

· 必须将constraint_exclusion设置为ON或Partition，否则planner将无法正常跳过不符合条件的分区表，也即无法发挥表分区的优势

· 除了在查询上的优势，分区表的使用，也可提高删除旧数据的性能

· 为了充分利用分区表的优势，应该保证各分区表的检查限制条件互斥，但目前并无自动化的方式来保证这一点。因此使用代码造化创建或者修改分区表比手工操作更安全

· 在更新数据集时，如果使得partition key column(s)变化到需要使某些数据移动到其它分区，则该更新操作会因为检查限制的存在而失败。如果一定要处理这种情景，可以使用更新Trigger，但这会使得结构变得复杂。

· 大量的分区表会极大地增加查询计划时间。表分区在多达几百个分区表时能很好地发挥优势，但不要使用多达几千个分区表。