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;

Database user setup

The DBtune agent requires a PostgreSQL user with permissions to collect metrics and apply configuration changes. You can use an existing user or create a dedicated one — a dedicated user is recommended so access can be managed and revoked independently.

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

note

GRANT ALTER SYSTEM ON PARAMETER was introduced in PostgreSQL 15. On PostgreSQL 12–14 this syntax does not exist — superuser is the only option.

The permissions required to apply configuration changes depend on your PostgreSQL version and the tuning mode you plan to use.

PostgreSQL 15+ — least-privilege setup (recommended for production)

On PostgreSQL 15+, you can grant per-parameter ALTER SYSTEM privileges without superuser.

For reload-only tuning mode:

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;
GRANT ALTER SYSTEM ON PARAMETER checkpoint_completion_target TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER effective_io_concurrency TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER max_parallel_workers_per_gather TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER max_parallel_workers TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER max_wal_size TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER min_wal_size TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER bgwriter_lru_maxpages TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER bgwriter_delay TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER effective_cache_size TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER maintenance_work_mem TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER default_statistics_target TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER max_parallel_maintenance_workers TO dbtune_agent;

-- Required to reload configuration after applying changes
GRANT EXECUTE ON FUNCTION pg_catalog.pg_reload_conf() TO dbtune_agent;

Additionally, for restart tuning mode:

GRANT ALTER SYSTEM ON PARAMETER shared_buffers TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER max_worker_processes TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER wal_buffers TO dbtune_agent;
GRANT ALTER SYSTEM ON PARAMETER huge_pages TO dbtune_agent;

PostgreSQL 12–14 — superuser required

On PostgreSQL 12–14, ALTER SYSTEM requires superuser:

ALTER USER dbtune_agent WITH SUPERUSER;

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
19. huge_pages