-
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 applications. 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, the the data might be converted into a static file format such as JSON or stored in some other document database. In the case of APIs that must be maintained for backwards compatibility on older clients.
Once that is complete, you can drop the table.