Postgresql – When postgresql sequences get out of sync

I recommend ordering by timestamp. As long as the only records with identical timestamps are inserted in the same transaction, you can use the sequence-based ID as a secondary ordering.

_{Answer originally left as a question comment.}

For zero downtime during tables refactoring, we create new tables along with old ones, and switch in small steps.

From Developing Low-Maintenance Databases by Alex Kuznetsov on Simple Talk.

Instead of making all the changes at once, we make multiple incremental changes, as follows:

• Make sure that all the modules accessing the table are well covered with automated tests.
• Create new tables. Alter all procedures that modify the old table, so that they modify both old and new tables.
• Migrate existing data into new structure. Do it in smallish batches, so that it does not seriously impact the overall performance on the server.
• Verify that the migration of data succeeded.
• Redirect some of the selecting procedures from the old table to the new ones. Use automated tests to make sure that the changed modules are still correct. Make sure their performance is acceptable. Deploy the altered procedures.
• Repeat the previous step until all the reports use the new tables.
• Change the procedures that modify the tickets, so that they only access the new tables.
• Archive the old table and remove it from the system.

It is true that this approach has many steps and is not very simple; yet we love it – it is far better than its alternatives. Each step incurs very little risk, and if needed, we can roll it back in a split second. We can set the migration aside and switch to another task, or enjoy our weekend, at any time – the system keeps working in any of these intermediate states for as much as needed. Our users do not experience any disruptions.

See the original article for full details.