AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2018-12-13 5404
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB
简介： 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
测试技术
关系型数据库
PostgreSQL
机器学习/深度学习
SQL
关键词：
PostgreSQL字段
PostgreSQL表
PostgreSQL特性
云数据库 RDS PostgreSQL 版表
PostgreSQL新特性
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
francs.tan
目录
相关文章
风水道人
|
6月前
|
SQL 关系型数据库 PostgreSQL
把PostgreSQL的表导入SQLite
把PostgreSQL的表导入SQLite
风水道人
90 0
翎野君
|
6月前
|
关系型数据库 PostgreSQL
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
翎野君
134 0
翎野君
|
6月前
|
SQL 关系型数据库 MySQL
mysql使用default给列设置默认值的问题
mysql使用default给列设置默认值的问题
翎野君
128 0
嘟嘟嘟嘟嘟嘟
|
6月前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错之使用SQL 将 PostgreSQL 的 date 类型字段转换为 TIMESTAMP 类型时遇到报错，该如何处理
在使用实时计算Flink版过程中，可能会遇到各种错误，了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题，查看Flink的日志是关键，它们通常会提供更详细的错误信息和堆栈跟踪，有助于定位问题。此外，Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
嘟嘟嘟嘟嘟嘟
343 1
三分钟热度的鱼
|
6月前
|
运维 Cloud Native 关系型数据库
云原生数据仓库产品使用合集之原生数据仓库AnalyticDB PostgreSQL版如果是列存表的话， adb支持通过根据某个字段做upsert吗
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能，并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景，为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述，包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
三分钟热度的鱼
407 0
三分钟热度的鱼
|
4月前
|
SQL 监控 关系型数据库
实时计算 Flink版操作报错合集之在设置监控PostgreSQL数据库时，将wal_level设置为logical，出现一些表更新和删除操作报错，怎么办
在使用实时计算Flink版过程中，可能会遇到各种错误，了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题，查看Flink的日志是关键，它们通常会提供更详细的错误信息和堆栈跟踪，有助于定位问题。此外，Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
三分钟热度的鱼
182 1
三分钟热度的鱼
|
4月前
|
SQL 存储 索引
MySQL设计规约问题之为什么应该把字段定义为NOT NULL并且提供默认值
MySQL设计规约问题之为什么应该把字段定义为NOT NULL并且提供默认值
三分钟热度的鱼
51 0
yuanzhengme
|
5月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL和greenplum的copy命令可以添加字段吗？
【6月更文挑战第5天】PostgreSQL和greenplum的copy命令可以添加字段吗？
yuanzhengme
90 3
yuanzhengme
|
5月前
|
关系型数据库 PostgreSQL
postgresql如何将没有关联关系的两张表的字段合并
【6月更文挑战第2天】postgresql如何将没有关联关系的两张表的字段合并
yuanzhengme
141 3
张飞的猪
|
5月前
|
SQL 关系型数据库 数据库连接
ClickHouse(20)ClickHouse集成PostgreSQL表引擎详细解析
ClickHouse的PostgreSQL引擎允许直接查询和插入远程PostgreSQL服务器的数据。`CREATE TABLE`语句示例展示了如何定义这样的表，包括服务器信息和权限。查询在只读事务中执行，简单筛选在PostgreSQL端处理，复杂操作在ClickHouse端完成。`INSERT`通过`COPY`命令在PostgreSQL事务中进行。注意，数组类型的处理和Nullable列的行为。示例展示了如何从PostgreSQL到ClickHouse同步数据。一系列的文章详细解释了ClickHouse的各种特性和表引擎。
张飞的猪
166 0

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    【MySQL用法】MySQL中大于，大于等于，小于，小于等于的转义写法
  • 4
    mysql连接池DataSource，DruidDataSource的理解及其使用
  • 5
    一文搞懂阿里云服务器部署MySQL并整合Spring Boot
  • 6
    应用MySQL MGR
  • 7
    干货分享 ｜ 揭秘下一代云数据库引擎MyBase for MySQL
  • 8
    Linux操作系统上的mysql安装
  • 9
    mysql中char与varchar的区别分析
  • 10
    【Navicat 连接MySQL时出现错误1251：客户端不支持服务器请求的身份验证协议；请考虑升级MySQL客户端】
  • 1
    玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词
    665
  • 2
    使用ECS和RDS搭建个人博客
    1329
  • 3
    PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
    834
  • 4
    【ECS生长万物之开源】如何一键本地部署PolarDB for PostgreSQL
    1135
  • 5
    NineData已支持「最受欢迎数据库」PostgreSQL
    353
  • 6
    postgresql snapshot 快照源码解读
    665
  • 7
    如何使用AnalyticDB PostgreSQL 版实现“一站式全文检索”业务
    32315
  • 8
    将 PostgreSQL 迁移到 MySQL 数据库
    1754
  • 9
    flink postgresql cdc实时同步（含pg安装配置等）
    3194
  • 10
    PostgreSQL内存上下文[翻译]
    287

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云企业级自治数据库 RDS 详解
  • 阿里云MySQL云数据库产品体系介绍

    • 相关实验场景

    更多
  • 自建MySQL 5.7数据库备份上云
  • RDS MySQL高并发访问下的系统稳定性
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    阿里云OSS设置跨域访问