MIME-Version: 1.0
References: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
In-Reply-To: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Wed, 6 Aug 2025 15:42:37 +0200
Message-ID: <CAFCRh-8PjVRFQQScR6zKi82cBhzRcOSbkCX3Grj7o_eqf18AGw@mail.gmail.com>
Subject: Re: Stored procedures or raw queries
To: Simon Connah <simon@connah.dev>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-8PjVRFQQScR6zKi82cBhzRcOSbkCX3Grj7o_eqf18AGw%40mail.gmail.com>
Precedence: bulk

On Wed, Aug 6, 2025 at 2:04=E2=80=AFPM Simon Connah <simon@connah.dev> wrot=
e:
> 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