Community PostgreSQL

This guide covers Community PostgreSQL, the standard upstream distribution for standalone on-premises or cloud VM environments. DBtune manages these instances by applying configurations via the ALTER SYSTEM command. For an overview of how the DBtune agent works, see agent overview.

Prerequisites

Before starting the DBtune agent, there are a few setup steps you need to complete on your PostgreSQL instance.

Install the pg_stat_statements extension to collect query metrics:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Make sure it is added to shared_preload_libraries:

SHOW shared_preload_libraries;

note: A PostgreSQL restart is required if pg_stat_statements is not already included in shared_preload_libraries.

Next, verify that the pg_stat_statements.track parameter is properly configured. By default, it is set to top upon creation. You can also set it to all, as DBtune supports both options.

SHOW pg_stat_statements.track;

Parameters tuned

DBtune supports multiple tuning modes that determine which parameters can be optimized. For a detailed explanation of tuning modes, see tuning modes.

Reload-only tuning mode

The following parameters are tuned in reload-only mode:

1. work_mem
2. random_page_cost
3. seq_page_cost
4. checkpoint_completion_target
5. effective_io_concurrency
6. max_parallel_workers_per_gather
7. max_parallel_workers
8. max_wal_size
9. min_wal_size
10. bgwriter_lru_maxpages
11. bgwriter_delay
12. effective_cache_size
13. maintenance_work_mem
14. default_statistics_target
15. max_parallel_maintenance_workers

Restart tuning mode

The following parameters are tuned in restart mode, in addition to all reload-only parameters:

16. shared_buffers
17. max_worker_processes
18. wal_buffers