MIME-Version: 1.0
References: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
In-Reply-To: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
From: Justin Swanhart <greenlion@gmail.com>
Date: Wed, 6 Aug 2025 08:07:49 -0400
Message-ID: <CAJM9iN0YDegRcLG7V6Cv-0pj__B6bB31Of_mp=7RPmdogH6gtw@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="0000000000003676c5063bb131cd"
Archived-At: <https://www.postgresql.org/message-id/CAJM9iN0YDegRcLG7V6Cv-0pj__B6bB31Of_mp%3D7RPmdogH6gtw%40mail.gmail.com>
Precedence: bulk

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

Generally you should use stored procedures when it will reduce the number
of round trips to the database.  Wrapping simple SELECT statements in a
stored proc just adds friction for updating the application especially as
the number of developers grows.

On Wed, Aug 6, 2025, 8:04=E2=80=AFAM Simon Connah <simon@connah.dev> wrote:

> 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.
>
> If you need any additional information then please let me know.
>
> Simon.
>
>
>

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

<div dir=3D"auto">Generally you should use stored procedures when it will r=
educe the number of round trips to the database.=C2=A0 Wrapping simple SELE=
CT statements in a stored proc just adds friction for updating the applicat=
ion especially as the number of developers grows.</div><br><div class=3D"gm=
ail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On W=
ed, Aug 6, 2025, 8:04=E2=80=AFAM Simon Connah &lt;<a href=3D"mailto:simon@c=
onnah.dev">simon@connah.dev</a>&gt; wrote:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft: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>
<br>
If you need any additional information then please let me know.<br>
<br>
Simon.<br>
<br>
<br>
</blockquote></div>

--0000000000003676c5063bb131cd--