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

Do you really need foreign keys?

shayon.dev

Post reply on HN
Print view View original

101–110 of 179 posts

Re: Do you really need foreign keys?

#101

Post by DasIch »

eddd-ddde wrote: post #71
arrowleaf wrote: post #66

Earlier quoted context omitted.

Have a single application clearly owning the data. All access to the data is done through that application. Why would you _want_ multiple applications deciding how to handle data in a common store? What about access authorization to sensitive data?

Because that single application already exists, in my case its postgres. Pgweb is one of my interfaces, admin dashboard for free, and I am sure the changes I make there are as valid as changes through any other interface. I can implement a website as a SSR app that talks directly to the db. Maybe tomorrow I decide I need to work on a web scraper that will use python, instead of adding more API endpoints to allow the…

If you operate at the scale where you consider the performance implications of foreign keys, you probably are not ok with anyone accessing the database or running just any query against it.

It is not realistic that you can trust everyone who needs to access the data with access to the database as they might easily cause problems with poorly written queries.

Additionally you may want invariants maintained, that the database cannot maintain but an application in front of it can.

Also for historical data or analytical queries postgres is not ideal either, so you probably want to move the data into some OLAP database or datalake.

> I can implement a website as a SSR app that talks directly to the db. Maybe tomorrow I decide I need to work on a web scraper that will use python, instead of adding more API endpoints to allow the scraper to talk to the database, I just talk to the database...

If the website and the scraper are just parts of the same application, it makes sense to do this but if they are genuinely different applications. I would use different databases here.

Re: Do you really need foreign keys?

#102

Post by jrumbut »

eddd-ddde wrote: post #71
arrowleaf wrote: post #66

Earlier quoted context omitted.

Have a single application clearly owning the data. All access to the data is done through that application. Why would you _want_ multiple applications deciding how to handle data in a common store? What about access authorization to sensitive data?

Because that single application already exists, in my case its postgres. Pgweb is one of my interfaces, admin dashboard for free, and I am sure the changes I make there are as valid as changes through any other interface. I can implement a website as a SSR app that talks directly to the db. Maybe tomorrow I decide I need to work on a web scraper that will use python, instead of adding more API endpoints to allow the…

> Because that single application already exists, in my case its postgres.

This is exactly what postgres was designed for! Tens of thousands of hours of work over decades to solve the problem of relational database management. That's why we call it an RDBMS!

Which isn't to say you shouldn't make your own API ever. There are a lot of situations where you don't want things to connect directly to postgres.

But you shouldn't be afraid of having multiple systems connect to postgres. It has incredibly mature and robust features to accommodate that use case. It's the expected use case.

Re: Do you really need foreign keys?

#103

Post by richardwhiuk »

brlewis wrote: post #51
lrobinovitch wrote: post #14

This github issue is often linked when this topic is discussed: https://github.com/github/gh-ost/issues/331 > Personally, it took me quite a few years to make up my mind about whether foreign keys are good or evil, and for the past 3 years I'm in the unchanging strong opinion that foreign keys should not be used. Main reasons are: > * FKs are in your way to shard your database. Your app is accustomed to rely on FK to…

Note that this was written in 2016 in the context of a mysql-centric project. You will not find an "unchanging strong opinion that foreign keys should not be used" outside that context. I haven't kept up with mysql enough to know if there are still good reasons to avoid foreign keys. I just stick with postgresql.

sharding is still a big problem for foreign keys

Re: Do you really need foreign keys?

#104

Post by ch4s3 »

epicfeedback wrote: post #88
Pet_Ant wrote: post #83

Earlier quoted context omitted.

> probably not with the same ORM or even the same language I mean, they shouldn't? Like you've just identified a bug: another application can access your database. If another department needs your data, they should request an endpoint that you control. You should be using an "application database"[1] not an "integration database"[2]. [1] https://martinfowler.com/bliki/ApplicationDatabase.html [2] https://martinfowler…

They are you. You is they. It’s one company with one goal — keep companying. The idea that ever “department” should access every other department’s data through some bespoke interface that the latter department maintains might work at some corporate behemoth, but at almost all other scales is absurd.

This would be a total waste of effort when you need to be building a product and iterating. I hate articles like this because they do a poor job contextualizing the tradeoffs and when it might be appropriate to do the weird exceptional thing.

IMHO if you have a performance critical case when foreign keys are in the way, load THAT data into an in memory DB on a recurring basis and server time sensitive requests from there.

Re: Do you really need foreign keys?

#105

Post by winnebago »

Pet_Ant wrote: post #84
mavelikara wrote: post #64

Earlier quoted context omitted.

+1. When I started in the industry, it was common for more experienced developers to drill the “data outlives the application that generated it” principle into you. Somewhere in the transition to NoSQL and back we lost this.

The _data_ yes, the _database_ no. The data will get migrated to different solutions at different times. I've lost track of how often I've been porting MySQL to SQL Server, SQL Server to Postgres, Postgres to Mongo, Mongo to cloud etc. EDIT: fixed capitalization of "PostGres"

This really depends on the size of your data set. If you have a large database (TBs+) it's likely to be very long-lived due the effort/hardware resources a migration requires - especially if you want to improve the schema when migrating.

Re: Do you really need foreign keys?

#106

Post by butlerm »

goostavos wrote: post #32
erik_seaberg wrote: post #27

Earlier quoted context omitted.

Whenever anyone talks about “the application,” I immediately ask “what about all the other applications?” I promise you that you will find customer service and accounting and biz dev have also built stuff that uses the database to get their jobs done (probably not with the same ORM or even the same language) unless you have taken draconian measures to prevent them.

I would argue that if you have completely different contexts / business lines / concerns (customer service, accounting, biz dev) all directly accessing the same database you have far, far larger architectural concerns that FK will ever hope to address.

Large ERP systems do that sort of thing as a matter of course and have for decades now. It does require careful planning and design. I mean AR / AP / scheduling / manufacturing / inventory and so on.

The main downside of splitting everything into isolated databases is that it makes it approximately impossible to generate reports that require joining across databases. Not without writing new and relatively complex application code to do what used to require a simple SQL query to accomplish anyway.

Of course if you have the sort of business with scalability problems that require abandoning or restructuring your database on a regular basis, then placing that kind of data in a shared database is probably not such a great idea.

It should also be said that common web APIs as a programming technique are much harder to use and implement reliably due to the data marshalling and extra error handling code required than just about any system of queries or stored procedures against a conventional database. The need to page is perverse, for example.

That does not mean that sort of tight coupling is appropriate in many cases, but it is (typically) much easier to implement. Web APIs could use standard support for two phase commit and internally paged queries that preserve some semblance of consistency. The problem is that stateless architecture makes that sort of thing virtually impossible. Who knows which rows will disappear when you query for page two because the positions of all of your records have just shifted? Or which parts of a distributed transaction will still be there if anything goes wrong?

Re: Do you really need foreign keys?

#107

Post by jrumbut »

dasil003 wrote: post #100
butlerm wrote: post #94

Earlier quoted context omitted.

If you do not particularly care about performance or have a great deal of headroom then database enforcement of referential integrity is great. Alternatively you could just write test cases to check for it and not pay the severe performance penalty. The other major downside of database enforcement of referential integrity is the common need to drop and re-create foreign keys during database schema upgrades and data c…

"just write test cases to check for [referential integrity]" is doing some awful heavy lifting in this comment. Assuming a standard n-tier application architecture, how do you guarantee the test prevents race conditions?

You either end up reinventing foreign keys, your support volume will scale faster than your data, or user experience will suffer.

There may be situations where foreign keys become too much overhead, but it's worth fighting to keep them as long as possible. Data integrity only becomes more important at scale. Every orphaned record is a support ticket, lost sale, etc.

Re: Do you really need foreign keys?

#108

Post by bhaak »

I’m missing the execution plans of these DELETE statements.

Without them it‘s not clear what‘s the problem.

But in any case sacrificing referential integrity for speed is a bad idea. You could go NoSQL in that case anyway.

Re: Do you really need foreign keys?

#110

Post by jhardy54 »

Pet_Ant wrote: post #84
mavelikara wrote: post #64

Earlier quoted context omitted.

+1. When I started in the industry, it was common for more experienced developers to drill the “data outlives the application that generated it” principle into you. Somewhere in the transition to NoSQL and back we lost this.

The _data_ yes, the _database_ no. The data will get migrated to different solutions at different times. I've lost track of how often I've been porting MySQL to SQL Server, SQL Server to Postgres, Postgres to Mongo, Mongo to cloud etc. EDIT: fixed capitalization of "PostGres"

Friendly suggestion: the conventional capitalization is either Postgres or PostgreSQL.
Post reply on HN