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.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
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.