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/

"Is there a postgres feature which makes this happen?" I don't understand this question. — Gordon Linoff
– Gordon Linoff, Commented Nov 12, 2015 at 13:49
Makes what happen? (by the way: 1 million rows is far from being "very large") — user330315
– user330315, Commented Nov 12, 2015 at 13:50
When we update a value in a column, Postgres writes a whole new row in the disk, deprecates the old row and then proceeds to update all indexes. This is equivalent to an INSERT plus a DELETE for each row . So in this case we are adding 3 boolean fields , Is Postgres adding these fields in one shot? It should be faster to create a new table from scratch than to update every single row. Sequential writes are faster than sparse updates. I have implemented a solution using temp table but with this feature , I think there is no need for such a solution — Shailendra
– Shailendra, Commented Nov 12, 2015 at 15:39

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

329k83 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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related