Subject: Re: Stored procedures or raw queries

From: Dominique Devienne <[email-masked]>
Date: Wed, 6 Aug 2025 15:42:37 +0200

On Wed, Aug 6, 2025 at 2:04 PM Simon Connah <[email-masked]> wrote:
> 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

---

From: Marco Torres <[email-masked]>
Date: Wed, 6 Aug 2025 07:54:33 -0600

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.