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

161–170 of 337 posts

Re: We do not use foreign keys (2016)

#161

Post by resters »

taurath wrote: post #153
resters wrote: post #130

Earlier quoted context omitted.

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

I didn't say never care about scale in the early stage. I've found it a more common problem that people in startups are building systems with too much complexity to solve problems they faced at previous employers, in order to "save themselves time" in the future that ends up never coming. There are certainly some technology companies for which scalability is the most important thing - Instagram being the biggest exam…

> YAGNI

OK so then does YAGNI apply to investment? Why not let existing firms handle it in the S&P?

> YAGNI (continued)

over-engineering is definitely a problem, but so is hindsight bias.

I think that most non-trivial tech builds involve some smart investments and some dumb ones. It's essentially a portfolio of decisions made in the face of uncertainty. YAGNI is cynicism and hindsight bias, and over-generalization.

I think this is less obvious than it should be because of the popularity of the highly cynical "agile" approaches that consider a project that does not plan beyond the next sprint as somehow not taking on code debt.

Re: We do not use foreign keys (2016)

#162

Post by LaGrange »

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…

> Reporting and bi data might get hosed.

> Account management might get hosed.

LOL, if I read the data directly from the db instead of via the application's API then sure, I lose the application's guarantees. But, y'know, same might be said if I just go and read the DBs files from a sidecar shell script or something.

> It puts all applications on top into a undefined state

...that's just an assumption that's false.

Re: We do not use foreign keys (2016)

#163

Post by evanelias »

matt4077 wrote: post #142
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 been a while, but IIRC at the time Rails got started MySQL actually did not even support foreign key constraints. Since that was the DBMS of choice, it wasn't much of choice.

InnoDB's foreign key support predates the existence of Rails by several years. However, InnoDB wasn't the default storage engine for MySQL at the time, so that may be a factor.

Re: We do not use foreign keys (2016)

#164

Post by spookthesunset »

lmm wrote: post #136
Diggsey wrote: post #121

Earlier quoted context omitted.

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.

I'd argue that any decent application programming language can express these constraints better than SQL can.

How? FK constraints are trivially expressed in SQL. It is just a "REFERENCES table_blah ON DELETE DO BLAH ON UPDATE DO BLAH".

Re: We do not use foreign keys (2016)

#165

Post by rexpop »

steveklabnik wrote: post #12
amingilani wrote: post #8

How would a Rails app be impacted by this? How would you create one-to-many, or many-to-many relationships? I'm sorry if this question sounds silly, but I've never heard this line of reasoning before and I'm fascinated by it.

GitHub is a Rails app. [1] Rails does not create foreign keys by default, so most Rails apps are doing exactly this. I think you may be missing the distinction between the database feature "foreign keys", and the general concept of an entry that refers to another entry. Rails does the latter, but not the former, unless you explicitly do it yourself. (Maybe Rails has changed dramatically in the last few years, but up…

Thank you, this was helpful for me. I still don't, frankly, recall (did I ever know) how Rails implements the latter without the former.

Re: We do not use foreign keys (2016)

#167

Post by fdr »

Hamuko wrote: post #72
fdr wrote: post #9

There seems to be a certain mysql tinge to this value calculation. I use foreign keys and like it (I use Postgres). I do not encounter the problems the author does. I omit them when they are a problem for some reason. This is rare in routine work.

I'm gonna wager a guess and say that most MySQL users do use foreign keys without a problem. Most MySQL users are not running Github scale.

That's probably true. The only tell for that, really, was the complexity around migrations/schema changes, which seems a lot more painful on MySQL.

Re: We do not use foreign keys (2016)

#168

Post by [deleted] »

resters wrote: post #161
taurath wrote: post #153

Earlier quoted context omitted.

I didn't say never care about scale in the early stage. I've found it a more common problem that people in startups are building systems with too much complexity to solve problems they faced at previous employers, in order to "save themselves time" in the future that ends up never coming. There are certainly some technology companies for which scalability is the most important thing - Instagram being the biggest exam…

> YAGNI OK so then does YAGNI apply to investment? Why not let existing firms handle it in the S&P? > YAGNI (continued) over-engineering is definitely a problem, but so is hindsight bias. I think that most non-trivial tech builds involve some smart investments and some dumb ones. It's essentially a portfolio of decisions made in the face of uncertainty. YAGNI is cynicism and hindsight bias, and over-generalization. I…

[deleted]

Re: We do not use foreign keys (2016)

#169

Post by cryptonector »

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…

Not using FKs essentially means eventual consistency, and it's on you to ensure it. Obviously, eventual consistency is something a lot of systems have to settle for, often for reasons that have nothing to do with architecture, but just because of circumstances where you have to synchronize disparate systems and there's no way to eliminate all but one.

I'll note that there are very large systems that do provide ACID-like consistency (e.g., Spanner, Lustre), so it's not at all that "large -> eventual consistency".

I won't pass judgment on projects where eventual consistency was a given from the start, even if I might think they could have done better, but too, I wouldn't make either approach a hard and fast rule: circumstances vary. What bothers me is the extent to which I see "triggers bad", "FKs bad", "business logic in the DB bad" blanket statements out there.

Re: We do not use foreign keys (2016)

#170

Post by spookthesunset »

GauntletWizard wrote: post #155
spookthesunset wrote: post #97

Earlier quoted context omitted.

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…

You can keep saying that, but it does not make it true. Properly modeled data does not need those constraints. Well written software handles these correctly. Mediocrely written software fails and complains loudly. Badly written software might get hosed. You're at just as much risk (or more, in my opinion) of that with bad schema changes as with not having foreign key constraints.

Do you not check input from your javascript front-end before you save it? Even if that front-end does its own validation? (No, you see, but there is only one web front-end.... we don't need the backend to validate the input!!!!)

In what way is letting the database ensure it isn't getting fed crap any different?

Why do developers constantly think it is okay to let unvalidated user input hit their database? Any client calling your database is a hostile client that will feed your database bad data. Arguing otherwise is complete ignorance.

Post reply on HN