PostgreSQL: adding a few columns more than triples the original table size

Question

I created a table2 from another table1 with:

CREATE TABLE table2 AS SELECT * FROM table1;

table1 is 4.8 GB with 1.5 mio. rows and 20 columns of types integer (8x), charvar(1) (9x), real (2x) and geometry (1x). table2 is 3.5 GB after doing that.

I then add 3 columns (real, real, integer) to table2, and update the columns with some values.

However, after doing that, table2 becomes about 4 times larger with 14 GB.

What could be the cause for that? I expect adding these columns would occupy less space.

I performed a full vacuum but it didn't change anything.

I check the sizes with:

select table_name, pg_total_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2;

I create the table and use the update-set commands in a SQL script called with psql:

DO $$
BEGIN
...
All my SQL commands
...
END $$

Answer 1

That is as expected. Since updating a row will write a new row version while leaving the old one in place, updating all rows of a table will double its size.

If you run several updates without giving autovacuum time in between to free the dead row version, the size can increase even more.

After your updates are done, run VACUUM (FULL) on the table to rewrite it and get rid of the extra storage space.

Answer 2

In stead of adding and updating the columns when table2 has been created, add the new columns to the select when you create table2.

CREATE TABLE table2 AS
  SELECT t1.*,
    ... new_real1,
    ... new_real2,
    ... new_int
  FROM table1 t1;