Automatic configuration of PostgreSQL parameters (Auto-tuning)
Reviewed on · View on GitHub
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;max_connections: 100 per instance.
Connection limits
max_connections is fixed at 100 per instance, regardless of the cluster sizing: it is not part of auto-tuning. Three slots are reserved for the platform (superuser_reserved_connections = 3), leaving 97 for client connections on each instance.
All client connections go through PgBouncer, a connection pooler automatically deployed in front of every PostgreSQL cluster:
| Setting | Value | Description |
|---|---|---|
| Pooling mode | session | A client keeps its server connection for the whole session |
| PgBouncer replicas | 3 | Three identical pooler instances behind the connection endpoint |
max_client_conn | 100 per replica | Maximum concurrent client connections (300 cluster-wide) |
default_pool_size | 20 per pool and replica | Maximum simultaneously active sessions per database and user (60 cluster-wide per pool) |
client_idle_timeout | 600 s | Idle client connections are closed after 10 minutes |
Consequences for application design:
- a cluster accepts up to 300 concurrent client connections (3 × 100 per PgBouncer replica);
- pooled connections always run on the primary instance (read-write endpoint): the read replicas do not add connection capacity;
- beyond 60 simultaneously active sessions (3 × 20 per pool), additional clients are queued on PgBouncer until a server connection is released — long-running transactions and idle-in-transaction sessions extend the wait;
- to avoid queue timeouts, close idle connections and size client-side pools accordingly: a bounded pool with a few dozen connections per application instance is recommended.
For the full connection behaviour — the 60-second idle connection timeout applied by the connection endpoint and its impact on long operations and application pools — see Connections and timeouts.
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.