We do not use foreign keys (2016)

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.

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.

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

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.

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

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.

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.

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.

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

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

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

I can only imagine how many production databases contain incorrect data.

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.

MySQL does not support transactional DDL, unfortunately.

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

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.

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.

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

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

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.

The items table might have a shopping_cart_id column

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.

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.

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

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?

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.

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.

It's an easy mistake to make though.

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.

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.

It's just an index lookup, so what? You'd rather mash all the data into a single parent field, is that it?

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)
    );
    

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

    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';

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

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

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.

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.

... ?

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.

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

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