PostgreSQL 12
This page describes the Optimization Pack for the component type PostgreSQL 12
Metrics
Concurrency
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
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
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
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
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
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
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
pg_statement_timeout
integer
milliseconds
0
0 → 2147483647
no
The maximum allowed duration of any statement.
Concurrency
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
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
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
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
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
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
pg_default_statistics_target
integer
100
1 → 10000
no
Sets the amount of default statistics target for table columns.
Last updated