Introduction
A stored procedure is a set of SQL statements that is stored on the database server and is available to be executed by name...
For further actions, you may consider blocking this person and/or reporting abuse
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.
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.
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!
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 :)
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.
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.
leave. Why i choose leave? Because untracking, everyone can be imposter is stored procedure fail to execute.
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?