We do not use foreign keys (2016) • Hacker News Board

Re: We do not use foreign keys (2016)

#231

Post by evanelias » Sat, Nov 09, 2019, 12:06 AM UTC

spookthesunset wrote: post #89

mixedCase wrote: post #80

Earlier quoted context omitted.

> Maybe shitty toy database systems like MySQL While appropiate to define in few words some of MySQL's colossal mistakes, this isn't the kind of language that will sway heads that have been comfortably using MySQL because those defects are just "what DB's do".

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 statements are incorrect. Do you have much direct experience with high-volume OLTP database workloads, or are you basing your views of MySQL on something else?

Re: We do not use foreign keys (2016)

#232

Post by spookthesunset » Sat, Nov 09, 2019, 12:08 AM UTC

muglug wrote: post #53

I work at Vimeo. We don't have any foreign key constraints in our database either.

Sorry to hear it!

Re: We do not use foreign keys (2016)

#233

Post by spookthesunset » Sat, Nov 09, 2019, 12:12 AM UTC

bfrog wrote: post #166

Well when you use MySQL, you end up with MySQL hacks like this

Dunno why you are getting downvoted. MySQL is such a poor database that is always leads to hacks. The people who use MySQL almost always get burnt by something and figure all DBs are like that. Then people who have never worked with non-toy databases assume all databases are like MySQL and dismiss the whole field, which is a complete shame.

The fact is, a real database system like PostgreSQL is one of the coolest, most underutilized tools developers have in your toolbox. Good databases can do so much cool shit to help you. It is amazing how well a good database system can take your weird crazy query and return something in mere milliseconds.

Re: We do not use foreign keys (2016)

#234

Post by jagged-chisel » Sat, Nov 09, 2019, 12:12 AM UTC

asdkhadsj wrote: post #91

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.

No DELETEs - it's banking!

But seriously, there are a host of issues one must overcome for this plan to work. And there were indeed managed permissions for DELETE. But ultimately, I wasn't aware of much of the implementation detail ...

Re: We do not use foreign keys (2016)

#235

Post by jagged-chisel » Sat, Nov 09, 2019, 12:15 AM UTC

munk-a wrote: post #83

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…

This approach is quite scary to me and I would have argued very vocally against their rejections of FKs. There are ways to rely on replication for read serving, periodic disabling of FKs during batch inserts, FK integrity checks on replications, ... these can all address the performance issues inherent in FKs, it's also (generally) quite possible to attempt to architecturally disentangle too large networks of interde…

> This approach is quite scary to me...

No kidding. My first thoughts went to referential integrity, and getting misbehaving apps successfully past testing...

Re: We do not use foreign keys (2016)

#236

Post by spookthesunset » Sat, Nov 09, 2019, 12:15 AM UTC

Aeolun wrote: post #198

spookthesunset wrote: post #150

Earlier quoted context omitted.

> Don't enforce FK relationships with a constraint in the DB. Make sure the values in both tables which may be joined are consistent by using application code to enforce this. For those reading at home who aren't good with databases. This is exactly the same statement as: "Don't enforce valid inputs on the backend. The javascript front-end will enforce it for us". Always validate your inputs at the appropriate layer.…

Except hopefully your customers do not have direct access to your backend.

Are your engineers omnipotent beings who never fail, never make mistakes and understand the system entirely (even all the parts they don't know about)? Is your QA process perfect? Your servers never crash in weird states?

Lucky for you!

Re: We do not use foreign keys (2016)

#237

Post by patsplat » Sat, Nov 09, 2019, 12:23 AM UTC

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…

Who needs foreign keys... or relations?

Documents do well for a wide variety of applications ;-)

Re: We do not use foreign keys (2016)

#238

Post by ken » Sat, Nov 09, 2019, 12:27 AM UTC

GitHub is a special case, perhaps in all of computerdom, in that most of their public data is stored not in (SQL) databases, but in git repositories. That's rather unique!

So we know they must already have a great indexing and search system in place, and they can't use foreign key constraints on most of their data, anyway. Git simply doesn't have that. It's not that kind of database.

Given this environment, it's not surprising they don't use FKs. It wouldn't surprise me if they don't use JOINs much! Throw it all in a repo, and let the indexer sort it out.

That's great for them, but my data isn't shaped like that. Data types and referential integrity are more important to me than history and bisection.

Re: We do not use foreign keys (2016)

#239

Post by TimTheTinker » Sat, Nov 09, 2019, 12:28 AM UTC

bbrks wrote: post #218

TimTheTinker wrote: post #69

Earlier quoted context omitted.

If you have a need for a ID that is unique but otherwise meaningless in a distributed system, feeding a concatenated node ID, timestamp (to ms), and looping transaction counter (mod a sufficiently large number) into a secure hash like SHA-256 should take care of it.

Sure, that satisfies the uniqueness aspect if that's all you're looking for in an ID. But it doesn't satisfy the predictability aspect. You can't know what the ID will be ahead-of writing the item to the database, nor store the item in a shopping cart without retrieving the item via a different piece of information (which is pretty likely to be SKU in this case)

When there aren't any connections to known unique keys (like SKUs for shopping), PK predictability isn't really possible (or necessary). The above formula is just an alternative to using auto-incrementing integers when in a distributed environment.

Re: We do not use foreign keys (2016)

#240

Post by nickpeterson » Sat, Nov 09, 2019, 12:32 AM UTC

My advice, put constraints that make sense as you're developing an application, and marvel at how often you accidentally violate them while writing trivial code. You'll realize pretty quickly that even when you know the whole area of what you're working on you still screw up. FKs might not add a lot of value on bet the company, mission critical, heavily tested code. But on less rigorous codebases they will save your ass.