26 ms·

How often is data actually DELETEd from production databases? Unless following through with regulatory removal such as GDPR, it’s far better to use an isdelete

How often is data actually DELETEd from production databases? Unless following through with regulatory removal such as GDPR, it’s far better to use an isdeleted flag IMO (so you can un-delete if the action was a mistake).

pphysch 3y ago
That's true, but managing the "soft" referential integrity becomes a big authorization/security headache. How do you prevent/allow "deleted" objects from being accessed indirectly? I like Django's ORM approach which allows you to easily set baseline filters for your Model Managers, so you can implicitly exclude "deleted" objects from most queries.
- binarymax 3y ago
  Good point. I’m one of those “never-ORM” people, so writing & maintaining the SQL is prone to error when the DB gets complex, and it does introduce mental overhead during dev.
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 ago
  FKs 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 ago
    I 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.

pphysch 3y ago

That's true, but managing the "soft" referential integrity becomes a big authorization/security headache. How do you prevent/allow "deleted" objects from being accessed indirectly? I like Django's ORM approach which allows you to easily set baseline filters for your Model Managers, so you can implicitly exclude "deleted" objects from most queries.

binarymax 3y ago
Good point. I’m one of those “never-ORM” people, so writing & maintaining the SQL is prone to error when the DB gets complex, and it does introduce mental overhead during dev.

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 ago
FKs 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 ago
  I 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.