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

291–300 of 337 posts

Re: We do not use foreign keys (2016)

#291

Post by js4ever »

jerzyt wrote: post #244
spookthesunset wrote: post #221

Earlier quoted context omitted.

Then you have an implicit rule that is enforced by a hope and a prayer that some junior dev never makes a mistake, your senior engineers are clairvoyant and understand every single aspect of your systems 100% with zero off-days, your code review process catches every single possible edge case (especially the edge cases that you never knew existed), your QA process is 100% and never makes mistakes, your servers never…

That's exactly my experience and viewpoint.

Mine is exactly the opposite, most use cases I'm exposed to do not require FK at all, so I simply never use them.

Re: We do not use foreign keys (2016)

#292

Post by blihp »

adrianN wrote: post #277
megablast wrote: post #274

Earlier quoted context omitted.

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

That's often more what you'd bring management consultants in for. A technology consultant is more likely just to tell you to dump whatever tech you are using in favor of whatever the flavor of the month is.

Re: We do not use foreign keys (2016)

#293

Post by barrkel »

spookthesunset wrote: post #228
barrkel wrote: post #212

Earlier quoted context omitted.

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.

This sounds like an implementation specific detail (no doubt for mysql) that for all you know has been fixed in later versions but gets passed around as if it is a permeant truth that applies to all database servers.

FK consistency can't be guaranteed without ensuring the referenced rows don't disappear before the transaction has been committed. Think about it.

Re: We do not use foreign keys (2016)

#294

Post by Xylakant »

Iv wrote: post #286
megablast wrote: post #274

Earlier quoted context omitted.

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

As a consultant I have never been hired by someone whose system was working. I am willing to bet the consultant was hired to add a feature, could not figure the installed code, and proposed to redo it the only way he or she was used to.

I have been hired a few times to review working systems. Basically get an external set of eyes on specific things. And “yes, this all looks good, you might want to tweak a little here and keep an eye on that once you grow significantly.” is an entirely accepted outcome of such reviews.

Re: We do not use foreign keys (2016)

#295

Post by Hamuko »

yellowapple wrote: post #219
Hamuko wrote: post #151

Earlier quoted context omitted.

I'm not sure if I understand.

If you're finalizing the invoice, you're hopefully doing something like this, right? UPDATE invoices SET final = TRUE, invoice_number = @InvoiceNumber WHERE id = @InvoiceId; (Where @InvoiceNumber is some variable the application's substituting into the query) If so, then the problem you present should never happen (unless the DB doesn't do atomic updates by default, but wrapping the update in a transaction should pro…

>you're hopefully doing something like this, right?

Well, in an ideal case, yes, you would be doing it like that. The reality might be different, especially when using an ORM.

>Of course, I'd also be wondering why an invoice would ever exist without an invoice number

Draft invoices do not have an invoice number, since they don't really exist anywhere. You can delete a draft invoice and nothing has happened. But if you have an invoice number, that's a record that must be kept.

Re: We do not use foreign keys (2016)

#296

Post by arpa »

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.

Re: We do not use foreign keys (2016)

#297

Post by JoshuaDavid »

hrktb wrote: post #289
joevandyk wrote: post #258

Earlier quoted context omitted.

PostgreSQL supports deferring constraints. https://begriffs.com/posts/2017-08-27-deferrable-sql-constra...

This looks nice, it’s still limit to a single commit though. That’s where it’s a PITA for anything that won’t (or we don’t want to) fit a single commit. In particular splitting commits allows to ingest data in parralel (for instance if we import stores and store owners, both could be ingested separately without caring at first if each references a valid entity)

At least with mysql, and probably with postgres as well, you can temporarily turn off foreign key checks for a set of statements. So you can still get the benefits of foreign key constraints by default but when they do more harm than good you can turn them off. With the added benefit that turning off FK constraints screams "I am doing something unusual and dangerous - this requires extra caution."

Re: We do not use foreign keys (2016)

#298

Post by jeltz »

barrkel wrote: post #212
munk-a wrote: post #83

Earlier quoted context omitted.

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

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.

These sound like implementation details. In PostgreSQL FKs just add a shared write lock to the foreign key columns of the referenced table (i.e. the locks prevent anyone from updating the primary key of the referenced row). Also what you said about 32-bits is not true in PostgreSQL either as far as I can remember.

The costs of having FKs in PostreSQL are:

1) If you update the primary key of the referenced table you might see issues with locking. But this is rare in real world applications.

2) Performance overhead from having to check all FKs and taking the locks. This can be a big issue on some workloads and may force you to add extra indexes. A PostgreSQL specific issue is that FK checks cannot be done in batches.

3) Adding new FKs block writes from the referenced table and dropping FKs lock out both readers and writers from the referenced table. This is a limitation of the implementation.

Re: We do not use foreign keys (2016)

#299

Post by jeltz »

barrkel wrote: post #293
spookthesunset wrote: post #228

Earlier quoted context omitted.

This sounds like an implementation specific detail (no doubt for mysql) that for all you know has been fixed in later versions but gets passed around as if it is a permeant truth that applies to all database servers.

FK consistency can't be guaranteed without ensuring the referenced rows don't disappear before the transaction has been committed. Think about it.

Yes, but it can be done without locking out reads. The only thing you need to lock against is someone changing the primary key of the referenced row or deleting the row.

PostgreSQL has implemented this minimum level of necessary locking for quite many years now.

Re: We do not use foreign keys (2016)

#300

Post by malvosenior »

tomlu wrote: post #283
malvosenior wrote: post #98

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.

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

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.
Post reply on HN