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.