Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We do not use foreign keys (2016)

github.com

Post reply on HN
Print view View original

261–270 of 337 posts

Re: We do not use foreign keys (2016)

#261

Post by sturgill »

hrktb wrote: post #107
sturgill wrote: post #78

Earlier quoted context omitted.

I’m pretty confident that GitHub doesn’t use foreign keys because it was built as a Rails app. And the “Rails Way” is to create these constraints in the model. Foreign key constraints weren’t a first-class member in Rails until v4 (if memory serves correctly). I was once a full-time Rails dev and really loved the framework (I don’t write as many user facing applications these days). Most of the Omakase trade offs did…

It’s at its core a case by case decision, I think FK are also a net negative in data ingestion scenari where the data set is big enough. Trying to make sure everything is where it needs to be at any given time, everything is inserted in the right order and the data is always consistent brings exponential amount of conplexity when it could all be checked at the end and pruned for invalid data. And usually DB integrity…

App level data integrity is usually critical. Ingestion into a reporting database is an entirely different construct (where I agree FK constraints are burdensome).

Re: We do not use foreign keys (2016)

#262

Post by nwallin »

Hamuko wrote: post #61
brlewis wrote: post #31

> It may even rely on FK to cascade deletes (shudder) Is this just the author's personal taste, or is there something about mysql that makes ON DELETE CASCADE a bad idea? In postgresql it's a useful tool for maintaining database consistency.

I'm guessing it's the possibility for data loss. So someone accidentally deletes a user and it cascade deletes all invoices referenced to that user (deleting invoices is a big no-no in accounting).

I mean, sure, don't use on delete cascade in that case. But there's a big difference between "sometimes bad" and "always bad". The link seems to argue "always bad".

If I have data which much always be deleted if its owner is deleted, what's the process? Manually issue exhaustive delete statements in a transaction?

Is on delete cascade always bad or just a bad default? (It's obviously a bad default, I'm not arguing that.)

Re: We do not use foreign keys (2016)

#263

Post by sturgill »

evanelias wrote: post #185
sturgill wrote: post #78

Earlier quoted context omitted.

I’m pretty confident that GitHub doesn’t use foreign keys because it was built as a Rails app. And the “Rails Way” is to create these constraints in the model. Foreign key constraints weren’t a first-class member in Rails until v4 (if memory serves correctly). I was once a full-time Rails dev and really loved the framework (I don’t write as many user facing applications these days). Most of the Omakase trade offs did…

> I’m pretty confident that GitHub doesn’t use foreign keys because it was built as a Rails app Maybe originally, but lack of foreign key usage is certainly not Rails specific today. Large MySQL shops generally don't use foreign keys, full stop, for the exact reasons Shlomi described in the original comment. Facebook does not use foreign keys either. In my experience, same thing is true at all the other large MySQL-b…

“[S]harding alone is very painful even without introducing a ton of new application-level constraint logic at the same time.”

I typically espouse app logic to check state and use foreign keys to ensure enforcement (eg, race conditions that are very hard to ensure at the app level but are built into many RDBMS). Foreign key failures are just treated like any other failure mode.

But honestly, I haven’t been part of a company that have really hit those upper limits that require sharding. They exist, yes. But most companies will never need to worry about it. Which is my point.

Re: We do not use foreign keys (2016)

#264

Post by nwallin »

jackcviers3 wrote: post #43

I thought foreign keys were necessary for the consistency part of ACID compliance. Once you remove the integrity constraint, the database cannot guarantee consistency, even with isolation and atomicity. Sure, removing the constraint will get you a huge burst in performance, but now you are in charge of guaranteeing consistency outside of transactions in a distributed system involving at least one client and one serve…

My understanding is that it's the other way around. Without ACID you can't guarantee FK correctness. You can't be sure another transaction might add a key to a row you're about to delete.

It's an easy mistake to make though.

Re: We do not use foreign keys (2016)

#265

Post by couchand »

spookthesunset wrote: post #112
munk-a wrote: post #99

Earlier quoted context omitted.

Oh I absolutely agree and even when a DB is properly configured with references all cleared defined and constrained it's absolutely a good UX thing to pre-check as much as possible. But, beyond that, it is quite possible to remove FK checks and still have strong guarantees about data integrity. It is stupidly expensive and unless you have a few billion in the bank there is absolutely no reason to even consider it, bu…

Being pedantic in this case doesn’t help the cause. Too many developers don’t understand database theory at all and will read this > But, beyond that, it is quite possible to remove FK checks and still have strong guarantees about data integrity And not the rest of your post. Yes technically you are right but it is stupidly expensive and nobody should do it. The problem with being technically correct is, again, peopl…

Hey there, you and I still need work. Let them speak!

Re: We do not use foreign keys (2016)

#266

Post by devy »

conroy wrote: post #5

When posts like these come up, I'd like to remind people that context matters when making technical decisions. What works for large companies with huge scale (GitHub, Google, Facebook) may not work for you. As a counter point to the linked issue, I operate a few small applications. Foreign-keys (and constraints in general) are great at ensuring that invalid data doesn't find its way into your database. Yes, they have…

In addition to context + YMMV, I want to mention, sharding is NOT THE ONLY solution for scaling[1], albeit it's a popular technique. So that makes Shlomi Noach's first point weaker.

[1]: https://www.quora.com/What-are-alternatives-to-sharding-data...

Re: We do not use foreign keys (2016)

#268

Post by zenhack »

cryptonector wrote: post #171
benburleson wrote: post #105

Earlier quoted context omitted.

`malloc` won't fail, right?

It won't on Linux! But hey, random processes will get OOM-killed.

You can turn off overcommit, in which case you'll get a null return instead of OOM killing.

But also, you can still get a malloc failure without actually be running out of memory if the allocator can't find a big enough contiguous chunk of address space.

This is highly unlikely on a 64 bit system, but if you try to malloc gigabytes on a 32 bit machine you might see it.

Re: We do not use foreign keys (2016)

#269

Post by projektfu »

perlgeek wrote: post #137
PopeDotNinja wrote: post #22

Because I've never thought about this, I'll ask the dumb question... A shopping cart has many items. An item belongs to a shopping cart. In a relational database, without foreign keys, how do you associate the shopping cart with the items?

In a non-relational model, you'd generally copy the items/products and their quantity into the shopping cart. That's a huge change from the relational model, and takes much more space than a normalized approach, but it also comes with some advantages. For example, it makes sure that the price of the items in the cart remain constant, even when you change product prices. That way, you don't have to inform the user "oh…

Agreed. Invoices, for example, are a representation of history so they need the exact data that was used at the time. They can have a reference to a product id but at any time it's possible for the data associated with that product to be completely changed from the meaning at the time of the invoice. This happens with, e.g., UPCs, which can be reused. On the other hand, if you have assemblies of products then you probably want those items to update themselves automatically.

Re: We do not use foreign keys (2016)

#270

Post by CGamesPlay »

Oh man, that migrations point. I remember at my last company needing to handle this. We had some (huge) read-only tables that were referenced by other tables and we needed up update the read-only tables in batches from raw mysql dumps. The keys in the dumps were stable, so we could swap out the table and the references would still be valid. Since the referenced tables were huge, they took a long time to upload the batches, and we needed a migration that was ACID and fast.

The process ended up looking like this:

1. Create a `foo_load` and a `foo_unload` database, dropping any that already exist.

2. Populate `foo_load` with all of my new data.

4. Rename `foo.table` to `foo_unload.table` and `foo_load.table` to `foo.table`. This is atomic, fast, and updates references to point to `foo_unload.table`.

At this point the application reads from the new data, but referential integrity checks still verify against the old data.

5. Update each reference to `foo_unload.table` to point to `foo.table` using ALTER TABLE DROP FOREIGN KEY, ADD FOREIGN KEY. Again this is atomic and runs quickly.

6. Drop the `foo_unload` database.

Now this process is slow, but the switchover is 2 atomic steps and requires locking the tables for only a few milliseconds. The process is safe as well, if it crashes at any step, running the script again will cause it to safely recover. Well, with one more step zero:

0. If `foo_unload` exists, do the same rename step as step 5.

This solution worked for us for a few years. Eventually we swapped out the whole architecture around these tables and ended up with a more traditional ETL flow that didn't involve moving round huge mysql dumps.

Post reply on HN