3 ms·

Ask HN: Why is building apps around stored procs frowned on? I'm loving it

5 points by andrewstuart 3y ago

For a personal project I’ve just discovered the joy of Postgres stored procedures. I’ve written much of the back end logic in Postgres stored functions and it feels awesome. Instead of writing database logic in typescript I just do a very simple sql query to call the stored procedure……. it’s much easier. And ChatGPT seems extremely good at writing stored functions, so much of the development is conversing with AI to add features to the stored functions. So this has felt awesome and I’ve been more productive than ever before. There’s almost no logic in the web application, just very simple queries that receive JSON from Postgres so I don’t even have to do any faffing around to convert rows into arrays/objects. And it the same time I can almost hear all the industry experts yelling at me about what a bad idea it is to put your business logic in stored functions. But why? What’s the big problem? I don’t get it. I feel like I’ve discovered some secret superpower that everyone says is kryptonite. The stored functions are version controlled by my jetbrains IDE, so it can’t be that. So what is it that’s so bad about stored functions?

throwaway11460 3y ago
Combine that with Postgraphile or PostgREST and you don't need to do any server code. The hard thing is how to horizontally scale.
- gregjor 3y ago
  Scaling vertically and horizontally with relational databases got solved decades ago and continues to get better. Oracle and SQL Server run practically every huge enterprise database. Do you think those don’t scale?
  - throwaway11460 3y ago
    Not sure about Oracle or MS SQL, I work with Postgres. Back when I worked with Oracle and MS SQL it was about one-few huge DB servers, not many smaller ones. To scale the database you need replication, and replication makes it a distributed stateful system that might be harder to reason about than a classic server+database setup. In comparison, scaling a standard stateless backend server that connects to one huge DB server is fairly easy. If you have any guides to point me to I'd appreciate it.
    - gregjor 3y ago
      The state comes from the data, not the procedural code implemented in the database. Issues around replication happen with any distributed database. That doesn’t get worse with stored procedures, which don’t themselves have state.
      - throwaway11460 3y ago
        It's just as hard, indeed. But you don't need to replicate as early with server+DB because your DB is focused on data transactions only. You can have a replica ready for a potential outage and that's all you need to care about for a long time. When you run everything in your DB, replication issues will hit you much earlier. Of course this all depends on what kind of app are you building. If it's an internal tool or something like that, you probably don't need to worry.
        gregjor 3y ago
        The stored procedures and views and triggers get replicated too. I think you’re imagining that moving business logic into stored procedures will overload the database server sooner than having that in application code. I have never seen that. Instead you get fewer simultaneous connections and less data passed back and forth. Querying a compiled view or stored proc will often use fewer resources than parsing, planning, and optimizing SQL sent from application code. And the RDBMS can make better use of caching with business logic in the database. Scaling and replication may present complications with any architecture, but those aren’t good reasons in my opinion to not put business logic into the RDBMS. Real enterprise-scale databases tend to do that and they actually have scaling and replication issues, not just “may happen someday” issues.
        BenjieGillam 3y ago
        Completely agree; the issue I see with people who try putting logic in the DB ultimately to reject it is they try and bring their procedural programming paradigms (e.g. “for” loops and “if” statements) into the DB and that’s incredibly inefficient, requiring row-by-row looped fetches and ultimately causing their systems to grind to a halt and them to blame the concept of putting logic in the DB. If instead they were to embrace the declarative nature of the database and used efficient patterns such as statement triggers rather than row triggers, common table expressions (CTEs) rather than row-by-row looped processing, set-returning functions and joins rather than row-by-row function calls, etc then they would find that their code is both more expressive and often thousands of times more performant. As you say, embracing this model means less data transferred between cluster and client which can result in reduced load across the entire system without increasing complexity or having to worry about cache invalidation issues. And time spent learning databases is knowledge that will still be useful in a decade or two, it’s well worth investing into.
gregjor 3y ago
There’s nothing wrong with it. Enterprise environments often do it so business rules and logic live in one place, so application code (in multiple languages) doesn’t have multiple implementations. I think a few things came together to make programmers crap on stored procedures and putting business logic in the database: - OOP, which typically treats relational databases as dumb backing store for objects. - No enterprise experience, and weak RDBMS/SQL skills. - Popular open source relational databases with weak and clunky languages and tools, such as MySQL. Compared to Oracle and SQL Server the low-end RDBMSs most programmers learn look hobbled. - Unless you use Oracle or SQL Server or DB/2 you may have trouble debugging procedural code running in the database. If this style works for you don’t worry what other programmers say or what you read online. No matter how you choose to build your application you will get people telling you that you made bad choices.
ilaksh 3y ago
The funny thing is that there was a time (at least with Microsoft) where you were supposedly doing it totally wrong if you didn't use stored procedures enough. And I swear there was a time when every web developer knew that if you wanted a consistent, reliable layout for a home page, you sliced the design up into a table, and stayed away from that new CSS stuff because it was buggy and incompatible with different browsers. The reality is that there are several very different general approaches that can work for any particular information system. Some of them will be more compatible with each other or with certain tools. But I think that for the vast majority of applications, this comes down to trends more than any particular technical issue. I mean, technically speaking there are an effectively infinite number of ways to organize and distribute the code and data that will given the same outputs from given inputs. People use "best practices" as a shortcut to make it easier to judge things or decide how to approach a problem. I think one mark of a good engineer is their ability to adapt to different approaches. This actually requires more intelligence or at least energy because it means they might need to actually read code to decide if the approach makes sense. But it will save people quite a lot of energy to simply say "we [always/never] use stored procedures here!" And also even for brilliant engineers, constantly trying to wrap your head around completely different approaches can be impractical. So programming communities adopt their own worldviews. For the most part, it just means that you spend slightly more time on certain types of problems than the other group. But that group may not realize they are spending time on another thing. So there are trade-offs with every approach. Which is not to say that some frameworks can't save you more time than others. One aspect of this is compatibility. The whole approach may get thrown off if you use stored procedures versus not, depending on the rest of the framework. But I would suggest that there is usually just as much cultural preference as actual technical merit to these debates when you dig deep into the details.
beardyw 3y ago
Don't be too sensitive. Someone will sneer at you whatever you do. If it works, satisfies the business case and is maintainable all is good.

5 points by andrewstuart 3y ago

throwaway11460 3y ago

Combine that with Postgraphile or PostgREST and you don't need to do any server code. The hard thing is how to horizontally scale.

gregjor 3y ago
Scaling vertically and horizontally with relational databases got solved decades ago and continues to get better. Oracle and SQL Server run practically every huge enterprise database. Do you think those don’t scale?
- throwaway11460 3y ago
  Not sure about Oracle or MS SQL, I work with Postgres. Back when I worked with Oracle and MS SQL it was about one-few huge DB servers, not many smaller ones. To scale the database you need replication, and replication makes it a distributed stateful system that might be harder to reason about than a classic server+database setup. In comparison, scaling a standard stateless backend server that connects to one huge DB server is fairly easy. If you have any guides to point me to I'd appreciate it.
  - gregjor 3y ago
    The state comes from the data, not the procedural code implemented in the database. Issues around replication happen with any distributed database. That doesn’t get worse with stored procedures, which don’t themselves have state.
    - throwaway11460 3y ago
      It's just as hard, indeed. But you don't need to replicate as early with server+DB because your DB is focused on data transactions only. You can have a replica ready for a potential outage and that's all you need to care about for a long time. When you run everything in your DB, replication issues will hit you much earlier. Of course this all depends on what kind of app are you building. If it's an internal tool or something like that, you probably don't need to worry.
      - gregjor 3y ago
        The stored procedures and views and triggers get replicated too. I think you’re imagining that moving business logic into stored procedures will overload the database server sooner than having that in application code. I have never seen that. Instead you get fewer simultaneous connections and less data passed back and forth. Querying a compiled view or stored proc will often use fewer resources than parsing, planning, and optimizing SQL sent from application code. And the RDBMS can make better use of caching with business logic in the database. Scaling and replication may present complications with any architecture, but those aren’t good reasons in my opinion to not put business logic into the RDBMS. Real enterprise-scale databases tend to do that and they actually have scaling and replication issues, not just “may happen someday” issues.
        BenjieGillam 3y ago
        Completely agree; the issue I see with people who try putting logic in the DB ultimately to reject it is they try and bring their procedural programming paradigms (e.g. “for” loops and “if” statements) into the DB and that’s incredibly inefficient, requiring row-by-row looped fetches and ultimately causing their systems to grind to a halt and them to blame the concept of putting logic in the DB. If instead they were to embrace the declarative nature of the database and used efficient patterns such as statement triggers rather than row triggers, common table expressions (CTEs) rather than row-by-row looped processing, set-returning functions and joins rather than row-by-row function calls, etc then they would find that their code is both more expressive and often thousands of times more performant. As you say, embracing this model means less data transferred between cluster and client which can result in reduced load across the entire system without increasing complexity or having to worry about cache invalidation issues. And time spent learning databases is knowledge that will still be useful in a decade or two, it’s well worth investing into.

gregjor 3y ago

There’s nothing wrong with it. Enterprise environments often do it so business rules and logic live in one place, so application code (in multiple languages) doesn’t have multiple implementations. I think a few things came together to make programmers crap on stored procedures and putting business logic in the database: - OOP, which typically treats relational databases as dumb backing store for objects. - No enterprise experience, and weak RDBMS/SQL skills. - Popular open source relational databases with weak and clunky languages and tools, such as MySQL. Compared to Oracle and SQL Server the low-end RDBMSs most programmers learn look hobbled. - Unless you use Oracle or SQL Server or DB/2 you may have trouble debugging procedural code running in the database. If this style works for you don’t worry what other programmers say or what you read online. No matter how you choose to build your application you will get people telling you that you made bad choices.

ilaksh 3y ago

The funny thing is that there was a time (at least with Microsoft) where you were supposedly doing it totally wrong if you didn't use stored procedures enough. And I swear there was a time when every web developer knew that if you wanted a consistent, reliable layout for a home page, you sliced the design up into a table, and stayed away from that new CSS stuff because it was buggy and incompatible with different browsers. The reality is that there are several very different general approaches that can work for any particular information system. Some of them will be more compatible with each other or with certain tools. But I think that for the vast majority of applications, this comes down to trends more than any particular technical issue. I mean, technically speaking there are an effectively infinite number of ways to organize and distribute the code and data that will given the same outputs from given inputs. People use "best practices" as a shortcut to make it easier to judge things or decide how to approach a problem. I think one mark of a good engineer is their ability to adapt to different approaches. This actually requires more intelligence or at least energy because it means they might need to actually read code to decide if the approach makes sense. But it will save people quite a lot of energy to simply say "we [always/never] use stored procedures here!" And also even for brilliant engineers, constantly trying to wrap your head around completely different approaches can be impractical. So programming communities adopt their own worldviews. For the most part, it just means that you spend slightly more time on certain types of problems than the other group. But that group may not realize they are spending time on another thing. So there are trade-offs with every approach. Which is not to say that some frameworks can't save you more time than others. One aspect of this is compatibility. The whole approach may get thrown off if you use stored procedures versus not, depending on the rest of the framework. But I would suggest that there is usually just as much cultural preference as actual technical merit to these debates when you dig deep into the details.

beardyw 3y ago

Don't be too sensitive. Someone will sneer at you whatever you do. If it works, satisfies the business case and is maintainable all is good.