PostgreSQL 11 新特性解读 : 新增非空默认值字段不需要重写表

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: PostgreSQL 10 版本前表新增不带默认值的DDL不需要重写表,只需要更新数据字典,因此DDL能瞬间执行,如下: ALTER TABLE table_name ADD COLUMN flag text; 如果新增的字段带默认值,则需要重写表,表越大,执行时间越长,如下。

PostgreSQL 10 版本前表新增不带默认值的DDL不需要重写表,只需要更新数据字典,因此DDL能瞬间执行,如下:

ALTER TABLE table_name ADD COLUMN flag text;

如果新增的字段带默认值,则需要重写表,表越大,执行时间越长,如下。

ALTER TABLE table_name ADD COLUMN flag text DEFAULT 'default values';

生产环境下给大表添加带 Default 值的字段将非常吃力,通常分两步进行:

  1. 第一步: 先添加不带 Default值的字段。
  2. 第二步: 写函数批量刷新新增字段的默认值。

上述第二步比较麻烦,也可以在业务低谷或申请停服窗口一次性完成带DEFAUL值字段的新增。

PostgreSQL 11 版本这方面进一步增强,表新增带非空默认值的字段不再需要重写表,Release 中的说明如下:

Release中的说明

Allow ALTER TABLE to add a column with a non-null default without a table rewrite

本文分别在 10 版本和 11 版本进行测试。

PostgreSQL 10 版本

创建测试表并插入1000万数据,如下。

[pg10@pghost1 ~]$ psql mydb pguser
psql (10.0)
Type "help" for help.

mydb=> CREATE TABLE t1(id int4, name text);
CREATE TABLE

mydb=> INSERT INTO t1 (id,name ) SELECT n, n || '_ALTER TABLE TEST ' FROM generate_series (1,10000000) n;
INSERT 0 10000000

mydb=> ANALYZE t1;
ANALYZE

查看表的 relfilenode 和 relpages 信息,relfilenode 表示表的物理文件号。

mydb=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       25672 |    73530
(1 row)

新增带默认值的非空字段,如下。

mydb=> \timing
Timing is on.

mydb=> ALTER TABLE t1 ADD COLUMN flag text DEFAULT 'abcdefg';
ALTER TABLE
Time: 15540.002 ms (00:15.540)

执行时间较长,需要15秒左右。

表分析后再次查看表的 relfilenode 和 relpages信息

mydb=> ANALYZE t1;
ANALYZE

mydb=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       25679 |    83334
(1 row)

发现 relfilenode 有变化 ,之前的 relfilenode 值为 25672 ,说明表被重写。另一方面 relpages 变大了。

PostgreSQL 11 版本

创建测试表并插入1000万数据,如下。

[pg11@pghost2 ~]$ psql francs francs
psql (11beta3)
Type "help" for help.

francs=> CREATE TABLE t1(id int4, name text);
CREATE TABLE

francs=> INSERT INTO t1 (id,name ) SELECT n, n || '_ALTER TABLE TEST ' FROM generate_series (1,10000000) n;
INSERT 0 10000000

francs=> ANALYZE t1;
ANALYZE

查看表的 relfilenode 和 relpages信息,如下:

francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       16802 |    73530

新增带默认值的非空字段,如下。

francs=> \timing
Timing is on.

francs=> ALTER TABLE t1 ADD COLUMN flag text DEFAULT 'abcdefg';
ALTER TABLE
Time: 40.743 ms

执行时间只需要 40 ms,瞬间完成。

表分析后再次查看表的 relfilenode 和 relpages信息

francs=> ANALYZE t1;
ANALYZE

francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       16802 |    73530
(1 row)

发现 relfilenode 没有变化,依然是 16802,同时 relpages 也没有变化。

增加1000字段

PostgreSQL 11 版本给表 t1 增加了一个带默认值的字段后表占用空间没有变化,是不是增加的字段数不够多?接着往下测试,增加1000个带默认值的字段,看看情况如何?

创建测试表并插入1000万测试数据,如下:

francs=> DROP TABLE t1;
DROP TABLE

francs=> CREATE TABLE t1(id int4, name text);
CREATE TABLE

francs=> INSERT INTO t1 (id,name ) SELECT n, n || '_ALTER TABLE TEST ' FROM generate_series (1,10000000) n;
INSERT 0 10000000

francs=> ANALYZE t1;
ANALYZE

查看表的 relfilenode 和 relpages信息,如下:

francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       34187 |    73530
(1 row)

查看表大小,如下:

francs=> SELECT pg_size_pretty(pg_relation_size('t1'));
 pg_size_pretty
----------------
 574 MB
(1 row)

创建函数,此函数用来给表 t1 添加 1000 个带默认值的字段,如下:

CREATE OR REPLACE FUNCTION  add_column() RETURNS INTEGER  AS  $BODY$
DECLARE
    column_name       text;
    default_value     text;
BEGIN
         default_value:= repeat(md5('1'),10);

       FOR i in 1..1000 LOOP
         column_name:= 'flag' || i;
         EXECUTE  
$$
 ALTER TABLE t1 ADD COLUMN 
$$
 || column_name  || 
$$
 text default' 
$$
 || default_value  || 
$$
'
$$
 ;
       END LOOP;

       RETURN 1;
END
$BODY$  LANGUAGE 'plpgsql';

执行函数,如下:

francs=> SELECT add_column();
 add_column
------------
          1
(1 row)

这时表t1已增加了1000个字段,如下:

francs=> SELECT * FROM t1 LIMIT 1;
-----------------------------------------[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------

id       | 1
name     | 1_ALTER TABLE TEST
flag1    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag2    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag3    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag4    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag5    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag6    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag7    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
...省略

查看表 relfilenode 和 relpages,没有变化。

francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       34187 |    73530
(1 row)

再次确认表大小,依然还是 574MB。

francs=> SELECT pg_size_pretty(pg_relation_size('t1'));
 pg_size_pretty
----------------
 574 MB
(1 row)

从以上看出给表t1增加了1000个带默认值的字段后,t1表大小依然没有变化。

参考

新书推荐

最后推荐和张文升共同编写的《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数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
28天前
|
关系型数据库 PostgreSQL
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
18 0
|
28天前
|
SQL 关系型数据库 MySQL
mysql使用default给列设置默认值的问题
mysql使用default给列设置默认值的问题
16 0
|
3月前
|
SQL 关系型数据库 数据库
postgresql中连接两张表更新第三张表(updata)
如何结合两张表的数据来更新第三张表
36 0
|
2月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL【SQL 01】根据条件更新字段值或追加信息STRPOS(string, substring)函数使用及LIKE函数对比
PostgreSQL【SQL 01】根据条件更新字段值或追加信息STRPOS(string, substring)函数使用及LIKE函数对比
31 0
|
2月前
|
关系型数据库 数据库 PostgreSQL
postgresql | 数据库| 生成2000W条的简单测试表
postgresql | 数据库| 生成2000W条的简单测试表
21 0
|
7月前
|
存储 关系型数据库 数据库
探索PostgreSQL 14新特性--SEARCH和CYCLE
探索PostgreSQL 14新特性--SEARCH和CYCLE
41 0
|
3月前
|
关系型数据库 MySQL 数据库
Flink CDC中mysql 字段设置了默认值 ,然后插入数据时 指定该字段为null 会返回字段默认值 而不是null?
Flink CDC中mysql 字段设置了默认值 ,然后插入数据时 指定该字段为null 会返回字段默认值 而不是null?
108 2
|
5月前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL从表回收站中恢复误删的表
PolarDB MySQL提供表回收站的功能,删除的表会被临时转移到表回收站,本案例教您从表回收站恢复误删的表。
213 0
|
5月前
|
关系型数据库 分布式数据库 PolarDB
沉浸式学习PostgreSQL|PolarDB 15: 企业ERP软件、网站、分析型业务场景、营销场景人群圈选, 任意字段组合条件数据筛选
在企业ERP软件、网站中经常会有一些让用户输入筛选条件(或者勾选筛选条件)的地方, 一个页面可能出现若干个选项, 每个选项用户可以进行勾选或下拉框选择. 例如淘宝网, 发货地是哪里, 商品价格范围, 商品类目, 内存大小, .... 很多选项提供选择. 分析业务场景, 经常会使用大宽表来表示对象的特征, 每个字段代表一个特征维度, 然后通过各个字段的组合条件来进行数据的统计分析. 营销场景, 和前面分析场景类似, 通过各个字段的组合条件圈选目标用户. 通常一个选项代表一个对象的某属性, 用户可能根据任意组合条件进行筛选, 本实验目标学习如何快速在任意字段组合条件输入搜索到满足条件的数据.
511 0
|
6月前
|
SQL 关系型数据库 MySQL
MySQL默认值(DEFAULT)
MySQL默认值(DEFAULT)
107 0

热门文章

最新文章