News for hackers. Stuff that matters.
Re:GitHub's online schema migration for MySQL by cup-of-tea on Thursday May 03, 2018 @02:48AM (#16983847)
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?
Re:GitHub's online schema migration for MySQL by barrkel on Thursday May 03, 2018 @03:09AM (#16983935)
Foreign keys add locks to referenced rows during insert and update transactions. Bad news if you're locking those rows for other reasons, like a kind of distributed lock. So the problem with FKs isn't that you don't need them; it's that the extra locking limits concurrency.

FK checks also affect performance, of course. Where I work, we disable FKs on our bulk inserts but keep them enabled otherwise and also in tests; but our workload is different from the usual consumer web app, we have multi-million row inserts per user, and no more than 100 users or so per customer, who each get their own tenant DB.

Re:GitHub's online schema migration for MySQL by Chilinot on Thursday May 03, 2018 @03:07AM (#16983922)
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.
Re:GitHub's online schema migration for MySQL by cup-of-tea on Thursday May 03, 2018 @07:54AM (#16985097)
Yeah, but from a data integrity point of view that is horrifying. I guess they must have some very comprehensive testing framework to validate the code.

Also, pardon my further ignorance, but if you're not going to use foreign key constraints, what is the point of using a relational db? Why not just a fast key-value store for each index?

Re:GitHub's online schema migration for MySQL by fipar on Thursday May 03, 2018 @09:44AM (#16985935)
You may me able to architect your application to do well-enough without Foreign Keys yet still require features that key-value stores do not provide, like SQL, triggers, etc.

In the specfic case of MySQL, while still horrifying (I agree with you! but it is one of the things you some times have to do at scale), you can create the Foreign Key constraints but then disable their verification and periodically look for violations, as described here: https://www.percona.com/blog/2011/11/18/eventual-consistency...

Re:GitHub's online schema migration for MySQL by zzzeek on Thursday May 03, 2018 @11:52AM (#16987056)
The application still has foreign key references and JOIN, which you don't get in a k/v store, you just don't have the actual CONSTRAINT. MySQL worked this way for literally decades with the MyISAM storage engine that does not have referential integrity constraints, it just allows the FOREIGN KEY syntax to pass silently.
Re:GitHub's online schema migration for MySQL by gaius on Thursday May 03, 2018 @06:19AM (#16984655)
How does the app know what is valid in a foreign key column without repeatedly retrieving the primary key column of the other table?
Re:GitHub's online schema migration for MySQL by tomnipotent on Thursday May 03, 2018 @12:25PM (#16987363)
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.
Re:GitHub's online schema migration for MySQL by gaius on Thursday May 03, 2018 @12:50PM (#16987583)
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!
Re:GitHub's online schema migration for MySQL by tomnipotent on Thursday May 03, 2018 @08:26PM (#16991211)
>> 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.

Re:GitHub's online schema migration for MySQL by gaius on Friday May 04, 2018 @01:11AM (#16992327)
it's about locks and contention in the database caused by FK constraint enforcement

What locks do you mean?

Re:GitHub's online schema migration for MySQL by sa46 on Friday May 04, 2018 @03:39AM (#16992856)
Google's Spanner doesn't support foreign keys but does support interleaved tables to cover some use cases of foreign keys. Spanner sees wide use inside Google.

The usual routes for data consistency between tables are batch clean up or in-app validation.

Re:GitHub's online schema migration for MySQL by zzzeek on Thursday May 03, 2018 @11:49AM (#16987037)
Are you referring to ON UPDATE cascades? Mutable/natural primary key values are not very common these days especially in a DB like MySQL. Incrementing integers are most common.