Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: Stored procedures or raw queries
From: Philip Semanchuk <philip@americanefficient.com>
In-Reply-To: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
Date: Wed, 6 Aug 2025 09:16:50 -0400
Cc: pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <12D4341E-023D-420B-8522-DD9B97EC2B90@americanefficient.com>
References: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
To: Simon Connah <simon@connah.dev>
Archived-At: <https://www.postgresql.org/message-id/12D4341E-023D-420B-8522-DD9B97EC2B90%40americanefficient.com>
Precedence: bulk


> On Aug 6, 2025, at 8:03=E2=80=AFAM, Simon Connah <simon@connah.dev> =
wrote:
>=20
> Hi,
>=20
> I'm pretty new to PostgreSQL and am building a simple website with it.
>=20
> 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.
>=20
> 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.
>=20
> What is considered the best approach? My backend is written in Go if
> that makes a difference.
>=20
> If you need any additional information then please let me know.

If your only concern is performance, then using stored procedures might =
be overoptimizing. It=E2=80=99s true that they cut down on overhead, but =
that overhead might be meaningless in terms of overall performance. For =
instance, if you have low latency between your web server and database =
server, and your typical query takes 5-6 seconds to execute, then most =
of your users=E2=80=99 wait time is due to execution time, not to =
communication between servers. Stored procedures won=E2=80=99t help you =
much.

If you=E2=80=99re building a simple web site, I suggest starting with =
what seems simplest for you (which sounds like raw SQL from your =
description) and then addressing performance problems as they arise. =
Stored procedures are one tool in the performance toolbox.=20

good luck
Philip=