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 $$