106 . Waiting to read or update transaction commit timestamps. Waiting to acquire a lock on page of a relation. Resets some cluster-wide statistics counters to zero, depending on the argument. Waiting to find or allocate space in shared memory. Alternatively, one can build custom views using the underlying cumulative statistics functions, as discussed in Section28.2.24. shared_buffers parameter. Postgres Source Code Docs: Locking Overview. Waiting to add or examine locks for backends, or waiting to join or exit a locking group (used by parallel query). Waiting for a barrier event to be processed by all backends. Waiting for I/O on commit timestamp buffer. The type of event for which the backend is waiting, if any; otherwise NULL. Waiting for SLRU data to reach durable storage following a page write. These numbers do not act as stated above; instead they update continuously throughout the transaction. PostgreSQL accesses certain on-disk information via SLRU (simple least-recently-used) caches. Waiting for a write during a file copy operation. Normally these parameters are set in postgresql.conf so that they apply to all server processes, but it is possible to turn them on or off in individual sessions using the SET command. The pg_stat_subscription_stats view will contain one row per subscription. Waiting in main loop of syslogger process. For tranches registered by extensions, the name is specified by extension and this will be displayed as wait_event. The parameter track_io_timing enables monitoring of block read and write times. Time when this process' current transaction was started, or null if no transaction is active. 214 . PostgreSQL also supports reporting dynamic information about exactly what is going on in the system right now, such as the exact command currently being executed by other server processes, and which other connections exist in the system. Number of disk blocks read from this index. The pg_stat_replication_slots view will contain one row per logical replication slot, showing statistics about its usage. Waiting to elect a Parallel Hash participant to allocate more buckets. When a buffer is read from disk (or written to disk), an IO in progress lock is also acquired, which indicates to other processes that the page is being read (or written) they can queue up if they need to do something with this page. Waiting for an asynchronous prefetch from a relation data file. Waiting in WAL receiver to receive data from remote server. pg_blocking_pids function. Waiting for data to reach durable storage while creating the data directory lock file. See, One row per subscription, showing statistics about errors. This can be used to gauge the delay that, Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it (but not yet applied it). Waiting to acquire an advisory user lock. Waiting for the group leader to update transaction status at end of a parallel operation. The full object locks which last (usually) for the duration of a transaction and which you can see in pg_locks have info about them stored in shared memory. The access functions for per-database statistics take a database OID as an argument to identify which database to report on. Possible types are. Waiting to manage space allocation in shared memory. Waiting when WAL data is not available from any kind of sources (local, archive or stream) before trying again to retrieve WAL data, at recovery. Waiting during base backup when throttling activity. Waiting for an elected Parallel Hash participant to allocate the initial hash table. Streaming only works with top-level transactions (subtransactions can't be streamed independently), so the counter is not incremented for subtransactions. See, One row for each index in the current database, showing statistics about accesses to that specific index. Waiting for startup process to send initial data for streaming replication. Current overall state of this backend. It can be joined to pg_stat_activity or pg_stat_replication on the pid column to get more details about the connection. Waiting for mapping data to reach durable storage during a logical rewrite. Waiting to find or allocate space in shared memory. Waiting to read or update the progress of one replication origin. Waiting to elect a Parallel Hash participant to allocate the initial hash table. Send time of last reply message received from standby server. replication_origin: Waiting to read or update the replication progress. The parameter track_activities enables monitoring of the current command being executed by any server process. pg_stat_reset_replication_slot ( text ) void. In all other states, it shows the identifier of last query that was executed. Waiting for a write when creating a new WAL segment by copying an existing one. idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error. In such cases, an older set of per-backend statistics access functions can be used; these are shown in Table28.20. This is consistent with the goal of measuring synchronous commit and transaction visibility delays for recent write transactions. Waiting for the version file to be written while creating a database. Waiting for a write of mapping data during a logical rewrite. quorum: This standby server is considered as a candidate for quorum standbys. Number of in-progress transactions streamed to the decoding output plugin after the memory used by logical decoding to decode changes from WAL for this slot has exceeded logical_decoding_work_mem. Waiting to read or update the control file or creation of a new WAL file. 39919 LWLock buffer_mapping 5119 Client ClientRead 3116 IO DataFileRead With C-Hash Event Count Event Type Event Name quorum: This standby server is considered as a candidate for quorum standbys. Waiting to read or update a process' fast-path lock information. disabled: This state is reported if track_activities is disabled in this backend. Possible values are: active: The backend is executing a query. Waiting for SLRU data to reach durable storage during a checkpoint or database shutdown. Additional Statistics Functions. Waiting to read or update old snapshot control information. Table28.17.pg_stat_recovery_prefetch View, Number of blocks prefetched because they were not in the buffer pool, Number of blocks not prefetched because they were already in the buffer pool, Number of blocks not prefetched because they would be zero-initialized, Number of blocks not prefetched because they didn't exist yet, Number of blocks not prefetched because a full page image was included in the WAL, Number of blocks not prefetched because they were already recently prefetched, How many bytes ahead the prefetcher is looking, How many blocks ahead the prefetcher is looking, How many prefetches have been initiated but are not yet known to have completed, Process ID of the subscription worker process, OID of the relation that the worker is synchronizing; null for the main apply worker, Last write-ahead log location received, the initial value of this field being 0. Waiting for a read when creating a new WAL segment by copying an existing one. The parameter track_wal_io_timing enables monitoring of WAL write times. Note that only tables, indexes, and functions in the current database can be seen with these functions. Waiting in WAL receiver to establish connection to remote server. Thus, the server expects something to happen that is independent of its internal processes. This block has to be read from outside the shared buffer pool, defined by the See, One row for each index in the current database, showing statistics about I/O on that specific index. Since collection of statistics adds some overhead to query execution, the system can be configured to collect or not collect information. This standby's xmin horizon reported by hot_standby_feedback. The pg_stat_bgwriter view will always have a single row, containing global data for the cluster. Waiting for a replication slot to become inactive so it can be dropped. For more information, see LWLock:buffer_content (BufferContent). This counts top-level transactions only, and is not incremented for subtransactions. When the buffer manager receives a request, PostgreSQL uses the buffer_tag of the desired page. Waiting in main loop of startup process for WAL to arrive, during streaming recovery. Conversely, if it's known that statistics are only accessed once, caching accessed statistics is unnecessary and can be avoided by setting stats_fetch_consistency to none. Waiting for I/O on a sub-transaction SLRU buffer. Waiting to read or record conflicting serializable transactions. , LWTRANCHE_MXACTMEMBER_BUFFERS, LWTRANCHE_ASYNC_BUFFERS, LWTRANCHE_OLDSERXID_BUFFERS, LWTRANCHE_WAL_INSERT, LWTRANCHE_BUFFER_CONTENT, LWTRANCHE_BUFFER_IO_IN_PROGRESS, LWTRANCHE . Its Waiting to write a protocol message to a shared message queue. pg_stat_get_backend_wait_event ( integer ) text. The pg_stat_database view will contain one row for each database in the cluster, showing database-wide statistics. Waiting in main loop of background writer process background worker. Before PostgreSQL 8.1, all operations of the shared buffer manager itself were protected by a single system-wide lock, the BufMgrLock, which unsurprisingly proved to be a source of contention. 39919 LWLock buffer_mapping 5119 Client ClientRead 3116 IO DataFileRead . Waiting for parallel query dynamic shared memory allocation lock. It can be joined to pg_stat_activity or pg_stat_replication on the pid column to get more details about the connection. All temporary files are counted, regardless of why the temporary file was created, and regardless of the, Number of deadlocks detected in this database, Time spent reading data file blocks by backends in this database, in milliseconds, Time spent writing data file blocks by backends in this database, in milliseconds, Number of queries in this database that have been canceled due to dropped tablespaces, Number of queries in this database that have been canceled due to lock timeouts, Number of queries in this database that have been canceled due to old snapshots, Number of queries in this database that have been canceled due to pinned buffers, Number of queries in this database that have been canceled due to deadlocks, Number of sequential scans initiated on this table, Number of live rows fetched by sequential scans, Number of index scans initiated on this table, Number of live rows fetched by index scans, Number of rows updated (includes HOT updated rows), Number of rows HOT updated (i.e., with no separate index update required), Estimated number of rows modified since this table was last analyzed, Last time at which this table was manually vacuumed (not counting, Last time at which this table was vacuumed by the autovacuum daemon, Last time at which this table was manually analyzed, Last time at which this table was analyzed by the autovacuum daemon, Number of times this table has been manually vacuumed (not counting, Number of times this table has been vacuumed by the autovacuum daemon, Number of times this table has been manually analyzed, Number of times this table has been analyzed by the autovacuum daemon, Number of index scans initiated on this index, Number of index entries returned by scans on this index, Number of live table rows fetched by simple index scans using this index, Number of disk blocks read from this table, Number of disk blocks read from all indexes on this table, Number of buffer hits in all indexes on this table, Number of disk blocks read from this table's TOAST table (if any), Number of buffer hits in this table's TOAST table (if any), Number of disk blocks read from this table's TOAST table indexes (if any), Number of buffer hits in this table's TOAST table indexes (if any), Number of disk blocks read from this index, Number of disk blocks read from this sequence, Number of times this function has been called, Total time spent in this function and all other functions called by it, in milliseconds, Total time spent in this function itself, not including other functions called by it, in milliseconds, Process ID of the server process handling the current session, Returns a record of information about the backend with the specified PID, or one record for each active backend in the system if, Returns the timestamp of the current statistics snapshot, Reset all statistics counters for the current database to zero (requires superuser privileges by default, but EXECUTE for this function can be granted to others.