Bit of a bump, but liked this article and wanted to add that having all your SQL in stored procedure files (and hence having all those SQL create scripts in source control) will make it much easier to setup static analysis of the SQL as part of your CI/CD. DBAs may also find it easier to grok.
Developer Advocate at 🔷Microsoft (PostgreSQL on Azure), developer-focused expertise in databases: 🐘 PostgreSQL, 🚀 YugabyteDB, 🍃 MongoDB, 🅾️ Oracle (OCM), former ♠️ACE Director and🔶AWS Data Hero
Location
Lausanne, Switzerland
Education
Master MIAGE, Université Paris-Sud, France
Work
Developer Advocate at Microsoft (PostgreSQL on Azure - Principal Product Manager)
On the "love or leave 'em?" question, I easily apply a three-valued logic: nothing here is plain wrong or right. It is not about choosing one or another, but just having all possibilites. You may not want to deploy all procedural code into the database. But limiting the database service to SQL-92 statements ignores the past 20 years requirements from business and solutions from database vendors. Reducing a RDBMS to a simple row store goes to the same limits we have seen with NoSQL. Network and context switch latency still matters, even more in distributed databases. And not everything can be coded in declarative SQL without reducing readability amd maintainability. That's the reason the procedural extension has been added to SQL standard, for OLTP applications that needed to scale.
Let's take an example. Imagine a data generation microservice. Run pgbench initialization with -g (client-side generation) or -G (server-side generation, introduced in PostgreSQL 13). There's a huge difference in resources used. This means response time on user side, compute cost on the cloud bill, or energy saving if you care about green computing.
There's a common confusion between procedural language support and stored procedures. You can create stored procedures in declarative SQL only, if you want. And you can run PL/pgSQL from the application code, without deploying anything in the database. Look at the pgbench server-side generation code: client-side deployment, server-side execution.
You mentioned micro-services. This is where I see a case for stored procedures. Micro-services should be light, small, simple, rather than miniatures of client-server monoliths. Some microservices could easily be a REST API (PostgREST), with code in any language of choice (PL/pgSQL, Python, Javascript...). Easy to deploy, to test. to run.
Hi Franck, thanks for the note. I agree -- it's not a binary, 100% of the time decision. (Any decision that's so cut-and-dry quickly becomes a non-decision and we stop having conversations around it!)
I used to be way over on the "always stored procs!" side of the debate and now I'm closer to the other end of the spectrum.
On your point about Microservices, I do feel pretty strongly that having a DB with very little biz logic, then a Data API, then various microservices that consume from the Data API is a very healthy way to go about this. The logic that clearly belongs to the microservice goes in the microservice. The logic that is data-access related goes in the Data API.
This!
In my observation, a lot of reluctancy to use stored procedures has historical and cultural reasons from a time where a relational database, once installed, was very hard to change (a lot of "change processes" around DBs were actually "prevent change processes").
That has changed, and so have the development tools around databases and, as you pointed out, Franck, the features of the databases itself.
Change management complexity, nearly impossible seamless deployments and portability are arguments I had on my list too.
I would add that unit testing is much harder for procedures and missing language features like parallel method execution that you could use if logic is within the service.
I think logic in service also makes the tech stack simplier. So having all logic in the service but one procedure seems just not worth it. Then better pl/sql all the way with a thin rest proxy.
I've seen much too pain with stored procedures to consider them in a new software.
Thanks for an argumented article, there is no place for emotions in these dillemas :)
I find this to be a non-issue most of the time. I think it's more common to rewrite an application in another programming language than to move into another DB system. More often, this is an issue for those who are having database related performance issues but in reality, don't understand proper database design that is why their queries are performing poorly.
Proper normalization is key. If you don't understand normalization, then it doesn't matter if you use stored procedures or ORM. No amount of optimization will work. You will write bad queries and thousands of line of SQL.
You don't need to write hundreds of lines of code in stored procedures if your tables are properly normalized. Better normalization means writing less SQL / application codes.
Business rules are enforced in the DATA. Not in the database.
The better normalized your tables are, the less conditional logic you will need to write. The number one source of bugs in any application is conditional logic. I try to avoid them as much as I can that is why I spend lots of time in designing and normalizing my tables so that I can enforce business rules in the DATA (not in the stored procedures). That allows me minimize conditional logic.
Whenever you find yourself writing lots of conditional logic, that means your need to go back to your database design.
About the point: "Pro: Making re-use of logic possible."
The re-use of logic can be beneficial at a short-term, but once the services/processes start to evolve in different ways you will have a serious maintainece problem.
Have their use but it's 2% of the time. I think intelligent streaming of data from and to the database is almost always the right choice. Why make the database do all the work?
A young man who decided to choose a career as a software engineer from previously social media content, digital marketing, video editor, and product owner.
Bit of a bump, but liked this article and wanted to add that having all your SQL in stored procedure files (and hence having all those SQL create scripts in source control) will make it much easier to setup static analysis of the SQL as part of your CI/CD. DBAs may also find it easier to grok.
Yes, good points.
On the "love or leave 'em?" question, I easily apply a three-valued logic: nothing here is plain wrong or right. It is not about choosing one or another, but just having all possibilites. You may not want to deploy all procedural code into the database. But limiting the database service to SQL-92 statements ignores the past 20 years requirements from business and solutions from database vendors. Reducing a RDBMS to a simple row store goes to the same limits we have seen with NoSQL. Network and context switch latency still matters, even more in distributed databases. And not everything can be coded in declarative SQL without reducing readability amd maintainability. That's the reason the procedural extension has been added to SQL standard, for OLTP applications that needed to scale.
Let's take an example. Imagine a data generation microservice. Run
pgbenchinitialization with-g(client-side generation) or-G(server-side generation, introduced in PostgreSQL 13). There's a huge difference in resources used. This means response time on user side, compute cost on the cloud bill, or energy saving if you care about green computing.There's a common confusion between procedural language support and stored procedures. You can create stored procedures in declarative SQL only, if you want. And you can run PL/pgSQL from the application code, without deploying anything in the database. Look at the pgbench server-side generation code: client-side deployment, server-side execution.
You mentioned micro-services. This is where I see a case for stored procedures. Micro-services should be light, small, simple, rather than miniatures of client-server monoliths. Some microservices could easily be a REST API (PostgREST), with code in any language of choice (PL/pgSQL, Python, Javascript...). Easy to deploy, to test. to run.
Hi Franck, thanks for the note. I agree -- it's not a binary, 100% of the time decision. (Any decision that's so cut-and-dry quickly becomes a non-decision and we stop having conversations around it!)
I used to be way over on the "always stored procs!" side of the debate and now I'm closer to the other end of the spectrum.
On your point about Microservices, I do feel pretty strongly that having a DB with very little biz logic, then a Data API, then various microservices that consume from the Data API is a very healthy way to go about this. The logic that clearly belongs to the microservice goes in the microservice. The logic that is data-access related goes in the Data API.
Appreciate that input!
This!
In my observation, a lot of reluctancy to use stored procedures has historical and cultural reasons from a time where a relational database, once installed, was very hard to change (a lot of "change processes" around DBs were actually "prevent change processes").
That has changed, and so have the development tools around databases and, as you pointed out, Franck, the features of the databases itself.
Change management complexity, nearly impossible seamless deployments and portability are arguments I had on my list too.
I would add that unit testing is much harder for procedures and missing language features like parallel method execution that you could use if logic is within the service.
I think logic in service also makes the tech stack simplier. So having all logic in the service but one procedure seems just not worth it. Then better pl/sql all the way with a thin rest proxy.
I've seen much too pain with stored procedures to consider them in a new software.
Thanks for an argumented article, there is no place for emotions in these dillemas :)
Re: Loss of Database Portability
I find this to be a non-issue most of the time. I think it's more common to rewrite an application in another programming language than to move into another DB system. More often, this is an issue for those who are having database related performance issues but in reality, don't understand proper database design that is why their queries are performing poorly.
Whenever you find yourself writing lots of conditional logic, that means your need to go back to your database design.
Hi, good article.
About the point: "Pro: Making re-use of logic possible."
The re-use of logic can be beneficial at a short-term, but once the services/processes start to evolve in different ways you will have a serious maintainece problem.
Have their use but it's 2% of the time. I think intelligent streaming of data from and to the database is almost always the right choice. Why make the database do all the work?
leave. Why i choose leave? Because untracking, everyone can be imposter is stored procedure fail to execute.