4 ms·

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

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

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