Re: Stored procedures or raw queries

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 AM Dominique Devienne <ddevienne(at)gmail(dot)com> wrote:

> On Wed, Aug 6, 2025 at 2:04 PM Simon Connah <simon(at)connah(dot)dev> 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
>
>
>