...

General Insights

• Some of the operations that incur downtime can be problematic even for smaller existing tables (adding primary keys etc).

• Online DDL Operations, Instant DDL (Aurora), Fast DDL (these cover roughly the same concepts) are key for performance and avoiding downtime.

• Directly adding a foreign key incurs downtime.

• Making a foreign key non-nullable incurs downtime.

• Adding a new column is fine.

• Adding an index to an existing column where all values are NULL can be done without downtime using ALGORITHM=INPLACE, LOCK=NONE. Make sure there are no non-null values.

  • While the database may add these parameters by default, add them explicitly, to make sure they are not silently ignored if they are not supported.

• When doing bulk updates, like backfilling values in management commands, you need to avoid reading or updating all records at once to avoid loading everything into memory or performing a slow query.

  • Use batching for reading and writing

  • MySQL does not support streaming. Thus, if you use <queryset>.iterator(), it will ignore it and still load all rows into memory without batching. Instead, use something like the batch_by_pk method in the edx-enterprise repo.

  • Querysets are evaluated at some point, loading the contents into memory / performing the SQL query. Understand when they are evaluated and make sure they’re not evaluated prematurely.

  • Use transaction.atomic.

  • Check generated SQL queries. With bulk_update, for example, you generate a pretty big query with a lot of WHEN/CASE statements. That is fine, but make sure that the query size does not exceed permitted query size for the db.

• Adding the foreign key constraint itself for nullable values might require wrapping the SQL in SET FOREIGN_KEY_CHECKS = 0; and SET FOREIGN_KEY_CHECKS = 1; to temporarily disable checks on foreign key links to referenced model.

Recommended steps

This PR includes a carefully crafted strategy for adding a foreign key to a large table. You can use this as a boilerplate example.Further description will be added here soon.

Here’s a rundown of what works without downtime. Each step takes a separate PR, and they should be deployed in order, not just at once.

• Add new INT field first, all NULL values

• Create index on top of new field with ALGORITHM=INPLACE, LOCK=NONE

• Backfill values in batches for field via management command

  • Adjust save() method, django signals, or something like that to make sure that new records will not receive NULL values before next step

• Ensure there are no more NULL values in the db field

• Add foreign key to field with ALGORITHM=INPLACE, LOCK=NONE. It still needs to be nullable.

• Add any desired constraints to prevent null values to the django code, but not to the database.