Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We do not use foreign keys (2016)

github.com

Post reply on HN
Print view View original

191–200 of 337 posts

Re: We do not use foreign keys (2016)

#191

Post by papln »

rhinoceraptor wrote: post #135
spookthesunset wrote: post #62

Earlier quoted context omitted.

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

> MySQL What other database has been used at such a giant scale at so many companies, than MySQL? I'm sure Oracle and SQL Server are used at big companies, but nowhere near Facebook scale.

You aren't Facebook. Facebook has put massive work into adding layers on top of MySQL that your startup has not.

Also, you seem to be forgetting PostgreSQL

Re: We do not use foreign keys (2016)

#193

Post by whack »

taurath wrote: post #25
conroy wrote: post #5

When posts like these come up, I'd like to remind people that context matters when making technical decisions. What works for large companies with huge scale (GitHub, Google, Facebook) may not work for you. As a counter point to the linked issue, I operate a few small applications. Foreign-keys (and constraints in general) are great at ensuring that invalid data doesn't find its way into your database. Yes, they have…

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

Re: We do not use foreign keys (2016)

#194

Post by evanelias »

devit wrote: post #100

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

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.

Re: We do not use foreign keys (2016)

#195

Post by cryptonector »

hesk wrote: post #2

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?

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.

Re: We do not use foreign keys (2016)

#196

Post by papln »

SkyPuncher wrote: post #124
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.

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.

NoSQL got popular because Google needed it for their billion-user planet-scale system, and then a million startups pretended they were Google. As soon as Google built their planet-scale NoSQL system, they immediately started replacing it with a planet-scale SQL system (Spanner)

Re: We do not use foreign keys (2016)

#197

Post by cryptonector »

MadWombat wrote: post #147

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

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

Re: We do not use foreign keys (2016)

#198

Post by Aeolun »

spookthesunset wrote: post #150
greggyb wrote: post #33

Earlier quoted context omitted.

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

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

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

Re: We do not use foreign keys (2016)

#199

Post by cryptonector »

fdr wrote: post #167
Hamuko wrote: post #72

Earlier quoted context omitted.

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

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

I don't understand why anyone would choose MySQL over PG, honestly.

Re: We do not use foreign keys (2016)

#200

Post by [deleted] »

spookthesunset wrote: post #81
munk-a wrote: post #49

Earlier quoted context omitted.

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…

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

[deleted]
Post reply on HN