Skip to content

Add method for creating foreign keys concurrently

Yorick Peterse requested to merge concurrent-foreign-keys into master

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
    • email
  • 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.

cc @smcgivern @rdavila

Merge request reports