8 ms·

Follow this advice with caution. Dropping foreign keys is effectively giving up part of the C in ACID. It should be done with very, very open eyes to the downsi

• erik_seaberg 3y ago
  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.
  • goostavos 3y ago
    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.
    • eddd-ddde 3y ago
      I strongly believe the opposite. Your business IS your database. How do you handle access to the data without access to the data? 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?
      • arrowleaf 3y ago
        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?
        • eddd-ddde 3y ago
          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 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.
          • DasIch 3y ago
            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.
    • butlerm 3y ago
      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?
    • winnebago 3y ago
      Different business units often want aggregate, filter, join and transform data in different ways - sometimes in ways that are hard to anticipate. 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)
  • arrowleaf 3y ago
    My assumption (with modern applications!) is that nothing but the role directly owning the data will access the data. The development and DBA teams will likely have a role they can assume after performing a carefully-audited breakglass procedure to use in an emergency (rare) or to fulfill audit tasks. At least in my org this is a well-known problem with legacy applications sharing databases. Limit access to the database to a single role, used by a single application, and you absolve so many issues.
    • jbmsf 3y ago
      100%.
    • chasil 3y ago
      When your data has outlived several generations of front-end technologies (mine started with Powerbuilder), you will find that ownership of the data does not control the evolution of how it will be used with what replaces past clients. One particularly interesting Oracle problem is: ORA-00060: deadlock detected while waiting for resource Tom Kyte's book, Expert One-on-One Oracle, describes the primary culprit: "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; Foreign keys can cause interesting problems, and SQLite specifically prefers to avoid them.
    • pixl97 3y ago
      >Limit access to the database to a single role, used by a single application, and you absolve so many issues. 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.
      • arrowleaf 3y ago
  • mavelikara 3y ago
    +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.
    • Pet_Ant 3y ago
      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"
      • winnebago 3y ago
        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.
        • blowski 3y ago
          I’ve seen a database migration project outlive application rewrites, multiple changes in CTO, and the company being acquired.
      • jhardy54 3y ago
        Friendly suggestion: the conventional capitalization is either Postgres or PostgreSQL.
    • SkyPuncher 3y ago
      Context is key. 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.
  • Pet_Ant 3y ago
    > 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 https://martinfowler.com/bliki/ApplicationDatabase.html [2] https://martinfowler.com/bliki/IntegrationDatabase.html https://martinfowler.com/bliki/IntegrationDatabase.html
    • epicfeedback 3y ago
      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.
      • ch4s3 3y ago
        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.
        • GoblinSlayer 3y ago
          Foreign keys are slow on delete, not on read. If you have a popular table, say, users, and all other tables refer to it, then deleting a user locks the database for time proportional to the number of foreign keys - good old linear scaling.
          • ch4s3 3y ago
            Sorry I should have specified read/write and side that you can read from something in memory that is consistent after some short delay.
  • dasil003 3y ago
    Interesting that this whole thread makes no distinction between read and write access, as those are dramatically different use cases. Read access is by far the more necessary, and is usually solved relative easily by saving snapshots to a data warehouse. This is no panacea as data can still easily be misinterpreted or replicated and used out of context in violation of expected production data lifecycle by the team that owns the source-of-truth, but in many cases it's trivial and minimizes coordination overhead. 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.
    • JackFr 3y ago
      Indeed - I immediately thought of hypothetical system (similar to many systems I’ve worked on) where SELECTS are 100x more common than INSERTS which are 100x more common than DELETES. The 5x speed up on delete performance is a useless optimization.
    • pmontra 3y ago
      The no foreign keys advice tastes of 2005/2010 when the lack of support in some frameworks (Rails included) prompted developers to dismiss them as unnecessary. This piece of advice at least quantifies the terms of the tradeoff. 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.
  • bborud 3y ago
    If people use the database as an integration layer, IMNSHO, they deserve what they get. Database is implementation - not integration interface.
• buttercraft 3y ago
  > The data will very likely outlive the application that created it. 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.
• Fire-Dragon-DoL 3y ago
  I am fine with the concept of foreign keys, but if you have a perfectly designed database, it should be really hard to insert a row in any table by definition. 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
• butlerm 3y ago
  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 conversions.
  • hermanradtke 3y ago
    > you could just write test cases to check for it 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.
  • dasil003 3y ago
    "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?
    • jrumbut 3y ago
      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.
      • butlerm 3y ago
        Orphaned detail records are usually inconsequential, like uncollected garbage. References to anything with an optional relationship should use outer joins as a matter of course. If you delete something that really needs to be there you have a problem, which is one of the reasons not to delete rows like that, ever, but rather to mark them as inactive or deleted instead.
• forinti 3y ago
  That's the best advice: data will outlive the application. It will also serve other applications. So treat it as its own thing, not as an appendix of the application.
  • postalrat 3y ago
    Imo better advice would be your data may outlive the schema it's stored in or your application may outlive the data.
• shayonj 3y ago
  Hi! The author here. I appreciate your reply here and in few other threads for this post too. I think they are very well thought out. I also like how it brewed more branches of other good discussions. 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!