ADD COLUMN with DEFAULT value to a huge table

postgresql

Solution

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.

Problem

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 ?

Original source