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

111–120 of 337 posts

Re: We do not use foreign keys (2016)

#111

Post by taurath »

corebit wrote: post #71
taurath wrote: post #25

Earlier quoted context omitted.

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…

The biggest issue with CQRS I've seen is people thinking CQRS means you need multiple, duplicate data structures, mappers, a few Kafka topics and a PhD, when IN REALITY all it means is you put methods that return data without modifying it in one interface/class and methods that have side effects in another interface/class - which is really just a good application of interface segregation. Moreover, you now have a gre…

To be clear, I'm referring to the (in my circles) usual parlance of not "pure" CQRS, which is as you describe and could entirely be in a OOP "Model" layer, but what usually goes with it - things like event sourcing for auditing, cached read services (eventual consistency), etc. Should've been more specific but was just pattering off the first big complexity thing that came into my mind.

Re: We do not use foreign keys (2016)

#112

Post by spookthesunset »

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

Earlier quoted context omitted.

> 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, 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, people will stop at the sentence I quoted and go build yet another FK free system. Said system will undoubtedly fill up with corrupt bullshit data that eventually leads to exciting mystery bugs in production that has everybody scratching their heads. I’ve seen it time and time again....

Re: We do not use foreign keys (2016)

#113

Post by wwarner »

To me the issue is that the data model should be defined in a single place. If you're using foreign keys, then the development process needs to take that into account and provide a sensible way to modify the data model in a way that the FKs keep up with the evolution of the product. It's going to mean a lot more migrations for sure.

I personally have never seen foreign keys implemented correctly -- they've captured an outdated version of the data model and are just in the way. For me, they're similar to stored procedures, usually to be avoided.

Re: We do not use foreign keys (2016)

#114

Post by zabzonk »

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…

How would the system update the invoice at all without an invoice number?

Re: We do not use foreign keys (2016)

#115

Post by gigatexal »

Ahh to fkey or not to fkey. The problem with doing referential integrity in the app is what happens if a change is made to the database outside of the app?

I guess it depends. If you’re at the scale where you can’t refactor your database to keep up and you, after profiling or some other method, have proven that moving integrity enforcement into the model of your app is faster then by all means do that.

That being said: foreign keys help the query optimizer make better decisions. If you’re using and ORM though that doesn’t matter as you’ll get generic queries and you’re likely not profiling your queries anyway.

They also are a way of self documenting the relationship between things.

If you’re going to get rid of foreign keys then why not denormalize so that the data is easier to query and less likely to get corrupted? Reduce the number of tables say into a single table a-la the DynamoDB approach

Re: We do not use foreign keys (2016)

#116

Post by swasheck »

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,…

Such an excellent post. Thank you so much.

Re: We do not use foreign keys (2016)

#117

Post by whalesalad »

spookthesunset wrote: post #104
whalesalad wrote: post #75

Earlier quoted context omitted.

Eh. The issue arises when you have a very poorly designed schema and missing application-level operations to perform cleanup and deletion. You don’t want to be caught in that situation because then you are handcuffed and cannot clean the database properly. For example, from the perspective of business operations you might have something that creates 1 single thing where under the hood 5+ DB objects are all created in…

Bullshit. Even with the most perfect developers and perfect system, letting application code enforce constraints will lead to data corruption period. Some application will crash or something and leave dangling garbage behind and boom you are fucked. Would you ever trust that form data is valid because the JavaScript front end “validated” it? No! Why the hell are you going to trust that everything sent to the database…

Yikes. Someone got up on the wrong side of bed today!

Re: We do not use foreign keys (2016)

#118

Post by SkyPuncher »

spookthesunset wrote: post #97
GauntletWizard wrote: post #67

Earlier quoted context omitted.

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, leadin…

> It violates the rules of how the data relates

You're missing an important point, it violates a certain set of rules of how the data relates. That certain set of rules typically makes it easier to write most general data applications.

If your rules define a foreign key as optional, you can easily engineer an application around it.

Great examples include NoSQL and Ruby duck-typing. In both cases, you infer actions based on the data structure, rather than making explicit assumptions.

Re: We do not use foreign keys (2016)

#119

Post by uses »

I'm confused, how do you design a database where things in A can't reference things in B? It's not relational at that point right?

Or is this just about the problems of DBs where you enforce referential integrity? In which case, if that's such a problem, like just don't enforce referential integrity...right?

Post reply on HN