Re: Catching up with performance & PostgreSQL 15

On 11/28/22 21:59, Josh Berkus wrote:
> Hey, folks:
>
> I haven't configured a PostgreSQL server since version 11 (before
> that, I did quite a few).
>
> What's changed in terms of performance configuration since then? Have
> the fundamentals of shared_buffers/work_mem/max_connections changed at
> all?  Which new settings are must-tunes?
>
> I've heard about new parallel stuff an JIT, but neither is that
> applicable to my use-case.
>
Well, well! Long time no see! You'll probably be glad to learn that we
have hints now. Thank you for the following page you created:

https://laptrinhx.com/why-postgresql-doesn-t-have-query-hints-2912445911/

I've used it several times, with great success. It's priceless.

Now, to answer your question: no, fundamentals of shared buffers, work
memory and connections haven't changed. Parallelism works fine, it's
reliable and easy to enable. All you need is to set
max_parallel_workers_per_gather to an integer > 0 and PgSQL 15 will
automatically use parallel plan if the planner decides that it's the
best path. However, to warn you in advance, parallel query is not a
panacea. On OLTP databases, I usually disable it on purpose. Parallel
query will speed up sequential scans, but if your application is OLTP,
sequential scan is a sign of trouble. Parallelism is a data warehouse
only feature. And even then, you don't want it ti be run by multiple
users at the same time. Namely, the number of your CPU resources is
finite and having multiple users launch multiple processes is the best
way to run out of the CPU power fast. Normally, you would package an
output of the parallel query into a materialized view and let the users
query the view.

As for JIT, I've recently asked that question myself. I was told that
PostgreSQL with LLVM enabled performs approximately 25% better than
without it. I haven't measured it so I can't  either confirm or deny the
number.  I can tell you that there is a noticeable throughput
improvement with PL/PGSQL intensive applications. There was also an
increase in CPU consumption. I wasn't doing benchmarks, I was looking
for a generic settings to install via Ansible so I don't have the
numbers, only the feeling. One way of quantifying the difference would
be to run pgbench with and without JIT.

PS:

I am still an Oracle DBA, just as you wrote in the paper.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com