I created a
table2 from another
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 $$