3 ms·
That's true. Most large-scale MySQL shops, including Facebook, discourage or outright forbid foreign key constraints. This is sacrilege to many relational db pu
by evanelias 7y ago
That's true. Most large-scale MySQL shops, including Facebook, discourage or outright forbid foreign key constraints. This is sacrilege to many relational db purists, but there are a number of solid reasons:
Foreign keys aren't shard-aware, greatly reducing their utility.
They introduce performance bottlenecks due to extra locking. In an insanely-high-write-volume OLTP environment, such as a social network, this really matters.
They don't play nice with online schema change tools in general -- not just fb-osc. These tools all involve creating shadow tables and propagating changes to them, which is problematic with foreign keys.
- sokoloff 7y agoWe were a large MS-SQL shop and we had the same. No FKeys in test or prod and we were "only" a billion and change e-commerce, nowhere near a social media site level of traffic. To the original question: hand-written ALTER scripts, each taggable as pre, during, or post release actions. We had standard patterns for adding non-null columns (pre to add a nullable column and a cursor-based/batched update, then another ALTER to make the nullable column (now populated) non-nullable). Also had a set of rules to ensure version N of the code (web and DB) could run on the DB at version N or N+1.)