postgresql 10 add column、drop column 的实验

浅浅的花香味﹌ 2023-03-03 14:29 5阅读 0赞

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