| 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.
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:
This page describes the Optimization Pack for the component type PostgreSQL 12
| Parameter | Unit | Description |
|---|
| Parameter | Unit | Description |
|---|
| Metric | Unit | 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 |
|---|
PostgeSQL 11
PostgeSQL 12
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 |
|
| no | Controls whether the server should run the autovacuum launcher daemon. |
pg_autovacuum_vacuum_cost_delay | real | milliseconds |
|
| no | The cost delay value that will be used in automatic VACUUM operations. |
pg_autovacuum_vacuum_cost_limit | integer |
|
| no | The cost limit value that will be used in automatic VACUUM operations. |
pg_autovacuum_vacuum_threshold | integer | tuples |
|
| 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 |
|
| 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 |
|
| no | The maximum allowed duration of any statement. |
pg_max_connections | integer | connections |
|
| yes | The maximum number of concurrent connections allowed. |
pg_effective_io_concurrency | integer | iops |
|
| no | The number of simultaneous requests that can be handled efficiently by the disk subsystem. |
pg_max_parallel_maintenance_workers | integer | workers |
|
| no | Set the maximum number of parallel processes that can be started by a single utility command. |
pg_max_parallel_workers | integer | workers |
|
| 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 |
|
| 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 |
|
| no | The time to wait on a lock before checking for deadlock. |
pg_max_pred_locks_per_transaction | integer | predicate_locks |
|
| yes | The maximum number of predicate locks per transaction. |
pg_max_locks_per_transaction | integer | locks |
|
| yes | The maximum number of locks per transaction. |
pg_bgwriter_delay | integer | milliseconds |
|
| no | The delay between activity rounds for the background writer. |
pg_bgwriter_lru_maxpages | integer | buffers |
|
| no | The maximum number of LRU pages to flush per round by the background writer. |
pg_checkpoint_completion_target | real |
|
| no | The time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval. |
pg_wal_level | categorical | category |
|
| yes | The level of information written to the WAL. |
pg_wal_buffers | integer | kilobytes |
|
| no | The number of disk-page buffers in shared memory for WAL. |
pg_max_wal_senders | integer | processes |
|
| yes | The maximum number of simultaneously running WAL sender processes. Zero disables replication. |
pg_wal_compression | categorical |
|
| no | Set the compression of full-page writes written in WAL file. |
pg_max_wal_size | integer | megabytes |
|
| no | The maximum size to let the WAL grow to between automatic WAL checkpoints. |
pg_checkpoint_timeout | integer | seconds |
|
| no | The maximum time between automatic WAL checkpoints. |
pg_wal_sync_method | categorical | category |
|
| no | The method used for forcing WAL updates out to disk. |
pg_random_page_cost | real |
|
| no | The planner's estimate of the cost of a non-sequentially fetched disk page. |
pg_shared_buffers | integer | kilobytes |
|
| yes | The amount of memory dedicated to PostgreSQL to use for caching data. |
pg_work_mem | integer | kilobytes |
|
| 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 |
|
| 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 |
|
| 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 |
|
| no | Sets the amount of default statistics target for table columns. |