Altering column type from int to bigint frees space?

Question

I have table with an int type column, in a table of ~ 15 M rows.
OS windows 7 and C disk (where postgres is installed) shows that:

59 GB free of 238 GB

Then I changed this column type to bigint:

ALTER TABLE mytable ALTER column col TYPE bigint;

And now, C disk:

61 GB free of 238 GB

How are 2 GB freed? Looks like that bigint would take less space than int? Or what happened?

There is no other processes on this machine (this is local/home computer) at this moment.

Answer 1

bigint takes 8 bytes, int takes 4 bytes, but space on disk depends on the whole row.

• Making sense of Postgres row sizes
• Calculating and saving space in PostgreSQL

More importantly, the physical size of the file containing the table also depends on dead tuples (table bloat). VACUUM (typically only VACUUM FULL) can reduce the physical size of the table. Your ALTER TABLE caused a whole-table rewrite, which bloated the table. But it also made it simple for a later VACUUM (or VACUUM FULL) to trim data pages with only dead tuples - effectively compacting the table. VACUUM is run by autovacuum automatically with default settings (but not VACUUM FULL).

• VACUUM returning disk space to operating system
• Are regular VACUUM ANALYZE still recommended under 9.1?

Apart from that side effect, changing a column from int to bigint never reduces the row size (without bloat). Sometimes it stays the same because the previous row had 4 bytes of alignment padding that can be used by the bigint. Or the row size increases by another (typically) 8 bytes.

Answer 2

Just for clarity - only VACUUM FULL can reduce disk space used by table. And even this not always true - depend on amount of deleted tuples in datafile pages if some pages can be removed. And it creates new datafile.

Plain VACUUM only frees space in existing datafile pages by removing deleted tuples but does not lower number of allocated pages in datafile. And does not create new datafile.