MIME-Version: 1.0
References: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
 <12D4341E-023D-420B-8522-DD9B97EC2B90@americanefficient.com>
In-Reply-To: <12D4341E-023D-420B-8522-DD9B97EC2B90@americanefficient.com>
From: Simon Connah <simon@connah.dev>
Date: Wed, 6 Aug 2025 14:36:06 +0100
Message-ID: <CAAxq4K2t=nGq4+AYGxsPMZ+_f5qhjPbYsYxTr6Hj9NXc7JhRNw@mail.gmail.com>
Subject: Re: Stored procedures or raw queries
To: Philip Semanchuk <philip@americanefficient.com>
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/CAAxq4K2t%3DnGq4%2BAYGxsPMZ%2B_f5qhjPbYsYxTr6Hj9NXc7JhRNw%40mail.gmail.com>
Precedence: bulk

Thank you all for your help. It is much appreciated. I'll have a play
around and see how things work out.

Simon.

On Wed, 6 Aug 2025 at 14:17, Philip Semanchuk
<philip@americanefficient.com> wrote:
>
>
>
> > On Aug 6, 2025, at 8:03=E2=80=AFAM, Simon Connah <simon@connah.dev> wro=
te:
> >
> > Hi,
> >
> > I'm pretty new to PostgreSQL and am building a simple website with it.
> >
> > 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.
> >
> > What is considered the best approach? My backend is written in Go if
> > that makes a difference.
> >
> > If you need any additional information then please let me know.
>
> If your only concern is performance, then using stored procedures might b=
e overoptimizing. It=E2=80=99s true that they cut down on overhead, but tha=
t overhead might be meaningless in terms of overall performance. For instan=
ce, if you have low latency between your web server and database server, an=
d 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 wha=
t seems simplest for you (which sounds like raw SQL from your description) =
and then addressing performance problems as they arise. Stored procedures a=
re one tool in the performance toolbox.
>
> good luck
> Philip
>