文档章节

在PostgreSQL中秒级完成大表添加带有not null属性并带有default值的实验

PGSmith
 PGSmith
发布于 01/11 14:12
字数 1667
阅读 460
收藏 6
点赞 1
评论 0

近期同事在讨论如何在PostgreSQL中一张大表,添加一个带有not null属性的,且具有缺省值的字段,并且要求在秒级完成。

因为此,有了以下的实验记录:

首先我们是在PostgreSQL 10下做的实验:

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

开启计时:

postgres=# \timing 
Timing is on.

建表,并查询表信息,插入数据:

postgres=# create table add_c_d_in_ms(id int, a1 text, a2 text, a3 text, a4 text, a5 text, a6 text, a7 text, a8 text not null default 'wangshuo');
CREATE TABLE
Time: 72.243 ms
postgres=# select oid,relname,relnatts from pg_class where relname='add_c_d_in_ms';
  oid  |    relname    | relnatts 
-------+---------------+----------
 16384 | add_c_d_in_ms |        9
(1 row)

Time: 1.504 ms
postgres=# insert into add_c_d_in_ms select generate_series(1,10000000),'wangshuo','highgo'||random(),'huang','wang',generate_series(1,10000000)::text,random()*100000000000,'shuo','ms';
INSERT 0 10000000
Time: 81261.870 ms (01:21.262)

我们看一下列信息:

postgres=# select * from pg_attribute where attrelid = 16384;
 attrelid | attname  | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atth
asdef | attidentity | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions 
----------+----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----
------+-------------+--------------+------------+-------------+--------------+--------+------------+---------------
    16384 | tableoid |       26 |             0 |      4 |     -7 |        0 |          -1 |        -1 | t        | p          | i        | t          | f   
      |             | f            | t          |           0 |            0 |        |            | 
    16384 | cmax     |       29 |             0 |      4 |     -6 |        0 |          -1 |        -1 | t        | p          | i        | t          | f   
      |             | f            | t          |           0 |            0 |        |            | 
    16384 | xmax     |       28 |             0 |      4 |     -5 |        0 |          -1 |        -1 | t        | p          | i        | t          | f   
      |             | f            | t          |           0 |            0 |        |            | 
    16384 | cmin     |       29 |             0 |      4 |     -4 |        0 |          -1 |        -1 | t        | p          | i        | t          | f   
      |             | f            | t          |           0 |            0 |        |            | 
    16384 | xmin     |       28 |             0 |      4 |     -3 |        0 |          -1 |        -1 | t        | p          | i        | t          | f   
      |             | f            | t          |           0 |            0 |        |            | 
    16384 | ctid     |       27 |             0 |      6 |     -1 |        0 |          -1 |        -1 | f        | p          | s        | t          | f   
      |             | f            | t          |           0 |            0 |        |            | 
    16384 | id       |       23 |            -1 |      4 |      1 |        0 |          -1 |        -1 | t        | p          | i        | f          | f   
      |             | f            | t          |           0 |            0 |        |            | 
    16384 | a1       |       25 |            -1 |     -1 |      2 |        0 |          -1 |        -1 | f        | x          | i        | f          | f   
      |             | f            | t          |           0 |          100 |        |            | 
    16384 | a2       |       25 |            -1 |     -1 |      3 |        0 |          -1 |        -1 | f        | x          | i        | f          | f   
      |             | f            | t          |           0 |          100 |        |            | 
    16384 | a3       |       25 |            -1 |     -1 |      4 |        0 |          -1 |        -1 | f        | x          | i        | f          | f   
      |             | f            | t          |           0 |          100 |        |            | 
    16384 | a4       |       25 |            -1 |     -1 |      5 |        0 |          -1 |        -1 | f        | x          | i        | f          | f   
      |             | f            | t          |           0 |          100 |        |            | 
    16384 | a5       |       25 |            -1 |     -1 |      6 |        0 |          -1 |        -1 | f        | x          | i        | f          | f   
      |             | f            | t          |           0 |          100 |        |            | 
    16384 | a6       |       25 |            -1 |     -1 |      7 |        0 |          -1 |        -1 | f        | x          | i        | f          | f   
      |             | f            | t          |           0 |          100 |        |            | 
    16384 | a7       |       25 |            -1 |     -1 |      8 |        0 |          -1 |        -1 | f        | x          | i        | f          | f   
      |             | f            | t          |           0 |          100 |        |            | 
    16384 | a8       |       25 |            -1 |     -1 |      9 |        0 |          -1 |        -1 | f        | x          | i        | t          | t   
      |             | f            | t          |           0 |          100 |        |            | 
(15 rows)

Time: 1.574 ms

我们再来查询一条数据看一下:

postgres=# select * from add_c_d_in_ms where id=1;
 id |    a1    |           a2            |  a3   |  a4  | a5 |        a6        |  a7  | a8 
----+----------+-------------------------+-------+------+----+------------------+------+----
  1 | wangshuo | highgo0.460023149382323 | huang | wang | 1  | 25913513777.7776 | shuo | ms
(1 row)

Time: 806.036 ms

然后,我们看一下正常PostgreSQL加一个字段所花费的时间:

postgres=# alter table add_c_d_in_ms add a9 text not null default 'test';
ALTER TABLE
Time: 36803.610 ms (00:36.804)

明显看到时间花费相当长,其实PostgreSQL在这里将数据完全重写了,主要原因就是就是添加的字段带有not null属性。

我们来看下一新家字段的列属性:

postgres=# select * from pg_attribute where attrelid = 16384 and attname='a9';
 attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | attha
sdef | attidentity | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions 
----------+---------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+------
-----+-------------+--------------+------------+-------------+--------------+--------+------------+---------------
    16384 | a9      |       25 |            -1 |     -1 |     10 |        0 |          -1 |        -1 | f        | x          | i        | t          | t    
     |             | f            | t          |           0 |          100 |        |            | 
(1 row)

Time: 0.670 ms

这时候我们插入数据看一下:

postgres=# insert into add_c_d_in_ms (id) values (1);
INSERT 0 1
Time: 14.658 ms
postgres=# select * from add_c_d_in_ms where id=1;
 id |    a1    |           a2            |  a3   |  a4  | a5 |        a6        |  a7  |    a8    |  a9  
----+----------+-------------------------+-------+------+----+------------------+------+----------+------
  1 | wangshuo | highgo0.460023149382323 | huang | wang | 1  | 25913513777.7776 | shuo | ms       | test
  1 |          |                         |       |      |    |                  |      | wangshuo | test
(2 rows)

Time: 850.982 ms

下面见证奇迹的时刻来了,如何快速添加这么一个字段:

首先,在这里我们涉及三张系统表,pg_class(表属性)、pg_attribute(列属性)、pg_attrdef(缺省值信息),接下来依次看一下三张表的信息:

#pg_class:oid表系统序列号,relname表名,relnatts列个数(主要修改属性)
postgres=# select oid,relname,relnatts from pg_class where relname='add_c_d_in_ms';
  oid  |    relname    | relnatts 
-------+---------------+----------
 16384 | add_c_d_in_ms |       10
(1 row)

Time: 0.418 ms

#pg_attribute 这里还没有修改,和前面一致,在此就查看了

#pg_attrdef 缺省值信息,这里只有原来的a9带有缺省值
postgres=# select * from pg_attrdef ;
 adrelid | adnum |                                                                                       adbin                                               
                                        |      adsrc       
---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------+------------------
   16384 |     9 | {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 123 :constvalue 12 [ 48 
0 0 0 119 97 110 103 115 104 117 111 ]} | 'wangshuo'::text
   16384 |    10 | {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 55 :constvalue 8 [ 32 0 
0 0 116 101 115 116 ]}                  | 'test'::text
(2 rows)

Time: 0.363 ms

下面进行改造:

#添加字段属性
postgres=# insert into pg_attribute select attrelid,'new_n_d',atttypid,attstattarget,attlen,attnum+1,attndims,attcacheoff,atttypmod,attbyval,attstorage,attalign,'t',atthasdef,attidentity,attisdropped,attislocal,attinhcount,attcollation,attacl,attoptions,attfdwoptions from pg_attribute where attrelid=16384 and attname='a9';
INSERT 0 1
Time: 25.008 ms

#修改pg_class字段个数
postgres=# update pg_class set relnatts=relnatts+1 where relname='add_c_d_in_ms';
UPDATE 1
Time: 43.979 ms

#添加缺省值
postgres=# insert into pg_attrdef select adrelid,adnum+1,adbin,adsrc from pg_attrdef where adrelid=16384 and adnum=10;
INSERT 16399 1
Time: 15.774 ms

再次查看三个系统表系统信息:

#pg_class
postgres=# select oid,relname,relnatts from pg_class where relname='add_c_d_in_ms';
  oid  |    relname    | relnatts 
-------+---------------+----------
 16384 | add_c_d_in_ms |       11
(1 row)

#pg_attribute
postgres=# select * from pg_attribute where attrelid = 16384 and attname='new_n_d';
 attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | attha
sdef | attidentity | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions 
----------+---------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+------
-----+-------------+--------------+------------+-------------+--------------+--------+------------+---------------
    16384 | new_n_d |       25 |            -1 |     -1 |     11 |        0 |          -1 |        -1 | f        | x          | i        | t          | t    
     |             | f            | t          |           0 |          100 |        |            | 
(1 row)

Time: 0.516 ms

#pg_attrdef
postgres=# select * from pg_attrdef;
 adrelid | adnum |                                                                                       adbin                                               
                                        |      adsrc       
---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------+------------------
   16384 |     9 | {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 123 :constvalue 12 [ 48 
0 0 0 119 97 110 103 115 104 117 111 ]} | 'wangshuo'::text
   16384 |    10 | {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 55 :constvalue 8 [ 32 0 
0 0 116 101 115 116 ]}                  | 'test'::text
   16384 |    11 | {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 55 :constvalue 8 [ 32 0 
0 0 116 101 115 116 ]}                  | 'test'::text
(3 rows)

插入值实验:

postgres=# insert into add_c_d_in_ms (id) values (1);
INSERT 0 1
Time: 8.407 ms
postgres=# select * from add_c_d_in_ms where id=1;
 id |    a1    |           a2            |  a3   |  a4  | a5 |        a6        |  a7  |    a8    |  a9  | new_n_d 
----+----------+-------------------------+-------+------+----+------------------+------+----------+------+---------
  1 | wangshuo | highgo0.460023149382323 | huang | wang | 1  | 25913513777.7776 | shuo | ms       | test | 
  1 |          |                         |       |      |    |                  |      | wangshuo | test | 
  1 |          |                         |       |      |    |                  |      | wangshuo | test | test
(3 rows)

Time: 322.143 ms

问题:

#正常添加字段可以
postgres=# alter table add_c_d_in_ms add a10 text;
ALTER TABLE
#如果添加not null属性的字段,则会检测其他字段属性,将会报错
postgres=# alter table add_c_d_in_ms add a11 text not null default 'aaa';
2018-01-11 00:21:55.587 EST [4217] ERROR:  column "new_n_d" contains null values
2018-01-11 00:21:55.587 EST [4217] STATEMENT:  alter table add_c_d_in_ms add a11 text not null default 'aaa';
ERROR:  column "new_n_d" contains null values

另一种解决方法,添加字段,然后添加check约束:

postgres=# alter table add_c_d_in_ms_new add a9 text default 'abc';
ALTER TABLE
Time: 549.182 ms
postgres=# alter table add_c_d_in_ms_new add constraint ck_tbl_check_a check (a9 is not null);
ALTER TABLE
Time: 46.200 ms
postgres=# insert into add_c_d_in_ms_new (a1) values (1);
INSERT 0 1
Time: 30.716 ms

postgres=# select * from add_c_d_in_ms_new where a1=1::text;
 id | a1 | a2 | a3 | a4 | a5 | a6 | a7 |    a8    | a9  
----+----+----+----+----+----+----+----+----------+-----
    | 1  |    |    |    |    |    |    | wangshuo | abc
(1 row)

Time: 0.500 ms
postgres=# insert into add_c_d_in_ms_new (a1,a9) values (1,null);
2018-01-11 01:07:56.456 EST [4217] ERROR:  new row for relation "add_c_d_in_ms_new" violates check constraint "ck_tbl_check_a"
2018-01-11 01:07:56.456 EST [4217] DETAIL:  Failing row contains (null, 1, null, null, null, null, null, null, wangshuo, null).
2018-01-11 01:07:56.456 EST [4217] STATEMENT:  insert into add_c_d_in_ms_new (a1,a9) values (1,null);
ERROR:  new row for relation "add_c_d_in_ms_new" violates check constraint "ck_tbl_check_a"
DETAIL:  Failing row contains (null, 1, null, null, null, null, null, null, wangshuo, null).
Time: 0.383 ms

我的博客即将搬运同步至腾讯云+社区,邀请大家一同入驻:https://cloud.tencent.com/developer/support-plan?invite_code=25z8r3po8aqsw

© 著作权归作者所有

共有 人打赏支持
PGSmith

PGSmith

粉丝 91
博文 54
码字总数 59729
作品 0
济南
后端工程师
PostgreSQL 任意列组合条件 行数估算 实践 - 采样估算

标签 PostgreSQL , 优化器 , 采样 , 多列统计信息 , 行评估 , 大表任意维度过滤行数评估 背景 在任意维度查询(ADHoc)的场景中,有诸多技术手段来保证查询的性能,比如rum索引,GIN倒排索引...

德哥
05/06
0
0
PostgreSQL安装详细步骤(windows)

PostgreSQL安装: 一、windows下安装过程 安装介质:postgresql-9.1.3-1-windows.exe(46M),安装过程非常简单,过程如下: 1、开始安装: 2、选择程序安装目录: 注:安装 PostgreSQL 的分...

引鸩怼孑
2015/04/21
0
1
PostgreSQL安装详细步骤(windows)

PostgreSQL安装: 一、windows下安装过程 安装介质:postgresql-9.1.3-1-windows.exe(46M),安装过程非常简单,过程如下: 1、开始安装: 2、选择程序安装目录: 注:安装 PostgreSQL 的分...

moz1q1
2014/08/14
0
0
PostgreSQL 的一些你可能不知道但应该尝试的功能

PostgreSQL包含许多重要的功能。他们中的许多人都非常知名。其他人可以是非常有用的,但没有广泛赞赏。以下是我们首选的PostgreSQL功能,您可能没有仔细看过,但实际上应该这样做,因为它们可...

oschina
06/27
0
0
postgresql常见命令及操作

  pgsql已经更新到beta11了不同版本的服务器启动或相关命令、配置可能会有不同所以得根据pg版本进行操作。下面记录一些工作中常用到的一些操作主要包括服务启动、备份/恢复数据、数据目录迁...

zhoujie0111
06/02
0
0
PostgreSQL 11 preview - 添加非空默认值不需要 rewrite table

标签 PostgreSQL , add column default , rewrite table 背景 PostgreSQL ,我们在给表加列时,如果不设置列的默认值,不需要rewrite table,只是对元数据进行修改。 但是如果需要设置新增列...

德哥
06/29
0
0
PostgreSQL从继承到分区(三)

三、Pg_partman 3.1 介绍 pg_partman是基于PostgreSQL分区开发的一个分区表管理工具,通过多个引入函数实现了对分区表的管理,相比手工创建分区表、触发器函数、触发器显得更加快捷方便,同时...

LIAN-SA
2014/03/05
0
0
PostgreSQL 角色管理

一、角色与用户的区别 角色就相当于岗位:角色可以是经理,助理。 用户就是具体的人:比如陈XX经理,朱XX助理,王XX助理。 在PostgreSQL 里没有区分用户和角色的概念,"CREATE USER" 为 "CRE...

quanpower
2014/03/23
0
0
[笔记]将系统的数据库从MySQL 5.5迁移到PostgreSQL 9.1

环境 Windows Server 2003 x64 简体中文, MySQL 5.5 (UTF8编码), PostgreSQL 9.1.4-1 (UTF8编码) Spring 3.0.7, Struts 2.3.4, Hibernate 3.5.5 从MySQL迁移到PostgreSQL ------------------......

leeoo
2012/07/22
0
3
数据库案例集锦 - 开发者的《如来神掌》

标签 PostgreSQL , PG DBA cookbook , PG Oracle兼容性 , PG 架构师 cookbook , PG 开发者 cookbook , PG 应用案例 背景 「剑魔独孤求败,纵横江湖三十馀载,杀尽仇寇,败尽英雄,天下更无抗...

德哥
2017/06/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

微服务架构下的安全认证与鉴权

微服务下常见认证解决方案; OAuth认证与授权; JWT认证介绍; Spring Cloud的OAuth2实现; 单体应用转变为分布式应用 单体应用转变为分布式应用在架构方式上存在较大区别,单体应用下的简单...

Java大蜗牛
25分钟前
0
0
前端面试题汇总

最近在复习,准备找工作了,特此总结一下前端的相关知识。 1.获取浏览器URL中查询字符的参数: function getQuery(name){    var reg = new RegExp("(^|&)"+name+"=([^&]*)"(&|$));...

凛冬来袭
26分钟前
0
0
可持续发展的学习道路

与其要求别人,不如提升自己 内心渴望进步 经常做出改变现有模式,不断学习 寻找资源,整合资源,不断熟练这种模式 渠道很重要 先打开新世界的航路

狮子狗
30分钟前
0
0
apollox-lua开源项目 示例codepen2

今天在示例上增加了几个功能, 首先添加js array的标准库。 所有js array的方法目前都支持了。 添加查看code模式。 点击查看code可以看到生成的lua代码。默认web模式需要把标准库连接进来, ...

钟元OSS
45分钟前
0
0
javascript性能优化之避免重复工作

javascript最重要也最根本的性能优化标准之一是避免工作,避免工作又包括两点,第一,不做不必要的工作,第二,不做重复的已经完成的工作。第一部分可以通过代码重构完成,第二部分不做重复的...

老韭菜
57分钟前
0
0
缓存穿透、并发和雪崩那些事

0 题记 缓存穿透、缓存并发和缓存雪崩是常见的由于并发量大而导致的缓存问题,本文讲解其产生原因和解决方案。 缓存穿透通常是由恶意攻击或者无意造成的;缓存并发是由设计不足造成的;缓存雪...

Java填坑之路
今天
1
0
项目jar包管理构建工具---Maven

一、what is Maven? 我们来寻找一下官网,里面介绍了maven到底是什么?下面一句话就有讲解到:Apache Maven is a software project management and comprehension tool. Based on the conc...

一看就喷亏的小猿
今天
0
0
JVM学习手册(一):查看堆内存使用情况以及排错

平时出现内存溢出以及死锁,一般处理方式都是查看日志,找到抛出异常的代码行,然后本地分析代码,但是这样对于线上排查十分糟糕,这段时间在研究JVM发现了几个比较好的工具和指令. 1.针对频繁GC和...

勤奋的蚂蚁
今天
1
0
17.TCP:传输控制协议

介绍 TCP和UDP使用同一网络层(IP),但TCP提供了面向连接、可靠的传输层服务 TCP传输给IP层的信息单位称为报文段或段 TCP通过如下方式保证可靠性: 应用数据被分割成TCP认为最合适发送的数据...

loda0128
今天
0
0
重装Oracle时出现environment variable "PATH"错误的解决办法

在win7 64位下重新安装oracle 11g,一直报environment variable "PATH"的错误,按说明将path里多余的路径删除,但没办法解决。选择忽略错误继续安装,装一半会报CRC错误,还是安装失败。最好...

良言
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部