We do not use foreign keys (2016)

https://github.com/github/gh-ost/issues/331#issuecomment-266027731

by Scarbutt • 7 years ago

349 320 7 years ago

hesk• 7 years ago

The main criticism seems to be that the FK relationship makes migrating the referenced table difficult. But why not remove the FK with ALTER TABLE before the migration, migrate, and add the FK back again (which will catch any missing primary keys), preferably inside a transaction?

brobinson• 7 years ago

preferably inside a transaction?

MySQL does not support transactional DDL, unfortunately.

zerg2k• 7 years ago

That’s no longer true since MySQL8 https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

shawnz• 7 years ago

So then don't do it inside a transaction. It still wouldn't be any less safe than their current approach of just not using FKs at all

mumblemumble4• 7 years ago

I think the emphasis there is the word "online".

The method you propose might work, but not necessarily well. Catching missing primary keys at the end is a problem because then you have a bunch of data integrity issues to sort out, preventing you from re-applying the constraint, in a live system where apps are relying on the database to do integrity checks, meaning that there's a decent chance that the running system is creating integrity issues faster than you can sort them out. Possibly orders of magnitude faster.

Doing everything inside a transaction may invite concurrency issues around locking, maybe even deadlocks. Which is again a problem in a live system, because you might be causing services to fail.

I'm not sold on FKs being something you should never do, ever, but I will say that I used to work at a place that pushed their database servers very hard on real-time tasks, and also tended to avoid foreign keys, and my biggest complaint was not data integrity, it was just that there was extra effort that needed to go into documenting the logical foreign keys (the keys still exist, they're just not enforced by a database constraint), since you couldn't just read them from the database schema.

I'd like to say that this can be something where you default to having FKs, and remove them as you hit performance problems, except that the poster is right: Developers have a habit of leaning on database constraints instead of doing their own sanity checks, and they simply don't know all the places they're doing it - they're not necessarily even aware they're doing it when they're doing it - meaning that removing FK's after the fact doesn't get you to "constraints are enforced by the apps", it gets you to "constraints aren't enforced". I think you probably want to try and anticipate ahead of time if you'll have a problem, so that everyone can know from the get-go whether they'll be working without a safety net.

jayd162• 7 years ago

I'm not sure "you can't add checks because they might fail!" is sound reasoning...

vbezhenar• 7 years ago

Is it even possible to realistically do FK checks in app code? Nobody uses serializable transactions and with other isolation levels I think it's possible to check for FK, it's OK now, insert new row, but another transaction simultaneously deletes that row and both transactions happily commit. It's called phantom read. Probably it's very rare event, but it'll happen.

cryptonector10• 7 years ago

I didn't quite understand the migration issue TFA talks about, but at least with PG, there's more than enough clever ways to make schema changes incrementally.

conroy• 7 years ago

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 a performance cost. Yes, they make sharding more difficult. In my experience, at smaller scale the trade-offs are worth it. YMMV

DaiPlusPlus2• 7 years ago

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 application code rather than the DBMS.

I note there is another alternative: deferred constraints - or just run a query to check for invalid rows at 3am every morning.

spookthesunset25• 7 years ago

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. Punting the responsibility to a higher layer will result in corruption.

sturgill3• 7 years ago

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 didn’t bother me. But I never understood the disdain for foreign keys. For 99.99% of web apps, you want them (dare I say, need them).

Even in Rails 3 I would add them by hand in the migration files. Very few applications will ever actually care about sharding. We were pulling in millions at the last Rails company I worked for and were just fine with a master and a single replica.

If you get to a point where sharding is best for your company you hopefully have enough revenue coming in to fund a data transition. Your goal should be to outgrow what MySQL (or Postgres) can do for you in master/replica mode. If you do, you’ll likely be independently wealthy...

If data integrity matters at all, model-based checks (or periodic queries for orphaned data) will not suffice. Just put a foreign key in the table where it belongs and let your DB do what it does best. ACID is an amazing thing...

michaelt• 7 years ago

or just run a query to check for invalid rows at 3am every morning

One of the nice things you get from database FK constraints is when something messes up the data, you get an exception thrown in the code that's messing it up, complete with a backtrace and whatever inputs and timestamps you care to log.

taurath5• 7 years ago

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 biggest examples I've seen personally for this - your flexibility will go down, work to do "basic" things will go up, and you will really lose a lot of speed solving for 10 y/o company problems with technology rather than 1-5 y/o company problems in terms of product market fit.

opmac• 7 years ago

YAGNI - learn to embrace it.

corebit• 7 years ago

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 great rule of thumb for your brand new junior developers: "If you're handling a GET request, use this class, and if not use this other class" making code reviews easier to do and helping to enforce better structure in your code. Plus tons of other benefits.

The rest of that stuff that isn't CQRS you might eventually need if you scale, but is easy to add once you have the bones in place.

resters2• 7 years ago

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.

Udik• 7 years ago

CQRS is one of the biggest examples I've seen personally for this: your flexibility will go down, work to do "basic" things will go up

The only time I've worked on a codebase that had implemented CQRS, it was when I was given to maintain a crud application to manage maybe 20 different records. Rarely used. The people who had developed it in .net mvc were of such technical prowess that, besides the CQRS pattern, the frontend still had a functional shopping cart (yes, you clicked on "save" and your "order" went to the shopping cart, relic of the mvc demo app).

Sometimes I wonder if I've just been unlucky or the world is all like this.

whack• 7 years ago

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.

My favorite example is a bootstrapped startup that I co-founded. We had a couple thousand users and less than a gigabyte of data. The app was a run-of-the-mill CRUD app. I built the entire back-end API as a single service that could be built and deployed on heroku in minutes.

At some point, I left the project, and my co-founder hired an expensive consultant to review the system and provide feedback. One of his suggestions was to break up the entire service into a suite of microservices. And this was for a service that barely averaged a handful of active users at any one time. And a team of ~2 engineers working on the back end code. Microservices.

hinkley• 7 years ago

I like where you're going for that. Why don't we just call it 'temporarily embarrassed big data company'?

On one project where we seemed to handle engineering strategy well, one of the considerations we'd make is whether paying the tech debt would accompany an inflow of cash or not. If it didn't we'd better tackle it now so we don't bleed out.

It's probably another way to say "spend money to make money". Management is more willing to spend out of a problem when they've had a fresh fix.

The trick is, though, can you pay down that debt fast enough that the customers don't get upset while waiting. If someone's firing up a big ad push you'd better be prepared. And people, like me, who get caught flat-footed once (or have friends who were), don't want it to happen a second time. So they get opinions on 'last responsible moment' that might differ from the idealists.

duxup• 7 years ago

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.

My attitude is:

"Man if this takes off and I have to make some changes on how we do this....I should celebrate!"

capoDanger• 7 years ago

Came here to say something like this, though you said it more eloquently. Also a couple replies to you below add great context.

kyrra• 7 years ago

Two of Google's big internal databases don't use foreign keys (Spanner[0] and F1 [1]), rather they use hierarchies (parent/child tables). It definitely limits you a lot more than foreign keys, but seems to make sharding easier. It works well for some use cases, but takes some getting used to. Cockroachdb also has this functionality [2].

[0] https://cloud.google.com/spanner/docs/schema-and-data-model

[1] https://ai.google/research/pubs/pub41344

[2] https://www.cockroachlabs.com/docs/stable/interleave-in-pare...

munchbunny• 7 years ago

Someone two degrees of separation from me coined the term "medium data". I don't remember their name, but I absolutely love the term.

You are "big data" when black swan events (like hardware failure taking down a database node) become regular enough that you begin to statistically model it. Before that, you might just have a lot of data, but you aren't having "big data" problems.

blantonl• 7 years ago

When you get to a point where you need to start sharding your database, congratulate yourselves for the enormous success and toast the night to come.

The next morning you can embark upon the engineering effort to start the sharding re-architecture.

pfranz• 7 years ago

Even at very large companies, hacks can work surprisingly well for a surprising amount of time. As much as we harp on technical debt (which includes myself), we rarely appreciate how much time can be saved by implementing the most straightforward solution so we can focus on hard things.

I'm not sure if Guido was just being amicable, but in the recent Dropbox retirement post he said, “There was a small number of really smart, really young coders who produced a lot of very clever code that only they could understand,” said van Rossum. “That is probably the right attitude to have when you're a really small startup.”

eerikkivistik• 7 years ago

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, that if you ever hit that scaling problem, you are making so much money, that it's a nice problem to have.

swasheck2• 7 years ago

Such an excellent post. Thank you so much.

fennecfoxen• 7 years ago

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.

__jal• 7 years ago

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.

WorldMaker• 7 years ago

I've worked on projects where adding foreign keys increased performance tremendously. A good relational database can take advantage of them in all sorts of ways in execution planning. Even scenarios such as some types of sharding, a relational database sometimes can use foreign keys for useful data proximity information, to better co-locate "families" of records together.

You need to know your database engine, how normalized you are or are not, how appropriate your data is for its relational model. It's not always your FK constraints that are the bottleneck you might think they are. Sometimes their "slowness" is hiding a problem elsewhere in your schema, a bad shard or an index that could be better or a key-value pool with soft version controlled enums pretending to relational data.

Sometimes "optimal" isn't a simple spectrum but a checklist of trade-offs and no "right" answer.

swasheck2• 7 years ago

Additionally, at least in Sql Server, trusted foreign keys can give the CBO more, good options because of the guaranteed referential integrity on reads. FKs are killer on larger writes, though.

nkozyra• 7 years ago

Or, as a wise man once said, "Premature optimization is the root of all evil."

It's a different part of the field of course, but so many people yield opportunity cost chasing some difficult architectural problem that will likely never impact you, and if it does you'll have resources to throw at it.

Many startups won't even have slave production databases, much less have to worry about network distributed sharding, complex caching strategies, database optimization beyond query analysis.

kraig911• 7 years ago

+1 million times this. Seems like everywhere I work with are concerned about google size data problems when the apps I'm working on are at best 1000 concurrent users...

michelinman• 7 years ago

I agree. No two systems are comparable. Must be very confusing for the people ramping up. A solution fits the problem, not the other way around. Keep it simple stupid. KISS

patsplat• 7 years ago

Who needs foreign keys... or relations?

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

lemmsjid• 7 years ago

I will add to the pile of agreements on this.

Also, if you do end up sharding a relational database, it is often by identifying subgraphs of document-like structures that you can shard on. Need to ensure these subgraphs do not have foreign key relations with one another, but you can maintain the valuable foreign key relations WITHIN the subgraphs.

Practical example: database of user profiles where suddenly you have billions? You can skill keep foreign keys on user to email-addresses or user-to-comments while eliminating cross-user foreign keys.

I would also add a good rule of thumb: when you make the design decision to remove a database feature, you need to assume that you now need to handle that feature yourself, or your data will get corrupted. For example, when removing FK constraints, or transactional boundaries, or introduce irregular checkpointing, you now need to implement a data repair system, because your data will get broken. At which point you are probably going to end up using a change event log (your own transaction log) and a system that replays the logs in order to repair and rebuild the database.

devy• 7 years ago

In addition to context + YMMV, I want to mention, sharding is NOT THE ONLY solution for scaling[1], albeit it's a popular technique. So that makes Shlomi Noach's first point weaker.

[1] https://www.quora.com/What-are-alternatives-to-sharding-data...

amingilani• 7 years ago

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.

steveklabnik2• 7 years ago

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 until at least Rails 4, this was the case)

1: Obviously it's also way more, but the main codebase is a Rails app, and has been forever. This thread is from 2016, so that was probably even more true then than now.

rexpop2• 7 years ago

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.

cosarara• 7 years ago

The relationships are there and you join just the same, but the database doesn't have foreign key constraints.

hateful• 7 years ago

They're not talking about whether or not a field is considered a foreign key to another table, but whether or not to define those keys in the database itself so that it also creates a constraint. Really the post is about having a constraint in the database, not the fact that the fields can be considered foreign keys.

fdr2• 7 years ago

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.

Hamuko6• 7 years ago

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.

fdr2• 7 years ago

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.

jeltz4• 7 years ago

True, but the people I know who run PostgreSQL at that scale do not complain about foreign keys. Not since PostgreSQL added separate lock levels to improve concurrency with foreign keys.

jagged-chisel3• 7 years ago

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 have no idea what his roadmap was because I wasn't in that group. But his process made for good thought fodder.

OJFord• 7 years ago

Hm, that is 'good thought fodder'.

My feeling on it though is that it just shifts all the faith to your test data being true to not only does but conceivably could happen in prod.

All very well testing with a bunch of constraints, but if it turns out one actually isn't exercised and then happens in production?

It also means you can't use them for validation or whatever. Which, I don't know, maybe you shouldn't anyway, but if you turn them off in prod you can't.

munk-a7• 7 years ago

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 interdependent data in an effort to reduce how large any single data store you are relying on will get. But disabling FKs in production is... eh.

I'd much rather see the opposite, declare the FKs on a testing environment NOT VALID, run application code, VALIDATE CONSTRAINT those you'd marked and see if anything is in violation - ensuring that application code won't (in the normal course of operation) generally hit FK constraints can greatly reduce your performance hit from having them... then in prod you can turn them on and be confident in your comparably lighter performance loss to data integrity.

barrkel7• 7 years ago

FKs add read locks to referenced rows. It limits concurrency and it is observable. FKs constrain your ability to incrementally widen 32-bit FKs once you go over 2 billion rows, if you start out with 32-bit PKs. Two concrete reasons to avoid FKs in production, or at least disable them for longer running transactions.

I think this perspective is something you only get once you've run bigger databases in production.

jagged-chisel3• 7 years ago

This approach is quite scary to me...

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

asdkhadsj• 7 years ago

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.

jagged-chisel3• 7 years ago

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 ...

davidw2• 7 years ago

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.

spookthesunset25• 7 years ago

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.

munk-a7• 7 years ago

I agree with your general gist, but it's not impossible, you just, essentially, are writing the engine yourself.

I've seen a few applications that keep some data hot-loaded into a shared slice of memory and have tight controls over the altering and saving of that data - and that's sort of one of the easier ways to partially avoid a full reliance on FKs, if you want to use write-through caching then you're essentially accepting the cost of implementing data integrity checks in the application layer which can be done - if you're very very careful.

Diggsey• 7 years ago

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.

SkyPuncher2• 7 years ago

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.

barrkel7• 7 years ago

You can implement FKs with the same cost as FKs. Look up the referenced rows and add read locks. It's not cheap. FKs are by no means free.

barrkel7• 7 years ago

You don't need a service as big as GitHub; you just need billions of rows.

I think you should enable FKs for test and dev, and possibly QA, but disable them in production.

Depending on your database, you'll get surprising interference in concurrent operations via other means than FKs, but FKs don't help.

davidw2• 7 years ago

"Just" need a billion rows.

Sure, some people get there, but it makes sense to do things the right way first, then figure out how to cut corners only when and if you need to.

andreimatei1• 7 years ago

At GitHub they should be using CockroachDB (:P), a scalable relational database that also supports schema changes. Then their FKs would work just fine (albeit with some performance implications).

hw• 7 years ago

Or, just not use FKs at all. There are many factors to consider when deciding on a DB, and being able to use FKs is probably not high on the list.

brlewis5• 7 years ago

being able to use FKs is probably not high on the list.

No, that's not at all probable. For many, "database" is synonymous with "ACID database", for which foreign keys are important. https://en.wikipedia.org/wiki/ACID#Consistency

bregma• 7 years ago

Build a firewall to keep them foreign keys out. Make the database great again.

PopeDotNinja• 7 years ago

Because I've never thought about this, I'll ask the dumb question...

A shopping cart has many items. An item belongs to a shopping cart. In a relational database, without foreign keys, how do you associate the shopping cart with the items?

greggyb• 7 years ago

The suggestion is to not use foreign key constraints in the database.

The suggestion is not to eschew columns which might be JOINed on in a query.

I.e. "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."

spookthesunset25• 7 years ago

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. Your backend can never trust input from a web front-end (even if there is "only one web front-end"). Likewise, your database should never trust input from its clients (even if there is "only one client"). Ignore this rule at your own peril.

monknomo• 7 years ago

The way I've usually seen it done is that the foreign key columns still exist. The relationships are there, the database just doesn't enforce them.

The items table might have a shopping_cart_id column

larkost• 7 years ago

You do not have to have a foreign key defined to do a join across tables. Foreign keys just enforce that all the values in the given column of a table are present in another given column on a specified table (usually a second table). They prevent you from entering data that does not match up.

bbrks2• 7 years ago

A foreign key is a hard-constraint that must be enforced.

There's no stopping you from storing item IDs in a theoretical shopping cart table without FK constraints though. It's just that the onus is on your application to provide the guarantee that the item exists.

This becomes a little clearer if your item IDs are not simply auto-incrementing IDs (which have their own challenges in a large distributed system), but instead are SKUs, or things which have actual meaning.

TimTheTinker2• 7 years ago

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.

aeyes• 7 years ago

There is no difference when not having foreign key constraints.

In both cases you can't delete items from your catalog or you'd break old carts. So you just don't do it.

Another way is to copy the product from the product table to the cart table. This saves you from making references to versions of products.

gamblor956• 7 years ago

An item should never be deleted from a catalog. That would break a lot more than carts.

If you need to track the inventory status/availability of an item, then you store that in the database. The specific implementation (field, list of current items, etc) depends on your needs.

eternalban• 7 years ago

A FK declaration uses named field(s) in your table. You can omit the FK but the associative fields remain. An FK is a constraint on what values can be in those fields.

dguo• 7 years ago

The association would still exist implicitly in the code that uses the database. The columns could stay the same, and the code could still use the item's hypothetical cart_id column to get the items in a cart.

You lose the explicitness and safety of referential integrity in the database, but it's a trade off that can enable other things, like sharding, as mentioned by the GitHub employee.

spookthesunset25• 7 years ago

In what way does sharing make it impossible to have database enforced referential integrity?

emergie• 7 years ago

you are talking about relation, in this context fk is an abstract concept

linked article describes fk as an dbms construct - a schema constraint

relations and conceptual foreign keys may exist without dmbs constraints, in such scenario dmbs does not guard validity of conceptual "foreign keys" and treats them as usual data

for example consider schema:

user{id,name}; book{id,title}; library_borrow{id,user_id,book_id,date}

if you want you may define a constraint:

ALTER TABLE library_borrow ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES user(id) CONSTRAINT fk_library_borrow_user_id;

if you do not define such constraint then dbms will not verify existence of records in `user` table when you insert data in library_borrow

absence of constraints affects also indexes - if you do not define a constraint you usually have to manually define an index on fk column

perlgeek• 7 years ago

In a non-relational model, you'd generally copy the items/products and their quantity into the shopping cart.

That's a huge change from the relational model, and takes much more space than a normalized approach, but it also comes with some advantages. For example, it makes sure that the price of the items in the cart remain constant, even when you change product prices. That way, you don't have to inform the user "oh, your cart just got 8% more expensive" before checkout.

projektfu• 7 years ago

Agreed. Invoices, for example, are a representation of history so they need the exact data that was used at the time. They can have a reference to a product id but at any time it's possible for the data associated with that product to be completely changed from the meaning at the time of the invoice. This happens with, e.g., UPCs, which can be reused. On the other hand, if you have assemblies of products then you probably want those items to update themselves automatically.

adrianmonk• 7 years ago

It's confusion over the terminology. There are two definitions of "foreign key" in wide use:

(1) When one table's key occurs as a value in another table, in that second table that column is sometimes called a foreign key. For example, table A has id, table B has A_id, and people refer to A_id as "a foreign key".

(2) When you define this relationship explicitly in the database, so that the database can enforce it (and/or to document it), that's called a "foreign key constraint" but also sometimes just a "foreign key".

This article just means they don't use #2. It doesn't mean they don't use #1.

The phrasing was probably clearer in the context of the discussion they were having at the time because the comment above it linked some docs for the software in question (qh-ost), and under the "Limitations" section, those docs said, "Foreign key constraints are not supported." So to them it was clear they were talking about database constraints.

wisnesky2• 7 years ago

I agree, whether or not a given data model has foreign keys is a math question with a correct answer (up to isomorphism of the data model); if it does, where in a system they should or should not be enforced is an entirely separate, implementation (vs semantic) level issue.

shayanjavadi• 7 years ago

Genuine question, what are the alternatives?

Litmus23362• 7 years ago

In this case, they use "foreign keys" in the sense that there are keys which correspond to keys in other tables.

There are no foreign key constraints, the keyword is never used.

Hamuko6• 7 years ago

You have columns that refer to the PKs of other tables, just without constraints. So your invoice table would have a column `user_id`, which tells you which user the invoice is for. But if you enter an invoice for user ID 999,999 when you have no users, the database eats it happily, since it has no opinion as to what that data should be.

SamuelAdams• 7 years ago

That's fine when one application is using the database. I work in the enterprise space, and we have at least 5 different applications all working with the same database. Each of these applications has their own team. Expecting each team to handle constraint checking uniformly in their applications isn't always feasible, so we use foreign keys.

GitHub's approach might work fine for small, focused teams who have exclusive control over a product. However, that's not often the reality of most organizations.

spookthesunset25• 7 years ago

That's fine when one application is using the database.

It's not even fine for a single application. Said application has bugs in it. Said application can (and will) crash in unexpected ways that leave the database in an invalid state. And if you think that "oh, that will never happen to me". You just haven't been around long enough. It will happen, every single time. Any system without foreign keys will have corrupt data in it. Said system will have user-visible issues as a result.

Just like only the backend can truly validate user input from a webform, only the database can validate its input.

Mahn2• 7 years ago

There are ways to prevent invalid states in a database without foreign keys, for example enforcing "all or nothing" operations via transactions.

spookthesunset25• 7 years ago

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 with the most rigorous process. The database should always provide tools to keep its self from getting corrupted. This is why good database systems have constraints like FK’s. If your database software makes using those tools painful (cough MySQL), get a better database system.

Not using FK’s is just plain old ignorance.

Litmus23362• 7 years ago

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"

benburleson• 7 years ago

`malloc` won't fail, right?

dkhenry3• 7 years ago

Every time I have seen a database use foreign keys there has been data corruption, because everyone thought foreign keys were declarative and not procedural. Just because you have a foreign key doesn't mean it was always there or that it applied on every transaction. You can turn them off at the connection level and you in fact must turn them off for almost any kind of bulk data load.

You should read shlomi's post he gives good reasons. Specifically this is a github issue on his tool gh-ost which is for online schema migration, and FK's pose lots of problems for online schema migrations.

munk-a7• 7 years ago

I am a bit doubtful of this, especially within PostgreSQL you need to specifically go out of your way to create a NOT VALID constraint. I know that MySQL of old would default to an engine that didn't actually enforce key relationships (which was terrible but at least well documented) but in the modern world DBs will tend toward enforcement unless you specifically work against it.

spookthesunset25• 7 years ago

Maybe shitty toy database systems like MySQL let you disable constraints on a per session basis. A real database system might let you defer them until the end of a transaction—which is the only correct way to operate. Once you commit that transaction, what you put into the system better fucking make sense and it is the job of the database system (and only the database system) to enforce that.

Like I said before if your database system makes using constraints hard or lets you shoot yourself in the foot (lol at disabling constraints per session), don’t just walk but run away from that system.

munk-a7• 7 years ago

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 nice utilities for data consistency enforcement in the form of FKs, these are not the most performant approaches in all cases but they're optimized to be good for nearly all use cases.

If your business need /Requires/ moving off of FKs onto some other data integrity guarantee, then just understand that you're going to be reinventing the wheel. It might turn out to be a better wheel for your car, but it's going to be expensive. And... unless you specifically hire specialized people who comprehend data guarantees and ACID properties, you'll probably do it wrong. This sort of an undertaking is for mature companies only.

spookthesunset25• 7 years ago

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 handing. Good systems have both.

Yes, the application should check it isn’t about to violate a FK constraint. Just like it usually checks that it isn’t about to violate a unique constraint. That way the application can fail in happy, controlled way. But if the application doesnt do the right thing than the DB server still should puke all over the transaction with a “fuck you, don’t feed me bullshit” error.

Just like data entry over the web. The javascript can return all kinds of nice happy messages to the user about missing fields and stuff. But the backend should always validate and enforce correctness even if it’s mode of failure is some ugly “piss off, don’t feed me crap” message.

Just like you should never trust user provided data coming from an HTTP post, a database should never trust the INSERT or UPDATE is valid and won’t corrupt the database. Both backend systems can return mean old ugly errors when shit is bad and let the front end do pre-validation that can do happy nice errors. The backend always has to enforce its own data integrity. Period.

whalesalad3• 7 years ago

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 a transactional manner and a very specific order. So how do you unroll that? You gotta carefully construct the rollback commands or just get rid of the bowling lane bumpers and slash and burn.

So you gotta commit and go all-in. If you make the choice to do it all within the app later, at least you keep that flexibility.

It all depends on your processes and your team. If you don’t design things properly and institute the correct procedures for designing and operating the DB, having or not having constraints at the DB level is inconsequential to the other issues you will face.

spookthesunset25• 7 years ago

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 is valid? No constraints like FK’s is exactly like not validating input because “the JavaScript layer got it”.

It is out of ignorance that people argue otherwise, sorry. Too many people don’t understand relational database...

EugeneOZ• 7 years ago

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.

andrewfromx• 7 years ago

The engineer https://en.wikipedia.org/wiki/Niki_Lauda from Rush movie fame, if he's working on a race-car-level-system where every little bit of performance you can tweak out counts, like wringing a towel dry, every last bit... and in that mindset, FKs are removed NOT from ignorance!

rhinoceraptor2• 7 years ago

I had a job where the database was intentionally lacking FKs so that users could input data out of order, for example create a shipping route Foo that goes to a warehouse Bar, before the warehouse existed. Let's just say it was a suboptimal design.

redact2072• 7 years ago

this just sounds like eventual consistency which can form part of a perfectly optimal design

cryptonector10• 7 years ago

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.

Aeolun3• 7 years ago

The disdain some developers tend to have for their data storage system is pretty unreal. It’s like they see it as a necessary evil instead of their friend.

They often react to FK’s as if they cramp their style, where their style is to just insert whatever into the database without regards for what And where it is.

macspoofing• 7 years ago

At the scale of GitHub, FKs may very well being more problems than benefits. For everyone else, FKs are a benefit.

Iv3• 7 years ago

Thanks. As a mere occasional user of databases I was confused and trying to figure out how you could not use FKs without recreating essentially the same thing in code.

brlewis5• 7 years ago

It may even rely on FK to cascade deletes (shudder)

Is this just the author's personal taste, or is there something about mysql that makes ON DELETE CASCADE a bad idea? In postgresql it's a useful tool for maintaining database consistency.

Hamuko6• 7 years ago

I'm guessing it's the possibility for data loss. So someone accidentally deletes a user and it cascade deletes all invoices referenced to that user (deleting invoices is a big no-no in accounting).

brlewis5• 7 years ago

If the author is thinking of that kind of situation, he's right that ON DELETE CASCADE would be inappropriate, but he's also incredibly wrong to not want a foreign key enforcing that relationship. Deleting a user associated with invoices that should not be deleted is a big no-no too. A foreign key would not let the user be deleted until after the associated invoices are deleted.

There are plenty of other situations where ON DELETE CASCADE is the best option.

spookthesunset25• 7 years ago

These kinds of things are exactly why foreign keys are so helpful to keep things from getting corrupted. Invoices should always reference a user in the user table.

The relationship should not be "ON DELETE CASCADE" but probably "ON DELETE NO ACTION". Then when some junior dev tries to issue a DELETE on the users table, the DB will correctly tell them to get bent and throw an error because there are dangling invoices still associated with the user. Because you are a smart person, you've also prohibited DELETE and UPDATE's against the invoices table for the db user that is used. The application cannot delete the invoice, and it cannot delete the user either--which is the only correct course of action. The database can now protect itself from hostile input from all your junior devs. Exactly why you want FK's. You can't trust user input.

The fact that the author of the article shudders about cascading deletes demonstrates that they have zero authority to speak about databases--something that is very common in the developer community. DB's aren't mysterious black boxes. They are really fucking cool tools that can do all kinds of neat shit to keep your data nice, safe, cozy and warm (they do way more than that too... a good database is perhaps one of the most underutilized tools developers have in their toolbox)

yellowapple7• 7 years ago

Okay, then don't do ON DELETE CASCADE. If you really want to allow the user to be deleted entirely, then ON DELETE SET NULL (or ON DELETE SET DEFAULT pointing to the ID of some "null" user) should do the trick. Otherwise, if you want to retain the customer info (which if you're saving the invoices you almost certainly do), then ON DELETE RESTRICT is what you want. This is all defined in the context of the invoice's schema, mind you, so you'd have to make a conscious effort to cascadedly delete your invoices on user deletion when defining that foreign key constraint.

Use the database to do your bidding. Easier to stay sane that way :)

nwallin3• 7 years ago

I mean, sure, don't use on delete cascade in that case. But there's a big difference between "sometimes bad" and "always bad". The link seems to argue "always bad".

If I have data which much always be deleted if its owner is deleted, what's the process? Manually issue exhaustive delete statements in a transaction?

Is on delete cascade always bad or just a bad default? (It's obviously a bad default, I'm not arguing that.)

geophile2• 7 years ago

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 is woefully incomplete.

Hamuko6• 7 years ago

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 only imagine how many production databases contain incorrect data.

zabzonk• 7 years ago

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

CharlesColeman• 7 years ago

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

geophile2• 7 years ago

What is your point? That database constraints cannot catch all errors, so don’t even bother with the ones they can catch?

jackcviers3• 7 years ago

I thought foreign keys were necessary for the consistency part of ACID compliance. Once you remove the integrity constraint, the database cannot guarantee consistency, even with isolation and atomicity.

Sure, removing the constraint will get you a huge burst in performance, but now you are in charge of guaranteeing consistency outside of transactions in a distributed system involving at least one client and one server. That's a tall order. As Runar Bjarnson put it: "Constraints liberate, liberties constrain,"[1]. If you give something the freedom to be inconsistent at any extreme scale, Murphy's law requires that anything that can go wrong, will go wrong. It is hubris to think that the average developer who hasn't spent years becoming a consistency expert is going to be able to guarantee that data won't become corrupted better than the engineers that build and maintain and acacademics that study rdbms systems full-time.Even experts get this wrong all the time [2]. To all junior devs out there, until you know you need to abandon a guarantee available to you in a given system, exhaust every other possible option.

1. https://youtu.be/GqmsQeSzMdw 2. https://aphyr.com/posts/282-jepsen-postgres

mumblemumble4• 7 years ago

Consistency, in the context of ACID, just means that the integrity constraints that exist are enforced. It does not mean that constraints that don't exist should be enforced.

nwallin3• 7 years ago

My understanding is that it's the other way around. Without ACID you can't guarantee FK correctness. You can't be sure another transaction might add a key to a row you're about to delete.

It's an easy mistake to make though.

muglug• 7 years ago

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

spookthesunset25• 7 years ago

Sorry to hear it!

Mahn2• 7 years ago

This thread makes it sound like it's impossible to make a medium scale application work without FKs constrains. I work on a commercial application that depends on relational relationships without FKs and the number of data corruption cases that we run into on regular basis is zero. Data integrity is handled at the application side, unexpected crashes are accounted for by heavy use of transactions, and everything works just fine.

To be clear, I'm not advocating against the use of foreign keys. But not using them is perfectly doable and not at all what this thread would have you believe.

dekhn4• 7 years ago

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 in SQL and learned one way to do it was to have CHILD nodes with FK references to PARENT nodes. 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.

This always seemed strange and backwards to me and when I did a deeper dive I learned that tree representations in SQL are a rabbit hole of insanity all arranged around referential integrity.

topmonk2• 7 years ago

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?

dekhn4• 7 years ago

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.

josteink• 7 years ago

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.

dekhn4• 7 years ago

I'm not sure what data structure/language environment you mean, but in programming languages, trees are normally represented using pointers (typically from the parent to the child and back).

All of this literally goes back to hierarchical databases which were mostly replaces by relational databases for what appear to be mainly performance and implementation reasons.

camnora• 7 years ago

An alternative is using a nested set https://en.m.wikipedia.org/wiki/Nested_set_model

dekhn4• 7 years ago

"Updating requires renumbering and is therefore expensive".

cryptonector10• 7 years ago

No. Just use an RDBMS that doesn't suck -- one that supports WITH RECURSIVE CTEs.

cryptonector10• 7 years ago

All you need to know is WITH RECURSIVE CTEs.

yellowapple7• 7 years ago

It all came to a head when I naively asked an experienced SQL developer how to represent a tree in SQL and learned one way to do it was to have CHILD nodes with FK references to PARENT nodes. 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.

One far-less-painful solution: use what's called a "closure table" to track the ancestor-descendant relationships not just at the parent-child level, but also grandparent-grandchild, greatgrandparent-greatgrandchild, etc.

For example (assuming Postgres, and eliding some NOT NULL constraints for readability):

    CREATE TABLE node (
        id INTEGER PRIMARY KEY,
        parent_id INTEGER REFERENCES node
        name TEXT
    );
    
    CREATE TABLE node_closure (
        ancestor_id INTEGER REFERENCES node,
        descendant_id INTEGER REFERENCES node,
        depth INTEGER,
        PRIMARY KEY (ancestor_id, descendant_id)
    );

Then, whenever inserting a new node (assuming that Postgres allows specifying integer primary key values on insert, which I don't recall if it restricts by default):

    -- First node
    INSERT INTO node VALUES (0, NULL, "foo");
    INSERT INTO node_closure VALUES
        (0, 0, 0); -- self
    
    -- Second node (child of first node)
    INSERT INTO node VALUES (1, 0, "foobar");
    INSERT INTO node_closure VALUES
        (1, 1, 0), -- self
        (1, 0, 1); -- parent
    
    -- Third node (child of first node; sibling of second node)
    INSERT INTO node VALUES (2, 0, "foobaz");
    INSERT INTO node_closure VALUES
        (2, 2, 0), -- self
        (2, 0, 1); -- parent
    
    -- Fourth node (child of second node, grandchild of first node)
    INSERT INTO node VALUES (3, 1, "foobarbaz");
    INSERT INTO node_closure VALUES
        (3, 3, 0), -- self
        (3, 1, 1), -- parent
        (3, 0, 2); -- grandparent

The upside is that it's now trivial to query for a node and all its descendants:

    SELECT descendant.name
    FROM node AS descendant
    JOIN node_closure ON descendant.id = node_closure.descendant_id
    JOIN node AS ancestor ON ancestor.id = node_closure.ancestor_id
    WHERE ancestor.name = 'foo';

Or more succinctly (if you already know the ID):

    SELECT name FROM node JOIN node_closure ON node.id = node_closure.descendant_id
    WHERE node_closure.ancestor_id = 0;

Either of which gives you:

    ----
    name
    ----
    foo
    foobar
    foobaz
    foobarbaz

As part of this upside, since you're not having to loop through every level of ancestry, reads for deeply-nested descendants are much faster.

The downside is that you have to do extra inserts to an extra table. The inserts themselves can be automated by adding a trigger on node which automatically creates/updates/deletes node_closure rows as necessary (which is why you'd still want the parent_id in the main table: so that the trigger can grab that and build the closure rows, and so that if the closure table gets out-of-sync you can fall back on that and rebuild it), but that still leaves a performance impact on writes (pretty negligible for shallow descendants, but it gets worse for deeper ones).

Personally, I'd opt for a closure table if I know that reads are going to be more common than writes. If writes are more common than reads, then sure, some sort of crazy recursive query might be preferable.

edlebert• 7 years ago

Also, to be noted, Github was a Rails app since long before there was native support for adding foreign keys. I've worked on a lot of large monorails that were created in Rails' early days and none of them have foreign keys.

chase-seibert• 7 years ago

This decision makes sense from the perspective of the person who owns doing database migrations, or who owns the performance of the database.

However, the other 99% of the engineers in the company end up dealing with the fallout of not having FKs -- more bugs, and slower development velocity.

One alternative is to have FKs in smaller, sharded databases, where you don't have to migrate between shards. For example, all the database rows related to one repo could be co-located in a shared with strong FKs between them.

SkyBelow• 7 years ago

I fixed one too many data corruptions to do away with FKs. If we are large enough that FKs are causing performance issues, then we are large enough to invest in and deploy another performance boosting solution while still protecting the integrity of our database from contractors/employees running around with direct SQL access (another nightmare unto itself, but one that I as a developer have less control over than if there are FKs or not).

redact2072• 7 years ago

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.

yellowapple7• 7 years ago

If you ain't testing against the full system state, then I'd be hard-pressed to call that "integration testing".

If you're generating the test data from scratch, then it shouldn't be hard to generate the dependent data while you're at it. If you're testing against (anonymized) production data, then it shouldn't be hard to pull the dependent data while you're at it. In either case, you should be validating the integrity of those data relationships as part of the test criteria.

Ma8ee2• 7 years ago

It isn’t hard, but often very cumbersome. Oh, so you want to test invoicing, for this Customer, which must have a Delivery Address and an Invoicing Adress which both needs valid Postal Codes. And the Customer must have a Contact person. Then we need the Product, that must consist of at least one Article, and each Article must be connected to the Company that we bought it from, with Addresses and Contact Persons and half a dozen other entities. But we have forgotten that the article also need to be in a Category so we know which Sales tax or VAT that needs to applied, for the State or Country of Sale. Then of course, which Sales Person, belonging to a Sales office, with contact person, addresses etc should get credited for the sale. And we still haven’t been able to actually create the order yet, because we haven’t created the delivery options.

I think you get my point. I’ve easily used more than a full day just to get enough data in a naked system to make just the simplest test. I’m very grateful for tools like tsqlt that make unit testing possible (by temporarily turning of FKs)

malvosenior2• 7 years ago

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.

tomlu• 7 years ago

Github is probably past the stage of premature optimization.

Presume (for argument's sake) that we accept the premise that foreign keys do not scale. Then the question is whether there exists some sort of realistic migration strategy once usage grows past that. If it does, then using FK makes total sense until we hit that point, doesn't it? And if true this should apply to 99% of users.

But if there exists no such migration strategy, then maybe the guy has a point?

malvosenior2• 7 years ago

In all of my years of development, I’ve never seen data that can’t be migrated to a new system. I’m not even sure what that would mean tbh.

devit• 7 years ago

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

evanelias11• 7 years ago

The performance impact is at write time, and having the data cached in memory is irrelevant. It's the locking overhead that's non-trivial, especially if each table has several FKs, when operating in a high-volume OLTP environment.

As for "database engines that properly support online schema changes without locking, downtime or table renaming hacks", please name some. In my experience, every major DBMS has cases where certain ALTERs block concurrent DML, which is extremely problematic on very large tables. Or even cases where there's no locking, but the operation still takes a very long time, which is conceptually problematic for logical replication.

anshul008• 7 years ago

The performance of my team's application at Myntra (Bangalore) was badly affected by foreign keys on MySQL and we decided to drop them.

The trick we used was to drop them only in production, not in test environments. In test environments they acted as guardrails to ensure that our application did not break the constraint.

So in a way it gave us best of both worlds

baby• 7 years ago

Never used them in my personal projects either eventhough I learned about them in school. It just didn’t make sense to me, why would I constrain myself in the database when I could manage all of this in the application logic?

And if you want to experiment with different schema it’s just a nightmare.

wwarner• 7 years ago

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.

gigatexal• 7 years ago

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

uses• 7 years ago

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?

dylanz• 7 years ago

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.

tiglionabbit• 7 years ago

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

randomidiot123• 7 years ago

It's definitely asking for trouble. Murphy's Law.

sarah180• 7 years ago

"We do not use foreign workers" (2019) (gitlab.com)

(Yes, I know it's more nuanced than that.)

postit• 7 years ago

I'm working with a company that also refuses to use FK and indexes.

We have a huge DB cluster instead.

nwallin3• 7 years ago

I'm working with a company that also refuses to use [...] indexes.

... ?

MadWombat• 7 years ago

FKs are a performance impact. The fact they require indexes is likely fine, since those indexes are needed anyhow. But the lookup made for each insert/delete is an overhead.

You have to check referential integrity somewhere. You can do it in the application or you can let the RDBMS do it for you, but it will have to happen somewhere. So the overhead argument is kind of bullshit. Yes, managing the integrity in the app might provide more flexibility, but it comes with a price of, well, having to do your own integrity checking and introducing more bug space into your code.

FKs don't work well with online schema migrations. ... Doing schema migration of P is just not going to work. Recall that gh-ost renames the table at the end.

So basically, the problem is not foreign keys, the problem is gh-ost implementation. Since they don't use FKs, they implemented their migrations in such a way as to make it impossible to support foreign keys.

Yes, in the parent-child example, to migrate the parent you would have to migrate the child. But it is not an impossible thing to do. Create the new parent, import the data, map the record ids from the old parent to the new parent, create new child, drop the foreign key, import the data, update the FK field using the map and create a new FK. Rename the tables and you are done.

When eventually you want to shard or extract data out, you need to change & test the app to an unknown extent.

Sharding your data is not something you do on a whim. And you will need to test your app. But you were going to test your app anyway, weren't you? Or did you think that you could split your database into multiple databases without any testing? As for unknown extent, that is kinda bullshit. Look for places that catch integrity exceptions. Thats what you need to look at. Look at your database schema and if any of your tables are "ON CASCADE DELETE", go and punch your DBA in the face. If you are the DBA you might want to start running before someone else finds out.

cryptonector10• 7 years ago

> FKs are a performance impact. The fact they require indexes is likely fine, since those indexes are needed anyhow. But the lookup made for each insert/delete is an overhead. You have to check referential integrity somewhere. You can do it in the application or you can let the RDBMS do it for you, but it will have to happen somewhere. So the overhead argument is kind of bullshit. Yes, managing the integrity in the app might provide more flexibility, but it comes with a price of, well, having to do your own integrity checking and introducing more bug space into your code.

Perhaps they meant "latency" rather than "performance". With eventual consistency you can have lower latency, and spend more cycles fixing referential integrity later.

Of course, sometimes things fail in eventually-consistent systems (e.g., you order some item from a vendor who claims to have stock but oops! they don't, so your order will take a few more days to ship than they claimed and now you're a bit sad but hey, it all works out in the end, right?).

bfrog• 7 years ago

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

spookthesunset25• 7 years ago

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.

daveisfera• 7 years ago

FKs are a tool. Sometimes it's great to use a hammer for a nail, but sometimes a screwdriver just seems to work better when working with screws.

desc• 7 years ago

Er, what? Foreign keys are not necessarily a net performance cost. Depending on the scale of the database, they can be essential for fast queries.

ctataryn• 7 years ago

While we’re at it: should databases handle transactions or should we let the application deal with it all?

IMHO The answer is the same for this as with foreign keys.

cryptonector10• 7 years ago

Ahh, but what is your answer to both of those then?

kenshaw• 7 years ago

Foreign keys should definitely be used in most schema designs. While I can agree that they are problematic when doing a schema migration, a schema migration would only happen "very rarely" whereas inserts/deletes happen regularly. I believe this comment is more of a reflection of the specific database (MySQL) which has relatively poor FK performance and adjunct issues when compared to other SQL databases.

Also, from a process perspective of migrating a schema, the ideal mechanism would be to incrementally copy from the existing schema to the new schema (meaning there is no breakage in the interim). Granted (and somewhat obviously) for databases as large as what GitHub is likely using, this is not possible -- and so a "full migration" at once is their only option. However, part of that "full migration", to me, would be to _remove_ the original foreign keys from the schema, rename/delete/alter tables/fields/indices, and then after all of that is complete, as a final step, re-add referential integrity (ie, foreign keys).

I'd specifically like to point out the following:

1) This comment gives advice about all SQL databases based on the conclusion / operation of only one database (MySQL), whereas the same base facts do not apply to others (eg, PostgreSQL, Microsoft SQL Server, ...), and should not be used to evaluate whether using FKs makes sense for other databases or not.

2) Schema migrations should be done rarely, if ever. A better approach would have been to start with a schema design that was less prone to _needing_ change. Regardless, even if schema migrations are done with _regularity_, that frequency would still be orders of magnitude less regular than, eg, code changes or CRUD operations. As such, throwing away FKs simply to accommodate either a poor or non-thoroughly thought-out schema is tossing the baby with the bathwater, especially since the purpose of FKs is to reduce the ability of other tiers in the application from losing or otherwise corrupting the structured data storage.

3) FKs are useful when doing CRUD operations. They are not useful during schema changes. As such, the proper process in this case would be to temporarily remove FKs, perform the schema migration, and then re-institute the FKs. This may not always be possible, due to, eg, active business logic components or some such that can't allow the database to be taken offline to perform a migration (see point #2). In that instance, there are a number of other mitigations that can be put in place in the logic tiers, such as being able to recognize different schema revisions, and deploying that _prior_ to doing a schema change. As such, one would first migrate an active "smart" logic tier that can recognize different schema revisions, and allow for incremental migration of schemas without enacting a cluster-wide lock/disabling of reads/writes.

cryptonector10• 7 years ago

2b) And keep your schema changes as incremental and backwards-compatible as possible. Add new tables, add columns to tables, but don't drop or rename anything. If you need a flag day, in PG terms just setup a new schema, use triggers to sync the two, and migrate your apps to use the new schema -- when they're all migrated, drop the sync triggers and the old schema.

barrkel7• 7 years ago

Enable FKs for test and development, disable them in production, for tables that see billions of rows.

FKs have other downsides. They add locks on the referenced rows, to ensure they don't disappear before the transaction commits. If you start out with 32-bit primary keys, you have a major pain when you go over 2 billion records - incrementally upgrading all your FKs before your PK breaks your referential integrity, since FKs generally need to have the same type on each side.

ken• 7 years ago

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.

nickpeterson• 7 years ago

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.

jerzyt2• 7 years ago

Granted, I have never worked with a database the size of Github, so I've never had to deal with sharding issues. But, even though foreign keys can be a PITA in some situations, I prefer the DBMS to do it for me than rely on the application. Foreign keys have always kept my back. I've been burned a few times when they weren't used.

wisnesky2• 7 years ago

Many people on this thread have asked if there is a rigorous way to migrate data between relational schemas that contain non-trivial foreign keys, and the answer is yes: In the open-source categorical query language CQL, data migrations necessarily respect foreign keys - a property guaranteed by the mathematics of category theory and the automated theorem prover that ships with CQL. http://categoricaldata.net

rukuu001• 7 years ago

Let's just consider some of the 'non-technical' issues surrounding the use of foreign keys.

I've encountered resistance because

- Using them would require up-skilling existing devs

- They'd highlight crap data coming in from other parts of the org, which would be politically uncomfortable

- DB would need to be re-configured

- We're agile now and we can't be locked into those kind of constraints

edit: formatting

jayd162• 7 years ago

I'm curious what these comments think they're saying. What effort went into GitHub such that they feel they can make the claim that FKs are an anti pattern. Do they think they're just better than everyone else or is there some other practice that makes this claim more interesting than simple hubris? Me a

CGamesPlay• 7 years ago

Oh man, that migrations point. I remember at my last company needing to handle this. We had some (huge) read-only tables that were referenced by other tables and we needed up update the read-only tables in batches from raw mysql dumps. The keys in the dumps were stable, so we could swap out the table and the references would still be valid. Since the referenced tables were huge, they took a long time to upload the batches, and we needed a migration that was ACID and fast.

The process ended up looking like this:

1. Create a `foo_load` and a `foo_unload` database, dropping any that already exist.

2. Populate `foo_load` with all of my new data.

4. Rename `foo.table` to `foo_unload.table` and `foo_load.table` to `foo.table`. This is atomic, fast, and updates references to point to `foo_unload.table`.

At this point the application reads from the new data, but referential integrity checks still verify against the old data.

5. Update each reference to `foo_unload.table` to point to `foo.table` using ALTER TABLE DROP FOREIGN KEY, ADD FOREIGN KEY. Again this is atomic and runs quickly.

6. Drop the `foo_unload` database.

Now this process is slow, but the switchover is 2 atomic steps and requires locking the tables for only a few milliseconds. The process is safe as well, if it crashes at any step, running the script again will cause it to safely recover. Well, with one more step zero:

0. If `foo_unload` exists, do the same rename step as step 5.

This solution worked for us for a few years. Eventually we swapped out the whole architecture around these tables and ended up with a more traditional ETL flow that didn't involve moving round huge mysql dumps.

doctor_eval• 7 years ago

I had a database with no RI and it was a nightmare, filled with bugs.

So we added RI and it was awesome, and the bugs did go away with time.

But then we became dogmatic and had to have RI for everything, including things like maintaining local RI lookup tables for non local data. This just bogged us down so much that we couldn’t move.

Today, we use RI strictly within a single database. For foreign keys to remote databases, we assume the remote database knows what it’s talking about and that the incoming data has already been checked.

This works really well for us and in particular sharding is not a problem (sharding being a design decision not an implementation decision IMO)

Someone said elsewhere that Rails does the RI so the DB doesn’t need to. I don’t use Rails, and I don’t bet, but I’d wager that it’s faster to do RI inside Postgres than inside Rails.

The idea that we should throw out ALL RI is just dogmatic nonsense. I should know :)

mceachen• 7 years ago

Rails in 2010 was just coming around to adopting foreign keys, and the rubygem Foreigner made it happen before it was a proper rails feature.

The idea of rails enforcing unique constraints and FKs back then was... Optimistic. The design constraints assumed exactly one rails instance would access the database at any given time to ensure consistency.

Not a great assumption.

megapatch• 7 years ago

Confusing. Foreign keys are a vital part in RDBMS and very useful. He is talking about foreign key constraints, which can be problematic as described.

arpa• 7 years ago

Last time a DBA suggested me dropping foreigb keys for performance reasons, i suggested him to write code ensuring data consistency with distributed parallel writes. Haven't heard anything about FKs from him since.

hootbootscoot5• 7 years ago

while most rdbms-based projects will continue using foreign key constraints (and all will be referentially cast in stone), and postgressives will enjoy 100000x query speedups by remembering to include your hornsnaggle_tree_index_lookup_speeder_upper index,

there is a certain fanatical mindset from the rdbms world that sounds a bit like:

never forget always include jquery with every web app you make. it's made by real profeshunalz who know more than you and lots of people already included it and you should simply just learn to accept that all front-end web problems have already all been solved by jqueremy and you should simply refactor it all in terms of jqueremy. anything else is korrupshun. In other words, always only ever consider the same tools and techniques. LOL

A mere search for other database types produces pages upon pages of "why sql is beating nosql" and all stack overflow answers always doom you to the 7th level of hell for anything but 3rd normalized form. hey let's all use lookup tables for many to many. it's a cult. it's nuts. anyone who has spent any amount of time with database-backed applications is surely aware of the tradeoffs they are making when choosing non-relational databases of any kind.

The obsession with forcing every all all use cases into the rdbms mindset makes me say "well then, let's just flatten everything even further into mapped key-value a la ohm." in which one encodes the key with path info and the value with the value, etc.

if the act of suffering for the sake of suffering and breaking things into iddy bidd y pieces is virtuous in and of itself, surely this is a more true pure and rigorous religion to bend every single thing to.

(before smarty pants says "um, querying" may I remind you of adding another entire secondary index of any kind via adding it with the id of the first key as it's value.)

I'm not advocating everyone do this. no freakouts pls.

ideally we would just persist our objects, which is coming with persistent memory, but i digress...

This Lance fellow seems to have taken plenty of "you have no idea what you are talking about moron!" abuse, despite having a phd in computation theory and writing erp software since the dinosaurs roamed the earth. (I'm also not advocating his solutions for any and all problems, but not taking his ideas seriously is a mistake IMO) https://codeburst.io/databases-the-future-buggy-whips-of-sof...

https://codeburst.io/doing-without-databases-in-the-21st-cen...

mongodb is an incredibly powerful tool for when you want the whole json, all the json, and packed as bson. When you want to store/dish out blobs and not join nor have no use for the parts separately, then you can do that nicely.

(besides this usual story about "for when you don't know your schema or have lots of irregular semi-structured data, stripe, your mailgun webhooks, whatever. It's a great DB, for some things, despite the people saying "since it didn't do this one thing i really obsess over and think all dbs should always do that therefore its' crap"

(well, copying the same master-slave with single-write master failover or vertically scale story as the rdbms people was probably a weak point...)

pg as a jsonb store with secondary indices on actual columns LOL...

I guess we just basically need to accept that the same tools can be used as primitives/components in different contexts in terms of the actual application.

Some people use Redis as a primary datastore.

zealotry is best avoided, I guess..

look, this silo-ing stuff is also silly, and a product of the corporate dysfunctional mentality: split everyone up and get DBA's warring with Devs...

hootbootscoot5• 7 years ago

http://www.dbta.com/Columns/Database-Elaborations/The-Refere...

hootbootscoot5• 7 years ago

oh, I also forgot to add that many applications mix-n-match data components to create a data layer with a standard API that then may then be implemented with various backends for various purposes, each being applications in their own right, etc.

this is seldom discussed in such debates but is actually quite common for many applications.

We do not use foreign keys (2016)

Related Stories