5 ms·
Alternatively, you can use a database such as PostgreSQL, which stores metadata about tables in other tables, allowing you to not only add a column without lock
by saurik 14y ago
Alternatively, you can use a database such as PostgreSQL, which stores metadata about tables in other tables, allowing you to not only add a column without locking the table but do so as part of a transaction with other changes that can all be rolled back atomically on failure.
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.)
- fdr 14y ago> PostgreSQL also supports concurrent index creation 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.
- fdr 14y agoTrue to forgettable SQL-ish (did you know that indexes are not addressed by the SQL standard?) syntax, I got it slightly wrong: $ 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 ....
- SwellJoe 14y agoAnd, reddit uses PostgreSQL.
- dotborg2 14y agoapparently they are doing it wrong
- mesuutt 14y agowhy?
- dlss 14y agoThis is a deep question. Brainstorming I get: - 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?
- daigoba66 14y agoThis is also true for MS SQL. However online index operations do require the expensive licenses, unfortunately. Postgres is amazing.
- eckyptang 14y agoAck. SQL 2008 Enterprise hurt badly, especially as we have a couple of machines with 8 physical CPUs. +1 for PostgreSQL.
- DanielRibeiro 14y agoAlternatively, if using Active Record/Rails, you can use Large Hadron Migrator gem: http://backstage.soundcloud.com/2011/05/introducing-the-large-hadron-migrator-3/ http://backstage.soundcloud.com/2011/05/introducing-the-larg...
- DanielRibeiro 14y agoIn trying to undesrtand why such a negative reaction, soundcloud's take on this goes beyond just ruby or rails. 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 https://github.com/freels/table_migrator [2] http://www.facebook.com/note.php?note_id=430801045932 http://www.facebook.com/note.php?note_id=430801045932
- jbert 14y agoPresumably not that exact algorithm. 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.
- jeffdavis 14y ago"allowing you to not only add a column without locking the table" 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.