1. 7
  1. 6

    I’m all for stored procs–some say they’re the original microservices. Yet many seem staunchly against them. Probably recalling the horrors of bygone databases filled with hard to read, undocumented, untested procs with no version control. Those days are thankfully behind us using migrations, but many still cling to the irrational fear that even version-controlled migrations won’t be enough to keep the horrors at bay…

    1. 4

      I think there also used to be a lot more security concerns with writing stored procedures in C. But with plpgsql I don’t feel those same concerns…

    2. 2

      I’m amused and sad that In my long career, we have moved (40 years ago) from the then problem of how to remove the unmaintainable mess that stored procedures inevitably become, to today promoting they be applied again.

      Forget, rediscover, rename, silver bullet adoption, relearn limitations, discard, and repeat. The “science” of our industry.

      1. 3

        I’ve been keeping everything in PostgreSQL functions for 7 years now, for all of my web apps, and I’ve never found any problem with it.

        Ever since I first started posting about it in 2015 - https://sive.rs/pg - I’ve heard some commenters say this same thing you’re saying, but never any concrete explanation of why this is such a problem now, in 2022, in PostgreSQL.

        I think stored procedures must have really traumatized some people 40 years ago, but maybe the problems with it then are not still problems now.

      2. 2

        In hindsight, data logic should be in the database itself.

        This is the reason we are creating TigerBeetle [1] at Coil, as an open source distributed financial accounting database, with the double entry logic and financial invariants enforced through financial primitives within the database itself.

        This is all the more critical for financial data, because raw data consistency is not enough for financial transactions, you also need financial consistency, not to mention immutability.

        The performance of doing it this way is also so much easier. For example, around a million financial transactions per second on commodity hardware, with p100 latency around 10-20ms.

        [1] https://github.com/coilhq/tigerbeetle

        1. 5

          Quoting p100 latency instead of p99 is a nice touch. :)

          1. 5

            :) Thanks. p100 is nice and easy to measure and there’s nowhere to hide. I learned it through firefighting an incident on a JS system with a 32 GiB heap — the p99 was a few ms but the p100 was a few minutes. After that I became much more interested in taking the max!

        2. 1

          stored procs can have their uses, but there are defiantly costs to using them as well. In my experience the hardest part of developing with them is debugging and error messages when they don’t function as expected. SQL is a fine set language, but I don’t think any of the flavors works well with procedural logic, especially as the complexity grows. That complexity generally is most visible in the parameters of the stored procedures with inserts and updates… it’s not uncommon to have store procedures with 15+ parameters, it’s not fun to review client calls like that. The other major issue I’ve dealt with is change control, deployments and rollout of features is much harder when your application storage engine also holds your application logic. Do I release a new named version of my stored procedure and then release my services pointing to the new version of the proc? Do I update it in place? Will one service run a migration and update my stored proc for all services?

          All that said using stored procs to tightly control the actions of a client can be a big security boon. Also, if you need the most performance possible store procs will likely get you there.

          1. 1

            I think it is very important to look at the author here, and the intended audience. I think Sivers has two distinct things: minimalistic product vision and minimal “team”.

            The author is focused on simplicity and minimalism, doing the least amount of work to get the job done. In such way, I doubt he’d ever have a store procedure with 15+ parameters.

            And the other part is connected too: he’s often either working alone, or has a very small team. (At least that’s my understanding). So even if one more complicated proc sneaks in, it’s probably not overly complicated, and if it is, he probably wrote it anyway so he would likely manage with smaller risk then your usual 6-people team.

            1. 2

              Fair points, and I’m not very familiar with any additional context of the author so hopefully I’m not sounding too dismissive. I’m not against using stored procs or the pursuit toward minimalism, but hope to add some details of why store procs are often avoided.

              RE: the 15+ parameters, assuming you use stored procs for inserting or updating data this is very hard to avoid as a project grows. TSQL provides table value functions to help bring more structure to this, but at least as far as I know PL/SQL for postgres doesn’t have any way to support that.