These times represent the commit delay that was (or would have been) introduced by each synchronous commit level, if the remote server was configured as a synchronous standby. Buffer pin waits can be protracted if another process holds an open cursor which last read data from the buffer in question. PostgreSQL's statistics collector is a subsystem that supports collection and reporting of information about server activity. It also tracks the total number of rows in each table, and information about . Host name of the connected client, as reported by a reverse DNS lookup of, TCP port number that the client is using for communication with this backend, or. Waiting to read or update vacuum-related information for a B-tree index. Waiting for a buffered file to be truncated. Therefore, while holding an exclusive lock, a process prevents other processes from acquiring a shared or exclusive lock. 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. By default the query text is truncated at 1024 bytes; this value can be changed via the parameter track_activity_query_size. The pg_stat_all_indexes view will contain one row for each index in the current database, showing statistics about accesses to that specific index. The server process is waiting for a timeout to expire. Waiting for a write to a relation data file. This lock is used to handle multiple sessions that all require access to the same Ordinary users can only see all the information about their own sessions (sessions belonging to a role that they are a member of). The parameter track_io_timing enables monitoring of block read and write times. Waiting for a read from a relation data file. Note that only tables, indexes, and functions in the current database can be seen with these functions. 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 to acquire a lock on page of a relation. Waiting to allocate or assign a transaction id. Waiting for background worker to start up. Time when this process was started. There are also several other views, listed in Table28.2, available to show the accumulated statistics. If, Type of current backend. Resets statistics for a single function in the current database to zero. Alternatively, one can build custom views using the underlying statistics functions, as discussed in Section28.2.3. Waiting for a newly initialized WAL file to reach durable storage. Number of backends currently connected to this database, or NULL for shared objects. Time when this process' current transaction was started, or null if no transaction is active. idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error. The pg_stat_subscription_stats view will contain one row per subscription. Waiting in main loop of checkpointer process. Waiting for a write when creating a new WAL segment by copying an existing one. For better performance, stats_temp_directory can be pointed at a RAM-based file system, decreasing physical I/O requirements. See, One row per database, showing database-wide statistics about query cancels due to conflict with recovery on standby servers. Waiting for logical rewrite mappings to reach durable storage during a checkpoint. Additional functions related to the cumulative statistics system are listed in Table28.34. Waiting to access the sub-transaction SLRU cache. buffer_mapping: Waiting to associate a data block with a buffer in the buffer pool. The argument can be one of CommitTs, MultiXactMember, MultiXactOffset, Notify, Serial, Subtrans, or Xact to reset the counters for only that entry. Number of decoded transactions sent to the decoding output plugin for this slot. 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. Returns the process ID of the server process attached to the current session. This is used by system processes waiting for activity in their main processing loop. Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written it (but not yet flushed it or applied it). When a server, including a physical replica, shuts down cleanly, a permanent copy of the statistics data is stored in the pg_stat subdirectory, so that statistics can be retained across server restarts. PostgreSQL 's statistics collector is a subsystem that supports collection and reporting of information about server activity. See, One row for each table in the current database, showing statistics about accesses to that specific table. Waiting for a two phase state file to reach durable storage. to report a documentation issue. Most such locks protect a particular data structure in shared memory. Waiting to synchronize workers during Parallel Hash Join plan execution. These access functions use a backend ID number, which ranges from one to the number of currently active backends. idle in transaction: The backend is in a transaction, but is not currently executing a query. Number of scheduled checkpoints that have been performed, Number of requested checkpoints that have been performed, Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds, Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in milliseconds, Number of buffers written during checkpoints, Number of buffers written by the background writer, Number of times the background writer stopped a cleaning scan because it had written too many buffers, Number of buffers written directly by a backend, Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write). All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting. Resetting these counters can cause autovacuum to not perform necessary work, which can cause problems such as table bloat or out-dated table statistics. wait_event will contain a name identifying the purpose of the lightweight lock. Waiting to access the multixact offset SLRU cache. Waiting for data to reach durable storage while assigning a new WAL sync method. Waiting to get the start location of a scan on a table for synchronized scans. Waiting to read or record conflicting serializable transactions. 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. Waiting for stats dynamic shared memory allocator access, Waiting for stats shared memory hash table access, Waiting for shared memory stats data access. pg_stat_get_backend_wait_event ( integer ) text. Waiting to read or update transaction status. (For example, in psql you could issue \d+ pg_stat_activity.) See, One row per database, showing database-wide statistics. pg_stat_get_snapshot_timestamp () timestamp with time zone, Returns the timestamp of the current statistics snapshot, or NULL if no statistics snapshot has been taken. The parameter track_wal_io_timing enables monitoring of WAL write times. Send time of last reply message received from standby server. Waiting for a read of a two phase state file. streaming: This WAL sender is streaming changes after its connected standby server has caught up with the primary. Returns the time when the backend's current transaction was started. When using the cumulative statistics views and functions to monitor collected data, it is important to realize that the information does not update instantaneously. A process can wait for the data needed from a client ( Client) or another process ( IPC ). 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. pg_stat_get_backend_userid ( integer ) oid. The idx_tup_read and idx_tup_fetch counts can be different even without any use of bitmap scans, because idx_tup_read counts index entries retrieved from the index while idx_tup_fetch counts live rows fetched from the table. Total number of WAL full page images generated, Number of times WAL data was written to disk because WAL buffers became full. Table28.31.pg_statio_all_sequences View, Number of disk blocks read from this sequence. Waiting for a write to a replication slot control file. Text of this backend's most recent query. See, Only one row, showing statistics about the WAL receiver from that receiver's connected server. The following wait events are a subset of the list in Amazon Aurora PostgreSQL wait events. Waiting to send bytes to a shared message queue. 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. Waiting to read or update the fast-path lock information. pg_stat_get_backend_client_addr ( integer ) inet. Each such lock protects a particular data structure in shared memory. The pg_statio_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about I/O on that specific table. See, One row per connection (regular and replication), showing information about SSL used on this connection. Restrict the maximum number of connections to the database as a best practice. Waiting for a write during reorder buffer management. BufferPin: The server process is waiting to access to a data buffer during a period when no other process can be examining that buffer. Buffer pin waits can be protracted if another process holds an open cursor that last read data from the buffer in question. This is consistent with the goal of measuring synchronous commit and transaction visibility delays for recent write transactions. catchup: This WAL sender's connected standby is catching up with the primary. I am not the DBA, but receive reports occasionally when an application is causing load on the system. This has no effect in a quorum-based synchronous replication. Waiting for I/O on an async (notify) buffer. If the state is active and wait_event is non-null, it means that a query is being executed, but is being blocked somewhere in the system. The pg_stat_user_functions view will contain one row for each tracked function, showing statistics about executions of that function. 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). The idx_tup_read and idx_tup_fetch counts can be different even without any use of bitmap scans, because idx_tup_read counts index entries retrieved from the index while idx_tup_fetch counts live rows fetched from the table. David Christensen on Twitter. Waiting for a replication origin to become inactive to be dropped. You can split your If the argument is other (or indeed, any unrecognized name), then the counters for all other SLRU caches, such as extension-defined caches, are reset. (Some locks have specific names; others are part of a group of locks each with a similar purpose.). (See Chapter19 for details about setting configuration parameters.). Client: The server process is waiting for some activity on a socket from user applications, and that the server expects something to happen that is independent from its internal processes. Waiting for confirmation from remote server during synchronous replication. Note that this includes the transactions that are streamed and/or spilled. For tranches registered by extensions, the name is specified by extension and this will be displayed as wait_event. If the argument is NULL, resets statistics for all the replication slots. See, One row for each table in the current database, showing statistics about I/O on that specific table. This is controlled by configuration parameters that are normally set in postgresql.conf. Please refer to your browser's Help pages for instructions. Synchronous state of this standby server. The LWLock:BufferIO event occurs when Aurora PostgreSQL or RDS for PostgreSQL is waiting for other processes to finish their input/output (I/O) operations when concurrently trying to access a page. Waiting to find or allocate space in shared memory. See, One row for each index in the current database, showing statistics about I/O on that specific index. Waiting to associate a data block with a buffer in the buffer pool. This is consistent with the goal of measuring synchronous commit and transaction visibility delays for recent write transactions. Waiting to ensure that a table selected for autovacuum still needs vacuuming. Other ways of looking at the statistics can be set up by writing queries that use the same underlying statistics access functions used by the standard views shown above. Waiting to acquire an exclusive pin on a buffer. Waiting for a read while adding a line to the data directory lock file. Waiting for an elected Parallel Hash participant to finish allocating more buckets. The LWLock:BufferIO wait event precedes the IO:DataFileRead wait event. Waiting to read or update the progress of one replication origin. quorum: This standby server is considered as a candidate for quorum standbys. 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. This can be a host name, an IP address, or a directory path if the connection is via Unix socket. Waiting for a relation data file to reach durable storage. Waiting in main loop of WAL receiver process. Waiting in main loop of autovacuum launcher process. Waiting for a read during a file copy operation. See, One row only, showing statistics about the background writer process's activity. Waiting to elect a Parallel Hash participant to allocate the initial hash table. This field will only be non-null for IP connections, and only when log_hostname is enabled. * The BM_IO_IN_PROGRESS flag acts as a kind of lock, used to wait for I/O on a: buffer to complete (and in releases before 14, it was accompanied by a: per-buffer LWLock). The type of event for which the backend is waiting, if any; otherwise NULL. The access functions for per-database statistics take a database OID as an argument to identify which database to report on. In addition, background workers registered by extensions may have additional types. Alternatively, you can invoke pg_stat_clear_snapshot(), which will discard the current transaction's statistics snapshot (if any). Number of times in-progress transactions were streamed to the decoding output plugin while decoding changes from WAL for this slot. See, One row per database, showing database-wide statistics. Waiting to acquire a virtual transaction ID lock. Waiting to manage an extension's space allocation in shared memory. (Conflicts occur only on standby servers; see, Number of temporary files created by queries in this database. Waiting for a read from a relation data file. Waiting to access the shared per-process data structures (typically, to get a snapshot or report a session's transaction ID). In order to write the disk block into buffer memory, the buffer cache's hash table entry needs updating. Waiting for a WAL file to reach durable storage. Last write-ahead log location already received and written to disk, but not flushed. Number of deadlocks detected in this database. Waiting to read or update the state of logical replication workers. (To prevent ordinary users from hiding their activity from the administrator, only superusers are allowed to change these parameters with SET.). The counter gets incremented for both top-level transactions and subtransactions. Waiting in main loop of syslogger process. Waiting in WAL receiver to receive data from remote server. IP address of the client connected to this WAL sender. disabled: This state is reported if track_activities is disabled in this backend. Waiting for a write when creating a new WAL segment by copying an existing one. (The path case can be distinguished because it will always be an absolute path, beginning with /.). This category is useful for modules to track custom waiting points. Waiting to access the list of predicate locks held by serializable transactions. This facility is independent of the collector process. Waiting in main loop of autovacuum launcher process. The lag times reported in the pg_stat_replication view are measurements of the time taken for recent WAL to be written, flushed and replayed and for the sender to know about it. The server process is waiting for some condition defined by an extension module. The per-table and per-index functions take a table or index OID. Waiting to access predicate lock information used by serializable transactions. Then identify which query The pg_stat_archiver view will always have a single row, containing data about the archiver process of the cluster. LWLock:buffer_mapping. 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, flushed and applied it. Additional functions related to statistics collection are listed in Table28.19. Waiting for confirmation from a remote server during synchronous replication. 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 to access a shared tuple store during parallel query. If enabled, calls to user-defined functions and the total time spent in each one are counted as well. BufferCacheHitRatio metric dips. From the Actions drop-down menu, choose Create Read Replica. The reported lag times are not predictions of how long it will take for the standby to catch up with the sending server assuming the current rate of replay. I'd like to know more about what these locks could imply if anything. Waiting to ensure that the table it has selected for a vacuum still needs vacuuming. See, One row only, showing statistics about WAL activity. Waiting for changes to a relation data file to reach durable storage. Timeout: The server process is waiting for a timeout to expire. Thanks for letting us know this page needs work. When the server shuts down cleanly, a permanent copy of the statistics data is stored in the pg_stat subdirectory, so that statistics can be retained across server restarts. Waiting for a write while creating the data directory lock file. A transaction can also see its own statistics (not yet flushed out to the shared memory statistics) in the views pg_stat_xact_all_tables, pg_stat_xact_sys_tables, pg_stat_xact_user_tables, and pg_stat_xact_user_functions. IP address of the client connected to this backend. Process ID of the parallel group leader, if this process is a parallel query worker. These files are stored in the directory named by the stats_temp_directory parameter, pg_stat_tmp by default. Resets statistics to zero for a single SLRU cache, or for all SLRUs in the cluster. Amount of transaction data decoded for sending transactions to the decoding output plugin while decoding changes from WAL for this slot. Waiting for a write of a newly created timeline history file. Waiting for a new WAL segment created by copying an existing one to reach durable storage. Alternatively, one can build custom views using the underlying cumulative statistics functions, as discussed in Section28.2.24. See, At least one row per subscription, showing information about the subscription workers. async: This standby server is asynchronous. Waiting for a barrier event to be processed by all backends. The parameter track_counts controls whether cumulative statistics are collected about table and index accesses. Waiting for a write of a two phase state file. See, One row for each index in the current database, showing statistics about I/O on that specific index. This field is truncated if the principal is longer than NAMEDATALEN (64 characters in a standard build). NULL if this process is a parallel group leader or does not participate in parallel query. Waiting for the version file to be written while creating a database. Note, however, that the existence of a session and its general properties such as its sessions user and database are visible to all users. Waiting to read or update dynamic shared memory allocation information. See. Possible values are: active: The backend is executing a query. Waiting for the page number needed to continue a parallel B-tree scan to become available. Waiting to read or update the last value set for a transaction commit timestamp. Number of backends currently connected to this database. These access functions use a backend ID number, which ranges from one to the number of currently active backends. Activity: The server process is idle. The pg_stat_replication view will contain one row per WAL sender process, showing statistics about replication to that sender's connected standby server. However, these statistics do not give the entire story: due to the way in which PostgreSQL handles disk I/O, data that is not in the PostgreSQL buffer cache might still reside in the kernel's I/O cache, and might therefore still be fetched without requiring a physical read. potential: This standby server is now asynchronous, but can potentially become synchronous if one of current synchronous ones fails. Waiting to elect a Parallel Hash participant to allocate more batches. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting. backup: This WAL sender is sending a backup. This and other spill counters can be used to gauge the I/O which occurred during logical decoding and allow tuning logical_decoding_work_mem. might be causing it. See Table28.5 through Table28.13. Waiting to read or update the state of prepared transactions. Returns the time when the backend's most recent query was started. See, One row for each sequence in the current database, showing statistics about I/O on that specific sequence. Lag times work automatically for physical replication. It is quite possible that user has registered the tranche in one of the backends (by having allocation in dynamic shared memory) in which case other backends won't have that information, so we display extension for such cases. Waiting for an immediate synchronization of a relation data file to durable storage. . backup: This WAL sender is sending a backup. Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it (but not yet applied it). Extension: The server process is waiting for activity in an extension module. Waiting for a newly initialized WAL file to reach durable storage. Priority of this standby server for being chosen as the synchronous standby in a priority-based synchronous replication. Returns the OID of the user logged into this backend. Waiting to retrieve or remove messages from shared invalidation queue. Waiting to acquire an exclusive lock to truncate off any empty pages at the end of a table vacuumed. A process acquires an LWLock in a shared mode to read from the buffer and . Since collection of statistics adds some overhead to query execution, the system can be configured to collect or not collect information. Waiting to replace a page in WAL buffers. Waiting to read or update the control file or creation of a new WAL file. For more information on lightweight locks, see Locking Overview. Lag times work automatically for physical replication. The pg_stat_wal_receiver view will contain only one row, showing statistics about the WAL receiver from that receiver's connected server.