4 ms·

The boulder-sized caveat for all this admittedly really neat stuff being: > PlanetScale's Non-Blocking Schema Changes' workflow doesn't support FOREIGN KEYs in

The boulder-sized caveat for all this admittedly really neat stuff being: > PlanetScale's Non-Blocking Schema Changes' workflow doesn't support FOREIGN KEYs in users' databases. > PlanetScale determined that the production safety that Non-Blocking Schema Changes provide are worth this technical tradeoff. Learn more. https://docs.planetscale.com/concepts/nonblocking-schema-changes https://docs.planetscale.com/concepts/nonblocking-schema-cha...

lizztheblizz 5y ago
To be clear, this is not a Vitess/PlanetScale-specific opinion or choice. Foreign key constraints are a bit of a controversial topic in large-scale MySQL environments in general, which is the greater context in which this design decision was made by the Vitess team. PlanetScale's (and Vitess') non-blocking schema changes rely on open source tools for MySQL like pt-online-schema-change and gh-ost, which are widely used in production environments everywhere, and neither of them are too comfortable supporting FK's, though pt-osc does accommodate them to some extent (https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc...). gh-ost's lack of support was discussed on HN previously here: https://news.ycombinator.com/item?id=16983620 https://news.ycombinator.com/item?id=16983620 A good collection of resources on why they're considered problematic and many companies designing large-scale MySQL schemas tend to drop them can also be found here: https://federico-razzoli.com/foreign-key-bugs-in-mysql-and-mariadb https://federico-razzoli.com/foreign-key-bugs-in-mysql-and-m...
- Jarwain 5y ago
  Do you know if foreign keys tend to be a problem on postgresql as well?
  - lizztheblizz 5y ago
    I don't have nearly the experience in Postgres environments to have seen the same level of real-world impact there, but a quick search presents me with the following documentation, which seems to indicate mostly similar performance challenges related to the use of Foreign Key Constraints: https://www.postgresql.org/docs/13/populate.html#POPULATE-RM-FKEYS https://www.postgresql.org/docs/13/populate.html#POPULATE-RM...

lizztheblizz 5y ago

To be clear, this is not a Vitess/PlanetScale-specific opinion or choice. Foreign key constraints are a bit of a controversial topic in large-scale MySQL environments in general, which is the greater context in which this design decision was made by the Vitess team. PlanetScale's (and Vitess') non-blocking schema changes rely on open source tools for MySQL like pt-online-schema-change and gh-ost, which are widely used in production environments everywhere, and neither of them are too comfortable supporting FK's, though pt-osc does accommodate them to some extent (https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc...). gh-ost's lack of support was discussed on HN previously here: https://news.ycombinator.com/item?id=16983620 https://news.ycombinator.com/item?id=16983620 A good collection of resources on why they're considered problematic and many companies designing large-scale MySQL schemas tend to drop them can also be found here: https://federico-razzoli.com/foreign-key-bugs-in-mysql-and-mariadb https://federico-razzoli.com/foreign-key-bugs-in-mysql-and-m...

Jarwain 5y ago
Do you know if foreign keys tend to be a problem on postgresql as well?
- lizztheblizz 5y ago
  I don't have nearly the experience in Postgres environments to have seen the same level of real-world impact there, but a quick search presents me with the following documentation, which seems to indicate mostly similar performance challenges related to the use of Foreign Key Constraints: https://www.postgresql.org/docs/13/populate.html#POPULATE-RM-FKEYS https://www.postgresql.org/docs/13/populate.html#POPULATE-RM...