PostgreSQL修改列与重写表

前端之家收集整理的这篇文章主要介绍了PostgreSQL修改列与重写表前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
老版本的Postgresql在新增列时,会rewrite全表,相当于vacuum full table_name。这对生产是非常不利的,严重导致读写效率的低下。高版本已经有所改善,下面检验之。

检查有没有rewrite表,可以查看它的系统列ctid的变化来验证。

DB:Postgresql 9.1.2
postgres=# \d tab_kenyon                            
         Table "public.tab_kenyon"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(35) | 
 remark | text                  |
验证过程如下:
postgres=# vacuum full tab_kenyon;
VACUUM
postgres=# select ctid,* from tab_kenyon ;
 ctid  | id |     name     | remark 
-------+----+--------------+--------
 (0,1) |  1 | kenyon       | 
 (0,2) |  2 | china        | 
 (0,3) |  5 | oopoo        | 
 (0,4) | 45 | xxooxx       | 
 (0,5) |  4 | i love china | 
(5 rows)

postgres=# update tab_kenyon set name = 'kenyon_test' where id = 5;
UPDATE 1
postgres=# select ctid,* from tab_kenyon ;                         
 ctid  | id |     name     | remark 
-------+----+--------------+--------
 (0,5) |  4 | i love china | 
 (0,6) |  5 | kenyon_test  | 
(5 rows)
可以看到ctid已经没有了(0,3),而是新增了(0,6),这是新老版本混在一起的缘故,老版本的数据不可见,并将被清理。
postgres=# alter table tab_kenyon add tt varchar(2);
ALTER TABLE
postgres=# select ctid,* from tab_kenyon ;          
 ctid  | id |     name     | remark | tt 
-------+----+--------------+--------+----
 (0,1) |  1 | kenyon       |        | 
 (0,2) |  2 | china        |        | 
 (0,4) | 45 | xxooxx       |        | 
 (0,5) |  4 | i love china |        | 
 (0,6) |  5 | kenyon_test  |        | 
(5 rows)
可以看到ctid并没有更改,也就是说没有重写表(rewrite table)。

换一个场景,新增带default值的列
postgres=# alter table tab_kenyon add yy varchar(2) default 'Y';
ALTER TABLE
postgres=# select ctid,* from tab_kenyon ;                      
 ctid  | id |     name     | remark | tt | yy 
-------+----+--------------+--------+----+----
 (0,1) |  1 | kenyon       |        |    | Y
 (0,2) |  2 | china        |        |    | Y
 (0,3) | 45 | xxooxx       |        |    | Y
 (0,4) |  4 | i love china |        |    | Y
 (0,5) |  5 | kenyon_test  |        |    | Y
(5 rows)
此时可以看到ctid的值有了变化了。

总结:
1.新增NULL列时只是更新字典信息表,而不会对基础表重写,系统只会保持非常短时间的锁
2.新增非NULL列时会更新基础表
3.修改表结构时是会阻塞表的读写的,可以用begin...end来模拟
4.PG的9.2版本更加减少了alter table的表重写,如下是场景
varchar(x)--> varchar(y)当 y>=x. 或varchar(x)--> varchar or text (no size limitation)
numeric(x,z)--> numeric(y,z)当 y>=x,or to numeric without specifier
varbit(x)--> varbit(y) when y>=x,or to varbit without specifier
timestamp(x)--> timestamp(y) when y>=x or timestamp without specifier
timestamptz(x)--> timestamptz(y) when y>=x or timestamptz without specifier
interval(x)--> interval(y) when y>=x or interval without specifier

参考: http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2 原文链接:https://www.f2er.com/postgresql/196315.html

猜你在找的Postgre SQL相关文章