/
Adding a Foreign Key to a Large Table

Adding a Foreign Key to a Large Table

Owned by Jesper Hodge
Last updated: Mar 05, 2025
1 min read

Adding a foreign key to an existing large table is a complex process that needs to be done very carefully to avoid downtime.

General Requirements

Before continuing here, you should fully read Everything About Database Migrations , paying special attention to the section about large tables.

For large tables, you generally cannot “just” do a migration that adds a foreign key. ALTER TABLE operations in MySQL - and that includes AWS Aurora for MySQL - can have a significant performance impact on large tables, which causes downtime. In the Openedx space, we avoid downtime.

MySQL 8.0, and with that, Aurora for MySQL version 3, supports Fast DDL. Fast DDL provides options for specific operations to alter a table while keeping the database online and operating in a performant way. So to add a foreign key to a large table, we need to use Fast DDL and make sure that we do not perform slow operations that involve things like rebuilding the table.

To do everything correctly, you should acquire an in-depth understanding of the topics involved. Carefully read these documents:

For every operation performed by the migration, you want to ensure that it does not rebuild the table, and that it is performed in place.

Recommended steps

This work has not been tested on stage+prod yet, so follow at your own risk. I’ll update this soon.

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.

 

Related content