When the buffer manager receives a request, PostgreSQL uses the buffer_tag of the desired page. This is used by system processes waiting for activity in their main processing loop. Waiting for other Parallel Hash participants to finish partitioning the outer relation. 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. Waiting for a write to a replication slot control file. pg_stat_get_backend_client_addr ( integer ) inet. Waiting to send bytes to a shared message queue. 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). The pg_stat_user_indexes and pg_stat_sys_indexes views contain the same information, but filtered to only show user and system indexes respectively. Waiting to access the shared per-process data structures (typically, to get a snapshot or report a session's transaction ID). In such cases, an older set of per-backend statistics access functions can be used; these are shown in Table28.20. The generated IO patterns are also much worse. If the argument is NULL, all counters shown in the pg_stat_slru view for all SLRU caches are reset. Waiting to update limit on notification message storage. A transaction can also see its own statistics (as yet untransmitted to the collector) in the views pg_stat_xact_all_tables, pg_stat_xact_sys_tables, pg_stat_xact_user_tables, and pg_stat_xact_user_functions. Waiting to find or allocate space in shared memory. wait_event will identify the specific wait point. Normally, WAL files are archived in order, oldest to newest, but that is not guaranteed, and does not hold under special circumstances like when promoting a standby or after crash recovery. The type of event for which the backend is waiting, if any; otherwise NULL. Waiting to add a message to the shared catalog invalidation queue. Waiting for a write while initializing a new WAL file. Waiting in main loop of WAL writer process. The parameter track_activities enables monitoring of the current command being executed by any server process. 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 Waiting to read or update notification messages. Possible types are autovacuum launcher, autovacuum worker, logical replication launcher, logical replication worker, parallel worker, background writer, client backend, checkpointer, archiver, startup, walreceiver, walsender and walwriter. Returns the time when this process was started. See, Only one row, showing statistics about the WAL receiver from that receiver's connected server. This field is truncated if the DN field is longer than NAMEDATALEN (64 characters in a standard build). Waiting to add a message in shared invalidation queue. idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error. Waiting for a write of a newly created timeline history file. Waiting to read while creating the data directory lock file. backup: This WAL sender is sending a backup. idle in transaction: The backend is in a transaction, but is not currently executing a query. Waiting for a write to a relation data file. These numbers do not act as stated above; instead they update continuously throughout the transaction. For more information on lightweight locks, see Locking Overview. This view will only contain information on standby servers, since conflicts do not occur on master servers. There is no order to the granting of LWLocks and in a high concurrency system this can cause contention. Priority of this standby server for being chosen as the synchronous standby in a priority-based synchronous replication. Waiting to acquire a virtual transaction ID lock. shared_buffers parameter. See, At least one row per subscription, showing information about the subscription workers. If you've got a moment, please tell us how we can make the documentation better. The pg_statio_ views are primarily useful to determine the effectiveness of the buffer cache. Waiting for the relation map file to reach durable storage. Waiting to read or update dynamic shared memory allocation information. For tranches registered by extensions, the name is specified by extension and this will be displayed as wait_event. Aurora PostgreSQL wait events PDF RSS The following table lists the wait events for Aurora PostgreSQL that most commonly indicate performance problems, and summarizes the most common causes and corrective actions. The server process is waiting for a timeout to expire. Waiting to read or update the last value set for a transaction commit timestamp. Use partitioned tables (which also have partitioned indexes). 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. 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. Waiting to apply WAL during recovery because of a delay setting. If a backend is in the active state, it may or may not be waiting on some event. The pg_statio_user_indexes and pg_statio_sys_indexes views contain the same information, but filtered to only show user and system indexes respectively. Then identify which query 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 server process is waiting for exclusive access to a data buffer. The server process is waiting for some condition defined by an extension module. Table28.12.pg_stat_database_conflicts View. Waiting to read or write relation cache initialization file. Waiting for a read during recheck of the data directory lock file. Waiting to retrieve or store information about serializable transactions. See Table28.4. Simple test for lock_waits log messages. Waiting for logical rewrite mappings to reach durable storage during a checkpoint. Waiting for WAL to reach durable storage during bootstrapping. Autovacuum worker or launcher waiting to update or read the current state of autovacuum workers. Waiting for changes to a relation data file to reach durable storage. Waiting for background worker to shut down. Amount of transaction data decoded for sending transactions to the decoding output plugin while decoding changes from WAL for this slot. Waiting for a timeline history file received via streaming replication to reach durable storage. If state is active this field shows the identifier of the currently executing query. Waiting for SSL while attempting connection. All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the, Total amount of data written to temporary files by queries in this database. In contrast, when starting from an unclean shutdown (e.g., after an immediate shutdown, a server crash, starting from a base backup, and point-in-time recovery), all statistics counters are reset. If this field is null, it indicates that this is an internal server process. Waiting to access the serializable transaction conflict SLRU cache. Waiting to read or update background worker state. If this field is null, it indicates that the client is connected via a Unix socket on the server machine. See, One row for each index in the current database, showing statistics about accesses to that specific index. active: The backend is executing a query. The pg_stat_replication_slots view will contain one row per logical replication slot, showing statistics about its usage. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting. After v9.5, the buffer cache is protected by 128 hash tables each protected by a LWLock. Amount of transaction data decoded for streaming in-progress transactions to the decoding output plugin while decoding changes from WAL for this slot. Waiting to elect a Parallel Hash participant to decide on future batch growth. For more information, see LWLock:buffer_content (BufferContent). Waiting for a write of a serialized historical catalog snapshot. Returns the wait event type name if this backend is currently waiting, otherwise NULL. PostgreSQL utilizes lightweight locks (LWLocks) to synchronize and control access to the buffer content. Waiting to update limits on transaction id and multixact consumption. See, Only one row, showing statistics about blocks prefetched during recovery. Table28.19. Waiting for a relation data file to be extended. The pg_stat_user_functions view will contain one row for each tracked function, showing statistics about executions of that function. The pg_stat_database_conflicts view will contain one row per database, showing database-wide statistics about query cancels occurring due to conflicts with recovery on standby servers. Number of WAL files that have been successfully archived, Name of the WAL file most recently successfully archived, last_archived_time timestamp with time zone, Time of the most recent successful archive operation, Number of failed attempts for archiving WAL files, Name of the WAL file of the most recent failed archival operation, last_failed_time timestamp with time zone, Time of the most recent failed archival operation. The argument can be one of CommitTs, MultiXactMember, MultiXactOffset, Notify, Serial, Subtrans, or Xact to reset the counters for only that entry. The type of event for which the backend is waiting, if any; otherwise NULL. pg_stat_reset_subscription_stats ( oid ) void. The statistics collector transmits the collected information to other PostgreSQL processes through temporary files. Waiting to read or update the fast-path lock information. However, current-query information collected by track_activities is always up-to-date. Therefore, a bitmap scan increments the pg_stat_all_indexes.idx_tup_read count(s) for the index(es) it uses, and it increments the pg_stat_all_tables.idx_tup_fetch count for the table, but it does not affect pg_stat_all_indexes.idx_tup_fetch. OID of the user logged into this WAL sender process, Name of the user logged into this WAL sender process, Name of the application that is connected to this WAL sender. Waiting for parallel bitmap scan to become initialized. Waiting for a read of a two phase state file. The functions for per-function statistics take a function OID. Waiting for confirmation from remote server during synchronous replication.