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

121–130 of 337 posts

Re: We do not use foreign keys (2016)

#121

Post by Diggsey »

spookthesunset wrote: post #46
davidw wrote: post #11

How about this: if your service gets as big as github, then maybe consider doing odd things to eke out more performance or shard or whatever. Otherwise: use FK's to maintain stronger data integrity. As hesk mentions below, in Postgres, you can do all kinds of table ALTERing if needs be.

FK’s don’t just maintain “stronger” data integrity, they are the only way to maintain relational data integrity. Application code cannot maintain that relational integrity, period. Any developer who thinks application code can enforce relational integrity is naive and does not understand relational database systems. It is impossible for any layer above the database itself to keep things from getting corrupt.

This is not remotely true. You can do this with pretty much any database that supports SERIALIZABLE isolation-level transactions correctly.

The point is that application code has bugs, and it's a lot easier to specify your constraints declaratively in a single place using a purpose-built DSL (SQL) than it is to enforce them procedurally every time you access the database.

Re: We do not use foreign keys (2016)

#122

Post by spookthesunset »

benburleson wrote: post #105
Litmus2336 wrote: post #35

Earlier quoted context omitted.

Seriously. "As a C developer, I never check exit codes of child processes. We can just enforce it by ensuring child processes don't have bugs"

`malloc` won't fail, right?

the javascript will enforce the user input. no need to have the backend check it again!

Re: We do not use foreign keys (2016)

#123

Post by EugeneOZ »

spookthesunset wrote: post #27

The number of times I’ve seen serious data corruption because “foreign keys are bad and we can just enforce it in code” is amazing. There is zero excuse to not use FK’s Any database that doesn’t use FK’s is almost guaranteed to have crap in it that didn’t get cleaned up, resulting in data corruption (and yes, dangling stuff in tables count as data corruption). Developers aren’t perfect. Shit will slip through even wi…

> and yes, dangling stuff in tables count as data corruption

No. There are exceptions like financial systems, but most of the time it doesn't matter if your DB has dead links to some removed entities.

> Not using FK’s is just plain old ignorance.

Nice level of argumentation, but I prefer the way how author from github can prove his opinion.

Re: We do not use foreign keys (2016)

#124

Post by SkyPuncher »

spookthesunset wrote: post #46
davidw wrote: post #11

How about this: if your service gets as big as github, then maybe consider doing odd things to eke out more performance or shard or whatever. Otherwise: use FK's to maintain stronger data integrity. As hesk mentions below, in Postgres, you can do all kinds of table ALTERing if needs be.

FK’s don’t just maintain “stronger” data integrity, they are the only way to maintain relational data integrity. Application code cannot maintain that relational integrity, period. Any developer who thinks application code can enforce relational integrity is naive and does not understand relational database systems. It is impossible for any layer above the database itself to keep things from getting corrupt.

There are many cases where relational integrity does NOT need to be enforced and in fact can happen in the application level.

One of the big reasons NoSQL got popular was because it broke the norms around strict data relationships.

Re: We do not use foreign keys (2016)

#125

Post by CharlesColeman »

Hamuko wrote: post #70
geophile wrote: post #32

Wow. No discussion at all regarding the correctness of their databases. Is all the data correct, or are there FKs referencing missing PKs? Actually, even discovering this would be difficult, since any FK violation could be just viewing the middle of what would be a consistent update in flight. I get that cross-shard FKs are especially difficult. But any discussion of this topic without addressing correctness concerns…

It's not like you can avoid incorrect data just with foreign keys. Say you have an invoice model. There's a boolean value indicating that the invoice is final and a numerical value for invoice number. Final invoices must have invoice numbers. But a bug in your system manages to update an invoice so that it's final, but missing an invoice number. That row is incorrect and it's gonna cause an issue somewhere. I can onl…

> There's a boolean value indicating that the invoice is final and a numerical value for invoice number. Final invoices must have invoice numbers. But a bug in your system manages to update an invoice so that it's final, but missing an invoice number.

Couldn't you catch that with a CHECK constraint?

https://en.wikipedia.org/wiki/Check_constraint

Re: We do not use foreign keys (2016)

#127

Post by fennecfoxen »

eerikkivistik wrote: post #84
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…

I'll jump in here as well in case someone is considering throwing out foreign keys due to this. I would argue, never make compromises like this unless you have a scaling problem that is so bad, that there is no other way around it. Don't get me wrong, there are places where this is the case, but 99% of software does not have this issue. Use foreign keys and save yourself the headache in the future. Also bear in mind,…

I would add to this if you have the scaling problem, and are planning to throw out foreign keys, and start sharding: you have essentially moved to a distributed data store, but you are using a query language not designed for it. You will face challenges learning what parts of the database system you can and cannot use safely, and enforcing these constraints will be fraught.

You are essentially moving to NoSQL. Maybe it makes more sense to just own up to being on NoSQL, and using a data store and data access patterns that were actually built for the task? It should be something to think about, anyway.

Certainly consider this option if you're planning for that scale from the start as a more meaningful alternative to simply saying "no foreign keys" from the start. I won't necessarily say it's overengineering; there are entire problem classes where tracking millions and billions of records are on the table, particularly in event monitoring.

Re: We do not use foreign keys (2016)

#129

Post by tiglionabbit »

dylanz wrote: post #120

Application vs Integration databases: https://martinfowler.com/bliki/IntegrationDatabase.html . If you're going to have one application writing to your database, having your constraints live in your application is definitely a viable option.

Not if you have multiple processes accessing the database simultaneously without some additional coordination layer.

Re: We do not use foreign keys (2016)

#130

Post by resters »

taurath wrote: post #25
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 the same vein, I'd like to remind people that you are probably not a "temporarily low-scale big-data company", in the same vein as a temporarily embarrassed millionaire. In lots of cases going for the very long term scalable solution will be an impediment to your growth, and I'd suggest dealing with those issues when the chance that you need them is on the horizon, rather than across the globe. CQRS is one of the…

> I'd like to remind people that you are probably not a "temporarily low-scale big-data company", in the same vein as a temporarily embarrassed millionaire.

This point is valid in the same way as telling a startup founder to give up the startup and invest in the S&P 500. It's trivially true, but ultimately useless advice.

Building technology is hard precisely because there are so many tradeoffs. Speed to market vs scalability is one of many. It's silly to pretend that there is some kind of rote obviousness to never caring about scale in the early stage, anymore than there is rote obviousness to just investing in the S&P.

Ex post, most startup founders/investors should have just invested in the S&P, and most early stage tech leads should have just used Wordpress or Rails.

Post reply on HN