Re: Stored procedures or raw queries
| От | Pavel Stehule |
|---|---|
| Тема | Re: Stored procedures or raw queries |
| Дата | |
| Msg-id | CAFj8pRCoY2KF3fFMCyR+PpALmaKfq+0pNFhhMUY5cRgzuzAXCw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Stored procedures or raw queries (Simon Connah <simon@connah.dev>) |
| Список | pgsql-general |
Hi
st 6. 8. 2025 v 14:04 odesílatel Simon Connah <simon@connah.dev> napsal:
Hi,
I'm pretty new to PostgreSQL and am building a simple website with it.
My main question is whether I should use stored procedures / functions
or whether I should embed raw SQL queries in my backend? I understand
that procedures are faster as it cuts down on the round trip speed and
the database can optimise it better.
On the other hand raw SQL is much easier to manage as you just change
the query in your bankend code without having to apply changes to the
database at deployment time of your backend.
What is considered the best approach? My backend is written in Go if
that makes a difference.
The query executed from the stored procedure is executed with almost the same speed as the query executed from the application.
There can be a small benefit from cached plans, but you can cache plans too from application.
The sense of stored procedures is in possibility
a) an reduce a necessity to transfer data from server to client, the network communication can be reduced
b) with security definer function you can implement some very strong security solution
c) with stored procedures you can implement your own rules related to data integrity and security. Stored procedures are executed on the server. There is no way this can be bypassed.
Just for performance of queries there is not any benefit for stored procedures
Regards
Pavel
If you need any additional information then please let me know.
Simon.
В списке pgsql-general по дате отправления: