Every story and comment from the Hacker News API, in a layout borrowed from 2001.
A comment on Reddit's database has only two tables. Parent comment
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.
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