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.