Automatic configuration of PostgreSQL parameters (Auto-tuning)
Auto-tuning automatically configures PostgreSQL internal parameters based on the allocated resources: RAM, vCPU (virtual CPU), storage type and enabled extensions. No manual intervention is required.
This configuration is applied when a cluster is created and automatically recalculated when resources are modified.
Adjusted parameters
Memory
| Parameter | Formula / Value | Context |
|---|---|---|
shared_buffers | 25 % of RAM, capped at 8 GB | postmaster (restart required) |
effective_cache_size | 75 % of RAM | user (immediate) |
work_mem | (RAM − shared_buffers) / ((max_connections + max_worker_processes) × 6), minimum 4 MB | user (immediate) |
maintenance_work_mem | RAM / 16, minimum 64 MB, maximum 8 GB | sighup (reload) |
autovacuum_work_mem | 2 GB if maintenance_work_mem ≥ 2 GB, otherwise unset | sighup (reload) |
Parallel queries (vCPU ≥ 4)
These parameters are set only if the cluster has at least 4 vCPU. Smaller instances use the PostgreSQL default values.
| Parameter | Formula / Value | Context |
|---|---|---|
max_worker_processes | vcpuCount | postmaster (restart required) |
max_parallel_workers | vcpuCount | postmaster (restart required) |
max_parallel_workers_per_gather | ⌈vcpuCount / 2⌉, capped at 4 | user (immediate) |
max_parallel_maintenance_workers | ⌈vcpuCount / 2⌉, capped at 4 | user (immediate) |
Autovacuum
| vCPU | autovacuum_max_workers |
|---|---|
| < 16 | Unset (default value: 3) |
| 16 - 31 | 4 |
| ≥ 32 | 5 |
Storage
| Storage type | random_page_cost | effective_io_concurrency |
|---|---|---|
| Standard (HDD) | 4.0 | 2 |
| GP2 / IO1 (SSD) | 1.1 | 256 |
WAL (Write-Ahead Logging)
WAL file sizes:
min_wal_size: 1 GB.max_wal_size: 4 GB.
Fixed parameters
These parameters remain constant regardless of the cluster sizing:
wal_compression: lz4;jit: off;huge_pages: off.
PostgreSQL parameters
The following parameters are managed by the internal operator and cannot be configured:
- Replication:
primary_conninfo,synchronous_standby_names; - Backup:
recovery_target_*,restore_command; - Archiving:
archive_command,archive_mode; - WAL:
wal_level,max_wal_senders; - Network:
port,listen_addresses,hot_standby.
Special cases
TimescaleDB
If the TimescaleDB extension is enabled, the worker process parameters are adjusted accordingly:
| Parameter | vCPU 4-15 | vCPU ≥ 16 |
|---|---|---|
max_worker_processes | 3 + vcpuCount + 8 | 3 + vcpuCount + 16 |
timescaledb.max_background_workers | 8 | 16 |
PostgreSQL 18+
For PostgreSQL versions 18 and above (≥ 4 vCPU), the internal I/O parameters are also adjusted:
io_workers:min(32, max(3, vcpuCount / 4)).
Configuration examples
Small cluster (1 GiB, 1 vCPU, Standard storage, PostgreSQL 17)
| Parameter | Value |
|---|---|
shared_buffers | 256 MB |
effective_cache_size | 768 MB |
work_mem | 4 MB (floor value) |
maintenance_work_mem | 64 MB (floor value) |
random_page_cost | 4.0 |
effective_io_concurrency | 2 |
Intermediate cluster (8 GiB, 4 vCPU, GP2 storage, PostgreSQL 17)
| Parameter | Value |
|---|---|
shared_buffers | 2 GB |
effective_cache_size | 6 GB |
work_mem | 4 MB |
maintenance_work_mem | 512 MB |
random_page_cost | 1.1 |
effective_io_concurrency | 256 |
max_worker_processes | 4 |
max_parallel_workers | 4 |
max_parallel_workers_per_gather | 2 |
Large cluster with TimescaleDB (32 GiB, 16 vCPU, IO1 storage, PostgreSQL 18, TimescaleDB)
| Parameter | Value |
|---|---|
shared_buffers | 8 GB (cap) |
effective_cache_size | 24 GB |
maintenance_work_mem | 2 GB |
autovacuum_work_mem | 2 GB |
max_worker_processes | 35 (TimescaleDB formula) |
max_parallel_workers | 16 |
max_parallel_workers_per_gather | 4 (cap) |
autovacuum_max_workers | 4 |
timescaledb.max_background_workers | 16 |
io_workers | 4 |
Resource modification
When the resources of a cluster are modified through the API, the parameters are automatically recalculated and applied:
| Change type | Affected parameters | Operation required |
|---|---|---|
| RAM, vCPU | shared_buffers, max_worker_processes, etc. | Rolling restart |
| Storage type | random_page_cost, effective_io_concurrency | Configuration reload |
| Volume size | None | None |
Rolling restart
A rolling restart is triggered automatically when postmaster context parameters are modified. Each instance of the cluster is restarted one after another, which guarantees service continuity if you have at least one replica.
When resources are decreased (RAM or vCPU), the parameters are adjusted downward accordingly. Ensure that your workload remains compatible with the new configuration.
For more details on the impact of the rolling restart, see the documentation on modifying the resources of a cluster.