Read Only Table Swap

Imagine you have a scenario where you have a large table which stores referential information. Imagine that this table stores millions of rows of read-only data, and needs to be updated every so often (maybe every day).

First approach: Every day, run a job that truncates the existing table and then insert the new rows.

Pros:

• Simple

Cons:

• Truncate commits a transaction (on MySQL 5.x and certain vendors), so if the insertion fails, you are left with an empty table.

Second approach: Every day, run a job that creates a different table, inserts the new rows into that table, and then swaps the table.

Pros:

• All of the actions an be rolled into one transaction.
• If any part of it fails, you can send an alert and fix it immediately.

Cons:

• Longer query which lowers database performance while it executes.

To do the swap, we will do the following.

START TRANSACTION;
CREATE TABLE ${new-table} LIKE ${table-to-replace};
INSERT INTO ${table-to-replace} (${columns}) VALUES (${rows});
RENAME TABLE ${table-to-replace} TO ${table-to-drop}, ${new-table} TO ${table-to-replace};
DROP TABLE ${table-to-replace};
COMMIT;

If any of the above fails, nothing will happen; then we can figure out why the operation failed.