PostgreSQL添加字段以及vacuum full对表的影响

PostgreSQL添加字段对表的影响

[local]:5432 sky@sky=# select pg_relation_filepath('testcount');
 pg_relation_filepath 
----------------------
 base/16384/18081
(1 row)

Time: 0.163 ms
[local]:5432 sky@sky=# ALTER TABLE testcount ADD col

[local]:5432 sky@sky=# ALTER TABLE testcount ADD column bb_id int;
ALTER TABLE
Time: 0.917 ms
[local]:5432 sky@sky=# select pg_relation_filepath('testcount');
 pg_relation_filepath 
----------------------
 base/16384/18081
(1 row)

Time: 0.209 ms
[local]:5432 sky@sky=# ALTER TABLE testcount ADD column cc_id int default 0;
ALTER TABLE
Time: 6331.678 ms
[local]:5432 sky@sky=# select pg_relation_filepath('testcount');
 pg_relation_filepath 
----------------------
 base/16384/18085
(1 row)

Time: 0.240 ms
[local]:5432 sky@sky=# ALTER TABLE testcount ADD column dd_id int not null default 0;
ALTER TABLE
Time: 6242.897 ms
[local]:5432 sky@sky=# select pg_relation_filepath('testcount');
 pg_relation_filepath 
----------------------
 base/16384/18089
(1 row)

Time: 0.216 ms
[local]:5432 sky@sky=#

仅仅是add column的话，如果没有默认值，那它是非常快的（只是在字典表里插入一条数据）。如果添加了default表示默认值的话，那要重写整个表（相当于创建一个新的表文件，创建好结构，然后将旧表的数据全迁移到这个文件中）

Vacuum Full 对表的影响

[local]:5432 sky@sky=# VACUUM FULL ;
VACUUM
Time: 7287.372 ms
[local]:5432 sky@sky=# select pg_relation_filepath('testcount');
 pg_relation_filepath 
----------------------
 base/16384/18321
(1 row)

Time: 0.270 ms
[local]:5432 sky@sky=# VACUUM FULL ;
VACUUM
Time: 7214.193 ms
[local]:5432 sky@sky=# select pg_relation_filepath('testcount');
 pg_relation_filepath 
----------------------
 base/16384/18679
(1 row)

Time: 0.490 ms
[local]:5432 sky@sky=#

可以看到每次执行完vacuum full都要将表进行迁移到另一个全新的文件中的.

Contents

PostgreSQL添加字段对表的影响

Vacuum Full 对表的影响