7 ms·

We evaluated gh-ost, but the killer for us is that it doesn't support any kind of foreign keys. I understand that at GitHub's scale, foreign keys might be more

We evaluated gh-ost, but the killer for us is that it doesn't support any kind of foreign keys. I understand that at GitHub's scale, foreign keys might be more of a hassle than what they are worth, but for a smallish company that values data integrity over scale and uptime, this is not an acceptable choice.

pwnna 9y ago
This is an unfortunate by product due to the way that gh-ost is implemented. It is simply not possible to run it with a FK constraint. The reason is that since it replays the binlogs on the ghost table while the ghost table is not fully populated, the FK constraint will cause some of statements to fail. The data move from the original to the ghost table cannot be completed.
- codedokode 9y ago
  Cannot it add FK constraints after the ghost table is fully populated?
  - perlgeek 9y ago
    The problem is that adding FK constraints is another schema change, which causes MySQL to copy the whole table, and lock the table during this time -- which is precisely what gh-ost tries to avoid. Worse, foreign keys from other tables to the one that is being changed would need to be updated as well, blocking those tables in turn.
    - zmoazeni 9y ago
      > which causes MySQL to copy the whole table This is wrong on a couple levels. First it doesn't copy the whole table: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-... However, it can take a while if MySQL is evaluating the consistency. But you can disable that with `SET FOREIGN_KEY_CHECKS = 0` which turns it into a metadata change (nearly instantaneous). You still will need to check for violations, but you can do that in a more friendly-to-load manner, and of course will need to deal with any violations manually. But that strategy is a good middle ground to all-or-nothing FKs. Edit: Whoops, looks like I was wrong on the table-copy part. Per "Otherwise, only the COPY algorithm is supported." So it does copy the data when `FOREIGN_KEY_CHECKS=1` (the default)
  - zmoazeni 9y ago
    Yeah. See https://github.com/github/gh-ost/issues/507#issuecomment-338725563 https://github.com/github/gh-ost/issues/507#issuecomment-338... on how to do it in a performant way.
shlomi-noach 9y ago
Author of gh-ost here. Actually, it should be possible to support child-side foreign keys. They would have to be named differently (the foreign key constraint has a unique name in a schema) -- but it should work. See discussion in https://github.com/github/gh-ost/issues/507 https://github.com/github/gh-ost/issues/507 It is true that it is not on our roadmap to implement FK support for gh-ost (see https://github.com/github/gh-ost/issues/331 https://github.com/github/gh-ost/issues/331), but if anyone wishes to contribute support for FK we're grateful. We've had more complex contributions coming from the community and we're grateful for those.
- killbrad 9y ago
  Child-side foreign keys? Does that mean constraints in app logic instead of the database?
  - shlomi-noach 9y ago
    No, on the database itself. I used "child side" for lack of better terminology. Every foreign key has two edges: the "parent" table and the "child" table. Rows in the "child" table depend on existence of matching rows in the "parent" table. It should be feasible to run `gh-ost` to ALTER a table that only has "child"-side constraints. It will be impossible to run `gh-ost` to ALTER a table that has "parent" side constraints. Hope this clarifies.
cup-of-tea 9y ago
I'm interested to know what companies are doing "at scale" to not need to use foreign keys. Do they just write user ids or whatnot into other tables?
- Chilinot 9y ago
  I'm guessing they are validating the constraints in their applications instead of their databases. While this puts more requirements on your setup and developers, it offloads a lot of stress from your database.
  - gaius 9y ago
    How does the app know what is valid in a foreign key column without repeatedly retrieving the primary key column of the other table?
    - tomnipotent 9y ago
      It repeatedly retrieves the parent row. Look at the URL when replying on HN and change the id by adding a few 0's - the app prevents a comment from being added if the ID isn't found. Of course, this doesn't prevent a developer from circumventing the apps constraint logic.
      - gaius 9y ago
        This would place a far higher load on the DB (which would validate the constraint via the index if allowed to) and create more network roundtrips. It’s 10x as expensive as just using a FK, and far less reliable!
        tomnipotent 9y ago
        >> It’s 10x as expensive as just using a FK It's not about load, it's about locks and contention in the database caused by FK constraint enforcement. Extra read queries will barely be noticeable compared to that.
anton_gogolev 9y ago
How could foreign keys ever be a "hassle"?
- shlomi-noach 9y ago
  They can be a hassle when you want to shard your data, having outgrown your single-instance capacity. You will either shard functionally (extracting complete tables to other database servers), in which case FKs will completely break, or horizontally (split rows across database servers), in which case you may or may not be able to still use FKs. They're also a performance impact on large tables since inserts/deletes must make multiple trips to the tables/indexes. That's a growing operational hassle as tables grow larger.
  - asaph 9y ago
    > You will either shard functionally (extracting complete tables to other database servers) This isn't sharding. This is vertical partitioning. Sharding is a type of horizontal partitioning. Reference: https://en.m.wikipedia.org/wiki/Shard_(database_architecture) https://en.m.wikipedia.org/wiki/Shard_(database_architecture...
yxhuvud 9y ago
What? Why would enforced consistency be less worth at bigger scale? My guess would have been the complete opposite.
- sjansen 9y ago
  It's not a question of worth but feasibility. Just like an ideal schema is fully normalized, but performance concerns sometimes drive denormalization. When foreign keys can't be used to enforce data integrity, the application has to be built to compensate in other ways. Sometimes that means simply accepting dirty data, and designing the application to stay robust when encountering unexpected data. Other times it means building alternate solutions to discover and repair data issues.
- sciurus 9y ago
  shlomi-noach linked to https://github.com/github/gh-ost/issues/331 https://github.com/github/gh-ost/issues/331 in another comment. That goes into some of the reasons to avoid foreign keys. At a past job where we had a complex MySQL setup, I set up a slack autoresponse to post "Just say no!" anytime someone mentioned foreign keys. :-)

pwnna 9y ago

This is an unfortunate by product due to the way that gh-ost is implemented. It is simply not possible to run it with a FK constraint. The reason is that since it replays the binlogs on the ghost table while the ghost table is not fully populated, the FK constraint will cause some of statements to fail. The data move from the original to the ghost table cannot be completed.

codedokode 9y ago
Cannot it add FK constraints after the ghost table is fully populated?
- perlgeek 9y ago
  The problem is that adding FK constraints is another schema change, which causes MySQL to copy the whole table, and lock the table during this time -- which is precisely what gh-ost tries to avoid. Worse, foreign keys from other tables to the one that is being changed would need to be updated as well, blocking those tables in turn.
  - zmoazeni 9y ago
    > which causes MySQL to copy the whole table This is wrong on a couple levels. First it doesn't copy the whole table: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-... However, it can take a while if MySQL is evaluating the consistency. But you can disable that with `SET FOREIGN_KEY_CHECKS = 0` which turns it into a metadata change (nearly instantaneous). You still will need to check for violations, but you can do that in a more friendly-to-load manner, and of course will need to deal with any violations manually. But that strategy is a good middle ground to all-or-nothing FKs. Edit: Whoops, looks like I was wrong on the table-copy part. Per "Otherwise, only the COPY algorithm is supported." So it does copy the data when `FOREIGN_KEY_CHECKS=1` (the default)
- zmoazeni 9y ago
  Yeah. See https://github.com/github/gh-ost/issues/507#issuecomment-338725563 https://github.com/github/gh-ost/issues/507#issuecomment-338... on how to do it in a performant way.

shlomi-noach 9y ago

Author of gh-ost here. Actually, it should be possible to support child-side foreign keys. They would have to be named differently (the foreign key constraint has a unique name in a schema) -- but it should work. See discussion in https://github.com/github/gh-ost/issues/507 https://github.com/github/gh-ost/issues/507 It is true that it is not on our roadmap to implement FK support for gh-ost (see https://github.com/github/gh-ost/issues/331 https://github.com/github/gh-ost/issues/331), but if anyone wishes to contribute support for FK we're grateful. We've had more complex contributions coming from the community and we're grateful for those.

killbrad 9y ago
Child-side foreign keys? Does that mean constraints in app logic instead of the database?
- shlomi-noach 9y ago
  No, on the database itself. I used "child side" for lack of better terminology. Every foreign key has two edges: the "parent" table and the "child" table. Rows in the "child" table depend on existence of matching rows in the "parent" table. It should be feasible to run `gh-ost` to ALTER a table that only has "child"-side constraints. It will be impossible to run `gh-ost` to ALTER a table that has "parent" side constraints. Hope this clarifies.

cup-of-tea 9y ago

I'm interested to know what companies are doing "at scale" to not need to use foreign keys. Do they just write user ids or whatnot into other tables?

Chilinot 9y ago
I'm guessing they are validating the constraints in their applications instead of their databases. While this puts more requirements on your setup and developers, it offloads a lot of stress from your database.
- gaius 9y ago
  How does the app know what is valid in a foreign key column without repeatedly retrieving the primary key column of the other table?
  - tomnipotent 9y ago
    It repeatedly retrieves the parent row. Look at the URL when replying on HN and change the id by adding a few 0's - the app prevents a comment from being added if the ID isn't found. Of course, this doesn't prevent a developer from circumventing the apps constraint logic.
    - gaius 9y ago
      This would place a far higher load on the DB (which would validate the constraint via the index if allowed to) and create more network roundtrips. It’s 10x as expensive as just using a FK, and far less reliable!
      - tomnipotent 9y ago
        >> It’s 10x as expensive as just using a FK It's not about load, it's about locks and contention in the database caused by FK constraint enforcement. Extra read queries will barely be noticeable compared to that.

anton_gogolev 9y ago

How could foreign keys ever be a "hassle"?

shlomi-noach 9y ago
They can be a hassle when you want to shard your data, having outgrown your single-instance capacity. You will either shard functionally (extracting complete tables to other database servers), in which case FKs will completely break, or horizontally (split rows across database servers), in which case you may or may not be able to still use FKs. They're also a performance impact on large tables since inserts/deletes must make multiple trips to the tables/indexes. That's a growing operational hassle as tables grow larger.
- asaph 9y ago
  > You will either shard functionally (extracting complete tables to other database servers) This isn't sharding. This is vertical partitioning. Sharding is a type of horizontal partitioning. Reference: https://en.m.wikipedia.org/wiki/Shard_(database_architecture) https://en.m.wikipedia.org/wiki/Shard_(database_architecture...

yxhuvud 9y ago

What? Why would enforced consistency be less worth at bigger scale? My guess would have been the complete opposite.

sjansen 9y ago
It's not a question of worth but feasibility. Just like an ideal schema is fully normalized, but performance concerns sometimes drive denormalization. When foreign keys can't be used to enforce data integrity, the application has to be built to compensate in other ways. Sometimes that means simply accepting dirty data, and designing the application to stay robust when encountering unexpected data. Other times it means building alternate solutions to discover and repair data issues.
sciurus 9y ago
shlomi-noach linked to https://github.com/github/gh-ost/issues/331 https://github.com/github/gh-ost/issues/331 in another comment. That goes into some of the reasons to avoid foreign keys. At a past job where we had a complex MySQL setup, I set up a slack autoresponse to post "Just say no!" anytime someone mentioned foreign keys. :-)