While doing some testing I noticed this, which seems somewhat perverse:
create table t();
insert into t select from generate_series(1,10000);
select 'alter table t ' || string_agg(' add column c'||x::text||' int
default ' ||x::text,',')
from generate_series(1,1000) x \gexec
create table t_dropped();
insert into t_dropped select from generate_series(1,10000);
select 'alter table t_dropped ' || string_agg(' add column
c'||x::text||' int default ' ||x::text,',')
from generate_series(1,1000) x \gexec
alter table t_dropped drop column c900;
select pg_total_relation_size('t') as size_t,
pg_total_relation_size('t_dropped') as size_t_dropped;
size_t | size_t_dropped
----------+----------------
40960000 | 40960000
(1 row)
vacuum full t;
vacuum full t_dropped;
select pg_total_relation_size('t') as size_t,
pg_total_relation_size('t_dropped') as size_t_dropped;
size_t | size_t_dropped
----------+----------------
40960000 | 81920000
(1 row)
Why does VACUUM FULL cause the size of this table with a single
dropped column (1 out of 1000) cause the table size to double?
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
