I'd much rather see the opposite, declare the FKs on a testing environment NOT VALID, run application code, VALIDATE CONSTRAINT those you'd marked and see if anything is in violation - ensuring that application code won't (in the normal course of operation) generally hit FK constraints can greatly reduce your performance hit from having them... then in prod you can turn them on and be confident in your comparably lighter performance loss to data integrity.
No kidding. My first thoughts went to referential integrity, and getting misbehaving apps successfully past testing...
I think this perspective is something you only get once you've run bigger databases in production.
The costs of having FKs in PostreSQL are:
1) If you update the primary key of the referenced table you might see issues with locking. But this is rare in real world applications.
2) Performance overhead from having to check all FKs and taking the locks. This can be a big issue on some workloads and may force you to add extra indexes. A PostgreSQL specific issue is that FK checks cannot be done in batches.
3) Adding new FKs block writes from the referenced table and dropping FKs lock out both readers and writers from the referenced table. This is a limitation of the implementation.
(Let's not forget, amid all this Postgres-specific chatter, that the article is about GitHub, who use MySQL.)
Those locks would be very challenging to accomplish at the application level.
PostgreSQL has implemented this minimum level of necessary locking for quite many years now.
FWIW, I just tested in Postgres. Locks like I said it does:
A: create table parent(id int, value int, unique(id));
A: create table child(id int, parent_id int references parent(id));
A: insert into parent values (1, 10);
A: begin;
A: insert into child values (1, 1);
B: begin;
B: select 1 from parent where id = 1 for update;
B: (blocks)
I have specific experience of this due to use of database locks at the application level to avoid deadlocks (different lock orders) and inconsistent updates (updates based on reads across multiple tables that may have separate racing updates) by locking rows up front. For understandable schema reasons, what is logically a parent entity is the natural thing to lock, but for understandable performance reasons, FKs to the parent entity are distributed through some fairly large tables.
If A tries to insert a child for 1, and B changes the id to 2.... OOPS! And from both's perspective it looks perfectly safe.
Though, I imagine you could simply not use cascading behaviors.. Neat idea though. Scary, but neat hah.
But seriously, there are a host of issues one must overcome for this plan to work. And there were indeed managed permissions for DELETE. But ultimately, I wasn't aware of much of the implementation detail ...
My feeling on it though is that it just shifts all the faith to your test data being true to not only does but conceivably could happen in prod.
All very well testing with a bunch of constraints, but if it turns out one actually isn't exercised and then happens in production?
It also means you can't use them for validation or whatever. Which, I don't know, maybe you shouldn't anyway, but if you turn them off in prod you can't.
I feel like this was a stopgap on the way to eliminating FKs. I have no idea what his roadmap was because I wasn't in that group. But his process made for good thought fodder.