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

91–100 of 337 posts

Re: We do not use foreign keys (2016)

#91

Post by asdkhadsj »

jagged-chisel wrote: post #10

Worked at a bank years ago. One of their DBAs eschewed FKs in production. Databases were designed with FKs which were enforced in Dev and QA. If your app survived testing (automated, QA team ... the entire gamut) without producing FK violation exceptions, your app could be promoted to production, where FKs were not enforced, making things pretty fast. I feel like this was a stopgap on the way to eliminating FKs. I ha…

A neat idea for sure. However I'd be concerned that suddenly you have two different applications. Example, if you delete a row with a cascading deletion elsewhere, that will work on dev, but on prod it would leave dangling data.

Though, I imagine you could simply not use cascading behaviors.. Neat idea though. Scary, but neat hah.

Re: We do not use foreign keys (2016)

#92

Post by hrktb »

spookthesunset wrote: post #34
DaiPlusPlus wrote: post #20

Earlier quoted context omitted.

As someone currently fighting a battle with an LoB application written without foreign-key constraints with hundreds of thousands of rows of corrupted data because of bugs in sprocs that assigned the wrong value to the wrong foreign key column because they were similarly named - THIS! The reply in the GitHub thread we’re talking about makes it clear that they still perform FK validation - it’s just performed in the a…

The application code isn’t enforcing FK constraints because that is impossible for the application to do. Their database is almost 100% guaranteed to be corrupt as a result. Application code has bugs. Application code can fail in ways that result in corruption. A primary job of the database is to keep itself from getting corrupted. Enforcing foreign key violations is only something the database can do correctly. Punt…

> A primary job of the database is to keep itself from getting corrupted

That would be where the road splits. If you handle the database as a very fast and structured storage application, a lot of these assumptions go away, with then a different set of tradeoffs.

Some data corruption could be fine if you can guarantee the critical cases, just as bugs in the code are fine as long as the useful cases are covered.

I remember a database with scheduling entries in it that could get duplicated depending on the sharding, but it didn’t matter because the app handled the case gracefully.

I think understanding the tradeoffs that match the best the use case is the most important point, always assuming that a DB has to guarantee integrity can be a burden preventing from looking at all the options.

Re: We do not use foreign keys (2016)

#93

Post by __jal »

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've worked on exactly one project where performance concerns lead to removing Fkeys.

The compromise we came to was to enforce them in dev and qa in order to catch bugs, and relax them in prod.

I still strongly believe that database constraints are a developer's best friend, in that you can trivially make your data structures fight back against misuse. This makes several classes of bugs obvious. But like anything, there are times they are not optimal.

I'll also say I think there are very few cases where a small performance advantage outweighs the costs, and would be hesitant to head down this path with a team less competent than the folks at Github.

Re: We do not use foreign keys (2016)

#94

Post by josteink »

dekhn wrote: post #65

I have been perpetually annoyed at the SQL/RDBMS/relational calculus model. It always feels like a huge context shift from imperative programming. after many years of writing SQL, I noticed that many other people end up writing SQL statements that look more or less like computer programs (CASE statements, subselects, etc). It all came to a head when I naively asked an experienced SQL developer how to represent a tree…

> I did a deeper dive I learned that tree representations in SQL are a rabbit hole of insanity all arranged around referential integrity.

It’s not crazy, just impractical.

Trees are mostly represented the same way in memory, except you don’t have to access them in table-format, which admittedly for tree-data is very, very cumbersome.

Re: We do not use foreign keys (2016)

#95

Post by redact207 »

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.

Re: We do not use foreign keys (2016)

#96

Post by dekhn »

topmonk wrote: post #88
dekhn wrote: post #65

I have been perpetually annoyed at the SQL/RDBMS/relational calculus model. It always feels like a huge context shift from imperative programming. after many years of writing SQL, I noticed that many other people end up writing SQL statements that look more or less like computer programs (CASE statements, subselects, etc). It all came to a head when I naively asked an experienced SQL developer how to represent a tree…

> So any time you want to get all the CHILDREN of a PARENT you have to query all children to see if they have a FK to the appropriate PARENT. It's just an index lookup, so what? You'd rather mash all the data into a single parent field, is that it?

i will admit that once I saw postgres has arrays and composite types, it seemed more natural to model this as NODE with array of CHILDREN.

I think it's more about the expressivity of pointers and arrays compared to the "backwardness-feeling" of index lookups.

Re: We do not use foreign keys (2016)

#97

Post by spookthesunset »

GauntletWizard wrote: post #67
spookthesunset wrote: post #34

Earlier quoted context omitted.

The application code isn’t enforcing FK constraints because that is impossible for the application to do. Their database is almost 100% guaranteed to be corrupt as a result. Application code has bugs. Application code can fail in ways that result in corruption. A primary job of the database is to keep itself from getting corrupted. Enforcing foreign key violations is only something the database can do correctly. Punt…

There are 100% foreign key violations in their database. That is not the same as their database being corrupt. They have engineered for, and understand the implications of, foreign key violations. Typically, it's as simple as "This row can be deleted", and that can cascade - at a totally different rate than you'd find in a database and with totally different performance characteristics.

Foreign key violations are data corruption!!! It violates the rules of how the data relates and can and will screw up any number of things that depend on the rules being enforced.

Reporting and bi data might get hosed.

Account management might get hosed.

Who knows what happens when FK rules are violated because by definition they should never be violated. It puts all applications on top into a undefined state, leading to bugs and god knows what else.

Foreign key violations are 100% data corruption.

Re: We do not use foreign keys (2016)

#98

Post by malvosenior »

So basically they don't use foreign keys because they're into premature optimization.

If you need to shard your database, you're big enough that you'll have the resources to engineer the new solution.

The vast, vast majority of use cases will benefit from the database ensuring the integrity of your data.

Re: We do not use foreign keys (2016)

#99

Post by munk-a »

spookthesunset wrote: post #81
munk-a wrote: post #49

Earlier quoted context omitted.

I think this is a bit hyperbolic of an expression, but I do want to reinforce that all applications need to confirm data integrity, you either confirm it when saving the data, when extracting the data, or have to very carefully balance various consistency concerns and either enforce consistency before saving data or enforce consistency after saving (but before extracting) data. Things like RDBMS's provide some really…

> I do want to reinforce that all applications need to confirm data integrity, you either confirm it when saving the data, when extracting the data, or have to very carefully balance various consistency concerns and either enforce consistency before saving data or enforce consistency after saving (but before extracting) data. There are two kinds of error handling. The “happy error” and the “fuck you asshole” error ha…

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, but if you're dealing with data volumes like GitHub then it's conceivable that all the other salves for enforcing data integrity fall short. In that case there are ways to approach removing FKs, but, when you do so, you're not (logically speaking) giving up the data-integrity from FKs, you are replacing FKs as a tool for data-integrity with another tool for data-integrity (one that will probably be very similar to FKs) - under this guise DB FKs can stop making sense (though also having any sort of RDBMS engine likely also stops making sense as you're essentially adopting the functional responsibility for being an RDBMS into the primary application).

Re: We do not use foreign keys (2016)

#100

Post by devit »

All of these are mostly issues with the database engine implementation:

1. "FKs are in your way to shard your database." => not a problem for distributed databases that can query and duplicate data across shards/servers; alternatively, if the referenced data just isn't there in the database, a foreign key is not usable by definition

2. "FKs are a performance impact" => the app either just got the foreign key value from the database, so it should be cached in memory in a properly implemented database engine, or otherwise, the application relies on the database checking so you need the foreign key for correctness

3. "FKs don't work well with online schema migrations." => not a problem with database engines that properly support online schema changes without locking, downtime or table renaming hacks

Post reply on HN