# PostgreSQL 12 This page describes the Optimization Pack for the component type PostgreSQL 12 ## Metrics ### Concurrency | Parameter | Unit | Description | | ---------------- | ----------- | ------------------------------------------------------------------------------------------ | | 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. | ### Transactions | Parameter | Unit | Description | | ------------------------ | ----------- | -------------------------------------------------------------------- | | 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. | ### 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 | Parameter | Unit | Description | | --------------------------- | ------------- | -------------------------------------------------------------------------------------------------------------------------- | | 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. | ### Buffers | Metric | Unit | Description | | -------------------------------- | --------- | --------------------------------------------------------------------------------------------------------------------- | | 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. | ### Disks | Metric | Unit | Description | | -------------------- | ------------ | --------------------------------------------------------------------- | | 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. | ## Parameters ### Autovacuum | Parameter | Type | Unit | Default Value | Domain | Restart | Description | | ------------------------------------- | ----------- | ------------ | ------------- | ---------------------------------------------- | ------- | ----------------------------------------------------------------------------------------------------------------- | | 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. | ### 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 | 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. | ### Background Writer | Parameter | Type | Unit | Default value | Domain | Restart | Description | | --------------------------- | ------- | ------------ | ------------- | ------------------ | ------- | ---------------------------------------------------------------------------- | | 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. | ### 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 | Parameter | Type | Unit | Default value | Domain | Restart | Description | | ----------------------- | ----------- | --------- | ------------- | ------------------------------------------------------------------------------------------------------------------------- | ------- | --------------------------------------------------------------------------------------------- | | 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. | ### Cost | Parameter | Type | Unit | Default value | Domain | Restart | Description | | ---------------------- | ---- | ---- | ------------- | ---------------------- | ------- | --------------------------------------------------------------------------- | | 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. | ### Disks and Memory | Parameter | Type | Unit | Default value | Domain | Restart | Description | | -------------------------- | ------- | --------- | ------------- | --------------------- | ------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 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. | ### Statistics | Parameter | Type | Unit | Default value | Domain | Restart | Description | | ------------------------------- | ------- | ---- | ------------- | ------------- | ------- | --------------------------------------------------------------- | | pg\_default\_statistics\_target | integer | | `100` | `1` → `10000` | no | Sets the amount of default statistics target for table columns. |