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

Re: We do not use foreign keys (2016)

#271

Post by zzzeek » Sat, Nov 09, 2019, 4:57 AM UTC

Diggsey wrote: post #121

spookthesunset wrote: post #46

Earlier quoted context omitted.

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.

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.

if you are avoiding the use of foreign keys for the high and lofty goal of "performance" and 100% uptime while you run your fancy online migration utilities, SERIALIZABLE isolation is the last thing you would be using as it does what it says, serialiazes transactions and locks things like crazy. lots of waiting on locks, lots of deadlock potential. Throw in InnoDB's quirky implementations of isolation levels and you'd be in for a world of fun.

Re: We do not use foreign keys (2016)

#272

Post by Mahn » Sat, Nov 09, 2019, 5:19 AM UTC

spookthesunset wrote: post #145

SamuelAdams wrote: post #26

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

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

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

Re: We do not use foreign keys (2016)

#273

Post by Mahn » Sat, Nov 09, 2019, 5:37 AM UTC

muglug wrote: post #53

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

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.

Re: We do not use foreign keys (2016)

#274

Post by megablast » Sat, Nov 09, 2019, 5:38 AM UTC

whack wrote: post #193

taurath wrote: post #25

Earlier quoted context omitted.

In the same vein, I'd like to remind people that you are probably not a "temporarily low-scale big-data company", in the same vein as a temporarily embarrassed millionaire. In lots of cases going for the very long term scalable solution will be an impediment to your growth, and I'd suggest dealing with those issues when the chance that you need them is on the horizon, rather than across the globe. CQRS is one of the…

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

> At some point, I left the project, and my co-founder hired an expensive consultant to review the system and provide feedback.

People get pretty unhappy if they hire a consultant and don't get some drastic change recommendations. "Everything is good" doesn't sit well when handing over cash.

Re: We do not use foreign keys (2016)

#275

Post by taurath » Sat, Nov 09, 2019, 5:42 AM UTC

Quekid5 wrote: post #215

corebit wrote: post #71

Earlier quoted context omitted.

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

Like you, I've found the general idea of CQRS/ES[0] incredibly valuable because it both enforces a functional approach to state (e.g. 'current state' is a fold over events), and it forces people to really think about the domain. (E.g. which bits are really atomic units, or what can be fixed up if it goes wrong in some way.) It also forces people to think about something that's usually glossed over: Consistency. If yo…

I’ll tell you this - it decreases it, especially for small teams. You have to do a lot of work to make your query engine actually good and reliable, and reporting is also a huge bear to deal with. You have to cache somewhere to get your queries anywhere near real-time, and it takes a lot of reinvention.

Re: We do not use foreign keys (2016)

#276

Post by taurath » Sat, Nov 09, 2019, 5:45 AM UTC

hinkley wrote: post #227

taurath wrote: post #25

Earlier quoted context omitted.

In the same vein, I'd like to remind people that you are probably not a "temporarily low-scale big-data company", in the same vein as a temporarily embarrassed millionaire. In lots of cases going for the very long term scalable solution will be an impediment to your growth, and I'd suggest dealing with those issues when the chance that you need them is on the horizon, rather than across the globe. CQRS is one of the…

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

If your requests come in hard and fast then you’ll have a big problem with tech debt, but if you can schedule it properly you can do it as a part of adding functionality and people tend to be happy, because once the functionality is in it can be enhanced much more quickly.

Re: We do not use foreign keys (2016)

#277

Post by adrianN » Sat, Nov 09, 2019, 5:55 AM UTC

megablast wrote: post #274

whack wrote: post #193

Earlier quoted context omitted.

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

> At some point, I left the project, and my co-founder hired an expensive consultant to review the system and provide feedback. People get pretty unhappy if they hire a consultant and don't get some drastic change recommendations. "Everything is good" doesn't sit well when handing over cash.

I thought people hire consultants so that they can sell unpopular changes as recommendations from an external authority instead of letting the blame hit management directly.

Re: We do not use foreign keys (2016)

#278

Post by doctor_eval » Sat, Nov 09, 2019, 5:55 AM UTC

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

Re: We do not use foreign keys (2016)

#279

Post by thaumasiotes » Sat, Nov 09, 2019, 6:05 AM UTC

megablast wrote: post #274

whack wrote: post #193

Earlier quoted context omitted.

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

> At some point, I left the project, and my co-founder hired an expensive consultant to review the system and provide feedback. People get pretty unhappy if they hire a consultant and don't get some drastic change recommendations. "Everything is good" doesn't sit well when handing over cash.

> People get pretty unhappy if they hire a consultant and don't get some drastic change recommendations. "Everything is good" doesn't sit well when handing over cash.

I suggest that this is largely untrue when the consultant being hired is a security consultant.

There is demand out there for security consultants who will rubber-stamp your existing software.

Re: We do not use foreign keys (2016)

#280

Post by mceachen » Sat, Nov 09, 2019, 6:40 AM UTC

doctor_eval wrote: post #278

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

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.