Build1 publisher2 min readPublished
Laravel's one-line foreign key migration stalls writes on two Postgres tables
Postgres locks both tables with SHARE ROW EXCLUSIVE when a migration adds a foreign key, blocking writes until every child row is scanned. A dev.to guide on Laravel migrations splits the change into two statements so the slow scan stops holding up the app's writes.
The Engineer · Build desk
What happened
- The guide's fix first adds the key with NOT VALID, which skips existing rows and holds SHARE ROW EXCLUSIVE only briefly.
- A separate VALIDATE CONSTRAINT then does the row scan under SHARE UPDATE EXCLUSIVE, a lock the guide says does not block writes.
- Laravel's schema builder has no fluent NOT VALID modifier for foreign keys, so the Postgres version is raw DB::statement() calls on any Laravel version.
- On MySQL InnoDB the guide adds the key online with ALGORITHM=INPLACE, LOCK=NONE, after turning foreign_key_checks off for the session.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- exposure Writes to customers can stall on a migration whose code names only orders, so the parent table's traffic belongs in the review of any foreign key change.
- cost Laravel teams on Postgres write and maintain raw SQL for each foreign key on a busy table, and a different statement again for MySQL.
- constraint MySQL's lock-free add skips checking existing rows against customers, so proof that orders holds no orphaned rows has to come from a separate query.
The guide starts from one line of Laravel: `$table->foreignId('customer_id')->constrained();` inside a Schema::table call on orders [8]. A foreign key looks like metadata, the guide says, but adding one to an existing table means proving the rule already holds for every child row, and that scan runs under a lock [1]. Postgres already picks the gentler option. Most ADD CONSTRAINT forms take ACCESS EXCLUSIVE, while ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE [2]. Anything trying to write an order during the scan will not notice the difference [3].
The two-step version is good engineering on Postgres's part. It separates the work that needs a write-blocking lock from the work that takes time. The NOT VALID add holds its lock without scanning, and the scan moves to VALIDATE under a lock that does not block writes [4][5]. The guide uses the same split for NOT NULL columns in its second part [13].
The guide's prose and its sample disagree on where the split falls. Step one belongs in the normal deploy, the prose says, and step two runs separately, in a quiet period if the table is large, any time after step one [6]. Both ALTER TABLE statements sit in the same up() method in the sample [14]. Following the prose means two migrations, or a migration plus a separate job for VALIDATE.
No lock timeout is set on the Postgres side either [14]. For MySQL, the sample sets lock_wait_timeout = 5 before its ALTER and restores the default afterwards [11]. "Briefly" in the guide describes how long step one holds its lock once it has it [4]. On a busy orders table I would also want a limit on how long it waits to get it.
On MySQL, according to the guide, leaving foreign_key_checks on gets ALGORITHM=INPLACE rejected outright with an error [10]. Its MySQL sample is a single ALTER TABLE with no separate validation statement [15]. The available text of the guide ends mid-sentence in that section [16]. It stops before explaining what MySQL does with existing rows, and before the cascading-delete mistake its introduction says has nothing to do with locking [12].
What to watch
- The rest of the guide, including the cascading-delete mistake its introduction says has nothing to do with locking.
- Whether Laravel's schema builder gains a fluent NOT VALID modifier for foreign keys, as it already has online() for indexes.
- What the guide says MySQL does with rows already in orders that violate the key when foreign_key_checks is off.