End Point

News

Welcome to End Point's blog

Ongoing observations by End Point people.

Speedier column additions in PostgreSQL

Say you want to add a column to a large table, e.g.,

ALTER TABLE transactions ADD COLUMN email_sent BOOLEAN DEFAULT FALSE;

You really do want new rows to start out with the column "false" (if no value is supplied when the row is created). However, you also want all existing rows to be "true", so innocently:

UPDATE transactions SET email_sent = TRUE;

This is a great time for a coffee break, or a trip to the post office, or maybe (if you're a telecommuter like me), a stroll out to weed the garden. Unfortunately for all those side activities, you really didn't have to take the long way.

BEGIN;
ALTER TABLE transactions ADD COLUMN email_sent BOOLEAN DEFAULT TRUE;
ALTER TABLE transactions ALTER TABLE email_sent SET DEFAULT FALSE;
COMMIT;

This is a lot faster; create all the columns with the appropriate value, then set the default for new rows, and all inside a transaction so you know it gets done atomically.

2 comments:

Jon Jensen said...

Yay for only one sequential scan through the table!

David Christensen said...

It's nice also to be able to combine multiple column additions/removals using the comma-delimited form of ALTER TABLE:

ALTER TABLE foo ADD COLUMN bar text, ADD COLUMN baz int, DROP COLUMN bad;

This will also perform all of the actions with just a single scan through the table.