I use this all the time, and am flabbergasted how people can do without it. I feel like migration frameworks should make it the default with Postgres.

It's too bad it can't be mixed with transactional DDL, but because indexes are not logical changes, I don't really care as much, even if it is dissatisfying.

So, all in all, for those who want to take advantage of this feature in Postgres:

Stop doing this:

CREATE INDEX foo ...

Start doing this:

CREATE INDEX foo CONCURRENTLY ...

For the cost of one keyword, your index additions can be a non-event.

    $ psql
    fdr=> \h CREATE INDEX
    Command:     CREATE INDEX
    Description: define a new index
    Syntax:
    CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
        ( { column | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
        [ WITH ( storage_parameter = value [, ... ] ) ]
        [ TABLESPACE tablespace ]
        [ WHERE predicate ]

    CREATE INDEX CONCURRENTLY foo ....

+1 for PostgreSQL.

- Steve and Alex founded reddit fresh out of school, and schools don't generally teach databases beyond the theory

- Fresh out of school, they didn't have the opportunity to learn it from someone with more experience

- They were busy building the rest of the site, and so didn't spend the time to delve in to these concepts

- Paul either didn't feel the need to explain it to them, or had reasons similar to the above to not know

Hmm... I'm out of ideas. Anyone else?

To be more clear, the actual advantage is that adding a column in postgres is an O(1) operation if the default value is NULL. It still requires taking a lock, but for many workloads you won't notice it. You still need to be aware of it though, because it can cause problems if you have long-running transactions.

They detail an approach that goes extends tzs' suggestion:

1. Get the maximum primary key value for the table

2. Create new table and journal table

3. Activate journalling with triggers

4. Perform alter statement on new table

5. Copy in chunks up to max primary key value to new table

6. Switch new and original table names and remove triggers

7. Replay journal: insert, update, deletes

Not only did the have success with this approach, they studied Facebook's approach[1], and Twitter's[2], and explain why it didn't work for them.

[1] https://github.com/freels/table_migrator

[2] http://www.facebook.com/note.php?note_id=430801045932

There's a race between (1) and (3) where new entries could be created (increasing the max primary key value) before the triggers are in place.