Take care of my PostgreSQL optimization for me? - A DBtune overview

Why do people even tune their databases?

Databases are everywhere in the age of computing. Whether it is an e-commerce platform, a social network, a recipe website, or maybe an AI chatbot service; the database is giving its best for storing, manipulating and retrieving the information that makes the application work and keeping the customers happy. Or is it actually giving its best???

Like with most topics in engineering (and in life actually), there is no magical, one-size-fits-all technique for optimizing the thing at hand.

Whether you try to build the perfect car which rocks in all conditions, or maybe the perfect CPU scheduler which gets the most out of the CPU without compromising anything, it won’t actually turn out to be something realistic.

Because it is the nature from which engineering is born; you sacrifice on one side and gain on the other. It all comes down to finding the sweet spot for a specific scenario.

In the case of databases, scenarios are really diverse:

• If you’re running a ticket booking system where thousands of fans and bots are all waiting for the event tickets to become available—where thousands of tiny transactions happen in a short period—you start to care more about transaction throughput, cache hits and database locks.
• In another scenario, where you run analytics on the last 10 years of sales to get some insight, millisecond transaction times become less important and you start to care more about resource utilization (CPU, disk) and query runtimes.

And these are super-generic scenarios which are used as examples in many database courses. In production environments… things get much messier.

How did engineers find the sweet spot then; trial -> error -> try again?

Actually, that was the case. The most instinctive action when faced with an optimization problem is to:

• Observe how it behaves without touching anything.
• Play with the knobs a little. If it goes bad, go back to the defaults.
• Play with the knobs again. Did it get better? Note it down and move on to play with other knobs.

Then comes another term; automation. It is maybe the longest-lasting buzzword that has been at its peak since the Industrial Revolution! Even the modern computer is built upon that idea: automation of thought.

In the database world, especially in the PostgreSQL world, there have also been some efforts to automate the optimization process.

Solutions like PGTune and Cybertec PostgreSQL Configurator have been developed to make administrators’ jobs easier when tuning a PostgreSQL server. You specify the amount of available resources (CPU, RAM, disk specs, etc.), expected database traffic and workload type, and they generate a recommended PostgreSQL configuration.

These are great tools to find a reasonable starting PostgreSQL configuration based on the available hardware and workload. DBAs and sysadmins then adjust the recommendations based on workload metrics and their prior experience.

At this point, some questions come back into the automation-maniac engineer’s mind:

• DBAs and sysadmins are still playing with the knobs. Wasn’t it all about automating thought?
• These recommendation generators are based on our initial basic answers. How can they specialize for my specific workload without knowing my query performance?
• How about an environment where performance metrics are collected automatically, processed and analyzed intelligently, and pushed back into my PostgreSQL server without me touching any knob?
• Why not use that new big thing called artificial intelligence to analyze everything intelligently?

Collect -> Analyze -> Generate -> Apply -> Again!

Let’s address the big blue elephant in the room. This idea of AI-automated database tuning was the starting point for the DBtune team.

An idea which came to many DBAs’ and sysadmins’ minds while they were trying hard to find the optimal point day and night, wishing there were a tuning machine (or person) that could do the trial-and-error process automatically on the side, learn from past mistakes and make smarter decisions the next time.

DBtune implemented this idea with the following components:

• Metric collector and configuration manager (in other words, the DBtune agent):

  • Collects performance metrics from the PostgreSQL server.
  • Sends the metrics to DBtune servers for analysis.
  • Pulls the generated configurations from DBtune servers.
  • Applies the new optimized configuration to the PostgreSQL server.

• DBtune Cloud:

  • Gets the collected metrics and generates a configuration using its prior knowledge about the particular PostgreSQL server and database tuning in general.
  • Evaluates the performance outcomes of previously applied configurations and trains itself.
  • Generates a new, more optimized configuration.

Wait! I heard the AI thing could end the world, how can I hand it my precious databases?

Data, production, automation and AI; when these terms come together, things can take a very unexpected turn very quickly. But there is a way to enjoy them all together: guardrails.

DBtune agent only collects performance metrics, system metrics, database statistics, configuration parameters, hardware information and schema metadata. It does not collect row data, stored values or personal data (For more detailed information about agent monitoring and privacy).

Even in the worst-case scenario where the DBtune agent turns into a demon and tries to get all the data for itself, it simply is not allowed to.

On a typical setup, the agent is granted the pg_monitor role, which provides read-only access to monitoring views and functions:

CREATE USER dbtune_agent WITH PASSWORD '<password>';
GRANT pg_monitor TO dbtune_agent;

To enable automated tuning, the agent is then granted ALTER SYSTEM privileges on a set of configuration parameters, allowing it to modify only those parameters without requiring full superuser privileges:

GRANT ALTER SYSTEM ON PARAMETER work_mem TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER random_page_cost TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER seq_page_cost TO dbtune_agent;
...
...
...

Additionally, it is granted permission to execute pg_reload_conf(), enabling configuration changes that do not require a server restart to take effect immediately:

GRANT EXECUTE ON FUNCTION pg_catalog.pg_reload_conf() TO dbtune_agent;

While being restricted from seeing the actual data, DBtune also has some other guardrailing features to prevent PostgreSQL server from suffering in a production environment:

• Memory: If a configuration causes memory usage to exceed 90% of available RAM, DBtune automatically goes back to baseline configuration
• Performance degradation: If average query runtine doubles, or transactions per second drops below %95 of baseline, it gets prompted to DBtune to generate new configuration, immediately
• User defined guardrails: Custom defined guardrails for monitoring specifically chosen metrics

I still want to be controlling the rudder, what if that AI guy gets hallucinative and stuff?!

To give the rudder back to DBAs and sysadmins (and to remind AI who is the master), there is this thing called “Human in the loop”.

When this feature is enabled;

• Whenever a new optimized configuration got generated, instead of applying it right into the PostgreSQL server it waits for the user’s approval.
• While it makes pressing the big red “REJECT” button available if the new configuration has not seen reasonable; it also presents a great opportunity to comprehend each iteration and each optimization before they get into the precious production environments.

Trying out DBtune

I have discovered DBtune and the optimization-as-a-service idea in general at the PostgreSQL Ankara 2026 event. Queries flowing on the terminal emulator with the DBtune web interface on the side got me excited as always to check out their stand.

Later on; we had a quick chat with Farshad from the DBtune team about DBtune, automating database optimization, the agent architecture, and much more after my dear friend Yasin also came along.

One of the topics we discussed was; how DBtune can be experienced in a personal setup and which benchmarks to run on the PostgreSQL server to see DBtune doing its optimizations.

For individual developers (like myself) DBtune supports these installation options:

• Container: DBtune agent works inside a container. This is preferable in scenarios where deployments are handled inside containers and PostgreSQL servers also inside of containers of their own.

  • Since the image is available publicly; it makes isolation, health/deployment management and updates much more easier.
  • I have experienced DBtune this way, using Podman as my container management engine.
  • On Kubernetes clusters, where PostgreSQL server instances are scaled up and down; where high availability is the most crucial thing, containerized agent is the way to GO.

• Native binary (Linux/macOS): DBtune agent is installed as an executable directly on the host machine. It becomes handy in environments where PostgreSQL servers and other deployed applications are directly installed on the host machine.

  • Pre-compiled executables are offered by DBtune team and since DBtune agent is an open-source project (written in Go btw), self-compiling the source code also is an option. Just a good-old, dead-simple go build command takes care of all.

Demo script and DBtune Docker container image utilizes Docker socket to be able to manage containers. For daemonless engines like Podman - even if missing socket does not break the demo script and the container - some modifications may be needed.

The demo script essentially does these things:

• Includes variables like API_KEY, DATABASE_ID, CONTAINER_NAME to authenticate with DBtune Cloud
• Creates the PostgreSQL container
• Creates the benchmarking container, which populates the database for chosen benchmark type (epinions by default) and after the initializition phase, puts the workload via queries on PostgreSQL server
• Creates and then starts the DBtune agent container, using the authentication environment variables as mentioned earlier

I chose to improve the “average query runtime” using the recommended settings from DBtune team and started the tuning session on my dedicated server which resided in Hetzner cloud.

I have let DBtune to find the most optimized configuration for ~2 hours. As it is seen on the bar chart, quick improvement right after the tuning start - on the 1st iteration - has quite impressed me.

In order to match the needs of the epinions benchmark, - as seen on the table above - the model has done significant changes on parameters. When we compare baseline and best configurations, these optimizations have shown themselves as improvement on performance metrics.

When we go back to beginning of this blog, and start thinking about the automation of thinking, finding the sweet spot, playing with the knobs little by little, I can certainly tell DBtune is trying to solve a problem which has existed for a long time, in the PostgreSQL world.

On top of all that, their collaboration with the open-source community, their efforts to make it easier for us to understand what is going on at the background, design of dead-simple to use web interface and their enthusiastic relations with curious people makes it much more valuable as an optimization-as-a-service solution.