4 ms·

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

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.
- ttfkam 3y ago
  If I recall correctly, "no foreign keys" hit its stride back in early PHP days when MySQL didn't support them properly. Rather than cop to the fact that MySQL just implemented them badly, MySQL AB went on a dev PR run telling folks that foreign keys weren't actually useful and just slowed a system down. 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.
  - pmontra 3y ago
    Nobody with a Computer Science or especially a Software Engineering degree should have felt for it but I know a number of good developers with a more varied background. Some of them inevitably became team leaders etc. By varied backgrounds I mean Philosophy, Agricultural Sciences, Graphic Design. Some of them know very well how a database work, some admit to never have learned SQL, go figure all the theory and the rationale behind some technologies. Give them some blog posts that make the job done without foreign keys and they won't even know what a foreign key is until it's 2015 and people started to move to PostgreSQL.
    - ttfkam 3y ago
      My degree is in literature. (I'm that dev who also writes docs, makes presentations, and serves as tech lead.) I still know that foreign key constraints should be the default. I even often have to remind those with CS and CE degrees that DBs aren't just dumb bit buckets and that database schemas are critical business logic. Bad programmers worry about the code. Good programmers worry about data structures and their relationships. – Linus Torvalds
- dasil003 3y ago
  > 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? 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.

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.

ttfkam 3y ago
If I recall correctly, "no foreign keys" hit its stride back in early PHP days when MySQL didn't support them properly. Rather than cop to the fact that MySQL just implemented them badly, MySQL AB went on a dev PR run telling folks that foreign keys weren't actually useful and just slowed a system down. 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.
- pmontra 3y ago
  Nobody with a Computer Science or especially a Software Engineering degree should have felt for it but I know a number of good developers with a more varied background. Some of them inevitably became team leaders etc. By varied backgrounds I mean Philosophy, Agricultural Sciences, Graphic Design. Some of them know very well how a database work, some admit to never have learned SQL, go figure all the theory and the rationale behind some technologies. Give them some blog posts that make the job done without foreign keys and they won't even know what a foreign key is until it's 2015 and people started to move to PostgreSQL.
  - ttfkam 3y ago
    My degree is in literature. (I'm that dev who also writes docs, makes presentations, and serves as tech lead.) I still know that foreign key constraints should be the default. I even often have to remind those with CS and CE degrees that DBs aren't just dumb bit buckets and that database schemas are critical business logic. Bad programmers worry about the code. Good programmers worry about data structures and their relationships. – Linus Torvalds
dasil003 3y ago
> 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? 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.