Thread

1. Stored procedures or raw queries

   Simon Connah <simon@connah.dev> — 2025-08-06T12:03:41Z

   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.
   
   Simon.
   
   
   
   

2. Re: Stored procedures or raw queries

   Justin Swanhart <greenlion@gmail.com> — 2025-08-06T12:07:49Z

   Generally you should use stored procedures when it will reduce the number
   of round trips to the database.  Wrapping simple SELECT statements in a
   stored proc just adds friction for updating the application especially as
   the number of developers grows.
   
   On Wed, Aug 6, 2025, 8:04 AM Simon Connah <simon@connah.dev> wrote:
   
   > 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.
   >
   > Simon.
   >
   >
   >
   

3. Re: Stored procedures or raw queries

   Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-06T12:11:43Z

   Hi
   
   st 6. 8. 2025 v 14:04 odesílatel Simon Connah <simon@connah.dev> napsal:
   
   > 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.
   >
   
   The query executed from the stored procedure is executed with almost the
   same speed as the query executed from the application.
   
   There can be a small benefit from cached plans, but you can cache plans too
   from application.
   
   The sense of stored procedures is in possibility
   
   a) an reduce a necessity to transfer data from server to client, the
   network communication can be reduced
   
   b) with security definer function you can implement some very strong
   security solution
   
   c) with stored procedures you can implement your own rules related to data
   integrity and security. Stored procedures are executed on the server. There
   is no way this can be bypassed.
   
   Just for performance of queries there is not any benefit for stored
   procedures
   
   Regards
   
   Pavel
   
   
   
   >
   > If you need any additional information then please let me know.
   >
   > Simon.
   >
   >
   >
   

4. Re: Stored procedures or raw queries

   Philip Semanchuk <philip@americanefficient.com> — 2025-08-06T13:16:50Z

   
   > On Aug 6, 2025, at 8:03 AM, Simon Connah <simon@connah.dev> wrote:
   > 
   > 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 be overoptimizing. It’s 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’ wait time is due to execution time, not to communication between servers. Stored procedures won’t help you much.
   
   If you’re 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. 
   
   good luck
   Philip
   
   
   

5. Re: Stored procedures or raw queries

   Simon Connah <simon@connah.dev> — 2025-08-06T13:36:06Z

   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 AM, Simon Connah <simon@connah.dev> wrote:
   > >
   > > 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 be overoptimizing. It’s 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’ wait time is due to execution time, not to communication between servers. Stored procedures won’t help you much.
   >
   > If you’re 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.
   >
   > good luck
   > Philip
   >
   
   
   
   

6. Re: Stored procedures or raw queries

   Dominique Devienne <ddevienne@gmail.com> — 2025-08-06T13:42:37Z

   On Wed, Aug 6, 2025 at 2:04 PM Simon Connah <simon@connah.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
   
   
   
   

7. Re: Stored procedures or raw queries

   Marco Torres <mtors25@gmail.com> — 2025-08-06T13:54:33Z

   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@gmail.com> wrote:
   
   > On Wed, Aug 6, 2025 at 2:04 PM Simon Connah <simon@connah.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
   >
   >
   >