MIME-Version: 1.0
References: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
 <CAFCRh-8PjVRFQQScR6zKi82cBhzRcOSbkCX3Grj7o_eqf18AGw@mail.gmail.com>
In-Reply-To: <CAFCRh-8PjVRFQQScR6zKi82cBhzRcOSbkCX3Grj7o_eqf18AGw@mail.gmail.com>
From: Marco Torres <mtors25@gmail.com>
Date: Wed, 6 Aug 2025 07:54:33 -0600
Message-ID: <CAG2LZV7BnYbr=nTZCPdUp-UBknaqkd=Mn5BGr1zs0BYBvm1pPQ@mail.gmail.com>
Subject: Re: Stored procedures or raw queries
To: Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000ffa515063bb2ae39"
Archived-At: <https://www.postgresql.org/message-id/CAG2LZV7BnYbr%3DnTZCPdUp-UBknaqkd%3DMn5BGr1zs0BYBvm1pPQ%40mail.gmail.com>
Precedence: bulk

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

In my experience, starting with store procedures in a project might seem
overwhelming. Still, as time passes, and your deliverables output grows, it
becomes easier to maintain and improve your products.

It is just a different paradigm that allows you to focus on improving your
code everywhere. Remember, the database is often neglected until you start
facing problems.

Use indexes and partitions from the beginning. Archiving and replication
might sound unnecessary at the moment; however, dealing with their
implications in production is just a pain.


On Wed, Aug 6, 2025, 7:43=E2=80=AFAM Dominique Devienne <ddevienne@gmail.co=
m> wrote:

> On Wed, Aug 6, 2025 at 2:04=E2=80=AFPM Simon Connah <simon@connah.dev> wr=
ote:
> > 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.
>
> That depends. Our backend is configured on the fly using code and the
> test's fixture, since our schemas are dynamically generated from
> higher-level logical constructs. Even if you use .sql files, you can
> run those yourself at test time. Creating/dropping a DB is fast, it's
> just a folder after all. In this manner, client-side vs server-side
> stored-proc for your code matters much less, as both are "dynamic" any
> time you run. And FWIW, we started having tons of SQL in the
> client-code, and are migrating to server-side for some of it, but for
> privilege escalation via SECURITY DEFINER reasons (because it's a
> 2-tier system), which doesn't sound like it applies to your use case.
> Sprinkling RAISE NOTICE (or similar) in the server-side code helps
> with debugging it, when it grows more complex, be sure to install a
> notice-handler to get them client-side. But if you want to keep things
> simple, sure, keep the SQL client-side. Another use-case for
> server-side is to cut down on round-trips, which matters to us, since
> 2-tier, and the client-side can be "far" away from the server, but in
> a web-app scenario, that's unlikely, so again, doesn't apply to you
> I'm guessing. FWIW. --DD
>
>
>

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

<div dir=3D"auto">In my experience, starting with store procedures in a pro=
ject might seem overwhelming. Still, as time passes, and your deliverables =
output grows, it becomes easier to maintain and improve your products.<div =
dir=3D"auto"><br></div><div dir=3D"auto">It is just a different paradigm th=
at allows you to focus on improving your code everywhere. Remember, the dat=
abase is often neglected until you start facing problems.=C2=A0</div><div d=
ir=3D"auto"><br></div><div dir=3D"auto">Use indexes and partitions from the=
 beginning. Archiving and replication might sound unnecessary at the moment=
; however, dealing with their implications in production is just a pain.</d=
iv><div dir=3D"auto"><br></div></div><br><div class=3D"gmail_quote gmail_qu=
ote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Aug 6, 2025, 7=
:43=E2=80=AFAM Dominique Devienne &lt;<a href=3D"mailto:ddevienne@gmail.com=
">ddevienne@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex=
">On Wed, Aug 6, 2025 at 2:04=E2=80=AFPM Simon Connah &lt;<a href=3D"mailto=
:simon@connah.dev" target=3D"_blank" rel=3D"noreferrer">simon@connah.dev</a=
>&gt; wrote:<br>
&gt; My main question is whether I should use stored procedures / functions=
<br>
&gt; or whether I should embed raw SQL queries in my backend? I understand<=
br>
&gt; that procedures are faster as it cuts down on the round trip speed and=
<br>
&gt; the database can optimise it better.<br>
&gt;<br>
&gt; On the other hand raw SQL is much easier to manage as you just change<=
br>
&gt; the query in your bankend code without having to apply changes to the<=
br>
&gt; database at deployment time of your backend.<br>
<br>
That depends. Our backend is configured on the fly using code and the<br>
test&#39;s fixture, since our schemas are dynamically generated from<br>
higher-level logical constructs. Even if you use .sql files, you can<br>
run those yourself at test time. Creating/dropping a DB is fast, it&#39;s<b=
r>
just a folder after all. In this manner, client-side vs server-side<br>
stored-proc for your code matters much less, as both are &quot;dynamic&quot=
; any<br>
time you run. And FWIW, we started having tons of SQL in the<br>
client-code, and are migrating to server-side for some of it, but for<br>
privilege escalation via SECURITY DEFINER reasons (because it&#39;s a<br>
2-tier system), which doesn&#39;t sound like it applies to your use case.<b=
r>
Sprinkling RAISE NOTICE (or similar) in the server-side code helps<br>
with debugging it, when it grows more complex, be sure to install a<br>
notice-handler to get them client-side. But if you want to keep things<br>
simple, sure, keep the SQL client-side. Another use-case for<br>
server-side is to cut down on round-trips, which matters to us, since<br>
2-tier, and the client-side can be &quot;far&quot; away from the server, bu=
t in<br>
a web-app scenario, that&#39;s unlikely, so again, doesn&#39;t apply to you=
<br>
I&#39;m guessing. FWIW. --DD<br>
<br>
<br>
</blockquote></div>

--000000000000ffa515063bb2ae39--