Feb 23, 2020 · ⏱ 3 minutes

Utilizing the data layer: Deadlock Relations with Deferred Constraints πŸ›‘

When designing your application's data layer, you might want to enforce separation of concerns and split tables by the data they hold, for example, moving all optional user data to a profile table and keeping track of the most important details like their name, email address and phone number in the primary account table.

Until now, everything described, you'll be able to realize with standard foreign keys, nothing unusual to be seen. But we'll introduce another requirement: An account must be linked to a profile and vice-versa. Of course, we could just create both and not worry about any case where one gets dropped by using cascading deletes, but we can go one step further.

πŸ”’ Creating a deadlock relation

We'll create two foreign keys, both on the account and profile id fields which identify an account, pointing to the id field of the reverse table. Since we've also chosen id to act as the primary key, we'll have a unique, non-nullable configuration added implicitly which requires us to create both an account and profile with the same identifier at once.

The problem we've now pushed ourselves into, is, that we cannot create records since we'll immediately receive a foreign key constraint violation upon creating either the account or profile, since the other side just doesn't exist yet.

However, by using, what's called a deferrable constraint in a deferred state, we can mitigate this issue completely!

⌚️ Deferred Or Immediate?

Postgres allows to create DEFERRABLE or NOT DEFERRABLE (immediate) constraints. This means, that any constraint violation checks of constraints including UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) will not be raised until the transaction is about to be committed, resulting in the possibility to handle deadlock relations in a graceful way. If we're able to create both account and profile records, we'll end up with no constraint violations upon committing the transaction, since no constraints are violated!

To create a deferrable constraint, we'll have to change our constraint creation syntax from

CREATE TABLE "account" (
-- ...
FOREIGN KEY("id") REFERENCES "profile"("id")


CREATE TABLE "account" (
-- ...

In addition to the DEFERRABLE, which makes our foreign key able to be deferred, or executed immediately as usual, the INITIALLY DEFERRED will set our default preference to using this constraint in deferred mode, rather than raising constraint violations immediately when executing a transaction statement. Another option would be to set it to INITIALLY IMMEDIATE, the default option, which will immediately raise any constraint violation once the statement is executed, not waiting for the end of the transaction.

πŸ—‘ Cleanup, Made Easy

If we were to delete an account, be it in deferred or immediate mode, we'd have to make sure to delete both the account and profile record, as we wouldn't want to maintain any data we'd want to be deleted. In this case, deferrable constraints don't do a lot for us, though allowing us to send two statements to clean up both tables' records instead of failing at the first DELETE statement due to our deadlock relation.

Luckily, Postgres has us covered here again: We can change the constraint to register a policy to handle deletes of the account record by deleting the related profile with it. This is as simple as adding an ON DELETE CASCADE to the foreign key on the profile table.

CREATE TABLE "profile" (
-- ...
FOREIGN KEY("id") REFERENCES "account"("id")

We might also want to prevent the profile from being deleted altogether, as the actual delete should be executed on an account. This can be realized simply by adding ON DELETE RESTRICT to the foreign key on the account table.

🚨 Alerting Early

In special cases, we might want to know whether a deferred constraint would be violated by an action we're about to take, and we'd like to know immediately, not at the end of our transaction. Once again, Postgres has us covered. The SET CONSTRAINTS command allows exactly this: You can specify a known constraint by name or simply toggle all constraints to immediate for the scope of the transaction. To get the most control over which constraints are currently deferred and which are run immediately on execution, we should refer to our constraint by name.


As you can see, designing deadlock relations makes heavy use of various constraint features Postgres has to offer, allowing us to add one workflow on top of another. Once done, we end up with a strict relationship between accounts and profiles, giving guarantees as to which may exist under certain conditions.

I hope you enjoyed this post and learned a thing or two about relations in Postgres. If you've got any questions, suggestions or any feedback at all, don't hesitate to reach out on Twitter or by mail.