# 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. |
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.akamas.io/akamas-docs/3.2.1-1/akamas-reference/optimization-packs/postgresql-pack/postgresql-12.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.