Write access on the other hand is a different story. IMO restricting write access of a database to a single application is table stakes for scaling complex applications. Sure there are other approaches such as writing all your logic in the database via constraints and stored procedures and making the DBA a god-like figure, but these approaches have fallen out of favor as they've proven less scalable compared to wrapping a DB with a service that has exclusive write access. The latter arrangement allows many constraints to be enforced in a horizontally scalable and more legible layer, while still leveraging the DB to prevent races with a better menu of tradeoffs.

Of course this requires thoughtful service and interface design by competent technical domain experts, which is easier said than done, but the alternative allows the overall system cohesion to degrade to where no one understands the system well enough to make any changes without risking major incidents. At that point, the agency of system builders and maintainers is replaced by care and feeding of the unknowable system to not upset the status quo, accompanied with increasingly byzantine hacks and workarounds to enable any business changes.

The 5x speed up on delete performance is a useless optimization.

Regarding the correct "saving snapshots to a data warehouse", if there are one million web apps in the world, how many of them have the scale to noticeably benefit from either doing without foreign keys or from a data warehouse? I've been using many of them like everybody else but they are totally irrelevant to the long tail of apps and developers.

By the way, a good DBA can make miracles for the performances of most databases in that long tail. A few days of work are worth the cost especially if the team pays attention and learn the lesson. The best remark I got about a DB of mine was a "not bad for being only a developer". The DBA version was better.

Yes I agree. This advice was assuming you have scale that necessitates multiple services, at which point you'll want to be able to query against data from multiple sources. I'm with you that the vast majority of teams (finger in the air: less than 20 full-time engineers working on a typical web app) are probably better off with a monolith and single database. At this point a read replica is a low effort way to safely provide access to a wide range of stakeholders.

Once MySQL implemented them less horribly, the PR push finally started to die down. I will never forgive them for that, and decades later where Oracle controls MySQL (and arguably is doing a better job), I still hold a grudge against MySQL that I have to actively suppress when the contract demands it.

    Bad programmers worry about the code. Good programmers worry about data structures and their relationships. – Linus Torvalds

EDIT: fixed capitalization of "PostGres"

Now there's typically three applications that need DB access:

1. The application it was built for.

2. The read-only reporting and visualization tools.

3. The web API.

If you have an established business or startup, data will outlive the application. However, you need a product that lives long enough for either data or application to matter.

In the startup world, that means making decisions that help you ship now, at the expense of debt/costs down the road.

One particularly interesting Oracle problem is:

  ORA-00060: deadlock detected while waiting for resource 

"Oracle considers deadlocks to be so rare, so unusual, that it creates a trace file on the server each and every time one does occur... The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys."

For another perspective, add to this a default setting in every SQLite database:

  $ sqlite3 verynew.db
  SQLite version 3.34.1 2021-01-20 14:10:07
  Enter ".help" for usage hints.
  sqlite> .dump
  PRAGMA foreign_keys=OFF;
  BEGIN TRANSACTION;
  COMMIT;

This kinda sounds like "Get rid of 90% of the usefulness of having a database"

Of course, maybe you mean make the same data the DB has available via API, or make other users of the data read only.

It is inevitable that 2 "organizations" will at some point need access to the same data. You could just have each of them decide how they interact with it. It doesn't matter how they do it because the database itself makes sure invariants are kept true (such as FKs).

What's the alternative?

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 scraper to talk to the database, I just talk to the database...

The database is that "single appplication", no need to write custom endpoints for every operation when SQL is good enough.

It's all a spectrum of course, today you can even go as far as to use something like pg_graphql and you don't even need to write a REST api yourself.

Edit: I forgot to answer the last section, but postgres can totally handle autorization with RLS for instance, allowing users to only see their own data, or maybe data marked as public, etc.

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.

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.

I used to work on an application where ALL database accesses were via stored procedures. Genuinly the best dev experience, to me, so far.

I found such an environment to be simply terrible.

In general, I think stored procs certainly have their place, but if ALL access is through stored procs, you better have a schema that’s basically set in stone otherwise dev will turn into a nightmare.

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?

In general, the closer to the persistence layer you can perform those transformations, the better they will scale. If you pull the transform into the app layer, you need to move and serialize more data. If you pull the transformation into a constellation of apps, you need to move and serialize a constellation of data.

(edit: formatting)

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.com/bliki/IntegrationDatabase.html

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.

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.

Rightly so because when deleting the user the database needs to do work to keep the referential integrity. Either it nulls the user_id, delete the rows, or it throws an error.

It will also serve other applications.

So treat it as its own thing, not as an appendix of the application.

Yeah, if your front end is garbage, you can fix or rewrite it. If your data is garbage, you've lost. Depending on your requirements, of course.

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

Of course, you can just skip the validation altogether and cross your fingers and hope you’re correct, but it’s the same reasoning as removing array bounds checking from your app code; you’ve eked out some more performance and it’s great until it’s catastrophically not so great.

Your reasoning should really be inverted. Be correct first, and maintain excessive validation as you can, and rip it out where performance matters. With OLTP workloads, your data’s correctness is generally much more valuable than the additional hardware you might have to throw at it.

I’m also not sure why dropping/creating foreign keys is a big deal for migrations, other than time spent

Assuming a standard n-tier application architecture, how do you guarantee the test prevents race conditions?

You can check for the first two of those things after the fact, and they are relatively benign. In many cases it will make no difference to application queries, especially with the judicious use of outer joins, which should be used for all optional relationships anyway.

If you need ON DELETE RESTRICT application code should probably check anyway, because otherwise you have unexpected delete failures with no application level visibility as to what went wrong. That can be tested for, and pretty much has to be before code that deletes rows subject to delete restrictions is released into production.

As far as race conditions go, they should be eliminated through the use of database transactions. Another alternative is never to delete rows that are referred to elsewhere and just set a deleted flag or something. That is mildly annoying to check for however. Clearing an active flag is simpler because you usually want rows like that to stay around anyway, just not be used in new transactions.

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.

This effectively means you are building an embedded database in your application and using the networked database for storage. There are a few reasons to do this and a million reasons not to.

I totally agree that this decision isn't to be taken lightly. That said, I figured I'd also take this opportunity to clarify a few things:

- My post aims to prompt developers to critically assess whether the constraints they employ are truly essential or just conventionally used without question. They should weigh these constraints against the considerations mentioned in the post.

- While I recognize that lock contention is the database's way of managing application code, I propose that if foreign keys aren't necessary from the outset, then lock contention may only serve to impede performance. If you can rewrite your application code, say with `FOR UPDATE SKIP LOCKED`, great!

- I am also not recommending to drop all FK constraints, but to challenge and see if each one of them is necessary, and if it is, also good.

- As always, there is no solution fits all!

What would solve this is defaulting to raise foreign key constraints errors at the end of transaction rather than when rows are inserted. I know postgres has an option for it, it should be the default