4 ms·
I guess you can use indices instead of foreign keys? And somehow implement all the `ON DELETE CASCADE` manually within any transaction that removes the original
by Yoric 4y ago
I guess you can use indices instead of foreign keys? And somehow implement all the `ON DELETE CASCADE` manually within any transaction that removes the original row? Not sure how it's "good" code but it could be faster.
- DasIch 4y agoA foreign key doesn't necessarily imply an index. If you are using postgres, you would have to add an index in addition to the foreign key, if you want one. On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster.
- sroussey 4y agoFaster in aggregate throughput can often be different from faster for a specific operation. Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure…
- Yoric 4y ago> Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure… Intriguing. Can you tell me more?
- Yoric 4y ago> A foreign key doesn't necessarily imply an index. If you are using postgres, you would have to add an index in addition to the foreign key, if you want one. Sure, I meant an index in which the key is primary. But, on second thought, that's probably me misreading the GP's message. > On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster. That can definitely be a problem (just like destructor deallocation stampedes in C++ or Rust). Still less risky than cascading manually and asynchronously, I suspect.