3 ms·

I remember working on a large database without foreign keys for a while. When asked why did not use foreign keys, I was told that they didn't like the opaquene

by Svip 3y ago

I remember working on a large database without foreign keys for a while. When asked why did not use foreign keys, I was told that they didn't like the opaqueness of CASCADE, which I could understand. I guess I did not give it much thought afterwards, until I later ended up in a shop, where the database did include foreign keys, but only with RESTRICT. It was eye-opening how useful foreign keys were, when they were just integrity checks.

shlomi-noach 3y ago
OP. I agree! RESTRICT is by far the best rule to use, and makes the most sense. Perhaps to balance my post a bit, and for what it's worth, I don't advocate for "don't ever use foreign keys" as a blanket statement. My experience was one where using foreign keys did not make sense. I do wish they were more operationally friendly.
- _a_a_a_ 3y ago
  what does 'more operationally friendly' mean? TIA
  - shlomi-noach 3y ago
    Like the issues I mention in my post: modifying the data type of a column that is used by a foreign key; otherwise the fact you can't run Online DDL on a table that participates in foreign key relationship ; that INSTANT does not support (yet?) adding/removing foreign key constraints ; that cascaded writes are not written the the binary logs. These are all things that the casual developer doesn't deal with when designing a schema and writes an app that INSERTs/DELETEs/UPDATEs to tables with foreign keys. But once there's a need for a change; once you wire 3rd party tools onto your database, that's where the operations hit a wall.
    - _a_a_a_ 3y ago
      I didn't realise grep_it and you (shlomi-noach) were the same, apologies
      - shlomi-noach 3y ago
        We are not the same. My mistake for writing "OP".
- shlomi-noach 3y ago
  Whoops. I wrote "OP" when I really meant "Post author". I'm a bit rusty with HN notations.
tomnipotent 3y ago
eBay released a blog/paper over two decades ago talking about how they achieved scale in part by removing FK constraints from their database. It was vogue for the next decade when SAN/DAS IOPS were ~1-3k, and FK constraints could lead to more disk writes (like Postgres multixact).

by Svip 3y ago

shlomi-noach 3y ago

OP. I agree! RESTRICT is by far the best rule to use, and makes the most sense. Perhaps to balance my post a bit, and for what it's worth, I don't advocate for "don't ever use foreign keys" as a blanket statement. My experience was one where using foreign keys did not make sense. I do wish they were more operationally friendly.

_a_a_a_ 3y ago
what does 'more operationally friendly' mean? TIA
- shlomi-noach 3y ago
  Like the issues I mention in my post: modifying the data type of a column that is used by a foreign key; otherwise the fact you can't run Online DDL on a table that participates in foreign key relationship ; that INSTANT does not support (yet?) adding/removing foreign key constraints ; that cascaded writes are not written the the binary logs. These are all things that the casual developer doesn't deal with when designing a schema and writes an app that INSERTs/DELETEs/UPDATEs to tables with foreign keys. But once there's a need for a change; once you wire 3rd party tools onto your database, that's where the operations hit a wall.
  - _a_a_a_ 3y ago
    I didn't realise grep_it and you (shlomi-noach) were the same, apologies
    - shlomi-noach 3y ago
      We are not the same. My mistake for writing "OP".
shlomi-noach 3y ago
Whoops. I wrote "OP" when I really meant "Post author". I'm a bit rusty with HN notations.

tomnipotent 3y ago

eBay released a blog/paper over two decades ago talking about how they achieved scale in part by removing FK constraints from their database. It was vogue for the next decade when SAN/DAS IOPS were ~1-3k, and FK constraints could lead to more disk writes (like Postgres multixact).