3 ms·
There's also an impact on inserts; it's not just deletes. Essentially a foreign key constraint is an on {insert,update,delete} trigger which checks whether the
by arp242 3y ago
There's also an impact on inserts; it's not just deletes.
Essentially a foreign key constraint is an on {insert,update,delete} trigger which checks whether the target check exists, so that's a select on the target table. I'm not sure if that's still the case, but I believe that for a long time foreign keys were just implemented as triggers in PostgreSQL.
For a lot of things that has a minimal performance impact and it's not a big deal. For some other things it can really add up.
- binarymax 3y agoFKs are typically primary keys/clustered indices on the related table, so in those cases the integrity overhead would be very minimal. So in the vast majority of cases, the integrity is worth it.
- arp242 3y agoI assume you mean "performance overhead"? For insert-heavy workloads the difference very much is not "very minimal". In a quick test it's ~100ms vs. ~20ms with two foreign keys which check against a "countries" table (250 rows) – one column for nationality and one for residence. ±a lot because this is a very quick test I ran just twice, but it fits what I measured before when I very much ran in to this performance penalty a few years back. And look, 20ms vs. 100ms is very much fine for a lot of use cases. But it's also not for a lot of others. And it's certainly not very minimal, and with many inserts it does amplify a lot.