streaming: This WAL sender is streaming changes after its connected standby server has caught up with the primary. The parameter track_io_timing enables monitoring of block read and write times. Wait Events of Type BufferPin, Table28.8. See, One row for each sequence in the current database, showing statistics about I/O on that specific sequence. See, One row per WAL sender process, showing statistics about replication to that sender's connected standby server. IP address of the client connected to this backend. Waiting for the control file to reach durable storage. Waiting to elect a Parallel Hash participant to allocate the initial hash table. Waiting to read or update information about synchronous replicas. being read from storage. To minimize skew, stats_fetch_consistency can be set to snapshot, at the price of increased memory usage for caching not-needed statistics data. Waiting for a write while adding a line to the data directory lock file. 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. Waiting for WAL from a stream at recovery. Total amount of time spent syncing WAL files to disk via issue_xlog_fsync request, in milliseconds (if track_wal_io_timing is enabled, fsync is on, and wal_sync_method is either fdatasync, fsync or fsync_writethrough, otherwise zero). pg_stat_reset_replication_slot ( text ) void. See, Time when the current transaction was started. This standby's xmin horizon reported by hot_standby_feedback. Simple test for lock_waits log messages. Waiting to retrieve or remove messages from shared invalidation queue. The pg_stat_user_tables and pg_stat_sys_tables views contain the same information, but filtered to only show user and system tables respectively. Waiting for recovery conflict resolution for dropping a tablespace. please use It can be joined to pg_stat_activity or pg_stat_replication on the pid column to get more details about the connection. The pg_stat_slru view will contain one row for each tracked SLRU cache, showing statistics about access to cached pages. See, One row for each tracked function, showing statistics about executions of that function. In addition, background workers registered by extensions may have additional types. The optimizer also accesses indexes to check for supplied constants whose values are outside the recorded range of the optimizer statistics because the optimizer statistics might be stale. Waiting to read or write a data page in memory. This has no effect in a quorum-based synchronous replication. Waiting for a relation data file to reach durable storage. Thus, the server expects something to happen that is independent of its internal processes. Another important point is that when a server process is asked to display any of the accumulated statistics, accessed values are cached until the end of its current transaction in the default configuration. Waiting for a relation data file to reach durable storage. See Section30.5 for more information about the internal WAL function XLogWrite. In a bitmap scan the output of several indexes can be combined via AND or OR rules, so it is difficult to associate individual heap row fetches with specific indexes when a bitmap scan is used. fastpath function call: The backend is executing a fast-path function. Waiting to read or update the last value set for the transaction timestamp. Waiting for activity from child process when executing. Waiting for WAL to reach durable storage during bootstrapping. The columns wal_distance, block_distance and io_depth show current values, and the other columns show cumulative counters that can be reset with the pg_stat_reset_shared function. Waiting for a newly initialized WAL file to reach durable storage. Common causes for the LWLock:BufferIO event to appear in top waits include the following: Multiple backends or connections trying to access the same page that's Doing this helps Only directly connected standbys are listed; no information is available about downstream standby servers. Waiting for a write of a WAL page during bootstrapping. If enabled, calls to user-defined functions and the total time spent in each one are counted as well. Waiting for I/O on a multixact member SLRU buffer. disabled: This state is reported if track_activities is disabled in this backend. Waiting to read or update dynamic shared memory state. Resets some cluster-wide statistics counters to zero, depending on the argument. Waiting for group leader to clear transaction id at transaction end. Waiting for a write to the relation map file. The pg_stat_user_functions view will contain one row for each tracked function, showing statistics about executions of that function. idle in transaction: The backend is in a transaction, but is not currently executing a query. 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. Waiting to access the serializable transaction conflict SLRU cache. Waiting in WAL receiver to establish connection to remote server. In particular, when the standby has caught up completely, pg_stat_replication shows the time taken to write, flush and replay the most recent reported WAL location rather than zero as some users might expect. Waiting to read or update sub-transaction information. Waiting in main loop of logical launcher process. Waiting to read or update information about serializable transactions. Waiting to access the list of finished serializable transactions. Waiting to acquire a speculative insertion lock. Waiting to read or update transaction commit timestamps. Table28.12.pg_stat_database_conflicts View. Waiting for a write while initializing a new WAL file. idle: The backend is waiting for a new client command. See, Only one row, showing statistics about the WAL receiver from that receiver's connected server. Waiting for I/O on a multixact_member buffer. Waiting for a write to a relation data file. Priority of this standby server for being chosen as the synchronous standby in a priority-based synchronous replication. See, One row per replication slot, showing statistics about the replication slot's usage. PostgreSQL is one of the most popular open-source databases in the world and has successful implementations across several mission-critical environments across various domains, using real-time high-end OLTP applications performing millions and billions of transactions per day. Waiting to read or update multixact member mappings. The function pg_stat_get_backend_idset provides a convenient way to generate one row for each active backend for invoking these functions. Waiting for a write to a replication slot control file. For tranches registered by extensions, the name is specified by extension and this will be displayed as wait_event. When analyzing statistics interactively, or with expensive queries, the time delta between accesses to individual statistics can lead to significant skew in the cached statistics. IP address of the client connected to this backend. Time spent reading data file blocks by backends in this database, in milliseconds (if track_io_timing is enabled, otherwise zero), Time spent writing data file blocks by backends in this database, in milliseconds (if track_io_timing is enabled, otherwise zero), Time spent by database sessions in this database, in milliseconds (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), Time spent executing SQL statements in this database, in milliseconds (this corresponds to the states active and fastpath function call in pg_stat_activity), idle_in_transaction_time double precision, Time spent idling while in a transaction in this database, in milliseconds (this corresponds to the states idle in transaction and idle in transaction (aborted) in pg_stat_activity), Total number of sessions established to this database, Number of database sessions to this database that were terminated because connection to the client was lost, Number of database sessions to this database that were terminated by fatal errors, Number of database sessions to this database that were terminated by operator intervention. The pg_stat_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about accesses to that specific table. Waiting for SSL while attempting connection. block. When recovery is performed at server start (e.g., after immediate shutdown, server crash, and point-in-time recovery), all statistics counters are reset. Waiting for a write while adding a line to the data directory lock file. Time when this process' current transaction was started, or null if no transaction is active. Waiting for a read while adding a line to the data directory lock file. Waiting for I/O on a multixact offset SLRU buffer. The IO:DataFileRead wait event occurs while data is Last write-ahead log location sent on this connection, Last write-ahead log location written to disk by this standby server, Last write-ahead log location flushed to disk by this standby server, Last write-ahead log location replayed into the database on this standby server. A database-wide ANALYZE is recommended after the statistics have been reset. See, One row for each index in the current database, showing statistics about accesses to that specific index. These files are stored in the directory named by the stats_temp_directory parameter, pg_stat_tmp by default. To reduce confusion for users expecting a different model of lag, the lag columns revert to NULL after a short time on a fully replayed idle system. See, One row for each backend (including autovacuum worker processes) running, One row only, showing statistics about the WAL archiver process's activity. Indexes can be used by simple index scans, bitmap index scans, and the optimizer. quorum: This standby server is considered as a candidate for quorum standbys. If a backend is in the active state, it may or may not be waiting on some event. For example, to show the PIDs and current queries of all backends: Table28.20. Waiting to create, drop or use a replication origin. Waiting for I/O on a sub-transaction SLRU buffer. LWTRANCHE_BUFFER_CONTENT @ LWTRANCHE_BUFFER_CONTENT. Javascript is disabled or is unavailable in your browser. For client backends, this is the time the client connected to the server. Presently, the collector can count accesses to tables and indexes in both disk-block and individual-row terms. The pg_statio_all_indexes view will contain one row for each index in the current database, showing statistics about I/O on that specific index. From pg_stat_activity i noticed that the wait_event_type and wait_event of these queries is as follows: The latter will be less if any dead or not-yet-committed rows are fetched using the index, or if any heap fetches are avoided by means of an index-only scan. The pg_stat_bgwriter view will always have a single row, containing global data for the cluster. Waiting for changes to a relation data file to reach durable storage. potential: This standby server is now asynchronous, but can potentially become synchronous if one of current synchronous ones fails. Text of this backend's most recent query. (See Chapter20 for details about setting configuration parameters.). See Table28.5 through Table28.13. pg_stat_get_backend_xact_start ( integer ) timestamp with time zone. Waiting to read or update information about the state of synchronous replication. Statistics Collection Configuration, One row per server process, showing information related to the current activity of that process, such as state and current query. Waiting to read or update the state of logical replication workers. Alternatively, you can invoke pg_stat_clear_snapshot(), which will discard the current transaction's statistics snapshot (if any). Table28.34. The server process is waiting for some interaction with another server process. Waiting for activity from a child process while executing a. Waiting for a read of a logical mapping during reorder buffer management. To use the Amazon Web Services Documentation, Javascript must be enabled. See, One row only, showing statistics about WAL activity. See, One row for each sequence in the current database, showing statistics about I/O on that specific sequence. The pg_statio_user_tables and pg_statio_sys_tables views contain the same information, but filtered to only show user and system tables respectively. proc: Waiting to read or update the fast-path lock information. Waiting for a read when creating a new WAL segment by copying an existing one. See, One row per database, showing database-wide statistics. Waiting for I/O on a commit timestamp SLRU buffer. (For example, in psql you could issue \d+ pg_stat_activity.) Waiting to acquire a lock on a non-relation database object. It is used per the rules above. The server process is waiting for an I/O operation to complete. Waiting for a write of a serialized historical catalog snapshot. Waiting for an immediate synchronization of a relation data file to durable storage. Number of backends currently connected to this database. See, One row for each tracked function, showing statistics about executions of that function. This is the only column in this view that returns a value reflecting current state; all other columns return the accumulated values since the last reset. Waiting for a replication origin to become inactive to be dropped. active: The backend is executing a query. Table28.19.pg_stat_subscription_stats View, Number of times an error occurred while applying changes, Number of times an error occurred during the initial table synchronization. Note that only tables, indexes, and functions in the current database can be seen with these functions. The per-index statistics are particularly useful to determine which indexes are being used and how effective they are. Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it (but not yet applied it).