Live data from Hacker News

We do not use foreign keys (2016)

github.com

301–310 of 337 posts

Re: We do not use foreign keys (2016)

#301
post #299

Earlier quoted context omitted.

FK consistency can't be guaranteed without ensuring the referenced rows don't disappear before the transaction has been committed. Think about it.

Yes, but it can be done without locking out reads. The only thing you need to lock against is someone changing the primary key of the referenced row or deleting the row. PostgreSQL has implemented this minimum level of necessary locking for quite many years now.

I never said anything about locking out reads. Did I?

FWIW, I just tested in Postgres. Locks like I said it does:

    A: create table parent(id int, value int, unique(id));
    A: create table child(id int, parent_id int references parent(id));
    A: insert into parent values (1, 10);
    A: begin;
    A: insert into child values (1, 1);
    B: begin;
    B: select 1 from parent where id = 1 for update;
    B: (blocks)
The situation in MySQL is worse because it'll block updates on any field, not just row locks.

I have specific experience of this due to use of database locks at the application level to avoid deadlocks (different lock orders) and inconsistent updates (updates based on reads across multiple tables that may have separate racing updates) by locking rows up front. For understandable schema reasons, what is logically a parent entity is the natural thing to lock, but for understandable performance reasons, FKs to the parent entity are distributed through some fairly large tables.

Re: We do not use foreign keys (2016)

#302
post #298

Earlier quoted context omitted.

FKs add read locks to referenced rows. It limits concurrency and it is observable. FKs constrain your ability to incrementally widen 32-bit FKs once you go over 2 billion rows, if you start out with 32-bit PKs. Two concrete reasons to avoid FKs in production, or at least disable them for longer running transactions. I think this perspective is something you only get once you've run bigger databases in production.

These sound like implementation details. In PostgreSQL FKs just add a shared write lock to the foreign key columns of the referenced table (i.e. the locks prevent anyone from updating the primary key of the referenced row). Also what you said about 32-bits is not true in PostgreSQL either as far as I can remember. The costs of having FKs in PostreSQL are: 1) If you update the primary key of the referenced table you m…

And that updates on tables with FKs block 'for update' locks on the referenced tables.

(Let's not forget, amid all this Postgres-specific chatter, that the article is about GitHub, who use MySQL.)

Re: We do not use foreign keys (2016)

#303

Earlier quoted context omitted.

But, if you want to ensure the data is never visible in an inconsistent state, you either need to use db-level concurrency-related features like transactions or locks; or some kind of lock or other concurrency-control features at the app level while guaranteeing the db has no clients other than your app. It seems difficult to wind up with better performance characteristics by doing this than using the higher-level ab…

Transactions are a pretty widely used RDBMS feature AFAIK.

Right, so are foreign key constraints.

Ensuring foreign key consistency without foreign key constraints is not just "use transactions", you have to be careful and intentional about how you are using them (and ensure all clients do). Why would you choose this over just using the foreign key constraint which takes care of it for you, using the same underlying technology?

Perhaps that's a clearer way to say what I was trying to say originally.

Re: We do not use foreign keys (2016)

#304
post #298

Earlier quoted context omitted.

These sound like implementation details. In PostgreSQL FKs just add a shared write lock to the foreign key columns of the referenced table (i.e. the locks prevent anyone from updating the primary key of the referenced row). Also what you said about 32-bits is not true in PostgreSQL either as far as I can remember. The costs of having FKs in PostreSQL are: 1) If you update the primary key of the referenced table you m…

And that updates on tables with FKs block 'for update' locks on the referenced tables. (Let's not forget, amid all this Postgres-specific chatter, that the article is about GitHub, who use MySQL.)

not sure about mysql, but in postgresql an update on a table with a foreign key will take 'FOR KEY SHARE' locks on the referenced table, which is a weaker type of lock. updates on the referenced table that do not update (primary) key columns (changing a pk is very uncommon anyway) will suffice with a 'FOR NO KEY UPDATE' lock, which does not get blocked by 'FOR KEY SHARE' locks. in fact, the main reason postgresql has these weaker 'FOR KEY SHARE' and 'FOR NO KEY UPDATE' lock types is for handing of foreign keys.

Re: We do not use foreign keys (2016)

#305

One great thing about not enforcing FKs is that it makes integration testing a lot easier. You can load just the data you need to test with, and none of the FKs need to point to rows that exist that aren't within the testing scope. This approach isn't for everyone. It works well with DDD where aggregates form contextual table boundaries and is eventually consistent by default.

If you ain't testing against the full system state, then I'd be hard-pressed to call that "integration testing". If you're generating the test data from scratch, then it shouldn't be hard to generate the dependent data while you're at it. If you're testing against (anonymized) production data, then it shouldn't be hard to pull the dependent data while you're at it. In either case, you should be validating the integri…

It isn’t hard, but often very cumbersome. Oh, so you want to test invoicing, for this Customer, which must have a Delivery Address and an Invoicing Adress which both needs valid Postal Codes. And the Customer must have a Contact person. Then we need the Product, that must consist of at least one Article, and each Article must be connected to the Company that we bought it from, with Addresses and Contact Persons and half a dozen other entities. But we have forgotten that the article also need to be in a Category so we know which Sales tax or VAT that needs to applied, for the State or Country of Sale. Then of course, which Sales Person, belonging to a Sales office, with contact person, addresses etc should get credited for the sale. And we still haven’t been able to actually create the order yet, because we haven’t created the delivery options.

I think you get my point. I’ve easily used more than a full day just to get enough data in a naked system to make just the simplest test. I’m very grateful for tools like tsqlt that make unit testing possible (by temporarily turning of FKs)

Re: We do not use foreign keys (2016)

#306

Earlier quoted context omitted.

Any database that would let you disable constraints on a session basis is a toy database. Such an operation doesn’t even make sense because at some point the relational integrity has to be enforced for the entire table. You can’t just have parts of a table be relationally correct. That is like saying 1 + 1 = 3. It is a completely illogical statement. However I would not at all be surprised to learn MySQL supports suc…

So are you asserting that the following products are all built on top of a "toy" database, and their engineers have no idea what they're doing: Facebook, YouTube, Wikipedia, Pinterest, Slack, GitHub, Etsy, Yelp, LinkedIn, Shopify, Dropbox, Wordpress, Wix, Tumblr, Square, Uber, Booking.com, Box, Venmo, SendGrid, Okta, SurveyMonkey, WePay, Alibaba, SoundCloud, among countless others... An alternative view is that your…

Qualifying MySQL as a "toy" database in 2019 is obviously wrong.

But I think most companies you mentioned don't use MySQL in the usual way, as they would use a "standard" enterprise database like Oracle, SQL Server or PostgreSQL.

These companies don't use MySQL directly. They use it indirectly as the storage component of a larger architecture. For example, YouTube uses Vitess "over" MySQL.

Companies like Instagram are known to do something similar with PostgreSQL.

My point is that maybe you and the parent comment are not thinking about the same use case.

Re: We do not use foreign keys (2016)

#307

Earlier quoted context omitted.

Cool, so I'm going to assume that means your answer to my question of "Do you have much direct experience with high-volume OLTP database workloads?" is "no". Given your "bet" as well as comments about schema change difficulty, I'm also going to assume you did not click through to my profile...

And to clarify, I'm not saying that to toot my own horn. My point was, I primarily work on open source schema management and related consulting. I talk to large companies about MySQL schema changes literally every single day. The comment about "the lengths people go to avoid schema changes is astonishing" simply does not gel with reality among large-scale MySQL users. As for the random blind accusations about bad cod…

Agreed. I think MySQL is still dragging the bad reputation it got in the early 2000s, which is unfair considering how much it improved. And I write that I someone that used to hate MySQL for all its shortcuts. I have one app in production based on MySQL. I have been thinking of switching to PostgreSQL for years. But the truth is that, the more MySQL improves, the less the switch is justified :) The engineering effort put by Google, Facebook, Oracle, etc. in MySQL and InnoDB during the last ten years is impressive.

Re: We do not use foreign keys (2016)

#308

Earlier quoted context omitted.

So are you asserting that the following products are all built on top of a "toy" database, and their engineers have no idea what they're doing: Facebook, YouTube, Wikipedia, Pinterest, Slack, GitHub, Etsy, Yelp, LinkedIn, Shopify, Dropbox, Wordpress, Wix, Tumblr, Square, Uber, Booking.com, Box, Venmo, SendGrid, Okta, SurveyMonkey, WePay, Alibaba, SoundCloud, among countless others... An alternative view is that your…

Qualifying MySQL as a "toy" database in 2019 is obviously wrong. But I think most companies you mentioned don't use MySQL in the usual way, as they would use a "standard" enterprise database like Oracle, SQL Server or PostgreSQL. These companies don't use MySQL directly. They use it indirectly as the storage component of a larger architecture. For example, YouTube uses Vitess "over" MySQL. Companies like Instagram ar…

> These companies don't use MySQL directly. They use it indirectly as the storage component of a larger architecture.

Yes and no. Often it's both. I say this first-hand, having performed significant work on the database tier for two of the companies I listed, and consulted for several others.

For example, while Facebook's largest db tier goes through a dao / writethru cache, there's plenty of other use-cases that are direct MySQL usage.

And in any case, why does it matter if there's another layer involved? It's still MySQL powering mission-critical global-scale use-cases. And for example with YouTube, literally the primary benefit of Vitess is that your application can treat it as a single normal unsharded MySQL installation, so those interactions are still very MySQLy.

Re: We do not use foreign keys (2016)

#309
post #298

Earlier quoted context omitted.

These sound like implementation details. In PostgreSQL FKs just add a shared write lock to the foreign key columns of the referenced table (i.e. the locks prevent anyone from updating the primary key of the referenced row). Also what you said about 32-bits is not true in PostgreSQL either as far as I can remember. The costs of having FKs in PostreSQL are: 1) If you update the primary key of the referenced table you m…

And that updates on tables with FKs block 'for update' locks on the referenced tables. (Let's not forget, amid all this Postgres-specific chatter, that the article is about GitHub, who use MySQL.)

In which case you can run FOR NO KEY UPDATE.

Re: We do not use foreign keys (2016)

#310
post #72
post #9

There seems to be a certain mysql tinge to this value calculation. I use foreign keys and like it (I use Postgres). I do not encounter the problems the author does. I omit them when they are a problem for some reason. This is rare in routine work.

I'm gonna wager a guess and say that most MySQL users do use foreign keys without a problem. Most MySQL users are not running Github scale.

True, but the people I know who run PostgreSQL at that scale do not complain about foreign keys. Not since PostgreSQL added separate lock levels to improve concurrency with foreign keys.
Post reply on HN