The PostgreSQL optimization pack allows you to explore and tune the configuration space of PostgreSQL parameters. In this way, an Akamas study can ramp up the transaction number or minimize its resource consumption according to your typical workload, cutting costs. The main tuning areas covered by the parameters provided in this optimization pack are:
Background writer management
VACUUM management
Deadlock and concurrency management
Write-ahead management
The optimization pack includes metrics to monitor:
Query executions
Concurrency and locks
Buffers and disk I/O
These component types model different PostgreSQL releases. They provided a subset of the parameters available for the best optimization results.
| Component Type | Description |
|---|---|
Here’s the command to install the PostgreSQL optimization pack using the Akamas CLI:
PostgeSQL 11
PostgeSQL 12
| Parameter | Type | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|---|
| Parameter | Type | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|---|
| Parameter | Type | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|---|
| Parameter | Type | Unit | Default value | Restart | Description | |
|---|---|---|---|---|---|---|
pg_connections
connections
The number of connections in the db.
pg_start_time
seconds
The total amount time spent by postresql to boot up.
pg_commits
commits/s
The number of transactions committed per second.
pg_rollbacks
rollbacks/s
The number of transactions rollbacked per second.
pg_checkpoint_executed
checkpoints/s
The total number of checkpoint operations executed by postgresql.
pg_disk_used
bytes
The amount of disk space used by postgresql.
pg_blocks_read
blocks/s
The number of blocks read per second by postgresql.
pg_blocks_cache_hit
blocks/s
Number of blocks found in the buffer cache.
pg_backend_fsync_count
syncs
The total number of times postgresql executed a sync of data to disk.
pg_effective_io_concurrency
integer
iops
1
0 → 1000
no
The number of simultaneous requests that can be handled efficiently by the disk subsystem.
pg_bgwriter_delay
integer
milliseconds
200
10 → 10000
no
The delay between activity rounds for the background writer.
pg_bgwriter_lru_maxpages
integer
buffers
100
0 → 1073741823
no
The maximum number of LRU pages to flush per round by the background writer.
pg_checkpoint_completion_target
real
0.5
0.0 → 1.0
no
The time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
pg_effective_cache_size
integer
kilobytes
524288
1 → 2147483647
no
The planner's assumption about the effective size of the disk cache available to a single query. A higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used.