postgresql表增加/删除字段是否会重写表 -m6米乐安卓版下载

1.postgresql10版本及以下

新增不带默认值的列
postgresql 10 版本前表新增不带默认值的列不需要重写表，只需要更新数据字典，因此能瞬间执行。如果不带默认值，则会填充空值。

新增带默认值的列
如果新增的字段带默认值，则需要重写表。表越大，执行时间越长。 重写表会对表加access exclusive锁，期间整张表是无法访问的。

如果是生产环境下给大表添加带 default 值的字段可能影响比较大，通常先添加不带 default值的字段，然后写函数批量刷新新增字段的默认值。也可以在业务量较低的时候或者申请割接窗口停业务一次性完成带defaul值字段的新增。除此之外，如果有必要，可以清理一下可能堵塞ddl的长事务或者后台任务(例如暂时关闭这张表上的autovacuum)。

2.postgresql11版本及以上

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

release中的说明
allow alter table to add a column with a non-null default without a table rewrite

主要的实现方法是：在系统表 pg_catalog.pg_attribute 中添加了两个字段：atthasmissing 和 attmissingval。新增列的默认值会记录到attmissingval，并且对应的atthasmissing会被设置为true。查询时如果tuple中不包含对应的列，则会返回attmissingval的值。

但是如果表之后因为其他的操作导致表被重写，例如vacuum full，则相应的atthasmissing和attmissingval属性将会被清除。

postgres=# create table t(id int, name varchar(20));
create table
postgres=# insert into t select generate_series(1,10),left(md5(random()::text),20);
insert 0 10
postgres=# select attname, attmissingval, atthasmissing from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
 attname | attmissingval | atthasmissing
--------- --------------- ---------------
 id      |               | f
 name    |               | f
(2 rows)
postgres=# alter table t add column test_default int default 1;
alter table
postgres=# alter table t add column test_nodefault int;
alter table
postgres=# select attname, attmissingval, atthasmissing from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
    attname     | attmissingval | atthasmissing
---------------- --------------- ---------------
 id             |               | f
 name           |               | f
 test_default   | {1}           | t
 test_nodefault |               | f
(4 rows)

这个功能对于postgresql11版本及以上加默认值的列很友好，因为大部分带默认值的列都不需要重写表。

这里说大部分的意思是确实也存在添加带默认值的列需要重写表的情况，通常情况下，增加列对应的默认值是常量以及stable或者immutable类型的函数的时候，不会发生重写表。而新增的默认值是 volatile 类型的函数就会重写表，例如默认值是random()函数的时候。这种时候因为 volatile 类型的函数的原因，导致表里所有的tuple的这列都不是一个值，所以无法在pg_attribute的attmissingval上做统一的标记。

一个大概的测试结果如下所示：
//增加默认值是常量的列，发现表没有重写。新增列的默认值记录到attmissingval，并且对应的atthasmissing被设置为true。

//增加默认值是stable或者immutable类型的函数，发现表没有重写。新增列的默认值记录到attmissingval，并且对应的atthasmissing被设置为true。

//增加默认值是volatile类型的函数，发现表发生重写。新增列的默认值不记录attmissingval

1.删除字段分析

删除一个列时，并不会重建表（逐行扫表重写），而是将pg_attribute中对应的列的attname字段修改为… pg.dropped.idx… ，attisdropped标记为true，查询时会跳过该列。因此，删除列操作可以很快完成。

postgres=# \d t
                            table "public.t"
     column     |         type          | collation | nullable | default
---------------- ----------------------- ----------- ---------- ---------
 id             | integer               |           |          |
 name           | character varying(20) |           |          |
 test_default   | integer               |           |          | 1
 test_nodefault | integer               |           |          |
postgres=# select attname, attmissingval, atthasmissing from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
    attname     | attmissingval | atthasmissing
---------------- --------------- ---------------
 id             |               | f
 name           |               | f
 test_default   | {1}           | t
 test_nodefault |               | f
(4 rows)
postgres=# alter table t drop column test_nodefault;
alter table
postgres=# select attname, attmissingval, atthasmissing from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
           attname            | attmissingval | atthasmissing
------------------------------ --------------- ---------------
 id                           |               | f
 name                         |               | f
 test_default                 | {1}           | t
 ........pg.dropped.4........ |               | f
(4 rows)

但是drop后的列，pg_attribute里的……… pg.dropped.idx… 记录不会因为做了vacuum full而被移除，这一点和11版本增加带默认值的列不同。

postgres=# select attname, attmissingval, atthasmissing from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
           attname            | attmissingval | atthasmissing
------------------------------ --------------- ---------------
 id                           |               | f
 name                         |               | f
 test_default                 | {1}           | t
 ........pg.dropped.4........ |               | f
(4 rows)
postgres=# vacuum full t;
vacuum
postgres=# select attname, attmissingval, atthasmissing from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
           attname            | attmissingval | atthasmissing
------------------------------ --------------- ---------------
 id                           |               | f
 name                         |               | f
 test_default                 |               | f
 ........pg.dropped.4........ |               | f
(4 rows)

2.vacuum full对删除字段的影响

不过做过了vacuum full 操作已经把这列对应的数据清理掉了，即使通过更新系统表让列恢复，数据也不能恢复了。（vacuum不会导致列数据恢复不了）
想要恢复可能需要根据全量备份加wal日志做pitr了。

如下是没做vacuum full和做过了vacuum full的列恢复情况。可以看到未做vacuum full的列可以正常恢复，但是做过了vacuum full的列虽然可以恢复，但是列原来存储的数据已经不存在了，用null填充。

（1）删除字段后未做vacuum full

//创建测试表
postgres=# create table t(id int, name varchar(20));
create table
postgres=# insert into t select generate_series(1,10),left(md5(random()::text),20);
insert 0 10
postgres=# select * from t;
 id |         name
---- ----------------------
  1 | bcbd19340969fda7c9c4
  2 | a9f514a971eae3937def
  3 | 20d53f04cce29b1e2984
  4 | 912dd222955487de27a7
  5 | 5b18d6e2c9b22d34884f
  6 | 4c7db5a43de739511864
  7 | b2f1b83e98bdfdce8bce
  8 | 1fe69e2bec216de50f29
  9 | 689ecc14d87ae81fc0d1
 10 | 0ba176b240d0875da3e8
(10 rows)

查询列信息，attrelid和pg_class.oid关联，即表的oid。atttypid和pg_type.oid关联，列类型的oid，1043即对应varchar类型。attisdropped为t则表示该列被删除且不再有效。一个删除的列仍然物理存在于表中，但是会被分析器忽略并因此无法通过sql访问。

postgres=# select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
 attrelid | attname | atttypid | attnum | attisdropped
---------- --------- ---------- -------- --------------
    16395 | id      |       23 |      1 | f
    16395 | name    |     1043 |      2 | f
(2 rows)
postgres=# select * from pg_type where oid=1043;
 oid  | typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typsubscript |
typelem | typarray | typinput  | typoutput  | typreceive  |   typsend   |    typmodin     |    typmodout     | typanalyze | typalign | typstorage | typnotnul
l | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl
------ --------- -------------- ---------- -------- ---------- --------- ------------- ---------------- -------------- ---------- ---------- -------------- -
-------- ---------- ----------- ------------ ------------- ------------- ----------------- ------------------ ------------ ---------- ------------ ----------
-- ------------- ----------- ---------- -------------- --------------- ------------ --------
 1043 | varchar |           11 |       10 |     -1 | f        | b       | s           | f              | t            | ,        |        0 | -            |
      0 |     1015 | varcharin | varcharout | varcharrecv | varcharsend | varchartypmodin | varchartypmodout | -          | i        | x          | f
  |           0 |        -1 |        0 |          100 |               |            |
(1 row)
//删除列
postgres=# alter table t drop column name;
alter table
postgres=# select * from t;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)
postgres=# select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
 attrelid |           attname            | atttypid | attnum | attisdropped
---------- ------------------------------ ---------- -------- --------------
    16395 | id                           |       23 |      1 | f
    16395 | ........pg.dropped.2........ |        0 |      2 | t
(2 rows)
//更新系统表，恢复删除的列
postgres=# update pg_attribute set attname='name',atttypid=1043,attisdropped='f' where attrelid='t'::regclass and attnum=2;
update 1
postgres=# select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
 attrelid | attname | atttypid | attnum | attisdropped
---------- --------- ---------- -------- --------------
    16395 | id      |       23 |      1 | f
    16395 | name    |     1043 |      2 | f
(2 rows)
postgres=# select * from t;
 id |         name
---- ----------------------
  1 | bcbd19340969fda7c9c4
  2 | a9f514a971eae3937def
  3 | 20d53f04cce29b1e2984
  4 | 912dd222955487de27a7
  5 | 5b18d6e2c9b22d34884f
  6 | 4c7db5a43de739511864
  7 | b2f1b83e98bdfdce8bce
  8 | 1fe69e2bec216de50f29
  9 | 689ecc14d87ae81fc0d1
 10 | 0ba176b240d0875da3e8
(10 rows)

（2）删除字段后做过vacuum full

//删除列
postgres=# alter table t drop column name;
alter table
postgres=# select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
 attrelid |           attname            | atttypid | attnum | attisdropped
---------- ------------------------------ ---------- -------- --------------
    16395 | id                           |       23 |      1 | f
    16395 | ........pg.dropped.2........ |        0 |      2 | t
(2 rows)

可以看到做过了vacuum full后，对应的pg_attribute里的这条已经删除的列的信息依旧没有清理掉。

postgres=# vacuum full t;
vacuum
postgres=# select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attnum > 0 and attrelid = 't'::regclass;
 attrelid |           attname            | atttypid | attnum | attisdropped
---------- ------------------------------ ---------- -------- --------------
    16395 | id                           |       23 |      1 | f
    16395 | ........pg.dropped.2........ |        0 |      2 | t
(2 rows)

//恢复数据
postgres=# update pg_attribute set attname='name',atttypid=1043,attisdropped='f' where attrelid='t'::regclass and attnum=2;
update 1
postgres=# select * from pg_type where oid=1043;
 oid  | typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typsubscript |
typelem | typarray | typinput  | typoutput  | typreceive  |   typsend   |    typmodin     |    typmodout     | typanalyze | typalign | typstorage | typnotnul
l | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl
------ --------- -------------- ---------- -------- ---------- --------- ------------- ---------------- -------------- ---------- ---------- -------------- -
-------- ---------- ----------- ------------ ------------- ------------- ----------------- ------------------ ------------ ---------- ------------ ----------
-- ------------- ----------- ---------- -------------- --------------- ------------ --------
 1043 | varchar |           11 |       10 |     -1 | f        | b       | s           | f              | t            | ,        |        0 | -            |
      0 |     1015 | varcharin | varcharout | varcharrecv | varcharsend | varchartypmodin | varchartypmodout | -          | i        | x          | f
  |           0 |        -1 |        0 |          100 |               |            |
(1 row)
//做过了vacuum full的列虽然可以恢复，但是列原来存储的数据已经不存在了，用null填充
postgres=# select * from t;
 id | name
---- ------
  1 |
  2 |
  3 |
  4 |
  5 |
  6 |
  7 |
  8 |
  9 |
 10 |
(10 rows)

所有的ddl操作都会锁表(堵塞读写)。

ddl操作有的只需要修改元数据,这种情况下一般是毫秒级别就可以完成。而有的需要rewrite表的情况,它的执行时间以及锁的占用时间,取决于表的大小以及索引的多少。

如果ddl操作未能及时获取表的排他锁(例如有其他长事务持有了表的共享锁), 则ddl的排他锁已进入等待队列, 会堵塞其他该表的一切dml和查询操作。