Re:Do you really need foreign keys?
by cogman10 on Thursday December 21, 2023 @02:14PM
(#38725285)
> a very small performance gain for strongly degraded data integrity.
It's not very small. Doing a lookup on every write can be hugely detrimental to performance especially with large numbers of writes.
> If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!)
That does not follow. The lookup for foreign records is not free so avoiding doing it when you don't need to will gain faster performance vs doing it all the time. Indexes make lookups faster, they don't make them free.
Further, you have to consider the impact of locks on such a system. Writes to a table that references another will lock the contents of the second table while the write is in flight. So if I wanted to update the foreign record in any way, that task now gets blocked until data integrity check finishes.
In MSSQL, that lock is held until the end of the transaction.
It's not very small. Doing a lookup on every write can be hugely detrimental to performance especially with large numbers of writes.
> If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!)
That does not follow. The lookup for foreign records is not free so avoiding doing it when you don't need to will gain faster performance vs doing it all the time. Indexes make lookups faster, they don't make them free.
Further, you have to consider the impact of locks on such a system. Writes to a table that references another will lock the contents of the second table while the write is in flight. So if I wanted to update the foreign record in any way, that task now gets blocked until data integrity check finishes.
In MSSQL, that lock is held until the end of the transaction.