March 15, 2024
Summary: This report section contains some server-level statistics in PostgreSQL.
Table of Contents
Database statistics
Contains per-database statistics during report interval, based on pg_stat_database view.
- Database - database name
- Transactions - database transaction statistics
- Commits - number of committed transactions (xact_commit)
- Rollbacks - number of rolled back transactions (xact_rollback)
- Deadlocks - number of deadlocks detected (deadlocks)
- Checksums - checksum failures (if any)
- Failures - number of block checksum failures detected
- Last - last checksum failure detected
- Block statistics - database blocks read and hit statistics
- Hit(%) - buffer cache hit ratio
- Read - number of disk blocks read in this database (blks_read)
- Hit - number of times disk blocks were found already in the buffer cache (blks_hit)
- Block I/O times - time spent on data blocks I/O
- Read - time spent reading data file blocks by backends, in seconds
- Write - time spent writing data file blocks by backends, in seconds
- Tuples - tuples statistics section
- Ret - number of returned tuples (tup_returned)
- Fet - number of fetched tuples (tup_fetched)
- Ins - inserted tuples count (tup_inserted)
- Upd - number of updated tuples (tup_updated)
- Del - number of deleted tuples (tup_deleted)
- Temp files - temporary files statistics
- Size - total amount of data written to temporary files by queries in this database (temp_bytes)
- Files - number of temporary files created by queries in this database (temp_files)
- Size - database size at the end of report interval (pg_database_size())
- Growth - database growth during report interval (pg_database_size() difference)
Cluster I/O statistics
I/O statistics by object types, backend types and contexts. Based on pg_stat_io, available since PostgreSQL 16
- Object - type of an I/O operation target object
- Backend - type of backend performed an I/O operation
- Context - context of an I/O operation
- Reads - read statistics
- Count - number of read operations
- Bytes - read data amount
- Time - time spent in reading operations (in seconds)
- Writes - write statistics
- Count - number of write operations
- Bytes - written data amount
- Time - time spent in writing operations (in seconds)
- Writebacks - requests on writing to permanent storage
- Count - number of blocks which the process requested the kernel write out to permanent storage
- Bytes - the amount of data requested for write out to permanent storage
- Time - time spent in writeback operations (seconds)
- Extends - relation extend operations
- Count - number of relation extend operations
- Bytes - the amount of space used by extend operations
- Time - time spent in extend operations (in seconds)
- Hits - the number of times a desired block was found in a shared buffer
- Evictions - number of times a block has been written out from a shared or local buffer in order to make it available for another use
- Reuses - the number of times an existing buffer in a size-limited ring buffer outside of shared buffers was reused as part of an I/O operation in the bulkread, bulkwrite, or vacuum contexts
- Fsyncs - fsync operations
- Count - number of fsync calls. These are only tracked in context normal
- Time - time spent in fsync operations (seconds)
Cluster SLRU statistics
Access statistics on SLRU caches (based on pg_stat_slru view)
- Name - name of the SLRU
- Zeroed - number of blocks zeroed during initializations
- Hits - number of times disk blocks were found already in the SLRU, so that a read was not necessary (this only includes hits in the SLRU, not the operating system’s file system cache)
- Reads - number of disk blocks read for this SLRU
- %Hit - number of disk blocks hits for this SLRU as a percentage of reads + hits
- Writes - number of disk blocks written for this SLRU
- Checked - number of blocks checked for existence for this SLRU (blks_exists field)
- Flushes - number of flushes of dirty data for this SLRU
- Truncates - number of truncates for this SLRU
Session statistics by database
This section contains session-related data from pg_stat_database view. Available since Postgres 14
- Database - database name
- Timings (s) - session timings in seconds
- Total - time spent by database sessions in this database (note that statistics are only updated when the state of a session changes, so if sessions have been idle for a long time, this idle time won’t be included) (session_time)
- Active - time spent executing SQL statements in this database (this corresponds to the states active and fastpath function call in pg_stat_activity) (active_time)
- Idle(T) - time spent idling while in a transaction in this database (this corresponds to the states idle in transaction and idle in transaction (aborted) in pg_stat_activity) (idle_in_transaction_time)
- Sessions - session counts for databases
- Established - total number of sessions established to this database (sessions)
- Abandoned - number of database sessions to this database that were terminated because connection to the client was lost (sessions_abandoned)
- Fatal - number of database sessions to this database that were terminated by fatal errors (sessions_fatal)
- Killed - number of database sessions to this database that were terminated by operator intervention (sessions_killed)
Statement statistics by database
Contains per-database aggregated total statistics of pg_stat_statements data (if pg_stat_statements extension was available during report interval)
- Database - database name
- Calls - total count of all statements executions (sum of calls)
- Time (s) - time spent in seconds
- Plan - time spent planning (sum of total_plan_time) - available since pg_stat_statements 1.8
- Exec - time spent executing (sum of total_time or total_exec_time)
- Read - time spent reading blocks (sum of blk_read_time)
- Write - time spent writing blocks (sum of blk_write_time)
- Trg - time spent executing trigger functions
- Temp I/O Time - time spent on temporary file blocks I/O
- Read - time spent reading temporary file blocks, in seconds
- Write - time spent writing temporary file blocks, in seconds
- Fetched (blk) - total blocks fetched from disk and buffer cache
- Shared - total fetched shared blocks count (sum of shared_blks_read + shared_blks_hit)
- Local - total fetched local blocks count (sum of local_blks_read + local_blks_hit)
- Dirtied (blk) - total blocks dirtied in database
- Shared - total number of shared blocks dirtied in the database (sum of shared_blks_dirtied)
- Local - total number of local blocks dirtied in the database (sum of local_blks_dirtied)
- Temp (blk) - blocks used for operations (like joins and sorts)
- Read - blocks read (sum of temp_blks_read)
- Write - blocks written (sum of temp_blks_written)
- Local (blk) - blocks used for temporary tables
- Read - blocks read (sum of local_blks_read)
- Write - blocks written (sum of local_blks_written)
- Statements - total count of captured statements
- WAL size - total amount of WAL generated by statements (sum of wal_bytes)
JIT statistics by database
Contains per database total JIT-related statistics provided by pg_stat_statements extension. Available since PostgreSQL 15 (pg_stat_statements 1.10).
- Database - database name
- Calls - total count of all statements executions (sum of calls)
- Time (s) - time spent in seconds
- Plan - time spent planning (sum of total_plan_time) - available since pg_stat_statements 1.8
- Exec - time spent executing (sum of total_time or total_exec_time)
- Generation
- Count - Total number of functions JIT-compiled by the statements
- Gen. time - Total time spent by the statements on generating JIT code
- Inlining
- Count - Number of times functions have been inlined
- Time - Total time spent by statements on inlining functions (in seconds)
- Optimization
- Count - Number of times statements has been optimized
- Time - Total time spent by statements on optimizing (in seconds)
- Emission
- Count - Number of times code has been emitted
- Time - Total time spent by statements on emitting code (in seconds)
Cluster statistics
This table contains data from pg_stat_bgwriter view
- Scheduled checkpoints - total number of checkpoints, completed on schedule due to checkpoint_timeout parameter (checkpoints_timed field)
- Requested checkpoints - total number of other checkpoints: due to values of max_wal_size, archive_timeout and CHECKPOINT commands (checkpoints_req field)
- Checkpoint write time (s) - total time spent writing checkpoints in seconds (checkpoint_write_time field)
- Checkpoint sync time (s) - total time spent syncing checkpoints in seconds (checkpoint_sync_time field)
- Checkpoints buffers written - total number of buffers, written by checkpointer (buffers_checkpoint field)
- Background buffers written - total number of buffers, written by background writer process (buffers_clean field)
- Backend buffers written - total number of buffers, written by backends (buffers_backend field)
- Backend fsync count - total number of backend fsync calls (buffers_backend_fsync field)
- Bgwriter interrupts (too many buffers) - total count of background writer interrupts due to reaching value of the bgwriter_lru_maxpages parameter.
- Number of buffers allocated - total count of buffers allocated (buffers_alloc field)
- WAL generated - total amount of WAL generated (based on pg_current_wal_lsn() difference)
- Start LSN - WAL LSN at the start of the current report
- End LSN - WAL LSN at the end of the current report
- WAL segments archived - archived WAL segments count (based on archived_count of pg_stat_archiver view)
- WAL segments archive failed - WAL segment archive failures count (based on failed_count of pg_stat_archiver view)
WAL statistics
This table contains data from pg_stat_wal view. Available since Postgres 14
- WAL generated - total amount of WAL generated (wal_bytes)
- WAL per second - average amount of WAL generated per second
- WAL records - total number of WAL records generated (wal_records)
- WAL FPI - total number of WAL full page images generated (wal_fpi)
- WAL buffers full - number of times WAL data was written to disk because WAL buffers became full (wal_buffers_full)
- WAL writes - number of times WAL buffers were written out to disk via XLogWrite request (wal_write)
- WAL writes per second - average number of times WAL buffers were written out to disk via XLogWrite request per second
- WAL sync - Number of times WAL files were synced to disk via issue_xlog_fsync request (if fsync is on and wal_sync_method is either fdatasync, fsync or fsync_writethrough, otherwise zero) (wal_sync)
- WAL syncs per second - average number of times WAL files were synced to disk via issue_xlog_fsync request per second
- WAL write time (s) - total amount of time spent writing WAL buffers to disk via XLogWrite request, in seconds (if track_wal_io_timing is enabled, otherwise zero). This includes the sync time when wal_sync_method is either open_datasync or open_sync. (wal_write_time)
- WAL write duty - WAL write time as a percentage of the report duration time
- WAL sync time (s) - Total amount of time spent syncing WAL files to disk via issue_xlog_fsync request, in seconds (if track_wal_io_timing is enabled, fsync is on, and wal_sync_method is either fdatasync, fsync or fsync_writethrough, otherwise zero) (wal_sync_time)
- WAL sync duty - WAL sync time as a percentage of the report duration time
Tablespace statistics
This table contains information about tablespaces sizes and growth:
- Tablespace - tablespace name
- Path - tablespace path
- Size - tablespace size as it was at time of last sample in report interval
- Growth - tablespace growth during report interval
Wait sampling
This section contains data obtained from pg_wait_sampling extension if it was available during report interval.
Wait events types
This table provides information about summary time waited in events of each wait event type during report interval. Wait statistics is provided for events, detected by pg_wait_sampling extension during statements execution and overall waits (including background activity).
- Wait event type - the type of wait event (see pg_stat_activity)
- Statements Waited (s) - amount of time waited in events of this event type by statements (when pg_wait_sampling_profile.queryid is not null) in seconds
- %Total - time waited in events of this wait event type by statements as a percentage of overall time waited by statements
- All Waited (s) - amount of time waited in events of this event type by all backends (including background activity)
- %Total - time waited in events of this wait event type by backends as a percentage of overall time waited by backends
Top wait events (statements)
This table contains top pg_profile.topn wait events by summary time waited during statements execution (when pg_wait_sampling_profile.queryid is not null).
- Wait event type - the type of wait event (see pg_stat_activity)
- Wait event - wait event name (see pg_stat_activity)
- Waited (s) - amount of time waited in event by statements in seconds
- %Total - time waited in event by statements as a percentage of overall time waited by statements
Top wait events (All)
This table contains top pg_profile.topn wait events by summary time waited including background activity.
- Wait event type - the type of wait event (see pg_stat_activity)
- Wait event - wait event name (see pg_stat_activity)
- Waited (s) - amount of time waited in event by all backends in seconds
- %Total - time waited in event by all backends as a percentage of overall time waited by backends