What are the benefits of stored procedures in PostgreSQL?

Question

PostgreSQL

Hi all!
Do you think there are any real benefits to using postgresql storage?
For example, I write work with data on JDBC, and it would be possible to pack large transactions into storages. Large transactions mean many inserts into different tables and updates of many records in one transaction.
Will there be a performance gain? And in general, what kind of rake can lie in wait on this path?

Answer 1

It is categorically not recommended to put business logic in the database. Think about the complexity of debugging.

Answer 2

There will definitely be a performance gain, at least due to the fact that the overhead costs for connecting to the database and transferring information over the network are removed. But the complexity of debugging these same stored procedures will immediately increase, and the cost of an error, especially on large databases. Stores IMHO are very difficult to cover with automatic tests

Answer 3

If there is really a lot of business logic, then yes, but if only CRUD. In one case, the request comes from java (the same sql), and in the other it is stored in the database. Unit tests will still have mocks. Or is there some other difficulty?
How is it stored in postgres? Do they compile in it? For example, in SQL server, storages are compiled, which increases performance.

Answer 4

I remember in 2005 it was fashionable to use storage. And there were plenty of holivars on this topic. Many did everything CRUD through storages and pulled through ado.net (hello dotnetchik)

Answer 5

The main benefit from storage is less traffic. If you don't care about traffic, you probably don't need them. Sometimes storages still restrict access to the database. All requests then go through . It is impossible to do something unforeseen in case of hacking. It also has the right to life.

Answer 6

It’s clear, in general, do you think that storables are actively used in the postgresql user community or are they legacy, few people use them and in general they are poorly developed? The question is rhetorical, of course, but still.