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
This page describes the Optimization Pack for the component type PostgreSQL 12
| Parameter | Unit | Description |
|---|---|---|
| Parameter | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| 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 | |
|---|---|---|---|---|---|---|
| Parameter | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| 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 | Domain | Restart | Description |
|---|---|---|---|---|---|---|
| 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 | Domain | Restart | Description |
|---|---|---|---|---|---|---|
| Parameter | Type | Unit | Default value | Domain | 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.
pg_connections
connections
The number of connections in the db.
pg_total_locks
locks
The total number of locks (of any type) performed.
pg_conflicts
conflicts/s
The number of queries canceled due to conflicts with recovery in this database per second.
pg_deadlocks
deadlocks/s
The number of deadlocks detected in this database per second.
pg_commits
commits/s
The number of transactions committed per second.
pg_rollbacks
rollbacks/s
The number of transactions rollbacked per second.
pg_longest_transaction
seconds
The max duration in seconds any active transaction has been running.
pg_fetched_rows
rows/s
The number of rows fetched by queries per second.
pg_inserted_rows
rows/s
The number of rows inserted by queries per second.
pg_updated_rows
rows/s
The number of rows updated by queries per second.
pg_deleted_rows
rows/s
The number of rows deleted by queries per second.
pg_returned_rows
rows/s
The number of rows returned by queries per second.
pg_query_per_second
queries/s
The number of queries performed per second (both committed and rollbacked).
pg_scheduled_checkpoints
checkpoints/s
The number of scheduled checkpoints performed in this database per second.
pg_requested_checkpoints
checkpoints/s
The number of requested checkpoints performed in this database per second.
pg_checkpoint_write_time
milliseconds
The total amount of time that has been spent in the portion of checkpoint processing where files are written to disk.
pg_checkpoint_write_time
milliseconds
The total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk.
pg_written_buffers_backend
writes/s
The number of buffers written directly by a backend per second.
pg_buffers_allocated
buffers/s
The number of buffers allocated per second.
pg_written_buffers_background
writes/s
The number of buffers written by the background writer per second.
pg_temp_files
bytes/s
The total amount of data written to temporary files by queries in this database per second.
pg_maxwritten_cleaning
stops/s
The number of times the background writer stopped a cleaning scan because it had written too many buffers per second.
pg_written_buffers_checkpoint
writes/s
The number of buffers written during checkpoints per second.
pg_cache_hit_rate
percent
The cache hit rate of the db.
pg_disks_reads
reads/s
The number of reads performed per second.
pg_read_time
milliseconds
The time spent reading data file blocks by backends in this database.
pg_disks_reads
milliseconds
The time spent writing data file blocks by backends in this database.
pg_backend_fsync
fsyncs/s
The time spent writing data file blocks by backends in this database.
pg_autovacuum
categorical
on
on,
off
no
Controls whether the server should run the autovacuum launcher daemon.
pg_autovacuum_vacuum_cost_delay
real
milliseconds
2
-1 → 100
no
The cost delay value that will be used in automatic VACUUM operations.
pg_autovacuum_vacuum_cost_limit
integer
-1
-1 → 10000
no
The cost limit value that will be used in automatic VACUUM operations.
pg_autovacuum_vacuum_threshold
integer
tuples
50
0 → 2147483647
no
The minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table.
pg_autovacuum_vacuum_scale_factor
real
tuples
0.2
0.0 → 100
no
The fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM.
pg_statement_timeout
integer
milliseconds
0
0 → 2147483647
no
The maximum allowed duration of any statement.
pg_max_connections
integer
connections
100
1→ 262143
yes
The maximum number of concurrent connections allowed.
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_max_parallel_maintenance_workers
integer
workers
2
0 → 1024
no
Set the maximum number of parallel processes that can be started by a single utility command.
pg_max_parallel_workers
integer
workers
8
0 → 1024
no
Set the maximum number of parallel workers that that the system can support for parallel operations.
pg_max_parallel_workers_per_gather
integer
workers
2
0 → 1024
no
Set the maximum number of parallel processes that can be started by a single Gather or Gather Merge node.
pg_deadlock_timeout
integer
milliseconds
1000
1 → 2147483647
no
The time to wait on a lock before checking for deadlock.
pg_max_pred_locks_per_transaction
integer
predicate_locks
64
10 → 2147483647
yes
The maximum number of predicate locks per transaction.
pg_max_locks_per_transaction
integer
locks
64
10 → 2147483647
yes
The maximum number of locks per transaction.
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_wal_level
categorical
category
replica
mminimal,
replica,
logical
yes
The level of information written to the WAL.
pg_wal_buffers
integer
kilobytes
-1
-1 → 2097144
no
The number of disk-page buffers in shared memory for WAL.
pg_max_wal_senders
integer
processes
10
0 → 262143
yes
The maximum number of simultaneously running WAL sender processes. Zero disables replication.
pg_wal_compression
categorical
off
on,
off
no
Set the compression of full-page writes written in WAL file.
pg_max_wal_size
integer
megabytes
1024
2 → 2147483647
no
The maximum size to let the WAL grow to between automatic WAL checkpoints.
pg_checkpoint_timeout
integer
seconds
300
30 → 86400
no
The maximum time between automatic WAL checkpoints.
pg_wal_sync_method
categorical
category
fdatasync
fsync,
fdatasync,
open_sync,
open_datasync
no
The method used for forcing WAL updates out to disk.
pg_random_page_cost
real
4.0
0.0 → 1.79769e+308
no
The planner's estimate of the cost of a non-sequentially fetched disk page.
pg_shared_buffers
integer
kilobytes
131072
128 → 8589934584
yes
The amount of memory dedicated to PostgreSQL to use for caching data.
pg_work_mem
integer
kilobytes
4096
64 → 2147483647
no
The maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.
pg_maintenance_work_mem
integer
kilobytes
65536
1024 → 2147483647
no
The maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.
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.
pg_default_statistics_target
integer
100
1 → 10000
no
Sets the amount of default statistics target for table columns.