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
