postgresql 10 add column、drop column 的实验

os: centos 7.4.1708
db: postgresql 10.11

create table

postgres=# create table tmp_t0(
 id bigint,
 name varchar(100),
 memo varchar(100)
);

postgres=# insert into tmp_t0
select id,
          md5(id::varchar) as name,
          md5(md5(id::varchar) ) as memo
from generate_series(1,1000000) as id;          

postgres=# select c.oid,
	 relname,
	 relfilenode,
	 pg_relation_filenode(c.oid),
	 pg_relation_filepath(c.oid),
	 pg_size_pretty(pg_relation_size(c.oid))
from pg_class c where c.relname='tmp_t0';

  oid   | relname | relfilenode | pg_relation_filenode | pg_relation_filepath | pg_size_pretty 
--------+---------+-------------+----------------------+----------------------+----------------
 375375 | tmp_t0  |      375375 |               375375 | base/13808/375375    | 104 MB
(1 row)

add column

postgres=# \timing
postgres=# alter table tmp_t0 add column pyb varchar(100);
ALTER TABLE
Time: 2.476 ms

postgres=# select c.oid,
	 relname,
	 relfilenode,
	 pg_relation_filenode(c.oid),
	 pg_relation_filepath(c.oid),
	 pg_size_pretty(pg_relation_size(c.oid))
from pg_class c where c.relname='tmp_t0';

  oid   | relname | relfilenode | pg_relation_filenode | pg_relation_filepath | pg_size_pretty 
--------+---------+-------------+----------------------+----------------------+----------------
 375375 | tmp_t0  |      375375 |               375375 | base/13808/375375    | 104 MB
(1 row)

观察relfilenode，没有发生 recreate table

add column default

postgres=# \timing
postgres=# alter table tmp_t0 add column pyb_default varchar(100) default '00';
ALTER TABLE
Time: 2621.333 ms (00:02.621)

postgres=# select c.oid,
	 relname,
	 relfilenode,
	 pg_relation_filenode(c.oid),
	 pg_relation_filepath(c.oid),
	 pg_size_pretty(pg_relation_size(c.oid))
from pg_class c where c.relname='tmp_t0';

  oid   | relname | relfilenode | pg_relation_filenode | pg_relation_filepath | pg_size_pretty 
--------+---------+-------------+----------------------+----------------------+----------------
 375375 | tmp_t0  |      375381 |               375381 | base/13808/375381    | 104 MB
(1 row)

观察relfilenode，已经发生 recreate table

drop column

postgres=# alter table tmp_t0 drop column pyb;
ALTER TABLE
Time: 6.837 ms

postgres=# select c.oid,
	 relname,
	 relfilenode,
	 pg_relation_filenode(c.oid),
	 pg_relation_filepath(c.oid),
	 pg_size_pretty(pg_relation_size(c.oid))
from pg_class c where c.relname='tmp_t0';

  oid   | relname | relfilenode | pg_relation_filenode | pg_relation_filepath | pg_size_pretty 
--------+---------+-------------+----------------------+----------------------+----------------
 375375 | tmp_t0  |      375381 |               375381 | base/13808/375381    | 104 MB
(1 row)

postgres=# alter table tmp_t0 drop column pyb_default;
ALTER TABLE
Time: 43.968 ms

postgres=# select c.oid,
	 relname,
	 relfilenode,
	 pg_relation_filenode(c.oid),
	 pg_relation_filepath(c.oid),
	 pg_size_pretty(pg_relation_size(c.oid))
from pg_class c where c.relname='tmp_t0';

  oid   | relname | relfilenode | pg_relation_filenode | pg_relation_filepath | pg_size_pretty 
--------+---------+-------------+----------------------+----------------------+----------------
 375375 | tmp_t0  |      375381 |               375381 | base/13808/375381    | 104 MB
(1 row)

没有发生 recreate table