adding boolean field with default value on a large postgres table

Question

We have a very large table (1 Million records) in some cases and we need to add boolean fields to it which have default values. If we add only column it takes 3 minutes and we add 3 columns in the same statement it takes same time.

$ALTER TABLE Job ADD COLUMN test BOOLEAN NOT NULL default false;
ALTER TABLE
Time: 186506.603 ms

    $ALTER TABLE Job ADD COLUMN test BOOLEAN NOT NULL default false , 
    ADD COLUMN test1 BOOLEAN NOT NULL default false, 
    ADD COLUMN test2 BOOLEAN NOT NULL default false;
    ALTER TABLE
    Time: 179055.546 ms

We are on Postgres 9.1 . Is there a postgres feature allows to add multiple boolean fields with default values in one shot? This is for a database change management /upgrade solution . Is it better than using temp table to copy and insert to add multiple boolean fields with default values to a table ? The temp table approach is described in this blog: http://blog.codacy.com/2015/05/14/how-to-update-large-tables-in-postgresql/

Craig Ringer · Accepted Answer · 2015-11-13 02:45:38Z

5

You've already shown the best (simple) way - a compound ALTER TABLE statement that adds them all at once.

To do it without a long lock, you have to do it in multiple steps. Add the column as nullable without the default. Add the default but leave it nullable. UPDATE all existing rows to add the new value, preferably in batches. Then finally alter the table to add the not null constraint.

answered Nov 13, 2015 at 2:45

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Epigene Over a year ago

This is a hassle to do, but sadly the only reliable way to avoid locking the table for a long time.

Collectives™ on Stack Overflow

adding boolean field with default value on a large postgres table

1 Answer 1

1 Comment

Your Answer

Collectives™ on Stack Overflow

adding boolean field with default value on a large postgres table

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest