Which ALTER TABLE Operation will rewrite the whole tables

digoal

某些ALTER 表操作可能导致全表重建,包括索引的重建，需要慎重操作。(如set data type操作(不管类型类有没有改变),set with oids和set without oids都会引起全表重建)。
除此之外，还需要注意的是:
  drop column 并不会立刻释放删掉的列的占用空间，仅仅是标记为不可见。同样增加列也不会立刻分配空间。
  set data type $type USING express 可以用于修改类型的同时更新该字段的值，（如不支持隐形转换的类型更改，或者是既想更改类型又想更新数据，这么做的好处是不会产生二次更新垃圾）
  SET STORAGE操作针对修改列的存储属性，如释放为普通存储或压缩存储，是否使用TOAST表等等。包含PLAIN , MAIN , EXTERNAL , EXTENDED 四个选择。plain表示普通且INLINE存储，固定长度的值如INT必须选择这个。main表示压缩且INLINE存储。EXTERNAL表示普通且外部存储。EXTENDED表示压缩且外部存储。外部存储将会新建一个TOAST表，外部存储的好处是提高超长字段的处理效率。改变列存储属性不会对已经存在的数据做修改，对后续操作有效。
  SET TABLESPACE也是比较大的操作，会把表的数据文件拷贝到指定的表空间，所以最好一开始就规划好表空间。
最后的建议是:
   对于会重写表的操作，尽量合并为一句SQL搞定，减少表的扫描次数和IO操作次数。

ALTER TABLE的语法:

ALTER TABLE [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
    RENAME TO new_name
ALTER TABLE name
    SET SCHEMA new_schema

where action is one of:

    ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]
    ALTER [ COLUMN ] column SET DEFAULT expression
    ALTER [ COLUMN ] column DROP DEFAULT
    ALTER [ COLUMN ] column { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column SET STATISTICS integer
    ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD table_constraint
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITH OIDS
    SET WITHOUT OIDS
    SET ( storage_parameter = value [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OWNER TO new_owner
    SET TABLESPACE new_tablespace


下面是举例：（从操作时间上就可以看出 set data type操作(不管类型类有没有改变),set with oids和set without oids都会引起全表重建 ）
digoal=> create table tbl_user (id int primary key,firstname varchar(32),lastname varchar(32),corp varchar(32),age int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tbl_user_pkey" for table "tbl_user"
CREATE TABLE
Time: 51.633 ms
digoal=> insert into tbl_user select generate_series(1,1000000),'zhou','digoal','sky-mobi',27;
INSERT 0 1000000
Time: 3837.951 ms
digoal=> analyze tbl_user;
ANALYZE
Time: 118.823 ms
digoal=> select pg_relation_size('tbl_user');
pg_relation_size
------------------
         60235776
(1 row)

Time: 0.370 ms
digoal=> alter table tbl_user drop column firstname;
ALTER TABLE
Time: 0.363 ms
digoal=> analyze tbl_user;
ANALYZE
Time: 111.081 ms
digoal=> select pg_relation_size('tbl_user');
pg_relation_size
------------------
         60235776
(1 row)

Time: 0.447 ms
digoal=> alter table tbl_user add column firstname varchar(32);
ALTER TABLE
Time: 0.521 ms
digoal=> analyze tbl_user;
ANALYZE
Time: 80.384 ms
digoal=> select pg_relation_size('tbl_user');
pg_relation_size
------------------
         60235776
(1 row)

Time: 0.297 ms
digoal=> alter table tbl_user alter column firstname set data type varchar(64);
ALTER TABLE
Time: 6432.210 ms
# 设置和当前相同类型,同样全表重写
digoal=> alter table tbl_user alter column firstname set data type varchar(64);
ALTER TABLE
Time: 4751.493 ms
digoal=> analyze tbl_user ;
ANALYZE
Time: 101.746 ms
digoal=> select pg_relation_size('tbl_user');
pg_relation_size
------------------
         52183040
(1 row)

Time: 0.296 ms
digoal=> alter table tbl_user alter column firstname set default 'zhou';
ALTER TABLE
Time: 31.990 ms

digoal=> alter table tbl_user alter column firstname drop default ;
ALTER TABLE
Time: 0.494 ms

digoal=> alter table tbl_user alter column lastname set not null ;
ALTER TABLE
Time: 100.171 ms
# set with oids和set without oids重写整表
digoal=> insert into tbl_user(id,lastname) select generate_series(1000001,2000000),'DIGOAL';
INSERT 0 1000000
Time: 3723.678 ms
digoal=> alter table tbl_user set with oids;
ALTER TABLE
Time: 11461.001 ms
digoal=> analyze tbl_user;
ANALYZE
Time: 171.743 ms
digoal=> select pg_relation_size('tbl_user');
-[ RECORD 1 ]----+----------
pg_relation_size | 112418816

Time: 0.290 ms
digoal=> alter table tbl_user set without oids;
ALTER TABLE
Time: 10742.167 ms
digoal=> select pg_relation_size('tbl_user');
-[ RECORD 1 ]----+---------
pg_relation_size | 96460800

Time: 0.456 ms
digoal=> analyze tbl_user;
ANALYZE
Time: 166.247 ms
digoal=> select pg_relation_size('tbl_user');
-[ RECORD 1 ]----+---------
pg_relation_size | 96460800

Time: 0.276 ms