Migration strategy to partition existing tables: Create partitioned tables
In order to partition existing large tables, we need to implement a migration strategy. The strategy needs to work "online" and without downtime, i.e. it cannot depend on the system being down.
The goal here is to implement migration helpers that allow us to migrate an existing large table to a partitioned new table. It is not possible to partition a table after it got created. This is, we need to create a new partitioned table and migrate existing data into it. This should be transparent to the application, so that any incoming traffic (full CRUD operations) can be served while the migration is running.
The migration strategy is broken into three parts:
- Create a new partitioned table (with appropriate partitions) which otherwise matches the schema of the specified table
- Add a trigger to the source table which will fire on each write, and perform the same write on the partitioned table. This will ensure the two tables remain in sync going forward.
- Use a Background Migration to batch copy the historic data from the non-partitioned table to the partitioned table.
This issue will capture implementation of the first step of the strategy, which is adding a migration helper to build the partitioned table.