PostgreSQL also supports concurrent index creation, so if you realize later you need an index on your amazingly large table you can have it built in the background while you are still using the table. (Managing indexes were another locking issue mentioned in the article.)
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 ]
So, rather: 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.
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.
(I also would have expected the PostgreSQL market to be better for this kind of thing, although I'm not in this business, so I'd love to understand where I'm wrong: the general argument being that relying on a third-party commercial index is a much lower commitment--and thereby a simpler sell--than relying on a third-party commercial storage engine, in addition to how most companies using MySQL seem to either know so little about database technology that their criteria was simply "popularity" or, alternatively, so much about database technology that to them MySQL is perfectly fine and they don't really need your solution.)
However, part of the point of storing things in a write-optimized data structure is that you can afford to store "the heap" directly in the index, and get good performance on range queries. Anyway, if you're interested, email us and we can talk about how we might be able to help.
You are right. I think this is a misunderstanding in the original material: if one does not use a default expression (i.e. the default is NULL) then the implementation they are using -- Postgres -- will just twiddle the catalog information. My guess is that they did not know this detailed system artifact -- adding a column of NULL value to table of billions of records is for most purposes as fast as a table of nothing.
No need to do such a fancy switchover of tables, although one will still need to backfill a desired default, as you have indicated in step 3.
(edit, as I forgot the conclusion:) At this point you will have all of your data filled in correctly, so you alter the column to add a not null constraint (if you had wanted that, which you probably did as otherwise you likely wouldn't have needed a non-null default in the first place) and then go back to into your application and go about business as usual using your new column in ways that would affect other data.
http://www.percona.com/doc/percona-toolkit/2.1/pt-online-sch...
A lock (even a very brief one) can be infeasible on a sufficiently high-traffic table, though there's always the possibility of, say, a 30-second maintenance window.
Mysql 5.5 supports atomic table switch, meaning no lock is necessary at all.
It does not take locks, other than for very briefly.
1. Make a new empty table that has the same structure as the table you wish to add a column to. Add your new column to the empty table.
2. Put triggers on the old table that, whenever a row is added or updated, makes a copy of the row in the new table or updates the copy already there.
3. Run a background process that goes through the old table doing dummy updates:
where the WHERE clause picks a small number of rows (e.g., just go through the primary key sequentially). Since you aren't actually modifying the table, all this does is trigger the trigger on the specified rows.4. When you've hit everything with a dummy update, rename the current table to a temp name, and rename the new table to the current table. This is the only step that needs a lock.
There are tools for MySQL to automate much of this. There was a post either here or on Reddit a while back about this which linked to them. I'm sorry but I didn't save a link to it so you'll have to search if you want it.