🐗 🔰 🚖 The harm of stored procedures 🧓🏿 🏹 🦒

In the chat of the podcast "Zinc Prod" they dropped an article about how some guys transferred all the business logic to stored procedures in pl / pgsql. And since the article had many advantages, it means that there are people, and maybe even the majority of them, who positively perceived such refactoring.

I will not spread my thoughts along the tree, but immediately throw in a bunch of disadvantages of using stored procedures.

Cons of stored procedures

Versioning

If, in the case of php code, you can simply switch to another branch in git and see what happened, then the stored procedures still need to be pushed into the database. And traditional migrations will not help here: if you write all changes to the storage as a new CREATE OR REPLACE PROCEDURE, then there will be hell on the code review: there is always a new file, which is incomprehensible to compare with. Therefore, you will have to look for some additional tools or write your bike.

The pl / pgsql language itself

It is an outdated procedural language from the nineties that has not evolved at all. No OOP or FP or whatever. Syntax without the slightest hint of syntactic sugar.

For example, variables must be declared at the beginning of a procedure, in a special DECLARE block. This is what our grandfathers did, there is a certain nostalgia for the Pascal language in this, but thanks, not in 2020.

Compare two functions that do the same thing in php and pl / pgsql:

CREATE OR REPLACE FUNCTION sum(x int, y int)
    RETURNS int
    LANGUAGE plpgsql
AS $$
DECLARE
    result int;
BEGIN
    result := x + y;
    return result;
END;
$$;

function sum(int $x, int $y): int
{
    $result = $x + $y;
    return $result;
}

About 2-3 times more scribble.

Also, the language is interpreted, no JIT, etc. (correct me if anything has changed in recent versions). Those. everything is very slow and sad. If you use some kind of storage, then in pure SQL or v8 (i.e. javascript).

Debugging

Believe me, debugging php code is 100500 times easier. You just corrected something and watch the result. You can overlay echo or see what's there through xdebug right in the IDE.

Debugging stored procedures is inconvenient. This must be done in pgadmin (by enabling a special extension). PgAdmin is far from PHPstorm in terms of convenience.

Logging and error handling

Forget about beautiful json with trace falling from stdout, and then to graylog and sentry. And so that all this happens automatically, giving the user a 500 error if the controller does not catch the exception.

In pl / pgsql storage you will do everything manually:

GET DIAGNOSTICS stack = PG_CONTEXT;
      

        
        
        
      

    
RAISE NOTICE E'---   ---\n%', stack;

Collecting metrics

You cannot, as in golang, just add the / metrics endpoint, which will be sucked in by Prometheus, where you cram business and other metrics for monitoring. I just don't know how to get out with pl / pgsql.

Scaling

Stored procedure execution wastes resources (eg CPU) of the database server. In the case of other languages, you can move the logic to other nodes.

Dependencies

In php, using the composer package manager, you can pull up the desired library from the Internet in one move. Just like in js it will be npm, in Rust it will be cargo, etc.

In the pl / pgsql world, one has to suffer. There is simply no dependency manager in this language.

Frameworks

In the modern world, a web application is often not written from scratch, but assembled on the basis of a framework using its components. For example, on Laravel you have routing, request validation, templating engine, authentication / authorization, 100,500 helpers for all occasions, etc. out of the box. To write it all by hand from scratch, in an outdated language - no thanks.

There will be a lot of bicycles, which will have to be maintained later.

Unit tests

It is even difficult to imagine how convenient it is to organize unit tests in pl / pgsql stores. I have never tried it. Please share in the comments.

Refactoring

Although there is an IDE for working with a database (Datagrip), refactoring tools are much richer for common languages. All kinds of linters, tips for simplifying code, etc.

A small example: in those pieces of code that I gave at the beginning of the article, PHPStorm gave a hint that the variable is $resultoptional, and you can just do it. return $x + $y;

In the case of plpgsql - silence.

Pros of stored procedures

There is no overhead for driving intermediate data along the backend-DB path.
The query plan is cached in stored procedures, which can save a couple of ms. those. as a wrapper over a query, sometimes it makes sense to do it (in rare cases, and not on pl / pgsql, but on bare sql), if there is a frenzied highload, and the query itself is executed quickly.
When you write your extension for postgres, you cannot do without storage.
When you want to hide some data for security reasons, giving the application access to only one or two storages (rare case).

conclusions

In my opinion, stored procedures are needed only in very, very rare cases when you are sure that you cannot do without them at all. In other cases, you will only complicate the life of the developers, and significantly.

I would understand if in the original article some of the logic was transferred to SQL, this can be understood. But why the storage is a mystery.

I would be glad if you think that I am wrong or if you know any other situations related to stored procedures (both pros and cons), and write about it in the comments.

The harm of stored procedures