Ok, here is the question. I have some table which I create like
create table maths( id1 bytea , id2 bytea , comment text[] )
there's more columns but that's insignificant. After creation I insert there ~500k rows and modify them by several queries each of them contains
array_append(comments, 'Some comment about current computing step')
And after all is done I get tablesize approx 1.6Gb's
If then I run
ALTER TABLE maths ALTER COLUMN comments TYPE varchar[] USING comments::varchar[];
the tablesize drops significantly, to approx ~300Mb's
Why is this happening? And how can I get similar result without altering column type?
note: I've tried to set column to varchar[] type on creation, but after doing all queries it still becomes 1.6Gb's in size and converting to text[] reduces it to 300Mb's again.
The ALTER TABLE .. ALTER TYPE .. does a full table rewrite, which compacts the table and indexes.
You'd get the same result from VACUUM FULL tablename; instead.
text and varchar have exactly identical storage.