ADD COLUMN with DEFAULT value to a huge table

Question

I have a postgresql DB and a table with almost billion of rows. when I try to add a new column with default value:

ALTER TABLE big_table
ADD COLUMN some_flag integer NOT NULL DEFAULT 0;

The transaction goes on for 30+ min .. and the DB logs starts to shoots warnings.

Any way to optimize the query ?

Answer 1

Besides doing it in batches (which will still take a while):

You could dump the table as COPY statements and write a script to edit the contents of the COPY statements to insert another column (COPY can be CSV IIRC).

Then you just reload your altered COPY dump and it should in theory be faster than the ALTER because COPY will not log transactions.

The other option is to turn off fsync while you run the command... just remember to turn it back on.

You can also do both of the above in batches.