PostgreSQL 11 新特性解读 : 分区表增加 Default Partition

  1. 云栖社区>
  2. PostgreSQL技术进阶>
  3. 博客>
  4. 正文

PostgreSQL 11 新特性解读 : 分区表增加 Default Partition

francs.tan 2018-11-27 09:53:14 浏览1943
展开阅读全文

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

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

PostgreSQL 10 版本

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

创建父表

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

创建子表

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 版本插入一条不在分区定义范围的记录,如下

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 分区,用来存储不满足分区定义的数据,测试如下。

创建父表

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

创建常规分区

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');

创建默认分区

CREATE TABLE tbl_log_default PARTITION OF tbl_log DEFAULT;

查看表tbl_log定义

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 分区。

插入数据

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

验证数据

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

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

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

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月份的日志数据,如下。

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分区

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

之后再创建新分区 tbl_log_p201803。

创建新分区

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分区报错,如下:

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 分区,如下:

francs=>  INSERT INTO tbl_log_p201803 SELECT * FROM tbl_log_default ;
INSERT 0 1

francs=> DELETE FROM  tbl_log_default ;
DELETE 1

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

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

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

查看表定义

再次查看分区表定义,

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

_5_PostgreSQL_

网友评论

登录后评论
0/500
评论
francs.tan
+ 关注
所属云栖号: PostgreSQL技术进阶