Do you really need foreign keys?
shayon.dev
shayon.dev
The data will very likely outlive the application that created it. It's almost guaranteed to outlive your tenure on the team. Viciously guarding its correctness solves all the problems that not guarding it causes.
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.
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.
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.
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)
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.
EDIT: fixed capitalization of "PostGres"
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.
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.
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.
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.
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 TorvaldsYes 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.
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.
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.
Assuming a standard n-tier application architecture, how do you guarantee the test prevents race conditions?
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.
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.
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
It will also serve other applications.
So treat it as its own thing, not as an appendix of the application.
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
Always important to remember that while learning from others is important; They're just as human as you are.
That said, I quite like this post and the way the author is thinking critically about software decisions most of us take for granted. Even if you decide that foreign keys are always best, this type of thinking is a great way to improve your understanding.
I completely agree with the idea that foreign keys aren't always recommended. For example, one of the reasons that MongoDB was so great at edge writes was because of the lack of referential integrity and the "document" as a row concept - denormalize the data into a document and store everything you need there.
That works great for high volume edge writes that you want to operationalize later, asynchronously. Sure, the data can be messy and may have errors - but many use cases tolerate this well.
The nice thing about modern postgres is that you get the best of both worlds, RDBMS stuff when you need it, and NoSQL stuff if you don't.
The author's point, which I think is an excellent one, is that just because it's in a database, it doesn't necessarily make sense to go full 4th normal form on everything.
I was recently working on a system that split data across multiple database instances (unnecessarily) and that means referential integrity is lacking and sometimes a huge problem.
To expound, I was tracking down something call a "slotid" and the absence of foreign keys does not mean anything. That data could very well live in some other table on the other DB instance. It turned out though that "slot-id" instead referred to a start time of day as counted by 'minutes-from-midnight'. Thus "slot-id=375" was just "6:15am"... Yup.. 3 hours of my life I will not get back to realize that data did not refer to anything at all. If everything that could have had foreign keys did, then it would have been a super quick investigation to realize the data was not a reference to anything at all.
Mostly, this is in order to support our complex custom schema functionality, described in the Multitenant Whitepaper here (https://www.developerforce.com/media/ForcedotcomBookLibrary/...)
But interestingly, this also lets our internal data modelers choose to make a relationship e.g. leave dangling records or have other update/delete patterns that are appropriate for the data and data volume. Obviously there are cleanups and tradeoffs required here. It's certainly not a model I'd suggest people start with, but there really are times where database native FKs aren't the answer.
I assume "internal data modelers" is an actual role of person whose job is to ensure long run data integrity?
Data volume and Salesforce also don't belong in the same sentence, as it is not comparable to the data volume a basic Postgres database can handle.
Until recently Salesforce didn't provide a data backup facility, other than exporting CSVs, so not even sure you can call it a database.
This can be done in application logic, but that risks bugs allowing broken references into your data. Its more foolproof when these checks are enforced directly at the db, making sure data is valid before it's stored or updated.
You better very carefully read your database's concurrency guarantees and ensure that all transactions are running with the right consistency level otherwise you will have a bad day.
On top of that the JOIN method of following foreign keys often leads to missing rows if expected keys don't exist which can be very hard to debug.
[1] https://www.reddit.com/r/ExperiencedDevs/comments/18ldexi/wr...
They all had PK/FK/unique/check constraints, and they were still a clusterfuck to understand and maintain. Lots of outright stupid and dangerous code to work around the issues.
Still, if there were no constraints, it would have been even worse, as some 'intelligent' people temporarily disabled constraints to update data, resulting in data integrity hell.
Anyone planning to test "YMMV", or full blown follow through, is advised to read that dailywtf.
> 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 maintain integrity, instead of doing it on its own. It may even rely on FK to cascade deletes (shudder). When eventually you want to shard or extract data out, you need to change & test the app to an unknown extent.
> * 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.
> * FKs don't work well with online schema migrations.
This is not a valid argument at all and I'm concerned anyone would think it is.
If you have a foreign key, it means you have a dependency that needs to be updated or deleted. If that's the case, you will have an overhead anyway, the only question being whether it's at the DB level or at the application level.
I don't think there are many cases where there's any advantage to self-manage them at the application level.
> FKs don't work well with online schema migrations
This seems to be related only to the specific project that the issue is about if you read about the detailed explanation below.
It is not a binary situation like that. With the rise of 'n-tier' systems that are ever so popular today, there are often multiple DB levels. The question is not so much if it should go into the end user application – pretty much everyone will say definitely not there – but at which DB level it should it go in. That is less clear, and where you will get mixed responses.
Inserts and updates do not require referential integrity checking if you know that the reference in question is valid in advance. Common cases are references to rows you create in the same transaction or rows you know will not be deleted.
If you actually want to delete something that may be referred to elsewhere then checking is appropriate of course, and in many applications such checking is necessary in advance so you have some idea whether something can be deleted (and if not why not). That type of check may not be race free of course, hence "some idea".
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.
* https://code.openark.org/blog/mysql/things-that-dont-work-we...
* https://code.openark.org/blog/mysql/the-problem-with-mysql-f...
If you’re disciplined it’s not difficult to implement yourself in the application, but the challenge is if folks can access the database directly. If so, good luck, since it’s inevitable that they end up making modifications and break integrity.
If one insists on not having foreign keys (and there are decent reasons to not have them), I would really really suggest not allowing direct access to the database and enforce whatever application be the main or only client to the database.
Of course it's not a real substitute for foreign keys, but definitely better than nothing.
I only see disadvantages:
- no data integrity check in the database
- more complicated definition of foreign relation, or none at all, leaving possibility for deviant data
- scatteted schema. Cant look at the db table and understand the entire model. Have to hunt in code an potentially across a lot of code.
It just seems like a data integrity issue that needs to be enforced at the db level.
It is always implemented in the application. The question is about whether you do it in your own application or use someone else's application.
The disadvantage of doing it in your application is that you have to do the work that someone else has probably already done, and are likely not as great of a programmer as that other person, thus more likely to screw it up.
The big disadvantage of foreign keys is they do verify integrity. That means making lookups on every write/update which can be very costly especially as the model becomes more complex.
If you have a read heavy application with low levels of writes then by all means put in foreign keys to you hearts content. But if you are at a point where you have millions or billions of records in multiple tables, they simply aren't feasible.
At my first job they used mongodb for no reason other than it was the fad at the moment, with the big data and so on.
There were often crashes in the application because our records followed several different schemas, because of bugs in the application that were later fixed, behaviour that got changed, ORM got replaced with hand written code that was much faster…
Basically, what happens with over confident developers and CTO that think they are very good developers and aren't.
(Asking to learn, not to argue)
U could use caching so not to hit db often and also app scales way easier than db.
The overhead of checking for the existence of referred to records in ordinary inserts and updates in application code is unnecessary in most cases, and that is where the problem is. Either you have to check to have any idea what is going on, because your key values are being supplied from an outside source or you should be able write your application so that it does not insert random references into your database.
If you actually need to delete a row that might be referred to, the best thing to do is not to do that, because you will need application level checks to make the reason why you cannot delete something visible in any case. 'Delete failed because the record is referred to somewhere' is usually an inadequate explanation. The application should probably check so that delete isn't even presented as an option in cases like that.
I feel like this belongs to the same strategy as duplicating form-validation on frontend/backend. The frontend validations can't be trusted (they can be skipped over with e.g. curl POST), so backend validation must be done. But you choose duplicate it to the frontend for user-convenience / better reporting / faster feedback loop. The backend remains the source of truth on validations.
The same between database and application; the database is much more likely to be correct when enforcing basic data constraints and referential integrity. The application can do it, its just a lot more awkward because they're also juggling other things and have a higher-level view of the data (and the only real way to check you didn't screw up is to make your testcase do exactly the same thing... but be correct about it -- no one else is going to tell you your dataset got fucked. Also true in an RDBMS, but it's trivial to verify by eye, and there's only one place to check per relationship). Thus in my world-view, the database must validate, and the application can choose to duplicate validation for user-convenience / better reporting. The database remains the source of truth on validations. As an optimization, you remove the database validations, but at your own risk.
And then in a multi-app, single db world, then you really can't trust the application (validations can be skipped), so even that optimization is likely illegal. Or you do many-apps *-> single-api -> db, and maintain the optimization at the cost of pretty much completely dropping the flexibility of having an RDBMS in the first place
If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!) and/or that the foreign records are never gathered up for referring table. If that's the case the problem isn't having superfluous indexes it's that you have superfluous data in your database.
It's not very small. Doing a lookup on every write can be hugely detrimental to performance especially with large numbers of writes.
> If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!)
That does not follow. The lookup for foreign records is not free so avoiding doing it when you don't need to will gain faster performance vs doing it all the time. Indexes make lookups faster, they don't make them free.
Further, you have to consider the impact of locks on such a system. Writes to a table that references another will lock the contents of the second table while the write is in flight. So if I wanted to update the foreign record in any way, that task now gets blocked until data integrity check finishes.
In MSSQL, that lock is held until the end of the transaction.
That's all fine, but we know how this ends up in reality.
Checking that child records that can exist only if there is a parent record are deleted when the parent is deleted and that the foreign key you're using in a record actually exists in the pointed-to table, for the price of a single index you very likely need anyway, is hardly a problem.
You don't need to cascade deletes or anything, that's a red herring, as is migration issues where you can turn off referential integrity while the process is performed.
This is not the challenge. The challenge is that you think you can do an 'almost-as-good' job of data integrity as the RDBMS designers.
Even if you could, you will not always be the person that maintains that code.
When you reduce complexity and take off the safeguards things get faster! Cock that foot gun and hope that it doesn't go off!
Can you do what the author suggests. You sure can and we did it for a long time with MYSQL. Should you? It depends on your team, how in tune they are with working with databases, sql etc...
You would fully normalize, use proper joins etc. But without FK constraints you could end up with orphan data... Not the worst thing in the world, depending on how the joins in your system were structured.
Care and diligence were the order of the day. You had to know your schema and make sure you were doing the RIGHT thing at all points in the stack.
With a certain level of team maturity and thoughtful reviews, this has rarely been an issue. Sometimes there are orphaned rows (from incomplete/buggy writes) which also have clean-ups that tend to have jobs for pruning data that has gone 'out of the retention window'.
I have seen many people running MySQL make that claim about lots of things... But what I have never seen is a MySQL database for business data without major issues.
I’m a big fan of FKs. They stop you from doing stupid shit, and if the time ever truly comes that you have to drop them, you can do so.
I work on a legacy MySQL database that has 100+ moderatedly-related tables with zero foreign key constraints.
Someone wanted to change the name of a particular object, so I did a little investigation and found that approximately two dozen tables were storing this name-as-key, with at least half a dozen different column names. So essentially we have to scan every row in the entire database to robustly maintain referential integrity, and I vetoed the largely cosmetic change. Granted, this isn't 100% a FK constraint issue, but I imagine if the original DBA knew what FKs were, I wouldn't be in this mess.
On the other hand, I have implemented prototypes that clearly had way too many FK constraints, which leads to a bloated schema of unnecessary tables with 1-2 columns each, and makes object lifecycles a spaghetti nightmare.
If your system is hooked up to a data warehouse you can run queries there, too.
I bet you can find all sorts of weird edge-case records doing this.
Soft deletion is occasionally useful, but losing foreign keys for it is a big pain.
EDIT: got a bunch of responses, thanks! To be clear, the issue I have in mind is e.g. you want to have a foreign key that makes sure the “singular” side of a one-to-many relationship isn’t soft-deleted (on delete restrict) as long as it has anything in the “many” side. Both sides using soft-deletion.
1. For each relevant foreign key, have an additional column without a constraint where the foreign key value can be copied to before deletion. (This requires that the original foreign key be nullable.)
2. Make soft deletion universal so that foreign keys can remain and any associated rows will still exist.
At a basic level you could duplicate the entire record into the audit table on every action, e.g. the audit table would look like `audit_id | record_id | user/process_id | action (insert, update, delete) | timestamp | ...<record rows>`.
You can optimize it to not duplicating column values unless necessary. On inserts you only need the metadata of the action. On updates, the old value of columns with changes goes into the audit table. On deletes the whole record goes into the audit table.
Having a separate table for deleted records means that
- FK references to the main table will just work
- the main table can be kept clean.
- it avoids the class of problems where a user or the application treats soft-deleted records as real records, because they weren't aware of the `deleted_at` column.
a join b join c
a join c
I suggest that anyone thinking that has a look at a database where there are no foreign keys.
It was a nightmare. The article seems to basically be saying "but it's hard!". Well, I'd rather put down the extra effort so that I don't have corrupt data.
You know what's really hard? Fixing corrupt data.
The data is the most important thing in a database.
You can drop foreign key constraints and enforce referential integrity in your code, but from a logical standpoint your foo.barId column is still a foreign key.
Actually getting rid of foreign keys on the other hand can be done in a data store that allows nested structures, like e.g. a document based store. The price to pay is data duplication and denormalisation (and the related complexity you will have to deal with in all write operations), the pros are ease and speed of retrieval.
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.
In other words, if your tables aren't referencing other tables, how do you perform JOINS for your queries? From my limited experience, they seem pretty essential for most databases. Or is the article just talking about foreign key CONSTRAINTS and cascading UPDATES and DELETES, rather than literally talking about the values stored in foreign key columns?
If anyone can explain, I would greatly appreciate it.
While FKs and JOINs often go hand in hand, JOINs actually do not require foreign keys or any other constraint. You can join any table and column that you want regardless of FK or index or whatever (although you have to be careful about performance).
So the universe decided to punish him and a few weeks later some data got deleted that would have been protected by an FK.
This idea is no longer discussed.
Then there isn't really any opportunity for inconsistent data.
We had some missing FK constraints, along with missing cleanup in code for a given table. Some customers had many millions of orphaned rows, while only thousands of live rows.
Certainly didn't help index scans...
obj = new_object()
obj.col1 = get_value_from_somewhere()
obj.user_id = get_logged_in_user_id()
obj.insert()
# Find correct ID.
obj.some_id = run_query("select some_id from tbl where x=?", param)
Here's the common scenario:
deals = sql("select * from current_deals ...");
// Show the user the deals.
// User takes his sweet time to ponder over the deals.
// Meanwhile, the deals are gone.
order = new_order()
order.deal_id = deals[5].id
order.insert()
// User calls in to complain never receiving the deal.
// Ok, need to verify the deal still exists.
sql("select deal from current_deals where id = ? for update", deals[5].id)
... insert ...
sql("commit")
Also some referential errors are sort of ok in PROD, as long as it's only about not dropping user data; which can be dealt with later on (INT gets reset with PROD user data from a backup each week, it also helps in the restore plan, fk are enabled, errors are caught, then data gets pruned heavily)
If referential integrity is a business-level bug, then of course we should enable them.
I suppose it's entirely dependent on the type of data you're working with though.
Can you tell the name of the company? It's for my lawyer.
I mean I think the constraints should be on in all environments, but disabling them in prod but not dev seems utterly backwards? Protect your test data from getting corrupted but not your actual customer data?
Have a load balancer or replication in prod? You must have the same when you test.
Otherwise, you will have a bad time m'kay.
And don't get me started about removing fk constraint, anybody doing that on purpose is either very, very smart, either very, very ignorant/inexperienced.
Foreign keys != Foreign key constraints
And if you're concerned for microspeed of basic operations Postgres isn't your friend anyway.
Do you really need consistent data?
Do you really need test coverage?
I mean, you can make do without it. But it’s generally better to have it than not.
I like Django's ORM approach which allows you to easily set baseline filters for your Model Managers, so you can implicitly exclude "deleted" objects from most queries.
Essentially a foreign key constraint is an on {insert,update,delete} trigger which checks whether the target check exists, so that's a select on the target table. I'm not sure if that's still the case, but I believe that for a long time foreign keys were just implemented as triggers in PostgreSQL.
For a lot of things that has a minimal performance impact and it's not a big deal. For some other things it can really add up.
So in the vast majority of cases, the integrity is worth it.
±a lot because this is a very quick test I ran just twice, but it fits what I measured before when I very much ran in to this performance penalty a few years back.
And look, 20ms vs. 100ms is very much fine for a lot of use cases. But it's also not for a lot of others. And it's certainly not very minimal, and with many inserts it does amplify a lot.
Not complaining about the types of coworkers I see, but sometimes tapas are kind of nice.