<- Back to Blog

How Stormatics optimized PostgreSQL workloads with DBtune

DBtune ·
How Stormatics optimized PostgreSQL workloads with DBtune

This article originally appeared as “PostgreSQL Tuning and DBtune” on Stormatics’ blog.

Parameter tuning in PostgreSQL involves the adjustment of various configuration settings inside postgresql.conf file which dictates how the database operates. These parameters affect many aspects of the database’s operation which includes memory allocation, query planning, connection handling and disk I/O operations. Proper tuning ensures that PostgreSQL runs efficiently, making full use of the available hardware resources

Why Is Parameter Tuning Essential?

PostgreSQL database is installed with default parameter settings that can work with a wide range of applications. However, the needs of each application can vary, which is influenced by factors such as the nature of the data, how it is accessed (whether the workload is more read or write intensive) and how the queries are designed.

The main goal of adjusting parameters is to make the database work better. This means speeding up query response times and more effective use of resources like memory and processing power. When there is a load, a well-tuned database can manage extra work without slowing or crashing it down. Before investing in additional hardware to address performance problems, it’s important to first assess whether the database is making full use of the existing hardware.

Understanding DBtune

DBtune is an AI-powered service designed to automate and optimize the process of database parameter tuning. It utilizes advanced algorithms and machine learning techniques to analyze your database workload and suggests optimal configuration. You can check it out here: https://www.dbtune.com/

Benefits of Using DBtune

DBtune continuously monitors PostgreSQL catalog views, hardware usage (Memory/Disk/CPU) and then adjusts settings to maintain optimal performance:

  • DBtune fully automates the parameter tuning process, eliminating manual effort and reduces the time required for tuning.
  • DBtune provides tuning suggestions as per your specific workload and hardware setup which makes sure that your PostgreSQL database is operating at its peak efficiency.
  • DBtune minimizes the risk of incorrect configuration, which can lead to performance degradation or downtime.

Which PostgreSQL parameters are modified by DBtune?

When configuring a database for optimization with DBtune, it asks if it is permissible to restart the database. This is because some PostgreSQL parameters only take effect after a restart. If DBtune is authorized to restart the database, it will adjust the following parameters:

  1. shared_buffers: Specifies how much dedicated memory is allocated for caching data within PostgreSQL.
  2. work_mem: Determines the amount of memory used for operations within individual queries, such as sorting and hashing.
  3. max_wal_size: Specifies the maximum size of the Write-Ahead Logging (WAL) files.
  4. seq_page_cost: It represents the cost of reading sequential disk pages. And helps the planner in deciding the most efficient method to execute a query
  5. random_page_cost: Similar to seq_page_cost, but for random disk page reads. A lower value can make the planner favor index scans over sequential scans.
  6. checkpoint_completion_target: This setting is used to spread out disk I/O and reduce peak load during checkpoint operations.
  7. effective_io_concurrency: This parameter helps PostgreSQL optimize its use of available I/O channels. Note that this is only used for bitmap operations.
  8. bgwriter_lru_maxpages: Determines the maximum number of buffer pages that the background writer process can write per round. It works in conjunction with checkpointer process
  9. max_worker_processes: The maximum number of background processes that the database can support at any given time. It’s an important setting because these worker processes are used for a variety of purposes, including carrying out parallel queries, autovacuum workers etc.
  10. max_parallel_workers: Sets the maximum number of parallel workers that can be used globally in PostgreSQL. This parameter value is bounded by max_worker_processes:
  11. max_parallel_workers_per_gather: Specifies the maximum number of parallel processes that can be used for a single node of a query. This parameter value is bounded by max_parallel_workers.

If DBtune is not allowed to restart the PostgreSQL database instance, it will focus on following parameters as they only need a reload signal to take effect.

  • work_mem
  • max_wal_size
  • seq_page_cost
  • random_page_cost
  • checkpoint_completion_target
  • effective_io_concurrency
  • bgwriter_lru_maxpages

DBtune in Action

Create a new VM on AWS (m5d.xlarge)

We are using Ubuntu focal distribution to run this test.

Install git repo

git clone https://github.com/dbtuneai/synthetic_workload.git

Set disk mountpoint variable

export VAR=nvme1n1 # Can be found using lsblk command

Output of lsblk command:

loop0      7:0 0  24.9M  1 loop /snap/amazon-ssm-agent/7628
loop1      7:1 0  55.7M  1 loop /snap/core18/2790
loop2      7:2 0  40.9M  1 loop /snap/snapd/20290
loop3      7:3 0  63.5M  1 loop /snap/core20/2015
loop4      7:4 0  91.9M  1 loop /snap/lxd/24061
nvme1n1  259:0 0 139.7G  0 disk
nvme0n1  259:1 0   100G  0 disk
├─nvme0n1p1  259:2 0  99.9G  0 part /
├─nvme0n1p14 259:3 0 4M  0 part
└─nvme0n1p15 259:4 0   106M  0 part /boot/efi

Setup synthetic workload

cd synthetic_workload
source setup_ubuntu_20.sh

Load synthetic data from BenchBase

cd benchbase-postgres
python3 loader.py --benchmark resourcestresser

Run benchmark

cd benchbase-postgres
python3 runner.py --benchmark resourcestresser


Tuning Session

For DBtune integration, first create a trial account on DBtune Create Account.

And follow the steps on DBtune Documentation.

Following options were selected with DBtune as preferred recommendations.

  • Database = PostgreSQL
  • Database Hosting = Self-Managed
  • Restarts = on
  • Tuning Target = Query Runtime (You can also choose Throughput to focus on increasing TPS)

Let your workload run for 5-10 minutes and then start the DBtune process.

sudo su # Switch to root user
python3 dbtune.py

click on the Start Tuning button on GUI.

Total 4 tests were performed to check DBtune recommendations. The tuning session completed 30 iterations, each one accompanied by modifications to the postgresql.conf file and a subsequent restart. Each iteration ran for an average time of 7 minutes. In each iteration, it monitors postgresql performance based on current parameters, query execution times, pg_stat_statement view statistics and hardware usage.

The current nature of the workload appears to be focused solely on executing updates on the iotable, involving 50 concurrent users operating in Repeatable Read mode.

Table Structure:


Id int PK,

Value text


Id int PK,

Value1 Text,

Value2 Text,



Value 15 Text

Table sizes:

Iotable: 37GB

Iosmalltable: 730MB

Test Case #1

This test began using the default values set in the postgresql.conf file.

ParametersDefault PostgreSQL ConfigurationDBtune Best Recommendation
Work_mem4MB78 MB
Max_wal_size1GB8.0 GB
Throughput33 txn/sec160 txn/sec
Query runtime17.9 ms1.3 ms
Query Runtime Improvement Ratio1.0x13.6x
Throughput(txn/sec) Improvement Ratio1.0x4.8x
Iterations requiredN/A30

So DBtune gave the best setting after 30 iterations. Here we got 13.6x improvement, reducing the query runtime to 1.3 ms and increased the overall throughput by 4.8x.

Test Case # 2

This time, we manually tuned the configuration of the database instance to check how DBtune can improve the performance.

The parameters were selected based on the following considerations:

  • The workload type, which involved updates.
  • checkpoints frequency and background worker statistics as they are disk based operations.
  • PostgreSQL hardware resource usage.
  • Index vs Sequential scans on the table.
  • Utilization of parallel workers.
  • Sorting or Hashing operations used by queries.
ParametersPostgreSQL Configuration (manually tuned)DBtune Best Recommendation
Throughput145 txn/sec169 txn/sec
Query runtime5.3 ms1.7 ms
Query Runtime Improvement Ratio1.0x3.05x
Throughput(txn/sec) Improvement Ratio1.0x1.16x
Iterations requiredN/A23

DBtune gave the best setting after 23 iterations. Here we got 3.05x improvement, reducing the query runtime to 1.7 ms and increased the overall throughput by 1.16x.

Test Case # 3

This time, we tuned above parameters as well as some external parameters (Not tuned by DBtune) defined below:

wal_compression = 'on'
effective_cache_size = '8GB'
min_wal_size = '4GB'
maintenance_work_mem = '128MB'
bgwriter_delay = '100ms'
Fillfactor set for IOtable = 80

In order to enable hot updates for the table, we’ve configured a fill factor of 80, allowing for in-place updates without index modification. Background writers will now wake up every 100ms to flush data from shared_buffers to disk,thus reducing the checkpoint workload. Wal_compression is enabled to reduce the high volume of walfiles and checkpoint requests but at the cost of some extra CPU.

Here are the results:

ParametersPostgreSQL Configuration (manually tuned)DBtune Best Recommendation
Throughput157 txn/sec146 txn/sec
Query runtime3.4 ms1.5 ms
Query Runtime Improvement Ratio1.0x2.2x
Throughput(txn/sec) Improvement Ratio1.0x1.0x
Iterations requiredN/A27

DBtune gave the best setting after 27 iterations. Here we got 2.2x improvement and the query runtime is reduced to 1.5 ms. However, it did not benefit the throughput.

Test Case # 4

This time, we are using DBtune with Restarts = off and PostgreSQL default configuration.

Tuning session report:

ParametersPostgresql Default ConfigurationBest Configuration Query Runtime
Max_wal_size1GB32.0 GB
Throughput76 txn/sec195 txn/sec
Query runtime19.1 ms6.4 ms
Query Runtime Improvement Ratio1.0x2.6x
Throughput(txn/sec) Improvement Ratio1.0x2.5x
Iterations requiredN/A30

DBtune gave the best setting after 30 iterations. Here we got 2.6x improvement, reducing the query runtime to 6.4ms and increased the overall throughput by 2.5x.

When comparing Test case 1 and 4, there is a significant discrepancy in performance, with restarts set to on resulting in a 13.6x increase, whereas having restarts set to off yields only a 2.6x improvement.


The main responsibility of a Database engineer is to adjust settings based on certain factors. This includes understanding if the workload is more about processing transactions quickly (OLTP) or analyzing data (OLAP), understanding the dataset size, and monitoring the frequency of data modifications such as inserts, updates, and deletes. Internal views inside PostgreSQL such as pg_stat_statements, pg_stat_all_tables, and pg_stat_bgwriter helps in getting this information, while EXPLAIN ANALYZE assists in understanding query planning requirements. Additionally, it’s crucial to monitor hardware usage, including disk, memory, and CPU performance, with tools like iostat and iotop or graphical tools such as Grafana and Zabbix.

Services like DBtune are transforming the way we tune databases, making it easier, automated, faster, and impactful. This means database engineers have more time to work on other important aspects of managing databases instead of spending a lot of time on tuning database configurations.


Get started

Schedule a 30 minute demo and discover how DBtune can improve your database performance.