How to add non-null column to postgresql table without doubling its size on disk

Question

Is there a way to add a non-null type column to a Postgresql table without doubling its size on disk? If, for example, I have a table with specific columns and I want to add a column, I would do the following:

alter table my_table add new_column int  
update table my_table set new_column = 0 
alter table my_table alter column new_column set not null

This, in fact, doubles the space that is allocated for the table due to how Postgresql works. Updates create new tuples that will be marked for reuse after completion of this transaction, and the vacuum does its job. If the table is large (i.e., several million rows) but grows very slowly or is almost constant in size, these rows will never be reused, and only “full vacuum” or full database backup and restore will free up space on disk. Is there a way to automatically add a column with some default value, but without this behavior? For example, if there was a way to lock the table and perform an update, then in this case there would be no need for MVCC.

Answer 1

follow the steps:

• alter table add new column
• alter table add default value for column
• update, but not the whole table with 1 update statement, but release it as 10,000 separate updates, each in its own transaction
• run a vacuum every couple of hundreds of updates, or better - autovacuum
• alter table set not null

Answer 2

If the table is large (i.e., several million rows), but growing very slowly or almost constant in size, the rows will never be reused, and only a “full vacuum” or a full database backup and restore to disk.

. , - , postgres , . Postgres <= 8.3, , max_fsm_pages , ? , , , , , , " ". Postgres fsm .

max_fsm_pages Postgresql 8.4. 8.4, .