Add method for creating foreign keys concurrently
This adds a migration helper that can be used to add foreign keys without locking the source table forever.
To verify this approach indeed does not lock tables I created a database with two tables:
- users
- id (primary key)
- name
- posts
- id (primary key)
- user_id
- title
- updated_at (timestamp)
I filled the users table with 1 million rows, and the posts table with 6 179 000 rows. I then had a separate script update random posts
rows periodically, printing out the time it took for the update to complete.
When adding a foreign key the usual way the UPDATE queries would be blocked for a second or two. Using the two-step process used by add_concurrent_foreign_key
no noticeable increase in UPDATE timings was observed.