I normally don’t blog about my company EDB, but today I’d like to discuss the updated tuning guide I helped review. You can find this tuning guide here.
Disclaimer 🔗
You will find as many different tuning guides as there are experts out there. Each person has their opinion on the “best starting point.” It depends on what they saw in production. Still, all experts agree on one point: Postgres default settings are very cautious. You shouldn’t run Postgres in production without tuning them a bit.
This tuning guide is only the starting point. You will need to watch your Postgres cluster’s performance. Do this to ensure that it performs well. And, to keep it that way for years to come.
Why is Postgres so bad out of the box? 🔗
You might ask yourself why the PostgreSQL project opted for bad defaults. After all, PostgreSQL hackers aren’t stupid, so there must be a reason!
Why set such conservative values for some parameters? They will make PostgreSQL run poorly in production. Is it so that PostgreSQL consultants can make good money? Of course not! The point is that we want Postgres to run in whatever hardware it’s thrown at. Postgres hasn’t yet determined its resources at installation. The default parameters will use a few resources to run. The project is positive that Postgres can run on a Raspberry Pi, first generation!
Why can’t experts agree on “good” values? 🔗
Look at the parameter shared_buffers. For example, all experts agree its default
value is bad. But, they will struggle to agree on a good value. shared_buffers
is the amount of memory Postgres will use. As you can guess, this is crucial for
performance. If you don’t have enough memory, you will need to swap on disk and
this will slow down your queries. If you allocate too much memory to Postgres, it will take a long time to clean up, which can slow down your queries. (Alongside with the risk that
your host runs out of memory.)
So, what’s the good value?
The reason why experts can’t agree on a “good value” is that it depends on your workload. We can start with various calculations, but results may be inaccurate if your workload is unusual, unlike what we typically see in production.
Again, the tuning guide will give you a formula. But, it’s just a starting point. There might be good improvements by tuning it to fit your workload.
How did we come up with the formulas and values? 🔗
This is also a good question. First, remember that this tuning guide is a review of a previous one written by various EDB employees. This takes into account years of experience at EDB, managing production clusters. Again, the values described in this tuning guide might not be the best for you; they need to be seen as a starting point. Don’t hesitate to benchmark your cluster with your workload. With my colleague Vibhor, I reviewed each recommendation. We asked for feedback from support and consultants. We compared the result with our personal experience. Benchmarking different values would have been useless. The results depend on your hardware, your OS, and your workload. There are way too many unknowns to solve that equation!
To conclude 🔗
This tuning guide is not perfect. Far from it. But, it is a good starting point for new clusters. It will have to be adjusted for your workload and hardware. emember, when it comes to performance, you don’t need the perfect tuning setup. You will never reach it. But, you need your performance to be good enough by your criteria. Trying to over-optimize is wasting your time!