MIME-Version: 1.0
References: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
In-Reply-To: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Wed, 6 Aug 2025 14:11:43 +0200
Message-ID: <CAFj8pRCoY2KF3fFMCyR+PpALmaKfq+0pNFhhMUY5cRgzuzAXCw@mail.gmail.com>
Subject: Re: Stored procedures or raw queries
To: Simon Connah <simon@connah.dev>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a0827e063bb14095"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRCoY2KF3fFMCyR%2BPpALmaKfq%2B0pNFhhMUY5cRgzuzAXCw%40mail.gmail.com>
Precedence: bulk

--000000000000a0827e063bb14095
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

st 6. 8. 2025 v 14:04 odes=C3=ADlatel Simon Connah <simon@connah.dev> napsa=
l:

> 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.
>
>
>

--000000000000a0827e063bb14095
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi</div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">st 6. 8. 2025 v=C2=A014:04 od=
es=C3=ADlatel Simon Connah &lt;<a href=3D"mailto:simon@connah.dev">simon@co=
nnah.dev</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">Hi,<br>
<br>
I&#39;m pretty new to PostgreSQL and am building a simple website with it.<=
br>
<br>
My main question is whether I should use stored procedures / functions<br>
or whether I should embed raw SQL queries in my backend? I understand<br>
that procedures are faster as it cuts down on the round trip speed and<br>
the database can optimise it better.<br>
<br>
On the other hand raw SQL is much easier to manage as you just change<br>
the query in your bankend code without having to apply changes to the<br>
database at deployment time of your backend.<br>
<br>
What is considered the best approach? My backend is written in Go if<br>
that makes a difference.<br></blockquote><div><br></div><div>The query exec=
uted from the stored procedure is executed with almost the same speed as th=
e query executed from the application.</div><div><br></div><div>There can b=
e a small benefit from cached plans, but you can cache plans too from appli=
cation.=C2=A0</div><div><br></div><div>The sense of stored procedures is in=
 possibility</div><div><br></div><div>a) an reduce a necessity to transfer =
data from server to client, the network communication can be reduced</div><=
div><br></div><div>b) with security definer function you can implement some=
 very strong security solution</div><div><br></div><div>c) with stored proc=
edures you can implement your own rules related to data integrity and secur=
ity. Stored procedures are executed on the server. There is no way this can=
 be bypassed.=C2=A0</div><div><br></div><div>Just for performance of querie=
s there is not any benefit for stored procedures</div><div><br></div><div>R=
egards</div><div><br></div><div>Pavel</div><div><br></div><div>=C2=A0</div>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
If you need any additional information then please let me know.<br>
<br>
Simon.<br>
<br>
<br>
</blockquote></div></div>

--000000000000a0827e063bb14095--