PostgreSQL 11 新特性解读 : 分区表支持UPDATE分区键

简介:
+关注继续查看

PostgreSQL 10 版本已支持分区表,但不支持分区表根据分区键UPDATE记录,PostgreSQL 11 版本这方面得到增加,当分区表的分区键字段被UPDATE后,会自动将该记录转移至新的分区中。

官网Release说明如下:

UPDATE statements that change a partition key now move affected rows to the appropriate partitions

本文以UPDATE列表分区表分区键为例进行演示。

测试环境准备

创建列表分区表并插入测试数据,为演示做准备。

创建父表

CREATE TABLE cities (
    city_id      bigserial not null,
    name         text not null,
    population   bigint
) PARTITION BY LIST (left(lower(name), 1));

创建子表

CREATE TABLE cities_a PARTITION OF cities ( CONSTRAINT city_id_nonzero CHECK (city_id != 0)) FOR VALUES IN ('a');
CREATE TABLE cities_b PARTITION OF cities ( CONSTRAINT city_id_nonzero CHECK (city_id != 0)) FOR VALUES IN ('b');
CREATE TABLE cities_c PARTITION OF cities ( CONSTRAINT city_id_nonzero CHECK (city_id != 0)) FOR VALUES IN ('c');

插入测试数据

INSERT INTO cities(city_id,name,population) VALUES (1,'a_city','100000');
INSERT INTO cities(city_id,name,population) VALUES (2,'b_city','200000');
INSERT INTO cities(city_id,name,population) VALUES (3,'c_city','300000');

PostgreSQL 10 测试

PostgreSQL 10 版本UPDATE分区键报错,如下:

mydb=> SELECT version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
(1 row)


mydb=> UPDATE cities SET name='ca_city' WHERE city_id=1;
ERROR:  new row for relation "cities_a" violates partition constraint
DETAIL:  Failing row contains (1, ca_city, 100000).

PostgreSQL 11 测试

PostgreSQL 11 版本支持更新分区键,如下:

francs=> SELECT version();
                                                  version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
(1 row)

francs=> UPDATE cities SET name='ca_city' WHERE city_id=1;
UPDATE 1

francs=> SELECT * FROM cities;
 city_id |  name   | population
---------+---------+------------
       2 | b_city  |     200000
       3 | c_city  |     300000
       1 | ca_city |     100000
(3 rows)

查看cities_c分区,发现city_id为1的记录已转移到此分区,如下:

francs=> SELECT * from cities_c;
 city_id |  name   | population
---------+---------+------------
       3 | c_city  |     300000
       1 | ca_city |     100000
(2 rows)

参考

新书推荐

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

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
9月前
|
关系型数据库 数据库 数据安全/隐私保护
RDS 5.6 执行update更新报ERROR 1142 (42000)
RDS 5.6 执行update更新报ERROR 1142 (42000)
RDS 5.6 执行update更新报ERROR 1142 (42000)
|
SQL 弹性计算 Oracle
PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量
标签 PostgreSQL , 分区表 , native partition , 性能 , pg_pathman , plpgsql , 动态SQL , 服务端绑定变量 , prepare , execute 背景 目前PG的native partition分区性能不佳,一种解决方法是使用pg_pathman插件,另一种方法是业务上直接插分区,还有一种方法是使用UDF函数接口(函数内部使
1160 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL Heap Only Tuple - HOT (降低UPDATE引入的索引写IO放大)
标签 PostgreSQL , Heap Only Tuple , HOT 背景 PostgreSQL目前默认的存储引擎在更新记录时,会在堆内产生一条新版本,旧版本在不需要使用后VACUUM回收,回收旧版本前,需要先回收所有关联这个版本的所有索引POINT。
2306 0
|
SQL 关系型数据库 测试技术
PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化
标签 PostgreSQL , pgbench , tpcb 背景 pgbench是PG的一款测试工具,内置的测试CASE为tpcb测试。同时支持用户自己写测试CASE。 大量自定义CASE参考 https://github.com/digoal/blog/blob/master/201711/readme.md 当我们使用tpcb测试CASE时,如果生成的数据量过于庞大,例如我最近在生成1万亿的CASE,可以考虑使用分区表,但是目前PG内置分区表的性能在分区非常多时,使用PREPARED STATEMENT会导致性能下降。
1730 0
|
Web App开发 关系型数据库 测试技术
|
关系型数据库 PostgreSQL 索引
推荐文章
更多