tl;dr: We removed all of our foreign key constraints because they were locking tables for too long and this led to outages.

In April 2026, Fullscript was experiencing outages. They usually only lasted a few minutes and affected a subset of our pages, but were frequent enough that we knew we had a real problem that needed to be addressed. The trigger was database schema migrations, but we didn't know the root cause of the problem. Some schema migrations, even on large tables (>10M records), were applied without a problem while others froze our app. While this wasn't a problem in the past, we had reached a scale where the effects were noticeable and we needed to better understand table locking in MySQL.
Locks and algorithms at a glance
To understand the problem, we first need to understand a few different terms and concepts around database locks and the algorithms used for schema changes.
For the purpose of this post there are two different kinds of locks in a relational database: row locks and metadata locks (MDL). Row locks are the locks that "normal" data manipulation language (DML) queries like SELECT ... FOR UPDATE, INSERT, UPDATE, and DELETE statements obtain when reading or mutating data in a table. The maximum overall time that a query is allowed to run is determined by max_execution_time (default is 0, i.e. infinite) while the maximum allowed time to wait for a row lock is governed by innodb_lock_wait_timeout (default is 50 seconds). I'm glossing over A LOT of details, but this is what most developers need to know. Metadata locks, on the other hand, are locks on the table definition. DML queries require a shared metadata lock while data definition language (DDL) queries such as ALTER TABLE, CREATE INDEX, RENAME TABLE, etc. require exclusive metadata locks. Once an exclusive metadata lock has been requested, no new queries are processed for that table until the lock has been acquired and released. The timeout for metadata locks is governed by a separate system variable, lock_wait_timeout. Its default value is one year and I think that's hilarious.
Next, there are algorithms. As documented here, different DDL operations support different algorithms which are tied to locking mechanisms. Some statements such as an ALTER TABLE statement that changes a column's data type only support the COPY algorithm which requires a metadata lock (MDL) on the table and block other locking statements for the full duration of the change while the table is copied. These are the hardest to handle. Other statements such as adding an index support the INPLACE algorithm which requires a brief (milliseconds) MDL at the start of the operation and a second brief MDL at the end. Finally, statements such as adding a new column can use the INSTANT algorithm which requires a single brief MDL. These are the easiest to handle.
Just knowing this gave us much more confidence in our schema migration process and made it easier to know which changes would be risky vs. which ones would be safe without manually testing each one. Once we understood this, we added some guardrails to our BaseMigration class such as the ones shown below. While MySQL supports the INSTANT algorithm when adding a new column, which one it chooses at runtime isn't guaranteed. By specifying the algorithm we want, we force the database to raise an exception rather than use a different algorithm and silently lock tables more than expected. We also set timeouts to more reasonable values. innodb_lock_wait_timeout is now set to 30 seconds so that our app doesn't hang when it can't obtain a lock on a record it wants and we set lock_wait_timeout to 10 seconds. This way, schema migrations fail fast and can be retried. Adding IF NOT EXISTS made them idempotent.
1def add_column(table_name, column_name, type, **options)2 options[:algorithm] ||= :instant3 options[:if_not_exists] = true if options[:if_not_exists].nil?4 super5end678def add_index(table_name, column_name, **options)9 options[:algorithm] ||= :inplace10 options[:if_not_exists] = true if options[:if_not_exists].nil?11 super12end
Foreign keys enter the picture
Despite setting short row lock and metadata lock timeouts, we still encountered outages during certain migrations that would last over 2 minutes. We verified that the settings were applied to all of our production environments, double-checked them with a migration that logged the values to ensure that our session wasn't configured differently, questioned our sanity, and nothing fit. But late one night after a long conversation with Claude, we found the problem: foreign key constraints. As it turns out, whenever you modify a table, you not only need a metadata lock on that table, but on all of the tables that point to it. More precisely, you need a simultaneous lock on the modified table and on all of the tables that have a foreign key constraint towards it. During a migration, MySQL first obtains a lock on the modified table, and then all of the child tables in alphabetical order to avoid deadlocks AND each lock resets the timeout! (MySQL docs) That means a table with 20 foreign key constraints pointing to it could maintain a lock for up to 210 seconds for lock_wait_timeout=10 ((20 child tables + 1 parent table) x 10s). We had found the problem...
Removing foreign keys
Foreign key constraints had always been a recurring topic of discussion at Fullscript. The obvious benefits of using them are that they perfectly enforce data integrity and we don't need to worry about race conditions involving ActiveRecord validation. The drawbacks are that they can make testing tricky by forcing us to create additional records that aren't required during testing. They would also prevent us from partitioning (sharding) our database someday, but that day was always far in the future. Up until April, we understood these benefits and drawbacks and let developers choose whether or not they wanted to use them. However, this newly found performance gotcha made the decision clear: we had to remove all of them.
We had 497 foreign key constraints throughout our schema, so we knew we had to be systematic about their removal and careful that we didn't break anything in the process. We broke the work into 3 steps.
Step 1: Add validation
We made a list of all of the affected tables, found their corresponding ActiveRecord models and added any missing validation. Most cases were straightforward: replace belongs_to :store, optional: true with belongs_to :store or add a new relation if it was missing entirely.
Step 2: Make removal foolproof
To ensure that we didn't miss any validation, we made sure that a schema migration that attempts to remove a foreign key constraint literally could not run unless the correct validation was present. For this, we added the strong migration rules in the appendix below. (The strong_migrations gem is great, btw.)
Step 3: Remove them all!
The final step was just to remove them all. We spread out the removal across 17 migrations, grouping them by "tables with 1 child table", "tables with 2 child tables", etc. and some individual migrations for large important tables.
Predictable migrations
Since the change, performance issues related to schema migrations have been both rarer and shorter. For large tables (>10M records), we restrict migrations to times when our application has less traffic and for migrations that require extended locks (ALGORITHM=COPY), we use the lhm-shopify gem. While application-level validation isn't perfect, it's been well worth the tradeoff.
Appendix
Strong migration rules
1# TODO: Remove this once all foreign keys have been removed2StrongMigrations.add_check do |method, args|3 next unless method == :remove_foreign_key4 next if version && version < 2026_06_02_000000567 from_model_name = args.last.try(:dig, :from_model_name)8 unless from_model_name9 stop! <<~END10 You must provide a from_model_name to remove a foreign key11 to ensure that the validation is preserved by ActiveRecord.12 END13 end141516 from_model = from_model_name.safe_constantize17 # Schema migrations must remain runnable even if the model name is removed18 next unless from_model192021 from_table = args[0].to_s22 to_table = args[1].to_s23 belongs_to_reflection = from_model.reflections.values.find { |reflection|24 reflection.macro == :belongs_to && !reflection.options[:polymorphic] && reflection.table_name == to_table25 }262728 unless belongs_to_reflection29 stop! <<~END30 Do not remove foreign keys from #{from_table} to #{to_table}31 unless there is a belongs_to relation in #{from_model_name} to validate the foreign key.32 END33 end343536 fk_column_name = belongs_to_reflection.foreign_key.to_s37 fk_column = from_model.columns_hash[fk_column_name]383940 if fk_column && !fk_column.null && belongs_to_reflection.options[:optional]41 stop! <<~END42 Because the foreign key column #{fk_column_name} on #{from_table} is not nullable,43 the belongs_to relation in #{from_model_name} must not be optional.44 END45 end46end

