-
Notifications
You must be signed in to change notification settings - Fork 0
How to migrate the database
Database migration, or changing the schema, always carries a certain amount of risk in a production system. This document is intended to highlight the specific risks of changing a database used by two different applications.
At the time of writing, the Challenge portal is built on the Elixir/Phoenix stack. The end state of the application will be to retire the Phoenix app and run everything on Rails. Until that is achieved, there will be an indefinite period where two applications work in tandem to deliver the full set of features.
- Challenge Portal (Manage Challenges & Submissions)
- Challenge Platform (Evaluation & Submission)
The site aims to achieve zero downtime for regular operations such as data migration. This imposes certain restrictions on what is possible and how to accomplish the desired schema changes.
Specifically, any changes that are destructive of existing data are not allowed until all applications have been update to remove their dependence on that data.
- Dropping a table
- Removing a column
- Renaming a table
- Renaming a column
- Changing the data type of a column
- Adding a new table
- Adding a new column
If the data is not being actively used in the current app version, with any destructive operation, consider the following:
- API stability - consider whether older clients, whether browser- or mobile-based, might not be updated at the same time as the deployed backend server, and handle any related errors appropriately.
- Backup processes - some scripts or backup systems might depend on specific tables or columns to be present
- External systems - are there any external systems that might export or import the data?
- Historical data - If the table could be useful for understanding the system in the future, and does not add significant storage costs, it can be retained with little cost. Provide documentation about the decision to retain historical data and the context of its usage at the time it was retired.
In order to drop a table, first ensure that the table is not being used or referenced anywhere in the application(s). If the data is being used, first deploy changes that remove those dependencies. If the data is required but no longer needs to be updated or used in any SQL queries, it might be converted into a static file format such as JSON or stored in some other document storage solution. In the case of APIs that must be maintained for backwards compatibility on older clients, this static data can be used in lieu of the table reference.
Once that is complete, you can drop the table. Create a new migration on the Rails app and run it on the next deployment.
In order to remove a column, first ensure that the column is not being used or referenced anywhere in the application(s). Do not rely on tests; perform a full text search of the codebases for the column name. If the data is being used, first deploy changes that remove those dependencies. A default value representing empty data can be added to legacy APIs to maintain backwards compatibility.
Once that is complete, you can remove the column. Create a new migration on the Rails app and run it on the next deployment.
Although the SQL to accomplish this is simple, the impact on the codebase is not. Logically the effect is dropping and adding tables. If it's necessary:
- Create a copy of the existing table and perform dual-writes in both applications to keep them in sync
- Create a new table migration in the Rails app and run it on the next deployment
- Deploy updates to both apps that write to both tables for any data changes
- Update both applications to use the new table as the primary table for models and schemas. Deploy both applications to production.
- Deploy changes to stop dual-writing in both applications. The older table will be frozen for writes at this point (no further updates).
- Create a migration in the Rails app that drops the unused table.
The strategy is similar to renaming a table. Create a copy of the column, write to both columns until the applications are updated to only use the new column, stop dual-writes and drop the older column. If needed, alias the old column to the new one to maintain backwards compatibility for API clients and external systems support.
Changing data types requires additional planning depending on the old and new types and whether the conversion results in potential data loss, e.g. changing float to int. Consider the tradeoffs and plan accordingly. Consult with the technical lead to create a plan before attempting this type of migration. The UI inputs should be updated to reflect the new types. Users may need to be notified if there is potential data loss from the migration.
New data can be added more easily than changes that destroy or modify existing data. For new data, create the migration in Rails. In most cases new data will be tied to new features (Rails app only) and therefore no changes required for Phoenix. Migrations are run as part of the normal deployment process before code is updated, so that you can be assured that the schema will exist when the app code is updated. To avoid any risk of bad migration causing production issues, you can separate the deployment of migration changes from the deployment of code changes that reference that migration.