5 ms·

> No foreign key constraints Wat?

sgarland 2y ago
You say this as though the kind of people interested in a serverless distributed SQL product know what FK Constraints are, let alone have interest in using them.
- dalyons 2y ago
  This but unironically - if I’m in the market for a geo distributed hyper scale DB, I genuinely and sincerely have no interest in foreign key constraints. They are pretty much useless, if not actively harmful, at scale.
  - sgarland 2y ago
    Disagree heavily that they’re useless at scale. Both PlanetScale and Citus support them (though the former doesn’t support it on sharded DBs yet), as well as TiDB (alpha), and Yugabyte. As far as performance hits go, I guarantee I could find a half dozen other, larger problems that would have a bigger impact than disabling FK constraints. It is of course possible to maintain referential integrity without them, but it requires much more diligence from devs, and I have yet to see it implemented well anywhere. Until a single DB instance is serving well in excess of 100K QPS, I wouldn’t be concerned with the impact of FK constraints. Let RDBMS do what it’s good at – keeping your data safe.
    - dalyons 2y ago
      I didnt necessarily mean scale as req/sec, although that is a factor, more system complexity & num of engineers. In a large enough companies tech ecosystem you tend to evolve a few characteristics: - many data stores. even if you're not doing microservices persay, you're almost certainly going to end up with different domains/buis units/systems having their own data stores. FK constraints obviously dont work across system boundaries. They could be vendor systems too, not even just your own services. - soft deletes. you rarely hard delete root objects, so you dont need the FK constraint to protect those dangling refs - eventual consistentency/idempotency - you end up creating data in different systems as part of a workflow (eg signup), and you have to tolerate partial creation for resiliency. - some sort of ORM or sql framework that makes it very difficult to create a leaf row with an invalid FK parent pointer. So in a fundamentally distributed large scale tech ecosystem, FK constraints end up protecting against.... not much. Ive worked at three large scale (10m+ users) companies which all ended up deleting all their FK constraints. And... nothing happened, nothing ever went wrong due to their absence. Its theoretical protection, not practically needed IMHO.
      - sgarland 2y ago
        > FK constraints obviously dont work across system boundaries. Not to their full extent, but they can still be used. At the simplest level, it is of course entirely possible to give different services their own schema in a given database, and FK constraints are supported across schemata in both MySQL and Postgres. Vertical scaling can take you enormously far with properly architected schemata and queries. A more flexible, but still easy to reason about way to accomplish this is to have local versions of certain tables in each DB. This can be manually implemented (though this is not an easy problem to solve, for a variety of reasons), or by using something like Citus [0], which accomplishes this using 2PC. This is of course slower, but if your data model is carefully designed, it can be managed. > soft deletes. Sure, but now you have a new problem - needing to add a `is_deleted` or `deleted_at` column to a bunch of tables, and indexing that column on every table. In Postgres you might get away with this by using `DEFAULT [FALSE, NULL]` (respectively), and then creating a partial index with `...WHERE <column> IS NOT [FALSE, NULL]`; that way the index size stays reasonable, and the cardinality isn't as horrible (well, it is for bools, but since you're only using it as a filter, it can work OK). Also, of course, you have to include this predicate in most queries. > Its theoretical protection, not practically needed IMHO. Different subjective experiences, of course, but IME it very much saves you time and headaches. [0]: https://www.citusdata.com https://www.citusdata.com
deleted 2y ago
[deleted]

You say this as though the kind of people interested in a serverless distributed SQL product know what FK Constraints are, let alone have interest in using them.

dalyons 2y ago
This but unironically - if I’m in the market for a geo distributed hyper scale DB, I genuinely and sincerely have no interest in foreign key constraints. They are pretty much useless, if not actively harmful, at scale.
- sgarland 2y ago
  Disagree heavily that they’re useless at scale. Both PlanetScale and Citus support them (though the former doesn’t support it on sharded DBs yet), as well as TiDB (alpha), and Yugabyte. As far as performance hits go, I guarantee I could find a half dozen other, larger problems that would have a bigger impact than disabling FK constraints. It is of course possible to maintain referential integrity without them, but it requires much more diligence from devs, and I have yet to see it implemented well anywhere. Until a single DB instance is serving well in excess of 100K QPS, I wouldn’t be concerned with the impact of FK constraints. Let RDBMS do what it’s good at – keeping your data safe.
  - dalyons 2y ago
    I didnt necessarily mean scale as req/sec, although that is a factor, more system complexity & num of engineers. In a large enough companies tech ecosystem you tend to evolve a few characteristics: - many data stores. even if you're not doing microservices persay, you're almost certainly going to end up with different domains/buis units/systems having their own data stores. FK constraints obviously dont work across system boundaries. They could be vendor systems too, not even just your own services. - soft deletes. you rarely hard delete root objects, so you dont need the FK constraint to protect those dangling refs - eventual consistentency/idempotency - you end up creating data in different systems as part of a workflow (eg signup), and you have to tolerate partial creation for resiliency. - some sort of ORM or sql framework that makes it very difficult to create a leaf row with an invalid FK parent pointer. So in a fundamentally distributed large scale tech ecosystem, FK constraints end up protecting against.... not much. Ive worked at three large scale (10m+ users) companies which all ended up deleting all their FK constraints. And... nothing happened, nothing ever went wrong due to their absence. Its theoretical protection, not practically needed IMHO.
    - sgarland 2y ago
      > FK constraints obviously dont work across system boundaries. Not to their full extent, but they can still be used. At the simplest level, it is of course entirely possible to give different services their own schema in a given database, and FK constraints are supported across schemata in both MySQL and Postgres. Vertical scaling can take you enormously far with properly architected schemata and queries. A more flexible, but still easy to reason about way to accomplish this is to have local versions of certain tables in each DB. This can be manually implemented (though this is not an easy problem to solve, for a variety of reasons), or by using something like Citus [0], which accomplishes this using 2PC. This is of course slower, but if your data model is carefully designed, it can be managed. > soft deletes. Sure, but now you have a new problem - needing to add a `is_deleted` or `deleted_at` column to a bunch of tables, and indexing that column on every table. In Postgres you might get away with this by using `DEFAULT [FALSE, NULL]` (respectively), and then creating a partial index with `...WHERE <column> IS NOT [FALSE, NULL]`; that way the index size stays reasonable, and the cardinality isn't as horrible (well, it is for bools, but since you're only using it as a filter, it can work OK). Also, of course, you have to include this predicate in most queries. > Its theoretical protection, not practically needed IMHO. Different subjective experiences, of course, but IME it very much saves you time and headaches. [0]: https://www.citusdata.com https://www.citusdata.com

deleted 2y ago

[deleted]