# PostgreSQL 12

This page describes the Optimization Pack for the component type PostgreSQL 12

## Metrics

### Concurrency

<table><thead><tr><th width="178">Parameter</th><th width="144.33333333333334">Unit</th><th>Description</th></tr></thead><tbody><tr><td>pg_connections</td><td>connections</td><td>The number of connections in the db.</td></tr><tr><td>pg_total_locks</td><td>locks</td><td>The total number of locks (of any type) performed.</td></tr><tr><td>pg_conflicts</td><td>conflicts/s</td><td>The number of queries canceled due to conflicts with recovery in this database per second.</td></tr><tr><td>pg_deadlocks</td><td>deadlocks/s</td><td>The number of deadlocks detected in this database per second.</td></tr></tbody></table>

### Transactions

<table><thead><tr><th width="172">Parameter</th><th width="131.33333333333334">Unit</th><th>Description</th></tr></thead><tbody><tr><td>pg_commits</td><td>commits/s</td><td>The number of transactions committed per second.</td></tr><tr><td>pg_rollbacks</td><td>rollbacks/s</td><td>The number of transactions rollbacked per second.</td></tr><tr><td>pg_longest_transaction</td><td>seconds</td><td>The max duration in seconds any active transaction has been running.</td></tr></tbody></table>

### Queries

| Metric                 | Unit      | Description                                                                 |
| ---------------------- | --------- | --------------------------------------------------------------------------- |
| 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). |

### Checkpoints

<table><thead><tr><th width="248.33333333333334">Parameter</th><th width="152">Unit</th><th>Description</th></tr></thead><tbody><tr><td>pg_scheduled_checkpoints</td><td>checkpoints/s</td><td>The number of scheduled checkpoints performed in this database per second.</td></tr><tr><td>pg_requested_checkpoints</td><td>checkpoints/s</td><td>The number of requested checkpoints performed in this database per second.</td></tr><tr><td>pg_checkpoint_write_time</td><td>milliseconds</td><td>The total amount of time that has been spent in the portion of checkpoint processing where files are written to disk.</td></tr><tr><td>pg_checkpoint_write_time</td><td>milliseconds</td><td>The total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk.</td></tr></tbody></table>

### Buffers

<table><thead><tr><th width="306">Metric</th><th width="125.33333333333334">Unit</th><th>Description</th></tr></thead><tbody><tr><td>pg_written_buffers_backend</td><td>writes/s</td><td>The number of buffers written directly by a backend per second.</td></tr><tr><td>pg_buffers_allocated</td><td>buffers/s</td><td>The number of buffers allocated per second.</td></tr><tr><td>pg_written_buffers_background</td><td>writes/s</td><td>The number of buffers written by the background writer per second.</td></tr><tr><td>pg_temp_files</td><td>bytes/s</td><td>The total amount of data written to temporary files by queries in this database per second.</td></tr><tr><td>pg_maxwritten_cleaning</td><td>stops/s</td><td>The number of times the background writer stopped a cleaning scan because it had written too many buffers per second.</td></tr><tr><td>pg_written_buffers_checkpoint</td><td>writes/s</td><td>The number of buffers written during checkpoints per second.</td></tr></tbody></table>

### Disks

<table><thead><tr><th width="250">Metric</th><th width="131.33333333333334">Unit</th><th>Description</th></tr></thead><tbody><tr><td>pg_cache_hit_rate</td><td>percent</td><td>The cache hit rate of the db.</td></tr><tr><td>pg_disks_reads</td><td>reads/s</td><td>The number of reads performed per second.</td></tr><tr><td>pg_read_time</td><td>milliseconds</td><td>The time spent reading data file blocks by backends in this database.</td></tr><tr><td>pg_disks_reads</td><td>milliseconds</td><td>The time spent writing data file blocks by backends in this database.</td></tr><tr><td>pg_backend_fsync</td><td>fsyncs/s</td><td>The time spent writing data file blocks by backends in this database.</td></tr></tbody></table>

## Parameters

### Autovacuum

| Parameter                             | Type        | Unit         | Default Value | Domain                                         | Restart | Description                                                                                                       |
| ------------------------------------- | ----------- | ------------ | ------------- | ---------------------------------------------- | ------- | ----------------------------------------------------------------------------------------------------------------- |
| pg\_autovacuum                        | categorical |              | `on`          | <p><code>on</code>,</p><p><code>off</code></p> | 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      | <p>The minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table.</p><p> </p>       |
| 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. |

### Statements

| Parameter              | Type    | Unit         | Default Value | Domain             | Restart | Description                                    |
| ---------------------- | ------- | ------------ | ------------- | ------------------ | ------- | ---------------------------------------------- |
| pg\_statement\_timeout | integer | milliseconds | `0`           | `0` → `2147483647` | no      | The maximum allowed duration of any statement. |

### Concurrency

| Parameter                               | Type    | Unit             | Default Value | Domain              | Restart | Description                                                                                                              |
| --------------------------------------- | ------- | ---------------- | ------------- | ------------------- | ------- | ------------------------------------------------------------------------------------------------------------------------ |
| 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      | <p>The number of simultaneous requests that can be handled efficiently by the disk subsystem.</p><p> </p>                |
| pg\_max\_parallel\_maintenance\_workers | integer | workers          | `2`           | `0` → `1024`        | no      | <p>Set the maximum number of parallel processes that can be started by a single utility command.</p><p> </p>             |
| pg\_max\_parallel\_workers              | integer | workers          | `8`           | `0` → `1024`        | no      | <p>Set the maximum number of parallel workers that that the system can support for parallel operations.</p><p> </p>      |
| pg\_max\_parallel\_workers\_per\_gather | integer | workers          | `2`           | `0` → `1024`        | no      | <p>Set the maximum number of parallel processes that can be started by a single Gather or Gather Merge node.</p><p> </p> |
| 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.                                                                             |

### Background Writer

<table><thead><tr><th width="145">Parameter</th><th width="99">Type</th><th>Unit</th><th>Default value</th><th>Domain</th><th>Restart</th><th>Description</th></tr></thead><tbody><tr><td>pg_bgwriter_delay</td><td>integer</td><td>milliseconds</td><td><code>200</code></td><td><code>10</code> → <code>10000</code></td><td>no</td><td>The delay between activity rounds for the background writer.</td></tr><tr><td>pg_bgwriter_lru_maxpages</td><td>integer</td><td>buffers</td><td><code>100</code></td><td><code>0</code> → <code>1073741823</code></td><td>no</td><td>The maximum number of LRU pages to flush per round by the background writer.</td></tr></tbody></table>

### Checkpoints

| Parameter                          | Type | Unit | Default value | Domain        | Restart | Description                                                                                  |
| ---------------------------------- | ---- | ---- | ------------- | ------------- | ------- | -------------------------------------------------------------------------------------------- |
| 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. |

### Write-Ahead

<table><thead><tr><th>Parameter</th><th>Type</th><th width="129">Unit</th><th>Default value</th><th>Domain</th><th>Restart</th><th>Description</th></tr></thead><tbody><tr><td>pg_wal_level</td><td>categorical</td><td>category</td><td><code>replica</code></td><td><p><code>mminimal</code>,</p><p><code>replica</code>,</p><p><code>logical</code></p></td><td>yes</td><td>The level of information written to the WAL.</td></tr><tr><td>pg_wal_buffers</td><td>integer</td><td>kilobytes</td><td><code>-1</code></td><td><code>-1</code> → <code>2097144</code></td><td>no</td><td>The number of disk-page buffers in shared memory for WAL.</td></tr><tr><td>pg_max_wal_senders</td><td>integer</td><td>processes</td><td><code>10</code></td><td><code>0</code> → <code>262143</code></td><td>yes</td><td><p>The maximum number of simultaneously running WAL sender processes. Zero disables replication.</p><p> </p></td></tr><tr><td>pg_wal_compression</td><td>categorical</td><td> </td><td><code>off</code></td><td><p><code>on</code>,</p><p><code>off</code></p></td><td>no</td><td>Set the compression of full-page writes written in WAL file.</td></tr><tr><td>pg_max_wal_size</td><td>integer</td><td>megabytes</td><td><code>1024</code></td><td><code>2</code> → <code>2147483647</code></td><td>no</td><td>The maximum size to let the WAL grow to between automatic WAL checkpoints.</td></tr><tr><td>pg_checkpoint_timeout</td><td>integer</td><td>seconds</td><td><code>300</code></td><td><code>30</code> → <code>86400</code></td><td>no</td><td>The maximum time between automatic WAL checkpoints.</td></tr><tr><td>pg_wal_sync_method</td><td>categorical</td><td>category</td><td><code>fdatasync</code></td><td><p><code>fsync</code>,</p><p><code>fdatasync</code>,</p><p><code>open_sync</code>,</p><p><code>open_datasync</code></p></td><td>no</td><td>The method used for forcing WAL updates out to disk.</td></tr></tbody></table>

### Cost

<table><thead><tr><th>Parameter</th><th>Type</th><th width="78">Unit</th><th>Default value</th><th>Domain</th><th>Restart</th><th>Description</th></tr></thead><tbody><tr><td>pg_random_page_cost</td><td>real</td><td> </td><td><code>4.0</code></td><td><code>0.0</code> → <code>1.79769e+308</code></td><td>no</td><td>The planner's estimate of the cost of a non-sequentially fetched disk page.</td></tr></tbody></table>

### Disks and Memory

<table><thead><tr><th width="142">Parameter</th><th width="80">Type</th><th width="95">Unit</th><th width="110">Default value</th><th width="98">Domain</th><th width="89">Restart</th><th>Description</th></tr></thead><tbody><tr><td>pg_shared_buffers</td><td>integer</td><td>kilobytes</td><td><code>131072</code></td><td><code>128</code> → <code>8589934584</code></td><td>yes</td><td>The amount of memory dedicated to PostgreSQL to use for caching data.</td></tr><tr><td>pg_work_mem</td><td>integer</td><td>kilobytes</td><td><code>4096</code></td><td><code>64</code> → <code>2147483647</code></td><td>no</td><td>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.</td></tr><tr><td>pg_maintenance_work_mem</td><td>integer</td><td>kilobytes</td><td><code>65536</code></td><td><code>1024</code> → <code>2147483647</code></td><td>no</td><td><p>The maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.</p><p><br></p></td></tr><tr><td>pg_effective_cache_size</td><td>integer</td><td>kilobytes</td><td><code>524288</code></td><td><code>1</code> → <code>2147483647</code></td><td>no</td><td>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.</td></tr></tbody></table>

### Statistics

<table><thead><tr><th>Parameter</th><th width="125">Type</th><th width="123">Unit</th><th>Default value</th><th>Domain</th><th>Restart</th><th>Description</th></tr></thead><tbody><tr><td>pg_default_statistics_target</td><td>integer</td><td> </td><td><code>100</code></td><td><code>1</code> → <code>10000</code></td><td>no</td><td>Sets the amount of default statistics target for table columns.</td></tr></tbody></table>