PostgreSQL分区表

最新推荐文章于 2023-08-02 18:39:41 发布

我躲过世俗的雨

最新推荐文章于 2023-08-02 18:39:41 发布

阅读量4.1k

点赞数 2

分类专栏： PostgreSQL数据库文章标签：数据库

原文链接：https://zhuanlan.zhihu.com

版权

PostgreSQL数据库专栏收录该内容

15 篇文章 4 订阅

订阅专栏

PostgreSQL分区表

声明：内容转载别处，觉得有用就分享了出来

为什么需要分区表？

首先我们需要思考一个问题，为什么需要分区表？

是因为Big Table的性能差，而且容量有上限，所以才需要分而治之。一张表最大的容量是2³²的Page页面，而Page默认值是8K，所以一张表最大的上限是32TB，如果你把Page大小改成32KB，则单表大小可以达到128TB。但是在使用分区表的情况下，一个表可以获得2³²个子表，每个子表的大小又能达到2³² * 8192字节。所以使用了分区表就不用担心容量上限的问题了。

至于性能：当大量的insert情况下，如果存在索引，且存在自动增长列的时候，表一直增大也没有性能问题。而一旦表上的索引，没有自动增长列处于随机插入的时候，表大小达到6GB后，TPS就急剧下降好几倍，这种下降严重影响性能。

PostgreSQL分区表介绍

PostgreSQL的分区表有两种形式，一种是声明式分区，在PostgreSQL 10版本开始支持。另外一种是表继承的方式，是10版本以前主要使用方式。两者的区别就是使用继承的方式需要使用约束来定义分区和规则，还需要使用触发器将数据路由到适合的分区，用户必须编写和维护这些代码。

以下是表继承的实现步骤：

创建父表，所有分区都将从该表继承。
创建几个子表(每个子表代表数据分区)，每个表均从父表继承。
向分区表添加约束，以定义每个分区中的行值。
分别在父表和子表上创建索引。（索引不会从父表传播到子表）。
在主表上创建一个合适的触发器函数，以便将插入到父表中的内容重定向到相应的分区表中。
创建一个调用该触发器函数的触发器。

注：当子表规则发生变化时，要重新定义触发器函数。

表继承相当的复杂。而声明式分区则简单许多。它的实现步骤如下：

使用PARTITION BY子句创建一个分区表，该子句包含了分区方法，可以使用Range、List、Hash三种方式。
创建分区表后，可以手动创建子分区。
然后在子分区上创建索引。

声明式分区大大简化了表继承的若干过程，对用户来说更简单。插入自动化（勿需触发器）和直接对分区表进行操作更加简单和人性化。

下图是各个版本对分区功能的支持，在PostgreSQL 10版本中，声明式分区功能还比较弱，要到11版本声明式分区才会有大幅的提升。

PostgreSQL分区表基本使用

接下来使用一下PostgreSQL的分区表功能，采用的是声明式分区方式。

List分区

列表分区明确指定根据某字段的某个具体值进行分区，默认分区（可选值）保存不属于任何指定分区的列表值。

CREATE TABLE students (id INTEGER, status character varying(30), name character varying(30)) PARTITION BY LIST(status);
CREATE TABLE stu_active PARTITION OF students FOR VALUES IN ('ACTIVE');
CREATE TABLE stu_exp PARTITION OF students FOR VALUES IN ('EXPIRED');
CREATE TABLE stu_others PARTITION OF students DEFAULT;

postgres=# \d+ students
                                   Partitioned table "public.students"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 name   | character varying(30) |           |          |         | extended |              | 
Partition key: LIST (status)
Partitions: stu_active FOR VALUES IN ('ACTIVE'),
            stu_exp FOR VALUES IN ('EXPIRED'),
            stu_others DEFAULT

postgres=# \d+ stu_active
                                        Table "public.stu_active"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 name   | character varying(30) |           |          |         | extended |              | 
Partition of: students FOR VALUES IN ('ACTIVE')
Partition constraint: ((status IS NOT NULL) AND ((status)::text = 'ACTIVE'::character varying(30)))
Access method: heap

postgres=# INSERT INTO students VALUES (1,'ACTIVE','zhangyit'), (2,'EXPIRED','lias'), (3,'EXPIRED','wangwei'), (4,'REACTIVATED','liulah');

postgres=# select tableoid::regclass,* from students;
  tableoid  | id |   status    |   name   
------------+----+-------------+----------
 stu_active |  1 | ACTIVE      | zhangyit
 stu_exp    |  2 | EXPIRED     | lias
 stu_exp    |  3 | EXPIRED     | wangwei
 stu_others |  4 | REACTIVATED | liulah

Range分区

范围分区就是根据某个字段值的范围来进行分区。

CREATE TABLE students (id INTEGER, status character varying(30), grade INTEGER) PARTITION BY RANGE(grade);
CREATE TABLE stu_fail PARTITION OF students FOR VALUES FROM (MINVALUE) TO (60);
CREATE TABLE stu_pass PARTITION OF students FOR VALUES FROM (60) TO (75);
CREATE TABLE stu_excellent PARTITION OF students FOR VALUES FROM (75) TO (MAXVALUE);

postgres=# \d+  students
                                   Partitioned table "public.students"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Partition key: RANGE (grade)
Partitions: stu_excellent FOR VALUES FROM (75) TO (MAXVALUE),
            stu_fail FOR VALUES FROM (MINVALUE) TO (60),
            stu_pass FOR VALUES FROM (60) TO (75)

postgres=# \d+ stu_fail
                                         Table "public.stu_fail"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Partition of: students FOR VALUES FROM (MINVALUE) TO (60)
Partition constraint: ((grade IS NOT NULL) AND (grade < 60))
Access method: heap

postgres=# \d+ stu_pass
                                         Table "public.stu_pass"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Partition of: students FOR VALUES FROM (60) TO (75)
Partition constraint: ((grade IS NOT NULL) AND (grade >= 60) AND (grade < 75))
Access method: heap

postgres=# \d+ stu_excellent
                                      Table "public.stu_excellent"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Partition of: students FOR VALUES FROM (75) TO (MAXVALUE)
Partition constraint: ((grade IS NOT NULL) AND (grade >= 75))
Access method: heap

postgres=# INSERT INTO students VALUES (1,'ACTIVE',58), (2,'ACTIVE',60), (3,'ACTIVE',75), (4,'ACTIVE',90);
INSERT 0 4
postgres=# select tableoid::regclass,* from students;
   tableoid    | id | status | grade 
---------------+----+--------+-------
 stu_fail      |  1 | ACTIVE |    58
 stu_pass      |  2 | ACTIVE |    60
 stu_excellent |  3 | ACTIVE |    75
 stu_excellent |  4 | ACTIVE |    90

注意：minvalue的上限值是小于，不是小于等于。而maxvalue的区间的下限是大于等于，不是大于。

Hash分区

通过对每个分区使用取模和余数来创建hash分区，modulus指定了对N取模，而remainder指定了除完后的余数。

CREATE TABLE students (id INTEGER, status character varying(30), grade INTEGER) PARTITION BY HASH(id);
CREATE TABLE stu_part1 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE stu_part2 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE stu_part3 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 2);

postgres=# \d+ stu_part1
                                        Table "public.stu_part1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Partition of: students FOR VALUES WITH (modulus 3, remainder 0)
Partition constraint: satisfies_hash_partition('20026077'::oid, 3, 0, id)
Access method: heap

postgres=# \d+ stu_part2
                                        Table "public.stu_part2"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Partition of: students FOR VALUES WITH (modulus 3, remainder 1)
Partition constraint: satisfies_hash_partition('20026077'::oid, 3, 1, id)
Access method: heap

postgres-# \d+ stu_part3
                                        Table "public.stu_part3"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Partition of: students FOR VALUES WITH (modulus 3, remainder 2)
Partition constraint: satisfies_hash_partition('20026077'::oid, 3, 2, id)
Access method: heap

postgres=# INSERT INTO students VALUES (1,'ACTIVE',58), (2,'ACTIVE',60), (3,'ACTIVE',75), (4,'ACTIVE',90);
INSERT 0 4
postgres=# select tableoid::regclass,* from students;s
 tableoid  | id | status | grade 
-----------+----+--------+-------
 stu_part1 |  2 | ACTIVE |    60
 stu_part1 |  4 | ACTIVE |    90
 stu_part2 |  3 | ACTIVE |    75
 stu_part3 |  1 | ACTIVE |    58

我们还可以建立子分区，也就是多级分区，使用PostgreSQL声明性分区的创建子分区方法有：LIST-LIST，LIST-RANGE，LIST-HASH，RANGE-RANGE，RANGE-LIST，RANGE-HASH，HASH-HASH，HASH-LIST和HASH-RANGE。

分区表转换成普通表

我们可以使用DETACH PARTITION命令把分区表转换成普通表。

postgres=# alter table students detach partition stu_part3;
ALTER TABLE

postgres=# \d+ students
                                   Partitioned table "public.students"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Partition key: HASH (id)
Partitions: stu_part1 FOR VALUES WITH (modulus 3, remainder 0),
            stu_part2 FOR VALUES WITH (modulus 3, remainder 1)

postgres=# \d+ stu_part3
                                        Table "public.stu_part3"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Access method: heap

普通表转换成分区表

我们也可以把某个普通表在附加到分区表上去，使用attach partition命令。

postgres=# alter table students attach partition stu_part3 FOR VALUES WITH (modulus 3, remainder 2);
ALTER TABLE

postgres=# \d+ students
                                   Partitioned table "public.students"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Partition key: HASH (id)
Partitions: stu_part1 FOR VALUES WITH (modulus 3, remainder 0),
            stu_part2 FOR VALUES WITH (modulus 3, remainder 1),
            stu_part3 FOR VALUES WITH (modulus 3, remainder 2)

postgres=# \d+ stu_part3
                                        Table "public.stu_part3"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 status | character varying(30) |           |          |         | extended |              | 
 grade  | integer               |           |          |         | plain    |              | 
Partition of: students FOR VALUES WITH (modulus 3, remainder 2)
Partition constraint: satisfies_hash_partition('20026077'::oid, 3, 2, id)
Access method: heap

附加上去的表必须具有相同的列/类型，不能存在额外的列。

在运行ATTACH PARTITION命令之前，建议在要附加的表上创建一个与所需分区约束匹配的约束。这样，系统将能够跳过扫描以验证隐式分区约束。如果没有约束，则将扫描附加表以验证分区约束正确性，同时对该分区持有ACCESS EXCLUSIVE锁定，并在父表上持有SHARE UPDATE EXCLUSIVE锁。在完成ATTACH PARTITION后，可能需要删除冗余CHECK约束。

分区裁剪

分区裁剪就是当已知分区不包含我们要的数据时，在查询的时候就不扫描这个分区，以获得更高的性能。在PostgreSQL 10中，这种消除是通过constraint_exclusion机制来实现的，这种机制是一种线性算法，需要一个一个地查看每个分区的元数据，以检查该分区是否与查询WHERE子句匹配，很显然这种效率并不高。而在PostgreSQL 11版本引入了新的算法，不再是详尽的搜索，而使用二分查找法搜索，可以快速识别匹配的LIST和RANGE分区，而HASH分区则通过散列函数查找匹配的分区。

PostgreSQL 11版本该功能通过参数enable_partition_pruning进行控制。默认是打开的。

postgres=# explain analyze select * from students where id in (1,2);
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..37.82 rows=14 width=86) (actual time=0.048..0.066 rows=2 loops=1)
   ->  Seq Scan on stu_part1  (cost=0.00..18.88 rows=7 width=86) (actual time=0.046..0.049 rows=1 loops=1)
         Filter: (id = ANY ('{1,2}'::integer[]))
         Rows Removed by Filter: 1
   ->  Seq Scan on stu_part3  (cost=0.00..18.88 rows=7 width=86) (actual time=0.013..0.014 rows=1 loops=1)
         Filter: (id = ANY ('{1,2}'::integer[]))
 Planning Time: 0.309 ms
 Execution Time: 0.118 ms
(8 rows)

postgres=# set enable_partition_pruning=off;
SET
postgres=# explain analyze select * from students where id in (1,2);
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..56.73 rows=21 width=86) (actual time=0.054..0.070 rows=2 loops=1)
   ->  Seq Scan on stu_part1  (cost=0.00..18.88 rows=7 width=86) (actual time=0.049..0.052 rows=1 loops=1)
         Filter: (id = ANY ('{1,2}'::integer[]))
         Rows Removed by Filter: 1
   ->  Seq Scan on stu_part2  (cost=0.00..18.88 rows=7 width=86) (actual time=0.006..0.006 rows=0 loops=1)
         Filter: (id = ANY ('{1,2}'::integer[]))
         Rows Removed by Filter: 1
   ->  Seq Scan on stu_part3  (cost=0.00..18.88 rows=7 width=86) (actual time=0.005..0.006 rows=1 loops=1)
         Filter: (id = ANY ('{1,2}'::integer[]))
 Planning Time: 0.322 ms
 Execution Time: 0.133 ms
(11 rows)

参数为on的时候，只扫描了2个分区。而将参数改成off之后，会对所有表分区进行扫描。

Run-time 分区剪裁

PostgreSQL 11还有一项新技术就是Run-time分区剪裁，就是在执行时对分区进行裁剪。在某些情况下，这是一个优点。例如PREPARE查询，子查询的值、嵌套循环连接的内层参数值，这些都只有在SQL运行的时候才能知道具体的值。Run-time 分区剪裁发生有以下两种类型。

第一类，就是执行期间参数不变的情况。

下面案例将参数$ 1设置为70，并且该值在整个查询执行过程中不会更改，初始化Append节点时会进行剪裁。

[postgres@paas-telepg-test3 ~]$ psql
psql (12.3)
Type "help" for help.

postgres=# PREPARE a1 (INT) AS SELECT * FROM students WHERE grade<$1;   
PREPARE
postgres=# explain analyze execute a1(70);
                                                 QUERY PLAN                                                 
-------------------------------------------------------------------------------
 Append  (cost=0.00..40.12 rows=474 width=86) (actual time=0.012..0.012 rows=0 loops=1)
   ->  Seq Scan on stu_fail  (cost=0.00..18.88 rows=237 width=86) (actual time=0.007..0.007 rows=0 loops=1)
         Filter: (grade < 70)
   ->  Seq Scan on stu_pass  (cost=0.00..18.88 rows=237 width=86) (actual time=0.001..0.001 rows=0 loops=1)
         Filter: (grade < 70)
 Planning Time: 1.636 ms
 Execution Time: 0.128 ms
(7 rows)

第二类，就是执行期间值可能动态发生变化，例如子查询可能查到多个值，因此在运行期间每次参数值更改时都将执行修剪。如果要排除分区，则该子计划的EXPLAIN输出中将显示（never executed）。

postgres=# explain analyze SELECT * FROM students WHERE id = (select 3);
                                                QUERY PLAN                                                 
-------------------------------------------------------------------------------
 Append  (cost=0.01..56.70 rows=12 width=86) (actual time=0.046..0.048 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)
   ->  Seq Scan on stu_part1  (cost=0.00..18.88 rows=4 width=86) (never executed)
         Filter: (id = $0)
   ->  Seq Scan on stu_part2  (cost=0.00..18.88 rows=4 width=86) (actual time=0.026..0.028 rows=1 loops=1)
         Filter: (id = $0)
   ->  Seq Scan on stu_part3  (cost=0.00..18.88 rows=4 width=86) (never executed)
         Filter: (id = $0)
 Planning Time: 0.387 ms
 Execution Time: 0.125 ms
(11 rows)

根据2ndquadrant提供的Run-time分区剪裁的基准测试结果，在分区数量少的情况下，PG12的性能要明显好于11，但是分区达到一定的数量之后，PG12的性能也会下降。

enable_partitionwise_join

允许查询优化器使用面向分区的连接，当分区表和分区表进行连接的时候，直接让分区和分区进行连接。面向分区的连接当前只适用于连接条件包括所有分区键的情况，连接条件必须是相同的数据类型并且子分区集合要完全匹配。由于面向分区的连接在规划期间会使用一些CPU Time和内存，所以默认值为off。

接下来我们来造点数据测试一下这个功能，分别创建a1和a2两张分区表，造300万数据。

CREATE TABLE a1 (a int primary key, b text) PARTITION BY RANGE (a);
CREATE TABLE a1_1 PARTITION OF a1 FOR VALUES FROM (1) TO (1000001);
CREATE TABLE a1_2 PARTITION OF a1 FOR VALUES FROM (1000001) TO (2000001);
CREATE TABLE a1_3 PARTITION OF a1 FOR VALUES FROM (2000001) TO (3000001);


CREATE TABLE a2 (c int primary key, d text) PARTITION BY RANGE (c);
CREATE TABLE a2_1 PARTITION OF a2 FOR VALUES FROM (1) TO (1000001);
CREATE TABLE a2_2 PARTITION OF a2 FOR VALUES FROM (1000001) TO (2000001);
CREATE TABLE a2_3 PARTITION OF a2 FOR VALUES FROM (2000001) TO (3000001);


INSERT INTO a1 SELECT a, md5((current_timestamp)::text) FROM generate_series(1,3000000) as a;
INSERT INTO a2 SELECT a, md5((current_timestamp)::text) FROM generate_series(1,3000000) as a;

analyze a1;
analyze a2;

然后a1和a2表执行左联接查询，当enable_partitionwise_join参数是off的时候，会单独对每个分区表上的分区索引进行扫描，然后再merge。而打开enable_partitionwise_join参数后，分区索引之间就会直接进行连接。

从实际执行效果上来看，打开参数执行速度更快。但是为什么参数默认是关闭的呢？这是因为使用这项功能有限制。

只有非常特定的查询才能从中受益，表需要相同的分区，并且分区键必须是连接条件的一部分。而启用该参数在执行计划规划期间会增加一些CPU time和内存的消耗。由于PostgreSQL通常不缓存执行计划，所以说开启这个参数来说收益不大。所以该参数默认就是禁用的。

enable_partitionwise_aggregate

允许查询优化器使用面向分区的分组和聚合，这样使得分区表上的分组和聚合可以在每个分区上分别执行。

我们使用上面创建的表a1，来做一个count统计。

可以看到当参数enable_partitionwise_aggregate设置为off的时候，会先扫描分区数据，在汇总进行统计。而参数设置为on的情况下，就会直接对分区进行聚合统计。

而这个参数默认也是不启动的，同样是因为在执行计划规划期间会增加一些CPU时间和内存的消耗。

声明式分区最佳实践

最后我们来探讨一下声明式分区最佳实践，明智的选择分区策略，需要考虑以下几件事。

快速的删除卸载旧数据。例如保留最近一年的数据，就可以设计按月分区，现在是12月份，就可以把去年11月份的数据分区给卸载掉。
根据where子句的条件，设计分区规则。where子句的条件与分区键能匹配兼容，这是最好的情况，这样就可以快速的帮助执行计划进行分区裁剪，扫描更少的分区就能更快的执行查询。
分区数量也是一个重要的指标，没有足够的分区，就会导致索引仍然很大，数据位置仍然较差，从而导致缓存命中率低。但是将分区划分太多也会有问题，在查询规划和执行期间，过多的分区会导致查询计划时间更长，内存消耗也更高。同时大量的分区也会导致UPDATE/DELETE变慢。如果很多会话需要访问大量的分区，服务器的内存消耗也会显著增加，这是因为每个分区都需要将元数据加载到每个会话的本地内存中。

总结

在这篇文章中介绍了分区表的使用，及一些优化参数，还有最佳实践。在PostgreSQL版本不停的发展下，分区表的功能已经得到了大幅增强。

我躲过世俗的雨

关注

2
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
PostgreSQL分区表

PostgreSQL分区表声明：内容转载别处，觉得有用就分享了出来为什么需要分区表？首先我们需要思考一个问题，为什么需要分区表？是因为Big Table的性能差，而且容量有上限，所以才需要分而治之。一张表最大的容量是232的Page页面，而Page默认值是8K，所以一张表最大的上限是32TB，如果你把Page大小改成32KB，则单表大小可以达到128TB。但是在使用分区表的情况下，一个表可以获得232个子表，每个子表的大小又能达到232 * 8192字节。所以使用了分区表就不用担心容量上限的问题了
复制链接

扫一扫

专栏目录