4 ms·

When I was choosing jobs in the real world[1], I would avoid any company that depends heavily on stored procedures for business logic and where things were rule

When I was choosing jobs in the real world[1], I would avoid any company that depends heavily on stored procedures for business logic and where things were ruled by “database developers” and all the developers were doing were calling stored procedures. Stored procedures are harder to unit test, do automated rollbacks, version control, etc. I’ve never seen a system that 5 years in anyone said “I’m so glad we decided to use stored procedures everywhere” [1] I work in consulting now and have no allusions that I face the same issues that day to day developers do. I get to lead development “solutions” and move on.

horsawlarway 4y ago
It's worse than that. Your database will be the first major thing to cave under load. The traditional tools most folks use (postgres/mysql/maria/etc) want to scale vertically, not horizontally (you can do it, but it's hard, and has lots of drawbacks). Eventually - You will hit hardware limits. Providers only have instances that are so big. There is a ceiling on how much you can scale your DB without having to do major, major work. I've seen this pattern play out now at 3 high growth companies I've worked at. The one that was using lots of stored procs and materialized views got hit the fastest and the hardest.
- pjmlp 4y ago
  When it happens it is time to actually use the big boys databases.
  - scarface74 4y ago
    I’ve worked with the big boys and seen plenty of times where databases fell over no matter how much data you through at it because of locking issues and other database contention issues. It’s much easier to horizontally scale a bunch of application servers than database servers.
    - pjmlp 4y ago
      That is why big boys databases do clusters.
      - scarface74 4y ago
        “clusters” only help when you can deal with eventual consistency (which is often admittedly more than most businesses realize). Sure you can scale your read only instances. BTW: I work in the consulting department of a little company I am sure you have heard of that knows something about dealing with “the big boys” at scale.
        pjmlp 4y ago
        Great, me too, several of them actually.
  - AdieuToLogic 4y ago
    > When it happens it is time to actually use the big boys databases. For years Oracle had a very small limit of how many CPU's stored procs could use baked into the DBMS engine. IIRC, it was something like 4. Maybe that's changed now, I don't know. I do know Oracle qualifies for most as being one of "the big boys databases."
  - Shorel 4y ago
    MySQL doesn't have materialized views, but it has better replication (at least using Aurora in AWS) than the big boys databases. Adding more replicas and changing the instance size scales much better than fine-tuning the big complexity of Oracle and its materialized views. So, maybe it is not the tool, but how to use it what matters. And fuck Oracle if by big boys databases you mean Oracle.
- nijave 4y ago
  More generically, scaling stateful things is generally hard than scaling stateless things (because you have to figure out what to do with the state). Even horizontally scaling systems you either have to redistribute the data on scale actions or keep the data in place and send new data to new infra (which may or may not be helpful)
0xCMP 4y ago
I would agree, but I think it's worth checking out one of Siver's postgres repos where I thought he tested things pretty well[0]. Also things like Postgres are often run as a "server" on local dev or CI but could easily be run more ephemerally[1]. That should help with testing various states and behavior of the database. I still would actually follow what you said thought because often times places which rely on stored procedures are, as you say, ruled by developers with the database access to manage those procedures and from what I've seen often do not have anything close to a reproducible setup. Besides the fact that often it's some proprietary database which is much more difficult to run locally than sqlite3 or PostgreSQL. Still I keep that separate from considering if views/stored procedures are actually a good solution. I think sometimes it really is. Honestly Sivers' experiments really convinced me of that. [0]: https://github.com/sivers/store https://github.com/sivers/store [1]: https://jamey.thesharps.us/2019/05/29/per-project-postgres/ https://jamey.thesharps.us/2019/05/29/per-project-postgres/
pjmlp 4y ago
It is a matter of database tooling, Oracle and SQL Server provide quite good IDEs, graphical debugging, merge tooling, testing infrastructuring. I can tell you that at least a pharmaceutical is quite happy to have plenty of stored procedures into their Oracle databases, including making use of Apex, and it is at least several decades in production.
- scarface74 4y ago
  And when you get ready to rollback, can you just revert all of your code and deploy? Can you just do a “git branch” and work on your own isolated code in your dev account?” How well does merging work when you do a pull request and have to merge your code and make sure your stored procedures are up to date?
  - pjmlp 4y ago
    Yes, because there is such thing as CI/CD pipelines, and test environments, for database code as well.
    - scarface74 4y ago
      If there are ten developers with 10 feature branches are they going to be running 10 instances of Oracle with their own “branches” of the stored procedures?
      - pjmlp 4y ago
        Yes,install the RDMS into their computer just like any other development tool. Yes, learn to use multiple schemas and instances, just like using multiple deployments into Apache, Tomcat, IIS,... Or even better, join the hype, and use containers with volumes configured for each feature branch.
        scarface74 4y ago
        And then copy “enough” data on their computers to have a realistic test and make sure you have some type of script so sensitive data isn’t on their laptops and ensure schema changes are constantly propagated to each developers workstation. This isn’t even mentioning if you have a commercial database like Oracle where you have to deal with licenses. And how do you merge changes and do rollbacks? Not to mention the whole GetCustomer_1 GetCustomer_2 GetCustomer_3 Where code is copy and pasted and modified slightly.

horsawlarway 4y ago

It's worse than that. Your database will be the first major thing to cave under load. The traditional tools most folks use (postgres/mysql/maria/etc) want to scale vertically, not horizontally (you can do it, but it's hard, and has lots of drawbacks). Eventually - You will hit hardware limits. Providers only have instances that are so big. There is a ceiling on how much you can scale your DB without having to do major, major work. I've seen this pattern play out now at 3 high growth companies I've worked at. The one that was using lots of stored procs and materialized views got hit the fastest and the hardest.

pjmlp 4y ago
When it happens it is time to actually use the big boys databases.
- scarface74 4y ago
  I’ve worked with the big boys and seen plenty of times where databases fell over no matter how much data you through at it because of locking issues and other database contention issues. It’s much easier to horizontally scale a bunch of application servers than database servers.
  - pjmlp 4y ago
    That is why big boys databases do clusters.
    - scarface74 4y ago
      “clusters” only help when you can deal with eventual consistency (which is often admittedly more than most businesses realize). Sure you can scale your read only instances. BTW: I work in the consulting department of a little company I am sure you have heard of that knows something about dealing with “the big boys” at scale.
      - pjmlp 4y ago
        Great, me too, several of them actually.
- AdieuToLogic 4y ago
  > When it happens it is time to actually use the big boys databases. For years Oracle had a very small limit of how many CPU's stored procs could use baked into the DBMS engine. IIRC, it was something like 4. Maybe that's changed now, I don't know. I do know Oracle qualifies for most as being one of "the big boys databases."
- Shorel 4y ago
  MySQL doesn't have materialized views, but it has better replication (at least using Aurora in AWS) than the big boys databases. Adding more replicas and changing the instance size scales much better than fine-tuning the big complexity of Oracle and its materialized views. So, maybe it is not the tool, but how to use it what matters. And fuck Oracle if by big boys databases you mean Oracle.
nijave 4y ago
More generically, scaling stateful things is generally hard than scaling stateless things (because you have to figure out what to do with the state). Even horizontally scaling systems you either have to redistribute the data on scale actions or keep the data in place and send new data to new infra (which may or may not be helpful)

0xCMP 4y ago

I would agree, but I think it's worth checking out one of Siver's postgres repos where I thought he tested things pretty well[0]. Also things like Postgres are often run as a "server" on local dev or CI but could easily be run more ephemerally[1]. That should help with testing various states and behavior of the database. I still would actually follow what you said thought because often times places which rely on stored procedures are, as you say, ruled by developers with the database access to manage those procedures and from what I've seen often do not have anything close to a reproducible setup. Besides the fact that often it's some proprietary database which is much more difficult to run locally than sqlite3 or PostgreSQL. Still I keep that separate from considering if views/stored procedures are actually a good solution. I think sometimes it really is. Honestly Sivers' experiments really convinced me of that. [0]: https://github.com/sivers/store https://github.com/sivers/store [1]: https://jamey.thesharps.us/2019/05/29/per-project-postgres/ https://jamey.thesharps.us/2019/05/29/per-project-postgres/

pjmlp 4y ago

It is a matter of database tooling, Oracle and SQL Server provide quite good IDEs, graphical debugging, merge tooling, testing infrastructuring. I can tell you that at least a pharmaceutical is quite happy to have plenty of stored procedures into their Oracle databases, including making use of Apex, and it is at least several decades in production.

scarface74 4y ago
And when you get ready to rollback, can you just revert all of your code and deploy? Can you just do a “git branch” and work on your own isolated code in your dev account?” How well does merging work when you do a pull request and have to merge your code and make sure your stored procedures are up to date?
- pjmlp 4y ago
  Yes, because there is such thing as CI/CD pipelines, and test environments, for database code as well.
  - scarface74 4y ago
    If there are ten developers with 10 feature branches are they going to be running 10 instances of Oracle with their own “branches” of the stored procedures?
    - pjmlp 4y ago
      Yes,install the RDMS into their computer just like any other development tool. Yes, learn to use multiple schemas and instances, just like using multiple deployments into Apache, Tomcat, IIS,... Or even better, join the hype, and use containers with volumes configured for each feature branch.
      - scarface74 4y ago
        And then copy “enough” data on their computers to have a realistic test and make sure you have some type of script so sensitive data isn’t on their laptops and ensure schema changes are constantly propagated to each developers workstation. This isn’t even mentioning if you have a commercial database like Oracle where you have to deal with licenses. And how do you merge changes and do rollbacks? Not to mention the whole GetCustomer_1 GetCustomer_2 GetCustomer_3 Where code is copy and pasted and modified slightly.