Re: Stored procedures

------- Original Message -------
On Sunday, August 27th, 2023 at 16:55, Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:

>

>

> On Sun, Aug 27, 2023 at 4:49 AM Simon Connah
> simon(dot)n(dot)connah(at)protonmail(dot)com wrote:
>

> > I'm building a website in Python using the Django framework and am using the ORM at the moment but I can see a few situations where I might need to write raw SQL. My question is do stored procedures execute faster when compared to queries you generate using Python and then send to PostgreSQL? I would assume they would as there is less communication between the two sides and also because PostgreSQL knows the stored procedure ahead of time it should be able to better optimise it.
> >

> > Is that true? Does PostgreSQL use a JIT for stored procedures which make them faster than one-off queries?
>

>

> If your application logic involves many queries for one operation,
> queries that produce the result that's then massaged and used in
> subsequent queries, then yes, your application will benefit from
> bundling that logic in a function/stored procedure and execute purely
> on database server. This is because, as you noted, it reduces network
> round-trips. Hence it will lower your latency for that one operation.
> But also note that since the database is now doing most of the work,
> and because it's limited by the number of CPUs on the database server,
> this can lower your application's throughput; hence this is not
> advisable if you have many more, and always querying client
> connections than the number of CPUs on the server.
>

> If you desire high throughput, and still wish to use functions to keep
> the single-operation latency low, and if your application's workload
> has a very high read:write ratio, you can split your read-only
> workload to use streaming replicas/standby servers, which you can have
> as many as you want (within reason :-).
>

> Instead of, or in addition to functions, you may use CTE (aka WITH
> clause) to bundle many SQL commands into one.
>

> PL/pgSQL is an interpreted language, not a compiled one. It caches and
> reuses the query plans of the SQL commands in the function, but that's
> about it, in terms of optimizations. Unlike a compiled language, it's
> not optimized to eliminate unnecessary operations, etc. As David
> notes, plpgsql has some overhead, as well, so, for example, wrapping
> single SQL statements in a plpgsql function will generally make the
> operation slower than executing that SQL directly.
>

> Postgres does have JIT optimizations for expression evaluation, but
> that benefits all SQL commands, irrespective of whether they are
> directly coming from the client, or wrapped in a function.
>

> Best regards,
> Gurjeet
> http://Gurje.et

Thank you both for your replies. That was really handy.