PostgreSQL修改列与重写表

为什么80%的码农都做不了架构师？>>>   

老版本的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,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已经没有了(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://my.oschina.net/Kenyon/blog/99757