PostgreSQL11: 分区表增加 Default Partition

PostgreSQL 10 版本当往分区表写入的数据不在分区定义范围内时会报错,无法插入数据,PostgreSQL 11 版本分区表增加了 Default 分区用来存储不满足分区定义范围的数据。

本节以创建范围分区表为例进行测试。

PostgreSQL 10 版本

在PostgreSQL 10 版本进行测试,详见以下。

创建父表

1
2
3
4
5
create table tbl_log (
id serial,
create_time timestamp(0) without time zone,
remark char(1)
) PARTITION BY RANGE(create_time);

创建子表

1
2
CREATE TABLE tbl_log_p201801 PARTITION OF tbl_log FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');
CREATE TABLE tbl_log_p201802 PARTITION OF tbl_log FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');

插入测试

PostgreSQL 10 版本插入一条不在分区定义范围的记录,如下

1
2
3
mydb=> INSERT INTO tbl_log(id,create_time,remark) VALUES (3,'2018-03-01','a');
ERROR: no partition of relation "tbl_log" found for row
DETAIL: Partition key of the failing row contains (create_time) = (2018-03-01 00:00:00).

插入报错,提示找不到相应分区无法插入数据。

PostgreSQL 11 版本

PostgreSQL 11 版本支持创建 Default 分区,用来存储不满足分区定义的数据,测试如下。

创建父表

1
2
3
4
5
create table tbl_log (
id serial,
create_time timestamp(0) without time zone,
remark char(1)
) PARTITION BY RANGE(create_time);

创建常规分区

1
2
CREATE TABLE tbl_log_p201801 PARTITION OF tbl_log FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');
CREATE TABLE tbl_log_p201802 PARTITION OF tbl_log FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');

创建默认分区

1
CREATE TABLE tbl_log_default PARTITION OF tbl_log DEFAULT;

查看表tbl_log定义

1
2
3
4
5
6
7
8
9
10
11
12
francs=> \d+ tbl_log
Table "francs.tbl_log"
Column | Type | Collation | Nullable | Default | Storage | Stats target
| Description
-------------+--------------------------------+-----------+----------+-------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('tbl_log_id_seq'::regclass) | plain | |
create_time | timestamp(0) without time zone | | | | plain | |
remark | character(1) | | | | extended | |
Partition key: RANGE (create_time)
Partitions: tbl_log_p201801 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00'),
tbl_log_p201802 FOR VALUES FROM ('2018-02-01 00:00:00') TO ('2018-03-01 00:00:00'),
tbl_log_default DEFAULT

以上看出 tbl_log 表包含三个分区,其中一个是 DEFAULT 分区。

插入数据

1
2
francs=> INSERT INTO tbl_log(id,create_time,remark) VALUES (3,'2018-03-01','a');
INSERT 0 1

验证数据

可以直接查询子表数据验证,如下:

1
2
3
4
5
francs=> SELECT * FROM tbl_log_default ;
id | create_time | remark
----+---------------------+--------
3 | 2018-03-01 00:00:00 | a
(1 row)

也可以统计各子表记录总数统计,如下:

1
2
3
4
5
6
7
8
9
francs=> SELECT table_name,count(*) 
FROM tbl_log, LATERAL (
SELECT relname FROM pg_class WHERE pg_class.oid=tbl_log.tableoid) AS table_name
GROUP BY table_name
ORDER BY table_name;
table_name | count
-------------------+-------
(tbl_log_default) | 1
(1 row)

从以上测试看出,不满足分区定义的数据存储到了DEFAULT分区,那么问题来了,定义了DEFAULT分区的分区表如何添加分区?

定义了Default分区的分区表如何添加分区?

出于管理或业务需求,需要给分区表 tbl_log 新增 tbl_log_p201803 分区用来存储 2018年3月份的日志数据,如下。

1
2
francs=> CREATE TABLE tbl_log_p201803 PARTITION OF tbl_log FOR VALUES FROM ('2018-03-01') TO ('2018-04-01');
ERROR: updated partition constraint for default partition "tbl_log_default" would be violated by some row

以上添加分区报错,需要解绑default分区,之后再添加,如下:

解绑Default分区

1
2
francs=> ALTER TABLE tbl_log DETACH PARTITION tbl_log_default;
ALTER TABLE

之后再创建新分区 tbl_log_p201803。

创建新分区

1
2
francs=> CREATE TABLE tbl_log_p201803 PARTITION OF tbl_log FOR VALUES FROM ('2018-03-01') TO ('2018-04-01');
CREATE TABLE

分区创建成功,分区创建之后需把DEFAULT分区连接。

连接Default分区

连接DEFAULT分区报错,如下:

1
2
francs=> ALTER TABLE tbl_log ATTACH PARTITION tbl_log_default DEFAULT;
ERROR: partition constraint is violated by some row

以上是由于 2018-03-01 的记录存储在了DEFAULT分区上,根据分区规则这条记录应该存储到 tbl_log_p201803 分区。

需将 tbl_log_default 数据转移到 tbl_log_p201803 分区,如下:

1
2
3
4
5
francs=>  INSERT INTO tbl_log_p201803 SELECT * FROM tbl_log_default ;
INSERT 0 1

francs=> DELETE FROM tbl_log_default ;
DELETE 1

数据量大的话建议使用 TRUNCATE 清除数据,不产生垃圾数据。

再次连接DEFAULT分区成功,如下。

1
2
francs=> ALTER TABLE tbl_log ATTACH PARTITION tbl_log_default DEFAULT;
ALTER TABLE

查看表定义

再次查看分区表定义,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
francs=> \d+ tbl_log
Table "francs.tbl_log"
Column | Type | Collation | Nullable | Default | Storage | Stats target
| Description
-------------+--------------------------------+-----------+----------+-------------------------------------+----------+-------------

id | integer | | not null | nextval('tbl_log_id_seq'::regclass) | plain | |
create_time | timestamp(0) without time zone | | | | plain | |
remark | character(1) | | | | extended | |
Partition key: RANGE (create_time)
Partitions: tbl_log_p201801 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00'),
tbl_log_p201802 FOR VALUES FROM ('2018-02-01 00:00:00') TO ('2018-03-01 00:00:00'),
tbl_log_p201803 FOR VALUES FROM ('2018-03-01 00:00:00') TO ('2018-04-01 00:00:00'),
tbl_log_default DEFAULT

tbl_log_p201803 分区添加成功。

总结

  • PostgreSQL 11 版本分区表增加了DEFAULT分区,支持将不满足分区定义的数据存储到默认分区。
  • 对于添加了DEFAULT分区的分区表如果想增加分区,需参照“解绑DEFAULT分区,创建新分区,转移分区数据,连接DEFAULT分区”的步骤进行。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%