3 ms·

The performance gain by dropping foreign keys doesn’t hold water. You still have to do the referential checks in application or in the ORM code. Unless you mean

by ww520 3y ago

The performance gain by dropping foreign keys doesn’t hold water. You still have to do the referential checks in application or in the ORM code. Unless you meant dropping referential checks.

arrowleaf 3y ago
The only time I could see it really impacting performance is during bulk operations. But that's where making FK constraints deferred and wrapping the bulk actions in a transaction makes more sense.
crazygringo 3y ago
Not really -- in practice, it generally means all inserts are done as a transaction, and deletes simply aren't done. Then there isn't really any opportunity for inconsistent data.
magicalhippo 3y ago
Might be a performance penalty as well. We had some missing FK constraints, along with missing cleanup in code for a given table. Some customers had many millions of orphaned rows, while only thousands of live rows. Certainly didn't help index scans...
arp242 3y ago
Here' the common scenario: obj = new_object() obj.col1 = get_value_from_somewhere() obj.user_id = get_logged_in_user_id() obj.insert() Or: # Find correct ID. obj.some_id = run_query("select some_id from tbl where x=?", param) Lots of variation on that, but the user_id and some_id here are pretty much guaranteed to be accurate when implemented correctly. The biggest potential issue might be race conditions with deletes on the parent ID, but just having soft deletes sufficiently alleviates that (potential) small issue.
- ww520 3y ago
  What you picked are the happy paths. Here's the common scenario: deals = sql("select * from current_deals ..."); // Show the user the deals. // User takes his sweet time to ponder over the deals. // Meanwhile, the deals are gone. order = new_order() order.deal_id = deals[5].id order.insert() // User calls in to complain never receiving the deal. // Ok, need to verify the deal still exists. sql("select deal from current_deals where id = ? for update", deals[5].id) ... insert ... sql("commit") That's second select..for update is referential checking in code.

by ww520 3y ago

The performance gain by dropping foreign keys doesn’t hold water. You still have to do the referential checks in application or in the ORM code. Unless you meant dropping referential checks.

arrowleaf 3y ago

The only time I could see it really impacting performance is during bulk operations. But that's where making FK constraints deferred and wrapping the bulk actions in a transaction makes more sense.

crazygringo 3y ago

Not really -- in practice, it generally means all inserts are done as a transaction, and deletes simply aren't done. Then there isn't really any opportunity for inconsistent data.

magicalhippo 3y ago

Might be a performance penalty as well. We had some missing FK constraints, along with missing cleanup in code for a given table. Some customers had many millions of orphaned rows, while only thousands of live rows. Certainly didn't help index scans...

arp242 3y ago

Here' the common scenario: obj = new_object() obj.col1 = get_value_from_somewhere() obj.user_id = get_logged_in_user_id() obj.insert() Or: # Find correct ID. obj.some_id = run_query("select some_id from tbl where x=?", param) Lots of variation on that, but the user_id and some_id here are pretty much guaranteed to be accurate when implemented correctly. The biggest potential issue might be race conditions with deletes on the parent ID, but just having soft deletes sufficiently alleviates that (potential) small issue.

ww520 3y ago
What you picked are the happy paths. Here's the common scenario: deals = sql("select * from current_deals ..."); // Show the user the deals. // User takes his sweet time to ponder over the deals. // Meanwhile, the deals are gone. order = new_order() order.deal_id = deals[5].id order.insert() // User calls in to complain never receiving the deal. // Ok, need to verify the deal still exists. sql("select deal from current_deals where id = ? for update", deals[5].id) ... insert ... sql("commit") That's second select..for update is referential checking in code.