1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>27.2. The Cumulative Statistics System</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="monitoring-ps.html" title="27.1. Standard Unix Tools" /><link rel="next" href="monitoring-locks.html" title="27.3. Viewing Locks" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">27.2. The Cumulative Statistics System</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="monitoring-ps.html" title="27.1. Standard Unix Tools">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="monitoring.html" title="Chapter 27. Monitoring Database Activity">Up</a></td><th width="60%" align="center">Chapter 27. Monitoring Database Activity</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="monitoring-locks.html" title="27.3. Viewing Locks">Next</a></td></tr></table><hr /></div><div class="sect1" id="MONITORING-STATS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">27.2. The Cumulative Statistics System <a href="#MONITORING-STATS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-STATS-SETUP">27.2.1. Statistics Collection Configuration</a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-STATS-VIEWS">27.2.2. Viewing Statistics</a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW">27.2.3. <code class="structname">pg_stat_activity</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW">27.2.4. <code class="structname">pg_stat_replication</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW">27.2.5. <code class="structname">pg_stat_replication_slots</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-WAL-RECEIVER-VIEW">27.2.6. <code class="structname">pg_stat_wal_receiver</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-RECOVERY-PREFETCH">27.2.7. <code class="structname">pg_stat_recovery_prefetch</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-SUBSCRIPTION">27.2.8. <code class="structname">pg_stat_subscription</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-SUBSCRIPTION-STATS">27.2.9. <code class="structname">pg_stat_subscription_stats</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-SSL-VIEW">27.2.10. <code class="structname">pg_stat_ssl</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-GSSAPI-VIEW">27.2.11. <code class="structname">pg_stat_gssapi</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-ARCHIVER-VIEW">27.2.12. <code class="structname">pg_stat_archiver</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW">27.2.13. <code class="structname">pg_stat_io</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-BGWRITER-VIEW">27.2.14. <code class="structname">pg_stat_bgwriter</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-CHECKPOINTER-VIEW">27.2.15. <code class="structname">pg_stat_checkpointer</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW">27.2.16. <code class="structname">pg_stat_wal</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW">27.2.17. <code class="structname">pg_stat_database</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-DATABASE-CONFLICTS-VIEW">27.2.18. <code class="structname">pg_stat_database_conflicts</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW">27.2.19. <code class="structname">pg_stat_all_tables</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW">27.2.20. <code class="structname">pg_stat_all_indexes</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STATIO-ALL-TABLES-VIEW">27.2.21. <code class="structname">pg_statio_all_tables</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STATIO-ALL-INDEXES-VIEW">27.2.22. <code class="structname">pg_statio_all_indexes</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STATIO-ALL-SEQUENCES-VIEW">27.2.23. <code class="structname">pg_statio_all_sequences</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-USER-FUNCTIONS-VIEW">27.2.24. <code class="structname">pg_stat_user_functions</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-PG-STAT-SLRU-VIEW">27.2.25. <code class="structname">pg_stat_slru</code></a></span></dt><dt><span class="sect2"><a href="monitoring-stats.html#MONITORING-STATS-FUNCTIONS">27.2.26. Statistics Functions</a></span></dt></dl></div><a id="id-1.6.14.7.2" class="indexterm"></a><p>
3 <span class="productname">PostgreSQL</span>'s <em class="firstterm">cumulative statistics
4 system</em> supports collection and reporting of information about
5 server activity. Presently, accesses to tables and indexes in both
6 disk-block and individual-row terms are counted. The total number of rows
7 in each table, and information about vacuum and analyze actions for each
8 table are also counted. If enabled, calls to user-defined functions and
9 the total time spent in each one are counted as well.
11 <span class="productname">PostgreSQL</span> also supports reporting dynamic
12 information about exactly what is going on in the system right now, such as
13 the exact command currently being executed by other server processes, and
14 which other connections exist in the system. This facility is independent
15 of the cumulative statistics system.
16 </p><div class="sect2" id="MONITORING-STATS-SETUP"><div class="titlepage"><div><div><h3 class="title">27.2.1. Statistics Collection Configuration <a href="#MONITORING-STATS-SETUP" class="id_link">#</a></h3></div></div></div><p>
17 Since collection of statistics adds some overhead to query execution,
18 the system can be configured to collect or not collect information.
19 This is controlled by configuration parameters that are normally set in
20 <code class="filename">postgresql.conf</code>. (See <a class="xref" href="runtime-config.html" title="Chapter 19. Server Configuration">Chapter 19</a> for
21 details about setting configuration parameters.)
23 The parameter <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-ACTIVITIES">track_activities</a> enables monitoring
24 of the current command being executed by any server process.
26 The parameter <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-COST-DELAY-TIMING">track_cost_delay_timing</a> enables
27 monitoring of cost-based vacuum delay.
29 The parameter <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-COUNTS">track_counts</a> controls whether
30 cumulative statistics are collected about table and index accesses.
32 The parameter <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-FUNCTIONS">track_functions</a> enables tracking of
33 usage of user-defined functions.
35 The parameter <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> enables monitoring
36 of block read, write, extend, and fsync times.
38 The parameter <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-WAL-IO-TIMING">track_wal_io_timing</a> enables monitoring
39 of WAL read, write and fsync times.
41 Normally these parameters are set in <code class="filename">postgresql.conf</code> so
42 that they apply to all server processes, but it is possible to turn
43 them on or off in individual sessions using the <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a> command. (To prevent
44 ordinary users from hiding their activity from the administrator,
45 only superusers are allowed to change these parameters with
46 <code class="command">SET</code>.)
48 Cumulative statistics are collected in shared memory. Every
49 <span class="productname">PostgreSQL</span> process collects statistics locally,
50 then updates the shared data at appropriate intervals. When a server,
51 including a physical replica, shuts down cleanly, a permanent copy of the
52 statistics data is stored in the <code class="filename">pg_stat</code> subdirectory,
53 so that statistics can be retained across server restarts. In contrast,
54 when starting from an unclean shutdown (e.g., after an immediate shutdown,
55 a server crash, starting from a base backup, and point-in-time recovery),
56 all statistics counters are reset.
57 </p></div><div class="sect2" id="MONITORING-STATS-VIEWS"><div class="titlepage"><div><div><h3 class="title">27.2.2. Viewing Statistics <a href="#MONITORING-STATS-VIEWS" class="id_link">#</a></h3></div></div></div><p>
58 Several predefined views, listed in <a class="xref" href="monitoring-stats.html#MONITORING-STATS-DYNAMIC-VIEWS-TABLE" title="Table 27.1. Dynamic Statistics Views">Table 27.1</a>, are available to show
59 the current state of the system. There are also several other
60 views, listed in <a class="xref" href="monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE" title="Table 27.2. Collected Statistics Views">Table 27.2</a>, available to show the accumulated
61 statistics. Alternatively, one can
62 build custom views using the underlying cumulative statistics functions, as
63 discussed in <a class="xref" href="monitoring-stats.html#MONITORING-STATS-FUNCTIONS" title="27.2.26. Statistics Functions">Section 27.2.26</a>.
65 When using the cumulative statistics views and functions to monitor
66 collected data, it is important to realize that the information does not
67 update instantaneously. Each individual server process flushes out
68 accumulated statistics to shared memory just before going idle, but not
69 more frequently than once per <code class="varname">PGSTAT_MIN_INTERVAL</code>
70 milliseconds (1 second unless altered while building the server); so a
71 query or transaction still in progress does not affect the displayed totals
72 and the displayed information lags behind actual activity. However,
73 current-query information collected by <code class="varname">track_activities</code>
76 Another important point is that when a server process is asked to display
77 any of the accumulated statistics, accessed values are cached until the end
78 of its current transaction in the default configuration. So the statistics
79 will show static information as long as you continue the current
80 transaction. Similarly, information about the current queries of all
81 sessions is collected when any such information is first requested within a
82 transaction, and the same information will be displayed throughout the
83 transaction. This is a feature, not a bug, because it allows you to perform
84 several queries on the statistics and correlate the results without
85 worrying that the numbers are changing underneath you.
87 When analyzing statistics interactively, or with expensive queries, the
88 time delta between accesses to individual statistics can lead to
89 significant skew in the cached statistics. To minimize skew,
90 <code class="varname">stats_fetch_consistency</code> can be set to
91 <code class="literal">snapshot</code>, at the price of increased memory usage for
92 caching not-needed statistics data. Conversely, if it's known that
93 statistics are only accessed once, caching accessed statistics is
94 unnecessary and can be avoided by setting
95 <code class="varname">stats_fetch_consistency</code> to <code class="literal">none</code>.
97 You can invoke <code class="function">pg_stat_clear_snapshot()</code> to discard the
98 current transaction's statistics snapshot or cached values (if any). The
99 next use of statistical information will (when in snapshot mode) cause a
100 new snapshot to be built or (when in cache mode) accessed statistics to be
103 A transaction can also see its own statistics (not yet flushed out to the
104 shared memory statistics) in the views
105 <code class="structname">pg_stat_xact_all_tables</code>,
106 <code class="structname">pg_stat_xact_sys_tables</code>,
107 <code class="structname">pg_stat_xact_user_tables</code>, and
108 <code class="structname">pg_stat_xact_user_functions</code>. These numbers do not act as
109 stated above; instead they update continuously throughout the transaction.
111 Some of the information in the dynamic statistics views shown in <a class="xref" href="monitoring-stats.html#MONITORING-STATS-DYNAMIC-VIEWS-TABLE" title="Table 27.1. Dynamic Statistics Views">Table 27.1</a> is security restricted.
112 Ordinary users can only see all the information about their own sessions
113 (sessions belonging to a role that they are a member of). In rows about
114 other sessions, many columns will be null. Note, however, that the
115 existence of a session and its general properties such as its sessions user
116 and database are visible to all users. Superusers and roles with privileges of
117 built-in role <a class="link" href="predefined-roles.html#PREDEFINED-ROLE-PG-MONITOR"><code class="literal">pg_read_all_stats</code></a>
118 can see all the information about all sessions.
119 </p><div class="table" id="MONITORING-STATS-DYNAMIC-VIEWS-TABLE"><p class="title"><strong>Table 27.1. Dynamic Statistics Views</strong></p><div class="table-contents"><table class="table" summary="Dynamic Statistics Views" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>View Name</th><th>Description</th></tr></thead><tbody><tr><td>
120 <code class="structname">pg_stat_activity</code>
121 <a id="id-1.6.14.7.6.7.2.2.1.1.2" class="indexterm"></a>
123 One row per server process, showing information related to
124 the current activity of that process, such as state and current query.
125 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" title="27.2.3. pg_stat_activity">
126 <code class="structname">pg_stat_activity</code></a> for details.
127 </td></tr><tr><td><code class="structname">pg_stat_replication</code><a id="id-1.6.14.7.6.7.2.2.2.1.2" class="indexterm"></a></td><td>One row per WAL sender process, showing statistics about
128 replication to that sender's connected standby server.
129 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW" title="27.2.4. pg_stat_replication">
130 <code class="structname">pg_stat_replication</code></a> for details.
131 </td></tr><tr><td><code class="structname">pg_stat_wal_receiver</code><a id="id-1.6.14.7.6.7.2.2.3.1.2" class="indexterm"></a></td><td>Only one row, showing statistics about the WAL receiver from
132 that receiver's connected server.
133 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-WAL-RECEIVER-VIEW" title="27.2.6. pg_stat_wal_receiver">
134 <code class="structname">pg_stat_wal_receiver</code></a> for details.
135 </td></tr><tr><td><code class="structname">pg_stat_recovery_prefetch</code><a id="id-1.6.14.7.6.7.2.2.4.1.2" class="indexterm"></a></td><td>Only one row, showing statistics about blocks prefetched during recovery.
136 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-RECOVERY-PREFETCH" title="27.2.7. pg_stat_recovery_prefetch">
137 <code class="structname">pg_stat_recovery_prefetch</code></a> for details.
138 </td></tr><tr><td><code class="structname">pg_stat_subscription</code><a id="id-1.6.14.7.6.7.2.2.5.1.2" class="indexterm"></a></td><td>At least one row per subscription, showing information about
139 the subscription workers.
140 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-SUBSCRIPTION" title="27.2.8. pg_stat_subscription">
141 <code class="structname">pg_stat_subscription</code></a> for details.
142 </td></tr><tr><td><code class="structname">pg_stat_ssl</code><a id="id-1.6.14.7.6.7.2.2.6.1.2" class="indexterm"></a></td><td>One row per connection (regular and replication), showing information about
143 SSL used on this connection.
144 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-SSL-VIEW" title="27.2.10. pg_stat_ssl">
145 <code class="structname">pg_stat_ssl</code></a> for details.
146 </td></tr><tr><td><code class="structname">pg_stat_gssapi</code><a id="id-1.6.14.7.6.7.2.2.7.1.2" class="indexterm"></a></td><td>One row per connection (regular and replication), showing information about
147 GSSAPI authentication and encryption used on this connection.
148 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-GSSAPI-VIEW" title="27.2.11. pg_stat_gssapi">
149 <code class="structname">pg_stat_gssapi</code></a> for details.
150 </td></tr><tr><td><code class="structname">pg_stat_progress_analyze</code><a id="id-1.6.14.7.6.7.2.2.8.1.2" class="indexterm"></a></td><td>One row for each backend (including autovacuum worker processes) running
151 <code class="command">ANALYZE</code>, showing current progress.
152 See <a class="xref" href="progress-reporting.html#ANALYZE-PROGRESS-REPORTING" title="27.4.1. ANALYZE Progress Reporting">Section 27.4.1</a>.
153 </td></tr><tr><td><code class="structname">pg_stat_progress_create_index</code><a id="id-1.6.14.7.6.7.2.2.9.1.2" class="indexterm"></a></td><td>One row for each backend running <code class="command">CREATE INDEX</code> or <code class="command">REINDEX</code>, showing
155 See <a class="xref" href="progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING" title="27.4.4. CREATE INDEX Progress Reporting">Section 27.4.4</a>.
156 </td></tr><tr><td><code class="structname">pg_stat_progress_vacuum</code><a id="id-1.6.14.7.6.7.2.2.10.1.2" class="indexterm"></a></td><td>One row for each backend (including autovacuum worker processes) running
157 <code class="command">VACUUM</code>, showing current progress.
158 See <a class="xref" href="progress-reporting.html#VACUUM-PROGRESS-REPORTING" title="27.4.5. VACUUM Progress Reporting">Section 27.4.5</a>.
159 </td></tr><tr><td><code class="structname">pg_stat_progress_cluster</code><a id="id-1.6.14.7.6.7.2.2.11.1.2" class="indexterm"></a></td><td>One row for each backend running
160 <code class="command">CLUSTER</code> or <code class="command">VACUUM FULL</code>, showing current progress.
161 See <a class="xref" href="progress-reporting.html#CLUSTER-PROGRESS-REPORTING" title="27.4.2. CLUSTER Progress Reporting">Section 27.4.2</a>.
162 </td></tr><tr><td><code class="structname">pg_stat_progress_basebackup</code><a id="id-1.6.14.7.6.7.2.2.12.1.2" class="indexterm"></a></td><td>One row for each WAL sender process streaming a base backup,
163 showing current progress.
164 See <a class="xref" href="progress-reporting.html#BASEBACKUP-PROGRESS-REPORTING" title="27.4.6. Base Backup Progress Reporting">Section 27.4.6</a>.
165 </td></tr><tr><td><code class="structname">pg_stat_progress_copy</code><a id="id-1.6.14.7.6.7.2.2.13.1.2" class="indexterm"></a></td><td>One row for each backend running <code class="command">COPY</code>, showing current progress.
166 See <a class="xref" href="progress-reporting.html#COPY-PROGRESS-REPORTING" title="27.4.3. COPY Progress Reporting">Section 27.4.3</a>.
167 </td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="MONITORING-STATS-VIEWS-TABLE"><p class="title"><strong>Table 27.2. Collected Statistics Views</strong></p><div class="table-contents"><table class="table" summary="Collected Statistics Views" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>View Name</th><th>Description</th></tr></thead><tbody><tr><td><code class="structname">pg_stat_archiver</code><a id="id-1.6.14.7.6.8.2.2.1.1.2" class="indexterm"></a></td><td>One row only, showing statistics about the
168 WAL archiver process's activity. See
169 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ARCHIVER-VIEW" title="27.2.12. pg_stat_archiver">
170 <code class="structname">pg_stat_archiver</code></a> for details.
171 </td></tr><tr><td><code class="structname">pg_stat_bgwriter</code><a id="id-1.6.14.7.6.8.2.2.2.1.2" class="indexterm"></a></td><td>One row only, showing statistics about the
172 background writer process's activity. See
173 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-BGWRITER-VIEW" title="27.2.14. pg_stat_bgwriter">
174 <code class="structname">pg_stat_bgwriter</code></a> for details.
175 </td></tr><tr><td><code class="structname">pg_stat_checkpointer</code><a id="id-1.6.14.7.6.8.2.2.3.1.2" class="indexterm"></a></td><td>One row only, showing statistics about the
176 checkpointer process's activity. See
177 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-CHECKPOINTER-VIEW" title="27.2.15. pg_stat_checkpointer">
178 <code class="structname">pg_stat_checkpointer</code></a> for details.
179 </td></tr><tr><td><code class="structname">pg_stat_database</code><a id="id-1.6.14.7.6.8.2.2.4.1.2" class="indexterm"></a></td><td>One row per database, showing database-wide statistics. See
180 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW" title="27.2.17. pg_stat_database">
181 <code class="structname">pg_stat_database</code></a> for details.
182 </td></tr><tr><td><code class="structname">pg_stat_database_conflicts</code><a id="id-1.6.14.7.6.8.2.2.5.1.2" class="indexterm"></a></td><td>
183 One row per database, showing database-wide statistics about
184 query cancels due to conflict with recovery on standby servers.
185 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-DATABASE-CONFLICTS-VIEW" title="27.2.18. pg_stat_database_conflicts">
186 <code class="structname">pg_stat_database_conflicts</code></a> for details.
187 </td></tr><tr><td><code class="structname">pg_stat_io</code><a id="id-1.6.14.7.6.8.2.2.6.1.2" class="indexterm"></a></td><td>
188 One row for each combination of backend type, context, and target object
189 containing cluster-wide I/O statistics.
190 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW" title="27.2.13. pg_stat_io">
191 <code class="structname">pg_stat_io</code></a> for details.
192 </td></tr><tr><td><code class="structname">pg_stat_replication_slots</code><a id="id-1.6.14.7.6.8.2.2.7.1.2" class="indexterm"></a></td><td>One row per replication slot, showing statistics about the
193 replication slot's usage. See
194 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW" title="27.2.5. pg_stat_replication_slots">
195 <code class="structname">pg_stat_replication_slots</code></a> for details.
196 </td></tr><tr><td><code class="structname">pg_stat_slru</code><a id="id-1.6.14.7.6.8.2.2.8.1.2" class="indexterm"></a></td><td>One row per SLRU, showing statistics of operations. See
197 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-SLRU-VIEW" title="27.2.25. pg_stat_slru">
198 <code class="structname">pg_stat_slru</code></a> for details.
199 </td></tr><tr><td><code class="structname">pg_stat_subscription_stats</code><a id="id-1.6.14.7.6.8.2.2.9.1.2" class="indexterm"></a></td><td>One row per subscription, showing statistics about errors and conflicts.
200 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-SUBSCRIPTION-STATS" title="27.2.9. pg_stat_subscription_stats">
201 <code class="structname">pg_stat_subscription_stats</code></a> for details.
202 </td></tr><tr><td><code class="structname">pg_stat_wal</code><a id="id-1.6.14.7.6.8.2.2.10.1.2" class="indexterm"></a></td><td>One row only, showing statistics about WAL activity. See
203 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW" title="27.2.16. pg_stat_wal">
204 <code class="structname">pg_stat_wal</code></a> for details.
205 </td></tr><tr><td><code class="structname">pg_stat_all_tables</code><a id="id-1.6.14.7.6.8.2.2.11.1.2" class="indexterm"></a></td><td>
206 One row for each table in the current database, showing statistics
207 about accesses to that specific table.
208 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW" title="27.2.19. pg_stat_all_tables">
209 <code class="structname">pg_stat_all_tables</code></a> for details.
210 </td></tr><tr><td><code class="structname">pg_stat_sys_tables</code><a id="id-1.6.14.7.6.8.2.2.12.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_stat_all_tables</code>, except that only
211 system tables are shown.</td></tr><tr><td><code class="structname">pg_stat_user_tables</code><a id="id-1.6.14.7.6.8.2.2.13.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_stat_all_tables</code>, except that only user
212 tables are shown.</td></tr><tr><td><code class="structname">pg_stat_xact_all_tables</code><a id="id-1.6.14.7.6.8.2.2.14.1.2" class="indexterm"></a></td><td>Similar to <code class="structname">pg_stat_all_tables</code>, but counts actions
213 taken so far within the current transaction (which are <span class="emphasis"><em>not</em></span>
214 yet included in <code class="structname">pg_stat_all_tables</code> and related views).
215 The columns for numbers of live and dead rows and vacuum and
216 analyze actions are not present in this view.</td></tr><tr><td><code class="structname">pg_stat_xact_sys_tables</code><a id="id-1.6.14.7.6.8.2.2.15.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_stat_xact_all_tables</code>, except that only
217 system tables are shown.</td></tr><tr><td><code class="structname">pg_stat_xact_user_tables</code><a id="id-1.6.14.7.6.8.2.2.16.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_stat_xact_all_tables</code>, except that only
218 user tables are shown.</td></tr><tr><td><code class="structname">pg_stat_all_indexes</code><a id="id-1.6.14.7.6.8.2.2.17.1.2" class="indexterm"></a></td><td>
219 One row for each index in the current database, showing statistics
220 about accesses to that specific index.
221 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW" title="27.2.20. pg_stat_all_indexes">
222 <code class="structname">pg_stat_all_indexes</code></a> for details.
223 </td></tr><tr><td><code class="structname">pg_stat_sys_indexes</code><a id="id-1.6.14.7.6.8.2.2.18.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_stat_all_indexes</code>, except that only
224 indexes on system tables are shown.</td></tr><tr><td><code class="structname">pg_stat_user_indexes</code><a id="id-1.6.14.7.6.8.2.2.19.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_stat_all_indexes</code>, except that only
225 indexes on user tables are shown.</td></tr><tr><td><code class="structname">pg_stat_user_functions</code><a id="id-1.6.14.7.6.8.2.2.20.1.2" class="indexterm"></a></td><td>
226 One row for each tracked function, showing statistics
227 about executions of that function. See
228 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-USER-FUNCTIONS-VIEW" title="27.2.24. pg_stat_user_functions">
229 <code class="structname">pg_stat_user_functions</code></a> for details.
230 </td></tr><tr><td><code class="structname">pg_stat_xact_user_functions</code><a id="id-1.6.14.7.6.8.2.2.21.1.2" class="indexterm"></a></td><td>Similar to <code class="structname">pg_stat_user_functions</code>, but counts only
231 calls during the current transaction (which are <span class="emphasis"><em>not</em></span>
232 yet included in <code class="structname">pg_stat_user_functions</code>).</td></tr><tr><td><code class="structname">pg_statio_all_tables</code><a id="id-1.6.14.7.6.8.2.2.22.1.2" class="indexterm"></a></td><td>
233 One row for each table in the current database, showing statistics
234 about I/O on that specific table.
235 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STATIO-ALL-TABLES-VIEW" title="27.2.21. pg_statio_all_tables">
236 <code class="structname">pg_statio_all_tables</code></a> for details.
237 </td></tr><tr><td><code class="structname">pg_statio_sys_tables</code><a id="id-1.6.14.7.6.8.2.2.23.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_statio_all_tables</code>, except that only
238 system tables are shown.</td></tr><tr><td><code class="structname">pg_statio_user_tables</code><a id="id-1.6.14.7.6.8.2.2.24.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_statio_all_tables</code>, except that only
239 user tables are shown.</td></tr><tr><td><code class="structname">pg_statio_all_indexes</code><a id="id-1.6.14.7.6.8.2.2.25.1.2" class="indexterm"></a></td><td>
240 One row for each index in the current database,
241 showing statistics about I/O on that specific index.
242 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STATIO-ALL-INDEXES-VIEW" title="27.2.22. pg_statio_all_indexes">
243 <code class="structname">pg_statio_all_indexes</code></a> for details.
244 </td></tr><tr><td><code class="structname">pg_statio_sys_indexes</code><a id="id-1.6.14.7.6.8.2.2.26.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_statio_all_indexes</code>, except that only
245 indexes on system tables are shown.</td></tr><tr><td><code class="structname">pg_statio_user_indexes</code><a id="id-1.6.14.7.6.8.2.2.27.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_statio_all_indexes</code>, except that only
246 indexes on user tables are shown.</td></tr><tr><td><code class="structname">pg_statio_all_sequences</code><a id="id-1.6.14.7.6.8.2.2.28.1.2" class="indexterm"></a></td><td>
247 One row for each sequence in the current database,
248 showing statistics about I/O on that specific sequence.
249 See <a class="link" href="monitoring-stats.html#MONITORING-PG-STATIO-ALL-SEQUENCES-VIEW" title="27.2.23. pg_statio_all_sequences">
250 <code class="structname">pg_statio_all_sequences</code></a> for details.
251 </td></tr><tr><td><code class="structname">pg_statio_sys_sequences</code><a id="id-1.6.14.7.6.8.2.2.29.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_statio_all_sequences</code>, except that only
252 system sequences are shown. (Presently, no system sequences are defined,
253 so this view is always empty.)</td></tr><tr><td><code class="structname">pg_statio_user_sequences</code><a id="id-1.6.14.7.6.8.2.2.30.1.2" class="indexterm"></a></td><td>Same as <code class="structname">pg_statio_all_sequences</code>, except that only
254 user sequences are shown.</td></tr></tbody></table></div></div><br class="table-break" /><p>
255 The per-index statistics are particularly useful to determine which
256 indexes are being used and how effective they are.
258 The <code class="structname">pg_stat_io</code> and
259 <code class="structname">pg_statio_</code> set of views are useful for determining
260 the effectiveness of the buffer cache. They can be used to calculate a cache
261 hit ratio. Note that while <span class="productname">PostgreSQL</span>'s I/O
262 statistics capture most instances in which the kernel was invoked in order
263 to perform I/O, they do not differentiate between data which had to be
264 fetched from disk and that which already resided in the kernel page cache.
265 Users are advised to use the <span class="productname">PostgreSQL</span>
266 statistics views in combination with operating system utilities for a more
267 complete picture of their database's I/O performance.
268 </p></div><div class="sect2" id="MONITORING-PG-STAT-ACTIVITY-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.3. <code class="structname">pg_stat_activity</code> <a href="#MONITORING-PG-STAT-ACTIVITY-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.7.2" class="indexterm"></a><p>
269 The <code class="structname">pg_stat_activity</code> view will have one row
270 per server process, showing information related to
271 the current activity of that process.
272 </p><div class="table" id="PG-STAT-ACTIVITY-VIEW"><p class="title"><strong>Table 27.3. <code class="structname">pg_stat_activity</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_activity View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
277 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
278 <code class="structfield">datid</code> <code class="type">oid</code>
281 OID of the database this backend is connected to
282 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
283 <code class="structfield">datname</code> <code class="type">name</code>
286 Name of the database this backend is connected to
287 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
288 <code class="structfield">pid</code> <code class="type">integer</code>
291 Process ID of this backend
292 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
293 <code class="structfield">leader_pid</code> <code class="type">integer</code>
296 Process ID of the parallel group leader if this process is a parallel
297 query worker, or process ID of the leader apply worker if this process
298 is a parallel apply worker. <code class="literal">NULL</code> indicates that this
299 process is a parallel group leader or leader apply worker, or does not
300 participate in any parallel operation.
301 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
302 <code class="structfield">usesysid</code> <code class="type">oid</code>
305 OID of the user logged into this backend
306 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
307 <code class="structfield">usename</code> <code class="type">name</code>
310 Name of the user logged into this backend
311 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
312 <code class="structfield">application_name</code> <code class="type">text</code>
315 Name of the application that is connected
317 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
318 <code class="structfield">client_addr</code> <code class="type">inet</code>
321 IP address of the client connected to this backend.
322 If this field is null, it indicates either that the client is
323 connected via a Unix socket on the server machine or that this is an
324 internal process such as autovacuum.
325 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
326 <code class="structfield">client_hostname</code> <code class="type">text</code>
329 Host name of the connected client, as reported by a
330 reverse DNS lookup of <code class="structfield">client_addr</code>. This field will
331 only be non-null for IP connections, and only when <a class="xref" href="runtime-config-logging.html#GUC-LOG-HOSTNAME">log_hostname</a> is enabled.
332 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
333 <code class="structfield">client_port</code> <code class="type">integer</code>
336 TCP port number that the client is using for communication
337 with this backend, or <code class="literal">-1</code> if a Unix socket is used.
338 If this field is null, it indicates that this is an internal server process.
339 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
340 <code class="structfield">backend_start</code> <code class="type">timestamp with time zone</code>
343 Time when this process was started. For client backends,
344 this is the time the client connected to the server.
345 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
346 <code class="structfield">xact_start</code> <code class="type">timestamp with time zone</code>
349 Time when this process' current transaction was started, or null
350 if no transaction is active. If the current
351 query is the first of its transaction, this column is equal to the
352 <code class="structfield">query_start</code> column.
353 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
354 <code class="structfield">query_start</code> <code class="type">timestamp with time zone</code>
357 Time when the currently active query was started, or if
358 <code class="structfield">state</code> is not <code class="literal">active</code>, when the last query
360 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
361 <code class="structfield">state_change</code> <code class="type">timestamp with time zone</code>
364 Time when the <code class="structfield">state</code> was last changed
365 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
366 <code class="structfield">wait_event_type</code> <code class="type">text</code>
369 The type of event for which the backend is waiting, if any;
370 otherwise NULL. See <a class="xref" href="monitoring-stats.html#WAIT-EVENT-TABLE" title="Table 27.4. Wait Event Types">Table 27.4</a>.
371 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
372 <code class="structfield">wait_event</code> <code class="type">text</code>
375 Wait event name if backend is currently waiting, otherwise NULL.
376 See <a class="xref" href="monitoring-stats.html#WAIT-EVENT-ACTIVITY-TABLE" title="Table 27.5. Wait Events of Type Activity">Table 27.5</a> through
377 <a class="xref" href="monitoring-stats.html#WAIT-EVENT-TIMEOUT-TABLE" title="Table 27.13. Wait Events of Type Timeout">Table 27.13</a>.
378 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
379 <code class="structfield">state</code> <code class="type">text</code>
382 Current overall state of this backend.
384 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
385 <code class="literal">starting</code>: The backend is in initial startup. Client
386 authentication is performed during this phase.
387 </p></li><li class="listitem"><p>
388 <code class="literal">active</code>: The backend is executing a query.
389 </p></li><li class="listitem"><p>
390 <code class="literal">idle</code>: The backend is waiting for a new client command.
391 </p></li><li class="listitem"><p>
392 <code class="literal">idle in transaction</code>: The backend is in a transaction,
393 but is not currently executing a query.
394 </p></li><li class="listitem"><p>
395 <code class="literal">idle in transaction (aborted)</code>: This state is similar to
396 <code class="literal">idle in transaction</code>, except one of the statements in
397 the transaction caused an error.
398 </p></li><li class="listitem"><p>
399 <code class="literal">fastpath function call</code>: The backend is executing a
401 </p></li><li class="listitem"><p>
402 <code class="literal">disabled</code>: This state is reported if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-ACTIVITIES">track_activities</a> is disabled in this backend.
403 </p></li></ul></div><p>
404 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
405 <code class="structfield">backend_xid</code> <code class="type">xid</code>
408 Top-level transaction identifier of this backend, if any; see
409 <a class="xref" href="transaction-id.html" title="67.1. Transactions and Identifiers">Section 67.1</a>.
410 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
411 <code class="structfield">backend_xmin</code> <code class="type">xid</code>
414 The current backend's <code class="literal">xmin</code> horizon.
415 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
416 <code class="structfield">query_id</code> <code class="type">bigint</code>
419 Identifier of this backend's most recent query. If
420 <code class="structfield">state</code> is <code class="literal">active</code> this
421 field shows the identifier of the currently executing query. In
422 all other states, it shows the identifier of last query that was
423 executed. Query identifiers are not computed by default so this
424 field will be null unless <a class="xref" href="runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID">compute_query_id</a>
425 parameter is enabled or a third-party module that computes query
426 identifiers is configured.
427 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
428 <code class="structfield">query</code> <code class="type">text</code>
431 Text of this backend's most recent query. If
432 <code class="structfield">state</code> is <code class="literal">active</code> this field shows the
433 currently executing query. In all other states, it shows the last query
434 that was executed. By default the query text is truncated at 1024
435 bytes; this value can be changed via the parameter
436 <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE">track_activity_query_size</a>.
437 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
438 <code class="structfield">backend_type</code> <code class="type">text</code>
441 Type of current backend. Possible types are
442 <code class="literal">autovacuum launcher</code>, <code class="literal">autovacuum worker</code>,
443 <code class="literal">logical replication launcher</code>,
444 <code class="literal">logical replication worker</code>,
445 <code class="literal">parallel worker</code>, <code class="literal">background writer</code>,
446 <code class="literal">client backend</code>, <code class="literal">checkpointer</code>,
447 <code class="literal">archiver</code>, <code class="literal">standalone backend</code>,
448 <code class="literal">startup</code>, <code class="literal">walreceiver</code>,
449 <code class="literal">walsender</code>, <code class="literal">walwriter</code> and
450 <code class="literal">walsummarizer</code>.
451 In addition, background workers registered by extensions may have
453 </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
454 The <code class="structfield">wait_event</code> and <code class="structfield">state</code> columns are
455 independent. If a backend is in the <code class="literal">active</code> state,
456 it may or may not be <code class="literal">waiting</code> on some event. If the state
457 is <code class="literal">active</code> and <code class="structfield">wait_event</code> is non-null, it
458 means that a query is being executed, but is being blocked somewhere
459 in the system. To keep the reporting overhead low, the system does not
460 attempt to synchronize different aspects of activity data for a backend.
461 As a result, ephemeral discrepancies may exist between the view's columns.
462 </p></div><div class="table" id="WAIT-EVENT-TABLE"><p class="title"><strong>Table 27.4. Wait Event Types</strong></p><div class="table-contents"><table class="table" summary="Wait Event Types" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Wait Event Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">Activity</code></td><td>The server process is idle. This event type indicates a process
463 waiting for activity in its main processing loop.
464 <code class="literal">wait_event</code> will identify the specific wait point;
465 see <a class="xref" href="monitoring-stats.html#WAIT-EVENT-ACTIVITY-TABLE" title="Table 27.5. Wait Events of Type Activity">Table 27.5</a>.
466 </td></tr><tr><td><code class="literal">BufferPin</code></td><td>The server process is waiting for exclusive access to
467 a data buffer. Buffer pin waits can be protracted if
468 another process holds an open cursor that last read data from the
469 buffer in question. See <a class="xref" href="monitoring-stats.html#WAIT-EVENT-BUFFERPIN-TABLE" title="Table 27.6. Wait Events of Type Bufferpin">Table 27.6</a>.
470 </td></tr><tr><td><code class="literal">Client</code></td><td>The server process is waiting for activity on a socket
471 connected to a user application. Thus, the server expects something
472 to happen that is independent of its internal processes.
473 <code class="literal">wait_event</code> will identify the specific wait point;
474 see <a class="xref" href="monitoring-stats.html#WAIT-EVENT-CLIENT-TABLE" title="Table 27.7. Wait Events of Type Client">Table 27.7</a>.
475 </td></tr><tr><td><code class="literal">Extension</code></td><td>The server process is waiting for some condition defined by an
477 See <a class="xref" href="monitoring-stats.html#WAIT-EVENT-EXTENSION-TABLE" title="Table 27.8. Wait Events of Type Extension">Table 27.8</a>.
478 </td></tr><tr><td><code class="literal">InjectionPoint</code></td><td>The server process is waiting for an injection point to reach an
479 outcome defined in a test. See
480 <a class="xref" href="xfunc-c.html#XFUNC-ADDIN-INJECTION-POINTS" title="36.10.14. Injection Points">Section 36.10.14</a> for more details. This
481 type has no predefined wait points.
482 </td></tr><tr><td><code class="literal">IO</code></td><td>The server process is waiting for an I/O operation to complete.
483 <code class="literal">wait_event</code> will identify the specific wait point;
484 see <a class="xref" href="monitoring-stats.html#WAIT-EVENT-IO-TABLE" title="Table 27.9. Wait Events of Type Io">Table 27.9</a>.
485 </td></tr><tr><td><code class="literal">IPC</code></td><td>The server process is waiting for some interaction with
486 another server process. <code class="literal">wait_event</code> will
487 identify the specific wait point;
488 see <a class="xref" href="monitoring-stats.html#WAIT-EVENT-IPC-TABLE" title="Table 27.10. Wait Events of Type Ipc">Table 27.10</a>.
489 </td></tr><tr><td><code class="literal">Lock</code></td><td>The server process is waiting for a heavyweight lock.
490 Heavyweight locks, also known as lock manager locks or simply locks,
491 primarily protect SQL-visible objects such as tables. However,
492 they are also used to ensure mutual exclusion for certain internal
493 operations such as relation extension. <code class="literal">wait_event</code>
494 will identify the type of lock awaited;
495 see <a class="xref" href="monitoring-stats.html#WAIT-EVENT-LOCK-TABLE" title="Table 27.11. Wait Events of Type Lock">Table 27.11</a>.
496 </td></tr><tr><td><code class="literal">LWLock</code></td><td> The server process is waiting for a lightweight lock.
497 Most such locks protect a particular data structure in shared memory.
498 <code class="literal">wait_event</code> will contain a name identifying the purpose
499 of the lightweight lock. (Some locks have specific names; others
500 are part of a group of locks each with a similar purpose.)
501 See <a class="xref" href="monitoring-stats.html#WAIT-EVENT-LWLOCK-TABLE" title="Table 27.12. Wait Events of Type Lwlock">Table 27.12</a>.
502 </td></tr><tr><td><code class="literal">Timeout</code></td><td>The server process is waiting for a timeout
503 to expire. <code class="literal">wait_event</code> will identify the specific wait
504 point; see <a class="xref" href="monitoring-stats.html#WAIT-EVENT-TIMEOUT-TABLE" title="Table 27.13. Wait Events of Type Timeout">Table 27.13</a>.
505 </td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="WAIT-EVENT-ACTIVITY-TABLE"><p class="title"><strong>Table 27.5. Wait Events of Type <code class="literal">Activity</code></strong></p><div class="table-contents"><table class="table" summary="Wait Events of Type Activity" border="1"><colgroup><col /><col /></colgroup><thead><tr><th><code class="literal">Activity</code> Wait Event</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">ArchiverMain</code></td><td>Waiting in main loop of archiver process.</td></tr><tr><td><code class="literal">AutovacuumMain</code></td><td>Waiting in main loop of autovacuum launcher process.</td></tr><tr><td><code class="literal">BgwriterHibernate</code></td><td>Waiting in background writer process, hibernating.</td></tr><tr><td><code class="literal">BgwriterMain</code></td><td>Waiting in main loop of background writer process.</td></tr><tr><td><code class="literal">CheckpointerMain</code></td><td>Waiting in main loop of checkpointer process.</td></tr><tr><td><code class="literal">CheckpointerShutdown</code></td><td>Waiting for checkpointer process to be terminated.</td></tr><tr><td><code class="literal">IoWorkerMain</code></td><td>Waiting in main loop of IO Worker process.</td></tr><tr><td><code class="literal">LogicalApplyMain</code></td><td>Waiting in main loop of logical replication apply process.</td></tr><tr><td><code class="literal">LogicalLauncherMain</code></td><td>Waiting in main loop of logical replication launcher process.</td></tr><tr><td><code class="literal">LogicalParallelApplyMain</code></td><td>Waiting in main loop of logical replication parallel apply process.</td></tr><tr><td><code class="literal">RecoveryWalStream</code></td><td>Waiting in main loop of startup process for WAL to arrive, during streaming recovery.</td></tr><tr><td><code class="literal">ReplicationSlotsyncMain</code></td><td>Waiting in main loop of slot sync worker.</td></tr><tr><td><code class="literal">ReplicationSlotsyncShutdown</code></td><td>Waiting for slot sync worker to shut down.</td></tr><tr><td><code class="literal">SysloggerMain</code></td><td>Waiting in main loop of syslogger process.</td></tr><tr><td><code class="literal">WalReceiverMain</code></td><td>Waiting in main loop of WAL receiver process.</td></tr><tr><td><code class="literal">WalSenderMain</code></td><td>Waiting in main loop of WAL sender process.</td></tr><tr><td><code class="literal">WalSummarizerWal</code></td><td>Waiting in WAL summarizer for more WAL to be generated.</td></tr><tr><td><code class="literal">WalWriterMain</code></td><td>Waiting in main loop of WAL writer process.</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="WAIT-EVENT-BUFFERPIN-TABLE"><p class="title"><strong>Table 27.6. Wait Events of Type <code class="literal">Bufferpin</code></strong></p><div class="table-contents"><table class="table" summary="Wait Events of Type Bufferpin" border="1"><colgroup><col /><col /></colgroup><thead><tr><th><code class="literal">BufferPin</code> Wait Event</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">BufferPin</code></td><td>Waiting to acquire an exclusive pin on a buffer.</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="WAIT-EVENT-CLIENT-TABLE"><p class="title"><strong>Table 27.7. Wait Events of Type <code class="literal">Client</code></strong></p><div class="table-contents"><table class="table" summary="Wait Events of Type Client" border="1"><colgroup><col /><col /></colgroup><thead><tr><th><code class="literal">Client</code> Wait Event</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">ClientRead</code></td><td>Waiting to read data from the client.</td></tr><tr><td><code class="literal">ClientWrite</code></td><td>Waiting to write data to the client.</td></tr><tr><td><code class="literal">GssOpenServer</code></td><td>Waiting to read data from the client while establishing a GSSAPI session.</td></tr><tr><td><code class="literal">LibpqwalreceiverConnect</code></td><td>Waiting in WAL receiver to establish connection to remote server.</td></tr><tr><td><code class="literal">LibpqwalreceiverReceive</code></td><td>Waiting in WAL receiver to receive data from remote server.</td></tr><tr><td><code class="literal">SslOpenServer</code></td><td>Waiting for SSL while attempting connection.</td></tr><tr><td><code class="literal">WaitForStandbyConfirmation</code></td><td>Waiting for WAL to be received and flushed by the physical standby.</td></tr><tr><td><code class="literal">WalSenderWaitForWal</code></td><td>Waiting for WAL to be flushed in WAL sender process.</td></tr><tr><td><code class="literal">WalSenderWriteData</code></td><td>Waiting for any activity when processing replies from WAL receiver in WAL sender process.</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="WAIT-EVENT-EXTENSION-TABLE"><p class="title"><strong>Table 27.8. Wait Events of Type <code class="literal">Extension</code></strong></p><div class="table-contents"><table class="table" summary="Wait Events of Type Extension" border="1"><colgroup><col /><col /></colgroup><thead><tr><th><code class="literal">Extension</code> Wait Event</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">Extension</code></td><td>Waiting in an extension.</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="WAIT-EVENT-IO-TABLE"><p class="title"><strong>Table 27.9. Wait Events of Type <code class="literal">Io</code></strong></p><div class="table-contents"><table class="table" summary="Wait Events of Type Io" border="1"><colgroup><col /><col /></colgroup><thead><tr><th><code class="literal">IO</code> Wait Event</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">AioIoCompletion</code></td><td>Waiting for another process to complete IO.</td></tr><tr><td><code class="literal">AioIoUringExecution</code></td><td>Waiting for IO execution via io_uring.</td></tr><tr><td><code class="literal">AioIoUringSubmit</code></td><td>Waiting for IO submission via io_uring.</td></tr><tr><td><code class="literal">BasebackupRead</code></td><td>Waiting for base backup to read from a file.</td></tr><tr><td><code class="literal">BasebackupSync</code></td><td>Waiting for data written by a base backup to reach durable storage.</td></tr><tr><td><code class="literal">BasebackupWrite</code></td><td>Waiting for base backup to write to a file.</td></tr><tr><td><code class="literal">BuffileRead</code></td><td>Waiting for a read from a buffered file.</td></tr><tr><td><code class="literal">BuffileTruncate</code></td><td>Waiting for a buffered file to be truncated.</td></tr><tr><td><code class="literal">BuffileWrite</code></td><td>Waiting for a write to a buffered file.</td></tr><tr><td><code class="literal">ControlFileRead</code></td><td>Waiting for a read from the <code class="filename">pg_control</code> file.</td></tr><tr><td><code class="literal">ControlFileSync</code></td><td>Waiting for the <code class="filename">pg_control</code> file to reach durable storage.</td></tr><tr><td><code class="literal">ControlFileSyncUpdate</code></td><td>Waiting for an update to the <code class="filename">pg_control</code> file to reach durable storage.</td></tr><tr><td><code class="literal">ControlFileWrite</code></td><td>Waiting for a write to the <code class="filename">pg_control</code> file.</td></tr><tr><td><code class="literal">ControlFileWriteUpdate</code></td><td>Waiting for a write to update the <code class="filename">pg_control</code> file.</td></tr><tr><td><code class="literal">CopyFileCopy</code></td><td>Waiting for a file copy operation.</td></tr><tr><td><code class="literal">CopyFileRead</code></td><td>Waiting for a read during a file copy operation.</td></tr><tr><td><code class="literal">CopyFileWrite</code></td><td>Waiting for a write during a file copy operation.</td></tr><tr><td><code class="literal">DataFileExtend</code></td><td>Waiting for a relation data file to be extended.</td></tr><tr><td><code class="literal">DataFileFlush</code></td><td>Waiting for a relation data file to reach durable storage.</td></tr><tr><td><code class="literal">DataFileImmediateSync</code></td><td>Waiting for an immediate synchronization of a relation data file to durable storage.</td></tr><tr><td><code class="literal">DataFilePrefetch</code></td><td>Waiting for an asynchronous prefetch from a relation data file.</td></tr><tr><td><code class="literal">DataFileRead</code></td><td>Waiting for a read from a relation data file.</td></tr><tr><td><code class="literal">DataFileSync</code></td><td>Waiting for changes to a relation data file to reach durable storage.</td></tr><tr><td><code class="literal">DataFileTruncate</code></td><td>Waiting for a relation data file to be truncated.</td></tr><tr><td><code class="literal">DataFileWrite</code></td><td>Waiting for a write to a relation data file.</td></tr><tr><td><code class="literal">DsmAllocate</code></td><td>Waiting for a dynamic shared memory segment to be allocated.</td></tr><tr><td><code class="literal">DsmFillZeroWrite</code></td><td>Waiting to fill a dynamic shared memory backing file with zeroes.</td></tr><tr><td><code class="literal">LockFileAddtodatadirRead</code></td><td>Waiting for a read while adding a line to the data directory lock file.</td></tr><tr><td><code class="literal">LockFileAddtodatadirSync</code></td><td>Waiting for data to reach durable storage while adding a line to the data directory lock file.</td></tr><tr><td><code class="literal">LockFileAddtodatadirWrite</code></td><td>Waiting for a write while adding a line to the data directory lock file.</td></tr><tr><td><code class="literal">LockFileCreateRead</code></td><td>Waiting to read while creating the data directory lock file.</td></tr><tr><td><code class="literal">LockFileCreateSync</code></td><td>Waiting for data to reach durable storage while creating the data directory lock file.</td></tr><tr><td><code class="literal">LockFileCreateWrite</code></td><td>Waiting for a write while creating the data directory lock file.</td></tr><tr><td><code class="literal">LockFileRecheckdatadirRead</code></td><td>Waiting for a read during recheck of the data directory lock file.</td></tr><tr><td><code class="literal">LogicalRewriteCheckpointSync</code></td><td>Waiting for logical rewrite mappings to reach durable storage during a checkpoint.</td></tr><tr><td><code class="literal">LogicalRewriteMappingSync</code></td><td>Waiting for mapping data to reach durable storage during a logical rewrite.</td></tr><tr><td><code class="literal">LogicalRewriteMappingWrite</code></td><td>Waiting for a write of mapping data during a logical rewrite.</td></tr><tr><td><code class="literal">LogicalRewriteSync</code></td><td>Waiting for logical rewrite mappings to reach durable storage.</td></tr><tr><td><code class="literal">LogicalRewriteTruncate</code></td><td>Waiting for truncate of mapping data during a logical rewrite.</td></tr><tr><td><code class="literal">LogicalRewriteWrite</code></td><td>Waiting for a write of logical rewrite mappings.</td></tr><tr><td><code class="literal">RelationMapRead</code></td><td>Waiting for a read of the relation map file.</td></tr><tr><td><code class="literal">RelationMapReplace</code></td><td>Waiting for durable replacement of a relation map file.</td></tr><tr><td><code class="literal">RelationMapWrite</code></td><td>Waiting for a write to the relation map file.</td></tr><tr><td><code class="literal">ReorderBufferRead</code></td><td>Waiting for a read during reorder buffer management.</td></tr><tr><td><code class="literal">ReorderBufferWrite</code></td><td>Waiting for a write during reorder buffer management.</td></tr><tr><td><code class="literal">ReorderLogicalMappingRead</code></td><td>Waiting for a read of a logical mapping during reorder buffer management.</td></tr><tr><td><code class="literal">ReplicationSlotRead</code></td><td>Waiting for a read from a replication slot control file.</td></tr><tr><td><code class="literal">ReplicationSlotRestoreSync</code></td><td>Waiting for a replication slot control file to reach durable storage while restoring it to memory.</td></tr><tr><td><code class="literal">ReplicationSlotSync</code></td><td>Waiting for a replication slot control file to reach durable storage.</td></tr><tr><td><code class="literal">ReplicationSlotWrite</code></td><td>Waiting for a write to a replication slot control file.</td></tr><tr><td><code class="literal">SlruFlushSync</code></td><td>Waiting for SLRU data to reach durable storage during a checkpoint or database shutdown.</td></tr><tr><td><code class="literal">SlruRead</code></td><td>Waiting for a read of an SLRU page.</td></tr><tr><td><code class="literal">SlruSync</code></td><td>Waiting for SLRU data to reach durable storage following a page write.</td></tr><tr><td><code class="literal">SlruWrite</code></td><td>Waiting for a write of an SLRU page.</td></tr><tr><td><code class="literal">SnapbuildRead</code></td><td>Waiting for a read of a serialized historical catalog snapshot.</td></tr><tr><td><code class="literal">SnapbuildSync</code></td><td>Waiting for a serialized historical catalog snapshot to reach durable storage.</td></tr><tr><td><code class="literal">SnapbuildWrite</code></td><td>Waiting for a write of a serialized historical catalog snapshot.</td></tr><tr><td><code class="literal">TimelineHistoryFileSync</code></td><td>Waiting for a timeline history file received via streaming replication to reach durable storage.</td></tr><tr><td><code class="literal">TimelineHistoryFileWrite</code></td><td>Waiting for a write of a timeline history file received via streaming replication.</td></tr><tr><td><code class="literal">TimelineHistoryRead</code></td><td>Waiting for a read of a timeline history file.</td></tr><tr><td><code class="literal">TimelineHistorySync</code></td><td>Waiting for a newly created timeline history file to reach durable storage.</td></tr><tr><td><code class="literal">TimelineHistoryWrite</code></td><td>Waiting for a write of a newly created timeline history file.</td></tr><tr><td><code class="literal">TwophaseFileRead</code></td><td>Waiting for a read of a two phase state file.</td></tr><tr><td><code class="literal">TwophaseFileSync</code></td><td>Waiting for a two phase state file to reach durable storage.</td></tr><tr><td><code class="literal">TwophaseFileWrite</code></td><td>Waiting for a write of a two phase state file.</td></tr><tr><td><code class="literal">VersionFileSync</code></td><td>Waiting for the version file to reach durable storage while creating a database.</td></tr><tr><td><code class="literal">VersionFileWrite</code></td><td>Waiting for the version file to be written while creating a database.</td></tr><tr><td><code class="literal">WalsenderTimelineHistoryRead</code></td><td>Waiting for a read from a timeline history file during a walsender timeline command.</td></tr><tr><td><code class="literal">WalBootstrapSync</code></td><td>Waiting for WAL to reach durable storage during bootstrapping.</td></tr><tr><td><code class="literal">WalBootstrapWrite</code></td><td>Waiting for a write of a WAL page during bootstrapping.</td></tr><tr><td><code class="literal">WalCopyRead</code></td><td>Waiting for a read when creating a new WAL segment by copying an existing one.</td></tr><tr><td><code class="literal">WalCopySync</code></td><td>Waiting for a new WAL segment created by copying an existing one to reach durable storage.</td></tr><tr><td><code class="literal">WalCopyWrite</code></td><td>Waiting for a write when creating a new WAL segment by copying an existing one.</td></tr><tr><td><code class="literal">WalInitSync</code></td><td>Waiting for a newly initialized WAL file to reach durable storage.</td></tr><tr><td><code class="literal">WalInitWrite</code></td><td>Waiting for a write while initializing a new WAL file.</td></tr><tr><td><code class="literal">WalRead</code></td><td>Waiting for a read from a WAL file.</td></tr><tr><td><code class="literal">WalSummaryRead</code></td><td>Waiting for a read from a WAL summary file.</td></tr><tr><td><code class="literal">WalSummaryWrite</code></td><td>Waiting for a write to a WAL summary file.</td></tr><tr><td><code class="literal">WalSync</code></td><td>Waiting for a WAL file to reach durable storage.</td></tr><tr><td><code class="literal">WalSyncMethodAssign</code></td><td>Waiting for data to reach durable storage while assigning a new WAL sync method.</td></tr><tr><td><code class="literal">WalWrite</code></td><td>Waiting for a write to a WAL file.</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="WAIT-EVENT-IPC-TABLE"><p class="title"><strong>Table 27.10. Wait Events of Type <code class="literal">Ipc</code></strong></p><div class="table-contents"><table class="table" summary="Wait Events of Type Ipc" border="1"><colgroup><col /><col /></colgroup><thead><tr><th><code class="literal">IPC</code> Wait Event</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">AppendReady</code></td><td>Waiting for subplan nodes of an <code class="literal">Append</code> plan node to be ready.</td></tr><tr><td><code class="literal">ArchiveCleanupCommand</code></td><td>Waiting for <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-CLEANUP-COMMAND">archive_cleanup_command</a> to complete.</td></tr><tr><td><code class="literal">ArchiveCommand</code></td><td>Waiting for <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-COMMAND">archive_command</a> to complete.</td></tr><tr><td><code class="literal">BackendTermination</code></td><td>Waiting for the termination of another backend.</td></tr><tr><td><code class="literal">BackupWaitWalArchive</code></td><td>Waiting for WAL files required for a backup to be successfully archived.</td></tr><tr><td><code class="literal">BgworkerShutdown</code></td><td>Waiting for background worker to shut down.</td></tr><tr><td><code class="literal">BgworkerStartup</code></td><td>Waiting for background worker to start up.</td></tr><tr><td><code class="literal">BtreePage</code></td><td>Waiting for the page number needed to continue a parallel B-tree scan to become available.</td></tr><tr><td><code class="literal">BufferIo</code></td><td>Waiting for buffer I/O to complete.</td></tr><tr><td><code class="literal">CheckpointDelayComplete</code></td><td>Waiting for a backend that blocks a checkpoint from completing.</td></tr><tr><td><code class="literal">CheckpointDelayStart</code></td><td>Waiting for a backend that blocks a checkpoint from starting.</td></tr><tr><td><code class="literal">CheckpointDone</code></td><td>Waiting for a checkpoint to complete.</td></tr><tr><td><code class="literal">CheckpointStart</code></td><td>Waiting for a checkpoint to start.</td></tr><tr><td><code class="literal">ExecuteGather</code></td><td>Waiting for activity from a child process while executing a <code class="literal">Gather</code> plan node.</td></tr><tr><td><code class="literal">HashBatchAllocate</code></td><td>Waiting for an elected Parallel Hash participant to allocate a hash table.</td></tr><tr><td><code class="literal">HashBatchElect</code></td><td>Waiting to elect a Parallel Hash participant to allocate a hash table.</td></tr><tr><td><code class="literal">HashBatchLoad</code></td><td>Waiting for other Parallel Hash participants to finish loading a hash table.</td></tr><tr><td><code class="literal">HashBuildAllocate</code></td><td>Waiting for an elected Parallel Hash participant to allocate the initial hash table.</td></tr><tr><td><code class="literal">HashBuildElect</code></td><td>Waiting to elect a Parallel Hash participant to allocate the initial hash table.</td></tr><tr><td><code class="literal">HashBuildHashInner</code></td><td>Waiting for other Parallel Hash participants to finish hashing the inner relation.</td></tr><tr><td><code class="literal">HashBuildHashOuter</code></td><td>Waiting for other Parallel Hash participants to finish partitioning the outer relation.</td></tr><tr><td><code class="literal">HashGrowBatchesDecide</code></td><td>Waiting to elect a Parallel Hash participant to decide on future batch growth.</td></tr><tr><td><code class="literal">HashGrowBatchesElect</code></td><td>Waiting to elect a Parallel Hash participant to allocate more batches.</td></tr><tr><td><code class="literal">HashGrowBatchesFinish</code></td><td>Waiting for an elected Parallel Hash participant to decide on future batch growth.</td></tr><tr><td><code class="literal">HashGrowBatchesReallocate</code></td><td>Waiting for an elected Parallel Hash participant to allocate more batches.</td></tr><tr><td><code class="literal">HashGrowBatchesRepartition</code></td><td>Waiting for other Parallel Hash participants to finish repartitioning.</td></tr><tr><td><code class="literal">HashGrowBucketsElect</code></td><td>Waiting to elect a Parallel Hash participant to allocate more buckets.</td></tr><tr><td><code class="literal">HashGrowBucketsReallocate</code></td><td>Waiting for an elected Parallel Hash participant to finish allocating more buckets.</td></tr><tr><td><code class="literal">HashGrowBucketsReinsert</code></td><td>Waiting for other Parallel Hash participants to finish inserting tuples into new buckets.</td></tr><tr><td><code class="literal">LogicalApplySendData</code></td><td>Waiting for a logical replication leader apply process to send data to a parallel apply process.</td></tr><tr><td><code class="literal">LogicalParallelApplyStateChange</code></td><td>Waiting for a logical replication parallel apply process to change state.</td></tr><tr><td><code class="literal">LogicalSyncData</code></td><td>Waiting for a logical replication remote server to send data for initial table synchronization.</td></tr><tr><td><code class="literal">LogicalSyncStateChange</code></td><td>Waiting for a logical replication remote server to change state.</td></tr><tr><td><code class="literal">MessageQueueInternal</code></td><td>Waiting for another process to be attached to a shared message queue.</td></tr><tr><td><code class="literal">MessageQueuePutMessage</code></td><td>Waiting to write a protocol message to a shared message queue.</td></tr><tr><td><code class="literal">MessageQueueReceive</code></td><td>Waiting to receive bytes from a shared message queue.</td></tr><tr><td><code class="literal">MessageQueueSend</code></td><td>Waiting to send bytes to a shared message queue.</td></tr><tr><td><code class="literal">MultixactCreation</code></td><td>Waiting for a multixact creation to complete.</td></tr><tr><td><code class="literal">ParallelBitmapScan</code></td><td>Waiting for parallel bitmap scan to become initialized.</td></tr><tr><td><code class="literal">ParallelCreateIndexScan</code></td><td>Waiting for parallel <code class="command">CREATE INDEX</code> workers to finish heap scan.</td></tr><tr><td><code class="literal">ParallelFinish</code></td><td>Waiting for parallel workers to finish computing.</td></tr><tr><td><code class="literal">ProcarrayGroupUpdate</code></td><td>Waiting for the group leader to clear the transaction ID at transaction end.</td></tr><tr><td><code class="literal">ProcSignalBarrier</code></td><td>Waiting for a barrier event to be processed by all backends.</td></tr><tr><td><code class="literal">Promote</code></td><td>Waiting for standby promotion.</td></tr><tr><td><code class="literal">RecoveryConflictSnapshot</code></td><td>Waiting for recovery conflict resolution for a vacuum cleanup.</td></tr><tr><td><code class="literal">RecoveryConflictTablespace</code></td><td>Waiting for recovery conflict resolution for dropping a tablespace.</td></tr><tr><td><code class="literal">RecoveryEndCommand</code></td><td>Waiting for <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-END-COMMAND">recovery_end_command</a> to complete.</td></tr><tr><td><code class="literal">RecoveryPause</code></td><td>Waiting for recovery to be resumed.</td></tr><tr><td><code class="literal">ReplicationOriginDrop</code></td><td>Waiting for a replication origin to become inactive so it can be dropped.</td></tr><tr><td><code class="literal">ReplicationSlotDrop</code></td><td>Waiting for a replication slot to become inactive so it can be dropped.</td></tr><tr><td><code class="literal">RestoreCommand</code></td><td>Waiting for <a class="xref" href="runtime-config-wal.html#GUC-RESTORE-COMMAND">restore_command</a> to complete.</td></tr><tr><td><code class="literal">SafeSnapshot</code></td><td>Waiting to obtain a valid snapshot for a <code class="literal">READ ONLY DEFERRABLE</code> transaction.</td></tr><tr><td><code class="literal">SyncRep</code></td><td>Waiting for confirmation from a remote server during synchronous replication.</td></tr><tr><td><code class="literal">WalReceiverExit</code></td><td>Waiting for the WAL receiver to exit.</td></tr><tr><td><code class="literal">WalReceiverWaitStart</code></td><td>Waiting for startup process to send initial data for streaming replication.</td></tr><tr><td><code class="literal">WalSummaryReady</code></td><td>Waiting for a new WAL summary to be generated.</td></tr><tr><td><code class="literal">XactGroupUpdate</code></td><td>Waiting for the group leader to update transaction status at transaction end.</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="WAIT-EVENT-LOCK-TABLE"><p class="title"><strong>Table 27.11. Wait Events of Type <code class="literal">Lock</code></strong></p><div class="table-contents"><table class="table" summary="Wait Events of Type Lock" border="1"><colgroup><col /><col /></colgroup><thead><tr><th><code class="literal">Lock</code> Wait Event</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">advisory</code></td><td>Waiting to acquire an advisory user lock.</td></tr><tr><td><code class="literal">applytransaction</code></td><td>Waiting to acquire a lock on a remote transaction being applied by a logical replication subscriber.</td></tr><tr><td><code class="literal">extend</code></td><td>Waiting to extend a relation.</td></tr><tr><td><code class="literal">frozenid</code></td><td>Waiting to update <code class="structname">pg_database</code>.<code class="structfield">datfrozenxid</code> and <code class="structname">pg_database</code>.<code class="structfield">datminmxid</code>.</td></tr><tr><td><code class="literal">object</code></td><td>Waiting to acquire a lock on a non-relation database object.</td></tr><tr><td><code class="literal">page</code></td><td>Waiting to acquire a lock on a page of a relation.</td></tr><tr><td><code class="literal">relation</code></td><td>Waiting to acquire a lock on a relation.</td></tr><tr><td><code class="literal">spectoken</code></td><td>Waiting to acquire a speculative insertion lock.</td></tr><tr><td><code class="literal">transactionid</code></td><td>Waiting for a transaction to finish.</td></tr><tr><td><code class="literal">tuple</code></td><td>Waiting to acquire a lock on a tuple.</td></tr><tr><td><code class="literal">userlock</code></td><td>Waiting to acquire a user lock.</td></tr><tr><td><code class="literal">virtualxid</code></td><td>Waiting to acquire a virtual transaction ID lock; see <a class="xref" href="transaction-id.html" title="67.1. Transactions and Identifiers">Section 67.1</a>.</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="WAIT-EVENT-LWLOCK-TABLE"><p class="title"><strong>Table 27.12. Wait Events of Type <code class="literal">Lwlock</code></strong></p><div class="table-contents"><table class="table" summary="Wait Events of Type Lwlock" border="1"><colgroup><col /><col /></colgroup><thead><tr><th><code class="literal">LWLock</code> Wait Event</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">AddinShmemInit</code></td><td>Waiting to manage an extension's space allocation in shared memory.</td></tr><tr><td><code class="literal">AioUringCompletion</code></td><td>Waiting for another process to complete IO via io_uring.</td></tr><tr><td><code class="literal">AioWorkerSubmissionQueue</code></td><td>Waiting to access AIO worker submission queue.</td></tr><tr><td><code class="literal">AutoFile</code></td><td>Waiting to update the <code class="filename">postgresql.auto.conf</code> file.</td></tr><tr><td><code class="literal">Autovacuum</code></td><td>Waiting to read or update the current state of autovacuum workers.</td></tr><tr><td><code class="literal">AutovacuumSchedule</code></td><td>Waiting to ensure that a table selected for autovacuum still needs vacuuming.</td></tr><tr><td><code class="literal">BackgroundWorker</code></td><td>Waiting to read or update background worker state.</td></tr><tr><td><code class="literal">BtreeVacuum</code></td><td>Waiting to read or update vacuum-related information for a B-tree index.</td></tr><tr><td><code class="literal">BufferContent</code></td><td>Waiting to access a data page in memory.</td></tr><tr><td><code class="literal">BufferMapping</code></td><td>Waiting to associate a data block with a buffer in the buffer pool.</td></tr><tr><td><code class="literal">CheckpointerComm</code></td><td>Waiting to manage fsync requests.</td></tr><tr><td><code class="literal">CommitTs</code></td><td>Waiting to read or update the last value set for a transaction commit timestamp.</td></tr><tr><td><code class="literal">CommitTsBuffer</code></td><td>Waiting for I/O on a commit timestamp SLRU buffer.</td></tr><tr><td><code class="literal">CommitTsSLRU</code></td><td>Waiting to access the commit timestamp SLRU cache.</td></tr><tr><td><code class="literal">ControlFile</code></td><td>Waiting to read or update the <code class="filename">pg_control</code> file or create a new WAL file.</td></tr><tr><td><code class="literal">DSMRegistry</code></td><td>Waiting to read or update the dynamic shared memory registry.</td></tr><tr><td><code class="literal">DSMRegistryDSA</code></td><td>Waiting to access dynamic shared memory registry's dynamic shared memory allocator.</td></tr><tr><td><code class="literal">DSMRegistryHash</code></td><td>Waiting to access dynamic shared memory registry's shared hash table.</td></tr><tr><td><code class="literal">DynamicSharedMemoryControl</code></td><td>Waiting to read or update dynamic shared memory allocation information.</td></tr><tr><td><code class="literal">InjectionPoint</code></td><td>Waiting to read or update information related to injection points.</td></tr><tr><td><code class="literal">LockFastPath</code></td><td>Waiting to read or update a process' fast-path lock information.</td></tr><tr><td><code class="literal">LockManager</code></td><td>Waiting to read or update information about <span class="quote">“<span class="quote">heavyweight</span>”</span> locks.</td></tr><tr><td><code class="literal">LogicalRepLauncherDSA</code></td><td>Waiting to access logical replication launcher's dynamic shared memory allocator.</td></tr><tr><td><code class="literal">LogicalRepLauncherHash</code></td><td>Waiting to access logical replication launcher's shared hash table.</td></tr><tr><td><code class="literal">LogicalRepWorker</code></td><td>Waiting to read or update the state of logical replication workers.</td></tr><tr><td><code class="literal">MultiXactGen</code></td><td>Waiting to read or update shared multixact state.</td></tr><tr><td><code class="literal">MultiXactMemberBuffer</code></td><td>Waiting for I/O on a multixact member SLRU buffer.</td></tr><tr><td><code class="literal">MultiXactMemberSLRU</code></td><td>Waiting to access the multixact member SLRU cache.</td></tr><tr><td><code class="literal">MultiXactOffsetBuffer</code></td><td>Waiting for I/O on a multixact offset SLRU buffer.</td></tr><tr><td><code class="literal">MultiXactOffsetSLRU</code></td><td>Waiting to access the multixact offset SLRU cache.</td></tr><tr><td><code class="literal">MultiXactTruncation</code></td><td>Waiting to read or truncate multixact information.</td></tr><tr><td><code class="literal">NotifyBuffer</code></td><td>Waiting for I/O on a <code class="command">NOTIFY</code> message SLRU buffer.</td></tr><tr><td><code class="literal">NotifyQueue</code></td><td>Waiting to read or update <code class="command">NOTIFY</code> messages.</td></tr><tr><td><code class="literal">NotifyQueueTail</code></td><td>Waiting to update limit on <code class="command">NOTIFY</code> message storage.</td></tr><tr><td><code class="literal">NotifySLRU</code></td><td>Waiting to access the <code class="command">NOTIFY</code> message SLRU cache.</td></tr><tr><td><code class="literal">OidGen</code></td><td>Waiting to allocate a new OID.</td></tr><tr><td><code class="literal">ParallelAppend</code></td><td>Waiting to choose the next subplan during Parallel Append plan execution.</td></tr><tr><td><code class="literal">ParallelBtreeScan</code></td><td>Waiting to synchronize workers during Parallel B-tree scan plan execution.</td></tr><tr><td><code class="literal">ParallelHashJoin</code></td><td>Waiting to synchronize workers during Parallel Hash Join plan execution.</td></tr><tr><td><code class="literal">ParallelQueryDSA</code></td><td>Waiting for parallel query dynamic shared memory allocation.</td></tr><tr><td><code class="literal">ParallelVacuumDSA</code></td><td>Waiting for parallel vacuum dynamic shared memory allocation.</td></tr><tr><td><code class="literal">PerSessionDSA</code></td><td>Waiting for parallel query dynamic shared memory allocation.</td></tr><tr><td><code class="literal">PerSessionRecordType</code></td><td>Waiting to access a parallel query's information about composite types.</td></tr><tr><td><code class="literal">PerSessionRecordTypmod</code></td><td>Waiting to access a parallel query's information about type modifiers that identify anonymous record types.</td></tr><tr><td><code class="literal">PerXactPredicateList</code></td><td>Waiting to access the list of predicate locks held by the current serializable transaction during a parallel query.</td></tr><tr><td><code class="literal">PgStatsData</code></td><td>Waiting for shared memory stats data access.</td></tr><tr><td><code class="literal">PgStatsDSA</code></td><td>Waiting for stats dynamic shared memory allocator access.</td></tr><tr><td><code class="literal">PgStatsHash</code></td><td>Waiting for stats shared memory hash table access.</td></tr><tr><td><code class="literal">PredicateLockManager</code></td><td>Waiting to access predicate lock information used by serializable transactions.</td></tr><tr><td><code class="literal">ProcArray</code></td><td>Waiting to access the shared per-process data structures (typically, to get a snapshot or report a session's transaction ID).</td></tr><tr><td><code class="literal">RelationMapping</code></td><td>Waiting to read or update a <code class="filename">pg_filenode.map</code> file (used to track the filenode assignments of certain system catalogs).</td></tr><tr><td><code class="literal">RelCacheInit</code></td><td>Waiting to read or update a <code class="filename">pg_internal.init</code> relation cache initialization file.</td></tr><tr><td><code class="literal">ReplicationOrigin</code></td><td>Waiting to create, drop or use a replication origin.</td></tr><tr><td><code class="literal">ReplicationOriginState</code></td><td>Waiting to read or update the progress of one replication origin.</td></tr><tr><td><code class="literal">ReplicationSlotAllocation</code></td><td>Waiting to allocate or free a replication slot.</td></tr><tr><td><code class="literal">ReplicationSlotControl</code></td><td>Waiting to read or update replication slot state.</td></tr><tr><td><code class="literal">ReplicationSlotIO</code></td><td>Waiting for I/O on a replication slot.</td></tr><tr><td><code class="literal">SerialBuffer</code></td><td>Waiting for I/O on a serializable transaction conflict SLRU buffer.</td></tr><tr><td><code class="literal">SerialControl</code></td><td>Waiting to read or update shared <code class="filename">pg_serial</code> state.</td></tr><tr><td><code class="literal">SerializableFinishedList</code></td><td>Waiting to access the list of finished serializable transactions.</td></tr><tr><td><code class="literal">SerializablePredicateList</code></td><td>Waiting to access the list of predicate locks held by serializable transactions.</td></tr><tr><td><code class="literal">SerializableXactHash</code></td><td>Waiting to read or update information about serializable transactions.</td></tr><tr><td><code class="literal">SerialSLRU</code></td><td>Waiting to access the serializable transaction conflict SLRU cache.</td></tr><tr><td><code class="literal">SharedTidBitmap</code></td><td>Waiting to access a shared TID bitmap during a parallel bitmap index scan.</td></tr><tr><td><code class="literal">SharedTupleStore</code></td><td>Waiting to access a shared tuple store during parallel query.</td></tr><tr><td><code class="literal">ShmemIndex</code></td><td>Waiting to find or allocate space in shared memory.</td></tr><tr><td><code class="literal">SInvalRead</code></td><td>Waiting to retrieve messages from the shared catalog invalidation queue.</td></tr><tr><td><code class="literal">SInvalWrite</code></td><td>Waiting to add a message to the shared catalog invalidation queue.</td></tr><tr><td><code class="literal">SubtransBuffer</code></td><td>Waiting for I/O on a sub-transaction SLRU buffer.</td></tr><tr><td><code class="literal">SubtransSLRU</code></td><td>Waiting to access the sub-transaction SLRU cache.</td></tr><tr><td><code class="literal">SyncRep</code></td><td>Waiting to read or update information about the state of synchronous replication.</td></tr><tr><td><code class="literal">SyncScan</code></td><td>Waiting to select the starting location of a synchronized table scan.</td></tr><tr><td><code class="literal">TablespaceCreate</code></td><td>Waiting to create or drop a tablespace.</td></tr><tr><td><code class="literal">TwoPhaseState</code></td><td>Waiting to read or update the state of prepared transactions.</td></tr><tr><td><code class="literal">WaitEventCustom</code></td><td>Waiting to read or update custom wait events information.</td></tr><tr><td><code class="literal">WALBufMapping</code></td><td>Waiting to replace a page in WAL buffers.</td></tr><tr><td><code class="literal">WALInsert</code></td><td>Waiting to insert WAL data into a memory buffer.</td></tr><tr><td><code class="literal">WALSummarizer</code></td><td>Waiting to read or update WAL summarization state.</td></tr><tr><td><code class="literal">WALWrite</code></td><td>Waiting for WAL buffers to be written to disk.</td></tr><tr><td><code class="literal">WrapLimitsVacuum</code></td><td>Waiting to update limits on transaction id and multixact consumption.</td></tr><tr><td><code class="literal">XactBuffer</code></td><td>Waiting for I/O on a transaction status SLRU buffer.</td></tr><tr><td><code class="literal">XactSLRU</code></td><td>Waiting to access the transaction status SLRU cache.</td></tr><tr><td><code class="literal">XactTruncation</code></td><td>Waiting to execute <code class="function">pg_xact_status</code> or update the oldest transaction ID available to it.</td></tr><tr><td><code class="literal">XidGen</code></td><td>Waiting to allocate a new transaction ID.</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="WAIT-EVENT-TIMEOUT-TABLE"><p class="title"><strong>Table 27.13. Wait Events of Type <code class="literal">Timeout</code></strong></p><div class="table-contents"><table class="table" summary="Wait Events of Type Timeout" border="1"><colgroup><col /><col /></colgroup><thead><tr><th><code class="literal">Timeout</code> Wait Event</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">BaseBackupThrottle</code></td><td>Waiting during base backup when throttling activity.</td></tr><tr><td><code class="literal">CheckpointWriteDelay</code></td><td>Waiting between writes while performing a checkpoint.</td></tr><tr><td><code class="literal">PgSleep</code></td><td>Waiting due to a call to <code class="function">pg_sleep</code> or a sibling function.</td></tr><tr><td><code class="literal">RecoveryApplyDelay</code></td><td>Waiting to apply WAL during recovery because of a delay setting.</td></tr><tr><td><code class="literal">RecoveryRetrieveRetryInterval</code></td><td>Waiting during recovery when WAL data is not available from any source (<code class="filename">pg_wal</code>, archive or stream).</td></tr><tr><td><code class="literal">RegisterSyncRequest</code></td><td>Waiting while sending synchronization requests to the checkpointer, because the request queue is full.</td></tr><tr><td><code class="literal">SpinDelay</code></td><td>Waiting while acquiring a contended spinlock.</td></tr><tr><td><code class="literal">VacuumDelay</code></td><td>Waiting in a cost-based vacuum delay point.</td></tr><tr><td><code class="literal">VacuumTruncate</code></td><td>Waiting to acquire an exclusive lock to truncate off any empty pages at the end of a table vacuumed.</td></tr><tr><td><code class="literal">WalSummarizerError</code></td><td>Waiting after a WAL summarizer error.</td></tr></tbody></table></div></div><br class="table-break" /><p>
506 Here are examples of how wait events can be viewed:
508 </p><pre class="programlisting">
509 SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;
510 pid | wait_event_type | wait_event
511 ------+-----------------+------------
512 2540 | Lock | relation
513 6644 | LWLock | ProcArray
517 </p><pre class="programlisting">
518 SELECT a.pid, a.wait_event, w.description
519 FROM pg_stat_activity a JOIN
520 pg_wait_events w ON (a.wait_event_type = w.type AND
521 a.wait_event = w.name)
522 WHERE a.wait_event is NOT NULL and a.state = 'active';
523 -[ RECORD 1 ]------------------------------------------------------------------
525 wait_event | WALInitSync
526 description | Waiting for a newly initialized WAL file to reach durable storage
528 </p><div class="note"><h3 class="title">Note</h3><p>
529 Extensions can add <code class="literal">Extension</code>,
530 <code class="literal">InjectionPoint</code>, and <code class="literal">LWLock</code> events
531 to the lists shown in <a class="xref" href="monitoring-stats.html#WAIT-EVENT-EXTENSION-TABLE" title="Table 27.8. Wait Events of Type Extension">Table 27.8</a> and
532 <a class="xref" href="monitoring-stats.html#WAIT-EVENT-LWLOCK-TABLE" title="Table 27.12. Wait Events of Type Lwlock">Table 27.12</a>. In some cases, the name
533 of an <code class="literal">LWLock</code> assigned by an extension will not be
534 available in all server processes. It might be reported as just
535 <span class="quote">“<span class="quote"><code class="literal">extension</code></span>”</span> rather than the
536 extension-assigned name.
537 </p></div></div><div class="sect2" id="MONITORING-PG-STAT-REPLICATION-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.4. <code class="structname">pg_stat_replication</code> <a href="#MONITORING-PG-STAT-REPLICATION-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.8.2" class="indexterm"></a><p>
538 The <code class="structname">pg_stat_replication</code> view will contain one row
539 per WAL sender process, showing statistics about replication to that
540 sender's connected standby server. Only directly connected standbys are
541 listed; no information is available about downstream standby servers.
542 </p><div class="table" id="PG-STAT-REPLICATION-VIEW"><p class="title"><strong>Table 27.14. <code class="structname">pg_stat_replication</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_replication View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
547 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
548 <code class="structfield">pid</code> <code class="type">integer</code>
551 Process ID of a WAL sender process
552 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
553 <code class="structfield">usesysid</code> <code class="type">oid</code>
556 OID of the user logged into this WAL sender process
557 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
558 <code class="structfield">usename</code> <code class="type">name</code>
561 Name of the user logged into this WAL sender process
562 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
563 <code class="structfield">application_name</code> <code class="type">text</code>
566 Name of the application that is connected
568 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
569 <code class="structfield">client_addr</code> <code class="type">inet</code>
572 IP address of the client connected to this WAL sender.
573 If this field is null, it indicates that the client is
574 connected via a Unix socket on the server machine.
575 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
576 <code class="structfield">client_hostname</code> <code class="type">text</code>
579 Host name of the connected client, as reported by a
580 reverse DNS lookup of <code class="structfield">client_addr</code>. This field will
581 only be non-null for IP connections, and only when <a class="xref" href="runtime-config-logging.html#GUC-LOG-HOSTNAME">log_hostname</a> is enabled.
582 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
583 <code class="structfield">client_port</code> <code class="type">integer</code>
586 TCP port number that the client is using for communication
587 with this WAL sender, or <code class="literal">-1</code> if a Unix socket is used
588 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
589 <code class="structfield">backend_start</code> <code class="type">timestamp with time zone</code>
592 Time when this process was started, i.e., when the
593 client connected to this WAL sender
594 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
595 <code class="structfield">backend_xmin</code> <code class="type">xid</code>
598 This standby's <code class="literal">xmin</code> horizon reported
599 by <a class="xref" href="runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK">hot_standby_feedback</a>.
600 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
601 <code class="structfield">state</code> <code class="type">text</code>
604 Current WAL sender state.
606 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
607 <code class="literal">startup</code>: This WAL sender is starting up.
608 </p></li><li class="listitem"><p>
609 <code class="literal">catchup</code>: This WAL sender's connected standby is
610 catching up with the primary.
611 </p></li><li class="listitem"><p>
612 <code class="literal">streaming</code>: This WAL sender is streaming changes
613 after its connected standby server has caught up with the primary.
614 </p></li><li class="listitem"><p>
615 <code class="literal">backup</code>: This WAL sender is sending a backup.
616 </p></li><li class="listitem"><p>
617 <code class="literal">stopping</code>: This WAL sender is stopping.
618 </p></li></ul></div><p>
619 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
620 <code class="structfield">sent_lsn</code> <code class="type">pg_lsn</code>
623 Last write-ahead log location sent on this connection
624 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
625 <code class="structfield">write_lsn</code> <code class="type">pg_lsn</code>
628 Last write-ahead log location written to disk by this standby
630 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
631 <code class="structfield">flush_lsn</code> <code class="type">pg_lsn</code>
634 Last write-ahead log location flushed to disk by this standby
636 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
637 <code class="structfield">replay_lsn</code> <code class="type">pg_lsn</code>
640 Last write-ahead log location replayed into the database on this
642 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
643 <code class="structfield">write_lag</code> <code class="type">interval</code>
646 Time elapsed between flushing recent WAL locally and receiving
647 notification that this standby server has written it (but not yet
648 flushed it or applied it). This can be used to gauge the delay that
649 <code class="literal">synchronous_commit</code> level
650 <code class="literal">remote_write</code> incurred while committing if this
651 server was configured as a synchronous standby.
652 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
653 <code class="structfield">flush_lag</code> <code class="type">interval</code>
656 Time elapsed between flushing recent WAL locally and receiving
657 notification that this standby server has written and flushed it
658 (but not yet applied it). This can be used to gauge the delay that
659 <code class="literal">synchronous_commit</code> level
660 <code class="literal">on</code> incurred while committing if this
661 server was configured as a synchronous standby.
662 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
663 <code class="structfield">replay_lag</code> <code class="type">interval</code>
666 Time elapsed between flushing recent WAL locally and receiving
667 notification that this standby server has written, flushed and
668 applied it. This can be used to gauge the delay that
669 <code class="literal">synchronous_commit</code> level
670 <code class="literal">remote_apply</code> incurred while committing if this
671 server was configured as a synchronous standby.
672 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
673 <code class="structfield">sync_priority</code> <code class="type">integer</code>
676 Priority of this standby server for being chosen as the
677 synchronous standby in a priority-based synchronous replication.
678 This has no effect in a quorum-based synchronous replication.
679 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
680 <code class="structfield">sync_state</code> <code class="type">text</code>
683 Synchronous state of this standby server.
685 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
686 <code class="literal">async</code>: This standby server is asynchronous.
687 </p></li><li class="listitem"><p>
688 <code class="literal">potential</code>: This standby server is now asynchronous,
689 but can potentially become synchronous if one of current
690 synchronous ones fails.
691 </p></li><li class="listitem"><p>
692 <code class="literal">sync</code>: This standby server is synchronous.
693 </p></li><li class="listitem"><p>
694 <code class="literal">quorum</code>: This standby server is considered as a candidate
696 </p></li></ul></div><p>
697 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
698 <code class="structfield">reply_time</code> <code class="type">timestamp with time zone</code>
701 Send time of last reply message received from standby server
702 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
703 The lag times reported in the <code class="structname">pg_stat_replication</code>
704 view are measurements of the time taken for recent WAL to be written,
705 flushed and replayed and for the sender to know about it. These times
706 represent the commit delay that was (or would have been) introduced by each
707 synchronous commit level, if the remote server was configured as a
708 synchronous standby. For an asynchronous standby, the
709 <code class="structfield">replay_lag</code> column approximates the delay
710 before recent transactions became visible to queries. If the standby
711 server has entirely caught up with the sending server and there is no more
712 WAL activity, the most recently measured lag times will continue to be
713 displayed for a short time and then show NULL.
715 Lag times work automatically for physical replication. Logical decoding
716 plugins may optionally emit tracking messages; if they do not, the tracking
717 mechanism will simply display NULL lag.
718 </p><div class="note"><h3 class="title">Note</h3><p>
719 The reported lag times are not predictions of how long it will take for
720 the standby to catch up with the sending server assuming the current
721 rate of replay. Such a system would show similar times while new WAL is
722 being generated, but would differ when the sender becomes idle. In
723 particular, when the standby has caught up completely,
724 <code class="structname">pg_stat_replication</code> shows the time taken to
725 write, flush and replay the most recent reported WAL location rather than
726 zero as some users might expect. This is consistent with the goal of
727 measuring synchronous commit and transaction visibility delays for
728 recent write transactions.
729 To reduce confusion for users expecting a different model of lag, the
730 lag columns revert to NULL after a short time on a fully replayed idle
731 system. Monitoring systems should choose whether to represent this
732 as missing data, zero or continue to display the last known value.
733 </p></div></div><div class="sect2" id="MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.5. <code class="structname">pg_stat_replication_slots</code> <a href="#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.9.2" class="indexterm"></a><p>
734 The <code class="structname">pg_stat_replication_slots</code> view will contain
735 one row per logical replication slot, showing statistics about its usage.
736 </p><div class="table" id="PG-STAT-REPLICATION-SLOTS-VIEW"><p class="title"><strong>Table 27.15. <code class="structname">pg_stat_replication_slots</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_replication_slots View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
741 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
742 <code class="structfield">slot_name</code> <code class="type">text</code>
745 A unique, cluster-wide identifier for the replication slot
746 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
747 <code class="structfield">spill_txns</code> <code class="type">bigint</code>
750 Number of transactions spilled to disk once the memory used by
751 logical decoding to decode changes from WAL has exceeded
752 <code class="literal">logical_decoding_work_mem</code>. The counter gets
753 incremented for both top-level transactions and subtransactions.
754 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
755 <code class="structfield">spill_count</code> <code class="type">bigint</code>
758 Number of times transactions were spilled to disk while decoding
759 changes from WAL for this slot. This counter is incremented each time
760 a transaction is spilled, and the same transaction may be spilled
762 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
763 <code class="structfield">spill_bytes</code> <code class="type">bigint</code>
766 Amount of decoded transaction data spilled to disk while performing
767 decoding of changes from WAL for this slot. This and other spill
768 counters can be used to gauge the I/O which occurred during logical
769 decoding and allow tuning <code class="literal">logical_decoding_work_mem</code>.
770 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
771 <code class="structfield">stream_txns</code> <code class="type">bigint</code>
774 Number of in-progress transactions streamed to the decoding output
775 plugin after the memory used by logical decoding to decode changes
776 from WAL for this slot has exceeded
777 <code class="literal">logical_decoding_work_mem</code>. Streaming only
778 works with top-level transactions (subtransactions can't be streamed
779 independently), so the counter is not incremented for subtransactions.
780 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
781 <code class="structfield">stream_count</code><code class="type">bigint</code>
784 Number of times in-progress transactions were streamed to the decoding
785 output plugin while decoding changes from WAL for this slot. This
786 counter is incremented each time a transaction is streamed, and the
787 same transaction may be streamed multiple times.
788 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
789 <code class="structfield">stream_bytes</code><code class="type">bigint</code>
792 Amount of transaction data decoded for streaming in-progress
793 transactions to the decoding output plugin while decoding changes from
794 WAL for this slot. This and other streaming counters for this slot can
795 be used to tune <code class="literal">logical_decoding_work_mem</code>.
797 </td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
798 <code class="structfield">total_txns</code> <code class="type">bigint</code>
801 Number of decoded transactions sent to the decoding output plugin for
802 this slot. This counts top-level transactions only, and is not incremented
803 for subtransactions. Note that this includes the transactions that are
804 streamed and/or spilled.
805 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
806 <code class="structfield">total_bytes</code><code class="type">bigint</code>
809 Amount of transaction data decoded for sending transactions to the
810 decoding output plugin while decoding changes from WAL for this slot.
811 Note that this includes data that is streamed and/or spilled.
813 </td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
814 <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code>
817 Time at which these statistics were last reset
818 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-WAL-RECEIVER-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.6. <code class="structname">pg_stat_wal_receiver</code> <a href="#MONITORING-PG-STAT-WAL-RECEIVER-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.10.2" class="indexterm"></a><p>
819 The <code class="structname">pg_stat_wal_receiver</code> view will contain only
820 one row, showing statistics about the WAL receiver from that receiver's
822 </p><div class="table" id="PG-STAT-WAL-RECEIVER-VIEW"><p class="title"><strong>Table 27.16. <code class="structname">pg_stat_wal_receiver</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_wal_receiver View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
827 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
828 <code class="structfield">pid</code> <code class="type">integer</code>
831 Process ID of the WAL receiver process
832 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
833 <code class="structfield">status</code> <code class="type">text</code>
836 Activity status of the WAL receiver process
837 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
838 <code class="structfield">receive_start_lsn</code> <code class="type">pg_lsn</code>
841 First write-ahead log location used when WAL receiver is
843 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
844 <code class="structfield">receive_start_tli</code> <code class="type">integer</code>
847 First timeline number used when WAL receiver is started
848 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
849 <code class="structfield">written_lsn</code> <code class="type">pg_lsn</code>
852 Last write-ahead log location already received and written to disk,
853 but not flushed. This should not be used for data integrity checks.
854 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
855 <code class="structfield">flushed_lsn</code> <code class="type">pg_lsn</code>
858 Last write-ahead log location already received and flushed to
859 disk, the initial value of this field being the first log location used
860 when WAL receiver is started
861 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
862 <code class="structfield">received_tli</code> <code class="type">integer</code>
865 Timeline number of last write-ahead log location received and
866 flushed to disk, the initial value of this field being the timeline
867 number of the first log location used when WAL receiver is started
868 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
869 <code class="structfield">last_msg_send_time</code> <code class="type">timestamp with time zone</code>
872 Send time of last message received from origin WAL sender
873 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
874 <code class="structfield">last_msg_receipt_time</code> <code class="type">timestamp with time zone</code>
877 Receipt time of last message received from origin WAL sender
878 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
879 <code class="structfield">latest_end_lsn</code> <code class="type">pg_lsn</code>
882 Last write-ahead log location reported to origin WAL sender
883 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
884 <code class="structfield">latest_end_time</code> <code class="type">timestamp with time zone</code>
887 Time of last write-ahead log location reported to origin WAL sender
888 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
889 <code class="structfield">slot_name</code> <code class="type">text</code>
892 Replication slot name used by this WAL receiver
893 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
894 <code class="structfield">sender_host</code> <code class="type">text</code>
897 Host of the <span class="productname">PostgreSQL</span> instance
898 this WAL receiver is connected to. This can be a host name,
899 an IP address, or a directory path if the connection is via
900 Unix socket. (The path case can be distinguished because it
901 will always be an absolute path, beginning with <code class="literal">/</code>.)
902 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
903 <code class="structfield">sender_port</code> <code class="type">integer</code>
906 Port number of the <span class="productname">PostgreSQL</span> instance
907 this WAL receiver is connected to.
908 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
909 <code class="structfield">conninfo</code> <code class="type">text</code>
912 Connection string used by this WAL receiver,
913 with security-sensitive fields obfuscated.
914 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-RECOVERY-PREFETCH"><div class="titlepage"><div><div><h3 class="title">27.2.7. <code class="structname">pg_stat_recovery_prefetch</code> <a href="#MONITORING-PG-STAT-RECOVERY-PREFETCH" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.11.2" class="indexterm"></a><p>
915 The <code class="structname">pg_stat_recovery_prefetch</code> view will contain
916 only one row. The columns <code class="structfield">wal_distance</code>,
917 <code class="structfield">block_distance</code> and
918 <code class="structfield">io_depth</code> show current values, and the
919 other columns show cumulative counters that can be reset
920 with the <code class="function">pg_stat_reset_shared</code> function.
921 </p><div class="table" id="PG-STAT-RECOVERY-PREFETCH-VIEW"><p class="title"><strong>Table 27.17. <code class="structname">pg_stat_recovery_prefetch</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_recovery_prefetch View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
926 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry">
927 <p class="column_definition">
928 <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code>
931 Time at which these statistics were last reset
933 </td></tr><tr><td class="catalog_table_entry">
934 <p class="column_definition">
935 <code class="structfield">prefetch</code> <code class="type">bigint</code>
938 Number of blocks prefetched because they were not in the buffer pool
940 </td></tr><tr><td class="catalog_table_entry">
941 <p class="column_definition">
942 <code class="structfield">hit</code> <code class="type">bigint</code>
945 Number of blocks not prefetched because they were already in the buffer pool
947 </td></tr><tr><td class="catalog_table_entry">
948 <p class="column_definition">
949 <code class="structfield">skip_init</code> <code class="type">bigint</code>
952 Number of blocks not prefetched because they would be zero-initialized
954 </td></tr><tr><td class="catalog_table_entry">
955 <p class="column_definition">
956 <code class="structfield">skip_new</code> <code class="type">bigint</code>
959 Number of blocks not prefetched because they didn't exist yet
961 </td></tr><tr><td class="catalog_table_entry">
962 <p class="column_definition">
963 <code class="structfield">skip_fpw</code> <code class="type">bigint</code>
966 Number of blocks not prefetched because a full page image was included in the WAL
968 </td></tr><tr><td class="catalog_table_entry">
969 <p class="column_definition">
970 <code class="structfield">skip_rep</code> <code class="type">bigint</code>
973 Number of blocks not prefetched because they were already recently prefetched
975 </td></tr><tr><td class="catalog_table_entry">
976 <p class="column_definition">
977 <code class="structfield">wal_distance</code> <code class="type">int</code>
980 How many bytes ahead the prefetcher is looking
982 </td></tr><tr><td class="catalog_table_entry">
983 <p class="column_definition">
984 <code class="structfield">block_distance</code> <code class="type">int</code>
987 How many blocks ahead the prefetcher is looking
989 </td></tr><tr><td class="catalog_table_entry">
990 <p class="column_definition">
991 <code class="structfield">io_depth</code> <code class="type">int</code>
994 How many prefetches have been initiated but are not yet known to have completed
996 </td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-SUBSCRIPTION"><div class="titlepage"><div><div><h3 class="title">27.2.8. <code class="structname">pg_stat_subscription</code> <a href="#MONITORING-PG-STAT-SUBSCRIPTION" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.12.2" class="indexterm"></a><div class="table" id="PG-STAT-SUBSCRIPTION"><p class="title"><strong>Table 27.18. <code class="structname">pg_stat_subscription</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_subscription View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
1001 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
1002 <code class="structfield">subid</code> <code class="type">oid</code>
1005 OID of the subscription
1006 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1007 <code class="structfield">subname</code> <code class="type">name</code>
1010 Name of the subscription
1011 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1012 <code class="structfield">worker_type</code> <code class="type">text</code>
1015 Type of the subscription worker process. Possible types are
1016 <code class="literal">apply</code>, <code class="literal">parallel apply</code>, and
1017 <code class="literal">table synchronization</code>.
1018 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1019 <code class="structfield">pid</code> <code class="type">integer</code>
1022 Process ID of the subscription worker process
1023 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1024 <code class="structfield">leader_pid</code> <code class="type">integer</code>
1027 Process ID of the leader apply worker if this process is a parallel
1028 apply worker; NULL if this process is a leader apply worker or a table
1029 synchronization worker
1030 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1031 <code class="structfield">relid</code> <code class="type">oid</code>
1034 OID of the relation that the worker is synchronizing; NULL for the
1035 leader apply worker and parallel apply workers
1036 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1037 <code class="structfield">received_lsn</code> <code class="type">pg_lsn</code>
1040 Last write-ahead log location received, the initial value of
1041 this field being 0; NULL for parallel apply workers
1042 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1043 <code class="structfield">last_msg_send_time</code> <code class="type">timestamp with time zone</code>
1046 Send time of last message received from origin WAL sender; NULL for
1047 parallel apply workers
1048 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1049 <code class="structfield">last_msg_receipt_time</code> <code class="type">timestamp with time zone</code>
1052 Receipt time of last message received from origin WAL sender; NULL for
1053 parallel apply workers
1054 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1055 <code class="structfield">latest_end_lsn</code> <code class="type">pg_lsn</code>
1058 Last write-ahead log location reported to origin WAL sender; NULL for
1059 parallel apply workers
1060 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1061 <code class="structfield">latest_end_time</code> <code class="type">timestamp with time zone</code>
1064 Time of last write-ahead log location reported to origin WAL
1065 sender; NULL for parallel apply workers
1066 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-SUBSCRIPTION-STATS"><div class="titlepage"><div><div><h3 class="title">27.2.9. <code class="structname">pg_stat_subscription_stats</code> <a href="#MONITORING-PG-STAT-SUBSCRIPTION-STATS" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.13.2" class="indexterm"></a><p>
1067 The <code class="structname">pg_stat_subscription_stats</code> view will contain
1068 one row per subscription.
1069 </p><div class="table" id="PG-STAT-SUBSCRIPTION-STATS"><p class="title"><strong>Table 27.19. <code class="structname">pg_stat_subscription_stats</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_subscription_stats View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
1074 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
1075 <code class="structfield">subid</code> <code class="type">oid</code>
1078 OID of the subscription
1079 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1080 <code class="structfield">subname</code> <code class="type">name</code>
1083 Name of the subscription
1084 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1085 <code class="structfield">apply_error_count</code> <code class="type">bigint</code>
1088 Number of times an error occurred while applying changes. Note that any
1089 conflict resulting in an apply error will be counted in both
1090 <code class="literal">apply_error_count</code> and the corresponding conflict
1091 count (e.g., <code class="literal">confl_*</code>).
1092 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1093 <code class="structfield">sync_error_count</code> <code class="type">bigint</code>
1096 Number of times an error occurred during the initial table
1098 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1099 <code class="structfield">confl_insert_exists</code> <code class="type">bigint</code>
1102 Number of times a row insertion violated a
1103 <code class="literal">NOT DEFERRABLE</code> unique constraint during the
1104 application of changes. See <a class="xref" href="logical-replication-conflicts.html#CONFLICT-INSERT-EXISTS">insert_exists</a>
1105 for details about this conflict.
1106 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1107 <code class="structfield">confl_update_origin_differs</code> <code class="type">bigint</code>
1110 Number of times an update was applied to a row that had been previously
1111 modified by another source during the application of changes. See
1112 <a class="xref" href="logical-replication-conflicts.html#CONFLICT-UPDATE-ORIGIN-DIFFERS">update_origin_differs</a> for details about this
1114 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1115 <code class="structfield">confl_update_exists</code> <code class="type">bigint</code>
1118 Number of times that an updated row value violated a
1119 <code class="literal">NOT DEFERRABLE</code> unique constraint during the
1120 application of changes. See <a class="xref" href="logical-replication-conflicts.html#CONFLICT-UPDATE-EXISTS">update_exists</a>
1121 for details about this conflict.
1122 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1123 <code class="structfield">confl_update_missing</code> <code class="type">bigint</code>
1126 Number of times the tuple to be updated was not found during the
1127 application of changes. See <a class="xref" href="logical-replication-conflicts.html#CONFLICT-UPDATE-MISSING">update_missing</a>
1128 for details about this conflict.
1129 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1130 <code class="structfield">confl_delete_origin_differs</code> <code class="type">bigint</code>
1133 Number of times a delete operation was applied to row that had been
1134 previously modified by another source during the application of changes.
1135 See <a class="xref" href="logical-replication-conflicts.html#CONFLICT-DELETE-ORIGIN-DIFFERS">delete_origin_differs</a> for details about
1137 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1138 <code class="structfield">confl_delete_missing</code> <code class="type">bigint</code>
1141 Number of times the tuple to be deleted was not found during the application
1142 of changes. See <a class="xref" href="logical-replication-conflicts.html#CONFLICT-DELETE-MISSING">delete_missing</a> for details
1143 about this conflict.
1144 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1145 <code class="structfield">confl_multiple_unique_conflicts</code> <code class="type">bigint</code>
1148 Number of times a row insertion or an updated row values violated multiple
1149 <code class="literal">NOT DEFERRABLE</code> unique constraints during the
1150 application of changes. See <a class="xref" href="logical-replication-conflicts.html#CONFLICT-MULTIPLE-UNIQUE-CONFLICTS">multiple_unique_conflicts</a>
1151 for details about this conflict.
1152 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1153 <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code>
1156 Time at which these statistics were last reset
1157 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-SSL-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.10. <code class="structname">pg_stat_ssl</code> <a href="#MONITORING-PG-STAT-SSL-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.14.2" class="indexterm"></a><p>
1158 The <code class="structname">pg_stat_ssl</code> view will contain one row per
1159 backend or WAL sender process, showing statistics about SSL usage on
1160 this connection. It can be joined to <code class="structname">pg_stat_activity</code>
1161 or <code class="structname">pg_stat_replication</code> on the
1162 <code class="structfield">pid</code> column to get more details about the
1164 </p><div class="table" id="PG-STAT-SSL-VIEW"><p class="title"><strong>Table 27.20. <code class="structname">pg_stat_ssl</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_ssl View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
1169 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
1170 <code class="structfield">pid</code> <code class="type">integer</code>
1173 Process ID of a backend or WAL sender process
1174 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1175 <code class="structfield">ssl</code> <code class="type">boolean</code>
1178 True if SSL is used on this connection
1179 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1180 <code class="structfield">version</code> <code class="type">text</code>
1183 Version of SSL in use, or NULL if SSL is not in use
1185 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1186 <code class="structfield">cipher</code> <code class="type">text</code>
1189 Name of SSL cipher in use, or NULL if SSL is not in use
1191 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1192 <code class="structfield">bits</code> <code class="type">integer</code>
1195 Number of bits in the encryption algorithm used, or NULL
1196 if SSL is not used on this connection
1197 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1198 <code class="structfield">client_dn</code> <code class="type">text</code>
1201 Distinguished Name (DN) field from the client certificate
1202 used, or NULL if no client certificate was supplied or if SSL
1203 is not in use on this connection. This field is truncated if the
1204 DN field is longer than <code class="symbol">NAMEDATALEN</code> (64 characters
1205 in a standard build).
1206 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1207 <code class="structfield">client_serial</code> <code class="type">numeric</code>
1210 Serial number of the client certificate, or NULL if no client
1211 certificate was supplied or if SSL is not in use on this connection. The
1212 combination of certificate serial number and certificate issuer uniquely
1213 identifies a certificate (unless the issuer erroneously reuses serial
1215 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1216 <code class="structfield">issuer_dn</code> <code class="type">text</code>
1219 DN of the issuer of the client certificate, or NULL if no client
1220 certificate was supplied or if SSL is not in use on this connection.
1221 This field is truncated like <code class="structfield">client_dn</code>.
1222 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-GSSAPI-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.11. <code class="structname">pg_stat_gssapi</code> <a href="#MONITORING-PG-STAT-GSSAPI-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.15.2" class="indexterm"></a><p>
1223 The <code class="structname">pg_stat_gssapi</code> view will contain one row per
1224 backend, showing information about GSSAPI usage on this connection. It can
1225 be joined to <code class="structname">pg_stat_activity</code> or
1226 <code class="structname">pg_stat_replication</code> on the
1227 <code class="structfield">pid</code> column to get more details about the
1229 </p><div class="table" id="PG-STAT-GSSAPI-VIEW"><p class="title"><strong>Table 27.21. <code class="structname">pg_stat_gssapi</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_gssapi View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
1234 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
1235 <code class="structfield">pid</code> <code class="type">integer</code>
1238 Process ID of a backend
1239 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1240 <code class="structfield">gss_authenticated</code> <code class="type">boolean</code>
1243 True if GSSAPI authentication was used for this connection
1244 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1245 <code class="structfield">principal</code> <code class="type">text</code>
1248 Principal used to authenticate this connection, or NULL
1249 if GSSAPI was not used to authenticate this connection. This
1250 field is truncated if the principal is longer than
1251 <code class="symbol">NAMEDATALEN</code> (64 characters in a standard build).
1252 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1253 <code class="structfield">encrypted</code> <code class="type">boolean</code>
1256 True if GSSAPI encryption is in use on this connection
1257 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1258 <code class="structfield">credentials_delegated</code> <code class="type">boolean</code>
1261 True if GSSAPI credentials were delegated on this connection.
1262 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-ARCHIVER-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.12. <code class="structname">pg_stat_archiver</code> <a href="#MONITORING-PG-STAT-ARCHIVER-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.16.2" class="indexterm"></a><p>
1263 The <code class="structname">pg_stat_archiver</code> view will always have a
1264 single row, containing data about the archiver process of the cluster.
1265 </p><div class="table" id="PG-STAT-ARCHIVER-VIEW"><p class="title"><strong>Table 27.22. <code class="structname">pg_stat_archiver</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_archiver View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
1270 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
1271 <code class="structfield">archived_count</code> <code class="type">bigint</code>
1274 Number of WAL files that have been successfully archived
1275 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1276 <code class="structfield">last_archived_wal</code> <code class="type">text</code>
1279 Name of the WAL file most recently successfully archived
1280 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1281 <code class="structfield">last_archived_time</code> <code class="type">timestamp with time zone</code>
1284 Time of the most recent successful archive operation
1285 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1286 <code class="structfield">failed_count</code> <code class="type">bigint</code>
1289 Number of failed attempts for archiving WAL files
1290 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1291 <code class="structfield">last_failed_wal</code> <code class="type">text</code>
1294 Name of the WAL file of the most recent failed archival operation
1295 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1296 <code class="structfield">last_failed_time</code> <code class="type">timestamp with time zone</code>
1299 Time of the most recent failed archival operation
1300 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1301 <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code>
1304 Time at which these statistics were last reset
1305 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
1306 Normally, WAL files are archived in order, oldest to newest, but that is
1307 not guaranteed, and does not hold under special circumstances like when
1308 promoting a standby or after crash recovery. Therefore it is not safe to
1309 assume that all files older than
1310 <code class="structfield">last_archived_wal</code> have also been successfully
1312 </p></div><div class="sect2" id="MONITORING-PG-STAT-IO-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.13. <code class="structname">pg_stat_io</code> <a href="#MONITORING-PG-STAT-IO-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.17.2" class="indexterm"></a><p>
1313 The <code class="structname">pg_stat_io</code> view will contain one row for each
1314 combination of backend type, target I/O object, and I/O context, showing
1315 cluster-wide I/O statistics. Combinations which do not make sense are
1318 Currently, I/O on relations (e.g. tables, indexes) and WAL activity are
1319 tracked. However, relation I/O which bypasses shared buffers
1320 (e.g. when moving a table from one tablespace to another) is currently
1322 </p><div class="table" id="PG-STAT-IO-VIEW"><p class="title"><strong>Table 27.23. <code class="structname">pg_stat_io</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_io View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry">
1323 <p class="column_definition">
1329 </th></tr></thead><tbody><tr><td class="catalog_table_entry">
1330 <p class="column_definition">
1331 <code class="structfield">backend_type</code> <code class="type">text</code>
1334 Type of backend (e.g. background worker, autovacuum worker). See <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" title="27.2.3. pg_stat_activity">
1335 <code class="structname">pg_stat_activity</code></a> for more information
1336 on <code class="varname">backend_type</code>s. Some
1337 <code class="varname">backend_type</code>s do not accumulate I/O operation
1338 statistics and will not be included in the view.
1340 </td></tr><tr><td class="catalog_table_entry">
1341 <p class="column_definition">
1342 <code class="structfield">object</code> <code class="type">text</code>
1345 Target object of an I/O operation. Possible values are:
1346 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
1347 <code class="literal">relation</code>: Permanent relations.
1348 </p></li><li class="listitem"><p>
1349 <code class="literal">temp relation</code>: Temporary relations.
1350 </p></li><li class="listitem"><p>
1351 <code class="literal">wal</code>: Write Ahead Logs.
1352 </p></li></ul></div><p>
1354 </td></tr><tr><td class="catalog_table_entry">
1355 <p class="column_definition">
1356 <code class="structfield">context</code> <code class="type">text</code>
1359 The context of an I/O operation. Possible values are:
1361 <div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
1362 <code class="literal">normal</code>: The default or standard
1363 <code class="varname">context</code> for a type of I/O operation. For
1364 example, by default, relation data is read into and written out from
1365 shared buffers. Thus, reads and writes of relation data to and from
1366 shared buffers are tracked in <code class="varname">context</code>
1367 <code class="literal">normal</code>.
1368 </p></li><li class="listitem"><p>
1369 <code class="literal">init</code>: I/O operations performed while creating the
1370 WAL segments are tracked in <code class="varname">context</code>
1371 <code class="literal">init</code>.
1372 </p></li><li class="listitem"><p>
1373 <code class="literal">vacuum</code>: I/O operations performed outside of shared
1374 buffers while vacuuming and analyzing permanent relations. Temporary
1375 table vacuums use the same local buffer pool as other temporary table
1376 I/O operations and are tracked in <code class="varname">context</code>
1377 <code class="literal">normal</code>.
1378 </p></li><li class="listitem"><p>
1379 <code class="literal">bulkread</code>: Certain large read I/O operations
1380 done outside of shared buffers, for example, a sequential scan of a
1382 </p></li><li class="listitem"><p>
1383 <code class="literal">bulkwrite</code>: Certain large write I/O operations
1384 done outside of shared buffers, such as <code class="command">COPY</code>.
1385 </p></li></ul></div>
1386 </td></tr><tr><td class="catalog_table_entry">
1387 <p class="column_definition">
1388 <code class="structfield">reads</code> <code class="type">bigint</code>
1391 Number of read operations.
1393 </td></tr><tr><td class="catalog_table_entry">
1394 <p class="column_definition">
1395 <code class="structfield">read_bytes</code> <code class="type">numeric</code>
1398 The total size of read operations in bytes.
1400 </td></tr><tr><td class="catalog_table_entry">
1401 <p class="column_definition">
1402 <code class="structfield">read_time</code> <code class="type">double precision</code>
1405 Time spent waiting for read operations in milliseconds (if
1406 <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled and
1407 <code class="varname">object</code> is not <code class="literal">wal</code>,
1408 or if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-WAL-IO-TIMING">track_wal_io_timing</a> is enabled
1409 and <code class="varname">object</code> is <code class="literal">wal</code>,
1412 </td></tr><tr><td class="catalog_table_entry">
1413 <p class="column_definition">
1414 <code class="structfield">writes</code> <code class="type">bigint</code>
1417 Number of write operations.
1419 </td></tr><tr><td class="catalog_table_entry">
1420 <p class="column_definition">
1421 <code class="structfield">write_bytes</code> <code class="type">numeric</code>
1424 The total size of write operations in bytes.
1426 </td></tr><tr><td class="catalog_table_entry">
1427 <p class="column_definition">
1428 <code class="structfield">write_time</code> <code class="type">double precision</code>
1431 Time spent waiting for write operations in milliseconds (if
1432 <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled and
1433 <code class="varname">object</code> is not <code class="literal">wal</code>,
1434 or if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-WAL-IO-TIMING">track_wal_io_timing</a> is enabled
1435 and <code class="varname">object</code> is <code class="literal">wal</code>,
1438 </td></tr><tr><td class="catalog_table_entry">
1439 <p class="column_definition">
1440 <code class="structfield">writebacks</code> <code class="type">bigint</code>
1443 Number of units of size <code class="symbol">BLCKSZ</code> (typically 8kB) which
1444 the process requested the kernel write out to permanent storage.
1446 </td></tr><tr><td class="catalog_table_entry">
1447 <p class="column_definition">
1448 <code class="structfield">writeback_time</code> <code class="type">double precision</code>
1451 Time spent waiting for writeback operations in milliseconds (if
1452 <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled, otherwise zero). This
1453 includes the time spent queueing write-out requests and, potentially,
1454 the time spent to write out the dirty data.
1456 </td></tr><tr><td class="catalog_table_entry">
1457 <p class="column_definition">
1458 <code class="structfield">extends</code> <code class="type">bigint</code>
1461 Number of relation extend operations.
1463 </td></tr><tr><td class="catalog_table_entry">
1464 <p class="column_definition">
1465 <code class="structfield">extend_bytes</code> <code class="type">numeric</code>
1468 The total size of relation extend operations in bytes.
1470 </td></tr><tr><td class="catalog_table_entry">
1471 <p class="column_definition">
1472 <code class="structfield">extend_time</code> <code class="type">double precision</code>
1475 Time spent waiting for extend operations in milliseconds. (if
1476 <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled and
1477 <code class="varname">object</code> is not <code class="literal">wal</code>,
1478 or if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-WAL-IO-TIMING">track_wal_io_timing</a> is enabled
1479 and <code class="varname">object</code> is <code class="literal">wal</code>,
1482 </td></tr><tr><td class="catalog_table_entry">
1483 <p class="column_definition">
1484 <code class="structfield">hits</code> <code class="type">bigint</code>
1487 The number of times a desired block was found in a shared buffer.
1489 </td></tr><tr><td class="catalog_table_entry">
1490 <p class="column_definition">
1491 <code class="structfield">evictions</code> <code class="type">bigint</code>
1494 Number of times a block has been written out from a shared or local
1495 buffer in order to make it available for another use.
1498 In <code class="varname">context</code> <code class="literal">normal</code>, this counts
1499 the number of times a block was evicted from a buffer and replaced with
1500 another block. In <code class="varname">context</code>s
1501 <code class="literal">bulkwrite</code>, <code class="literal">bulkread</code>, and
1502 <code class="literal">vacuum</code>, this counts the number of times a block was
1503 evicted from shared buffers in order to add the shared buffer to a
1504 separate, size-limited ring buffer for use in a bulk I/O operation.
1506 </td></tr><tr><td class="catalog_table_entry">
1507 <p class="column_definition">
1508 <code class="structfield">reuses</code> <code class="type">bigint</code>
1511 The number of times an existing buffer in a size-limited ring buffer
1512 outside of shared buffers was reused as part of an I/O operation in the
1513 <code class="literal">bulkread</code>, <code class="literal">bulkwrite</code>, or
1514 <code class="literal">vacuum</code> <code class="varname">context</code>s.
1516 </td></tr><tr><td class="catalog_table_entry">
1517 <p class="column_definition">
1518 <code class="structfield">fsyncs</code> <code class="type">bigint</code>
1521 Number of <code class="literal">fsync</code> calls. These are only tracked in
1522 <code class="varname">context</code> <code class="literal">normal</code>.
1524 </td></tr><tr><td class="catalog_table_entry">
1525 <p class="column_definition">
1526 <code class="structfield">fsync_time</code> <code class="type">double precision</code>
1529 Time spent waiting for fsync operations in milliseconds (if
1530 <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled and
1531 <code class="varname">object</code> is not <code class="literal">wal</code>,
1532 or if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-WAL-IO-TIMING">track_wal_io_timing</a> is enabled
1533 and <code class="varname">object</code> is <code class="literal">wal</code>,
1536 </td></tr><tr><td class="catalog_table_entry">
1537 <p class="column_definition">
1538 <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code>
1541 Time at which these statistics were last reset.
1543 </td></tr></tbody></table></div></div><br class="table-break" /><p>
1544 Some backend types never perform I/O operations on some I/O objects and/or
1545 in some I/O contexts. These rows are omitted from the view. For example, the
1546 checkpointer does not checkpoint temporary tables, so there will be no rows
1547 for <code class="varname">backend_type</code> <code class="literal">checkpointer</code> and
1548 <code class="varname">object</code> <code class="literal">temp relation</code>.
1550 In addition, some I/O operations will never be performed either by certain
1551 backend types or on certain I/O objects and/or in certain I/O contexts.
1552 These cells will be NULL. For example, temporary tables are not
1553 <code class="literal">fsync</code>ed, so <code class="varname">fsyncs</code> will be NULL for
1554 <code class="varname">object</code> <code class="literal">temp relation</code>. Also, the
1555 background writer does not perform reads, so <code class="varname">reads</code> will
1556 be NULL in rows for <code class="varname">backend_type</code> <code class="literal">background
1559 For the <code class="varname">object</code> <code class="literal">wal</code>,
1560 <code class="varname">fsyncs</code> and <code class="varname">fsync_time</code> track the
1561 fsync activity of WAL files done in <code class="function">issue_xlog_fsync</code>.
1562 <code class="varname">writes</code> and <code class="varname">write_time</code>
1563 track the write activity of WAL files done in
1564 <code class="function">XLogWrite</code>.
1565 See <a class="xref" href="wal-configuration.html" title="28.5. WAL Configuration">Section 28.5</a> for more information.
1567 <code class="structname">pg_stat_io</code> can be used to inform database tuning.
1569 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
1570 A high <code class="varname">evictions</code> count can indicate that shared
1571 buffers should be increased.
1572 </p></li><li class="listitem"><p>
1573 Client backends rely on the checkpointer to ensure data is persisted to
1574 permanent storage. Large numbers of <code class="varname">fsyncs</code> by
1575 <code class="literal">client backend</code>s could indicate a misconfiguration of
1576 shared buffers or of the checkpointer. More information on configuring
1577 the checkpointer can be found in <a class="xref" href="wal-configuration.html" title="28.5. WAL Configuration">Section 28.5</a>.
1578 </p></li><li class="listitem"><p>
1579 Normally, client backends should be able to rely on auxiliary processes
1580 like the checkpointer and the background writer to write out dirty data
1581 as much as possible. Large numbers of writes by client backends could
1582 indicate a misconfiguration of shared buffers or of the checkpointer.
1583 More information on configuring the checkpointer can be found in <a class="xref" href="wal-configuration.html" title="28.5. WAL Configuration">Section 28.5</a>.
1584 </p></li></ul></div><p>
1585 </p><div class="note"><h3 class="title">Note</h3><p>
1586 Columns tracking I/O wait time will only be non-zero when
1587 <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled. The user should be
1588 careful when referencing these columns in combination with their
1589 corresponding I/O operations in case <code class="varname">track_io_timing</code>
1590 was not enabled for the entire time since the last stats reset.
1591 </p></div></div><div class="sect2" id="MONITORING-PG-STAT-BGWRITER-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.14. <code class="structname">pg_stat_bgwriter</code> <a href="#MONITORING-PG-STAT-BGWRITER-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.18.2" class="indexterm"></a><p>
1592 The <code class="structname">pg_stat_bgwriter</code> view will always have a
1593 single row, containing data about the background writer of the cluster.
1594 </p><div class="table" id="PG-STAT-BGWRITER-VIEW"><p class="title"><strong>Table 27.24. <code class="structname">pg_stat_bgwriter</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_bgwriter View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
1599 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
1600 <code class="structfield">buffers_clean</code> <code class="type">bigint</code>
1603 Number of buffers written by the background writer
1604 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1605 <code class="structfield">maxwritten_clean</code> <code class="type">bigint</code>
1608 Number of times the background writer stopped a cleaning
1609 scan because it had written too many buffers
1610 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1611 <code class="structfield">buffers_alloc</code> <code class="type">bigint</code>
1614 Number of buffers allocated
1615 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1616 <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code>
1619 Time at which these statistics were last reset
1620 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-CHECKPOINTER-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.15. <code class="structname">pg_stat_checkpointer</code> <a href="#MONITORING-PG-STAT-CHECKPOINTER-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.19.2" class="indexterm"></a><p>
1621 The <code class="structname">pg_stat_checkpointer</code> view will always have a
1622 single row, containing data about the checkpointer process of the cluster.
1623 </p><div class="table" id="PG-STAT-CHECKPOINTER-VIEW"><p class="title"><strong>Table 27.25. <code class="structname">pg_stat_checkpointer</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_checkpointer View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
1628 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
1629 <code class="structfield">num_timed</code> <code class="type">bigint</code>
1632 Number of scheduled checkpoints due to timeout
1633 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1634 <code class="structfield">num_requested</code> <code class="type">bigint</code>
1637 Number of requested checkpoints
1638 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1639 <code class="structfield">num_done</code> <code class="type">bigint</code>
1642 Number of checkpoints that have been performed
1643 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1644 <code class="structfield">restartpoints_timed</code> <code class="type">bigint</code>
1647 Number of scheduled restartpoints due to timeout or after a failed attempt to perform it
1648 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1649 <code class="structfield">restartpoints_req</code> <code class="type">bigint</code>
1652 Number of requested restartpoints
1653 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1654 <code class="structfield">restartpoints_done</code> <code class="type">bigint</code>
1657 Number of restartpoints that have been performed
1658 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1659 <code class="structfield">write_time</code> <code class="type">double precision</code>
1662 Total amount of time that has been spent in the portion of
1663 processing checkpoints and restartpoints where files are written to disk,
1665 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1666 <code class="structfield">sync_time</code> <code class="type">double precision</code>
1669 Total amount of time that has been spent in the portion of
1670 processing checkpoints and restartpoints where files are synchronized to
1671 disk, in milliseconds
1672 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1673 <code class="structfield">buffers_written</code> <code class="type">bigint</code>
1676 Number of shared buffers written during checkpoints and restartpoints
1677 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1678 <code class="structfield">slru_written</code> <code class="type">bigint</code>
1681 Number of SLRU buffers written during checkpoints and restartpoints
1682 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1683 <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code>
1686 Time at which these statistics were last reset
1687 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
1688 Checkpoints may be skipped if the server has been idle since the last one.
1689 <code class="structfield">num_timed</code> and
1690 <code class="structfield">num_requested</code> count both completed and skipped
1691 checkpoints, while <code class="structfield">num_done</code> tracks only
1692 the completed ones. Similarly, restartpoints may be skipped
1693 if the last replayed checkpoint record is already the last restartpoint.
1694 <code class="structfield">restartpoints_timed</code> and
1695 <code class="structfield">restartpoints_req</code> count both completed and
1696 skipped restartpoints, while <code class="structfield">restartpoints_done</code>
1697 tracks only the completed ones.
1698 </p></div><div class="sect2" id="MONITORING-PG-STAT-WAL-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.16. <code class="structname">pg_stat_wal</code> <a href="#MONITORING-PG-STAT-WAL-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.20.2" class="indexterm"></a><p>
1699 The <code class="structname">pg_stat_wal</code> view will always have a
1700 single row, containing data about WAL activity of the cluster.
1701 </p><div class="table" id="PG-STAT-WAL-VIEW"><p class="title"><strong>Table 27.26. <code class="structname">pg_stat_wal</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_wal View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
1706 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
1707 <code class="structfield">wal_records</code> <code class="type">bigint</code>
1710 Total number of WAL records generated
1711 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1712 <code class="structfield">wal_fpi</code> <code class="type">bigint</code>
1715 Total number of WAL full page images generated
1716 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1717 <code class="structfield">wal_bytes</code> <code class="type">numeric</code>
1720 Total amount of WAL generated in bytes
1721 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1722 <code class="structfield">wal_buffers_full</code> <code class="type">bigint</code>
1725 Number of times WAL data was written to disk because WAL buffers became full
1726 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1727 <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code>
1730 Time at which these statistics were last reset
1731 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-DATABASE-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.17. <code class="structname">pg_stat_database</code> <a href="#MONITORING-PG-STAT-DATABASE-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.21.2" class="indexterm"></a><p>
1732 The <code class="structname">pg_stat_database</code> view will contain one row
1733 for each database in the cluster, plus one for shared objects, showing
1734 database-wide statistics.
1735 </p><div class="table" id="PG-STAT-DATABASE-VIEW"><p class="title"><strong>Table 27.27. <code class="structname">pg_stat_database</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_database View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
1740 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
1741 <code class="structfield">datid</code> <code class="type">oid</code>
1744 OID of this database, or 0 for objects belonging to a shared
1746 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1747 <code class="structfield">datname</code> <code class="type">name</code>
1750 Name of this database, or <code class="literal">NULL</code> for shared
1752 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1753 <code class="structfield">numbackends</code> <code class="type">integer</code>
1756 Number of backends currently connected to this database, or
1757 <code class="literal">NULL</code> for shared objects. This is the only column
1758 in this view that returns a value reflecting current state; all other
1759 columns return the accumulated values since the last reset.
1760 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1761 <code class="structfield">xact_commit</code> <code class="type">bigint</code>
1764 Number of transactions in this database that have been
1766 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1767 <code class="structfield">xact_rollback</code> <code class="type">bigint</code>
1770 Number of transactions in this database that have been
1772 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1773 <code class="structfield">blks_read</code> <code class="type">bigint</code>
1776 Number of disk blocks read in this database
1777 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1778 <code class="structfield">blks_hit</code> <code class="type">bigint</code>
1781 Number of times disk blocks were found already in the buffer
1782 cache, so that a read was not necessary (this only includes hits in the
1783 PostgreSQL buffer cache, not the operating system's file system cache)
1784 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1785 <code class="structfield">tup_returned</code> <code class="type">bigint</code>
1788 Number of live rows fetched by sequential scans and index entries returned by index scans in this database
1789 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1790 <code class="structfield">tup_fetched</code> <code class="type">bigint</code>
1793 Number of live rows fetched by index scans in this database
1794 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1795 <code class="structfield">tup_inserted</code> <code class="type">bigint</code>
1798 Number of rows inserted by queries in this database
1799 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1800 <code class="structfield">tup_updated</code> <code class="type">bigint</code>
1803 Number of rows updated by queries in this database
1804 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1805 <code class="structfield">tup_deleted</code> <code class="type">bigint</code>
1808 Number of rows deleted by queries in this database
1809 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1810 <code class="structfield">conflicts</code> <code class="type">bigint</code>
1813 Number of queries canceled due to conflicts with recovery
1814 in this database. (Conflicts occur only on standby servers; see
1815 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-DATABASE-CONFLICTS-VIEW" title="27.2.18. pg_stat_database_conflicts">
1816 <code class="structname">pg_stat_database_conflicts</code></a> for details.)
1817 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1818 <code class="structfield">temp_files</code> <code class="type">bigint</code>
1821 Number of temporary files created by queries in this database.
1822 All temporary files are counted, regardless of why the temporary file
1823 was created (e.g., sorting or hashing), and regardless of the
1824 <a class="xref" href="runtime-config-logging.html#GUC-LOG-TEMP-FILES">log_temp_files</a> setting.
1825 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1826 <code class="structfield">temp_bytes</code> <code class="type">bigint</code>
1829 Total amount of data written to temporary files by queries in
1830 this database. All temporary files are counted, regardless of why
1831 the temporary file was created, and
1832 regardless of the <a class="xref" href="runtime-config-logging.html#GUC-LOG-TEMP-FILES">log_temp_files</a> setting.
1833 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1834 <code class="structfield">deadlocks</code> <code class="type">bigint</code>
1837 Number of deadlocks detected in this database
1838 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1839 <code class="structfield">checksum_failures</code> <code class="type">bigint</code>
1842 Number of data page checksum failures detected in this
1843 database (or on a shared object), or NULL if data checksums are
1845 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1846 <code class="structfield">checksum_last_failure</code> <code class="type">timestamp with time zone</code>
1849 Time at which the last data page checksum failure was detected in
1850 this database (or on a shared object), or NULL if data checksums are
1852 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1853 <code class="structfield">blk_read_time</code> <code class="type">double precision</code>
1856 Time spent reading data file blocks by backends in this database,
1857 in milliseconds (if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled,
1859 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1860 <code class="structfield">blk_write_time</code> <code class="type">double precision</code>
1863 Time spent writing data file blocks by backends in this database,
1864 in milliseconds (if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled,
1866 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1867 <code class="structfield">session_time</code> <code class="type">double precision</code>
1870 Time spent by database sessions in this database, in milliseconds
1871 (note that statistics are only updated when the state of a session
1872 changes, so if sessions have been idle for a long time, this idle time
1874 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1875 <code class="structfield">active_time</code> <code class="type">double precision</code>
1878 Time spent executing SQL statements in this database, in milliseconds
1879 (this corresponds to the states <code class="literal">active</code> and
1880 <code class="literal">fastpath function call</code> in
1881 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" title="27.2.3. pg_stat_activity">
1882 <code class="structname">pg_stat_activity</code></a>)
1883 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1884 <code class="structfield">idle_in_transaction_time</code> <code class="type">double precision</code>
1887 Time spent idling while in a transaction in this database, in milliseconds
1888 (this corresponds to the states <code class="literal">idle in transaction</code> and
1889 <code class="literal">idle in transaction (aborted)</code> in
1890 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" title="27.2.3. pg_stat_activity">
1891 <code class="structname">pg_stat_activity</code></a>)
1892 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1893 <code class="structfield">sessions</code> <code class="type">bigint</code>
1896 Total number of sessions established to this database
1897 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1898 <code class="structfield">sessions_abandoned</code> <code class="type">bigint</code>
1901 Number of database sessions to this database that were terminated
1902 because connection to the client was lost
1903 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1904 <code class="structfield">sessions_fatal</code> <code class="type">bigint</code>
1907 Number of database sessions to this database that were terminated
1909 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1910 <code class="structfield">sessions_killed</code> <code class="type">bigint</code>
1913 Number of database sessions to this database that were terminated
1914 by operator intervention
1915 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1916 <code class="structfield">parallel_workers_to_launch</code> <code class="type">bigint</code>
1919 Number of parallel workers planned to be launched by queries on this database
1920 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1921 <code class="structfield">parallel_workers_launched</code> <code class="type">bigint</code>
1924 Number of parallel workers launched by queries on this database
1925 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1926 <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code>
1929 Time at which these statistics were last reset
1930 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-DATABASE-CONFLICTS-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.18. <code class="structname">pg_stat_database_conflicts</code> <a href="#MONITORING-PG-STAT-DATABASE-CONFLICTS-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.22.2" class="indexterm"></a><p>
1931 The <code class="structname">pg_stat_database_conflicts</code> view will contain
1932 one row per database, showing database-wide statistics about
1933 query cancels occurring due to conflicts with recovery on standby servers.
1934 This view will only contain information on standby servers, since
1935 conflicts do not occur on primary servers.
1936 </p><div class="table" id="PG-STAT-DATABASE-CONFLICTS-VIEW"><p class="title"><strong>Table 27.28. <code class="structname">pg_stat_database_conflicts</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_database_conflicts View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
1941 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
1942 <code class="structfield">datid</code> <code class="type">oid</code>
1946 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1947 <code class="structfield">datname</code> <code class="type">name</code>
1950 Name of this database
1951 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1952 <code class="structfield">confl_tablespace</code> <code class="type">bigint</code>
1955 Number of queries in this database that have been canceled due to
1957 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1958 <code class="structfield">confl_lock</code> <code class="type">bigint</code>
1961 Number of queries in this database that have been canceled due to
1963 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1964 <code class="structfield">confl_snapshot</code> <code class="type">bigint</code>
1967 Number of queries in this database that have been canceled due to
1969 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1970 <code class="structfield">confl_bufferpin</code> <code class="type">bigint</code>
1973 Number of queries in this database that have been canceled due to
1975 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1976 <code class="structfield">confl_deadlock</code> <code class="type">bigint</code>
1979 Number of queries in this database that have been canceled due to
1981 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
1982 <code class="structfield">confl_active_logicalslot</code> <code class="type">bigint</code>
1985 Number of uses of logical slots in this database that have been
1986 canceled due to old snapshots or too low a <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a>
1988 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-ALL-TABLES-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.19. <code class="structname">pg_stat_all_tables</code> <a href="#MONITORING-PG-STAT-ALL-TABLES-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.23.2" class="indexterm"></a><p>
1989 The <code class="structname">pg_stat_all_tables</code> view will contain
1990 one row for each table in the current database (including TOAST
1991 tables), showing statistics about accesses to that specific table. The
1992 <code class="structname">pg_stat_user_tables</code> and
1993 <code class="structname">pg_stat_sys_tables</code> views
1994 contain the same information,
1995 but filtered to only show user and system tables respectively.
1996 </p><div class="table" id="PG-STAT-ALL-TABLES-VIEW"><p class="title"><strong>Table 27.29. <code class="structname">pg_stat_all_tables</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_all_tables View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
2001 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
2002 <code class="structfield">relid</code> <code class="type">oid</code>
2006 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2007 <code class="structfield">schemaname</code> <code class="type">name</code>
2010 Name of the schema that this table is in
2011 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2012 <code class="structfield">relname</code> <code class="type">name</code>
2016 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2017 <code class="structfield">seq_scan</code> <code class="type">bigint</code>
2020 Number of sequential scans initiated on this table
2021 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2022 <code class="structfield">last_seq_scan</code> <code class="type">timestamp with time zone</code>
2025 The time of the last sequential scan on this table, based on the
2026 most recent transaction stop time
2027 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2028 <code class="structfield">seq_tup_read</code> <code class="type">bigint</code>
2031 Number of live rows fetched by sequential scans
2032 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2033 <code class="structfield">idx_scan</code> <code class="type">bigint</code>
2036 Number of index scans initiated on this table
2037 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2038 <code class="structfield">last_idx_scan</code> <code class="type">timestamp with time zone</code>
2041 The time of the last index scan on this table, based on the
2042 most recent transaction stop time
2043 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2044 <code class="structfield">idx_tup_fetch</code> <code class="type">bigint</code>
2047 Number of live rows fetched by index scans
2048 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2049 <code class="structfield">n_tup_ins</code> <code class="type">bigint</code>
2052 Total number of rows inserted
2053 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2054 <code class="structfield">n_tup_upd</code> <code class="type">bigint</code>
2057 Total number of rows updated. (This includes row updates
2058 counted in <code class="structfield">n_tup_hot_upd</code> and
2059 <code class="structfield">n_tup_newpage_upd</code>, and remaining
2060 non-<acronym class="acronym">HOT</acronym> updates.)
2061 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2062 <code class="structfield">n_tup_del</code> <code class="type">bigint</code>
2065 Total number of rows deleted
2066 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2067 <code class="structfield">n_tup_hot_upd</code> <code class="type">bigint</code>
2070 Number of rows <a class="link" href="storage-hot.html" title="66.7. Heap-Only Tuples (HOT)">HOT updated</a>.
2071 These are updates where no successor versions are required in
2073 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2074 <code class="structfield">n_tup_newpage_upd</code> <code class="type">bigint</code>
2077 Number of rows updated where the successor version goes onto a
2078 <span class="emphasis"><em>new</em></span> heap page, leaving behind an original
2080 <a class="link" href="storage-page-layout.html#STORAGE-TUPLE-LAYOUT" title="66.6.1. Table Row Layout"><code class="structfield">t_ctid</code>
2081 field</a> that points to a different heap page. These are
2082 always non-<acronym class="acronym">HOT</acronym> updates.
2083 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2084 <code class="structfield">n_live_tup</code> <code class="type">bigint</code>
2087 Estimated number of live rows
2088 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2089 <code class="structfield">n_dead_tup</code> <code class="type">bigint</code>
2092 Estimated number of dead rows
2093 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2094 <code class="structfield">n_mod_since_analyze</code> <code class="type">bigint</code>
2097 Estimated number of rows modified since this table was last analyzed
2098 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2099 <code class="structfield">n_ins_since_vacuum</code> <code class="type">bigint</code>
2102 Estimated number of rows inserted since this table was last vacuumed
2103 (not counting <code class="command">VACUUM FULL</code>)
2104 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2105 <code class="structfield">last_vacuum</code> <code class="type">timestamp with time zone</code>
2108 Last time at which this table was manually vacuumed
2109 (not counting <code class="command">VACUUM FULL</code>)
2110 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2111 <code class="structfield">last_autovacuum</code> <code class="type">timestamp with time zone</code>
2114 Last time at which this table was vacuumed by the autovacuum
2116 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2117 <code class="structfield">last_analyze</code> <code class="type">timestamp with time zone</code>
2120 Last time at which this table was manually analyzed
2121 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2122 <code class="structfield">last_autoanalyze</code> <code class="type">timestamp with time zone</code>
2125 Last time at which this table was analyzed by the autovacuum
2127 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2128 <code class="structfield">vacuum_count</code> <code class="type">bigint</code>
2131 Number of times this table has been manually vacuumed
2132 (not counting <code class="command">VACUUM FULL</code>)
2133 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2134 <code class="structfield">autovacuum_count</code> <code class="type">bigint</code>
2137 Number of times this table has been vacuumed by the autovacuum
2139 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2140 <code class="structfield">analyze_count</code> <code class="type">bigint</code>
2143 Number of times this table has been manually analyzed
2144 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2145 <code class="structfield">autoanalyze_count</code> <code class="type">bigint</code>
2148 Number of times this table has been analyzed by the autovacuum
2150 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2151 <code class="structfield">total_vacuum_time</code> <code class="type">double precision</code>
2154 Total time this table has been manually vacuumed, in milliseconds
2155 (not counting <code class="command">VACUUM FULL</code>).
2156 (This includes the time spent sleeping due to cost-based delays.)
2157 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2158 <code class="structfield">total_autovacuum_time</code> <code class="type">double precision</code>
2161 Total time this table has been vacuumed by the autovacuum daemon,
2162 in milliseconds. (This includes the time spent sleeping due to
2164 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2165 <code class="structfield">total_analyze_time</code> <code class="type">double precision</code>
2168 Total time this table has been manually analyzed, in milliseconds.
2169 (This includes the time spent sleeping due to cost-based delays.)
2170 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2171 <code class="structfield">total_autoanalyze_time</code> <code class="type">double precision</code>
2174 Total time this table has been analyzed by the autovacuum daemon,
2175 in milliseconds. (This includes the time spent sleeping due to
2177 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-ALL-INDEXES-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.20. <code class="structname">pg_stat_all_indexes</code> <a href="#MONITORING-PG-STAT-ALL-INDEXES-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.24.2" class="indexterm"></a><p>
2178 The <code class="structname">pg_stat_all_indexes</code> view will contain
2179 one row for each index in the current database,
2180 showing statistics about accesses to that specific index. The
2181 <code class="structname">pg_stat_user_indexes</code> and
2182 <code class="structname">pg_stat_sys_indexes</code> views
2183 contain the same information,
2184 but filtered to only show user and system indexes respectively.
2185 </p><div class="table" id="PG-STAT-ALL-INDEXES-VIEW"><p class="title"><strong>Table 27.30. <code class="structname">pg_stat_all_indexes</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_all_indexes View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
2190 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
2191 <code class="structfield">relid</code> <code class="type">oid</code>
2194 OID of the table for this index
2195 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2196 <code class="structfield">indexrelid</code> <code class="type">oid</code>
2200 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2201 <code class="structfield">schemaname</code> <code class="type">name</code>
2204 Name of the schema this index is in
2205 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2206 <code class="structfield">relname</code> <code class="type">name</code>
2209 Name of the table for this index
2210 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2211 <code class="structfield">indexrelname</code> <code class="type">name</code>
2215 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2216 <code class="structfield">idx_scan</code> <code class="type">bigint</code>
2219 Number of index scans initiated on this index
2220 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2221 <code class="structfield">last_idx_scan</code> <code class="type">timestamp with time zone</code>
2224 The time of the last scan on this index, based on the
2225 most recent transaction stop time
2226 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2227 <code class="structfield">idx_tup_read</code> <code class="type">bigint</code>
2230 Number of index entries returned by scans on this index
2231 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2232 <code class="structfield">idx_tup_fetch</code> <code class="type">bigint</code>
2235 Number of live table rows fetched by simple index scans using this
2237 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
2238 Indexes can be used by simple index scans, <span class="quote">“<span class="quote">bitmap</span>”</span> index scans,
2239 and the optimizer. In a bitmap scan
2240 the output of several indexes can be combined via AND or OR rules,
2241 so it is difficult to associate individual heap row fetches
2242 with specific indexes when a bitmap scan is used. Therefore, a bitmap
2244 <code class="structname">pg_stat_all_indexes</code>.<code class="structfield">idx_tup_read</code>
2245 count(s) for the index(es) it uses, and it increments the
2246 <code class="structname">pg_stat_all_tables</code>.<code class="structfield">idx_tup_fetch</code>
2247 count for the table, but it does not affect
2248 <code class="structname">pg_stat_all_indexes</code>.<code class="structfield">idx_tup_fetch</code>.
2249 The optimizer also accesses indexes to check for supplied constants
2250 whose values are outside the recorded range of the optimizer statistics
2251 because the optimizer statistics might be stale.
2252 </p><div class="note"><h3 class="title">Note</h3><p>
2253 The <code class="structfield">idx_tup_read</code> and <code class="structfield">idx_tup_fetch</code> counts
2254 can be different even without any use of bitmap scans,
2255 because <code class="structfield">idx_tup_read</code> counts
2256 index entries retrieved from the index while <code class="structfield">idx_tup_fetch</code>
2257 counts live rows fetched from the table. The latter will be less if any
2258 dead or not-yet-committed rows are fetched using the index, or if any
2259 heap fetches are avoided by means of an index-only scan.
2260 </p></div><div class="note"><h3 class="title">Note</h3><p>
2261 Index scans may sometimes perform multiple index searches per execution.
2262 Each index search increments <code class="structname">pg_stat_all_indexes</code>.<code class="structfield">idx_scan</code>,
2263 so it's possible for the count of index scans to significantly exceed the
2264 total number of index scan executor node executions.
2266 This can happen with queries that use certain <acronym class="acronym">SQL</acronym>
2267 constructs to search for rows matching any value out of a list or array of
2268 multiple scalar values (see <a class="xref" href="functions-comparisons.html" title="9.25. Row and Array Comparisons">Section 9.25</a>). It
2269 can also happen to queries with a
2270 <code class="literal"><em class="replaceable"><code>column_name</code></em> =
2271 <em class="replaceable"><code>value1</code></em> OR
2272 <em class="replaceable"><code>column_name</code></em> =
2273 <em class="replaceable"><code>value2</code></em> ...</code> construct, though only
2274 when the optimizer transforms the construct into an equivalent
2275 multi-valued array representation. Similarly, when B-tree index scans use
2276 the skip scan optimization, an index search is performed each time the
2277 scan is repositioned to the next index leaf page that might have matching
2278 tuples (see <a class="xref" href="indexes-multicolumn.html" title="11.3. Multicolumn Indexes">Section 11.3</a>).
2279 </p></div><div class="tip"><h3 class="title">Tip</h3><p>
2280 <code class="command">EXPLAIN ANALYZE</code> outputs the total number of index
2281 searches performed by each index scan node. See
2282 <a class="xref" href="using-explain.html#USING-EXPLAIN-ANALYZE" title="14.1.2. EXPLAIN ANALYZE">Section 14.1.2</a> for an example demonstrating how
2284 </p></div></div><div class="sect2" id="MONITORING-PG-STATIO-ALL-TABLES-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.21. <code class="structname">pg_statio_all_tables</code> <a href="#MONITORING-PG-STATIO-ALL-TABLES-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.25.2" class="indexterm"></a><p>
2285 The <code class="structname">pg_statio_all_tables</code> view will contain
2286 one row for each table in the current database (including TOAST
2287 tables), showing statistics about I/O on that specific table. The
2288 <code class="structname">pg_statio_user_tables</code> and
2289 <code class="structname">pg_statio_sys_tables</code> views
2290 contain the same information,
2291 but filtered to only show user and system tables respectively.
2292 </p><div class="table" id="PG-STATIO-ALL-TABLES-VIEW"><p class="title"><strong>Table 27.31. <code class="structname">pg_statio_all_tables</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_statio_all_tables View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
2297 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
2298 <code class="structfield">relid</code> <code class="type">oid</code>
2302 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2303 <code class="structfield">schemaname</code> <code class="type">name</code>
2306 Name of the schema that this table is in
2307 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2308 <code class="structfield">relname</code> <code class="type">name</code>
2312 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2313 <code class="structfield">heap_blks_read</code> <code class="type">bigint</code>
2316 Number of disk blocks read from this table
2317 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2318 <code class="structfield">heap_blks_hit</code> <code class="type">bigint</code>
2321 Number of buffer hits in this table
2322 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2323 <code class="structfield">idx_blks_read</code> <code class="type">bigint</code>
2326 Number of disk blocks read from all indexes on this table
2327 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2328 <code class="structfield">idx_blks_hit</code> <code class="type">bigint</code>
2331 Number of buffer hits in all indexes on this table
2332 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2333 <code class="structfield">toast_blks_read</code> <code class="type">bigint</code>
2336 Number of disk blocks read from this table's TOAST table (if any)
2337 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2338 <code class="structfield">toast_blks_hit</code> <code class="type">bigint</code>
2341 Number of buffer hits in this table's TOAST table (if any)
2342 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2343 <code class="structfield">tidx_blks_read</code> <code class="type">bigint</code>
2346 Number of disk blocks read from this table's TOAST table indexes (if any)
2347 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2348 <code class="structfield">tidx_blks_hit</code> <code class="type">bigint</code>
2351 Number of buffer hits in this table's TOAST table indexes (if any)
2352 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STATIO-ALL-INDEXES-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.22. <code class="structname">pg_statio_all_indexes</code> <a href="#MONITORING-PG-STATIO-ALL-INDEXES-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.26.2" class="indexterm"></a><p>
2353 The <code class="structname">pg_statio_all_indexes</code> view will contain
2354 one row for each index in the current database,
2355 showing statistics about I/O on that specific index. The
2356 <code class="structname">pg_statio_user_indexes</code> and
2357 <code class="structname">pg_statio_sys_indexes</code> views
2358 contain the same information,
2359 but filtered to only show user and system indexes respectively.
2360 </p><div class="table" id="PG-STATIO-ALL-INDEXES-VIEW"><p class="title"><strong>Table 27.32. <code class="structname">pg_statio_all_indexes</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_statio_all_indexes View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
2365 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
2366 <code class="structfield">relid</code> <code class="type">oid</code>
2369 OID of the table for this index
2370 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2371 <code class="structfield">indexrelid</code> <code class="type">oid</code>
2375 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2376 <code class="structfield">schemaname</code> <code class="type">name</code>
2379 Name of the schema this index is in
2380 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2381 <code class="structfield">relname</code> <code class="type">name</code>
2384 Name of the table for this index
2385 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2386 <code class="structfield">indexrelname</code> <code class="type">name</code>
2390 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2391 <code class="structfield">idx_blks_read</code> <code class="type">bigint</code>
2394 Number of disk blocks read from this index
2395 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2396 <code class="structfield">idx_blks_hit</code> <code class="type">bigint</code>
2399 Number of buffer hits in this index
2400 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STATIO-ALL-SEQUENCES-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.23. <code class="structname">pg_statio_all_sequences</code> <a href="#MONITORING-PG-STATIO-ALL-SEQUENCES-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.27.2" class="indexterm"></a><p>
2401 The <code class="structname">pg_statio_all_sequences</code> view will contain
2402 one row for each sequence in the current database,
2403 showing statistics about I/O on that specific sequence.
2404 </p><div class="table" id="PG-STATIO-ALL-SEQUENCES-VIEW"><p class="title"><strong>Table 27.33. <code class="structname">pg_statio_all_sequences</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_statio_all_sequences View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
2409 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
2410 <code class="structfield">relid</code> <code class="type">oid</code>
2414 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2415 <code class="structfield">schemaname</code> <code class="type">name</code>
2418 Name of the schema this sequence is in
2419 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2420 <code class="structfield">relname</code> <code class="type">name</code>
2423 Name of this sequence
2424 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2425 <code class="structfield">blks_read</code> <code class="type">bigint</code>
2428 Number of disk blocks read from this sequence
2429 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2430 <code class="structfield">blks_hit</code> <code class="type">bigint</code>
2433 Number of buffer hits in this sequence
2434 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-USER-FUNCTIONS-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.24. <code class="structname">pg_stat_user_functions</code> <a href="#MONITORING-PG-STAT-USER-FUNCTIONS-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.28.2" class="indexterm"></a><p>
2435 The <code class="structname">pg_stat_user_functions</code> view will contain
2436 one row for each tracked function, showing statistics about executions of
2437 that function. The <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-FUNCTIONS">track_functions</a> parameter
2438 controls exactly which functions are tracked.
2439 </p><div class="table" id="PG-STAT-USER-FUNCTIONS-VIEW"><p class="title"><strong>Table 27.34. <code class="structname">pg_stat_user_functions</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_user_functions View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
2444 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
2445 <code class="structfield">funcid</code> <code class="type">oid</code>
2449 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2450 <code class="structfield">schemaname</code> <code class="type">name</code>
2453 Name of the schema this function is in
2454 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2455 <code class="structfield">funcname</code> <code class="type">name</code>
2458 Name of this function
2459 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2460 <code class="structfield">calls</code> <code class="type">bigint</code>
2463 Number of times this function has been called
2464 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2465 <code class="structfield">total_time</code> <code class="type">double precision</code>
2468 Total time spent in this function and all other functions
2469 called by it, in milliseconds
2470 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2471 <code class="structfield">self_time</code> <code class="type">double precision</code>
2474 Total time spent in this function itself, not including
2475 other functions called by it, in milliseconds
2476 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-PG-STAT-SLRU-VIEW"><div class="titlepage"><div><div><h3 class="title">27.2.25. <code class="structname">pg_stat_slru</code> <a href="#MONITORING-PG-STAT-SLRU-VIEW" class="id_link">#</a></h3></div></div></div><a id="id-1.6.14.7.29.2" class="indexterm"></a><a id="id-1.6.14.7.29.3" class="indexterm"></a><p>
2477 <span class="productname">PostgreSQL</span> accesses certain on-disk information
2478 via <code class="literal">SLRU</code> (<em class="firstterm">simple least-recently-used</em>)
2480 The <code class="structname">pg_stat_slru</code> view will contain
2481 one row for each tracked SLRU cache, showing statistics about access
2484 For each <code class="literal">SLRU</code> cache that's part of the core server,
2485 there is a configuration parameter that controls its size, with the suffix
2486 <code class="literal">_buffers</code> appended.
2487 </p><div class="table" id="PG-STAT-SLRU-VIEW"><p class="title"><strong>Table 27.35. <code class="structname">pg_stat_slru</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_slru View" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
2492 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
2493 <code class="structfield">name</code> <code class="type">text</code>
2497 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2498 <code class="structfield">blks_zeroed</code> <code class="type">bigint</code>
2501 Number of blocks zeroed during initializations
2502 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2503 <code class="structfield">blks_hit</code> <code class="type">bigint</code>
2506 Number of times disk blocks were found already in the SLRU,
2507 so that a read was not necessary (this only includes hits in the
2508 SLRU, not the operating system's file system cache)
2509 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2510 <code class="structfield">blks_read</code> <code class="type">bigint</code>
2513 Number of disk blocks read for this SLRU
2514 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2515 <code class="structfield">blks_written</code> <code class="type">bigint</code>
2518 Number of disk blocks written for this SLRU
2519 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2520 <code class="structfield">blks_exists</code> <code class="type">bigint</code>
2523 Number of blocks checked for existence for this SLRU
2524 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2525 <code class="structfield">flushes</code> <code class="type">bigint</code>
2528 Number of flushes of dirty data for this SLRU
2529 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2530 <code class="structfield">truncates</code> <code class="type">bigint</code>
2533 Number of truncates for this SLRU
2534 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
2535 <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code>
2538 Time at which these statistics were last reset
2539 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="MONITORING-STATS-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">27.2.26. Statistics Functions <a href="#MONITORING-STATS-FUNCTIONS" class="id_link">#</a></h3></div></div></div><p>
2540 Other ways of looking at the statistics can be set up by writing
2541 queries that use the same underlying statistics access functions used by
2542 the standard views shown above. For details such as the functions' names,
2543 consult the definitions of the standard views. (For example, in
2544 <span class="application">psql</span> you could issue <code class="literal">\d+ pg_stat_activity</code>.)
2545 The access functions for per-database statistics take a database OID as an
2546 argument to identify which database to report on.
2547 The per-table and per-index functions take a table or index OID.
2548 The functions for per-function statistics take a function OID.
2549 Note that only tables, indexes, and functions in the current database
2550 can be seen with these functions.
2552 Additional functions related to the cumulative statistics system are listed
2553 in <a class="xref" href="monitoring-stats.html#MONITORING-STATS-FUNCS-TABLE" title="Table 27.36. Additional Statistics Functions">Table 27.36</a>.
2554 </p><div class="table" id="MONITORING-STATS-FUNCS-TABLE"><p class="title"><strong>Table 27.36. Additional Statistics Functions</strong></p><div class="table-contents"><table class="table" summary="Additional Statistics Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
2559 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
2560 <code class="function">pg_backend_pid</code> ()
2561 → <code class="returnvalue">integer</code>
2564 Returns the process ID of the server process attached to the current
2566 </p></td></tr><tr><td id="PG-STAT-GET-BACKEND-IO" class="func_table_entry"><p class="func_signature">
2567 <a id="id-1.6.14.7.30.4.2.2.2.1.1.1" class="indexterm"></a>
2568 <code class="function">pg_stat_get_backend_io</code> ( <code class="type">integer</code> )
2569 → <code class="returnvalue">setof record</code>
2572 Returns I/O statistics about the backend with the specified
2573 process ID. The output fields are exactly the same as the ones in the
2574 <code class="structname">pg_stat_io</code> view.
2577 The function does not return I/O statistics for the checkpointer,
2578 the background writer, the startup process and the autovacuum launcher
2579 as they are already visible in the <code class="structname">pg_stat_io</code>
2580 view and there is only one of each.
2581 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2582 <a id="id-1.6.14.7.30.4.2.2.3.1.1.1" class="indexterm"></a>
2583 <code class="function">pg_stat_get_activity</code> ( <code class="type">integer</code> )
2584 → <code class="returnvalue">setof record</code>
2587 Returns a record of information about the backend with the specified
2588 process ID, or one record for each active backend in the system
2589 if <code class="literal">NULL</code> is specified. The fields returned are a
2590 subset of those in the <code class="structname">pg_stat_activity</code> view.
2591 </p></td></tr><tr><td id="PG-STAT-GET-BACKEND-WAL" class="func_table_entry"><p class="func_signature">
2592 <a id="id-1.6.14.7.30.4.2.2.4.1.1.1" class="indexterm"></a>
2593 <code class="function">pg_stat_get_backend_wal</code> ( <code class="type">integer</code> )
2594 → <code class="returnvalue">record</code>
2597 Returns WAL statistics about the backend with the specified
2598 process ID. The output fields are exactly the same as the ones in the
2599 <code class="structname">pg_stat_wal</code> view.
2602 The function does not return WAL statistics for the checkpointer,
2603 the background writer, the startup process and the autovacuum launcher.
2604 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2605 <a id="id-1.6.14.7.30.4.2.2.5.1.1.1" class="indexterm"></a>
2606 <code class="function">pg_stat_get_snapshot_timestamp</code> ()
2607 → <code class="returnvalue">timestamp with time zone</code>
2610 Returns the timestamp of the current statistics snapshot, or NULL if
2611 no statistics snapshot has been taken. A snapshot is taken the first
2612 time cumulative statistics are accessed in a transaction if
2613 <code class="varname">stats_fetch_consistency</code> is set to
2614 <code class="literal">snapshot</code>
2615 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2616 <a id="id-1.6.14.7.30.4.2.2.6.1.1.1" class="indexterm"></a>
2617 <code class="function">pg_stat_get_xact_blocks_fetched</code> ( <code class="type">oid</code> )
2618 → <code class="returnvalue">bigint</code>
2621 Returns the number of block read requests for table or index, in the
2622 current transaction. This number minus
2623 <code class="function">pg_stat_get_xact_blocks_hit</code> gives the number of
2624 kernel <code class="function">read()</code> calls; the number of actual
2625 physical reads is usually lower due to kernel-level buffering.
2626 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2627 <a id="id-1.6.14.7.30.4.2.2.7.1.1.1" class="indexterm"></a>
2628 <code class="function">pg_stat_get_xact_blocks_hit</code> ( <code class="type">oid</code> )
2629 → <code class="returnvalue">bigint</code>
2632 Returns the number of block read requests for table or index, in the
2633 current transaction, found in cache (not triggering kernel
2634 <code class="function">read()</code> calls).
2635 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2636 <a id="id-1.6.14.7.30.4.2.2.8.1.1.1" class="indexterm"></a>
2637 <code class="function">pg_stat_clear_snapshot</code> ()
2638 → <code class="returnvalue">void</code>
2641 Discards the current statistics snapshot or cached information.
2642 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2643 <a id="id-1.6.14.7.30.4.2.2.9.1.1.1" class="indexterm"></a>
2644 <code class="function">pg_stat_reset</code> ()
2645 → <code class="returnvalue">void</code>
2648 Resets all statistics counters for the current database to zero.
2651 This function is restricted to superusers by default, but other users
2652 can be granted EXECUTE to run the function.
2653 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2654 <a id="id-1.6.14.7.30.4.2.2.10.1.1.1" class="indexterm"></a>
2655 <code class="function">pg_stat_reset_shared</code> ( [ <em class="parameter"><code>target</code></em> <code class="type">text</code> <code class="literal">DEFAULT</code> <code class="literal">NULL</code> ] )
2656 → <code class="returnvalue">void</code>
2659 Resets some cluster-wide statistics counters to zero, depending on the
2660 argument. <em class="parameter"><code>target</code></em> can be:
2661 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
2662 <code class="literal">archiver</code>: Reset all the counters shown in the
2663 <code class="structname">pg_stat_archiver</code> view.
2664 </p></li><li class="listitem"><p>
2665 <code class="literal">bgwriter</code>: Reset all the counters shown in the
2666 <code class="structname">pg_stat_bgwriter</code> view.
2667 </p></li><li class="listitem"><p>
2668 <code class="literal">checkpointer</code>: Reset all the counters shown in the
2669 <code class="structname">pg_stat_checkpointer</code> view.
2670 </p></li><li class="listitem"><p>
2671 <code class="literal">io</code>: Reset all the counters shown in the
2672 <code class="structname">pg_stat_io</code> view.
2673 </p></li><li class="listitem"><p>
2674 <code class="literal">recovery_prefetch</code>: Reset all the counters shown in
2675 the <code class="structname">pg_stat_recovery_prefetch</code> view.
2676 </p></li><li class="listitem"><p>
2677 <code class="literal">slru</code>: Reset all the counters shown in the
2678 <code class="structname">pg_stat_slru</code> view.
2679 </p></li><li class="listitem"><p>
2680 <code class="literal">wal</code>: Reset all the counters shown in the
2681 <code class="structname">pg_stat_wal</code> view.
2682 </p></li><li class="listitem"><p>
2683 <code class="literal">NULL</code> or not specified: All the counters from the
2684 views listed above are reset.
2685 </p></li></ul></div><p>
2688 This function is restricted to superusers by default, but other users
2689 can be granted EXECUTE to run the function.
2690 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2691 <a id="id-1.6.14.7.30.4.2.2.11.1.1.1" class="indexterm"></a>
2692 <code class="function">pg_stat_reset_single_table_counters</code> ( <code class="type">oid</code> )
2693 → <code class="returnvalue">void</code>
2696 Resets statistics for a single table or index in the current database
2697 or shared across all databases in the cluster to zero.
2700 This function is restricted to superusers by default, but other users
2701 can be granted EXECUTE to run the function.
2702 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2703 <a id="id-1.6.14.7.30.4.2.2.12.1.1.1" class="indexterm"></a>
2704 <code class="function">pg_stat_reset_backend_stats</code> ( <code class="type">integer</code> )
2705 → <code class="returnvalue">void</code>
2708 Resets statistics for a single backend with the specified process ID
2712 This function is restricted to superusers by default, but other users
2713 can be granted EXECUTE to run the function.
2714 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2715 <a id="id-1.6.14.7.30.4.2.2.13.1.1.1" class="indexterm"></a>
2716 <code class="function">pg_stat_reset_single_function_counters</code> ( <code class="type">oid</code> )
2717 → <code class="returnvalue">void</code>
2720 Resets statistics for a single function in the current database to
2724 This function is restricted to superusers by default, but other users
2725 can be granted EXECUTE to run the function.
2726 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2727 <a id="id-1.6.14.7.30.4.2.2.14.1.1.1" class="indexterm"></a>
2728 <code class="function">pg_stat_reset_slru</code> ( [ <em class="parameter"><code>target</code></em> <code class="type">text</code> <code class="literal">DEFAULT</code> <code class="literal">NULL</code> ] )
2729 → <code class="returnvalue">void</code>
2732 Resets statistics to zero for a single SLRU cache, or for all SLRUs in
2733 the cluster. If <em class="parameter"><code>target</code></em> is
2734 <code class="literal">NULL</code> or is not specified, all the counters shown in
2735 the <code class="structname">pg_stat_slru</code> view for all SLRU caches are
2736 reset. The argument can be one of
2737 <code class="literal">commit_timestamp</code>,
2738 <code class="literal">multixact_member</code>,
2739 <code class="literal">multixact_offset</code>,
2740 <code class="literal">notify</code>,
2741 <code class="literal">serializable</code>,
2742 <code class="literal">subtransaction</code>, or
2743 <code class="literal">transaction</code>
2744 to reset the counters for only that entry.
2745 If the argument is <code class="literal">other</code> (or indeed, any
2746 unrecognized name), then the counters for all other SLRU caches, such
2747 as extension-defined caches, are reset.
2750 This function is restricted to superusers by default, but other users
2751 can be granted EXECUTE to run the function.
2752 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2753 <a id="id-1.6.14.7.30.4.2.2.15.1.1.1" class="indexterm"></a>
2754 <code class="function">pg_stat_reset_replication_slot</code> ( <code class="type">text</code> )
2755 → <code class="returnvalue">void</code>
2758 Resets statistics of the replication slot defined by the argument. If
2759 the argument is <code class="literal">NULL</code>, resets statistics for all
2760 the replication slots.
2763 This function is restricted to superusers by default, but other users
2764 can be granted EXECUTE to run the function.
2765 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2766 <a id="id-1.6.14.7.30.4.2.2.16.1.1.1" class="indexterm"></a>
2767 <code class="function">pg_stat_reset_subscription_stats</code> ( <code class="type">oid</code> )
2768 → <code class="returnvalue">void</code>
2771 Resets statistics for a single subscription shown in the
2772 <code class="structname">pg_stat_subscription_stats</code> view to zero. If
2773 the argument is <code class="literal">NULL</code>, reset statistics for all
2777 This function is restricted to superusers by default, but other users
2778 can be granted EXECUTE to run the function.
2779 </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="warning"><h3 class="title">Warning</h3><p>
2780 Using <code class="function">pg_stat_reset()</code> also resets counters that
2781 autovacuum uses to determine when to trigger a vacuum or an analyze.
2782 Resetting these counters can cause autovacuum to not perform necessary
2783 work, which can cause problems such as table bloat or out-dated
2784 table statistics. A database-wide <code class="command">ANALYZE</code> is
2785 recommended after the statistics have been reset.
2787 <code class="function">pg_stat_get_activity</code>, the underlying function of
2788 the <code class="structname">pg_stat_activity</code> view, returns a set of records
2789 containing all the available information about each backend process.
2790 Sometimes it may be more convenient to obtain just a subset of this
2791 information. In such cases, another set of per-backend statistics
2792 access functions can be used; these are shown in <a class="xref" href="monitoring-stats.html#MONITORING-STATS-BACKEND-FUNCS-TABLE" title="Table 27.37. Per-Backend Statistics Functions">Table 27.37</a>.
2793 These access functions use the session's backend ID number, which is a
2794 small integer (>= 0) that is distinct from the backend ID of any
2795 concurrent session, although a session's ID can be recycled as soon as
2796 it exits. The backend ID is used, among other things, to identify the
2797 session's temporary schema if it has one.
2798 The function <code class="function">pg_stat_get_backend_idset</code> provides a
2799 convenient way to list all the active backends' ID numbers for
2800 invoking these functions. For example, to show the <acronym class="acronym">PID</acronym>s and
2801 current queries of all backends:
2803 </p><pre class="programlisting">
2804 SELECT pg_stat_get_backend_pid(backendid) AS pid,
2805 pg_stat_get_backend_activity(backendid) AS query
2806 FROM pg_stat_get_backend_idset() AS backendid;
2808 </p><div class="table" id="MONITORING-STATS-BACKEND-FUNCS-TABLE"><p class="title"><strong>Table 27.37. Per-Backend Statistics Functions</strong></p><div class="table-contents"><table class="table" summary="Per-Backend Statistics Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
2813 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
2814 <a id="id-1.6.14.7.30.7.2.2.1.1.1.1" class="indexterm"></a>
2815 <code class="function">pg_stat_get_backend_activity</code> ( <code class="type">integer</code> )
2816 → <code class="returnvalue">text</code>
2819 Returns the text of this backend's most recent query.
2820 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2821 <a id="id-1.6.14.7.30.7.2.2.2.1.1.1" class="indexterm"></a>
2822 <code class="function">pg_stat_get_backend_activity_start</code> ( <code class="type">integer</code> )
2823 → <code class="returnvalue">timestamp with time zone</code>
2826 Returns the time when the backend's most recent query was started.
2827 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2828 <a id="id-1.6.14.7.30.7.2.2.3.1.1.1" class="indexterm"></a>
2829 <code class="function">pg_stat_get_backend_client_addr</code> ( <code class="type">integer</code> )
2830 → <code class="returnvalue">inet</code>
2833 Returns the IP address of the client connected to this backend.
2834 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2835 <a id="id-1.6.14.7.30.7.2.2.4.1.1.1" class="indexterm"></a>
2836 <code class="function">pg_stat_get_backend_client_port</code> ( <code class="type">integer</code> )
2837 → <code class="returnvalue">integer</code>
2840 Returns the TCP port number that the client is using for communication.
2841 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2842 <a id="id-1.6.14.7.30.7.2.2.5.1.1.1" class="indexterm"></a>
2843 <code class="function">pg_stat_get_backend_dbid</code> ( <code class="type">integer</code> )
2844 → <code class="returnvalue">oid</code>
2847 Returns the OID of the database this backend is connected to.
2848 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2849 <a id="id-1.6.14.7.30.7.2.2.6.1.1.1" class="indexterm"></a>
2850 <code class="function">pg_stat_get_backend_idset</code> ()
2851 → <code class="returnvalue">setof integer</code>
2854 Returns the set of currently active backend ID numbers.
2855 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2856 <a id="id-1.6.14.7.30.7.2.2.7.1.1.1" class="indexterm"></a>
2857 <code class="function">pg_stat_get_backend_pid</code> ( <code class="type">integer</code> )
2858 → <code class="returnvalue">integer</code>
2861 Returns the process ID of this backend.
2862 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2863 <a id="id-1.6.14.7.30.7.2.2.8.1.1.1" class="indexterm"></a>
2864 <code class="function">pg_stat_get_backend_start</code> ( <code class="type">integer</code> )
2865 → <code class="returnvalue">timestamp with time zone</code>
2868 Returns the time when this process was started.
2869 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2870 <a id="id-1.6.14.7.30.7.2.2.9.1.1.1" class="indexterm"></a>
2871 <code class="function">pg_stat_get_backend_subxact</code> ( <code class="type">integer</code> )
2872 → <code class="returnvalue">record</code>
2875 Returns a record of information about the subtransactions of the
2876 backend with the specified ID.
2877 The fields returned are <em class="parameter"><code>subxact_count</code></em>, which
2878 is the number of subtransactions in the backend's subtransaction cache,
2879 and <em class="parameter"><code>subxact_overflow</code></em>, which indicates whether
2880 the backend's subtransaction cache is overflowed or not.
2881 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2882 <a id="id-1.6.14.7.30.7.2.2.10.1.1.1" class="indexterm"></a>
2883 <code class="function">pg_stat_get_backend_userid</code> ( <code class="type">integer</code> )
2884 → <code class="returnvalue">oid</code>
2887 Returns the OID of the user logged into this backend.
2888 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2889 <a id="id-1.6.14.7.30.7.2.2.11.1.1.1" class="indexterm"></a>
2890 <code class="function">pg_stat_get_backend_wait_event</code> ( <code class="type">integer</code> )
2891 → <code class="returnvalue">text</code>
2894 Returns the wait event name if this backend is currently waiting,
2895 otherwise NULL. See <a class="xref" href="monitoring-stats.html#WAIT-EVENT-ACTIVITY-TABLE" title="Table 27.5. Wait Events of Type Activity">Table 27.5</a> through
2896 <a class="xref" href="monitoring-stats.html#WAIT-EVENT-TIMEOUT-TABLE" title="Table 27.13. Wait Events of Type Timeout">Table 27.13</a>.
2897 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2898 <a id="id-1.6.14.7.30.7.2.2.12.1.1.1" class="indexterm"></a>
2899 <code class="function">pg_stat_get_backend_wait_event_type</code> ( <code class="type">integer</code> )
2900 → <code class="returnvalue">text</code>
2903 Returns the wait event type name if this backend is currently waiting,
2904 otherwise NULL. See <a class="xref" href="monitoring-stats.html#WAIT-EVENT-TABLE" title="Table 27.4. Wait Event Types">Table 27.4</a> for details.
2905 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2906 <a id="id-1.6.14.7.30.7.2.2.13.1.1.1" class="indexterm"></a>
2907 <code class="function">pg_stat_get_backend_xact_start</code> ( <code class="type">integer</code> )
2908 → <code class="returnvalue">timestamp with time zone</code>
2911 Returns the time when the backend's current transaction was started.
2912 </p></td></tr></tbody></table></div></div><br class="table-break" /></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="monitoring-ps.html" title="27.1. Standard Unix Tools">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="monitoring.html" title="Chapter 27. Monitoring Database Activity">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="monitoring-locks.html" title="27.3. Viewing Locks">Next</a></td></tr><tr><td width="40%" align="left" valign="top">27.1. Standard Unix Tools </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 27.3. Viewing Locks</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob