How to Migrate High-load Tables With Zero Downtime Using Background Jobs and SQL Views

Full accessibility to all the data during the entire migration process for read, write, delete and update.

Shortest downtime possible.

Minimum of data duplication (no database copies or full copies of the migrated table).

Migration triggered and controlled from the web-framework.

No changes to the existing framework code - only some additions related to the migration triggering and monitoring. No additional deploys needed during the entire process.

The first table (‘old’) is the actual old table with lots of rows.

The second table (‘new’) copies the structure of the ‘old’ table, but has the required modifications (bigint primary key in our case). This table is the resulting one, it will store the data and remain in the system after the migration is finished.

The third table(‘temp’) is used to handle ongoing inserts, updates and deletes caused by users. Its structure copies the ‘new’ table.

Create the new table, modify it and rename the old one:

Create the view to emulate the ‘cars’ table behavior on SELECT queries from the web-framework:

Set the next id value for ‘cars’ to the current cars id sequence.

Create a function to handle the insert, update and delete actions coming from the web-framework:

Create the trigger to redirect the calls to ‘cars’ table to the function created above.

Open a transaction.

Call the sync function one last time to make sure no data is lost. (‘SELECT move_cars(10000000);’)

Execute the swap and cleaning code.

Close the transaction.