Hacker News Reader

Comment by hinkley | Hacker News Reader

The one that always got us is much more mundane. Deleting a row requiring an index in every table with a FK to avoid tables scans.

Near as I can tell, we assume there is some bit of magic built into the foreign key concept that handles this for us, but that is not the case.

View on HN

Yeah I’ve run into this a few times. In Postgres, you can’t add a foreign key referencing columns that aren’t indexed. I assume this is for performance reasons so inserts/updates are efficient, but there is no such check that the source columns are also indexed, so updates/deletes on the referenced table can be terribly slow.

Isn’t the source almost always an indexed primary key?

No. The target is normally a primary key but the source is not. The source is rarely even unique.

Foreign keys are naturally referencing primary keys, which have their own (unique) indexes by default. And there's some extra magic with CASCADE.

But it seems like I am missing your point: care to expand on it so I can learn about the gotcha as well? What are you attempting to do, and what's making the performance slow?

If I've understood correctly it's the foreign key column itself that isn't indexed by default. Deleting a record then requires a table scan to ensure that the constraint holds

Example: if you delete a record from the customers table, you want an index on the foreign key in the orders table to delete the corresponding entries. This also means the performance of the delete can have the unfortunate property where the small work of deleting a single row cascades into the work of deleting many rows.

Which is why many elect to just ban deletes.

That’s not the reason. The reason why people don’t delete is because nobody wants to be left with inconsistent data relations. Deleting a customer is more deleting their PII(our scrambling it) and leaving everything else in tact.

Or in the case of Silicon Valley, leave everything in tact with a disabled flag and then spam you for the next decade or so.

I don't buy that reason, because that inconsistency can be easily prevented with good schema hygiene (either ON DELETE NO ACTION or ON DELETE CASCADE). The problem is rather that in order to maintain consistent data relations, delete operations must be carefully designed and that part of the functional design is usually skipped because it's perceived as not important.

Which is more or less what the GP says as well, deletes are not implemented because doing it properly requires proper design.

Reply on news.ycombinator.com