- 论坛徽章:
- 0
|
某些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 |
|