深度解析PostgreSQL-12分区表的一篇文章

姜瑞海

中国PG分会认证专家

PostgreSQL资深内核研发工程师

一、初识分区表

通常情况下，扫描一个大表会很慢。 例如，如果一个订单表orders的数据量是50G，统计某个州范围内订单的平均额度，往往会消耗几分钟的时间。

 select avg(total_amount) from orders where state_code=1;

如果能够把大表分拆成小表，查询数据的时猴，只扫描数据所属的小表，就能大大降低扫描时间，提高查询速度。

PostgreSQL的分区表(Table Partitioning)可以用来解决此类问题。解决方式是：创建一个表orders，作为分区表(partitionedtable)，再创建50个分区（partition)，orders_1, orders_2, …, orders_50， 每一个分区对应一个州的数据，分区的数据量平均是1G。分区表和分区都是表。本例中，这50分区联合在一起，组成分区表orders。在执行查询语句（如下）的时候：

select avg(total_amount) from orders where state_code=1;

PostgreSQL通过对执行语句的分析处理，最终把扫描的任务定位在分区order_1上，把查询语句转换成下面的语句，其他分区根本不需要扫描。

select avg(total_amount) from orders_1;

二、PostgreSQL分区表应用举例

温度采集在物联网应用中非常普遍，通常一个系统中部署大量的温度传感器，传感器按照设定的采集频率把温度数据发送到服务器。 下面是一个温度采集的例子，表temperature_sensor_data，用于保存温度传感器采集的温度数据。 如果有10万个传感器，每隔一小时采集一次数据，则每一个月会产生3.7G的数据，一年会产生大约43G的数据。

对于这样量级的数据，通常需要采用特殊的处理方式。一种可能的方式是：按照月创建分区，数据按照所属的月份，被存储到较小的分区。

2.1 创建分区表

在下面的例子中，创建了分区表temperature_sensor_data和12分区。分区表代表2017年全年的数据，而每一个分区代表单月的数据。

droptableifexists temperature_sensor_data ; CREATETABLEtemperature_sensor_data ( sensor_id integer NOTNULL, timestamp timestampNOTNULL,  temperature decimal(5,2) NOTNULL) PARTITION BY RANGE (timestamp); droptableifexists temperature_sensor_data_2017_1;CREATETABLEtemperature_sensor_data_2017_1 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-01-01') TO ('2017-02-01'); droptableifexists temperature_sensor_data_2017_2;CREATETABLEtemperature_sensor_data_2017_2 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-02-01') TO ('2017-03-01');  droptableifexists temperature_sensor_data_2017_3;CREATETABLEtemperature_sensor_data_2017_3 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-03-01') TO ('2017-04-01'); droptableifexists temperature_sensor_data_2017_4;CREATETABLEtemperature_sensor_data_2017_4 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-04-01') TO ('2017-05-01');  droptableifexists temperature_sensor_data_2017_5;CREATETABLEtemperature_sensor_data_2017_5 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-05-01') TO ('2017-06-01');  droptableifexists temperature_sensor_data_2017_6;CREATETABLEtemperature_sensor_data_2017_6 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-06-01') TO ('2017-07-01'); droptableifexists temperature_sensor_data_2017_7;CREATETABLEtemperature_sensor_data_2017_7 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-07-01') TO ('2017-08-01'); droptableifexists temperature_sensor_data_2017_8;CREATETABLEtemperature_sensor_data_2017_8 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-08-01') TO ('2017-09-01'); droptableifexists temperature_sensor_data_2017_9;CREATETABLEtemperature_sensor_data_2017_9 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-09-01') TO ('2017-10-01'); droptableifexists temperature_sensor_data_2017_10;CREATETABLEtemperature_sensor_data_2017_10 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-10-01') TO ('2017-11-01'); droptableifexists temperature_sensor_data_2017_11;CREATETABLEtemperature_sensor_data_2017_11 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-11-01') TO ('2017-12-01'); droptableifexists temperature_sensor_data_2017_12;CREATETABLEtemperature_sensor_data_2017_12 PARTITION OF temperature_sensor_data FORVALUESFROM ('2017-12-01') TO ('2018-01-01');

2.2 模拟加载数据

• 100000个传感器
• 每隔1小时采集一次数据
• 总共12个月
• 
  

with ids as ( select generate_series(1,100000) as sensor_id ),  times as ( SELECT generate_series( '2017-01-01 00:00:00'::timestamp,'2017-12-31 23:59:00', '1 hour' ) as timestamp ), samples as ( select sensor_id, timestamp, random()*100::decimal as temperature from ids full join times on 1=1 )insert into temperature_sensor_data select sensor_id, timestamp, round(temperature::decimal,2) as temperature from samples;postgres=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+---------------------------------+-------------------+-------------+---------+------------- public | temperature_sensor_data | partitioned table | postgres | 0 bytes |  public | temperature_sensor_data_2017_1 | table | postgres | 3703 MB |  public | temperature_sensor_data_2017_10 | table | postgres | 3703 MB |  public | temperature_sensor_data_2017_11 | table | postgres | 3584 MB |  public | temperature_sensor_data_2017_12 | table | postgres | 3703 MB |  public | temperature_sensor_data_2017_2 | table | postgres | 3345 MB |  public | temperature_sensor_data_2017_3 | table | postgres | 3703 MB |  public | temperature_sensor_data_2017_4 | table | postgres | 3584 MB |  public | temperature_sensor_data_2017_5 | table | postgres | 3703 MB |  public | temperature_sensor_data_2017_6 | table | postgres | 3584 MB |  public | temperature_sensor_data_2017_7 | table | postgres | 3703 MB |  public | temperature_sensor_data_2017_8 | table | postgres | 3703 MB |  public | temperature_sensor_data_2017_9 | table | postgres | 3584 MB | (13 rows)

2.3 统计1月份的平均温度

• 1月份的数据量是3703M
• 耗时大约33秒
• 
  

postgres=#selectavg(temperature) from temperature_sensor_data wheretimestampbetween '2017-01-01 00:00:00'and'2017-01-0123:59:00'; avg --------------------- 50.0171680480000000(1 row) Time: 33305.055 ms(00:33.305)postgres=#

2.4 使用一个大表，不使用分区表的查询结果

• 单个表数据量是43G
• 耗时大约7分51秒
• 
  

postgres=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+-------------------------+-------+-------------+-------+------------- public | temperature_sensor_data | table | postgres | 43 GB | (1 row)postgres=# select avg(temperature) from temperature_sensor_data where timestamp between '2017-01-01 00:00:00' and '2017-01-01 23:59:00'; avg --------------------- 50.0010354000000000(1 row)Time: 471373.514 ms (07:51.374)

三、使用DeclarativePartitioning定义分区表

PostgreSQL从版本10开始，支持DeclarativePartitioning功能，就是使用create table语句定义分区表和分区。

创建分区表的方式是：create table tablename (…) partition by (…)

创建分区的方式是： create table partitionname partition oftablename for values (…);

其中partition by (…)定义来分区表根据哪些列来分区，使用什么算法；for values (…)定义一个分区内，落入该分区的数据的取值范围。

目前PostgreSQL12提供来3种分区算法：

• partition by range(…)，pg10引入
• partition by list(…)，pg10引入
• parition by hash(…)，pg11引入

3.1 使用PARTITION BY RANGE方式定义分区

在创建分区表的时候，需要使用PARTITION BY指明该表是一个分区表，并且定义分区的方式。 以下是PostgreSQL官方文档中一个例子：

该例子中，根据logdate字段做分区，使用RANGE方式。分区表measurement对应3个分区：measurement_y2006m02，measurement_y2006m03，measurement_def。其中measurement_def是默认分区。

在插入数据的时候，如果logdate的取值在2016年2月份，则数据插入到分区measurement_y2006m02；如果logdate的取值在2016年3月份，则数据被插入到分区measurement_y2006m03；其它的数据，插入到默认分区measurement_def。

CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int) PARTITION BY RANGE (logdate);CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');CREATE TABLE measurement_def PARTITION OF measurement DEFAULT;

查询数据的时候，PostgreSQL能够根据合适的过滤条件，选择正确的分区做查询；如果没有适当的过滤条件，则扫描所有分区。

postgres=# explain select * from measurement where logdate='2006-02-10'; QUERY PLAN ---------------------------------------------------------------------- Seq Scan on measurement_y2006m02 (cost=0.00..33.12 rows=9 width=16) Filter: (logdate = '2006-02-10'::date)(2 rows)postgres=# explain select * from measurement; QUERY PLAN ------------------------------------------------------------------------------- Append (cost=0.00..113.25 rows=5550 width=16) -> Seq Scan on measurement_y2006m02 (cost=0.00..28.50 rows=1850 width=16) -> Seq Scan on measurement_y2006m03 (cost=0.00..28.50 rows=1850 width=16) -> Seq Scan on measurement_def (cost=0.00..28.50 rows=1850 width=16)(4 rows)

3.2 使用PARTITION BY LIST(column )定义分区

列的取值范围值是一个小的集合，类似编程中的枚举概念。当列值等于某个特定值的时候，落入指定的分区。

下面的例子中，分区表sale_order包含3个分区：

europe_order,asia_order,default_order。当列country等于'germany'或者'sweden'时，数据落入分区europe_order；当country的值等于india或japan时，行落入分区asia_order；当country等于其它值时，则行数据落入分区default_order。

CREATE TABLE sale_order( order_no integer,  store_no integer, country varchar(20), date date, amount decimal(5,2)) PARTITION BY LIST(country);CREATE TABLE europe_order PARTITION OF sale_order FOR VALUES IN ('germany', 'sweden');CREATE TABLE asia_order PARTITION OF sale_order FOR VALUES IN ('india', 'japan');CREATE TABLE default_order PARTITION OF sale_order DEFAULT;

查询数据的时候，PostgreSQL能够根据合适的过滤条件，选择正确的分区做查询；如果没有适当的过滤条件，则扫描所有分区。

postgres=#explain select * from sale_order where country='india'; QUERY PLAN ------------------------------------------------------------ Seq Scan on asia_order (cost=0.00..19.25 rows=4 width=82) Filter: ((country)::text = 'india'::text)(2 rows) postgres=#explain select * from sale_order; QUERY PLAN ----------------------------------------------------------------------- Append (cost=0.00..63.30 rows=2220 width=82) -> Seq Scan on europe_order (cost=0.00..17.40 rows=740 width=82) -> Seq Scan on asia_order (cost=0.00..17.40 rows=740 width=82) -> Seq Scan on default_order (cost=0.00..17.40 rows=740 width=82)(4 rows)

3.3 使用PARTITION BY HASH( column )定义分区

对列的值做哈希，哈希值把数据分割成几个分区。

下面的例子中，分区表orders包含4个分区：orders_p1，orders_p2，orders_p3，orders_p4。

插入数据时，对列o_w_id取余，结果等于0,1,2,3，行数据分别落入分区orders_p1, orders_p2, orders_p3，orders_p4。

createtableorders ( o_w_id integer notnull, o_d_id integer notnull, o_id integer notnull, o_c_id integer, o_carrier_id integer, o_ol_cnt integer, o_all_local integer, o_entry_d timestamp)PARTITIONBY HASH ( o_w_id ); CREATETABLEorders_p1 PARTITION OF orders FORVALUESWITH (MODULUS 4, REMAINDER 0);CREATETABLEorders_p2 PARTITION OF orders FORVALUESWITH (MODULUS 4, REMAINDER 1);CREATETABLEorders_p3 PARTITION OF orders FORVALUESWITH (MODULUS 4, REMAINDER 2);CREATETABLEorders_p4 PARTITION OF orders FORVALUESWITH (MODULUS 4, REMAINDER 3);

3.4 分区的其它特性

• 可以在分区表上建立索引，相应的所有分区都能自动建立索引；或者，可以为分区单独建立索引。
• 可以根据需要，卸载或这增加一个分区。
• 分区可以指定单独的表空间，能充分利用多个磁盘。
• 分区可以指向一个PG外表，即FDW表。
• 分区表可以根据多个列的值来分区。
• 分区可以再次分区。

四、使用表继承(Inheritance)方式定义分区表

分区表也可以使用继承的方式来使用。该方式早在PostgreSQL8就支持了。创建方式举例如下：

• 1. 创建一个普通表measurement
• 
  

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

• 2. 创建子表，继承自measurement
• 每个子表的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_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);

• 3. 创建函数和触发器，用于把数据插入到相应的分区。
• 
  

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 '2007-12-01' AND NEW.logdate < DATE '2008-01-01' ) THEN INSERT INTO measurement_y2007m12 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;CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();

• 4. 插入数据

插入4条数据，应该分别落入4个子表。

insertinto measurement  values (1, '2006-02-10', 10, 1), (1, '2006-03-10', 10, 1), (1, '2007-12-10', 10, 1), (1, '2008-01-10', 10, 1);

• 5. 查询数据
• 
  

postgres=#select * from measurement; city_id | logdate | peaktemp | unitsales ---------+------------+----------+----------- 1 | 2006-02-10 | 10 | 1 1 | 2006-03-10 | 10 | 1 1 | 2007-12-10 | 10 | 1 1 | 2008-01-10 | 10 | 1(4 rows) postgres=#select * from measurement_y2006m02; city_id | logdate | peaktemp | unitsales ---------+------------+----------+----------- 1 | 2006-02-10 | 10 | 1(1 row) postgres=#select * from measurement_y2006m03; city_id | logdate | peaktemp | unitsales ---------+------------+----------+----------- 1 | 2006-03-10 | 10 | 1(1 row) postgres=#select * from measurement_y2007m12; city_id | logdate | peaktemp | unitsales ---------+------------+----------+----------- 1 | 2007-12-10 | 10 | 1(1 row) postgres=#select * from measurement_y2008m01 ; city_id | logdate | peaktemp | unitsales ---------+------------+----------+----------- 1 | 2008-01-10 | 10 | 1(1 row) postgres=#explain select * from measurement; QUERYPLAN ------------------------------------------------------------------------------- Append (cost=0.00..151.00 rows=7401 width=16) -> Seq Scan on measurement (cost=0.00..0.00 rows=1 width=16) -> Seq Scan on measurement_y2006m02 (cost=0.00..28.50 rows=1850 width=16) -> Seq Scan on measurement_y2006m03 (cost=0.00..28.50 rows=1850 width=16) -> Seq Scan on measurement_y2007m12 (cost=0.00..28.50 rows=1850 width=16) -> Seq Scan on measurement_y2008m01 (cost=0.00..28.50 rows=1850 width=16)(6 rows) postgres=#explain select * from measurement where logdate='2007-01-10'; QUERY PLAN ------------------------------------------------------------ Seq Scan on measurement (cost=0.00..0.00 rows=1 width=16) Filter: (logdate = '2007-01-10'::date)(2 rows)