Thread: VACUUM FULL vs dropped columns

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

Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> 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?

VACUUM FULL will rewrite the tuples with a null bitmap where they
had none before (cf reform_and_rewrite_tuple).  That's only a rather
marginal increase in the tuple size, but in this particular example,
it pushes the tuples from just under half a page to just over, so
that you go from 2 tuples/page to 1.

            regards, tom lane

On Sun, Mar 11, 2018 at 9:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
>> 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?
>
> VACUUM FULL will rewrite the tuples with a null bitmap where they
> had none before (cf reform_and_rewrite_tuple).  That's only a rather
> marginal increase in the tuple size, but in this particular example,
> it pushes the tuples from just under half a page to just over, so
> that you go from 2 tuples/page to 1.
>


Aha! Thanks for the explanation.

cheers

andrew



-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services