Debian Codegraph @

doc/src/sgml/monitoring.sgml

raw
1 <!-- doc/src/sgml/monitoring.sgml -->
2
3 <chapter id="monitoring">
4 <title>Monitoring Database Activity</title>
5
6 <indexterm zone="monitoring">
7 <primary>monitoring</primary>
8 <secondary>database activity</secondary>
9 </indexterm>
10
11 <indexterm zone="monitoring">
12 <primary>database activity</primary>
13 <secondary>monitoring</secondary>
14 </indexterm>
15
16 <para>
17 A database administrator frequently wonders, <quote>What is the system
18 doing right now?</quote>
19 This chapter discusses how to find that out.
20 </para>
21
22 <para>
23 Several tools are available for monitoring database activity and
24 analyzing performance. Most of this chapter is devoted to describing
25 <productname>PostgreSQL</productname>'s cumulative statistics system,
26 but one should not neglect regular Unix monitoring programs such as
27 <command>ps</command>, <command>top</command>, <command>iostat</command>, and <command>vmstat</command>.
28 Also, once one has identified a
29 poorly-performing query, further investigation might be needed using
30 <productname>PostgreSQL</productname>'s <link linkend="sql-explain"><command>EXPLAIN</command></link> command.
31 <xref linkend="using-explain"/> discusses <command>EXPLAIN</command>
32 and other methods for understanding the behavior of an individual
33 query.
34 </para>
35
36 <sect1 id="monitoring-ps">
37 <title>Standard Unix Tools</title>
38
39 <indexterm zone="monitoring-ps">
40 <primary>ps</primary>
41 <secondary>to monitor activity</secondary>
42 </indexterm>
43
44 <para>
45 On most Unix platforms, <productname>PostgreSQL</productname> modifies its
46 command title as reported by <command>ps</command>, so that individual server
47 processes can readily be identified. A sample display is
48
49 <screen>
50 $ ps auxww | grep ^postgres
51 postgres 15551 0.0 0.1 57536 7132 pts/0 S 18:02 0:00 postgres -i
52 postgres 15554 0.0 0.0 57536 1184 ? Ss 18:02 0:00 postgres: background writer
53 postgres 15555 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: checkpointer
54 postgres 15556 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: walwriter
55 postgres 15557 0.0 0.0 58504 2244 ? Ss 18:02 0:00 postgres: autovacuum launcher
56 postgres 15582 0.0 0.0 58772 3080 ? Ss 18:04 0:00 postgres: joe runbug 127.0.0.1 idle
57 postgres 15606 0.0 0.0 58772 3052 ? Ss 18:07 0:00 postgres: tgl regression [local] SELECT waiting
58 postgres 15610 0.0 0.0 58772 3056 ? Ss 18:07 0:00 postgres: tgl regression [local] idle in transaction
59 </screen>
60
61 (The appropriate invocation of <command>ps</command> varies across different
62 platforms, as do the details of what is shown. This example is from a
63 recent Linux system.) The first process listed here is the
64 primary server process. The command arguments
65 shown for it are the same ones used when it was launched. The next four
66 processes are background worker processes automatically launched by the
67 primary process. (The <quote>autovacuum launcher</quote> process will not
68 be present if you have set the system not to run autovacuum.)
69 Each of the remaining
70 processes is a server process handling one client connection. Each such
71 process sets its command line display in the form
72
73 <screen>
74 postgres: <replaceable>user</replaceable> <replaceable>database</replaceable> <replaceable>host</replaceable> <replaceable>activity</replaceable>
75 </screen>
76
77 The user, database, and (client) host items remain the same for
78 the life of the client connection, but the activity indicator changes.
79 The activity can be <literal>idle</literal> (i.e., waiting for a client command),
80 <literal>idle in transaction</literal> (waiting for client inside a <command>BEGIN</command> block),
81 or a command type name such as <literal>SELECT</literal>. Also,
82 <literal>waiting</literal> is appended if the server process is presently waiting
83 on a lock held by another session. In the above example we can infer
84 that process 15606 is waiting for process 15610 to complete its transaction
85 and thereby release some lock. (Process 15610 must be the blocker, because
86 there is no other active session. In more complicated cases it would be
87 necessary to look into the
88 <link linkend="view-pg-locks"><structname>pg_locks</structname></link>
89 system view to determine who is blocking whom.)
90 </para>
91
92 <para>
93 If <xref linkend="guc-cluster-name"/> has been configured the
94 cluster name will also be shown in <command>ps</command> output:
95 <screen>
96 $ psql -c 'SHOW cluster_name'
97 cluster_name
98 --------------
99 server1
100 (1 row)
101
102 $ ps aux|grep server1
103 postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: server1: background writer
104 ...
105 </screen>
106 </para>
107
108 <para>
109 If you have turned off <xref linkend="guc-update-process-title"/> then the
110 activity indicator is not updated; the process title is set only once
111 when a new process is launched. On some platforms this saves a measurable
112 amount of per-command overhead; on others it's insignificant.
113 </para>
114
115 <tip>
116 <para>
117 <productname>Solaris</productname> requires special handling. You must
118 use <command>/usr/ucb/ps</command>, rather than
119 <command>/bin/ps</command>. You also must use two <option>w</option>
120 flags, not just one. In addition, your original invocation of the
121 <command>postgres</command> command must have a shorter
122 <command>ps</command> status display than that provided by each
123 server process. If you fail to do all three things, the <command>ps</command>
124 output for each server process will be the original <command>postgres</command>
125 command line.
126 </para>
127 </tip>
128 </sect1>
129
130 <sect1 id="monitoring-stats">
131 <title>The Cumulative Statistics System</title>
132
133 <indexterm zone="monitoring-stats">
134 <primary>statistics</primary>
135 </indexterm>
136
137 <para>
138 <productname>PostgreSQL</productname>'s <firstterm>cumulative statistics
139 system</firstterm> supports collection and reporting of information about
140 server activity. Presently, accesses to tables and indexes in both
141 disk-block and individual-row terms are counted. The total number of rows
142 in each table, and information about vacuum and analyze actions for each
143 table are also counted. If enabled, calls to user-defined functions and
144 the total time spent in each one are counted as well.
145 </para>
146
147 <para>
148 <productname>PostgreSQL</productname> also supports reporting dynamic
149 information about exactly what is going on in the system right now, such as
150 the exact command currently being executed by other server processes, and
151 which other connections exist in the system. This facility is independent
152 of the cumulative statistics system.
153 </para>
154
155 <sect2 id="monitoring-stats-setup">
156 <title>Statistics Collection Configuration</title>
157
158 <para>
159 Since collection of statistics adds some overhead to query execution,
160 the system can be configured to collect or not collect information.
161 This is controlled by configuration parameters that are normally set in
162 <filename>postgresql.conf</filename>. (See <xref linkend="runtime-config"/> for
163 details about setting configuration parameters.)
164 </para>
165
166 <para>
167 The parameter <xref linkend="guc-track-activities"/> enables monitoring
168 of the current command being executed by any server process.
169 </para>
170
171 <para>
172 The parameter <xref linkend="guc-track-cost-delay-timing"/> enables
173 monitoring of cost-based vacuum delay.
174 </para>
175
176 <para>
177 The parameter <xref linkend="guc-track-counts"/> controls whether
178 cumulative statistics are collected about table and index accesses.
179 </para>
180
181 <para>
182 The parameter <xref linkend="guc-track-functions"/> enables tracking of
183 usage of user-defined functions.
184 </para>
185
186 <para>
187 The parameter <xref linkend="guc-track-io-timing"/> enables monitoring
188 of block read, write, extend, and fsync times.
189 </para>
190
191 <para>
192 The parameter <xref linkend="guc-track-wal-io-timing"/> enables monitoring
193 of WAL read, write and fsync times.
194 </para>
195
196 <para>
197 Normally these parameters are set in <filename>postgresql.conf</filename> so
198 that they apply to all server processes, but it is possible to turn
199 them on or off in individual sessions using the <xref
200 linkend="sql-set"/> command. (To prevent
201 ordinary users from hiding their activity from the administrator,
202 only superusers are allowed to change these parameters with
203 <command>SET</command>.)
204 </para>
205
206 <para>
207 Cumulative statistics are collected in shared memory. Every
208 <productname>PostgreSQL</productname> process collects statistics locally,
209 then updates the shared data at appropriate intervals. When a server,
210 including a physical replica, shuts down cleanly, a permanent copy of the
211 statistics data is stored in the <filename>pg_stat</filename> subdirectory,
212 so that statistics can be retained across server restarts. In contrast,
213 when starting from an unclean shutdown (e.g., after an immediate shutdown,
214 a server crash, starting from a base backup, and point-in-time recovery),
215 all statistics counters are reset.
216 </para>
217
218 </sect2>
219
220 <sect2 id="monitoring-stats-views">
221 <title>Viewing Statistics</title>
222
223 <para>
224 Several predefined views, listed in <xref
225 linkend="monitoring-stats-dynamic-views-table"/>, are available to show
226 the current state of the system. There are also several other
227 views, listed in <xref
228 linkend="monitoring-stats-views-table"/>, available to show the accumulated
229 statistics. Alternatively, one can
230 build custom views using the underlying cumulative statistics functions, as
231 discussed in <xref linkend="monitoring-stats-functions"/>.
232 </para>
233
234 <para>
235 When using the cumulative statistics views and functions to monitor
236 collected data, it is important to realize that the information does not
237 update instantaneously. Each individual server process flushes out
238 accumulated statistics to shared memory just before going idle, but not
239 more frequently than once per <varname>PGSTAT_MIN_INTERVAL</varname>
240 milliseconds (1 second unless altered while building the server); so a
241 query or transaction still in progress does not affect the displayed totals
242 and the displayed information lags behind actual activity. However,
243 current-query information collected by <varname>track_activities</varname>
244 is always up-to-date.
245 </para>
246
247 <para>
248 Another important point is that when a server process is asked to display
249 any of the accumulated statistics, accessed values are cached until the end
250 of its current transaction in the default configuration. So the statistics
251 will show static information as long as you continue the current
252 transaction. Similarly, information about the current queries of all
253 sessions is collected when any such information is first requested within a
254 transaction, and the same information will be displayed throughout the
255 transaction. This is a feature, not a bug, because it allows you to perform
256 several queries on the statistics and correlate the results without
257 worrying that the numbers are changing underneath you.
258
259 When analyzing statistics interactively, or with expensive queries, the
260 time delta between accesses to individual statistics can lead to
261 significant skew in the cached statistics. To minimize skew,
262 <varname>stats_fetch_consistency</varname> can be set to
263 <literal>snapshot</literal>, at the price of increased memory usage for
264 caching not-needed statistics data. Conversely, if it's known that
265 statistics are only accessed once, caching accessed statistics is
266 unnecessary and can be avoided by setting
267 <varname>stats_fetch_consistency</varname> to <literal>none</literal>.
268
269 You can invoke <function>pg_stat_clear_snapshot()</function> to discard the
270 current transaction's statistics snapshot or cached values (if any). The
271 next use of statistical information will (when in snapshot mode) cause a
272 new snapshot to be built or (when in cache mode) accessed statistics to be
273 cached.
274 </para>
275
276 <para>
277 A transaction can also see its own statistics (not yet flushed out to the
278 shared memory statistics) in the views
279 <structname>pg_stat_xact_all_tables</structname>,
280 <structname>pg_stat_xact_sys_tables</structname>,
281 <structname>pg_stat_xact_user_tables</structname>, and
282 <structname>pg_stat_xact_user_functions</structname>. These numbers do not act as
283 stated above; instead they update continuously throughout the transaction.
284 </para>
285
286 <para>
287 Some of the information in the dynamic statistics views shown in <xref
288 linkend="monitoring-stats-dynamic-views-table"/> is security restricted.
289 Ordinary users can only see all the information about their own sessions
290 (sessions belonging to a role that they are a member of). In rows about
291 other sessions, many columns will be null. Note, however, that the
292 existence of a session and its general properties such as its sessions user
293 and database are visible to all users. Superusers and roles with privileges of
294 built-in role <link linkend="predefined-role-pg-monitor"><literal>pg_read_all_stats</literal></link>
295 can see all the information about all sessions.
296 </para>
297
298 <table id="monitoring-stats-dynamic-views-table">
299 <title>Dynamic Statistics Views</title>
300
301 <tgroup cols="2">
302 <thead>
303 <row>
304 <entry>View Name</entry>
305 <entry>Description</entry>
306 </row>
307 </thead>
308
309 <tbody>
310 <row>
311 <entry>
312 <structname>pg_stat_activity</structname>
313 <indexterm><primary>pg_stat_activity</primary></indexterm>
314 </entry>
315 <entry>
316 One row per server process, showing information related to
317 the current activity of that process, such as state and current query.
318 See <link linkend="monitoring-pg-stat-activity-view">
319 <structname>pg_stat_activity</structname></link> for details.
320 </entry>
321 </row>
322
323 <row>
324 <entry><structname>pg_stat_replication</structname><indexterm><primary>pg_stat_replication</primary></indexterm></entry>
325 <entry>One row per WAL sender process, showing statistics about
326 replication to that sender's connected standby server.
327 See <link linkend="monitoring-pg-stat-replication-view">
328 <structname>pg_stat_replication</structname></link> for details.
329 </entry>
330 </row>
331
332 <row>
333 <entry><structname>pg_stat_wal_receiver</structname><indexterm><primary>pg_stat_wal_receiver</primary></indexterm></entry>
334 <entry>Only one row, showing statistics about the WAL receiver from
335 that receiver's connected server.
336 See <link linkend="monitoring-pg-stat-wal-receiver-view">
337 <structname>pg_stat_wal_receiver</structname></link> for details.
338 </entry>
339 </row>
340
341 <row>
342 <entry><structname>pg_stat_recovery_prefetch</structname><indexterm><primary>pg_stat_recovery_prefetch</primary></indexterm></entry>
343 <entry>Only one row, showing statistics about blocks prefetched during recovery.
344 See <link linkend="monitoring-pg-stat-recovery-prefetch">
345 <structname>pg_stat_recovery_prefetch</structname></link> for details.
346 </entry>
347 </row>
348
349 <row>
350 <entry><structname>pg_stat_subscription</structname><indexterm><primary>pg_stat_subscription</primary></indexterm></entry>
351 <entry>At least one row per subscription, showing information about
352 the subscription workers.
353 See <link linkend="monitoring-pg-stat-subscription">
354 <structname>pg_stat_subscription</structname></link> for details.
355 </entry>
356 </row>
357
358 <row>
359 <entry><structname>pg_stat_ssl</structname><indexterm><primary>pg_stat_ssl</primary></indexterm></entry>
360 <entry>One row per connection (regular and replication), showing information about
361 SSL used on this connection.
362 See <link linkend="monitoring-pg-stat-ssl-view">
363 <structname>pg_stat_ssl</structname></link> for details.
364 </entry>
365 </row>
366
367 <row>
368 <entry><structname>pg_stat_gssapi</structname><indexterm><primary>pg_stat_gssapi</primary></indexterm></entry>
369 <entry>One row per connection (regular and replication), showing information about
370 GSSAPI authentication and encryption used on this connection.
371 See <link linkend="monitoring-pg-stat-gssapi-view">
372 <structname>pg_stat_gssapi</structname></link> for details.
373 </entry>
374 </row>
375
376 <row>
377 <entry><structname>pg_stat_progress_analyze</structname><indexterm><primary>pg_stat_progress_analyze</primary></indexterm></entry>
378 <entry>One row for each backend (including autovacuum worker processes) running
379 <command>ANALYZE</command>, showing current progress.
380 See <xref linkend="analyze-progress-reporting"/>.
381 </entry>
382 </row>
383
384 <row>
385 <entry><structname>pg_stat_progress_create_index</structname><indexterm><primary>pg_stat_progress_create_index</primary></indexterm></entry>
386 <entry>One row for each backend running <command>CREATE INDEX</command> or <command>REINDEX</command>, showing
387 current progress.
388 See <xref linkend="create-index-progress-reporting"/>.
389 </entry>
390 </row>
391
392 <row>
393 <entry><structname>pg_stat_progress_vacuum</structname><indexterm><primary>pg_stat_progress_vacuum</primary></indexterm></entry>
394 <entry>One row for each backend (including autovacuum worker processes) running
395 <command>VACUUM</command>, showing current progress.
396 See <xref linkend="vacuum-progress-reporting"/>.
397 </entry>
398 </row>
399
400 <row>
401 <entry><structname>pg_stat_progress_cluster</structname><indexterm><primary>pg_stat_progress_cluster</primary></indexterm></entry>
402 <entry>One row for each backend running
403 <command>CLUSTER</command> or <command>VACUUM FULL</command>, showing current progress.
404 See <xref linkend="cluster-progress-reporting"/>.
405 </entry>
406 </row>
407
408 <row>
409 <entry><structname>pg_stat_progress_basebackup</structname><indexterm><primary>pg_stat_progress_basebackup</primary></indexterm></entry>
410 <entry>One row for each WAL sender process streaming a base backup,
411 showing current progress.
412 See <xref linkend="basebackup-progress-reporting"/>.
413 </entry>
414 </row>
415
416 <row>
417 <entry><structname>pg_stat_progress_copy</structname><indexterm><primary>pg_stat_progress_copy</primary></indexterm></entry>
418 <entry>One row for each backend running <command>COPY</command>, showing current progress.
419 See <xref linkend="copy-progress-reporting"/>.
420 </entry>
421 </row>
422 </tbody>
423 </tgroup>
424 </table>
425
426 <table id="monitoring-stats-views-table">
427 <title>Collected Statistics Views</title>
428
429 <tgroup cols="2">
430 <thead>
431 <row>
432 <entry>View Name</entry>
433 <entry>Description</entry>
434 </row>
435 </thead>
436
437 <tbody>
438
439 <!-- everything related to global objects, alphabetically -->
440
441 <row>
442 <entry><structname>pg_stat_archiver</structname><indexterm><primary>pg_stat_archiver</primary></indexterm></entry>
443 <entry>One row only, showing statistics about the
444 WAL archiver process's activity. See
445 <link linkend="monitoring-pg-stat-archiver-view">
446 <structname>pg_stat_archiver</structname></link> for details.
447 </entry>
448 </row>
449
450 <row>
451 <entry><structname>pg_stat_bgwriter</structname><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
452 <entry>One row only, showing statistics about the
453 background writer process's activity. See
454 <link linkend="monitoring-pg-stat-bgwriter-view">
455 <structname>pg_stat_bgwriter</structname></link> for details.
456 </entry>
457 </row>
458
459 <row>
460 <entry><structname>pg_stat_checkpointer</structname><indexterm><primary>pg_stat_checkpointer</primary></indexterm></entry>
461 <entry>One row only, showing statistics about the
462 checkpointer process's activity. See
463 <link linkend="monitoring-pg-stat-checkpointer-view">
464 <structname>pg_stat_checkpointer</structname></link> for details.
465 </entry>
466 </row>
467
468 <row>
469 <entry><structname>pg_stat_database</structname><indexterm><primary>pg_stat_database</primary></indexterm></entry>
470 <entry>One row per database, showing database-wide statistics. See
471 <link linkend="monitoring-pg-stat-database-view">
472 <structname>pg_stat_database</structname></link> for details.
473 </entry>
474 </row>
475
476 <row>
477 <entry><structname>pg_stat_database_conflicts</structname><indexterm><primary>pg_stat_database_conflicts</primary></indexterm></entry>
478 <entry>
479 One row per database, showing database-wide statistics about
480 query cancels due to conflict with recovery on standby servers.
481 See <link linkend="monitoring-pg-stat-database-conflicts-view">
482 <structname>pg_stat_database_conflicts</structname></link> for details.
483 </entry>
484 </row>
485
486 <row>
487 <entry><structname>pg_stat_io</structname><indexterm><primary>pg_stat_io</primary></indexterm></entry>
488 <entry>
489 One row for each combination of backend type, context, and target object
490 containing cluster-wide I/O statistics.
491 See <link linkend="monitoring-pg-stat-io-view">
492 <structname>pg_stat_io</structname></link> for details.
493 </entry>
494 </row>
495
496 <row>
497 <entry><structname>pg_stat_replication_slots</structname><indexterm><primary>pg_stat_replication_slots</primary></indexterm></entry>
498 <entry>One row per replication slot, showing statistics about the
499 replication slot's usage. See
500 <link linkend="monitoring-pg-stat-replication-slots-view">
501 <structname>pg_stat_replication_slots</structname></link> for details.
502 </entry>
503 </row>
504
505 <row>
506 <entry><structname>pg_stat_slru</structname><indexterm><primary>pg_stat_slru</primary></indexterm></entry>
507 <entry>One row per SLRU, showing statistics of operations. See
508 <link linkend="monitoring-pg-stat-slru-view">
509 <structname>pg_stat_slru</structname></link> for details.
510 </entry>
511 </row>
512
513 <row>
514 <entry><structname>pg_stat_subscription_stats</structname><indexterm><primary>pg_stat_subscription_stats</primary></indexterm></entry>
515 <entry>One row per subscription, showing statistics about errors and conflicts.
516 See <link linkend="monitoring-pg-stat-subscription-stats">
517 <structname>pg_stat_subscription_stats</structname></link> for details.
518 </entry>
519 </row>
520
521 <row>
522 <entry><structname>pg_stat_wal</structname><indexterm><primary>pg_stat_wal</primary></indexterm></entry>
523 <entry>One row only, showing statistics about WAL activity. See
524 <link linkend="monitoring-pg-stat-wal-view">
525 <structname>pg_stat_wal</structname></link> for details.
526 </entry>
527 </row>
528
529 <!-- all "stat" for schema objects, by "importance" -->
530
531 <row>
532 <entry><structname>pg_stat_all_tables</structname><indexterm><primary>pg_stat_all_tables</primary></indexterm></entry>
533 <entry>
534 One row for each table in the current database, showing statistics
535 about accesses to that specific table.
536 See <link linkend="monitoring-pg-stat-all-tables-view">
537 <structname>pg_stat_all_tables</structname></link> for details.
538 </entry>
539 </row>
540
541 <row>
542 <entry><structname>pg_stat_sys_tables</structname><indexterm><primary>pg_stat_sys_tables</primary></indexterm></entry>
543 <entry>Same as <structname>pg_stat_all_tables</structname>, except that only
544 system tables are shown.</entry>
545 </row>
546
547 <row>
548 <entry><structname>pg_stat_user_tables</structname><indexterm><primary>pg_stat_user_tables</primary></indexterm></entry>
549 <entry>Same as <structname>pg_stat_all_tables</structname>, except that only user
550 tables are shown.</entry>
551 </row>
552
553 <row>
554 <entry><structname>pg_stat_xact_all_tables</structname><indexterm><primary>pg_stat_xact_all_tables</primary></indexterm></entry>
555 <entry>Similar to <structname>pg_stat_all_tables</structname>, but counts actions
556 taken so far within the current transaction (which are <emphasis>not</emphasis>
557 yet included in <structname>pg_stat_all_tables</structname> and related views).
558 The columns for numbers of live and dead rows and vacuum and
559 analyze actions are not present in this view.</entry>
560 </row>
561
562 <row>
563 <entry><structname>pg_stat_xact_sys_tables</structname><indexterm><primary>pg_stat_xact_sys_tables</primary></indexterm></entry>
564 <entry>Same as <structname>pg_stat_xact_all_tables</structname>, except that only
565 system tables are shown.</entry>
566 </row>
567
568 <row>
569 <entry><structname>pg_stat_xact_user_tables</structname><indexterm><primary>pg_stat_xact_user_tables</primary></indexterm></entry>
570 <entry>Same as <structname>pg_stat_xact_all_tables</structname>, except that only
571 user tables are shown.</entry>
572 </row>
573
574 <row>
575 <entry><structname>pg_stat_all_indexes</structname><indexterm><primary>pg_stat_all_indexes</primary></indexterm></entry>
576 <entry>
577 One row for each index in the current database, showing statistics
578 about accesses to that specific index.
579 See <link linkend="monitoring-pg-stat-all-indexes-view">
580 <structname>pg_stat_all_indexes</structname></link> for details.
581 </entry>
582 </row>
583
584 <row>
585 <entry><structname>pg_stat_sys_indexes</structname><indexterm><primary>pg_stat_sys_indexes</primary></indexterm></entry>
586 <entry>Same as <structname>pg_stat_all_indexes</structname>, except that only
587 indexes on system tables are shown.</entry>
588 </row>
589
590 <row>
591 <entry><structname>pg_stat_user_indexes</structname><indexterm><primary>pg_stat_user_indexes</primary></indexterm></entry>
592 <entry>Same as <structname>pg_stat_all_indexes</structname>, except that only
593 indexes on user tables are shown.</entry>
594 </row>
595
596 <row>
597 <entry><structname>pg_stat_user_functions</structname><indexterm><primary>pg_stat_user_functions</primary></indexterm></entry>
598 <entry>
599 One row for each tracked function, showing statistics
600 about executions of that function. See
601 <link linkend="monitoring-pg-stat-user-functions-view">
602 <structname>pg_stat_user_functions</structname></link> for details.
603 </entry>
604 </row>
605
606 <row>
607 <entry><structname>pg_stat_xact_user_functions</structname><indexterm><primary>pg_stat_xact_user_functions</primary></indexterm></entry>
608 <entry>Similar to <structname>pg_stat_user_functions</structname>, but counts only
609 calls during the current transaction (which are <emphasis>not</emphasis>
610 yet included in <structname>pg_stat_user_functions</structname>).</entry>
611 </row>
612
613 <!-- all "statio" for schema objects, by "importance" -->
614
615 <row>
616 <entry><structname>pg_statio_all_tables</structname><indexterm><primary>pg_statio_all_tables</primary></indexterm></entry>
617 <entry>
618 One row for each table in the current database, showing statistics
619 about I/O on that specific table.
620 See <link linkend="monitoring-pg-statio-all-tables-view">
621 <structname>pg_statio_all_tables</structname></link> for details.
622 </entry>
623 </row>
624
625 <row>
626 <entry><structname>pg_statio_sys_tables</structname><indexterm><primary>pg_statio_sys_tables</primary></indexterm></entry>
627 <entry>Same as <structname>pg_statio_all_tables</structname>, except that only
628 system tables are shown.</entry>
629 </row>
630
631 <row>
632 <entry><structname>pg_statio_user_tables</structname><indexterm><primary>pg_statio_user_tables</primary></indexterm></entry>
633 <entry>Same as <structname>pg_statio_all_tables</structname>, except that only
634 user tables are shown.</entry>
635 </row>
636
637 <row>
638 <entry><structname>pg_statio_all_indexes</structname><indexterm><primary>pg_statio_all_indexes</primary></indexterm></entry>
639 <entry>
640 One row for each index in the current database,
641 showing statistics about I/O on that specific index.
642 See <link linkend="monitoring-pg-statio-all-indexes-view">
643 <structname>pg_statio_all_indexes</structname></link> for details.
644 </entry>
645 </row>
646
647 <row>
648 <entry><structname>pg_statio_sys_indexes</structname><indexterm><primary>pg_statio_sys_indexes</primary></indexterm></entry>
649 <entry>Same as <structname>pg_statio_all_indexes</structname>, except that only
650 indexes on system tables are shown.</entry>
651 </row>
652
653 <row>
654 <entry><structname>pg_statio_user_indexes</structname><indexterm><primary>pg_statio_user_indexes</primary></indexterm></entry>
655 <entry>Same as <structname>pg_statio_all_indexes</structname>, except that only
656 indexes on user tables are shown.</entry>
657 </row>
658
659 <row>
660 <entry><structname>pg_statio_all_sequences</structname><indexterm><primary>pg_statio_all_sequences</primary></indexterm></entry>
661 <entry>
662 One row for each sequence in the current database,
663 showing statistics about I/O on that specific sequence.
664 See <link linkend="monitoring-pg-statio-all-sequences-view">
665 <structname>pg_statio_all_sequences</structname></link> for details.
666 </entry>
667 </row>
668
669 <row>
670 <entry><structname>pg_statio_sys_sequences</structname><indexterm><primary>pg_statio_sys_sequences</primary></indexterm></entry>
671 <entry>Same as <structname>pg_statio_all_sequences</structname>, except that only
672 system sequences are shown. (Presently, no system sequences are defined,
673 so this view is always empty.)</entry>
674 </row>
675
676 <row>
677 <entry><structname>pg_statio_user_sequences</structname><indexterm><primary>pg_statio_user_sequences</primary></indexterm></entry>
678 <entry>Same as <structname>pg_statio_all_sequences</structname>, except that only
679 user sequences are shown.</entry>
680 </row>
681
682 </tbody>
683 </tgroup>
684 </table>
685
686 <para>
687 The per-index statistics are particularly useful to determine which
688 indexes are being used and how effective they are.
689 </para>
690
691 <para>
692 The <structname>pg_stat_io</structname> and
693 <structname>pg_statio_</structname> set of views are useful for determining
694 the effectiveness of the buffer cache. They can be used to calculate a cache
695 hit ratio. Note that while <productname>PostgreSQL</productname>'s I/O
696 statistics capture most instances in which the kernel was invoked in order
697 to perform I/O, they do not differentiate between data which had to be
698 fetched from disk and that which already resided in the kernel page cache.
699 Users are advised to use the <productname>PostgreSQL</productname>
700 statistics views in combination with operating system utilities for a more
701 complete picture of their database's I/O performance.
702 </para>
703
704 </sect2>
705
706 <sect2 id="monitoring-pg-stat-activity-view">
707 <title><structname>pg_stat_activity</structname></title>
708
709 <indexterm>
710 <primary>pg_stat_activity</primary>
711 </indexterm>
712
713 <para>
714 The <structname>pg_stat_activity</structname> view will have one row
715 per server process, showing information related to
716 the current activity of that process.
717 </para>
718
719 <table id="pg-stat-activity-view" xreflabel="pg_stat_activity">
720 <title><structname>pg_stat_activity</structname> View</title>
721 <tgroup cols="1">
722 <thead>
723 <row>
724 <entry role="catalog_table_entry"><para role="column_definition">
725 Column Type
726 </para>
727 <para>
728 Description
729 </para></entry>
730 </row>
731 </thead>
732
733 <tbody>
734 <row>
735 <entry role="catalog_table_entry"><para role="column_definition">
736 <structfield>datid</structfield> <type>oid</type>
737 </para>
738 <para>
739 OID of the database this backend is connected to
740 </para></entry>
741 </row>
742
743 <row>
744 <entry role="catalog_table_entry"><para role="column_definition">
745 <structfield>datname</structfield> <type>name</type>
746 </para>
747 <para>
748 Name of the database this backend is connected to
749 </para></entry>
750 </row>
751
752 <row>
753 <entry role="catalog_table_entry"><para role="column_definition">
754 <structfield>pid</structfield> <type>integer</type>
755 </para>
756 <para>
757 Process ID of this backend
758 </para></entry>
759 </row>
760
761 <row>
762 <entry role="catalog_table_entry"><para role="column_definition">
763 <structfield>leader_pid</structfield> <type>integer</type>
764 </para>
765 <para>
766 Process ID of the parallel group leader if this process is a parallel
767 query worker, or process ID of the leader apply worker if this process
768 is a parallel apply worker. <literal>NULL</literal> indicates that this
769 process is a parallel group leader or leader apply worker, or does not
770 participate in any parallel operation.
771 </para></entry>
772 </row>
773
774 <row>
775 <entry role="catalog_table_entry"><para role="column_definition">
776 <structfield>usesysid</structfield> <type>oid</type>
777 </para>
778 <para>
779 OID of the user logged into this backend
780 </para></entry>
781 </row>
782
783 <row>
784 <entry role="catalog_table_entry"><para role="column_definition">
785 <structfield>usename</structfield> <type>name</type>
786 </para>
787 <para>
788 Name of the user logged into this backend
789 </para></entry>
790 </row>
791
792 <row>
793 <entry role="catalog_table_entry"><para role="column_definition">
794 <structfield>application_name</structfield> <type>text</type>
795 </para>
796 <para>
797 Name of the application that is connected
798 to this backend
799 </para></entry>
800 </row>
801
802 <row>
803 <entry role="catalog_table_entry"><para role="column_definition">
804 <structfield>client_addr</structfield> <type>inet</type>
805 </para>
806 <para>
807 IP address of the client connected to this backend.
808 If this field is null, it indicates either that the client is
809 connected via a Unix socket on the server machine or that this is an
810 internal process such as autovacuum.
811 </para></entry>
812 </row>
813
814 <row>
815 <entry role="catalog_table_entry"><para role="column_definition">
816 <structfield>client_hostname</structfield> <type>text</type>
817 </para>
818 <para>
819 Host name of the connected client, as reported by a
820 reverse DNS lookup of <structfield>client_addr</structfield>. This field will
821 only be non-null for IP connections, and only when <xref linkend="guc-log-hostname"/> is enabled.
822 </para></entry>
823 </row>
824
825 <row>
826 <entry role="catalog_table_entry"><para role="column_definition">
827 <structfield>client_port</structfield> <type>integer</type>
828 </para>
829 <para>
830 TCP port number that the client is using for communication
831 with this backend, or <literal>-1</literal> if a Unix socket is used.
832 If this field is null, it indicates that this is an internal server process.
833 </para></entry>
834 </row>
835
836 <row>
837 <entry role="catalog_table_entry"><para role="column_definition">
838 <structfield>backend_start</structfield> <type>timestamp with time zone</type>
839 </para>
840 <para>
841 Time when this process was started. For client backends,
842 this is the time the client connected to the server.
843 </para></entry>
844 </row>
845
846 <row>
847 <entry role="catalog_table_entry"><para role="column_definition">
848 <structfield>xact_start</structfield> <type>timestamp with time zone</type>
849 </para>
850 <para>
851 Time when this process' current transaction was started, or null
852 if no transaction is active. If the current
853 query is the first of its transaction, this column is equal to the
854 <structfield>query_start</structfield> column.
855 </para></entry>
856 </row>
857
858 <row>
859 <entry role="catalog_table_entry"><para role="column_definition">
860 <structfield>query_start</structfield> <type>timestamp with time zone</type>
861 </para>
862 <para>
863 Time when the currently active query was started, or if
864 <structfield>state</structfield> is not <literal>active</literal>, when the last query
865 was started
866 </para></entry>
867 </row>
868
869 <row>
870 <entry role="catalog_table_entry"><para role="column_definition">
871 <structfield>state_change</structfield> <type>timestamp with time zone</type>
872 </para>
873 <para>
874 Time when the <structfield>state</structfield> was last changed
875 </para></entry>
876 </row>
877
878 <row>
879 <entry role="catalog_table_entry"><para role="column_definition">
880 <structfield>wait_event_type</structfield> <type>text</type>
881 </para>
882 <para>
883 The type of event for which the backend is waiting, if any;
884 otherwise NULL. See <xref linkend="wait-event-table"/>.
885 </para></entry>
886 </row>
887
888 <row>
889 <entry role="catalog_table_entry"><para role="column_definition">
890 <structfield>wait_event</structfield> <type>text</type>
891 </para>
892 <para>
893 Wait event name if backend is currently waiting, otherwise NULL.
894 See <xref linkend="wait-event-activity-table"/> through
895 <xref linkend="wait-event-timeout-table"/>.
896 </para></entry>
897 </row>
898
899 <row>
900 <entry role="catalog_table_entry"><para role="column_definition">
901 <structfield>state</structfield> <type>text</type>
902 </para>
903 <para>
904 Current overall state of this backend.
905 Possible values are:
906 <itemizedlist>
907 <listitem>
908 <para>
909 <literal>starting</literal>: The backend is in initial startup. Client
910 authentication is performed during this phase.
911 </para>
912 </listitem>
913 <listitem>
914 <para>
915 <literal>active</literal>: The backend is executing a query.
916 </para>
917 </listitem>
918 <listitem>
919 <para>
920 <literal>idle</literal>: The backend is waiting for a new client command.
921 </para>
922 </listitem>
923 <listitem>
924 <para>
925 <literal>idle in transaction</literal>: The backend is in a transaction,
926 but is not currently executing a query.
927 </para>
928 </listitem>
929 <listitem>
930 <para>
931 <literal>idle in transaction (aborted)</literal>: This state is similar to
932 <literal>idle in transaction</literal>, except one of the statements in
933 the transaction caused an error.
934 </para>
935 </listitem>
936 <listitem>
937 <para>
938 <literal>fastpath function call</literal>: The backend is executing a
939 fast-path function.
940 </para>
941 </listitem>
942 <listitem>
943 <para>
944 <literal>disabled</literal>: This state is reported if <xref linkend="guc-track-activities"/> is disabled in this backend.
945 </para>
946 </listitem>
947 </itemizedlist>
948 </para></entry>
949 </row>
950
951 <row>
952 <entry role="catalog_table_entry"><para role="column_definition">
953 <structfield>backend_xid</structfield> <type>xid</type>
954 </para>
955 <para>
956 Top-level transaction identifier of this backend, if any; see
957 <xref linkend="transaction-id"/>.
958 </para></entry>
959 </row>
960
961 <row>
962 <entry role="catalog_table_entry"><para role="column_definition">
963 <structfield>backend_xmin</structfield> <type>xid</type>
964 </para>
965 <para>
966 The current backend's <literal>xmin</literal> horizon.
967 </para></entry>
968 </row>
969
970 <row>
971 <entry role="catalog_table_entry"><para role="column_definition">
972 <structfield>query_id</structfield> <type>bigint</type>
973 </para>
974 <para>
975 Identifier of this backend's most recent query. If
976 <structfield>state</structfield> is <literal>active</literal> this
977 field shows the identifier of the currently executing query. In
978 all other states, it shows the identifier of last query that was
979 executed. Query identifiers are not computed by default so this
980 field will be null unless <xref linkend="guc-compute-query-id"/>
981 parameter is enabled or a third-party module that computes query
982 identifiers is configured.
983 </para></entry>
984 </row>
985
986 <row>
987 <entry role="catalog_table_entry"><para role="column_definition">
988 <structfield>query</structfield> <type>text</type>
989 </para>
990 <para>
991 Text of this backend's most recent query. If
992 <structfield>state</structfield> is <literal>active</literal> this field shows the
993 currently executing query. In all other states, it shows the last query
994 that was executed. By default the query text is truncated at 1024
995 bytes; this value can be changed via the parameter
996 <xref linkend="guc-track-activity-query-size"/>.
997 </para></entry>
998 </row>
999
1000 <row>
1001 <entry role="catalog_table_entry"><para role="column_definition">
1002 <structfield>backend_type</structfield> <type>text</type>
1003 </para>
1004 <para>
1005 Type of current backend. Possible types are
1006 <literal>autovacuum launcher</literal>, <literal>autovacuum worker</literal>,
1007 <literal>logical replication launcher</literal>,
1008 <literal>logical replication worker</literal>,
1009 <literal>parallel worker</literal>, <literal>background writer</literal>,
1010 <literal>client backend</literal>, <literal>checkpointer</literal>,
1011 <literal>archiver</literal>, <literal>standalone backend</literal>,
1012 <literal>startup</literal>, <literal>walreceiver</literal>,
1013 <literal>walsender</literal>, <literal>walwriter</literal> and
1014 <literal>walsummarizer</literal>.
1015 In addition, background workers registered by extensions may have
1016 additional types.
1017 </para></entry>
1018 </row>
1019 </tbody>
1020 </tgroup>
1021 </table>
1022
1023 <note>
1024 <para>
1025 The <structfield>wait_event</structfield> and <structfield>state</structfield> columns are
1026 independent. If a backend is in the <literal>active</literal> state,
1027 it may or may not be <literal>waiting</literal> on some event. If the state
1028 is <literal>active</literal> and <structfield>wait_event</structfield> is non-null, it
1029 means that a query is being executed, but is being blocked somewhere
1030 in the system. To keep the reporting overhead low, the system does not
1031 attempt to synchronize different aspects of activity data for a backend.
1032 As a result, ephemeral discrepancies may exist between the view's columns.
1033 </para>
1034 </note>
1035
1036 <table id="wait-event-table">
1037 <title>Wait Event Types</title>
1038 <tgroup cols="2">
1039 <thead>
1040 <row>
1041 <entry>Wait Event Type</entry>
1042 <entry>Description</entry>
1043 </row>
1044 </thead>
1045
1046 <tbody>
1047 <row>
1048 <entry><literal>Activity</literal></entry>
1049 <entry>The server process is idle. This event type indicates a process
1050 waiting for activity in its main processing loop.
1051 <literal>wait_event</literal> will identify the specific wait point;
1052 see <xref linkend="wait-event-activity-table"/>.
1053 </entry>
1054 </row>
1055 <row>
1056 <entry><literal>BufferPin</literal></entry>
1057 <entry>The server process is waiting for exclusive access to
1058 a data buffer. Buffer pin waits can be protracted if
1059 another process holds an open cursor that last read data from the
1060 buffer in question. See <xref linkend="wait-event-bufferpin-table"/>.
1061 </entry>
1062 </row>
1063 <row>
1064 <entry><literal>Client</literal></entry>
1065 <entry>The server process is waiting for activity on a socket
1066 connected to a user application. Thus, the server expects something
1067 to happen that is independent of its internal processes.
1068 <literal>wait_event</literal> will identify the specific wait point;
1069 see <xref linkend="wait-event-client-table"/>.
1070 </entry>
1071 </row>
1072 <row>
1073 <entry><literal>Extension</literal></entry>
1074 <entry>The server process is waiting for some condition defined by an
1075 extension module.
1076 See <xref linkend="wait-event-extension-table"/>.
1077 </entry>
1078 </row>
1079 <row>
1080 <entry><literal>InjectionPoint</literal></entry>
1081 <entry>The server process is waiting for an injection point to reach an
1082 outcome defined in a test. See
1083 <xref linkend="xfunc-addin-injection-points"/> for more details. This
1084 type has no predefined wait points.
1085 </entry>
1086 </row>
1087 <row>
1088 <entry><literal>IO</literal></entry>
1089 <entry>The server process is waiting for an I/O operation to complete.
1090 <literal>wait_event</literal> will identify the specific wait point;
1091 see <xref linkend="wait-event-io-table"/>.
1092 </entry>
1093 </row>
1094 <row>
1095 <entry><literal>IPC</literal></entry>
1096 <entry>The server process is waiting for some interaction with
1097 another server process. <literal>wait_event</literal> will
1098 identify the specific wait point;
1099 see <xref linkend="wait-event-ipc-table"/>.
1100 </entry>
1101 </row>
1102 <row>
1103 <entry><literal>Lock</literal></entry>
1104 <entry>The server process is waiting for a heavyweight lock.
1105 Heavyweight locks, also known as lock manager locks or simply locks,
1106 primarily protect SQL-visible objects such as tables. However,
1107 they are also used to ensure mutual exclusion for certain internal
1108 operations such as relation extension. <literal>wait_event</literal>
1109 will identify the type of lock awaited;
1110 see <xref linkend="wait-event-lock-table"/>.
1111 </entry>
1112 </row>
1113 <row>
1114 <entry><literal>LWLock</literal></entry>
1115 <entry> The server process is waiting for a lightweight lock.
1116 Most such locks protect a particular data structure in shared memory.
1117 <literal>wait_event</literal> will contain a name identifying the purpose
1118 of the lightweight lock. (Some locks have specific names; others
1119 are part of a group of locks each with a similar purpose.)
1120 See <xref linkend="wait-event-lwlock-table"/>.
1121 </entry>
1122 </row>
1123 <row>
1124 <entry><literal>Timeout</literal></entry>
1125 <entry>The server process is waiting for a timeout
1126 to expire. <literal>wait_event</literal> will identify the specific wait
1127 point; see <xref linkend="wait-event-timeout-table"/>.
1128 </entry>
1129 </row>
1130 </tbody>
1131 </tgroup>
1132 </table>
1133
1134 &wait_event_types;
1135
1136 <para>
1137 Here are examples of how wait events can be viewed:
1138
1139 <programlisting>
1140 SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;
1141 pid | wait_event_type | wait_event
1142 ------+-----------------+------------
1143 2540 | Lock | relation
1144 6644 | LWLock | ProcArray
1145 (2 rows)
1146 </programlisting>
1147
1148 <programlisting>
1149 SELECT a.pid, a.wait_event, w.description
1150 FROM pg_stat_activity a JOIN
1151 pg_wait_events w ON (a.wait_event_type = w.type AND
1152 a.wait_event = w.name)
1153 WHERE a.wait_event is NOT NULL and a.state = 'active';
1154 -[ RECORD 1 ]------------------------------------------------------&zwsp;------------
1155 pid | 686674
1156 wait_event | WALInitSync
1157 description | Waiting for a newly initialized WAL file to reach durable storage
1158 </programlisting>
1159 </para>
1160
1161 <note>
1162 <para>
1163 Extensions can add <literal>Extension</literal>,
1164 <literal>InjectionPoint</literal>, and <literal>LWLock</literal> events
1165 to the lists shown in <xref linkend="wait-event-extension-table"/> and
1166 <xref linkend="wait-event-lwlock-table"/>. In some cases, the name
1167 of an <literal>LWLock</literal> assigned by an extension will not be
1168 available in all server processes. It might be reported as just
1169 <quote><literal>extension</literal></quote> rather than the
1170 extension-assigned name.
1171 </para>
1172 </note>
1173 </sect2>
1174
1175 <sect2 id="monitoring-pg-stat-replication-view">
1176 <title><structname>pg_stat_replication</structname></title>
1177
1178 <indexterm>
1179 <primary>pg_stat_replication</primary>
1180 </indexterm>
1181
1182 <para>
1183 The <structname>pg_stat_replication</structname> view will contain one row
1184 per WAL sender process, showing statistics about replication to that
1185 sender's connected standby server. Only directly connected standbys are
1186 listed; no information is available about downstream standby servers.
1187 </para>
1188
1189 <table id="pg-stat-replication-view" xreflabel="pg_stat_replication">
1190 <title><structname>pg_stat_replication</structname> View</title>
1191 <tgroup cols="1">
1192 <thead>
1193 <row>
1194 <entry role="catalog_table_entry"><para role="column_definition">
1195 Column Type
1196 </para>
1197 <para>
1198 Description
1199 </para></entry>
1200 </row>
1201 </thead>
1202
1203 <tbody>
1204 <row>
1205 <entry role="catalog_table_entry"><para role="column_definition">
1206 <structfield>pid</structfield> <type>integer</type>
1207 </para>
1208 <para>
1209 Process ID of a WAL sender process
1210 </para></entry>
1211 </row>
1212
1213 <row>
1214 <entry role="catalog_table_entry"><para role="column_definition">
1215 <structfield>usesysid</structfield> <type>oid</type>
1216 </para>
1217 <para>
1218 OID of the user logged into this WAL sender process
1219 </para></entry>
1220 </row>
1221
1222 <row>
1223 <entry role="catalog_table_entry"><para role="column_definition">
1224 <structfield>usename</structfield> <type>name</type>
1225 </para>
1226 <para>
1227 Name of the user logged into this WAL sender process
1228 </para></entry>
1229 </row>
1230
1231 <row>
1232 <entry role="catalog_table_entry"><para role="column_definition">
1233 <structfield>application_name</structfield> <type>text</type>
1234 </para>
1235 <para>
1236 Name of the application that is connected
1237 to this WAL sender
1238 </para></entry>
1239 </row>
1240
1241 <row>
1242 <entry role="catalog_table_entry"><para role="column_definition">
1243 <structfield>client_addr</structfield> <type>inet</type>
1244 </para>
1245 <para>
1246 IP address of the client connected to this WAL sender.
1247 If this field is null, it indicates that the client is
1248 connected via a Unix socket on the server machine.
1249 </para></entry>
1250 </row>
1251
1252 <row>
1253 <entry role="catalog_table_entry"><para role="column_definition">
1254 <structfield>client_hostname</structfield> <type>text</type>
1255 </para>
1256 <para>
1257 Host name of the connected client, as reported by a
1258 reverse DNS lookup of <structfield>client_addr</structfield>. This field will
1259 only be non-null for IP connections, and only when <xref linkend="guc-log-hostname"/> is enabled.
1260 </para></entry>
1261 </row>
1262
1263 <row>
1264 <entry role="catalog_table_entry"><para role="column_definition">
1265 <structfield>client_port</structfield> <type>integer</type>
1266 </para>
1267 <para>
1268 TCP port number that the client is using for communication
1269 with this WAL sender, or <literal>-1</literal> if a Unix socket is used
1270 </para></entry>
1271 </row>
1272
1273 <row>
1274 <entry role="catalog_table_entry"><para role="column_definition">
1275 <structfield>backend_start</structfield> <type>timestamp with time zone</type>
1276 </para>
1277 <para>
1278 Time when this process was started, i.e., when the
1279 client connected to this WAL sender
1280 </para></entry>
1281 </row>
1282
1283 <row>
1284 <entry role="catalog_table_entry"><para role="column_definition">
1285 <structfield>backend_xmin</structfield> <type>xid</type>
1286 </para>
1287 <para>
1288 This standby's <literal>xmin</literal> horizon reported
1289 by <xref linkend="guc-hot-standby-feedback"/>.
1290 </para></entry>
1291 </row>
1292
1293 <row>
1294 <entry role="catalog_table_entry"><para role="column_definition">
1295 <structfield>state</structfield> <type>text</type>
1296 </para>
1297 <para>
1298 Current WAL sender state.
1299 Possible values are:
1300 <itemizedlist>
1301 <listitem>
1302 <para>
1303 <literal>startup</literal>: This WAL sender is starting up.
1304 </para>
1305 </listitem>
1306 <listitem>
1307 <para>
1308 <literal>catchup</literal>: This WAL sender's connected standby is
1309 catching up with the primary.
1310 </para>
1311 </listitem>
1312 <listitem>
1313 <para>
1314 <literal>streaming</literal>: This WAL sender is streaming changes
1315 after its connected standby server has caught up with the primary.
1316 </para>
1317 </listitem>
1318 <listitem>
1319 <para>
1320 <literal>backup</literal>: This WAL sender is sending a backup.
1321 </para>
1322 </listitem>
1323 <listitem>
1324 <para>
1325 <literal>stopping</literal>: This WAL sender is stopping.
1326 </para>
1327 </listitem>
1328 </itemizedlist>
1329 </para></entry>
1330 </row>
1331
1332 <row>
1333 <entry role="catalog_table_entry"><para role="column_definition">
1334 <structfield>sent_lsn</structfield> <type>pg_lsn</type>
1335 </para>
1336 <para>
1337 Last write-ahead log location sent on this connection
1338 </para></entry>
1339 </row>
1340
1341 <row>
1342 <entry role="catalog_table_entry"><para role="column_definition">
1343 <structfield>write_lsn</structfield> <type>pg_lsn</type>
1344 </para>
1345 <para>
1346 Last write-ahead log location written to disk by this standby
1347 server
1348 </para></entry>
1349 </row>
1350
1351 <row>
1352 <entry role="catalog_table_entry"><para role="column_definition">
1353 <structfield>flush_lsn</structfield> <type>pg_lsn</type>
1354 </para>
1355 <para>
1356 Last write-ahead log location flushed to disk by this standby
1357 server
1358 </para></entry>
1359 </row>
1360
1361 <row>
1362 <entry role="catalog_table_entry"><para role="column_definition">
1363 <structfield>replay_lsn</structfield> <type>pg_lsn</type>
1364 </para>
1365 <para>
1366 Last write-ahead log location replayed into the database on this
1367 standby server
1368 </para></entry>
1369 </row>
1370
1371 <row>
1372 <entry role="catalog_table_entry"><para role="column_definition">
1373 <structfield>write_lag</structfield> <type>interval</type>
1374 </para>
1375 <para>
1376 Time elapsed between flushing recent WAL locally and receiving
1377 notification that this standby server has written it (but not yet
1378 flushed it or applied it). This can be used to gauge the delay that
1379 <literal>synchronous_commit</literal> level
1380 <literal>remote_write</literal> incurred while committing if this
1381 server was configured as a synchronous standby.
1382 </para></entry>
1383 </row>
1384
1385 <row>
1386 <entry role="catalog_table_entry"><para role="column_definition">
1387 <structfield>flush_lag</structfield> <type>interval</type>
1388 </para>
1389 <para>
1390 Time elapsed between flushing recent WAL locally and receiving
1391 notification that this standby server has written and flushed it
1392 (but not yet applied it). This can be used to gauge the delay that
1393 <literal>synchronous_commit</literal> level
1394 <literal>on</literal> incurred while committing if this
1395 server was configured as a synchronous standby.
1396 </para></entry>
1397 </row>
1398
1399 <row>
1400 <entry role="catalog_table_entry"><para role="column_definition">
1401 <structfield>replay_lag</structfield> <type>interval</type>
1402 </para>
1403 <para>
1404 Time elapsed between flushing recent WAL locally and receiving
1405 notification that this standby server has written, flushed and
1406 applied it. This can be used to gauge the delay that
1407 <literal>synchronous_commit</literal> level
1408 <literal>remote_apply</literal> incurred while committing if this
1409 server was configured as a synchronous standby.
1410 </para></entry>
1411 </row>
1412
1413 <row>
1414 <entry role="catalog_table_entry"><para role="column_definition">
1415 <structfield>sync_priority</structfield> <type>integer</type>
1416 </para>
1417 <para>
1418 Priority of this standby server for being chosen as the
1419 synchronous standby in a priority-based synchronous replication.
1420 This has no effect in a quorum-based synchronous replication.
1421 </para></entry>
1422 </row>
1423
1424 <row>
1425 <entry role="catalog_table_entry"><para role="column_definition">
1426 <structfield>sync_state</structfield> <type>text</type>
1427 </para>
1428 <para>
1429 Synchronous state of this standby server.
1430 Possible values are:
1431 <itemizedlist>
1432 <listitem>
1433 <para>
1434 <literal>async</literal>: This standby server is asynchronous.
1435 </para>
1436 </listitem>
1437 <listitem>
1438 <para>
1439 <literal>potential</literal>: This standby server is now asynchronous,
1440 but can potentially become synchronous if one of current
1441 synchronous ones fails.
1442 </para>
1443 </listitem>
1444 <listitem>
1445 <para>
1446 <literal>sync</literal>: This standby server is synchronous.
1447 </para>
1448 </listitem>
1449 <listitem>
1450 <para>
1451 <literal>quorum</literal>: This standby server is considered as a candidate
1452 for quorum standbys.
1453 </para>
1454 </listitem>
1455 </itemizedlist>
1456 </para></entry>
1457 </row>
1458
1459 <row>
1460 <entry role="catalog_table_entry"><para role="column_definition">
1461 <structfield>reply_time</structfield> <type>timestamp with time zone</type>
1462 </para>
1463 <para>
1464 Send time of last reply message received from standby server
1465 </para></entry>
1466 </row>
1467 </tbody>
1468 </tgroup>
1469 </table>
1470
1471 <para>
1472 The lag times reported in the <structname>pg_stat_replication</structname>
1473 view are measurements of the time taken for recent WAL to be written,
1474 flushed and replayed and for the sender to know about it. These times
1475 represent the commit delay that was (or would have been) introduced by each
1476 synchronous commit level, if the remote server was configured as a
1477 synchronous standby. For an asynchronous standby, the
1478 <structfield>replay_lag</structfield> column approximates the delay
1479 before recent transactions became visible to queries. If the standby
1480 server has entirely caught up with the sending server and there is no more
1481 WAL activity, the most recently measured lag times will continue to be
1482 displayed for a short time and then show NULL.
1483 </para>
1484
1485 <para>
1486 Lag times work automatically for physical replication. Logical decoding
1487 plugins may optionally emit tracking messages; if they do not, the tracking
1488 mechanism will simply display NULL lag.
1489 </para>
1490
1491 <note>
1492 <para>
1493 The reported lag times are not predictions of how long it will take for
1494 the standby to catch up with the sending server assuming the current
1495 rate of replay. Such a system would show similar times while new WAL is
1496 being generated, but would differ when the sender becomes idle. In
1497 particular, when the standby has caught up completely,
1498 <structname>pg_stat_replication</structname> shows the time taken to
1499 write, flush and replay the most recent reported WAL location rather than
1500 zero as some users might expect. This is consistent with the goal of
1501 measuring synchronous commit and transaction visibility delays for
1502 recent write transactions.
1503 To reduce confusion for users expecting a different model of lag, the
1504 lag columns revert to NULL after a short time on a fully replayed idle
1505 system. Monitoring systems should choose whether to represent this
1506 as missing data, zero or continue to display the last known value.
1507 </para>
1508 </note>
1509
1510 </sect2>
1511
1512 <sect2 id="monitoring-pg-stat-replication-slots-view">
1513 <title><structname>pg_stat_replication_slots</structname></title>
1514
1515 <indexterm>
1516 <primary>pg_stat_replication_slots</primary>
1517 </indexterm>
1518
1519 <para>
1520 The <structname>pg_stat_replication_slots</structname> view will contain
1521 one row per logical replication slot, showing statistics about its usage.
1522 </para>
1523
1524 <table id="pg-stat-replication-slots-view" xreflabel="pg_stat_replication_slots">
1525 <title><structname>pg_stat_replication_slots</structname> View</title>
1526 <tgroup cols="1">
1527 <thead>
1528 <row>
1529 <entry role="catalog_table_entry"><para role="column_definition">
1530 Column Type
1531 </para>
1532 <para>
1533 Description
1534 </para></entry>
1535 </row>
1536 </thead>
1537
1538 <tbody>
1539 <row>
1540 <entry role="catalog_table_entry"><para role="column_definition">
1541 <structfield>slot_name</structfield> <type>text</type>
1542 </para>
1543 <para>
1544 A unique, cluster-wide identifier for the replication slot
1545 </para></entry>
1546 </row>
1547
1548 <row>
1549 <entry role="catalog_table_entry"><para role="column_definition">
1550 <structfield>spill_txns</structfield> <type>bigint</type>
1551 </para>
1552 <para>
1553 Number of transactions spilled to disk once the memory used by
1554 logical decoding to decode changes from WAL has exceeded
1555 <literal>logical_decoding_work_mem</literal>. The counter gets
1556 incremented for both top-level transactions and subtransactions.
1557 </para></entry>
1558 </row>
1559
1560 <row>
1561 <entry role="catalog_table_entry"><para role="column_definition">
1562 <structfield>spill_count</structfield> <type>bigint</type>
1563 </para>
1564 <para>
1565 Number of times transactions were spilled to disk while decoding
1566 changes from WAL for this slot. This counter is incremented each time
1567 a transaction is spilled, and the same transaction may be spilled
1568 multiple times.
1569 </para></entry>
1570 </row>
1571
1572 <row>
1573 <entry role="catalog_table_entry"><para role="column_definition">
1574 <structfield>spill_bytes</structfield> <type>bigint</type>
1575 </para>
1576 <para>
1577 Amount of decoded transaction data spilled to disk while performing
1578 decoding of changes from WAL for this slot. This and other spill
1579 counters can be used to gauge the I/O which occurred during logical
1580 decoding and allow tuning <literal>logical_decoding_work_mem</literal>.
1581 </para></entry>
1582 </row>
1583
1584 <row>
1585 <entry role="catalog_table_entry"><para role="column_definition">
1586 <structfield>stream_txns</structfield> <type>bigint</type>
1587 </para>
1588 <para>
1589 Number of in-progress transactions streamed to the decoding output
1590 plugin after the memory used by logical decoding to decode changes
1591 from WAL for this slot has exceeded
1592 <literal>logical_decoding_work_mem</literal>. Streaming only
1593 works with top-level transactions (subtransactions can't be streamed
1594 independently), so the counter is not incremented for subtransactions.
1595 </para></entry>
1596 </row>
1597
1598 <row>
1599 <entry role="catalog_table_entry"><para role="column_definition">
1600 <structfield>stream_count</structfield><type>bigint</type>
1601 </para>
1602 <para>
1603 Number of times in-progress transactions were streamed to the decoding
1604 output plugin while decoding changes from WAL for this slot. This
1605 counter is incremented each time a transaction is streamed, and the
1606 same transaction may be streamed multiple times.
1607 </para></entry>
1608 </row>
1609
1610 <row>
1611 <entry role="catalog_table_entry"><para role="column_definition">
1612 <structfield>stream_bytes</structfield><type>bigint</type>
1613 </para>
1614 <para>
1615 Amount of transaction data decoded for streaming in-progress
1616 transactions to the decoding output plugin while decoding changes from
1617 WAL for this slot. This and other streaming counters for this slot can
1618 be used to tune <literal>logical_decoding_work_mem</literal>.
1619 </para>
1620 </entry>
1621 </row>
1622
1623 <row>
1624 <entry role="catalog_table_entry"><para role="column_definition">
1625 <structfield>total_txns</structfield> <type>bigint</type>
1626 </para>
1627 <para>
1628 Number of decoded transactions sent to the decoding output plugin for
1629 this slot. This counts top-level transactions only, and is not incremented
1630 for subtransactions. Note that this includes the transactions that are
1631 streamed and/or spilled.
1632 </para></entry>
1633 </row>
1634
1635 <row>
1636 <entry role="catalog_table_entry"><para role="column_definition">
1637 <structfield>total_bytes</structfield><type>bigint</type>
1638 </para>
1639 <para>
1640 Amount of transaction data decoded for sending transactions to the
1641 decoding output plugin while decoding changes from WAL for this slot.
1642 Note that this includes data that is streamed and/or spilled.
1643 </para>
1644 </entry>
1645 </row>
1646
1647 <row>
1648 <entry role="catalog_table_entry"><para role="column_definition">
1649 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
1650 </para>
1651 <para>
1652 Time at which these statistics were last reset
1653 </para></entry>
1654 </row>
1655 </tbody>
1656 </tgroup>
1657 </table>
1658
1659 </sect2>
1660
1661 <sect2 id="monitoring-pg-stat-wal-receiver-view">
1662 <title><structname>pg_stat_wal_receiver</structname></title>
1663
1664 <indexterm>
1665 <primary>pg_stat_wal_receiver</primary>
1666 </indexterm>
1667
1668 <para>
1669 The <structname>pg_stat_wal_receiver</structname> view will contain only
1670 one row, showing statistics about the WAL receiver from that receiver's
1671 connected server.
1672 </para>
1673
1674 <table id="pg-stat-wal-receiver-view" xreflabel="pg_stat_wal_receiver">
1675 <title><structname>pg_stat_wal_receiver</structname> View</title>
1676 <tgroup cols="1">
1677 <thead>
1678 <row>
1679 <entry role="catalog_table_entry"><para role="column_definition">
1680 Column Type
1681 </para>
1682 <para>
1683 Description
1684 </para></entry>
1685 </row>
1686 </thead>
1687
1688 <tbody>
1689 <row>
1690 <entry role="catalog_table_entry"><para role="column_definition">
1691 <structfield>pid</structfield> <type>integer</type>
1692 </para>
1693 <para>
1694 Process ID of the WAL receiver process
1695 </para></entry>
1696 </row>
1697
1698 <row>
1699 <entry role="catalog_table_entry"><para role="column_definition">
1700 <structfield>status</structfield> <type>text</type>
1701 </para>
1702 <para>
1703 Activity status of the WAL receiver process
1704 </para></entry>
1705 </row>
1706
1707 <row>
1708 <entry role="catalog_table_entry"><para role="column_definition">
1709 <structfield>receive_start_lsn</structfield> <type>pg_lsn</type>
1710 </para>
1711 <para>
1712 First write-ahead log location used when WAL receiver is
1713 started
1714 </para></entry>
1715 </row>
1716
1717 <row>
1718 <entry role="catalog_table_entry"><para role="column_definition">
1719 <structfield>receive_start_tli</structfield> <type>integer</type>
1720 </para>
1721 <para>
1722 First timeline number used when WAL receiver is started
1723 </para></entry>
1724 </row>
1725
1726 <row>
1727 <entry role="catalog_table_entry"><para role="column_definition">
1728 <structfield>written_lsn</structfield> <type>pg_lsn</type>
1729 </para>
1730 <para>
1731 Last write-ahead log location already received and written to disk,
1732 but not flushed. This should not be used for data integrity checks.
1733 </para></entry>
1734 </row>
1735
1736 <row>
1737 <entry role="catalog_table_entry"><para role="column_definition">
1738 <structfield>flushed_lsn</structfield> <type>pg_lsn</type>
1739 </para>
1740 <para>
1741 Last write-ahead log location already received and flushed to
1742 disk, the initial value of this field being the first log location used
1743 when WAL receiver is started
1744 </para></entry>
1745 </row>
1746
1747 <row>
1748 <entry role="catalog_table_entry"><para role="column_definition">
1749 <structfield>received_tli</structfield> <type>integer</type>
1750 </para>
1751 <para>
1752 Timeline number of last write-ahead log location received and
1753 flushed to disk, the initial value of this field being the timeline
1754 number of the first log location used when WAL receiver is started
1755 </para></entry>
1756 </row>
1757
1758 <row>
1759 <entry role="catalog_table_entry"><para role="column_definition">
1760 <structfield>last_msg_send_time</structfield> <type>timestamp with time zone</type>
1761 </para>
1762 <para>
1763 Send time of last message received from origin WAL sender
1764 </para></entry>
1765 </row>
1766
1767 <row>
1768 <entry role="catalog_table_entry"><para role="column_definition">
1769 <structfield>last_msg_receipt_time</structfield> <type>timestamp with time zone</type>
1770 </para>
1771 <para>
1772 Receipt time of last message received from origin WAL sender
1773 </para></entry>
1774 </row>
1775
1776 <row>
1777 <entry role="catalog_table_entry"><para role="column_definition">
1778 <structfield>latest_end_lsn</structfield> <type>pg_lsn</type>
1779 </para>
1780 <para>
1781 Last write-ahead log location reported to origin WAL sender
1782 </para></entry>
1783 </row>
1784
1785 <row>
1786 <entry role="catalog_table_entry"><para role="column_definition">
1787 <structfield>latest_end_time</structfield> <type>timestamp with time zone</type>
1788 </para>
1789 <para>
1790 Time of last write-ahead log location reported to origin WAL sender
1791 </para></entry>
1792 </row>
1793
1794 <row>
1795 <entry role="catalog_table_entry"><para role="column_definition">
1796 <structfield>slot_name</structfield> <type>text</type>
1797 </para>
1798 <para>
1799 Replication slot name used by this WAL receiver
1800 </para></entry>
1801 </row>
1802
1803 <row>
1804 <entry role="catalog_table_entry"><para role="column_definition">
1805 <structfield>sender_host</structfield> <type>text</type>
1806 </para>
1807 <para>
1808 Host of the <productname>PostgreSQL</productname> instance
1809 this WAL receiver is connected to. This can be a host name,
1810 an IP address, or a directory path if the connection is via
1811 Unix socket. (The path case can be distinguished because it
1812 will always be an absolute path, beginning with <literal>/</literal>.)
1813 </para></entry>
1814 </row>
1815
1816 <row>
1817 <entry role="catalog_table_entry"><para role="column_definition">
1818 <structfield>sender_port</structfield> <type>integer</type>
1819 </para>
1820 <para>
1821 Port number of the <productname>PostgreSQL</productname> instance
1822 this WAL receiver is connected to.
1823 </para></entry>
1824 </row>
1825
1826 <row>
1827 <entry role="catalog_table_entry"><para role="column_definition">
1828 <structfield>conninfo</structfield> <type>text</type>
1829 </para>
1830 <para>
1831 Connection string used by this WAL receiver,
1832 with security-sensitive fields obfuscated.
1833 </para></entry>
1834 </row>
1835 </tbody>
1836 </tgroup>
1837 </table>
1838
1839 </sect2>
1840
1841 <sect2 id="monitoring-pg-stat-recovery-prefetch">
1842 <title><structname>pg_stat_recovery_prefetch</structname></title>
1843
1844 <indexterm>
1845 <primary>pg_stat_recovery_prefetch</primary>
1846 </indexterm>
1847
1848 <para>
1849 The <structname>pg_stat_recovery_prefetch</structname> view will contain
1850 only one row. The columns <structfield>wal_distance</structfield>,
1851 <structfield>block_distance</structfield> and
1852 <structfield>io_depth</structfield> show current values, and the
1853 other columns show cumulative counters that can be reset
1854 with the <function>pg_stat_reset_shared</function> function.
1855 </para>
1856
1857 <table id="pg-stat-recovery-prefetch-view" xreflabel="pg_stat_recovery_prefetch">
1858 <title><structname>pg_stat_recovery_prefetch</structname> View</title>
1859 <tgroup cols="1">
1860 <thead>
1861 <row>
1862 <entry role="catalog_table_entry"><para role="column_definition">
1863 Column Type
1864 </para>
1865 <para>
1866 Description
1867 </para></entry>
1868 </row>
1869 </thead>
1870
1871 <tbody>
1872 <row>
1873 <entry role="catalog_table_entry">
1874 <para role="column_definition">
1875 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
1876 </para>
1877 <para>
1878 Time at which these statistics were last reset
1879 </para>
1880 </entry>
1881 </row>
1882
1883 <row>
1884 <entry role="catalog_table_entry">
1885 <para role="column_definition">
1886 <structfield>prefetch</structfield> <type>bigint</type>
1887 </para>
1888 <para>
1889 Number of blocks prefetched because they were not in the buffer pool
1890 </para>
1891 </entry>
1892 </row>
1893
1894 <row>
1895 <entry role="catalog_table_entry">
1896 <para role="column_definition">
1897 <structfield>hit</structfield> <type>bigint</type>
1898 </para>
1899 <para>
1900 Number of blocks not prefetched because they were already in the buffer pool
1901 </para>
1902 </entry>
1903 </row>
1904
1905 <row>
1906 <entry role="catalog_table_entry">
1907 <para role="column_definition">
1908 <structfield>skip_init</structfield> <type>bigint</type>
1909 </para>
1910 <para>
1911 Number of blocks not prefetched because they would be zero-initialized
1912 </para>
1913 </entry>
1914 </row>
1915
1916 <row>
1917 <entry role="catalog_table_entry">
1918 <para role="column_definition">
1919 <structfield>skip_new</structfield> <type>bigint</type>
1920 </para>
1921 <para>
1922 Number of blocks not prefetched because they didn't exist yet
1923 </para>
1924 </entry>
1925 </row>
1926
1927 <row>
1928 <entry role="catalog_table_entry">
1929 <para role="column_definition">
1930 <structfield>skip_fpw</structfield> <type>bigint</type>
1931 </para>
1932 <para>
1933 Number of blocks not prefetched because a full page image was included in the WAL
1934 </para>
1935 </entry>
1936 </row>
1937
1938 <row>
1939 <entry role="catalog_table_entry">
1940 <para role="column_definition">
1941 <structfield>skip_rep</structfield> <type>bigint</type>
1942 </para>
1943 <para>
1944 Number of blocks not prefetched because they were already recently prefetched
1945 </para>
1946 </entry>
1947 </row>
1948
1949 <row>
1950 <entry role="catalog_table_entry">
1951 <para role="column_definition">
1952 <structfield>wal_distance</structfield> <type>int</type>
1953 </para>
1954 <para>
1955 How many bytes ahead the prefetcher is looking
1956 </para>
1957 </entry>
1958 </row>
1959
1960 <row>
1961 <entry role="catalog_table_entry">
1962 <para role="column_definition">
1963 <structfield>block_distance</structfield> <type>int</type>
1964 </para>
1965 <para>
1966 How many blocks ahead the prefetcher is looking
1967 </para>
1968 </entry>
1969 </row>
1970
1971 <row>
1972 <entry role="catalog_table_entry">
1973 <para role="column_definition">
1974 <structfield>io_depth</structfield> <type>int</type>
1975 </para>
1976 <para>
1977 How many prefetches have been initiated but are not yet known to have completed
1978 </para>
1979 </entry>
1980 </row>
1981 </tbody>
1982 </tgroup>
1983 </table>
1984
1985 </sect2>
1986
1987 <sect2 id="monitoring-pg-stat-subscription">
1988 <title><structname>pg_stat_subscription</structname></title>
1989
1990 <indexterm>
1991 <primary>pg_stat_subscription</primary>
1992 </indexterm>
1993
1994 <table id="pg-stat-subscription" xreflabel="pg_stat_subscription">
1995 <title><structname>pg_stat_subscription</structname> View</title>
1996 <tgroup cols="1">
1997 <thead>
1998 <row>
1999 <entry role="catalog_table_entry"><para role="column_definition">
2000 Column Type
2001 </para>
2002 <para>
2003 Description
2004 </para></entry>
2005 </row>
2006 </thead>
2007
2008 <tbody>
2009 <row>
2010 <entry role="catalog_table_entry"><para role="column_definition">
2011 <structfield>subid</structfield> <type>oid</type>
2012 </para>
2013 <para>
2014 OID of the subscription
2015 </para></entry>
2016 </row>
2017
2018 <row>
2019 <entry role="catalog_table_entry"><para role="column_definition">
2020 <structfield>subname</structfield> <type>name</type>
2021 </para>
2022 <para>
2023 Name of the subscription
2024 </para></entry>
2025 </row>
2026
2027 <row>
2028 <entry role="catalog_table_entry"><para role="column_definition">
2029 <structfield>worker_type</structfield> <type>text</type>
2030 </para>
2031 <para>
2032 Type of the subscription worker process. Possible types are
2033 <literal>apply</literal>, <literal>parallel apply</literal>, and
2034 <literal>table synchronization</literal>.
2035 </para></entry>
2036 </row>
2037
2038 <row>
2039 <entry role="catalog_table_entry"><para role="column_definition">
2040 <structfield>pid</structfield> <type>integer</type>
2041 </para>
2042 <para>
2043 Process ID of the subscription worker process
2044 </para></entry>
2045 </row>
2046
2047 <row>
2048 <entry role="catalog_table_entry"><para role="column_definition">
2049 <structfield>leader_pid</structfield> <type>integer</type>
2050 </para>
2051 <para>
2052 Process ID of the leader apply worker if this process is a parallel
2053 apply worker; NULL if this process is a leader apply worker or a table
2054 synchronization worker
2055 </para></entry>
2056 </row>
2057
2058 <row>
2059 <entry role="catalog_table_entry"><para role="column_definition">
2060 <structfield>relid</structfield> <type>oid</type>
2061 </para>
2062 <para>
2063 OID of the relation that the worker is synchronizing; NULL for the
2064 leader apply worker and parallel apply workers
2065 </para></entry>
2066 </row>
2067
2068 <row>
2069 <entry role="catalog_table_entry"><para role="column_definition">
2070 <structfield>received_lsn</structfield> <type>pg_lsn</type>
2071 </para>
2072 <para>
2073 Last write-ahead log location received, the initial value of
2074 this field being 0; NULL for parallel apply workers
2075 </para></entry>
2076 </row>
2077
2078 <row>
2079 <entry role="catalog_table_entry"><para role="column_definition">
2080 <structfield>last_msg_send_time</structfield> <type>timestamp with time zone</type>
2081 </para>
2082 <para>
2083 Send time of last message received from origin WAL sender; NULL for
2084 parallel apply workers
2085 </para></entry>
2086 </row>
2087
2088 <row>
2089 <entry role="catalog_table_entry"><para role="column_definition">
2090 <structfield>last_msg_receipt_time</structfield> <type>timestamp with time zone</type>
2091 </para>
2092 <para>
2093 Receipt time of last message received from origin WAL sender; NULL for
2094 parallel apply workers
2095 </para></entry>
2096 </row>
2097
2098 <row>
2099 <entry role="catalog_table_entry"><para role="column_definition">
2100 <structfield>latest_end_lsn</structfield> <type>pg_lsn</type>
2101 </para>
2102 <para>
2103 Last write-ahead log location reported to origin WAL sender; NULL for
2104 parallel apply workers
2105 </para></entry>
2106 </row>
2107
2108 <row>
2109 <entry role="catalog_table_entry"><para role="column_definition">
2110 <structfield>latest_end_time</structfield> <type>timestamp with time zone</type>
2111 </para>
2112 <para>
2113 Time of last write-ahead log location reported to origin WAL
2114 sender; NULL for parallel apply workers
2115 </para></entry>
2116 </row>
2117 </tbody>
2118 </tgroup>
2119 </table>
2120
2121 </sect2>
2122
2123 <sect2 id="monitoring-pg-stat-subscription-stats">
2124 <title><structname>pg_stat_subscription_stats</structname></title>
2125
2126 <indexterm>
2127 <primary>pg_stat_subscription_stats</primary>
2128 </indexterm>
2129
2130 <para>
2131 The <structname>pg_stat_subscription_stats</structname> view will contain
2132 one row per subscription.
2133 </para>
2134
2135 <table id="pg-stat-subscription-stats" xreflabel="pg_stat_subscription_stats">
2136 <title><structname>pg_stat_subscription_stats</structname> View</title>
2137 <tgroup cols="1">
2138 <thead>
2139 <row>
2140 <entry role="catalog_table_entry"><para role="column_definition">
2141 Column Type
2142 </para>
2143 <para>
2144 Description
2145 </para></entry>
2146 </row>
2147 </thead>
2148
2149 <tbody>
2150 <row>
2151 <entry role="catalog_table_entry"><para role="column_definition">
2152 <structfield>subid</structfield> <type>oid</type>
2153 </para>
2154 <para>
2155 OID of the subscription
2156 </para></entry>
2157 </row>
2158
2159 <row>
2160 <entry role="catalog_table_entry"><para role="column_definition">
2161 <structfield>subname</structfield> <type>name</type>
2162 </para>
2163 <para>
2164 Name of the subscription
2165 </para></entry>
2166 </row>
2167
2168 <row>
2169 <entry role="catalog_table_entry"><para role="column_definition">
2170 <structfield>apply_error_count</structfield> <type>bigint</type>
2171 </para>
2172 <para>
2173 Number of times an error occurred while applying changes. Note that any
2174 conflict resulting in an apply error will be counted in both
2175 <literal>apply_error_count</literal> and the corresponding conflict
2176 count (e.g., <literal>confl_*</literal>).
2177 </para></entry>
2178 </row>
2179
2180 <row>
2181 <entry role="catalog_table_entry"><para role="column_definition">
2182 <structfield>sync_error_count</structfield> <type>bigint</type>
2183 </para>
2184 <para>
2185 Number of times an error occurred during the initial table
2186 synchronization
2187 </para></entry>
2188 </row>
2189
2190 <row>
2191 <entry role="catalog_table_entry"><para role="column_definition">
2192 <structfield>confl_insert_exists</structfield> <type>bigint</type>
2193 </para>
2194 <para>
2195 Number of times a row insertion violated a
2196 <literal>NOT DEFERRABLE</literal> unique constraint during the
2197 application of changes. See <xref linkend="conflict-insert-exists"/>
2198 for details about this conflict.
2199 </para></entry>
2200 </row>
2201
2202 <row>
2203 <entry role="catalog_table_entry"><para role="column_definition">
2204 <structfield>confl_update_origin_differs</structfield> <type>bigint</type>
2205 </para>
2206 <para>
2207 Number of times an update was applied to a row that had been previously
2208 modified by another source during the application of changes. See
2209 <xref linkend="conflict-update-origin-differs"/> for details about this
2210 conflict.
2211 </para></entry>
2212 </row>
2213
2214 <row>
2215 <entry role="catalog_table_entry"><para role="column_definition">
2216 <structfield>confl_update_exists</structfield> <type>bigint</type>
2217 </para>
2218 <para>
2219 Number of times that an updated row value violated a
2220 <literal>NOT DEFERRABLE</literal> unique constraint during the
2221 application of changes. See <xref linkend="conflict-update-exists"/>
2222 for details about this conflict.
2223 </para></entry>
2224 </row>
2225
2226 <row>
2227 <entry role="catalog_table_entry"><para role="column_definition">
2228 <structfield>confl_update_missing</structfield> <type>bigint</type>
2229 </para>
2230 <para>
2231 Number of times the tuple to be updated was not found during the
2232 application of changes. See <xref linkend="conflict-update-missing"/>
2233 for details about this conflict.
2234 </para></entry>
2235 </row>
2236
2237 <row>
2238 <entry role="catalog_table_entry"><para role="column_definition">
2239 <structfield>confl_delete_origin_differs</structfield> <type>bigint</type>
2240 </para>
2241 <para>
2242 Number of times a delete operation was applied to row that had been
2243 previously modified by another source during the application of changes.
2244 See <xref linkend="conflict-delete-origin-differs"/> for details about
2245 this conflict.
2246 </para></entry>
2247 </row>
2248
2249 <row>
2250 <entry role="catalog_table_entry"><para role="column_definition">
2251 <structfield>confl_delete_missing</structfield> <type>bigint</type>
2252 </para>
2253 <para>
2254 Number of times the tuple to be deleted was not found during the application
2255 of changes. See <xref linkend="conflict-delete-missing"/> for details
2256 about this conflict.
2257 </para></entry>
2258 </row>
2259
2260 <row>
2261 <entry role="catalog_table_entry"><para role="column_definition">
2262 <structfield>confl_multiple_unique_conflicts</structfield> <type>bigint</type>
2263 </para>
2264 <para>
2265 Number of times a row insertion or an updated row values violated multiple
2266 <literal>NOT DEFERRABLE</literal> unique constraints during the
2267 application of changes. See <xref linkend="conflict-multiple-unique-conflicts"/>
2268 for details about this conflict.
2269 </para></entry>
2270 </row>
2271
2272 <row>
2273 <entry role="catalog_table_entry"><para role="column_definition">
2274 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
2275 </para>
2276 <para>
2277 Time at which these statistics were last reset
2278 </para></entry>
2279 </row>
2280 </tbody>
2281 </tgroup>
2282 </table>
2283
2284 </sect2>
2285
2286 <sect2 id="monitoring-pg-stat-ssl-view">
2287 <title><structname>pg_stat_ssl</structname></title>
2288
2289 <indexterm>
2290 <primary>pg_stat_ssl</primary>
2291 </indexterm>
2292
2293 <para>
2294 The <structname>pg_stat_ssl</structname> view will contain one row per
2295 backend or WAL sender process, showing statistics about SSL usage on
2296 this connection. It can be joined to <structname>pg_stat_activity</structname>
2297 or <structname>pg_stat_replication</structname> on the
2298 <structfield>pid</structfield> column to get more details about the
2299 connection.
2300 </para>
2301
2302 <table id="pg-stat-ssl-view" xreflabel="pg_stat_ssl">
2303 <title><structname>pg_stat_ssl</structname> View</title>
2304 <tgroup cols="1">
2305 <thead>
2306 <row>
2307 <entry role="catalog_table_entry"><para role="column_definition">
2308 Column Type
2309 </para>
2310 <para>
2311 Description
2312 </para></entry>
2313 </row>
2314 </thead>
2315
2316 <tbody>
2317 <row>
2318 <entry role="catalog_table_entry"><para role="column_definition">
2319 <structfield>pid</structfield> <type>integer</type>
2320 </para>
2321 <para>
2322 Process ID of a backend or WAL sender process
2323 </para></entry>
2324 </row>
2325
2326 <row>
2327 <entry role="catalog_table_entry"><para role="column_definition">
2328 <structfield>ssl</structfield> <type>boolean</type>
2329 </para>
2330 <para>
2331 True if SSL is used on this connection
2332 </para></entry>
2333 </row>
2334
2335 <row>
2336 <entry role="catalog_table_entry"><para role="column_definition">
2337 <structfield>version</structfield> <type>text</type>
2338 </para>
2339 <para>
2340 Version of SSL in use, or NULL if SSL is not in use
2341 on this connection
2342 </para></entry>
2343 </row>
2344
2345 <row>
2346 <entry role="catalog_table_entry"><para role="column_definition">
2347 <structfield>cipher</structfield> <type>text</type>
2348 </para>
2349 <para>
2350 Name of SSL cipher in use, or NULL if SSL is not in use
2351 on this connection
2352 </para></entry>
2353 </row>
2354
2355 <row>
2356 <entry role="catalog_table_entry"><para role="column_definition">
2357 <structfield>bits</structfield> <type>integer</type>
2358 </para>
2359 <para>
2360 Number of bits in the encryption algorithm used, or NULL
2361 if SSL is not used on this connection
2362 </para></entry>
2363 </row>
2364
2365 <row>
2366 <entry role="catalog_table_entry"><para role="column_definition">
2367 <structfield>client_dn</structfield> <type>text</type>
2368 </para>
2369 <para>
2370 Distinguished Name (DN) field from the client certificate
2371 used, or NULL if no client certificate was supplied or if SSL
2372 is not in use on this connection. This field is truncated if the
2373 DN field is longer than <symbol>NAMEDATALEN</symbol> (64 characters
2374 in a standard build).
2375 </para></entry>
2376 </row>
2377
2378 <row>
2379 <entry role="catalog_table_entry"><para role="column_definition">
2380 <structfield>client_serial</structfield> <type>numeric</type>
2381 </para>
2382 <para>
2383 Serial number of the client certificate, or NULL if no client
2384 certificate was supplied or if SSL is not in use on this connection. The
2385 combination of certificate serial number and certificate issuer uniquely
2386 identifies a certificate (unless the issuer erroneously reuses serial
2387 numbers).
2388 </para></entry>
2389 </row>
2390
2391 <row>
2392 <entry role="catalog_table_entry"><para role="column_definition">
2393 <structfield>issuer_dn</structfield> <type>text</type>
2394 </para>
2395 <para>
2396 DN of the issuer of the client certificate, or NULL if no client
2397 certificate was supplied or if SSL is not in use on this connection.
2398 This field is truncated like <structfield>client_dn</structfield>.
2399 </para></entry>
2400 </row>
2401 </tbody>
2402 </tgroup>
2403 </table>
2404
2405 </sect2>
2406
2407 <sect2 id="monitoring-pg-stat-gssapi-view">
2408 <title><structname>pg_stat_gssapi</structname></title>
2409
2410 <indexterm>
2411 <primary>pg_stat_gssapi</primary>
2412 </indexterm>
2413
2414 <para>
2415 The <structname>pg_stat_gssapi</structname> view will contain one row per
2416 backend, showing information about GSSAPI usage on this connection. It can
2417 be joined to <structname>pg_stat_activity</structname> or
2418 <structname>pg_stat_replication</structname> on the
2419 <structfield>pid</structfield> column to get more details about the
2420 connection.
2421 </para>
2422
2423 <table id="pg-stat-gssapi-view" xreflabel="pg_stat_gssapi">
2424 <title><structname>pg_stat_gssapi</structname> View</title>
2425 <tgroup cols="1">
2426 <thead>
2427 <row>
2428 <entry role="catalog_table_entry"><para role="column_definition">
2429 Column Type
2430 </para>
2431 <para>
2432 Description
2433 </para></entry>
2434 </row>
2435 </thead>
2436
2437 <tbody>
2438 <row>
2439 <entry role="catalog_table_entry"><para role="column_definition">
2440 <structfield>pid</structfield> <type>integer</type>
2441 </para>
2442 <para>
2443 Process ID of a backend
2444 </para></entry>
2445 </row>
2446
2447 <row>
2448 <entry role="catalog_table_entry"><para role="column_definition">
2449 <structfield>gss_authenticated</structfield> <type>boolean</type>
2450 </para>
2451 <para>
2452 True if GSSAPI authentication was used for this connection
2453 </para></entry>
2454 </row>
2455
2456 <row>
2457 <entry role="catalog_table_entry"><para role="column_definition">
2458 <structfield>principal</structfield> <type>text</type>
2459 </para>
2460 <para>
2461 Principal used to authenticate this connection, or NULL
2462 if GSSAPI was not used to authenticate this connection. This
2463 field is truncated if the principal is longer than
2464 <symbol>NAMEDATALEN</symbol> (64 characters in a standard build).
2465 </para></entry>
2466 </row>
2467
2468 <row>
2469 <entry role="catalog_table_entry"><para role="column_definition">
2470 <structfield>encrypted</structfield> <type>boolean</type>
2471 </para>
2472 <para>
2473 True if GSSAPI encryption is in use on this connection
2474 </para></entry>
2475 </row>
2476
2477 <row>
2478 <entry role="catalog_table_entry"><para role="column_definition">
2479 <structfield>credentials_delegated</structfield> <type>boolean</type>
2480 </para>
2481 <para>
2482 True if GSSAPI credentials were delegated on this connection.
2483 </para></entry>
2484 </row>
2485 </tbody>
2486 </tgroup>
2487 </table>
2488
2489 </sect2>
2490
2491 <sect2 id="monitoring-pg-stat-archiver-view">
2492 <title><structname>pg_stat_archiver</structname></title>
2493
2494 <indexterm>
2495 <primary>pg_stat_archiver</primary>
2496 </indexterm>
2497
2498 <para>
2499 The <structname>pg_stat_archiver</structname> view will always have a
2500 single row, containing data about the archiver process of the cluster.
2501 </para>
2502
2503 <table id="pg-stat-archiver-view" xreflabel="pg_stat_archiver">
2504 <title><structname>pg_stat_archiver</structname> View</title>
2505 <tgroup cols="1">
2506 <thead>
2507 <row>
2508 <entry role="catalog_table_entry"><para role="column_definition">
2509 Column Type
2510 </para>
2511 <para>
2512 Description
2513 </para></entry>
2514 </row>
2515 </thead>
2516
2517 <tbody>
2518 <row>
2519 <entry role="catalog_table_entry"><para role="column_definition">
2520 <structfield>archived_count</structfield> <type>bigint</type>
2521 </para>
2522 <para>
2523 Number of WAL files that have been successfully archived
2524 </para></entry>
2525 </row>
2526
2527 <row>
2528 <entry role="catalog_table_entry"><para role="column_definition">
2529 <structfield>last_archived_wal</structfield> <type>text</type>
2530 </para>
2531 <para>
2532 Name of the WAL file most recently successfully archived
2533 </para></entry>
2534 </row>
2535
2536 <row>
2537 <entry role="catalog_table_entry"><para role="column_definition">
2538 <structfield>last_archived_time</structfield> <type>timestamp with time zone</type>
2539 </para>
2540 <para>
2541 Time of the most recent successful archive operation
2542 </para></entry>
2543 </row>
2544
2545 <row>
2546 <entry role="catalog_table_entry"><para role="column_definition">
2547 <structfield>failed_count</structfield> <type>bigint</type>
2548 </para>
2549 <para>
2550 Number of failed attempts for archiving WAL files
2551 </para></entry>
2552 </row>
2553
2554 <row>
2555 <entry role="catalog_table_entry"><para role="column_definition">
2556 <structfield>last_failed_wal</structfield> <type>text</type>
2557 </para>
2558 <para>
2559 Name of the WAL file of the most recent failed archival operation
2560 </para></entry>
2561 </row>
2562
2563 <row>
2564 <entry role="catalog_table_entry"><para role="column_definition">
2565 <structfield>last_failed_time</structfield> <type>timestamp with time zone</type>
2566 </para>
2567 <para>
2568 Time of the most recent failed archival operation
2569 </para></entry>
2570 </row>
2571
2572 <row>
2573 <entry role="catalog_table_entry"><para role="column_definition">
2574 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
2575 </para>
2576 <para>
2577 Time at which these statistics were last reset
2578 </para></entry>
2579 </row>
2580 </tbody>
2581 </tgroup>
2582 </table>
2583
2584 <para>
2585 Normally, WAL files are archived in order, oldest to newest, but that is
2586 not guaranteed, and does not hold under special circumstances like when
2587 promoting a standby or after crash recovery. Therefore it is not safe to
2588 assume that all files older than
2589 <structfield>last_archived_wal</structfield> have also been successfully
2590 archived.
2591 </para>
2592 </sect2>
2593
2594 <sect2 id="monitoring-pg-stat-io-view">
2595 <title><structname>pg_stat_io</structname></title>
2596
2597 <indexterm>
2598 <primary>pg_stat_io</primary>
2599 </indexterm>
2600
2601 <para>
2602 The <structname>pg_stat_io</structname> view will contain one row for each
2603 combination of backend type, target I/O object, and I/O context, showing
2604 cluster-wide I/O statistics. Combinations which do not make sense are
2605 omitted.
2606 </para>
2607
2608 <para>
2609 Currently, I/O on relations (e.g. tables, indexes) and WAL activity are
2610 tracked. However, relation I/O which bypasses shared buffers
2611 (e.g. when moving a table from one tablespace to another) is currently
2612 not tracked.
2613 </para>
2614
2615 <table id="pg-stat-io-view" xreflabel="pg_stat_io">
2616 <title><structname>pg_stat_io</structname> View</title>
2617 <tgroup cols="1">
2618 <thead>
2619 <row>
2620 <entry role="catalog_table_entry">
2621 <para role="column_definition">
2622 Column Type
2623 </para>
2624 <para>
2625 Description
2626 </para>
2627 </entry>
2628 </row>
2629 </thead>
2630 <tbody>
2631 <row>
2632 <entry role="catalog_table_entry">
2633 <para role="column_definition">
2634 <structfield>backend_type</structfield> <type>text</type>
2635 </para>
2636 <para>
2637 Type of backend (e.g. background worker, autovacuum worker). See <link
2638 linkend="monitoring-pg-stat-activity-view">
2639 <structname>pg_stat_activity</structname></link> for more information
2640 on <varname>backend_type</varname>s. Some
2641 <varname>backend_type</varname>s do not accumulate I/O operation
2642 statistics and will not be included in the view.
2643 </para>
2644 </entry>
2645 </row>
2646
2647 <row>
2648 <entry role="catalog_table_entry">
2649 <para role="column_definition">
2650 <structfield>object</structfield> <type>text</type>
2651 </para>
2652 <para>
2653 Target object of an I/O operation. Possible values are:
2654 <itemizedlist>
2655 <listitem>
2656 <para>
2657 <literal>relation</literal>: Permanent relations.
2658 </para>
2659 </listitem>
2660 <listitem>
2661 <para>
2662 <literal>temp relation</literal>: Temporary relations.
2663 </para>
2664 </listitem>
2665 <listitem>
2666 <para>
2667 <literal>wal</literal>: Write Ahead Logs.
2668 </para>
2669 </listitem>
2670 </itemizedlist>
2671 </para>
2672 </entry>
2673 </row>
2674
2675 <row>
2676 <entry role="catalog_table_entry">
2677 <para role="column_definition">
2678 <structfield>context</structfield> <type>text</type>
2679 </para>
2680 <para>
2681 The context of an I/O operation. Possible values are:
2682 </para>
2683 <itemizedlist>
2684 <listitem>
2685 <para>
2686 <literal>normal</literal>: The default or standard
2687 <varname>context</varname> for a type of I/O operation. For
2688 example, by default, relation data is read into and written out from
2689 shared buffers. Thus, reads and writes of relation data to and from
2690 shared buffers are tracked in <varname>context</varname>
2691 <literal>normal</literal>.
2692 </para>
2693 </listitem>
2694 <listitem>
2695 <para>
2696 <literal>init</literal>: I/O operations performed while creating the
2697 WAL segments are tracked in <varname>context</varname>
2698 <literal>init</literal>.
2699 </para>
2700 </listitem>
2701 <listitem>
2702 <para>
2703 <literal>vacuum</literal>: I/O operations performed outside of shared
2704 buffers while vacuuming and analyzing permanent relations. Temporary
2705 table vacuums use the same local buffer pool as other temporary table
2706 I/O operations and are tracked in <varname>context</varname>
2707 <literal>normal</literal>.
2708 </para>
2709 </listitem>
2710 <listitem>
2711 <para>
2712 <literal>bulkread</literal>: Certain large read I/O operations
2713 done outside of shared buffers, for example, a sequential scan of a
2714 large table.
2715 </para>
2716 </listitem>
2717 <listitem>
2718 <para>
2719 <literal>bulkwrite</literal>: Certain large write I/O operations
2720 done outside of shared buffers, such as <command>COPY</command>.
2721 </para>
2722 </listitem>
2723 </itemizedlist>
2724 </entry>
2725 </row>
2726
2727 <row>
2728 <entry role="catalog_table_entry">
2729 <para role="column_definition">
2730 <structfield>reads</structfield> <type>bigint</type>
2731 </para>
2732 <para>
2733 Number of read operations.
2734 </para>
2735 </entry>
2736 </row>
2737
2738 <row>
2739 <entry role="catalog_table_entry">
2740 <para role="column_definition">
2741 <structfield>read_bytes</structfield> <type>numeric</type>
2742 </para>
2743 <para>
2744 The total size of read operations in bytes.
2745 </para>
2746 </entry>
2747 </row>
2748
2749 <row>
2750 <entry role="catalog_table_entry">
2751 <para role="column_definition">
2752 <structfield>read_time</structfield> <type>double precision</type>
2753 </para>
2754 <para>
2755 Time spent waiting for read operations in milliseconds (if
2756 <xref linkend="guc-track-io-timing"/> is enabled and
2757 <varname>object</varname> is not <literal>wal</literal>,
2758 or if <xref linkend="guc-track-wal-io-timing"/> is enabled
2759 and <varname>object</varname> is <literal>wal</literal>,
2760 otherwise zero)
2761 </para>
2762 </entry>
2763 </row>
2764
2765 <row>
2766 <entry role="catalog_table_entry">
2767 <para role="column_definition">
2768 <structfield>writes</structfield> <type>bigint</type>
2769 </para>
2770 <para>
2771 Number of write operations.
2772 </para>
2773 </entry>
2774 </row>
2775
2776 <row>
2777 <entry role="catalog_table_entry">
2778 <para role="column_definition">
2779 <structfield>write_bytes</structfield> <type>numeric</type>
2780 </para>
2781 <para>
2782 The total size of write operations in bytes.
2783 </para>
2784 </entry>
2785 </row>
2786
2787 <row>
2788 <entry role="catalog_table_entry">
2789 <para role="column_definition">
2790 <structfield>write_time</structfield> <type>double precision</type>
2791 </para>
2792 <para>
2793 Time spent waiting for write operations in milliseconds (if
2794 <xref linkend="guc-track-io-timing"/> is enabled and
2795 <varname>object</varname> is not <literal>wal</literal>,
2796 or if <xref linkend="guc-track-wal-io-timing"/> is enabled
2797 and <varname>object</varname> is <literal>wal</literal>,
2798 otherwise zero)
2799 </para>
2800 </entry>
2801 </row>
2802
2803 <row>
2804 <entry role="catalog_table_entry">
2805 <para role="column_definition">
2806 <structfield>writebacks</structfield> <type>bigint</type>
2807 </para>
2808 <para>
2809 Number of units of size <symbol>BLCKSZ</symbol> (typically 8kB) which
2810 the process requested the kernel write out to permanent storage.
2811 </para>
2812 </entry>
2813 </row>
2814
2815 <row>
2816 <entry role="catalog_table_entry">
2817 <para role="column_definition">
2818 <structfield>writeback_time</structfield> <type>double precision</type>
2819 </para>
2820 <para>
2821 Time spent waiting for writeback operations in milliseconds (if
2822 <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero). This
2823 includes the time spent queueing write-out requests and, potentially,
2824 the time spent to write out the dirty data.
2825 </para>
2826 </entry>
2827 </row>
2828
2829 <row>
2830 <entry role="catalog_table_entry">
2831 <para role="column_definition">
2832 <structfield>extends</structfield> <type>bigint</type>
2833 </para>
2834 <para>
2835 Number of relation extend operations.
2836 </para>
2837 </entry>
2838 </row>
2839
2840 <row>
2841 <entry role="catalog_table_entry">
2842 <para role="column_definition">
2843 <structfield>extend_bytes</structfield> <type>numeric</type>
2844 </para>
2845 <para>
2846 The total size of relation extend operations in bytes.
2847 </para>
2848 </entry>
2849 </row>
2850
2851 <row>
2852 <entry role="catalog_table_entry">
2853 <para role="column_definition">
2854 <structfield>extend_time</structfield> <type>double precision</type>
2855 </para>
2856 <para>
2857 Time spent waiting for extend operations in milliseconds. (if
2858 <xref linkend="guc-track-io-timing"/> is enabled and
2859 <varname>object</varname> is not <literal>wal</literal>,
2860 or if <xref linkend="guc-track-wal-io-timing"/> is enabled
2861 and <varname>object</varname> is <literal>wal</literal>,
2862 otherwise zero)
2863 </para>
2864 </entry>
2865 </row>
2866
2867 <row>
2868 <entry role="catalog_table_entry">
2869 <para role="column_definition">
2870 <structfield>hits</structfield> <type>bigint</type>
2871 </para>
2872 <para>
2873 The number of times a desired block was found in a shared buffer.
2874 </para>
2875 </entry>
2876 </row>
2877
2878 <row>
2879 <entry role="catalog_table_entry">
2880 <para role="column_definition">
2881 <structfield>evictions</structfield> <type>bigint</type>
2882 </para>
2883 <para>
2884 Number of times a block has been written out from a shared or local
2885 buffer in order to make it available for another use.
2886 </para>
2887 <para>
2888 In <varname>context</varname> <literal>normal</literal>, this counts
2889 the number of times a block was evicted from a buffer and replaced with
2890 another block. In <varname>context</varname>s
2891 <literal>bulkwrite</literal>, <literal>bulkread</literal>, and
2892 <literal>vacuum</literal>, this counts the number of times a block was
2893 evicted from shared buffers in order to add the shared buffer to a
2894 separate, size-limited ring buffer for use in a bulk I/O operation.
2895 </para>
2896 </entry>
2897 </row>
2898
2899 <row>
2900 <entry role="catalog_table_entry">
2901 <para role="column_definition">
2902 <structfield>reuses</structfield> <type>bigint</type>
2903 </para>
2904 <para>
2905 The number of times an existing buffer in a size-limited ring buffer
2906 outside of shared buffers was reused as part of an I/O operation in the
2907 <literal>bulkread</literal>, <literal>bulkwrite</literal>, or
2908 <literal>vacuum</literal> <varname>context</varname>s.
2909 </para>
2910 </entry>
2911 </row>
2912
2913 <row>
2914 <entry role="catalog_table_entry">
2915 <para role="column_definition">
2916 <structfield>fsyncs</structfield> <type>bigint</type>
2917 </para>
2918 <para>
2919 Number of <literal>fsync</literal> calls. These are only tracked in
2920 <varname>context</varname> <literal>normal</literal>.
2921 </para>
2922 </entry>
2923 </row>
2924
2925 <row>
2926 <entry role="catalog_table_entry">
2927 <para role="column_definition">
2928 <structfield>fsync_time</structfield> <type>double precision</type>
2929 </para>
2930 <para>
2931 Time spent waiting for fsync operations in milliseconds (if
2932 <xref linkend="guc-track-io-timing"/> is enabled and
2933 <varname>object</varname> is not <literal>wal</literal>,
2934 or if <xref linkend="guc-track-wal-io-timing"/> is enabled
2935 and <varname>object</varname> is <literal>wal</literal>,
2936 otherwise zero)
2937 </para>
2938 </entry>
2939 </row>
2940
2941 <row>
2942 <entry role="catalog_table_entry">
2943 <para role="column_definition">
2944 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
2945 </para>
2946 <para>
2947 Time at which these statistics were last reset.
2948 </para>
2949 </entry>
2950 </row>
2951 </tbody>
2952 </tgroup>
2953 </table>
2954
2955 <para>
2956 Some backend types never perform I/O operations on some I/O objects and/or
2957 in some I/O contexts. These rows are omitted from the view. For example, the
2958 checkpointer does not checkpoint temporary tables, so there will be no rows
2959 for <varname>backend_type</varname> <literal>checkpointer</literal> and
2960 <varname>object</varname> <literal>temp relation</literal>.
2961 </para>
2962
2963 <para>
2964 In addition, some I/O operations will never be performed either by certain
2965 backend types or on certain I/O objects and/or in certain I/O contexts.
2966 These cells will be NULL. For example, temporary tables are not
2967 <literal>fsync</literal>ed, so <varname>fsyncs</varname> will be NULL for
2968 <varname>object</varname> <literal>temp relation</literal>. Also, the
2969 background writer does not perform reads, so <varname>reads</varname> will
2970 be NULL in rows for <varname>backend_type</varname> <literal>background
2971 writer</literal>.
2972 </para>
2973
2974 <para>
2975 For the <varname>object</varname> <literal>wal</literal>,
2976 <varname>fsyncs</varname> and <varname>fsync_time</varname> track the
2977 fsync activity of WAL files done in <function>issue_xlog_fsync</function>.
2978 <varname>writes</varname> and <varname>write_time</varname>
2979 track the write activity of WAL files done in
2980 <function>XLogWrite</function>.
2981 See <xref linkend="wal-configuration"/> for more information.
2982 </para>
2983
2984 <para>
2985 <structname>pg_stat_io</structname> can be used to inform database tuning.
2986 For example:
2987 <itemizedlist>
2988 <listitem>
2989 <para>
2990 A high <varname>evictions</varname> count can indicate that shared
2991 buffers should be increased.
2992 </para>
2993 </listitem>
2994 <listitem>
2995 <para>
2996 Client backends rely on the checkpointer to ensure data is persisted to
2997 permanent storage. Large numbers of <varname>fsyncs</varname> by
2998 <literal>client backend</literal>s could indicate a misconfiguration of
2999 shared buffers or of the checkpointer. More information on configuring
3000 the checkpointer can be found in <xref linkend="wal-configuration"/>.
3001 </para>
3002 </listitem>
3003 <listitem>
3004 <para>
3005 Normally, client backends should be able to rely on auxiliary processes
3006 like the checkpointer and the background writer to write out dirty data
3007 as much as possible. Large numbers of writes by client backends could
3008 indicate a misconfiguration of shared buffers or of the checkpointer.
3009 More information on configuring the checkpointer can be found in <xref
3010 linkend="wal-configuration"/>.
3011 </para>
3012 </listitem>
3013 </itemizedlist>
3014 </para>
3015
3016 <note>
3017 <para>
3018 Columns tracking I/O wait time will only be non-zero when
3019 <xref linkend="guc-track-io-timing"/> is enabled. The user should be
3020 careful when referencing these columns in combination with their
3021 corresponding I/O operations in case <varname>track_io_timing</varname>
3022 was not enabled for the entire time since the last stats reset.
3023 </para>
3024 </note>
3025
3026
3027
3028 </sect2>
3029
3030 <sect2 id="monitoring-pg-stat-bgwriter-view">
3031 <title><structname>pg_stat_bgwriter</structname></title>
3032
3033 <indexterm>
3034 <primary>pg_stat_bgwriter</primary>
3035 </indexterm>
3036
3037 <para>
3038 The <structname>pg_stat_bgwriter</structname> view will always have a
3039 single row, containing data about the background writer of the cluster.
3040 </para>
3041
3042 <table id="pg-stat-bgwriter-view" xreflabel="pg_stat_bgwriter">
3043 <title><structname>pg_stat_bgwriter</structname> View</title>
3044 <tgroup cols="1">
3045 <thead>
3046 <row>
3047 <entry role="catalog_table_entry"><para role="column_definition">
3048 Column Type
3049 </para>
3050 <para>
3051 Description
3052 </para></entry>
3053 </row>
3054 </thead>
3055
3056 <tbody>
3057 <row>
3058 <entry role="catalog_table_entry"><para role="column_definition">
3059 <structfield>buffers_clean</structfield> <type>bigint</type>
3060 </para>
3061 <para>
3062 Number of buffers written by the background writer
3063 </para></entry>
3064 </row>
3065
3066 <row>
3067 <entry role="catalog_table_entry"><para role="column_definition">
3068 <structfield>maxwritten_clean</structfield> <type>bigint</type>
3069 </para>
3070 <para>
3071 Number of times the background writer stopped a cleaning
3072 scan because it had written too many buffers
3073 </para></entry>
3074 </row>
3075
3076 <row>
3077 <entry role="catalog_table_entry"><para role="column_definition">
3078 <structfield>buffers_alloc</structfield> <type>bigint</type>
3079 </para>
3080 <para>
3081 Number of buffers allocated
3082 </para></entry>
3083 </row>
3084
3085 <row>
3086 <entry role="catalog_table_entry"><para role="column_definition">
3087 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
3088 </para>
3089 <para>
3090 Time at which these statistics were last reset
3091 </para></entry>
3092 </row>
3093 </tbody>
3094 </tgroup>
3095 </table>
3096
3097 </sect2>
3098
3099 <sect2 id="monitoring-pg-stat-checkpointer-view">
3100 <title><structname>pg_stat_checkpointer</structname></title>
3101
3102 <indexterm>
3103 <primary>pg_stat_checkpointer</primary>
3104 </indexterm>
3105
3106 <para>
3107 The <structname>pg_stat_checkpointer</structname> view will always have a
3108 single row, containing data about the checkpointer process of the cluster.
3109 </para>
3110
3111 <table id="pg-stat-checkpointer-view" xreflabel="pg_stat_checkpointer">
3112 <title><structname>pg_stat_checkpointer</structname> View</title>
3113 <tgroup cols="1">
3114 <thead>
3115 <row>
3116 <entry role="catalog_table_entry"><para role="column_definition">
3117 Column Type
3118 </para>
3119 <para>
3120 Description
3121 </para></entry>
3122 </row>
3123 </thead>
3124
3125 <tbody>
3126 <row>
3127 <entry role="catalog_table_entry"><para role="column_definition">
3128 <structfield>num_timed</structfield> <type>bigint</type>
3129 </para>
3130 <para>
3131 Number of scheduled checkpoints due to timeout
3132 </para></entry>
3133 </row>
3134
3135 <row>
3136 <entry role="catalog_table_entry"><para role="column_definition">
3137 <structfield>num_requested</structfield> <type>bigint</type>
3138 </para>
3139 <para>
3140 Number of requested checkpoints
3141 </para></entry>
3142 </row>
3143
3144 <row>
3145 <entry role="catalog_table_entry"><para role="column_definition">
3146 <structfield>num_done</structfield> <type>bigint</type>
3147 </para>
3148 <para>
3149 Number of checkpoints that have been performed
3150 </para></entry>
3151 </row>
3152
3153 <row>
3154 <entry role="catalog_table_entry"><para role="column_definition">
3155 <structfield>restartpoints_timed</structfield> <type>bigint</type>
3156 </para>
3157 <para>
3158 Number of scheduled restartpoints due to timeout or after a failed attempt to perform it
3159 </para></entry>
3160 </row>
3161
3162 <row>
3163 <entry role="catalog_table_entry"><para role="column_definition">
3164 <structfield>restartpoints_req</structfield> <type>bigint</type>
3165 </para>
3166 <para>
3167 Number of requested restartpoints
3168 </para></entry>
3169 </row>
3170
3171 <row>
3172 <entry role="catalog_table_entry"><para role="column_definition">
3173 <structfield>restartpoints_done</structfield> <type>bigint</type>
3174 </para>
3175 <para>
3176 Number of restartpoints that have been performed
3177 </para></entry>
3178 </row>
3179
3180 <row>
3181 <entry role="catalog_table_entry"><para role="column_definition">
3182 <structfield>write_time</structfield> <type>double precision</type>
3183 </para>
3184 <para>
3185 Total amount of time that has been spent in the portion of
3186 processing checkpoints and restartpoints where files are written to disk,
3187 in milliseconds
3188 </para></entry>
3189 </row>
3190
3191 <row>
3192 <entry role="catalog_table_entry"><para role="column_definition">
3193 <structfield>sync_time</structfield> <type>double precision</type>
3194 </para>
3195 <para>
3196 Total amount of time that has been spent in the portion of
3197 processing checkpoints and restartpoints where files are synchronized to
3198 disk, in milliseconds
3199 </para></entry>
3200 </row>
3201
3202 <row>
3203 <entry role="catalog_table_entry"><para role="column_definition">
3204 <structfield>buffers_written</structfield> <type>bigint</type>
3205 </para>
3206 <para>
3207 Number of shared buffers written during checkpoints and restartpoints
3208 </para></entry>
3209 </row>
3210
3211 <row>
3212 <entry role="catalog_table_entry"><para role="column_definition">
3213 <structfield>slru_written</structfield> <type>bigint</type>
3214 </para>
3215 <para>
3216 Number of SLRU buffers written during checkpoints and restartpoints
3217 </para></entry>
3218 </row>
3219
3220 <row>
3221 <entry role="catalog_table_entry"><para role="column_definition">
3222 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
3223 </para>
3224 <para>
3225 Time at which these statistics were last reset
3226 </para></entry>
3227 </row>
3228 </tbody>
3229 </tgroup>
3230 </table>
3231
3232 <para>
3233 Checkpoints may be skipped if the server has been idle since the last one.
3234 <structfield>num_timed</structfield> and
3235 <structfield>num_requested</structfield> count both completed and skipped
3236 checkpoints, while <structfield>num_done</structfield> tracks only
3237 the completed ones. Similarly, restartpoints may be skipped
3238 if the last replayed checkpoint record is already the last restartpoint.
3239 <structfield>restartpoints_timed</structfield> and
3240 <structfield>restartpoints_req</structfield> count both completed and
3241 skipped restartpoints, while <structfield>restartpoints_done</structfield>
3242 tracks only the completed ones.
3243 </para>
3244 </sect2>
3245
3246 <sect2 id="monitoring-pg-stat-wal-view">
3247 <title><structname>pg_stat_wal</structname></title>
3248
3249 <indexterm>
3250 <primary>pg_stat_wal</primary>
3251 </indexterm>
3252
3253 <para>
3254 The <structname>pg_stat_wal</structname> view will always have a
3255 single row, containing data about WAL activity of the cluster.
3256 </para>
3257
3258 <table id="pg-stat-wal-view" xreflabel="pg_stat_wal">
3259 <title><structname>pg_stat_wal</structname> View</title>
3260 <tgroup cols="1">
3261 <thead>
3262 <row>
3263 <entry role="catalog_table_entry"><para role="column_definition">
3264 Column Type
3265 </para>
3266 <para>
3267 Description
3268 </para></entry>
3269 </row>
3270 </thead>
3271
3272 <tbody>
3273 <row>
3274 <entry role="catalog_table_entry"><para role="column_definition">
3275 <structfield>wal_records</structfield> <type>bigint</type>
3276 </para>
3277 <para>
3278 Total number of WAL records generated
3279 </para></entry>
3280 </row>
3281
3282 <row>
3283 <entry role="catalog_table_entry"><para role="column_definition">
3284 <structfield>wal_fpi</structfield> <type>bigint</type>
3285 </para>
3286 <para>
3287 Total number of WAL full page images generated
3288 </para></entry>
3289 </row>
3290
3291 <row>
3292 <entry role="catalog_table_entry"><para role="column_definition">
3293 <structfield>wal_bytes</structfield> <type>numeric</type>
3294 </para>
3295 <para>
3296 Total amount of WAL generated in bytes
3297 </para></entry>
3298 </row>
3299
3300 <row>
3301 <entry role="catalog_table_entry"><para role="column_definition">
3302 <structfield>wal_buffers_full</structfield> <type>bigint</type>
3303 </para>
3304 <para>
3305 Number of times WAL data was written to disk because WAL buffers became full
3306 </para></entry>
3307 </row>
3308
3309 <row>
3310 <entry role="catalog_table_entry"><para role="column_definition">
3311 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
3312 </para>
3313 <para>
3314 Time at which these statistics were last reset
3315 </para></entry>
3316 </row>
3317 </tbody>
3318 </tgroup>
3319 </table>
3320
3321 </sect2>
3322
3323 <sect2 id="monitoring-pg-stat-database-view">
3324 <title><structname>pg_stat_database</structname></title>
3325
3326 <indexterm>
3327 <primary>pg_stat_database</primary>
3328 </indexterm>
3329
3330 <para>
3331 The <structname>pg_stat_database</structname> view will contain one row
3332 for each database in the cluster, plus one for shared objects, showing
3333 database-wide statistics.
3334 </para>
3335
3336 <table id="pg-stat-database-view" xreflabel="pg_stat_database">
3337 <title><structname>pg_stat_database</structname> View</title>
3338 <tgroup cols="1">
3339 <thead>
3340 <row>
3341 <entry role="catalog_table_entry"><para role="column_definition">
3342 Column Type
3343 </para>
3344 <para>
3345 Description
3346 </para></entry>
3347 </row>
3348 </thead>
3349
3350 <tbody>
3351 <row>
3352 <entry role="catalog_table_entry"><para role="column_definition">
3353 <structfield>datid</structfield> <type>oid</type>
3354 </para>
3355 <para>
3356 OID of this database, or 0 for objects belonging to a shared
3357 relation
3358 </para></entry>
3359 </row>
3360
3361 <row>
3362 <entry role="catalog_table_entry"><para role="column_definition">
3363 <structfield>datname</structfield> <type>name</type>
3364 </para>
3365 <para>
3366 Name of this database, or <literal>NULL</literal> for shared
3367 objects.
3368 </para></entry>
3369 </row>
3370
3371 <row>
3372 <entry role="catalog_table_entry"><para role="column_definition">
3373 <structfield>numbackends</structfield> <type>integer</type>
3374 </para>
3375 <para>
3376 Number of backends currently connected to this database, or
3377 <literal>NULL</literal> for shared objects. This is the only column
3378 in this view that returns a value reflecting current state; all other
3379 columns return the accumulated values since the last reset.
3380 </para></entry>
3381 </row>
3382
3383 <row>
3384 <entry role="catalog_table_entry"><para role="column_definition">
3385 <structfield>xact_commit</structfield> <type>bigint</type>
3386 </para>
3387 <para>
3388 Number of transactions in this database that have been
3389 committed
3390 </para></entry>
3391 </row>
3392
3393 <row>
3394 <entry role="catalog_table_entry"><para role="column_definition">
3395 <structfield>xact_rollback</structfield> <type>bigint</type>
3396 </para>
3397 <para>
3398 Number of transactions in this database that have been
3399 rolled back
3400 </para></entry>
3401 </row>
3402
3403 <row>
3404 <entry role="catalog_table_entry"><para role="column_definition">
3405 <structfield>blks_read</structfield> <type>bigint</type>
3406 </para>
3407 <para>
3408 Number of disk blocks read in this database
3409 </para></entry>
3410 </row>
3411
3412 <row>
3413 <entry role="catalog_table_entry"><para role="column_definition">
3414 <structfield>blks_hit</structfield> <type>bigint</type>
3415 </para>
3416 <para>
3417 Number of times disk blocks were found already in the buffer
3418 cache, so that a read was not necessary (this only includes hits in the
3419 PostgreSQL buffer cache, not the operating system's file system cache)
3420 </para></entry>
3421 </row>
3422
3423 <row>
3424 <entry role="catalog_table_entry"><para role="column_definition">
3425 <structfield>tup_returned</structfield> <type>bigint</type>
3426 </para>
3427 <para>
3428 Number of live rows fetched by sequential scans and index entries returned by index scans in this database
3429 </para></entry>
3430 </row>
3431
3432 <row>
3433 <entry role="catalog_table_entry"><para role="column_definition">
3434 <structfield>tup_fetched</structfield> <type>bigint</type>
3435 </para>
3436 <para>
3437 Number of live rows fetched by index scans in this database
3438 </para></entry>
3439 </row>
3440
3441 <row>
3442 <entry role="catalog_table_entry"><para role="column_definition">
3443 <structfield>tup_inserted</structfield> <type>bigint</type>
3444 </para>
3445 <para>
3446 Number of rows inserted by queries in this database
3447 </para></entry>
3448 </row>
3449
3450 <row>
3451 <entry role="catalog_table_entry"><para role="column_definition">
3452 <structfield>tup_updated</structfield> <type>bigint</type>
3453 </para>
3454 <para>
3455 Number of rows updated by queries in this database
3456 </para></entry>
3457 </row>
3458
3459 <row>
3460 <entry role="catalog_table_entry"><para role="column_definition">
3461 <structfield>tup_deleted</structfield> <type>bigint</type>
3462 </para>
3463 <para>
3464 Number of rows deleted by queries in this database
3465 </para></entry>
3466 </row>
3467
3468 <row>
3469 <entry role="catalog_table_entry"><para role="column_definition">
3470 <structfield>conflicts</structfield> <type>bigint</type>
3471 </para>
3472 <para>
3473 Number of queries canceled due to conflicts with recovery
3474 in this database. (Conflicts occur only on standby servers; see
3475 <link linkend="monitoring-pg-stat-database-conflicts-view">
3476 <structname>pg_stat_database_conflicts</structname></link> for details.)
3477 </para></entry>
3478 </row>
3479
3480 <row>
3481 <entry role="catalog_table_entry"><para role="column_definition">
3482 <structfield>temp_files</structfield> <type>bigint</type>
3483 </para>
3484 <para>
3485 Number of temporary files created by queries in this database.
3486 All temporary files are counted, regardless of why the temporary file
3487 was created (e.g., sorting or hashing), and regardless of the
3488 <xref linkend="guc-log-temp-files"/> setting.
3489 </para></entry>
3490 </row>
3491
3492 <row>
3493 <entry role="catalog_table_entry"><para role="column_definition">
3494 <structfield>temp_bytes</structfield> <type>bigint</type>
3495 </para>
3496 <para>
3497 Total amount of data written to temporary files by queries in
3498 this database. All temporary files are counted, regardless of why
3499 the temporary file was created, and
3500 regardless of the <xref linkend="guc-log-temp-files"/> setting.
3501 </para></entry>
3502 </row>
3503
3504 <row>
3505 <entry role="catalog_table_entry"><para role="column_definition">
3506 <structfield>deadlocks</structfield> <type>bigint</type>
3507 </para>
3508 <para>
3509 Number of deadlocks detected in this database
3510 </para></entry>
3511 </row>
3512
3513 <row>
3514 <entry role="catalog_table_entry"><para role="column_definition">
3515 <structfield>checksum_failures</structfield> <type>bigint</type>
3516 </para>
3517 <para>
3518 Number of data page checksum failures detected in this
3519 database (or on a shared object), or NULL if data checksums are
3520 disabled.
3521 </para></entry>
3522 </row>
3523
3524 <row>
3525 <entry role="catalog_table_entry"><para role="column_definition">
3526 <structfield>checksum_last_failure</structfield> <type>timestamp with time zone</type>
3527 </para>
3528 <para>
3529 Time at which the last data page checksum failure was detected in
3530 this database (or on a shared object), or NULL if data checksums are
3531 disabled.
3532 </para></entry>
3533 </row>
3534
3535 <row>
3536 <entry role="catalog_table_entry"><para role="column_definition">
3537 <structfield>blk_read_time</structfield> <type>double precision</type>
3538 </para>
3539 <para>
3540 Time spent reading data file blocks by backends in this database,
3541 in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
3542 otherwise zero)
3543 </para></entry>
3544 </row>
3545
3546 <row>
3547 <entry role="catalog_table_entry"><para role="column_definition">
3548 <structfield>blk_write_time</structfield> <type>double precision</type>
3549 </para>
3550 <para>
3551 Time spent writing data file blocks by backends in this database,
3552 in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
3553 otherwise zero)
3554 </para></entry>
3555 </row>
3556
3557 <row>
3558 <entry role="catalog_table_entry"><para role="column_definition">
3559 <structfield>session_time</structfield> <type>double precision</type>
3560 </para>
3561 <para>
3562 Time spent by database sessions in this database, in milliseconds
3563 (note that statistics are only updated when the state of a session
3564 changes, so if sessions have been idle for a long time, this idle time
3565 won't be included)
3566 </para></entry>
3567 </row>
3568
3569 <row>
3570 <entry role="catalog_table_entry"><para role="column_definition">
3571 <structfield>active_time</structfield> <type>double precision</type>
3572 </para>
3573 <para>
3574 Time spent executing SQL statements in this database, in milliseconds
3575 (this corresponds to the states <literal>active</literal> and
3576 <literal>fastpath function call</literal> in
3577 <link linkend="monitoring-pg-stat-activity-view">
3578 <structname>pg_stat_activity</structname></link>)
3579 </para></entry>
3580 </row>
3581
3582 <row>
3583 <entry role="catalog_table_entry"><para role="column_definition">
3584 <structfield>idle_in_transaction_time</structfield> <type>double precision</type>
3585 </para>
3586 <para>
3587 Time spent idling while in a transaction in this database, in milliseconds
3588 (this corresponds to the states <literal>idle in transaction</literal> and
3589 <literal>idle in transaction (aborted)</literal> in
3590 <link linkend="monitoring-pg-stat-activity-view">
3591 <structname>pg_stat_activity</structname></link>)
3592 </para></entry>
3593 </row>
3594
3595 <row>
3596 <entry role="catalog_table_entry"><para role="column_definition">
3597 <structfield>sessions</structfield> <type>bigint</type>
3598 </para>
3599 <para>
3600 Total number of sessions established to this database
3601 </para></entry>
3602 </row>
3603
3604 <row>
3605 <entry role="catalog_table_entry"><para role="column_definition">
3606 <structfield>sessions_abandoned</structfield> <type>bigint</type>
3607 </para>
3608 <para>
3609 Number of database sessions to this database that were terminated
3610 because connection to the client was lost
3611 </para></entry>
3612 </row>
3613
3614 <row>
3615 <entry role="catalog_table_entry"><para role="column_definition">
3616 <structfield>sessions_fatal</structfield> <type>bigint</type>
3617 </para>
3618 <para>
3619 Number of database sessions to this database that were terminated
3620 by fatal errors
3621 </para></entry>
3622 </row>
3623
3624 <row>
3625 <entry role="catalog_table_entry"><para role="column_definition">
3626 <structfield>sessions_killed</structfield> <type>bigint</type>
3627 </para>
3628 <para>
3629 Number of database sessions to this database that were terminated
3630 by operator intervention
3631 </para></entry>
3632 </row>
3633
3634 <row>
3635 <entry role="catalog_table_entry"><para role="column_definition">
3636 <structfield>parallel_workers_to_launch</structfield> <type>bigint</type>
3637 </para>
3638 <para>
3639 Number of parallel workers planned to be launched by queries on this database
3640 </para></entry>
3641 </row>
3642
3643 <row>
3644 <entry role="catalog_table_entry"><para role="column_definition">
3645 <structfield>parallel_workers_launched</structfield> <type>bigint</type>
3646 </para>
3647 <para>
3648 Number of parallel workers launched by queries on this database
3649 </para></entry>
3650 </row>
3651
3652 <row>
3653 <entry role="catalog_table_entry"><para role="column_definition">
3654 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
3655 </para>
3656 <para>
3657 Time at which these statistics were last reset
3658 </para></entry>
3659 </row>
3660 </tbody>
3661 </tgroup>
3662 </table>
3663
3664 </sect2>
3665
3666 <sect2 id="monitoring-pg-stat-database-conflicts-view">
3667 <title><structname>pg_stat_database_conflicts</structname></title>
3668
3669 <indexterm>
3670 <primary>pg_stat_database_conflicts</primary>
3671 </indexterm>
3672
3673 <para>
3674 The <structname>pg_stat_database_conflicts</structname> view will contain
3675 one row per database, showing database-wide statistics about
3676 query cancels occurring due to conflicts with recovery on standby servers.
3677 This view will only contain information on standby servers, since
3678 conflicts do not occur on primary servers.
3679 </para>
3680
3681 <table id="pg-stat-database-conflicts-view" xreflabel="pg_stat_database_conflicts">
3682 <title><structname>pg_stat_database_conflicts</structname> View</title>
3683 <tgroup cols="1">
3684 <thead>
3685 <row>
3686 <entry role="catalog_table_entry"><para role="column_definition">
3687 Column Type
3688 </para>
3689 <para>
3690 Description
3691 </para></entry>
3692 </row>
3693 </thead>
3694
3695 <tbody>
3696 <row>
3697 <entry role="catalog_table_entry"><para role="column_definition">
3698 <structfield>datid</structfield> <type>oid</type>
3699 </para>
3700 <para>
3701 OID of a database
3702 </para></entry>
3703 </row>
3704
3705 <row>
3706 <entry role="catalog_table_entry"><para role="column_definition">
3707 <structfield>datname</structfield> <type>name</type>
3708 </para>
3709 <para>
3710 Name of this database
3711 </para></entry>
3712 </row>
3713
3714 <row>
3715 <entry role="catalog_table_entry"><para role="column_definition">
3716 <structfield>confl_tablespace</structfield> <type>bigint</type>
3717 </para>
3718 <para>
3719 Number of queries in this database that have been canceled due to
3720 dropped tablespaces
3721 </para></entry>
3722 </row>
3723
3724 <row>
3725 <entry role="catalog_table_entry"><para role="column_definition">
3726 <structfield>confl_lock</structfield> <type>bigint</type>
3727 </para>
3728 <para>
3729 Number of queries in this database that have been canceled due to
3730 lock timeouts
3731 </para></entry>
3732 </row>
3733
3734 <row>
3735 <entry role="catalog_table_entry"><para role="column_definition">
3736 <structfield>confl_snapshot</structfield> <type>bigint</type>
3737 </para>
3738 <para>
3739 Number of queries in this database that have been canceled due to
3740 old snapshots
3741 </para></entry>
3742 </row>
3743
3744 <row>
3745 <entry role="catalog_table_entry"><para role="column_definition">
3746 <structfield>confl_bufferpin</structfield> <type>bigint</type>
3747 </para>
3748 <para>
3749 Number of queries in this database that have been canceled due to
3750 pinned buffers
3751 </para></entry>
3752 </row>
3753
3754 <row>
3755 <entry role="catalog_table_entry"><para role="column_definition">
3756 <structfield>confl_deadlock</structfield> <type>bigint</type>
3757 </para>
3758 <para>
3759 Number of queries in this database that have been canceled due to
3760 deadlocks
3761 </para></entry>
3762 </row>
3763
3764 <row>
3765 <entry role="catalog_table_entry"><para role="column_definition">
3766 <structfield>confl_active_logicalslot</structfield> <type>bigint</type>
3767 </para>
3768 <para>
3769 Number of uses of logical slots in this database that have been
3770 canceled due to old snapshots or too low a <xref linkend="guc-wal-level"/>
3771 on the primary
3772 </para></entry>
3773 </row>
3774 </tbody>
3775 </tgroup>
3776 </table>
3777
3778 </sect2>
3779
3780 <sect2 id="monitoring-pg-stat-all-tables-view">
3781 <title><structname>pg_stat_all_tables</structname></title>
3782
3783 <indexterm>
3784 <primary>pg_stat_all_tables</primary>
3785 </indexterm>
3786
3787 <para>
3788 The <structname>pg_stat_all_tables</structname> view will contain
3789 one row for each table in the current database (including TOAST
3790 tables), showing statistics about accesses to that specific table. The
3791 <structname>pg_stat_user_tables</structname> and
3792 <structname>pg_stat_sys_tables</structname> views
3793 contain the same information,
3794 but filtered to only show user and system tables respectively.
3795 </para>
3796
3797 <table id="pg-stat-all-tables-view" xreflabel="pg_stat_all_tables">
3798 <title><structname>pg_stat_all_tables</structname> View</title>
3799 <tgroup cols="1">
3800 <thead>
3801 <row>
3802 <entry role="catalog_table_entry"><para role="column_definition">
3803 Column Type
3804 </para>
3805 <para>
3806 Description
3807 </para></entry>
3808 </row>
3809 </thead>
3810
3811 <tbody>
3812 <row>
3813 <entry role="catalog_table_entry"><para role="column_definition">
3814 <structfield>relid</structfield> <type>oid</type>
3815 </para>
3816 <para>
3817 OID of a table
3818 </para></entry>
3819 </row>
3820
3821 <row>
3822 <entry role="catalog_table_entry"><para role="column_definition">
3823 <structfield>schemaname</structfield> <type>name</type>
3824 </para>
3825 <para>
3826 Name of the schema that this table is in
3827 </para></entry>
3828 </row>
3829
3830 <row>
3831 <entry role="catalog_table_entry"><para role="column_definition">
3832 <structfield>relname</structfield> <type>name</type>
3833 </para>
3834 <para>
3835 Name of this table
3836 </para></entry>
3837 </row>
3838
3839 <row>
3840 <entry role="catalog_table_entry"><para role="column_definition">
3841 <structfield>seq_scan</structfield> <type>bigint</type>
3842 </para>
3843 <para>
3844 Number of sequential scans initiated on this table
3845 </para></entry>
3846 </row>
3847
3848 <row>
3849 <entry role="catalog_table_entry"><para role="column_definition">
3850 <structfield>last_seq_scan</structfield> <type>timestamp with time zone</type>
3851 </para>
3852 <para>
3853 The time of the last sequential scan on this table, based on the
3854 most recent transaction stop time
3855 </para></entry>
3856 </row>
3857
3858 <row>
3859 <entry role="catalog_table_entry"><para role="column_definition">
3860 <structfield>seq_tup_read</structfield> <type>bigint</type>
3861 </para>
3862 <para>
3863 Number of live rows fetched by sequential scans
3864 </para></entry>
3865 </row>
3866
3867 <row>
3868 <entry role="catalog_table_entry"><para role="column_definition">
3869 <structfield>idx_scan</structfield> <type>bigint</type>
3870 </para>
3871 <para>
3872 Number of index scans initiated on this table
3873 </para></entry>
3874 </row>
3875
3876 <row>
3877 <entry role="catalog_table_entry"><para role="column_definition">
3878 <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type>
3879 </para>
3880 <para>
3881 The time of the last index scan on this table, based on the
3882 most recent transaction stop time
3883 </para></entry>
3884 </row>
3885
3886 <row>
3887 <entry role="catalog_table_entry"><para role="column_definition">
3888 <structfield>idx_tup_fetch</structfield> <type>bigint</type>
3889 </para>
3890 <para>
3891 Number of live rows fetched by index scans
3892 </para></entry>
3893 </row>
3894
3895 <row>
3896 <entry role="catalog_table_entry"><para role="column_definition">
3897 <structfield>n_tup_ins</structfield> <type>bigint</type>
3898 </para>
3899 <para>
3900 Total number of rows inserted
3901 </para></entry>
3902 </row>
3903
3904 <row>
3905 <entry role="catalog_table_entry"><para role="column_definition">
3906 <structfield>n_tup_upd</structfield> <type>bigint</type>
3907 </para>
3908 <para>
3909 Total number of rows updated. (This includes row updates
3910 counted in <structfield>n_tup_hot_upd</structfield> and
3911 <structfield>n_tup_newpage_upd</structfield>, and remaining
3912 non-<acronym>HOT</acronym> updates.)
3913 </para></entry>
3914 </row>
3915
3916 <row>
3917 <entry role="catalog_table_entry"><para role="column_definition">
3918 <structfield>n_tup_del</structfield> <type>bigint</type>
3919 </para>
3920 <para>
3921 Total number of rows deleted
3922 </para></entry>
3923 </row>
3924
3925 <row>
3926 <entry role="catalog_table_entry"><para role="column_definition">
3927 <structfield>n_tup_hot_upd</structfield> <type>bigint</type>
3928 </para>
3929 <para>
3930 Number of rows <link linkend="storage-hot">HOT updated</link>.
3931 These are updates where no successor versions are required in
3932 indexes.
3933 </para></entry>
3934 </row>
3935
3936 <row>
3937 <entry role="catalog_table_entry"><para role="column_definition">
3938 <structfield>n_tup_newpage_upd</structfield> <type>bigint</type>
3939 </para>
3940 <para>
3941 Number of rows updated where the successor version goes onto a
3942 <emphasis>new</emphasis> heap page, leaving behind an original
3943 version with a
3944 <link linkend="storage-tuple-layout"><structfield>t_ctid</structfield>
3945 field</link> that points to a different heap page. These are
3946 always non-<acronym>HOT</acronym> updates.
3947 </para></entry>
3948 </row>
3949
3950 <row>
3951 <entry role="catalog_table_entry"><para role="column_definition">
3952 <structfield>n_live_tup</structfield> <type>bigint</type>
3953 </para>
3954 <para>
3955 Estimated number of live rows
3956 </para></entry>
3957 </row>
3958
3959 <row>
3960 <entry role="catalog_table_entry"><para role="column_definition">
3961 <structfield>n_dead_tup</structfield> <type>bigint</type>
3962 </para>
3963 <para>
3964 Estimated number of dead rows
3965 </para></entry>
3966 </row>
3967
3968 <row>
3969 <entry role="catalog_table_entry"><para role="column_definition">
3970 <structfield>n_mod_since_analyze</structfield> <type>bigint</type>
3971 </para>
3972 <para>
3973 Estimated number of rows modified since this table was last analyzed
3974 </para></entry>
3975 </row>
3976
3977 <row>
3978 <entry role="catalog_table_entry"><para role="column_definition">
3979 <structfield>n_ins_since_vacuum</structfield> <type>bigint</type>
3980 </para>
3981 <para>
3982 Estimated number of rows inserted since this table was last vacuumed
3983 (not counting <command>VACUUM FULL</command>)
3984 </para></entry>
3985 </row>
3986
3987 <row>
3988 <entry role="catalog_table_entry"><para role="column_definition">
3989 <structfield>last_vacuum</structfield> <type>timestamp with time zone</type>
3990 </para>
3991 <para>
3992 Last time at which this table was manually vacuumed
3993 (not counting <command>VACUUM FULL</command>)
3994 </para></entry>
3995 </row>
3996
3997 <row>
3998 <entry role="catalog_table_entry"><para role="column_definition">
3999 <structfield>last_autovacuum</structfield> <type>timestamp with time zone</type>
4000 </para>
4001 <para>
4002 Last time at which this table was vacuumed by the autovacuum
4003 daemon
4004 </para></entry>
4005 </row>
4006
4007 <row>
4008 <entry role="catalog_table_entry"><para role="column_definition">
4009 <structfield>last_analyze</structfield> <type>timestamp with time zone</type>
4010 </para>
4011 <para>
4012 Last time at which this table was manually analyzed
4013 </para></entry>
4014 </row>
4015
4016 <row>
4017 <entry role="catalog_table_entry"><para role="column_definition">
4018 <structfield>last_autoanalyze</structfield> <type>timestamp with time zone</type>
4019 </para>
4020 <para>
4021 Last time at which this table was analyzed by the autovacuum
4022 daemon
4023 </para></entry>
4024 </row>
4025
4026 <row>
4027 <entry role="catalog_table_entry"><para role="column_definition">
4028 <structfield>vacuum_count</structfield> <type>bigint</type>
4029 </para>
4030 <para>
4031 Number of times this table has been manually vacuumed
4032 (not counting <command>VACUUM FULL</command>)
4033 </para></entry>
4034 </row>
4035
4036 <row>
4037 <entry role="catalog_table_entry"><para role="column_definition">
4038 <structfield>autovacuum_count</structfield> <type>bigint</type>
4039 </para>
4040 <para>
4041 Number of times this table has been vacuumed by the autovacuum
4042 daemon
4043 </para></entry>
4044 </row>
4045
4046 <row>
4047 <entry role="catalog_table_entry"><para role="column_definition">
4048 <structfield>analyze_count</structfield> <type>bigint</type>
4049 </para>
4050 <para>
4051 Number of times this table has been manually analyzed
4052 </para></entry>
4053 </row>
4054
4055 <row>
4056 <entry role="catalog_table_entry"><para role="column_definition">
4057 <structfield>autoanalyze_count</structfield> <type>bigint</type>
4058 </para>
4059 <para>
4060 Number of times this table has been analyzed by the autovacuum
4061 daemon
4062 </para></entry>
4063 </row>
4064
4065 <row>
4066 <entry role="catalog_table_entry"><para role="column_definition">
4067 <structfield>total_vacuum_time</structfield> <type>double precision</type>
4068 </para>
4069 <para>
4070 Total time this table has been manually vacuumed, in milliseconds
4071 (not counting <command>VACUUM FULL</command>).
4072 (This includes the time spent sleeping due to cost-based delays.)
4073 </para></entry>
4074 </row>
4075
4076 <row>
4077 <entry role="catalog_table_entry"><para role="column_definition">
4078 <structfield>total_autovacuum_time</structfield> <type>double precision</type>
4079 </para>
4080 <para>
4081 Total time this table has been vacuumed by the autovacuum daemon,
4082 in milliseconds. (This includes the time spent sleeping due to
4083 cost-based delays.)
4084 </para></entry>
4085 </row>
4086
4087 <row>
4088 <entry role="catalog_table_entry"><para role="column_definition">
4089 <structfield>total_analyze_time</structfield> <type>double precision</type>
4090 </para>
4091 <para>
4092 Total time this table has been manually analyzed, in milliseconds.
4093 (This includes the time spent sleeping due to cost-based delays.)
4094 </para></entry>
4095 </row>
4096
4097 <row>
4098 <entry role="catalog_table_entry"><para role="column_definition">
4099 <structfield>total_autoanalyze_time</structfield> <type>double precision</type>
4100 </para>
4101 <para>
4102 Total time this table has been analyzed by the autovacuum daemon,
4103 in milliseconds. (This includes the time spent sleeping due to
4104 cost-based delays.)
4105 </para></entry>
4106 </row>
4107 </tbody>
4108 </tgroup>
4109 </table>
4110
4111 </sect2>
4112
4113 <sect2 id="monitoring-pg-stat-all-indexes-view">
4114 <title><structname>pg_stat_all_indexes</structname></title>
4115
4116 <indexterm>
4117 <primary>pg_stat_all_indexes</primary>
4118 </indexterm>
4119
4120 <para>
4121 The <structname>pg_stat_all_indexes</structname> view will contain
4122 one row for each index in the current database,
4123 showing statistics about accesses to that specific index. The
4124 <structname>pg_stat_user_indexes</structname> and
4125 <structname>pg_stat_sys_indexes</structname> views
4126 contain the same information,
4127 but filtered to only show user and system indexes respectively.
4128 </para>
4129
4130 <table id="pg-stat-all-indexes-view" xreflabel="pg_stat_all_indexes">
4131 <title><structname>pg_stat_all_indexes</structname> View</title>
4132 <tgroup cols="1">
4133 <thead>
4134 <row>
4135 <entry role="catalog_table_entry"><para role="column_definition">
4136 Column Type
4137 </para>
4138 <para>
4139 Description
4140 </para></entry>
4141 </row>
4142 </thead>
4143
4144 <tbody>
4145 <row>
4146 <entry role="catalog_table_entry"><para role="column_definition">
4147 <structfield>relid</structfield> <type>oid</type>
4148 </para>
4149 <para>
4150 OID of the table for this index
4151 </para></entry>
4152 </row>
4153
4154 <row>
4155 <entry role="catalog_table_entry"><para role="column_definition">
4156 <structfield>indexrelid</structfield> <type>oid</type>
4157 </para>
4158 <para>
4159 OID of this index
4160 </para></entry>
4161 </row>
4162
4163 <row>
4164 <entry role="catalog_table_entry"><para role="column_definition">
4165 <structfield>schemaname</structfield> <type>name</type>
4166 </para>
4167 <para>
4168 Name of the schema this index is in
4169 </para></entry>
4170 </row>
4171
4172 <row>
4173 <entry role="catalog_table_entry"><para role="column_definition">
4174 <structfield>relname</structfield> <type>name</type>
4175 </para>
4176 <para>
4177 Name of the table for this index
4178 </para></entry>
4179 </row>
4180
4181 <row>
4182 <entry role="catalog_table_entry"><para role="column_definition">
4183 <structfield>indexrelname</structfield> <type>name</type>
4184 </para>
4185 <para>
4186 Name of this index
4187 </para></entry>
4188 </row>
4189
4190 <row>
4191 <entry role="catalog_table_entry"><para role="column_definition">
4192 <structfield>idx_scan</structfield> <type>bigint</type>
4193 </para>
4194 <para>
4195 Number of index scans initiated on this index
4196 </para></entry>
4197 </row>
4198
4199 <row>
4200 <entry role="catalog_table_entry"><para role="column_definition">
4201 <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type>
4202 </para>
4203 <para>
4204 The time of the last scan on this index, based on the
4205 most recent transaction stop time
4206 </para></entry>
4207 </row>
4208
4209 <row>
4210 <entry role="catalog_table_entry"><para role="column_definition">
4211 <structfield>idx_tup_read</structfield> <type>bigint</type>
4212 </para>
4213 <para>
4214 Number of index entries returned by scans on this index
4215 </para></entry>
4216 </row>
4217
4218 <row>
4219 <entry role="catalog_table_entry"><para role="column_definition">
4220 <structfield>idx_tup_fetch</structfield> <type>bigint</type>
4221 </para>
4222 <para>
4223 Number of live table rows fetched by simple index scans using this
4224 index
4225 </para></entry>
4226 </row>
4227 </tbody>
4228 </tgroup>
4229 </table>
4230
4231 <para>
4232 Indexes can be used by simple index scans, <quote>bitmap</quote> index scans,
4233 and the optimizer. In a bitmap scan
4234 the output of several indexes can be combined via AND or OR rules,
4235 so it is difficult to associate individual heap row fetches
4236 with specific indexes when a bitmap scan is used. Therefore, a bitmap
4237 scan increments the
4238 <structname>pg_stat_all_indexes</structname>.<structfield>idx_tup_read</structfield>
4239 count(s) for the index(es) it uses, and it increments the
4240 <structname>pg_stat_all_tables</structname>.<structfield>idx_tup_fetch</structfield>
4241 count for the table, but it does not affect
4242 <structname>pg_stat_all_indexes</structname>.<structfield>idx_tup_fetch</structfield>.
4243 The optimizer also accesses indexes to check for supplied constants
4244 whose values are outside the recorded range of the optimizer statistics
4245 because the optimizer statistics might be stale.
4246 </para>
4247
4248 <note>
4249 <para>
4250 The <structfield>idx_tup_read</structfield> and <structfield>idx_tup_fetch</structfield> counts
4251 can be different even without any use of bitmap scans,
4252 because <structfield>idx_tup_read</structfield> counts
4253 index entries retrieved from the index while <structfield>idx_tup_fetch</structfield>
4254 counts live rows fetched from the table. The latter will be less if any
4255 dead or not-yet-committed rows are fetched using the index, or if any
4256 heap fetches are avoided by means of an index-only scan.
4257 </para>
4258 </note>
4259
4260 <note>
4261 <para>
4262 Index scans may sometimes perform multiple index searches per execution.
4263 Each index search increments <structname>pg_stat_all_indexes</structname>.<structfield>idx_scan</structfield>,
4264 so it's possible for the count of index scans to significantly exceed the
4265 total number of index scan executor node executions.
4266 </para>
4267 <para>
4268 This can happen with queries that use certain <acronym>SQL</acronym>
4269 constructs to search for rows matching any value out of a list or array of
4270 multiple scalar values (see <xref linkend="functions-comparisons"/>). It
4271 can also happen to queries with a
4272 <literal><replaceable>column_name</replaceable> =
4273 <replaceable>value1</replaceable> OR
4274 <replaceable>column_name</replaceable> =
4275 <replaceable>value2</replaceable> ...</literal> construct, though only
4276 when the optimizer transforms the construct into an equivalent
4277 multi-valued array representation. Similarly, when B-tree index scans use
4278 the skip scan optimization, an index search is performed each time the
4279 scan is repositioned to the next index leaf page that might have matching
4280 tuples (see <xref linkend="indexes-multicolumn"/>).
4281 </para>
4282 </note>
4283 <tip>
4284 <para>
4285 <command>EXPLAIN ANALYZE</command> outputs the total number of index
4286 searches performed by each index scan node. See
4287 <xref linkend="using-explain-analyze"/> for an example demonstrating how
4288 this works.
4289 </para>
4290 </tip>
4291
4292 </sect2>
4293
4294 <sect2 id="monitoring-pg-statio-all-tables-view">
4295 <title><structname>pg_statio_all_tables</structname></title>
4296
4297 <indexterm>
4298 <primary>pg_statio_all_tables</primary>
4299 </indexterm>
4300
4301 <para>
4302 The <structname>pg_statio_all_tables</structname> view will contain
4303 one row for each table in the current database (including TOAST
4304 tables), showing statistics about I/O on that specific table. The
4305 <structname>pg_statio_user_tables</structname> and
4306 <structname>pg_statio_sys_tables</structname> views
4307 contain the same information,
4308 but filtered to only show user and system tables respectively.
4309 </para>
4310
4311 <table id="pg-statio-all-tables-view" xreflabel="pg_statio_all_tables">
4312 <title><structname>pg_statio_all_tables</structname> View</title>
4313 <tgroup cols="1">
4314 <thead>
4315 <row>
4316 <entry role="catalog_table_entry"><para role="column_definition">
4317 Column Type
4318 </para>
4319 <para>
4320 Description
4321 </para></entry>
4322 </row>
4323 </thead>
4324
4325 <tbody>
4326 <row>
4327 <entry role="catalog_table_entry"><para role="column_definition">
4328 <structfield>relid</structfield> <type>oid</type>
4329 </para>
4330 <para>
4331 OID of a table
4332 </para></entry>
4333 </row>
4334
4335 <row>
4336 <entry role="catalog_table_entry"><para role="column_definition">
4337 <structfield>schemaname</structfield> <type>name</type>
4338 </para>
4339 <para>
4340 Name of the schema that this table is in
4341 </para></entry>
4342 </row>
4343
4344 <row>
4345 <entry role="catalog_table_entry"><para role="column_definition">
4346 <structfield>relname</structfield> <type>name</type>
4347 </para>
4348 <para>
4349 Name of this table
4350 </para></entry>
4351 </row>
4352
4353 <row>
4354 <entry role="catalog_table_entry"><para role="column_definition">
4355 <structfield>heap_blks_read</structfield> <type>bigint</type>
4356 </para>
4357 <para>
4358 Number of disk blocks read from this table
4359 </para></entry>
4360 </row>
4361
4362 <row>
4363 <entry role="catalog_table_entry"><para role="column_definition">
4364 <structfield>heap_blks_hit</structfield> <type>bigint</type>
4365 </para>
4366 <para>
4367 Number of buffer hits in this table
4368 </para></entry>
4369 </row>
4370
4371 <row>
4372 <entry role="catalog_table_entry"><para role="column_definition">
4373 <structfield>idx_blks_read</structfield> <type>bigint</type>
4374 </para>
4375 <para>
4376 Number of disk blocks read from all indexes on this table
4377 </para></entry>
4378 </row>
4379
4380 <row>
4381 <entry role="catalog_table_entry"><para role="column_definition">
4382 <structfield>idx_blks_hit</structfield> <type>bigint</type>
4383 </para>
4384 <para>
4385 Number of buffer hits in all indexes on this table
4386 </para></entry>
4387 </row>
4388
4389 <row>
4390 <entry role="catalog_table_entry"><para role="column_definition">
4391 <structfield>toast_blks_read</structfield> <type>bigint</type>
4392 </para>
4393 <para>
4394 Number of disk blocks read from this table's TOAST table (if any)
4395 </para></entry>
4396 </row>
4397
4398 <row>
4399 <entry role="catalog_table_entry"><para role="column_definition">
4400 <structfield>toast_blks_hit</structfield> <type>bigint</type>
4401 </para>
4402 <para>
4403 Number of buffer hits in this table's TOAST table (if any)
4404 </para></entry>
4405 </row>
4406
4407 <row>
4408 <entry role="catalog_table_entry"><para role="column_definition">
4409 <structfield>tidx_blks_read</structfield> <type>bigint</type>
4410 </para>
4411 <para>
4412 Number of disk blocks read from this table's TOAST table indexes (if any)
4413 </para></entry>
4414 </row>
4415
4416 <row>
4417 <entry role="catalog_table_entry"><para role="column_definition">
4418 <structfield>tidx_blks_hit</structfield> <type>bigint</type>
4419 </para>
4420 <para>
4421 Number of buffer hits in this table's TOAST table indexes (if any)
4422 </para></entry>
4423 </row>
4424 </tbody>
4425 </tgroup>
4426 </table>
4427
4428 </sect2>
4429
4430 <sect2 id="monitoring-pg-statio-all-indexes-view">
4431 <title><structname>pg_statio_all_indexes</structname></title>
4432
4433 <indexterm>
4434 <primary>pg_statio_all_indexes</primary>
4435 </indexterm>
4436
4437 <para>
4438 The <structname>pg_statio_all_indexes</structname> view will contain
4439 one row for each index in the current database,
4440 showing statistics about I/O on that specific index. The
4441 <structname>pg_statio_user_indexes</structname> and
4442 <structname>pg_statio_sys_indexes</structname> views
4443 contain the same information,
4444 but filtered to only show user and system indexes respectively.
4445 </para>
4446
4447 <table id="pg-statio-all-indexes-view" xreflabel="pg_statio_all_indexes">
4448 <title><structname>pg_statio_all_indexes</structname> View</title>
4449 <tgroup cols="1">
4450 <thead>
4451 <row>
4452 <entry role="catalog_table_entry"><para role="column_definition">
4453 Column Type
4454 </para>
4455 <para>
4456 Description
4457 </para></entry>
4458 </row>
4459 </thead>
4460
4461 <tbody>
4462 <row>
4463 <entry role="catalog_table_entry"><para role="column_definition">
4464 <structfield>relid</structfield> <type>oid</type>
4465 </para>
4466 <para>
4467 OID of the table for this index
4468 </para></entry>
4469 </row>
4470
4471 <row>
4472 <entry role="catalog_table_entry"><para role="column_definition">
4473 <structfield>indexrelid</structfield> <type>oid</type>
4474 </para>
4475 <para>
4476 OID of this index
4477 </para></entry>
4478 </row>
4479
4480 <row>
4481 <entry role="catalog_table_entry"><para role="column_definition">
4482 <structfield>schemaname</structfield> <type>name</type>
4483 </para>
4484 <para>
4485 Name of the schema this index is in
4486 </para></entry>
4487 </row>
4488
4489 <row>
4490 <entry role="catalog_table_entry"><para role="column_definition">
4491 <structfield>relname</structfield> <type>name</type>
4492 </para>
4493 <para>
4494 Name of the table for this index
4495 </para></entry>
4496 </row>
4497
4498 <row>
4499 <entry role="catalog_table_entry"><para role="column_definition">
4500 <structfield>indexrelname</structfield> <type>name</type>
4501 </para>
4502 <para>
4503 Name of this index
4504 </para></entry>
4505 </row>
4506
4507 <row>
4508 <entry role="catalog_table_entry"><para role="column_definition">
4509 <structfield>idx_blks_read</structfield> <type>bigint</type>
4510 </para>
4511 <para>
4512 Number of disk blocks read from this index
4513 </para></entry>
4514 </row>
4515
4516 <row>
4517 <entry role="catalog_table_entry"><para role="column_definition">
4518 <structfield>idx_blks_hit</structfield> <type>bigint</type>
4519 </para>
4520 <para>
4521 Number of buffer hits in this index
4522 </para></entry>
4523 </row>
4524 </tbody>
4525 </tgroup>
4526 </table>
4527
4528 </sect2>
4529
4530 <sect2 id="monitoring-pg-statio-all-sequences-view">
4531 <title><structname>pg_statio_all_sequences</structname></title>
4532
4533 <indexterm>
4534 <primary>pg_statio_all_sequences</primary>
4535 </indexterm>
4536
4537 <para>
4538 The <structname>pg_statio_all_sequences</structname> view will contain
4539 one row for each sequence in the current database,
4540 showing statistics about I/O on that specific sequence.
4541 </para>
4542
4543 <table id="pg-statio-all-sequences-view" xreflabel="pg_statio_all_sequences">
4544 <title><structname>pg_statio_all_sequences</structname> View</title>
4545 <tgroup cols="1">
4546 <thead>
4547 <row>
4548 <entry role="catalog_table_entry"><para role="column_definition">
4549 Column Type
4550 </para>
4551 <para>
4552 Description
4553 </para></entry>
4554 </row>
4555 </thead>
4556
4557 <tbody>
4558 <row>
4559 <entry role="catalog_table_entry"><para role="column_definition">
4560 <structfield>relid</structfield> <type>oid</type>
4561 </para>
4562 <para>
4563 OID of a sequence
4564 </para></entry>
4565 </row>
4566
4567 <row>
4568 <entry role="catalog_table_entry"><para role="column_definition">
4569 <structfield>schemaname</structfield> <type>name</type>
4570 </para>
4571 <para>
4572 Name of the schema this sequence is in
4573 </para></entry>
4574 </row>
4575
4576 <row>
4577 <entry role="catalog_table_entry"><para role="column_definition">
4578 <structfield>relname</structfield> <type>name</type>
4579 </para>
4580 <para>
4581 Name of this sequence
4582 </para></entry>
4583 </row>
4584
4585 <row>
4586 <entry role="catalog_table_entry"><para role="column_definition">
4587 <structfield>blks_read</structfield> <type>bigint</type>
4588 </para>
4589 <para>
4590 Number of disk blocks read from this sequence
4591 </para></entry>
4592 </row>
4593
4594 <row>
4595 <entry role="catalog_table_entry"><para role="column_definition">
4596 <structfield>blks_hit</structfield> <type>bigint</type>
4597 </para>
4598 <para>
4599 Number of buffer hits in this sequence
4600 </para></entry>
4601 </row>
4602 </tbody>
4603 </tgroup>
4604 </table>
4605
4606 </sect2>
4607
4608 <sect2 id="monitoring-pg-stat-user-functions-view">
4609 <title><structname>pg_stat_user_functions</structname></title>
4610
4611 <indexterm>
4612 <primary>pg_stat_user_functions</primary>
4613 </indexterm>
4614
4615 <para>
4616 The <structname>pg_stat_user_functions</structname> view will contain
4617 one row for each tracked function, showing statistics about executions of
4618 that function. The <xref linkend="guc-track-functions"/> parameter
4619 controls exactly which functions are tracked.
4620 </para>
4621
4622 <table id="pg-stat-user-functions-view" xreflabel="pg_stat_user_functions">
4623 <title><structname>pg_stat_user_functions</structname> View</title>
4624 <tgroup cols="1">
4625 <thead>
4626 <row>
4627 <entry role="catalog_table_entry"><para role="column_definition">
4628 Column Type
4629 </para>
4630 <para>
4631 Description
4632 </para></entry>
4633 </row>
4634 </thead>
4635
4636 <tbody>
4637 <row>
4638 <entry role="catalog_table_entry"><para role="column_definition">
4639 <structfield>funcid</structfield> <type>oid</type>
4640 </para>
4641 <para>
4642 OID of a function
4643 </para></entry>
4644 </row>
4645
4646 <row>
4647 <entry role="catalog_table_entry"><para role="column_definition">
4648 <structfield>schemaname</structfield> <type>name</type>
4649 </para>
4650 <para>
4651 Name of the schema this function is in
4652 </para></entry>
4653 </row>
4654
4655 <row>
4656 <entry role="catalog_table_entry"><para role="column_definition">
4657 <structfield>funcname</structfield> <type>name</type>
4658 </para>
4659 <para>
4660 Name of this function
4661 </para></entry>
4662 </row>
4663
4664 <row>
4665 <entry role="catalog_table_entry"><para role="column_definition">
4666 <structfield>calls</structfield> <type>bigint</type>
4667 </para>
4668 <para>
4669 Number of times this function has been called
4670 </para></entry>
4671 </row>
4672
4673 <row>
4674 <entry role="catalog_table_entry"><para role="column_definition">
4675 <structfield>total_time</structfield> <type>double precision</type>
4676 </para>
4677 <para>
4678 Total time spent in this function and all other functions
4679 called by it, in milliseconds
4680 </para></entry>
4681 </row>
4682
4683 <row>
4684 <entry role="catalog_table_entry"><para role="column_definition">
4685 <structfield>self_time</structfield> <type>double precision</type>
4686 </para>
4687 <para>
4688 Total time spent in this function itself, not including
4689 other functions called by it, in milliseconds
4690 </para></entry>
4691 </row>
4692 </tbody>
4693 </tgroup>
4694 </table>
4695
4696 </sect2>
4697
4698 <sect2 id="monitoring-pg-stat-slru-view">
4699 <title><structname>pg_stat_slru</structname></title>
4700
4701 <indexterm>
4702 <primary>SLRU</primary>
4703 </indexterm>
4704
4705 <indexterm>
4706 <primary>pg_stat_slru</primary>
4707 </indexterm>
4708
4709 <para>
4710 <productname>PostgreSQL</productname> accesses certain on-disk information
4711 via <literal>SLRU</literal> (<firstterm>simple least-recently-used</firstterm>)
4712 caches.
4713 The <structname>pg_stat_slru</structname> view will contain
4714 one row for each tracked SLRU cache, showing statistics about access
4715 to cached pages.
4716 </para>
4717
4718 <para>
4719 For each <literal>SLRU</literal> cache that's part of the core server,
4720 there is a configuration parameter that controls its size, with the suffix
4721 <literal>_buffers</literal> appended.
4722 </para>
4723
4724 <table id="pg-stat-slru-view" xreflabel="pg_stat_slru">
4725 <title><structname>pg_stat_slru</structname> View</title>
4726 <tgroup cols="1">
4727 <thead>
4728 <row>
4729 <entry role="catalog_table_entry"><para role="column_definition">
4730 Column Type
4731 </para>
4732 <para>
4733 Description
4734 </para></entry>
4735 </row>
4736 </thead>
4737
4738 <tbody>
4739 <row>
4740 <entry role="catalog_table_entry"><para role="column_definition">
4741 <structfield>name</structfield> <type>text</type>
4742 </para>
4743 <para>
4744 Name of the SLRU
4745 </para></entry>
4746 </row>
4747
4748 <row>
4749 <entry role="catalog_table_entry"><para role="column_definition">
4750 <structfield>blks_zeroed</structfield> <type>bigint</type>
4751 </para>
4752 <para>
4753 Number of blocks zeroed during initializations
4754 </para></entry>
4755 </row>
4756
4757 <row>
4758 <entry role="catalog_table_entry"><para role="column_definition">
4759 <structfield>blks_hit</structfield> <type>bigint</type>
4760 </para>
4761 <para>
4762 Number of times disk blocks were found already in the SLRU,
4763 so that a read was not necessary (this only includes hits in the
4764 SLRU, not the operating system's file system cache)
4765 </para></entry>
4766 </row>
4767
4768 <row>
4769 <entry role="catalog_table_entry"><para role="column_definition">
4770 <structfield>blks_read</structfield> <type>bigint</type>
4771 </para>
4772 <para>
4773 Number of disk blocks read for this SLRU
4774 </para></entry>
4775 </row>
4776
4777 <row>
4778 <entry role="catalog_table_entry"><para role="column_definition">
4779 <structfield>blks_written</structfield> <type>bigint</type>
4780 </para>
4781 <para>
4782 Number of disk blocks written for this SLRU
4783 </para></entry>
4784 </row>
4785
4786 <row>
4787 <entry role="catalog_table_entry"><para role="column_definition">
4788 <structfield>blks_exists</structfield> <type>bigint</type>
4789 </para>
4790 <para>
4791 Number of blocks checked for existence for this SLRU
4792 </para></entry>
4793 </row>
4794
4795 <row>
4796 <entry role="catalog_table_entry"><para role="column_definition">
4797 <structfield>flushes</structfield> <type>bigint</type>
4798 </para>
4799 <para>
4800 Number of flushes of dirty data for this SLRU
4801 </para></entry>
4802 </row>
4803
4804 <row>
4805 <entry role="catalog_table_entry"><para role="column_definition">
4806 <structfield>truncates</structfield> <type>bigint</type>
4807 </para>
4808 <para>
4809 Number of truncates for this SLRU
4810 </para></entry>
4811 </row>
4812
4813 <row>
4814 <entry role="catalog_table_entry"><para role="column_definition">
4815 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
4816 </para>
4817 <para>
4818 Time at which these statistics were last reset
4819 </para></entry>
4820 </row>
4821 </tbody>
4822 </tgroup>
4823 </table>
4824
4825 </sect2>
4826
4827 <sect2 id="monitoring-stats-functions">
4828 <title>Statistics Functions</title>
4829
4830 <para>
4831 Other ways of looking at the statistics can be set up by writing
4832 queries that use the same underlying statistics access functions used by
4833 the standard views shown above. For details such as the functions' names,
4834 consult the definitions of the standard views. (For example, in
4835 <application>psql</application> you could issue <literal>\d+ pg_stat_activity</literal>.)
4836 The access functions for per-database statistics take a database OID as an
4837 argument to identify which database to report on.
4838 The per-table and per-index functions take a table or index OID.
4839 The functions for per-function statistics take a function OID.
4840 Note that only tables, indexes, and functions in the current database
4841 can be seen with these functions.
4842 </para>
4843
4844 <para>
4845 Additional functions related to the cumulative statistics system are listed
4846 in <xref linkend="monitoring-stats-funcs-table"/>.
4847 </para>
4848
4849 <table id="monitoring-stats-funcs-table">
4850 <title>Additional Statistics Functions</title>
4851 <tgroup cols="1">
4852 <thead>
4853 <row>
4854 <entry role="func_table_entry"><para role="func_signature">
4855 Function
4856 </para>
4857 <para>
4858 Description
4859 </para></entry>
4860 </row>
4861 </thead>
4862
4863 <tbody>
4864 <row>
4865 <!-- See also the entry for this in func.sgml -->
4866 <entry role="func_table_entry"><para role="func_signature">
4867 <function>pg_backend_pid</function> ()
4868 <returnvalue>integer</returnvalue>
4869 </para>
4870 <para>
4871 Returns the process ID of the server process attached to the current
4872 session.
4873 </para></entry>
4874 </row>
4875
4876 <row>
4877 <entry id="pg-stat-get-backend-io" role="func_table_entry"><para role="func_signature">
4878 <indexterm>
4879 <primary>pg_stat_get_backend_io</primary>
4880 </indexterm>
4881 <function>pg_stat_get_backend_io</function> ( <type>integer</type> )
4882 <returnvalue>setof record</returnvalue>
4883 </para>
4884 <para>
4885 Returns I/O statistics about the backend with the specified
4886 process ID. The output fields are exactly the same as the ones in the
4887 <structname>pg_stat_io</structname> view.
4888 </para>
4889 <para>
4890 The function does not return I/O statistics for the checkpointer,
4891 the background writer, the startup process and the autovacuum launcher
4892 as they are already visible in the <structname>pg_stat_io</structname>
4893 view and there is only one of each.
4894 </para></entry>
4895 </row>
4896
4897 <row>
4898 <entry role="func_table_entry"><para role="func_signature">
4899 <indexterm>
4900 <primary>pg_stat_get_activity</primary>
4901 </indexterm>
4902 <function>pg_stat_get_activity</function> ( <type>integer</type> )
4903 <returnvalue>setof record</returnvalue>
4904 </para>
4905 <para>
4906 Returns a record of information about the backend with the specified
4907 process ID, or one record for each active backend in the system
4908 if <literal>NULL</literal> is specified. The fields returned are a
4909 subset of those in the <structname>pg_stat_activity</structname> view.
4910 </para></entry>
4911 </row>
4912
4913 <row>
4914 <entry id="pg-stat-get-backend-wal" role="func_table_entry"><para role="func_signature">
4915 <indexterm>
4916 <primary>pg_stat_get_backend_wal</primary>
4917 </indexterm>
4918 <function>pg_stat_get_backend_wal</function> ( <type>integer</type> )
4919 <returnvalue>record</returnvalue>
4920 </para>
4921 <para>
4922 Returns WAL statistics about the backend with the specified
4923 process ID. The output fields are exactly the same as the ones in the
4924 <structname>pg_stat_wal</structname> view.
4925 </para>
4926 <para>
4927 The function does not return WAL statistics for the checkpointer,
4928 the background writer, the startup process and the autovacuum launcher.
4929 </para></entry>
4930 </row>
4931
4932 <row>
4933 <entry role="func_table_entry"><para role="func_signature">
4934 <indexterm>
4935 <primary>pg_stat_get_snapshot_timestamp</primary>
4936 </indexterm>
4937 <function>pg_stat_get_snapshot_timestamp</function> ()
4938 <returnvalue>timestamp with time zone</returnvalue>
4939 </para>
4940 <para>
4941 Returns the timestamp of the current statistics snapshot, or NULL if
4942 no statistics snapshot has been taken. A snapshot is taken the first
4943 time cumulative statistics are accessed in a transaction if
4944 <varname>stats_fetch_consistency</varname> is set to
4945 <literal>snapshot</literal>
4946 </para></entry>
4947 </row>
4948
4949 <row>
4950 <entry role="func_table_entry"><para role="func_signature">
4951 <indexterm>
4952 <primary>pg_stat_get_xact_blocks_fetched</primary>
4953 </indexterm>
4954 <function>pg_stat_get_xact_blocks_fetched</function> ( <type>oid</type> )
4955 <returnvalue>bigint</returnvalue>
4956 </para>
4957 <para>
4958 Returns the number of block read requests for table or index, in the
4959 current transaction. This number minus
4960 <function>pg_stat_get_xact_blocks_hit</function> gives the number of
4961 kernel <function>read()</function> calls; the number of actual
4962 physical reads is usually lower due to kernel-level buffering.
4963 </para></entry>
4964 </row>
4965
4966 <row>
4967 <entry role="func_table_entry"><para role="func_signature">
4968 <indexterm>
4969 <primary>pg_stat_get_xact_blocks_hit</primary>
4970 </indexterm>
4971 <function>pg_stat_get_xact_blocks_hit</function> ( <type>oid</type> )
4972 <returnvalue>bigint</returnvalue>
4973 </para>
4974 <para>
4975 Returns the number of block read requests for table or index, in the
4976 current transaction, found in cache (not triggering kernel
4977 <function>read()</function> calls).
4978 </para></entry>
4979 </row>
4980
4981 <row>
4982 <entry role="func_table_entry"><para role="func_signature">
4983 <indexterm>
4984 <primary>pg_stat_clear_snapshot</primary>
4985 </indexterm>
4986 <function>pg_stat_clear_snapshot</function> ()
4987 <returnvalue>void</returnvalue>
4988 </para>
4989 <para>
4990 Discards the current statistics snapshot or cached information.
4991 </para></entry>
4992 </row>
4993
4994 <row>
4995 <entry role="func_table_entry"><para role="func_signature">
4996 <indexterm>
4997 <primary>pg_stat_reset</primary>
4998 </indexterm>
4999 <function>pg_stat_reset</function> ()
5000 <returnvalue>void</returnvalue>
5001 </para>
5002 <para>
5003 Resets all statistics counters for the current database to zero.
5004 </para>
5005 <para>
5006 This function is restricted to superusers by default, but other users
5007 can be granted EXECUTE to run the function.
5008 </para></entry>
5009 </row>
5010
5011 <row>
5012 <entry role="func_table_entry"><para role="func_signature">
5013 <indexterm>
5014 <primary>pg_stat_reset_shared</primary>
5015 </indexterm>
5016 <function>pg_stat_reset_shared</function> ( [ <parameter>target</parameter> <type>text</type> <literal>DEFAULT</literal> <literal>NULL</literal> ] )
5017 <returnvalue>void</returnvalue>
5018 </para>
5019 <para>
5020 Resets some cluster-wide statistics counters to zero, depending on the
5021 argument. <parameter>target</parameter> can be:
5022 <itemizedlist>
5023 <listitem>
5024 <para>
5025 <literal>archiver</literal>: Reset all the counters shown in the
5026 <structname>pg_stat_archiver</structname> view.
5027 </para>
5028 </listitem>
5029 <listitem>
5030 <para>
5031 <literal>bgwriter</literal>: Reset all the counters shown in the
5032 <structname>pg_stat_bgwriter</structname> view.
5033 </para>
5034 </listitem>
5035 <listitem>
5036 <para>
5037 <literal>checkpointer</literal>: Reset all the counters shown in the
5038 <structname>pg_stat_checkpointer</structname> view.
5039 </para>
5040 </listitem>
5041 <listitem>
5042 <para>
5043 <literal>io</literal>: Reset all the counters shown in the
5044 <structname>pg_stat_io</structname> view.
5045 </para>
5046 </listitem>
5047 <listitem>
5048 <para>
5049 <literal>recovery_prefetch</literal>: Reset all the counters shown in
5050 the <structname>pg_stat_recovery_prefetch</structname> view.
5051 </para>
5052 </listitem>
5053 <listitem>
5054 <para>
5055 <literal>slru</literal>: Reset all the counters shown in the
5056 <structname>pg_stat_slru</structname> view.
5057 </para>
5058 </listitem>
5059 <listitem>
5060 <para>
5061 <literal>wal</literal>: Reset all the counters shown in the
5062 <structname>pg_stat_wal</structname> view.
5063 </para>
5064 </listitem>
5065 <listitem>
5066 <para>
5067 <literal>NULL</literal> or not specified: All the counters from the
5068 views listed above are reset.
5069 </para>
5070 </listitem>
5071 </itemizedlist>
5072 </para>
5073 <para>
5074 This function is restricted to superusers by default, but other users
5075 can be granted EXECUTE to run the function.
5076 </para></entry>
5077 </row>
5078
5079 <row>
5080 <entry role="func_table_entry"><para role="func_signature">
5081 <indexterm>
5082 <primary>pg_stat_reset_single_table_counters</primary>
5083 </indexterm>
5084 <function>pg_stat_reset_single_table_counters</function> ( <type>oid</type> )
5085 <returnvalue>void</returnvalue>
5086 </para>
5087 <para>
5088 Resets statistics for a single table or index in the current database
5089 or shared across all databases in the cluster to zero.
5090 </para>
5091 <para>
5092 This function is restricted to superusers by default, but other users
5093 can be granted EXECUTE to run the function.
5094 </para></entry>
5095 </row>
5096
5097 <row>
5098 <entry role="func_table_entry"><para role="func_signature">
5099 <indexterm>
5100 <primary>pg_stat_reset_backend_stats</primary>
5101 </indexterm>
5102 <function>pg_stat_reset_backend_stats</function> ( <type>integer</type> )
5103 <returnvalue>void</returnvalue>
5104 </para>
5105 <para>
5106 Resets statistics for a single backend with the specified process ID
5107 to zero.
5108 </para>
5109 <para>
5110 This function is restricted to superusers by default, but other users
5111 can be granted EXECUTE to run the function.
5112 </para></entry>
5113 </row>
5114
5115 <row>
5116 <entry role="func_table_entry"><para role="func_signature">
5117 <indexterm>
5118 <primary>pg_stat_reset_single_function_counters</primary>
5119 </indexterm>
5120 <function>pg_stat_reset_single_function_counters</function> ( <type>oid</type> )
5121 <returnvalue>void</returnvalue>
5122 </para>
5123 <para>
5124 Resets statistics for a single function in the current database to
5125 zero.
5126 </para>
5127 <para>
5128 This function is restricted to superusers by default, but other users
5129 can be granted EXECUTE to run the function.
5130 </para></entry>
5131 </row>
5132
5133 <row>
5134 <entry role="func_table_entry"><para role="func_signature">
5135 <indexterm>
5136 <primary>pg_stat_reset_slru</primary>
5137 </indexterm>
5138 <function>pg_stat_reset_slru</function> ( [ <parameter>target</parameter> <type>text</type> <literal>DEFAULT</literal> <literal>NULL</literal> ] )
5139 <returnvalue>void</returnvalue>
5140 </para>
5141 <para>
5142 Resets statistics to zero for a single SLRU cache, or for all SLRUs in
5143 the cluster. If <parameter>target</parameter> is
5144 <literal>NULL</literal> or is not specified, all the counters shown in
5145 the <structname>pg_stat_slru</structname> view for all SLRU caches are
5146 reset. The argument can be one of
5147 <literal>commit_timestamp</literal>,
5148 <literal>multixact_member</literal>,
5149 <literal>multixact_offset</literal>,
5150 <literal>notify</literal>,
5151 <literal>serializable</literal>,
5152 <literal>subtransaction</literal>, or
5153 <literal>transaction</literal>
5154 to reset the counters for only that entry.
5155 If the argument is <literal>other</literal> (or indeed, any
5156 unrecognized name), then the counters for all other SLRU caches, such
5157 as extension-defined caches, are reset.
5158 </para>
5159 <para>
5160 This function is restricted to superusers by default, but other users
5161 can be granted EXECUTE to run the function.
5162 </para></entry>
5163 </row>
5164
5165 <row>
5166 <entry role="func_table_entry"><para role="func_signature">
5167 <indexterm>
5168 <primary>pg_stat_reset_replication_slot</primary>
5169 </indexterm>
5170 <function>pg_stat_reset_replication_slot</function> ( <type>text</type> )
5171 <returnvalue>void</returnvalue>
5172 </para>
5173 <para>
5174 Resets statistics of the replication slot defined by the argument. If
5175 the argument is <literal>NULL</literal>, resets statistics for all
5176 the replication slots.
5177 </para>
5178 <para>
5179 This function is restricted to superusers by default, but other users
5180 can be granted EXECUTE to run the function.
5181 </para></entry>
5182 </row>
5183
5184 <row>
5185 <entry role="func_table_entry"><para role="func_signature">
5186 <indexterm>
5187 <primary>pg_stat_reset_subscription_stats</primary>
5188 </indexterm>
5189 <function>pg_stat_reset_subscription_stats</function> ( <type>oid</type> )
5190 <returnvalue>void</returnvalue>
5191 </para>
5192 <para>
5193 Resets statistics for a single subscription shown in the
5194 <structname>pg_stat_subscription_stats</structname> view to zero. If
5195 the argument is <literal>NULL</literal>, reset statistics for all
5196 subscriptions.
5197 </para>
5198 <para>
5199 This function is restricted to superusers by default, but other users
5200 can be granted EXECUTE to run the function.
5201 </para></entry>
5202 </row>
5203 </tbody>
5204 </tgroup>
5205 </table>
5206
5207 <warning>
5208 <para>
5209 Using <function>pg_stat_reset()</function> also resets counters that
5210 autovacuum uses to determine when to trigger a vacuum or an analyze.
5211 Resetting these counters can cause autovacuum to not perform necessary
5212 work, which can cause problems such as table bloat or out-dated
5213 table statistics. A database-wide <command>ANALYZE</command> is
5214 recommended after the statistics have been reset.
5215 </para>
5216 </warning>
5217
5218 <para>
5219 <function>pg_stat_get_activity</function>, the underlying function of
5220 the <structname>pg_stat_activity</structname> view, returns a set of records
5221 containing all the available information about each backend process.
5222 Sometimes it may be more convenient to obtain just a subset of this
5223 information. In such cases, another set of per-backend statistics
5224 access functions can be used; these are shown in <xref
5225 linkend="monitoring-stats-backend-funcs-table"/>.
5226 These access functions use the session's backend ID number, which is a
5227 small integer (>= 0) that is distinct from the backend ID of any
5228 concurrent session, although a session's ID can be recycled as soon as
5229 it exits. The backend ID is used, among other things, to identify the
5230 session's temporary schema if it has one.
5231 The function <function>pg_stat_get_backend_idset</function> provides a
5232 convenient way to list all the active backends' ID numbers for
5233 invoking these functions. For example, to show the <acronym>PID</acronym>s and
5234 current queries of all backends:
5235
5236 <programlisting>
5237 SELECT pg_stat_get_backend_pid(backendid) AS pid,
5238 pg_stat_get_backend_activity(backendid) AS query
5239 FROM pg_stat_get_backend_idset() AS backendid;
5240 </programlisting>
5241 </para>
5242
5243 <table id="monitoring-stats-backend-funcs-table">
5244 <title>Per-Backend Statistics Functions</title>
5245 <tgroup cols="1">
5246 <thead>
5247 <row>
5248 <entry role="func_table_entry"><para role="func_signature">
5249 Function
5250 </para>
5251 <para>
5252 Description
5253 </para></entry>
5254 </row>
5255 </thead>
5256
5257 <tbody>
5258 <row>
5259 <entry role="func_table_entry"><para role="func_signature">
5260 <indexterm>
5261 <primary>pg_stat_get_backend_activity</primary>
5262 </indexterm>
5263 <function>pg_stat_get_backend_activity</function> ( <type>integer</type> )
5264 <returnvalue>text</returnvalue>
5265 </para>
5266 <para>
5267 Returns the text of this backend's most recent query.
5268 </para></entry>
5269 </row>
5270
5271 <row>
5272 <entry role="func_table_entry"><para role="func_signature">
5273 <indexterm>
5274 <primary>pg_stat_get_backend_activity_start</primary>
5275 </indexterm>
5276 <function>pg_stat_get_backend_activity_start</function> ( <type>integer</type> )
5277 <returnvalue>timestamp with time zone</returnvalue>
5278 </para>
5279 <para>
5280 Returns the time when the backend's most recent query was started.
5281 </para></entry>
5282 </row>
5283
5284 <row>
5285 <entry role="func_table_entry"><para role="func_signature">
5286 <indexterm>
5287 <primary>pg_stat_get_backend_client_addr</primary>
5288 </indexterm>
5289 <function>pg_stat_get_backend_client_addr</function> ( <type>integer</type> )
5290 <returnvalue>inet</returnvalue>
5291 </para>
5292 <para>
5293 Returns the IP address of the client connected to this backend.
5294 </para></entry>
5295 </row>
5296
5297 <row>
5298 <entry role="func_table_entry"><para role="func_signature">
5299 <indexterm>
5300 <primary>pg_stat_get_backend_client_port</primary>
5301 </indexterm>
5302 <function>pg_stat_get_backend_client_port</function> ( <type>integer</type> )
5303 <returnvalue>integer</returnvalue>
5304 </para>
5305 <para>
5306 Returns the TCP port number that the client is using for communication.
5307 </para></entry>
5308 </row>
5309
5310 <row>
5311 <entry role="func_table_entry"><para role="func_signature">
5312 <indexterm>
5313 <primary>pg_stat_get_backend_dbid</primary>
5314 </indexterm>
5315 <function>pg_stat_get_backend_dbid</function> ( <type>integer</type> )
5316 <returnvalue>oid</returnvalue>
5317 </para>
5318 <para>
5319 Returns the OID of the database this backend is connected to.
5320 </para></entry>
5321 </row>
5322
5323 <row>
5324 <entry role="func_table_entry"><para role="func_signature">
5325 <indexterm>
5326 <primary>pg_stat_get_backend_idset</primary>
5327 </indexterm>
5328 <function>pg_stat_get_backend_idset</function> ()
5329 <returnvalue>setof integer</returnvalue>
5330 </para>
5331 <para>
5332 Returns the set of currently active backend ID numbers.
5333 </para></entry>
5334 </row>
5335
5336 <row>
5337 <entry role="func_table_entry"><para role="func_signature">
5338 <indexterm>
5339 <primary>pg_stat_get_backend_pid</primary>
5340 </indexterm>
5341 <function>pg_stat_get_backend_pid</function> ( <type>integer</type> )
5342 <returnvalue>integer</returnvalue>
5343 </para>
5344 <para>
5345 Returns the process ID of this backend.
5346 </para></entry>
5347 </row>
5348
5349 <row>
5350 <entry role="func_table_entry"><para role="func_signature">
5351 <indexterm>
5352 <primary>pg_stat_get_backend_start</primary>
5353 </indexterm>
5354 <function>pg_stat_get_backend_start</function> ( <type>integer</type> )
5355 <returnvalue>timestamp with time zone</returnvalue>
5356 </para>
5357 <para>
5358 Returns the time when this process was started.
5359 </para></entry>
5360 </row>
5361
5362 <row>
5363 <entry role="func_table_entry"><para role="func_signature">
5364 <indexterm>
5365 <primary>pg_stat_get_backend_subxact</primary>
5366 </indexterm>
5367 <function>pg_stat_get_backend_subxact</function> ( <type>integer</type> )
5368 <returnvalue>record</returnvalue>
5369 </para>
5370 <para>
5371 Returns a record of information about the subtransactions of the
5372 backend with the specified ID.
5373 The fields returned are <parameter>subxact_count</parameter>, which
5374 is the number of subtransactions in the backend's subtransaction cache,
5375 and <parameter>subxact_overflow</parameter>, which indicates whether
5376 the backend's subtransaction cache is overflowed or not.
5377 </para></entry>
5378 </row>
5379
5380 <row>
5381 <entry role="func_table_entry"><para role="func_signature">
5382 <indexterm>
5383 <primary>pg_stat_get_backend_userid</primary>
5384 </indexterm>
5385 <function>pg_stat_get_backend_userid</function> ( <type>integer</type> )
5386 <returnvalue>oid</returnvalue>
5387 </para>
5388 <para>
5389 Returns the OID of the user logged into this backend.
5390 </para></entry>
5391 </row>
5392
5393 <row>
5394 <entry role="func_table_entry"><para role="func_signature">
5395 <indexterm>
5396 <primary>pg_stat_get_backend_wait_event</primary>
5397 </indexterm>
5398 <function>pg_stat_get_backend_wait_event</function> ( <type>integer</type> )
5399 <returnvalue>text</returnvalue>
5400 </para>
5401 <para>
5402 Returns the wait event name if this backend is currently waiting,
5403 otherwise NULL. See <xref linkend="wait-event-activity-table"/> through
5404 <xref linkend="wait-event-timeout-table"/>.
5405 </para></entry>
5406 </row>
5407
5408 <row>
5409 <entry role="func_table_entry"><para role="func_signature">
5410 <indexterm>
5411 <primary>pg_stat_get_backend_wait_event_type</primary>
5412 </indexterm>
5413 <function>pg_stat_get_backend_wait_event_type</function> ( <type>integer</type> )
5414 <returnvalue>text</returnvalue>
5415 </para>
5416 <para>
5417 Returns the wait event type name if this backend is currently waiting,
5418 otherwise NULL. See <xref linkend="wait-event-table"/> for details.
5419 </para></entry>
5420 </row>
5421
5422 <row>
5423 <entry role="func_table_entry"><para role="func_signature">
5424 <indexterm>
5425 <primary>pg_stat_get_backend_xact_start</primary>
5426 </indexterm>
5427 <function>pg_stat_get_backend_xact_start</function> ( <type>integer</type> )
5428 <returnvalue>timestamp with time zone</returnvalue>
5429 </para>
5430 <para>
5431 Returns the time when the backend's current transaction was started.
5432 </para></entry>
5433 </row>
5434 </tbody>
5435 </tgroup>
5436 </table>
5437
5438 </sect2>
5439 </sect1>
5440
5441 <sect1 id="monitoring-locks">
5442 <title>Viewing Locks</title>
5443
5444 <indexterm zone="monitoring-locks">
5445 <primary>lock</primary>
5446 <secondary>monitoring</secondary>
5447 </indexterm>
5448
5449 <para>
5450 Another useful tool for monitoring database activity is the
5451 <structname>pg_locks</structname> system table. It allows the
5452 database administrator to view information about the outstanding
5453 locks in the lock manager. For example, this capability can be used
5454 to:
5455
5456 <itemizedlist>
5457 <listitem>
5458 <para>
5459 View all the locks currently outstanding, all the locks on
5460 relations in a particular database, all the locks on a
5461 particular relation, or all the locks held by a particular
5462 <productname>PostgreSQL</productname> session.
5463 </para>
5464 </listitem>
5465
5466 <listitem>
5467 <para>
5468 Determine the relation in the current database with the most
5469 ungranted locks (which might be a source of contention among
5470 database clients).
5471 </para>
5472 </listitem>
5473
5474 <listitem>
5475 <para>
5476 Determine the effect of lock contention on overall database
5477 performance, as well as the extent to which contention varies
5478 with overall database traffic.
5479 </para>
5480 </listitem>
5481 </itemizedlist>
5482
5483 Details of the <structname>pg_locks</structname> view appear in
5484 <xref linkend="view-pg-locks"/>.
5485 For more information on locking and managing concurrency with
5486 <productname>PostgreSQL</productname>, refer to <xref linkend="mvcc"/>.
5487 </para>
5488 </sect1>
5489
5490 <sect1 id="progress-reporting">
5491 <title>Progress Reporting</title>
5492
5493 <para>
5494 <productname>PostgreSQL</productname> has the ability to report the progress of
5495 certain commands during command execution. Currently, the only commands
5496 which support progress reporting are <command>ANALYZE</command>,
5497 <command>CLUSTER</command>,
5498 <command>CREATE INDEX</command>, <command>VACUUM</command>,
5499 <command>COPY</command>,
5500 and <xref linkend="protocol-replication-base-backup"/> (i.e., replication
5501 command that <xref linkend="app-pgbasebackup"/> issues to take
5502 a base backup).
5503 This may be expanded in the future.
5504 </para>
5505
5506 <sect2 id="analyze-progress-reporting">
5507 <title>ANALYZE Progress Reporting</title>
5508
5509 <indexterm>
5510 <primary>pg_stat_progress_analyze</primary>
5511 </indexterm>
5512
5513 <para>
5514 Whenever <command>ANALYZE</command> is running, the
5515 <structname>pg_stat_progress_analyze</structname> view will contain a
5516 row for each backend that is currently running that command. The tables
5517 below describe the information that will be reported and provide
5518 information about how to interpret it.
5519 </para>
5520
5521 <table id="pg-stat-progress-analyze-view" xreflabel="pg_stat_progress_analyze">
5522 <title><structname>pg_stat_progress_analyze</structname> View</title>
5523 <tgroup cols="1">
5524 <thead>
5525 <row>
5526 <entry role="catalog_table_entry"><para role="column_definition">
5527 Column Type
5528 </para>
5529 <para>
5530 Description
5531 </para></entry>
5532 </row>
5533 </thead>
5534
5535 <tbody>
5536 <row>
5537 <entry role="catalog_table_entry"><para role="column_definition">
5538 <structfield>pid</structfield> <type>integer</type>
5539 </para>
5540 <para>
5541 Process ID of backend.
5542 </para></entry>
5543 </row>
5544
5545 <row>
5546 <entry role="catalog_table_entry"><para role="column_definition">
5547 <structfield>datid</structfield> <type>oid</type>
5548 </para>
5549 <para>
5550 OID of the database to which this backend is connected.
5551 </para></entry>
5552 </row>
5553
5554 <row>
5555 <entry role="catalog_table_entry"><para role="column_definition">
5556 <structfield>datname</structfield> <type>name</type>
5557 </para>
5558 <para>
5559 Name of the database to which this backend is connected.
5560 </para></entry>
5561 </row>
5562
5563 <row>
5564 <entry role="catalog_table_entry"><para role="column_definition">
5565 <structfield>relid</structfield> <type>oid</type>
5566 </para>
5567 <para>
5568 OID of the table being analyzed.
5569 </para></entry>
5570 </row>
5571
5572 <row>
5573 <entry role="catalog_table_entry"><para role="column_definition">
5574 <structfield>phase</structfield> <type>text</type>
5575 </para>
5576 <para>
5577 Current processing phase. See <xref linkend="analyze-phases"/>.
5578 </para></entry>
5579 </row>
5580
5581 <row>
5582 <entry role="catalog_table_entry"><para role="column_definition">
5583 <structfield>sample_blks_total</structfield> <type>bigint</type>
5584 </para>
5585 <para>
5586 Total number of heap blocks that will be sampled.
5587 </para></entry>
5588 </row>
5589
5590 <row>
5591 <entry role="catalog_table_entry"><para role="column_definition">
5592 <structfield>sample_blks_scanned</structfield> <type>bigint</type>
5593 </para>
5594 <para>
5595 Number of heap blocks scanned.
5596 </para></entry>
5597 </row>
5598
5599 <row>
5600 <entry role="catalog_table_entry"><para role="column_definition">
5601 <structfield>ext_stats_total</structfield> <type>bigint</type>
5602 </para>
5603 <para>
5604 Number of extended statistics.
5605 </para></entry>
5606 </row>
5607
5608 <row>
5609 <entry role="catalog_table_entry"><para role="column_definition">
5610 <structfield>ext_stats_computed</structfield> <type>bigint</type>
5611 </para>
5612 <para>
5613 Number of extended statistics computed. This counter only advances
5614 when the phase is <literal>computing extended statistics</literal>.
5615 </para></entry>
5616 </row>
5617
5618 <row>
5619 <entry role="catalog_table_entry"><para role="column_definition">
5620 <structfield>child_tables_total</structfield> <type>bigint</type>
5621 </para>
5622 <para>
5623 Number of child tables.
5624 </para></entry>
5625 </row>
5626
5627 <row>
5628 <entry role="catalog_table_entry"><para role="column_definition">
5629 <structfield>child_tables_done</structfield> <type>bigint</type>
5630 </para>
5631 <para>
5632 Number of child tables scanned. This counter only advances when the
5633 phase is <literal>acquiring inherited sample rows</literal>.
5634 </para></entry>
5635 </row>
5636
5637 <row>
5638 <entry role="catalog_table_entry"><para role="column_definition">
5639 <structfield>current_child_table_relid</structfield> <type>oid</type>
5640 </para>
5641 <para>
5642 OID of the child table currently being scanned. This field is
5643 only valid when the phase is
5644 <literal>acquiring inherited sample rows</literal>.
5645 </para></entry>
5646 </row>
5647
5648 <row>
5649 <entry role="catalog_table_entry"><para role="column_definition">
5650 <structfield>delay_time</structfield> <type>double precision</type>
5651 </para>
5652 <para>
5653 Total time spent sleeping due to cost-based delay (see
5654 <xref linkend="runtime-config-resource-vacuum-cost"/>), in milliseconds
5655 (if <xref linkend="guc-track-cost-delay-timing"/> is enabled, otherwise
5656 zero).
5657 </para></entry>
5658 </row>
5659 </tbody>
5660 </tgroup>
5661 </table>
5662
5663 <table id="analyze-phases">
5664 <title>ANALYZE Phases</title>
5665 <tgroup cols="2">
5666 <colspec colname="col1" colwidth="1*"/>
5667 <colspec colname="col2" colwidth="2*"/>
5668 <thead>
5669 <row>
5670 <entry>Phase</entry>
5671 <entry>Description</entry>
5672 </row>
5673 </thead>
5674 <tbody>
5675 <row>
5676 <entry><literal>initializing</literal></entry>
5677 <entry>
5678 The command is preparing to begin scanning the heap. This phase is
5679 expected to be very brief.
5680 </entry>
5681 </row>
5682 <row>
5683 <entry><literal>acquiring sample rows</literal></entry>
5684 <entry>
5685 The command is currently scanning the table given by
5686 <structfield>relid</structfield> to obtain sample rows.
5687 </entry>
5688 </row>
5689 <row>
5690 <entry><literal>acquiring inherited sample rows</literal></entry>
5691 <entry>
5692 The command is currently scanning child tables to obtain sample rows.
5693 Columns <structfield>child_tables_total</structfield>,
5694 <structfield>child_tables_done</structfield>, and
5695 <structfield>current_child_table_relid</structfield> contain the
5696 progress information for this phase.
5697 </entry>
5698 </row>
5699 <row>
5700 <entry><literal>computing statistics</literal></entry>
5701 <entry>
5702 The command is computing statistics from the sample rows obtained
5703 during the table scan.
5704 </entry>
5705 </row>
5706 <row>
5707 <entry><literal>computing extended statistics</literal></entry>
5708 <entry>
5709 The command is computing extended statistics from the sample rows
5710 obtained during the table scan.
5711 </entry>
5712 </row>
5713 <row>
5714 <entry><literal>finalizing analyze</literal></entry>
5715 <entry>
5716 The command is updating <structname>pg_class</structname>. When this
5717 phase is completed, <command>ANALYZE</command> will end.
5718 </entry>
5719 </row>
5720 </tbody>
5721 </tgroup>
5722 </table>
5723
5724 <note>
5725 <para>
5726 Note that when <command>ANALYZE</command> is run on a partitioned table
5727 without the <literal>ONLY</literal> keyword, all of its partitions are
5728 also recursively analyzed. In that case, <command>ANALYZE</command>
5729 progress is reported first for the parent table, whereby its inheritance
5730 statistics are collected, followed by that for each partition.
5731 </para>
5732 </note>
5733 </sect2>
5734
5735 <sect2 id="cluster-progress-reporting">
5736 <title>CLUSTER Progress Reporting</title>
5737
5738 <indexterm>
5739 <primary>pg_stat_progress_cluster</primary>
5740 </indexterm>
5741
5742 <para>
5743 Whenever <command>CLUSTER</command> or <command>VACUUM FULL</command> is
5744 running, the <structname>pg_stat_progress_cluster</structname> view will
5745 contain a row for each backend that is currently running either command.
5746 The tables below describe the information that will be reported and
5747 provide information about how to interpret it.
5748 </para>
5749
5750 <table id="pg-stat-progress-cluster-view" xreflabel="pg_stat_progress_cluster">
5751 <title><structname>pg_stat_progress_cluster</structname> View</title>
5752 <tgroup cols="1">
5753 <thead>
5754 <row>
5755 <entry role="catalog_table_entry"><para role="column_definition">
5756 Column Type
5757 </para>
5758 <para>
5759 Description
5760 </para></entry>
5761 </row>
5762 </thead>
5763
5764 <tbody>
5765 <row>
5766 <entry role="catalog_table_entry"><para role="column_definition">
5767 <structfield>pid</structfield> <type>integer</type>
5768 </para>
5769 <para>
5770 Process ID of backend.
5771 </para></entry>
5772 </row>
5773
5774 <row>
5775 <entry role="catalog_table_entry"><para role="column_definition">
5776 <structfield>datid</structfield> <type>oid</type>
5777 </para>
5778 <para>
5779 OID of the database to which this backend is connected.
5780 </para></entry>
5781 </row>
5782
5783 <row>
5784 <entry role="catalog_table_entry"><para role="column_definition">
5785 <structfield>datname</structfield> <type>name</type>
5786 </para>
5787 <para>
5788 Name of the database to which this backend is connected.
5789 </para></entry>
5790 </row>
5791
5792 <row>
5793 <entry role="catalog_table_entry"><para role="column_definition">
5794 <structfield>relid</structfield> <type>oid</type>
5795 </para>
5796 <para>
5797 OID of the table being clustered.
5798 </para></entry>
5799 </row>
5800
5801 <row>
5802 <entry role="catalog_table_entry"><para role="column_definition">
5803 <structfield>command</structfield> <type>text</type>
5804 </para>
5805 <para>
5806 The command that is running. Either <literal>CLUSTER</literal> or <literal>VACUUM FULL</literal>.
5807 </para></entry>
5808 </row>
5809
5810 <row>
5811 <entry role="catalog_table_entry"><para role="column_definition">
5812 <structfield>phase</structfield> <type>text</type>
5813 </para>
5814 <para>
5815 Current processing phase. See <xref linkend="cluster-phases"/>.
5816 </para></entry>
5817 </row>
5818
5819 <row>
5820 <entry role="catalog_table_entry"><para role="column_definition">
5821 <structfield>cluster_index_relid</structfield> <type>oid</type>
5822 </para>
5823 <para>
5824 If the table is being scanned using an index, this is the OID of the
5825 index being used; otherwise, it is zero.
5826 </para></entry>
5827 </row>
5828
5829 <row>
5830 <entry role="catalog_table_entry"><para role="column_definition">
5831 <structfield>heap_tuples_scanned</structfield> <type>bigint</type>
5832 </para>
5833 <para>
5834 Number of heap tuples scanned.
5835 This counter only advances when the phase is
5836 <literal>seq scanning heap</literal>,
5837 <literal>index scanning heap</literal>
5838 or <literal>writing new heap</literal>.
5839 </para></entry>
5840 </row>
5841
5842 <row>
5843 <entry role="catalog_table_entry"><para role="column_definition">
5844 <structfield>heap_tuples_written</structfield> <type>bigint</type>
5845 </para>
5846 <para>
5847 Number of heap tuples written.
5848 This counter only advances when the phase is
5849 <literal>seq scanning heap</literal>,
5850 <literal>index scanning heap</literal>
5851 or <literal>writing new heap</literal>.
5852 </para></entry>
5853 </row>
5854
5855 <row>
5856 <entry role="catalog_table_entry"><para role="column_definition">
5857 <structfield>heap_blks_total</structfield> <type>bigint</type>
5858 </para>
5859 <para>
5860 Total number of heap blocks in the table. This number is reported
5861 as of the beginning of <literal>seq scanning heap</literal>.
5862 </para></entry>
5863 </row>
5864
5865 <row>
5866 <entry role="catalog_table_entry"><para role="column_definition">
5867 <structfield>heap_blks_scanned</structfield> <type>bigint</type>
5868 </para>
5869 <para>
5870 Number of heap blocks scanned. This counter only advances when the
5871 phase is <literal>seq scanning heap</literal>.
5872 </para></entry>
5873 </row>
5874
5875 <row>
5876 <entry role="catalog_table_entry"><para role="column_definition">
5877 <structfield>index_rebuild_count</structfield> <type>bigint</type>
5878 </para>
5879 <para>
5880 Number of indexes rebuilt. This counter only advances when the phase
5881 is <literal>rebuilding index</literal>.
5882 </para></entry>
5883 </row>
5884 </tbody>
5885 </tgroup>
5886 </table>
5887
5888 <table id="cluster-phases">
5889 <title>CLUSTER and VACUUM FULL Phases</title>
5890 <tgroup cols="2">
5891 <colspec colname="col1" colwidth="1*"/>
5892 <colspec colname="col2" colwidth="2*"/>
5893 <thead>
5894 <row>
5895 <entry>Phase</entry>
5896 <entry>Description</entry>
5897 </row>
5898 </thead>
5899
5900 <tbody>
5901 <row>
5902 <entry><literal>initializing</literal></entry>
5903 <entry>
5904 The command is preparing to begin scanning the heap. This phase is
5905 expected to be very brief.
5906 </entry>
5907 </row>
5908 <row>
5909 <entry><literal>seq scanning heap</literal></entry>
5910 <entry>
5911 The command is currently scanning the table using a sequential scan.
5912 </entry>
5913 </row>
5914 <row>
5915 <entry><literal>index scanning heap</literal></entry>
5916 <entry>
5917 <command>CLUSTER</command> is currently scanning the table using an index scan.
5918 </entry>
5919 </row>
5920 <row>
5921 <entry><literal>sorting tuples</literal></entry>
5922 <entry>
5923 <command>CLUSTER</command> is currently sorting tuples.
5924 </entry>
5925 </row>
5926 <row>
5927 <entry><literal>writing new heap</literal></entry>
5928 <entry>
5929 <command>CLUSTER</command> is currently writing the new heap.
5930 </entry>
5931 </row>
5932 <row>
5933 <entry><literal>swapping relation files</literal></entry>
5934 <entry>
5935 The command is currently swapping newly-built files into place.
5936 </entry>
5937 </row>
5938 <row>
5939 <entry><literal>rebuilding index</literal></entry>
5940 <entry>
5941 The command is currently rebuilding an index.
5942 </entry>
5943 </row>
5944 <row>
5945 <entry><literal>performing final cleanup</literal></entry>
5946 <entry>
5947 The command is performing final cleanup. When this phase is
5948 completed, <command>CLUSTER</command>
5949 or <command>VACUUM FULL</command> will end.
5950 </entry>
5951 </row>
5952 </tbody>
5953 </tgroup>
5954 </table>
5955 </sect2>
5956
5957 <sect2 id="copy-progress-reporting">
5958 <title>COPY Progress Reporting</title>
5959
5960 <indexterm>
5961 <primary>pg_stat_progress_copy</primary>
5962 </indexterm>
5963
5964 <para>
5965 Whenever <command>COPY</command> is running, the
5966 <structname>pg_stat_progress_copy</structname> view will contain one row
5967 for each backend that is currently running a <command>COPY</command> command.
5968 The table below describes the information that will be reported and provides
5969 information about how to interpret it.
5970 </para>
5971
5972 <table id="pg-stat-progress-copy-view" xreflabel="pg_stat_progress_copy">
5973 <title><structname>pg_stat_progress_copy</structname> View</title>
5974 <tgroup cols="1">
5975 <thead>
5976 <row>
5977 <entry role="catalog_table_entry"><para role="column_definition">
5978 Column Type
5979 </para>
5980 <para>
5981 Description
5982 </para></entry>
5983 </row>
5984 </thead>
5985
5986 <tbody>
5987 <row>
5988 <entry role="catalog_table_entry"><para role="column_definition">
5989 <structfield>pid</structfield> <type>integer</type>
5990 </para>
5991 <para>
5992 Process ID of backend.
5993 </para></entry>
5994 </row>
5995
5996 <row>
5997 <entry role="catalog_table_entry"><para role="column_definition">
5998 <structfield>datid</structfield> <type>oid</type>
5999 </para>
6000 <para>
6001 OID of the database to which this backend is connected.
6002 </para></entry>
6003 </row>
6004
6005 <row>
6006 <entry role="catalog_table_entry"><para role="column_definition">
6007 <structfield>datname</structfield> <type>name</type>
6008 </para>
6009 <para>
6010 Name of the database to which this backend is connected.
6011 </para></entry>
6012 </row>
6013
6014 <row>
6015 <entry role="catalog_table_entry"><para role="column_definition">
6016 <structfield>relid</structfield> <type>oid</type>
6017 </para>
6018 <para>
6019 OID of the table on which the <command>COPY</command> command is
6020 executed. It is set to <literal>0</literal> if copying from a
6021 <command>SELECT</command> query.
6022 </para></entry>
6023 </row>
6024
6025 <row>
6026 <entry role="catalog_table_entry"><para role="column_definition">
6027 <structfield>command</structfield> <type>text</type>
6028 </para>
6029 <para>
6030 The command that is running: <literal>COPY FROM</literal>, or
6031 <literal>COPY TO</literal>.
6032 </para></entry>
6033 </row>
6034
6035 <row>
6036 <entry role="catalog_table_entry"><para role="column_definition">
6037 <structfield>type</structfield> <type>text</type>
6038 </para>
6039 <para>
6040 The I/O type that the data is read from or written to:
6041 <literal>FILE</literal>, <literal>PROGRAM</literal>,
6042 <literal>PIPE</literal> (for <command>COPY FROM STDIN</command> and
6043 <command>COPY TO STDOUT</command>), or <literal>CALLBACK</literal>
6044 (used for example during the initial table synchronization in
6045 logical replication).
6046 </para></entry>
6047 </row>
6048
6049 <row>
6050 <entry role="catalog_table_entry"><para role="column_definition">
6051 <structfield>bytes_processed</structfield> <type>bigint</type>
6052 </para>
6053 <para>
6054 Number of bytes already processed by <command>COPY</command> command.
6055 </para></entry>
6056 </row>
6057
6058 <row>
6059 <entry role="catalog_table_entry"><para role="column_definition">
6060 <structfield>bytes_total</structfield> <type>bigint</type>
6061 </para>
6062 <para>
6063 Size of source file for <command>COPY FROM</command> command in bytes.
6064 It is set to <literal>0</literal> if not available.
6065 </para></entry>
6066 </row>
6067
6068 <row>
6069 <entry role="catalog_table_entry"><para role="column_definition">
6070 <structfield>tuples_processed</structfield> <type>bigint</type>
6071 </para>
6072 <para>
6073 Number of tuples already processed by <command>COPY</command> command.
6074 </para></entry>
6075 </row>
6076
6077 <row>
6078 <entry role="catalog_table_entry"><para role="column_definition">
6079 <structfield>tuples_excluded</structfield> <type>bigint</type>
6080 </para>
6081 <para>
6082 Number of tuples not processed because they were excluded by the
6083 <command>WHERE</command> clause of the <command>COPY</command> command.
6084 </para></entry>
6085 </row>
6086
6087 <row>
6088 <entry role="catalog_table_entry"><para role="column_definition">
6089 <structfield>tuples_skipped</structfield> <type>bigint</type>
6090 </para>
6091 <para>
6092 Number of tuples skipped because they contain malformed data.
6093 This counter only advances when a value other than
6094 <literal>stop</literal> is specified to the <literal>ON_ERROR</literal>
6095 option.
6096 </para></entry>
6097 </row>
6098 </tbody>
6099 </tgroup>
6100 </table>
6101 </sect2>
6102
6103 <sect2 id="create-index-progress-reporting">
6104 <title>CREATE INDEX Progress Reporting</title>
6105
6106 <indexterm>
6107 <primary>pg_stat_progress_create_index</primary>
6108 </indexterm>
6109
6110 <para>
6111 Whenever <command>CREATE INDEX</command> or <command>REINDEX</command> is running, the
6112 <structname>pg_stat_progress_create_index</structname> view will contain
6113 one row for each backend that is currently creating indexes. The tables
6114 below describe the information that will be reported and provide information
6115 about how to interpret it.
6116 </para>
6117
6118 <table id="pg-stat-progress-create-index-view" xreflabel="pg_stat_progress_create_index">
6119 <title><structname>pg_stat_progress_create_index</structname> View</title>
6120 <tgroup cols="1">
6121 <thead>
6122 <row>
6123 <entry role="catalog_table_entry"><para role="column_definition">
6124 Column Type
6125 </para>
6126 <para>
6127 Description
6128 </para></entry>
6129 </row>
6130 </thead>
6131
6132 <tbody>
6133 <row>
6134 <entry role="catalog_table_entry"><para role="column_definition">
6135 <structfield>pid</structfield> <type>integer</type>
6136 </para>
6137 <para>
6138 Process ID of the backend creating indexes.
6139 </para></entry>
6140 </row>
6141
6142 <row>
6143 <entry role="catalog_table_entry"><para role="column_definition">
6144 <structfield>datid</structfield> <type>oid</type>
6145 </para>
6146 <para>
6147 OID of the database to which this backend is connected.
6148 </para></entry>
6149 </row>
6150
6151 <row>
6152 <entry role="catalog_table_entry"><para role="column_definition">
6153 <structfield>datname</structfield> <type>name</type>
6154 </para>
6155 <para>
6156 Name of the database to which this backend is connected.
6157 </para></entry>
6158 </row>
6159
6160 <row>
6161 <entry role="catalog_table_entry"><para role="column_definition">
6162 <structfield>relid</structfield> <type>oid</type>
6163 </para>
6164 <para>
6165 OID of the table on which the index is being created.
6166 </para></entry>
6167 </row>
6168
6169 <row>
6170 <entry role="catalog_table_entry"><para role="column_definition">
6171 <structfield>index_relid</structfield> <type>oid</type>
6172 </para>
6173 <para>
6174 OID of the index being created or reindexed. During a
6175 non-concurrent <command>CREATE INDEX</command>, this is 0.
6176 </para></entry>
6177 </row>
6178
6179 <row>
6180 <entry role="catalog_table_entry"><para role="column_definition">
6181 <structfield>command</structfield> <type>text</type>
6182 </para>
6183 <para>
6184 Specific command type: <literal>CREATE INDEX</literal>,
6185 <literal>CREATE INDEX CONCURRENTLY</literal>,
6186 <literal>REINDEX</literal>, or <literal>REINDEX CONCURRENTLY</literal>.
6187 </para></entry>
6188 </row>
6189
6190 <row>
6191 <entry role="catalog_table_entry"><para role="column_definition">
6192 <structfield>phase</structfield> <type>text</type>
6193 </para>
6194 <para>
6195 Current processing phase of index creation. See <xref linkend="create-index-phases"/>.
6196 </para></entry>
6197 </row>
6198
6199 <row>
6200 <entry role="catalog_table_entry"><para role="column_definition">
6201 <structfield>lockers_total</structfield> <type>bigint</type>
6202 </para>
6203 <para>
6204 Total number of lockers to wait for, when applicable.
6205 </para></entry>
6206 </row>
6207
6208 <row>
6209 <entry role="catalog_table_entry"><para role="column_definition">
6210 <structfield>lockers_done</structfield> <type>bigint</type>
6211 </para>
6212 <para>
6213 Number of lockers already waited for.
6214 </para></entry>
6215 </row>
6216
6217 <row>
6218 <entry role="catalog_table_entry"><para role="column_definition">
6219 <structfield>current_locker_pid</structfield> <type>bigint</type>
6220 </para>
6221 <para>
6222 Process ID of the locker currently being waited for.
6223 </para></entry>
6224 </row>
6225
6226 <row>
6227 <entry role="catalog_table_entry"><para role="column_definition">
6228 <structfield>blocks_total</structfield> <type>bigint</type>
6229 </para>
6230 <para>
6231 Total number of blocks to be processed in the current phase.
6232 </para></entry>
6233 </row>
6234
6235 <row>
6236 <entry role="catalog_table_entry"><para role="column_definition">
6237 <structfield>blocks_done</structfield> <type>bigint</type>
6238 </para>
6239 <para>
6240 Number of blocks already processed in the current phase.
6241 </para></entry>
6242 </row>
6243
6244 <row>
6245 <entry role="catalog_table_entry"><para role="column_definition">
6246 <structfield>tuples_total</structfield> <type>bigint</type>
6247 </para>
6248 <para>
6249 Total number of tuples to be processed in the current phase.
6250 </para></entry>
6251 </row>
6252
6253 <row>
6254 <entry role="catalog_table_entry"><para role="column_definition">
6255 <structfield>tuples_done</structfield> <type>bigint</type>
6256 </para>
6257 <para>
6258 Number of tuples already processed in the current phase.
6259 </para></entry>
6260 </row>
6261
6262 <row>
6263 <entry role="catalog_table_entry"><para role="column_definition">
6264 <structfield>partitions_total</structfield> <type>bigint</type>
6265 </para>
6266 <para>
6267 Total number of partitions on which the index is to be created
6268 or attached, including both direct and indirect partitions.
6269 <literal>0</literal> during a <literal>REINDEX</literal>, or when
6270 the index is not partitioned.
6271 </para></entry>
6272 </row>
6273
6274 <row>
6275 <entry role="catalog_table_entry"><para role="column_definition">
6276 <structfield>partitions_done</structfield> <type>bigint</type>
6277 </para>
6278 <para>
6279 Number of partitions on which the index has already been created
6280 or attached, including both direct and indirect partitions.
6281 <literal>0</literal> during a <literal>REINDEX</literal>, or when
6282 the index is not partitioned.
6283 </para></entry>
6284 </row>
6285 </tbody>
6286 </tgroup>
6287 </table>
6288
6289 <table id="create-index-phases">
6290 <title>CREATE INDEX Phases</title>
6291 <tgroup cols="2">
6292 <colspec colname="col1" colwidth="1*"/>
6293 <colspec colname="col2" colwidth="2*"/>
6294 <thead>
6295 <row>
6296 <entry>Phase</entry>
6297 <entry>Description</entry>
6298 </row>
6299 </thead>
6300 <tbody>
6301 <row>
6302 <entry><literal>initializing</literal></entry>
6303 <entry>
6304 <command>CREATE INDEX</command> or <command>REINDEX</command> is preparing to create the index. This
6305 phase is expected to be very brief.
6306 </entry>
6307 </row>
6308 <row>
6309 <entry><literal>waiting for writers before build</literal></entry>
6310 <entry>
6311 <command>CREATE INDEX CONCURRENTLY</command> or <command>REINDEX CONCURRENTLY</command> is waiting for transactions
6312 with write locks that can potentially see the table to finish.
6313 This phase is skipped when not in concurrent mode.
6314 Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
6315 and <structname>current_locker_pid</structname> contain the progress
6316 information for this phase.
6317 </entry>
6318 </row>
6319 <row>
6320 <entry><literal>building index</literal></entry>
6321 <entry>
6322 The index is being built by the access method-specific code. In this phase,
6323 access methods that support progress reporting fill in their own progress data,
6324 and the subphase is indicated in this column. Typically,
6325 <structname>blocks_total</structname> and <structname>blocks_done</structname>
6326 will contain progress data, as well as potentially
6327 <structname>tuples_total</structname> and <structname>tuples_done</structname>.
6328 </entry>
6329 </row>
6330 <row>
6331 <entry><literal>waiting for writers before validation</literal></entry>
6332 <entry>
6333 <command>CREATE INDEX CONCURRENTLY</command> or <command>REINDEX CONCURRENTLY</command> is waiting for transactions
6334 with write locks that can potentially write into the table to finish.
6335 This phase is skipped when not in concurrent mode.
6336 Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
6337 and <structname>current_locker_pid</structname> contain the progress
6338 information for this phase.
6339 </entry>
6340 </row>
6341 <row>
6342 <entry><literal>index validation: scanning index</literal></entry>
6343 <entry>
6344 <command>CREATE INDEX CONCURRENTLY</command> is scanning the index searching
6345 for tuples that need to be validated.
6346 This phase is skipped when not in concurrent mode.
6347 Columns <structname>blocks_total</structname> (set to the total size of the index)
6348 and <structname>blocks_done</structname> contain the progress information for this phase.
6349 </entry>
6350 </row>
6351 <row>
6352 <entry><literal>index validation: sorting tuples</literal></entry>
6353 <entry>
6354 <command>CREATE INDEX CONCURRENTLY</command> is sorting the output of the
6355 index scanning phase.
6356 </entry>
6357 </row>
6358 <row>
6359 <entry><literal>index validation: scanning table</literal></entry>
6360 <entry>
6361 <command>CREATE INDEX CONCURRENTLY</command> is scanning the table
6362 to validate the index tuples collected in the previous two phases.
6363 This phase is skipped when not in concurrent mode.
6364 Columns <structname>blocks_total</structname> (set to the total size of the table)
6365 and <structname>blocks_done</structname> contain the progress information for this phase.
6366 </entry>
6367 </row>
6368 <row>
6369 <entry><literal>waiting for old snapshots</literal></entry>
6370 <entry>
6371 <command>CREATE INDEX CONCURRENTLY</command> or <command>REINDEX CONCURRENTLY</command> is waiting for transactions
6372 that can potentially see the table to release their snapshots. This
6373 phase is skipped when not in concurrent mode.
6374 Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
6375 and <structname>current_locker_pid</structname> contain the progress
6376 information for this phase.
6377 </entry>
6378 </row>
6379 <row>
6380 <entry><literal>waiting for readers before marking dead</literal></entry>
6381 <entry>
6382 <command>REINDEX CONCURRENTLY</command> is waiting for transactions
6383 with read locks on the table to finish, before marking the old index dead.
6384 This phase is skipped when not in concurrent mode.
6385 Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
6386 and <structname>current_locker_pid</structname> contain the progress
6387 information for this phase.
6388 </entry>
6389 </row>
6390 <row>
6391 <entry><literal>waiting for readers before dropping</literal></entry>
6392 <entry>
6393 <command>REINDEX CONCURRENTLY</command> is waiting for transactions
6394 with read locks on the table to finish, before dropping the old index.
6395 This phase is skipped when not in concurrent mode.
6396 Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
6397 and <structname>current_locker_pid</structname> contain the progress
6398 information for this phase.
6399 </entry>
6400 </row>
6401 </tbody>
6402 </tgroup>
6403 </table>
6404
6405 </sect2>
6406
6407 <sect2 id="vacuum-progress-reporting">
6408 <title>VACUUM Progress Reporting</title>
6409
6410 <indexterm>
6411 <primary>pg_stat_progress_vacuum</primary>
6412 </indexterm>
6413
6414 <para>
6415 Whenever <command>VACUUM</command> is running, the
6416 <structname>pg_stat_progress_vacuum</structname> view will contain
6417 one row for each backend (including autovacuum worker processes) that is
6418 currently vacuuming. The tables below describe the information
6419 that will be reported and provide information about how to interpret it.
6420 Progress for <command>VACUUM FULL</command> commands is reported via
6421 <structname>pg_stat_progress_cluster</structname>
6422 because both <command>VACUUM FULL</command> and <command>CLUSTER</command>
6423 rewrite the table, while regular <command>VACUUM</command> only modifies it
6424 in place. See <xref linkend="cluster-progress-reporting"/>.
6425 </para>
6426
6427 <table id="pg-stat-progress-vacuum-view" xreflabel="pg_stat_progress_vacuum">
6428 <title><structname>pg_stat_progress_vacuum</structname> View</title>
6429 <tgroup cols="1">
6430 <thead>
6431 <row>
6432 <entry role="catalog_table_entry"><para role="column_definition">
6433 Column Type
6434 </para>
6435 <para>
6436 Description
6437 </para></entry>
6438 </row>
6439 </thead>
6440
6441 <tbody>
6442 <row>
6443 <entry role="catalog_table_entry"><para role="column_definition">
6444 <structfield>pid</structfield> <type>integer</type>
6445 </para>
6446 <para>
6447 Process ID of backend.
6448 </para></entry>
6449 </row>
6450
6451 <row>
6452 <entry role="catalog_table_entry"><para role="column_definition">
6453 <structfield>datid</structfield> <type>oid</type>
6454 </para>
6455 <para>
6456 OID of the database to which this backend is connected.
6457 </para></entry>
6458 </row>
6459
6460 <row>
6461 <entry role="catalog_table_entry"><para role="column_definition">
6462 <structfield>datname</structfield> <type>name</type>
6463 </para>
6464 <para>
6465 Name of the database to which this backend is connected.
6466 </para></entry>
6467 </row>
6468
6469 <row>
6470 <entry role="catalog_table_entry"><para role="column_definition">
6471 <structfield>relid</structfield> <type>oid</type>
6472 </para>
6473 <para>
6474 OID of the table being vacuumed.
6475 </para></entry>
6476 </row>
6477
6478 <row>
6479 <entry role="catalog_table_entry"><para role="column_definition">
6480 <structfield>phase</structfield> <type>text</type>
6481 </para>
6482 <para>
6483 Current processing phase of vacuum. See <xref linkend="vacuum-phases"/>.
6484 </para></entry>
6485 </row>
6486
6487 <row>
6488 <entry role="catalog_table_entry"><para role="column_definition">
6489 <structfield>heap_blks_total</structfield> <type>bigint</type>
6490 </para>
6491 <para>
6492 Total number of heap blocks in the table. This number is reported
6493 as of the beginning of the scan; blocks added later will not be (and
6494 need not be) visited by this <command>VACUUM</command>.
6495 </para></entry>
6496 </row>
6497
6498 <row>
6499 <entry role="catalog_table_entry"><para role="column_definition">
6500 <structfield>heap_blks_scanned</structfield> <type>bigint</type>
6501 </para>
6502 <para>
6503 Number of heap blocks scanned. Because the
6504 <link linkend="storage-vm">visibility map</link> is used to optimize scans,
6505 some blocks will be skipped without inspection; skipped blocks are
6506 included in this total, so that this number will eventually become
6507 equal to <structfield>heap_blks_total</structfield> when the vacuum is complete.
6508 This counter only advances when the phase is <literal>scanning heap</literal>.
6509 </para></entry>
6510 </row>
6511
6512 <row>
6513 <entry role="catalog_table_entry"><para role="column_definition">
6514 <structfield>heap_blks_vacuumed</structfield> <type>bigint</type>
6515 </para>
6516 <para>
6517 Number of heap blocks vacuumed. Unless the table has no indexes, this
6518 counter only advances when the phase is <literal>vacuuming heap</literal>.
6519 Blocks that contain no dead tuples are skipped, so the counter may
6520 sometimes skip forward in large increments.
6521 </para></entry>
6522 </row>
6523
6524 <row>
6525 <entry role="catalog_table_entry"><para role="column_definition">
6526 <structfield>index_vacuum_count</structfield> <type>bigint</type>
6527 </para>
6528 <para>
6529 Number of completed index vacuum cycles.
6530 </para></entry>
6531 </row>
6532
6533 <row>
6534 <entry role="catalog_table_entry"><para role="column_definition">
6535 <structfield>max_dead_tuple_bytes</structfield> <type>bigint</type>
6536 </para>
6537 <para>
6538 Amount of dead tuple data that we can store before needing to perform
6539 an index vacuum cycle, based on
6540 <xref linkend="guc-maintenance-work-mem"/>.
6541 </para></entry>
6542 </row>
6543
6544 <row>
6545 <entry role="catalog_table_entry"><para role="column_definition">
6546 <structfield>dead_tuple_bytes</structfield> <type>bigint</type>
6547 </para>
6548 <para>
6549 Amount of dead tuple data collected since the last index vacuum cycle.
6550 </para></entry>
6551 </row>
6552
6553 <row>
6554 <entry role="catalog_table_entry"><para role="column_definition">
6555 <structfield>num_dead_item_ids</structfield> <type>bigint</type>
6556 </para>
6557 <para>
6558 Number of dead item identifiers collected since the last index vacuum cycle.
6559 </para></entry>
6560 </row>
6561
6562 <row>
6563 <entry role="catalog_table_entry"><para role="column_definition">
6564 <structfield>indexes_total</structfield> <type>bigint</type>
6565 </para>
6566 <para>
6567 Total number of indexes that will be vacuumed or cleaned up. This
6568 number is reported at the beginning of the
6569 <literal>vacuuming indexes</literal> phase or the
6570 <literal>cleaning up indexes</literal> phase.
6571 </para></entry>
6572 </row>
6573
6574 <row>
6575 <entry role="catalog_table_entry"><para role="column_definition">
6576 <structfield>indexes_processed</structfield> <type>bigint</type>
6577 </para>
6578 <para>
6579 Number of indexes processed. This counter only advances when the
6580 phase is <literal>vacuuming indexes</literal> or
6581 <literal>cleaning up indexes</literal>.
6582 </para></entry>
6583 </row>
6584
6585 <row>
6586 <entry role="catalog_table_entry"><para role="column_definition">
6587 <structfield>delay_time</structfield> <type>double precision</type>
6588 </para>
6589 <para>
6590 Total time spent sleeping due to cost-based delay (see
6591 <xref linkend="runtime-config-resource-vacuum-cost"/>), in milliseconds
6592 (if <xref linkend="guc-track-cost-delay-timing"/> is enabled, otherwise
6593 zero). This includes the time that any associated parallel workers have
6594 slept. However, parallel workers report their sleep time no more
6595 frequently than once per second, so the reported value may be slightly
6596 stale.
6597 </para></entry>
6598 </row>
6599 </tbody>
6600 </tgroup>
6601 </table>
6602
6603 <table id="vacuum-phases">
6604 <title>VACUUM Phases</title>
6605 <tgroup cols="2">
6606 <colspec colname="col1" colwidth="1*"/>
6607 <colspec colname="col2" colwidth="2*"/>
6608 <thead>
6609 <row>
6610 <entry>Phase</entry>
6611 <entry>Description</entry>
6612 </row>
6613 </thead>
6614
6615 <tbody>
6616 <row>
6617 <entry><literal>initializing</literal></entry>
6618 <entry>
6619 <command>VACUUM</command> is preparing to begin scanning the heap. This
6620 phase is expected to be very brief.
6621 </entry>
6622 </row>
6623 <row>
6624 <entry><literal>scanning heap</literal></entry>
6625 <entry>
6626 <command>VACUUM</command> is currently scanning the heap. It will prune and
6627 defragment each page if required, and possibly perform freezing
6628 activity. The <structfield>heap_blks_scanned</structfield> column can be used
6629 to monitor the progress of the scan.
6630 </entry>
6631 </row>
6632 <row>
6633 <entry><literal>vacuuming indexes</literal></entry>
6634 <entry>
6635 <command>VACUUM</command> is currently vacuuming the indexes. If a table has
6636 any indexes, this will happen at least once per vacuum, after the heap
6637 has been completely scanned. It may happen multiple times per vacuum
6638 if <xref linkend="guc-maintenance-work-mem"/> (or, in the case of autovacuum,
6639 <xref linkend="guc-autovacuum-work-mem"/> if set) is insufficient to store
6640 the number of dead tuples found.
6641 </entry>
6642 </row>
6643 <row>
6644 <entry><literal>vacuuming heap</literal></entry>
6645 <entry>
6646 <command>VACUUM</command> is currently vacuuming the heap. Vacuuming the heap
6647 is distinct from scanning the heap, and occurs after each instance of
6648 vacuuming indexes. If <structfield>heap_blks_scanned</structfield> is less than
6649 <structfield>heap_blks_total</structfield>, the system will return to scanning
6650 the heap after this phase is completed; otherwise, it will begin
6651 cleaning up indexes after this phase is completed.
6652 </entry>
6653 </row>
6654 <row>
6655 <entry><literal>cleaning up indexes</literal></entry>
6656 <entry>
6657 <command>VACUUM</command> is currently cleaning up indexes. This occurs after
6658 the heap has been completely scanned and all vacuuming of the indexes
6659 and the heap has been completed.
6660 </entry>
6661 </row>
6662 <row>
6663 <entry><literal>truncating heap</literal></entry>
6664 <entry>
6665 <command>VACUUM</command> is currently truncating the heap so as to return
6666 empty pages at the end of the relation to the operating system. This
6667 occurs after cleaning up indexes.
6668 </entry>
6669 </row>
6670 <row>
6671 <entry><literal>performing final cleanup</literal></entry>
6672 <entry>
6673 <command>VACUUM</command> is performing final cleanup. During this phase,
6674 <command>VACUUM</command> will vacuum the free space map, update statistics
6675 in <literal>pg_class</literal>, and report statistics to the cumulative
6676 statistics system. When this phase is completed, <command>VACUUM</command> will end.
6677 </entry>
6678 </row>
6679 </tbody>
6680 </tgroup>
6681 </table>
6682 </sect2>
6683
6684 <sect2 id="basebackup-progress-reporting">
6685 <title>Base Backup Progress Reporting</title>
6686
6687 <indexterm>
6688 <primary>pg_stat_progress_basebackup</primary>
6689 </indexterm>
6690
6691 <para>
6692 Whenever an application like <application>pg_basebackup</application>
6693 is taking a base backup, the
6694 <structname>pg_stat_progress_basebackup</structname>
6695 view will contain a row for each WAL sender process that is currently
6696 running the <command>BASE_BACKUP</command> replication command
6697 and streaming the backup. The tables below describe the information
6698 that will be reported and provide information about how to interpret it.
6699 </para>
6700
6701 <table id="pg-stat-progress-basebackup-view" xreflabel="pg_stat_progress_basebackup">
6702 <title><structname>pg_stat_progress_basebackup</structname> View</title>
6703 <tgroup cols="1">
6704 <thead>
6705 <row>
6706 <entry role="catalog_table_entry"><para role="column_definition">
6707 Column Type
6708 </para>
6709 <para>
6710 Description
6711 </para></entry>
6712 </row>
6713 </thead>
6714
6715 <tbody>
6716 <row>
6717 <entry role="catalog_table_entry"><para role="column_definition">
6718 <structfield>pid</structfield> <type>integer</type>
6719 </para>
6720 <para>
6721 Process ID of a WAL sender process.
6722 </para></entry>
6723 </row>
6724
6725 <row>
6726 <entry role="catalog_table_entry"><para role="column_definition">
6727 <structfield>phase</structfield> <type>text</type>
6728 </para>
6729 <para>
6730 Current processing phase. See <xref linkend="basebackup-phases"/>.
6731 </para></entry>
6732 </row>
6733
6734 <row>
6735 <entry role="catalog_table_entry"><para role="column_definition">
6736 <structfield>backup_total</structfield> <type>bigint</type>
6737 </para>
6738 <para>
6739 Total amount of data that will be streamed. This is estimated and
6740 reported as of the beginning of
6741 <literal>streaming database files</literal> phase. Note that
6742 this is only an approximation since the database
6743 may change during <literal>streaming database files</literal> phase
6744 and WAL log may be included in the backup later. This is always
6745 the same value as <structfield>backup_streamed</structfield>
6746 once the amount of data streamed exceeds the estimated
6747 total size. If the estimation is disabled in
6748 <application>pg_basebackup</application>
6749 (i.e., <literal>--no-estimate-size</literal> option is specified),
6750 this is <literal>NULL</literal>.
6751 </para></entry>
6752 </row>
6753
6754 <row>
6755 <entry role="catalog_table_entry"><para role="column_definition">
6756 <structfield>backup_streamed</structfield> <type>bigint</type>
6757 </para>
6758 <para>
6759 Amount of data streamed. This counter only advances
6760 when the phase is <literal>streaming database files</literal> or
6761 <literal>transferring wal files</literal>.
6762 </para></entry>
6763 </row>
6764
6765 <row>
6766 <entry role="catalog_table_entry"><para role="column_definition">
6767 <structfield>tablespaces_total</structfield> <type>bigint</type>
6768 </para>
6769 <para>
6770 Total number of tablespaces that will be streamed.
6771 </para></entry>
6772 </row>
6773
6774 <row>
6775 <entry role="catalog_table_entry"><para role="column_definition">
6776 <structfield>tablespaces_streamed</structfield> <type>bigint</type>
6777 </para>
6778 <para>
6779 Number of tablespaces streamed. This counter only
6780 advances when the phase is <literal>streaming database files</literal>.
6781 </para></entry>
6782 </row>
6783 </tbody>
6784 </tgroup>
6785 </table>
6786
6787 <table id="basebackup-phases">
6788 <title>Base Backup Phases</title>
6789 <tgroup cols="2">
6790 <colspec colname="col1" colwidth="1*"/>
6791 <colspec colname="col2" colwidth="2*"/>
6792 <thead>
6793 <row>
6794 <entry>Phase</entry>
6795 <entry>Description</entry>
6796 </row>
6797 </thead>
6798 <tbody>
6799 <row>
6800 <entry><literal>initializing</literal></entry>
6801 <entry>
6802 The WAL sender process is preparing to begin the backup.
6803 This phase is expected to be very brief.
6804 </entry>
6805 </row>
6806 <row>
6807 <entry><literal>waiting for checkpoint to finish</literal></entry>
6808 <entry>
6809 The WAL sender process is currently performing
6810 <function>pg_backup_start</function> to prepare to
6811 take a base backup, and waiting for the start-of-backup
6812 checkpoint to finish.
6813 </entry>
6814 </row>
6815 <row>
6816 <entry><literal>estimating backup size</literal></entry>
6817 <entry>
6818 The WAL sender process is currently estimating the total amount
6819 of database files that will be streamed as a base backup.
6820 </entry>
6821 </row>
6822 <row>
6823 <entry><literal>streaming database files</literal></entry>
6824 <entry>
6825 The WAL sender process is currently streaming database files
6826 as a base backup.
6827 </entry>
6828 </row>
6829 <row>
6830 <entry><literal>waiting for wal archiving to finish</literal></entry>
6831 <entry>
6832 The WAL sender process is currently performing
6833 <function>pg_backup_stop</function> to finish the backup,
6834 and waiting for all the WAL files required for the base backup
6835 to be successfully archived.
6836 If either <literal>--wal-method=none</literal> or
6837 <literal>--wal-method=stream</literal> is specified in
6838 <application>pg_basebackup</application>, the backup will end
6839 when this phase is completed.
6840 </entry>
6841 </row>
6842 <row>
6843 <entry><literal>transferring wal files</literal></entry>
6844 <entry>
6845 The WAL sender process is currently transferring all WAL logs
6846 generated during the backup. This phase occurs after
6847 <literal>waiting for wal archiving to finish</literal> phase if
6848 <literal>--wal-method=fetch</literal> is specified in
6849 <application>pg_basebackup</application>. The backup will end
6850 when this phase is completed.
6851 </entry>
6852 </row>
6853 </tbody>
6854 </tgroup>
6855 </table>
6856
6857 </sect2>
6858
6859 </sect1>
6860
6861 <sect1 id="dynamic-trace">
6862 <title>Dynamic Tracing</title>
6863
6864 <indexterm zone="dynamic-trace">
6865 <primary>DTrace</primary>
6866 </indexterm>
6867
6868 <para>
6869 <productname>PostgreSQL</productname> provides facilities to support
6870 dynamic tracing of the database server. This allows an external
6871 utility to be called at specific points in the code and thereby trace
6872 execution.
6873 </para>
6874
6875 <para>
6876 A number of probes or trace points are already inserted into the source
6877 code. These probes are intended to be used by database developers and
6878 administrators. By default the probes are not compiled into
6879 <productname>PostgreSQL</productname>; the user needs to explicitly tell
6880 the configure script to make the probes available.
6881 </para>
6882
6883 <para>
6884 Currently, the
6885 <ulink url="https://en.wikipedia.org/wiki/DTrace">DTrace</ulink>
6886 utility is supported, which, at the time of this writing, is available
6887 on Solaris, macOS, FreeBSD, NetBSD, and Oracle Linux. The
6888 <ulink url="https://sourceware.org/systemtap/">SystemTap</ulink> project
6889 for Linux provides a DTrace equivalent and can also be used. Supporting other dynamic
6890 tracing utilities is theoretically possible by changing the definitions for
6891 the macros in <filename>src/include/utils/probes.h</filename>.
6892 </para>
6893
6894 <sect2 id="compiling-for-trace">
6895 <title>Compiling for Dynamic Tracing</title>
6896
6897 <para>
6898 By default, probes are not available, so you will need to
6899 explicitly tell the configure script to make the probes available
6900 in <productname>PostgreSQL</productname>. To include DTrace support
6901 specify <option>--enable-dtrace</option> to configure. See <xref
6902 linkend="configure-options-devel"/> for further information.
6903 </para>
6904 </sect2>
6905
6906 <sect2 id="trace-points">
6907 <title>Built-in Probes</title>
6908
6909 <para>
6910 A number of standard probes are provided in the source code,
6911 as shown in <xref linkend="dtrace-probe-point-table"/>;
6912 <xref linkend="typedefs-table"/>
6913 shows the types used in the probes. More probes can certainly be
6914 added to enhance <productname>PostgreSQL</productname>'s observability.
6915 </para>
6916
6917 <table id="dtrace-probe-point-table">
6918 <title>Built-in DTrace Probes</title>
6919 <tgroup cols="3">
6920 <colspec colname="col1" colwidth="2*"/>
6921 <colspec colname="col2" colwidth="3*"/>
6922 <colspec colname="col3" colwidth="3*"/>
6923 <thead>
6924 <row>
6925 <entry>Name</entry>
6926 <entry>Parameters</entry>
6927 <entry>Description</entry>
6928 </row>
6929 </thead>
6930
6931 <tbody>
6932
6933 <row>
6934 <entry><literal>transaction-start</literal></entry>
6935 <entry><literal>(LocalTransactionId)</literal></entry>
6936 <entry>Probe that fires at the start of a new transaction.
6937 arg0 is the transaction ID.</entry>
6938 </row>
6939 <row>
6940 <entry><literal>transaction-commit</literal></entry>
6941 <entry><literal>(LocalTransactionId)</literal></entry>
6942 <entry>Probe that fires when a transaction completes successfully.
6943 arg0 is the transaction ID.</entry>
6944 </row>
6945 <row>
6946 <entry><literal>transaction-abort</literal></entry>
6947 <entry><literal>(LocalTransactionId)</literal></entry>
6948 <entry>Probe that fires when a transaction completes unsuccessfully.
6949 arg0 is the transaction ID.</entry>
6950 </row>
6951 <row>
6952 <entry><literal>query-start</literal></entry>
6953 <entry><literal>(const char *)</literal></entry>
6954 <entry>Probe that fires when the processing of a query is started.
6955 arg0 is the query string.</entry>
6956 </row>
6957 <row>
6958 <entry><literal>query-done</literal></entry>
6959 <entry><literal>(const char *)</literal></entry>
6960 <entry>Probe that fires when the processing of a query is complete.
6961 arg0 is the query string.</entry>
6962 </row>
6963 <row>
6964 <entry><literal>query-parse-start</literal></entry>
6965 <entry><literal>(const char *)</literal></entry>
6966 <entry>Probe that fires when the parsing of a query is started.
6967 arg0 is the query string.</entry>
6968 </row>
6969 <row>
6970 <entry><literal>query-parse-done</literal></entry>
6971 <entry><literal>(const char *)</literal></entry>
6972 <entry>Probe that fires when the parsing of a query is complete.
6973 arg0 is the query string.</entry>
6974 </row>
6975 <row>
6976 <entry><literal>query-rewrite-start</literal></entry>
6977 <entry><literal>(const char *)</literal></entry>
6978 <entry>Probe that fires when the rewriting of a query is started.
6979 arg0 is the query string.</entry>
6980 </row>
6981 <row>
6982 <entry><literal>query-rewrite-done</literal></entry>
6983 <entry><literal>(const char *)</literal></entry>
6984 <entry>Probe that fires when the rewriting of a query is complete.
6985 arg0 is the query string.</entry>
6986 </row>
6987 <row>
6988 <entry><literal>query-plan-start</literal></entry>
6989 <entry><literal>()</literal></entry>
6990 <entry>Probe that fires when the planning of a query is started.</entry>
6991 </row>
6992 <row>
6993 <entry><literal>query-plan-done</literal></entry>
6994 <entry><literal>()</literal></entry>
6995 <entry>Probe that fires when the planning of a query is complete.</entry>
6996 </row>
6997 <row>
6998 <entry><literal>query-execute-start</literal></entry>
6999 <entry><literal>()</literal></entry>
7000 <entry>Probe that fires when the execution of a query is started.</entry>
7001 </row>
7002 <row>
7003 <entry><literal>query-execute-done</literal></entry>
7004 <entry><literal>()</literal></entry>
7005 <entry>Probe that fires when the execution of a query is complete.</entry>
7006 </row>
7007 <row>
7008 <entry><literal>statement-status</literal></entry>
7009 <entry><literal>(const char *)</literal></entry>
7010 <entry>Probe that fires anytime the server process updates its
7011 <structname>pg_stat_activity</structname>.<structfield>status</structfield>.
7012 arg0 is the new status string.</entry>
7013 </row>
7014 <row>
7015 <entry><literal>checkpoint-start</literal></entry>
7016 <entry><literal>(int)</literal></entry>
7017 <entry>Probe that fires when a checkpoint is started.
7018 arg0 holds the bitwise flags used to distinguish different checkpoint
7019 types, such as shutdown, immediate or force.</entry>
7020 </row>
7021 <row>
7022 <entry><literal>checkpoint-done</literal></entry>
7023 <entry><literal>(int, int, int, int, int)</literal></entry>
7024 <entry>Probe that fires when a checkpoint is complete.
7025 (The probes listed next fire in sequence during checkpoint processing.)
7026 arg0 is the number of buffers written. arg1 is the total number of
7027 buffers. arg2, arg3 and arg4 contain the number of WAL files added,
7028 removed and recycled respectively.</entry>
7029 </row>
7030 <row>
7031 <entry><literal>clog-checkpoint-start</literal></entry>
7032 <entry><literal>(bool)</literal></entry>
7033 <entry>Probe that fires when the CLOG portion of a checkpoint is started.
7034 arg0 is true for normal checkpoint, false for shutdown
7035 checkpoint.</entry>
7036 </row>
7037 <row>
7038 <entry><literal>clog-checkpoint-done</literal></entry>
7039 <entry><literal>(bool)</literal></entry>
7040 <entry>Probe that fires when the CLOG portion of a checkpoint is
7041 complete. arg0 has the same meaning as for <literal>clog-checkpoint-start</literal>.</entry>
7042 </row>
7043 <row>
7044 <entry><literal>subtrans-checkpoint-start</literal></entry>
7045 <entry><literal>(bool)</literal></entry>
7046 <entry>Probe that fires when the SUBTRANS portion of a checkpoint is
7047 started.
7048 arg0 is true for normal checkpoint, false for shutdown
7049 checkpoint.</entry>
7050 </row>
7051 <row>
7052 <entry><literal>subtrans-checkpoint-done</literal></entry>
7053 <entry><literal>(bool)</literal></entry>
7054 <entry>Probe that fires when the SUBTRANS portion of a checkpoint is
7055 complete. arg0 has the same meaning as for
7056 <literal>subtrans-checkpoint-start</literal>.</entry>
7057 </row>
7058 <row>
7059 <entry><literal>multixact-checkpoint-start</literal></entry>
7060 <entry><literal>(bool)</literal></entry>
7061 <entry>Probe that fires when the MultiXact portion of a checkpoint is
7062 started.
7063 arg0 is true for normal checkpoint, false for shutdown
7064 checkpoint.</entry>
7065 </row>
7066 <row>
7067 <entry><literal>multixact-checkpoint-done</literal></entry>
7068 <entry><literal>(bool)</literal></entry>
7069 <entry>Probe that fires when the MultiXact portion of a checkpoint is
7070 complete. arg0 has the same meaning as for
7071 <literal>multixact-checkpoint-start</literal>.</entry>
7072 </row>
7073 <row>
7074 <entry><literal>buffer-checkpoint-start</literal></entry>
7075 <entry><literal>(int)</literal></entry>
7076 <entry>Probe that fires when the buffer-writing portion of a checkpoint
7077 is started.
7078 arg0 holds the bitwise flags used to distinguish different checkpoint
7079 types, such as shutdown, immediate or force.</entry>
7080 </row>
7081 <row>
7082 <entry><literal>buffer-sync-start</literal></entry>
7083 <entry><literal>(int, int)</literal></entry>
7084 <entry>Probe that fires when we begin to write dirty buffers during
7085 checkpoint (after identifying which buffers must be written).
7086 arg0 is the total number of buffers.
7087 arg1 is the number that are currently dirty and need to be written.</entry>
7088 </row>
7089 <row>
7090 <entry><literal>buffer-sync-written</literal></entry>
7091 <entry><literal>(int)</literal></entry>
7092 <entry>Probe that fires after each buffer is written during checkpoint.
7093 arg0 is the ID number of the buffer.</entry>
7094 </row>
7095 <row>
7096 <entry><literal>buffer-sync-done</literal></entry>
7097 <entry><literal>(int, int, int)</literal></entry>
7098 <entry>Probe that fires when all dirty buffers have been written.
7099 arg0 is the total number of buffers.
7100 arg1 is the number of buffers actually written by the checkpoint process.
7101 arg2 is the number that were expected to be written (arg1 of
7102 <literal>buffer-sync-start</literal>); any difference reflects other processes flushing
7103 buffers during the checkpoint.</entry>
7104 </row>
7105 <row>
7106 <entry><literal>buffer-checkpoint-sync-start</literal></entry>
7107 <entry><literal>()</literal></entry>
7108 <entry>Probe that fires after dirty buffers have been written to the
7109 kernel, and before starting to issue fsync requests.</entry>
7110 </row>
7111 <row>
7112 <entry><literal>buffer-checkpoint-done</literal></entry>
7113 <entry><literal>()</literal></entry>
7114 <entry>Probe that fires when syncing of buffers to disk is
7115 complete.</entry>
7116 </row>
7117 <row>
7118 <entry><literal>twophase-checkpoint-start</literal></entry>
7119 <entry><literal>()</literal></entry>
7120 <entry>Probe that fires when the two-phase portion of a checkpoint is
7121 started.</entry>
7122 </row>
7123 <row>
7124 <entry><literal>twophase-checkpoint-done</literal></entry>
7125 <entry><literal>()</literal></entry>
7126 <entry>Probe that fires when the two-phase portion of a checkpoint is
7127 complete.</entry>
7128 </row>
7129 <row>
7130 <entry><literal>buffer-extend-start</literal></entry>
7131 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, unsigned int)</literal></entry>
7132 <entry>Probe that fires when a relation extension starts.
7133 arg0 contains the fork to be extended. arg1, arg2, and arg3 contain the
7134 tablespace, database, and relation OIDs identifying the relation. arg4
7135 is the ID of the backend which created the temporary relation for a
7136 local buffer, or <symbol>INVALID_PROC_NUMBER</symbol> (-1) for a shared
7137 buffer. arg5 is the number of blocks the caller would like to extend
7138 by.</entry>
7139 </row>
7140 <row>
7141 <entry><literal>buffer-extend-done</literal></entry>
7142 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, unsigned int, BlockNumber)</literal></entry>
7143 <entry>Probe that fires when a relation extension is complete.
7144 arg0 contains the fork to be extended. arg1, arg2, and arg3 contain the
7145 tablespace, database, and relation OIDs identifying the relation. arg4
7146 is the ID of the backend which created the temporary relation for a
7147 local buffer, or <symbol>INVALID_PROC_NUMBER</symbol> (-1) for a shared
7148 buffer. arg5 is the number of blocks the relation was extended by, this
7149 can be less than the number in the
7150 <literal>buffer-extend-start</literal> due to resource
7151 constraints. arg6 contains the BlockNumber of the first new
7152 block.</entry>
7153 </row>
7154 <row>
7155 <entry><literal>buffer-read-start</literal></entry>
7156 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int)</literal></entry>
7157 <entry>Probe that fires when a buffer read is started.
7158 arg0 and arg1 contain the fork and block numbers of the page.
7159 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
7160 identifying the relation.
7161 arg5 is the ID of the backend which created the temporary relation for a
7162 local buffer, or <symbol>INVALID_PROC_NUMBER</symbol> (-1) for a shared buffer.
7163 </entry>
7164 </row>
7165 <row>
7166 <entry><literal>buffer-read-done</literal></entry>
7167 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, bool)</literal></entry>
7168 <entry>Probe that fires when a buffer read is complete.
7169 arg0 and arg1 contain the fork and block numbers of the page.
7170 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
7171 identifying the relation.
7172 arg5 is the ID of the backend which created the temporary relation for a
7173 local buffer, or <symbol>INVALID_PROC_NUMBER</symbol> (-1) for a shared buffer.
7174 arg6 is true if the buffer was found in the pool, false if not.</entry>
7175 </row>
7176 <row>
7177 <entry><literal>buffer-flush-start</literal></entry>
7178 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid)</literal></entry>
7179 <entry>Probe that fires before issuing any write request for a shared
7180 buffer.
7181 arg0 and arg1 contain the fork and block numbers of the page.
7182 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
7183 identifying the relation.</entry>
7184 </row>
7185 <row>
7186 <entry><literal>buffer-flush-done</literal></entry>
7187 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid)</literal></entry>
7188 <entry>Probe that fires when a write request is complete. (Note
7189 that this just reflects the time to pass the data to the kernel;
7190 it's typically not actually been written to disk yet.)
7191 The arguments are the same as for <literal>buffer-flush-start</literal>.</entry>
7192 </row>
7193 <row>
7194 <entry><literal>wal-buffer-write-dirty-start</literal></entry>
7195 <entry><literal>()</literal></entry>
7196 <entry>Probe that fires when a server process begins to write a
7197 dirty WAL buffer because no more WAL buffer space is available.
7198 (If this happens often, it implies that
7199 <xref linkend="guc-wal-buffers"/> is too small.)</entry>
7200 </row>
7201 <row>
7202 <entry><literal>wal-buffer-write-dirty-done</literal></entry>
7203 <entry><literal>()</literal></entry>
7204 <entry>Probe that fires when a dirty WAL buffer write is complete.</entry>
7205 </row>
7206 <row>
7207 <entry><literal>wal-insert</literal></entry>
7208 <entry><literal>(unsigned char, unsigned char)</literal></entry>
7209 <entry>Probe that fires when a WAL record is inserted.
7210 arg0 is the resource manager (rmid) for the record.
7211 arg1 contains the info flags.</entry>
7212 </row>
7213 <row>
7214 <entry><literal>wal-switch</literal></entry>
7215 <entry><literal>()</literal></entry>
7216 <entry>Probe that fires when a WAL segment switch is requested.</entry>
7217 </row>
7218 <row>
7219 <entry><literal>smgr-md-read-start</literal></entry>
7220 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int)</literal></entry>
7221 <entry>Probe that fires when beginning to read a block from a relation.
7222 arg0 and arg1 contain the fork and block numbers of the page.
7223 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
7224 identifying the relation.
7225 arg5 is the ID of the backend which created the temporary relation for a
7226 local buffer, or <symbol>INVALID_PROC_NUMBER</symbol> (-1) for a shared buffer.</entry>
7227 </row>
7228 <row>
7229 <entry><literal>smgr-md-read-done</literal></entry>
7230 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, int, int)</literal></entry>
7231 <entry>Probe that fires when a block read is complete.
7232 arg0 and arg1 contain the fork and block numbers of the page.
7233 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
7234 identifying the relation.
7235 arg5 is the ID of the backend which created the temporary relation for a
7236 local buffer, or <symbol>INVALID_PROC_NUMBER</symbol> (-1) for a shared buffer.
7237 arg6 is the number of bytes actually read, while arg7 is the number
7238 requested (if these are different it indicates a short read).</entry>
7239 </row>
7240 <row>
7241 <entry><literal>smgr-md-write-start</literal></entry>
7242 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int)</literal></entry>
7243 <entry>Probe that fires when beginning to write a block to a relation.
7244 arg0 and arg1 contain the fork and block numbers of the page.
7245 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
7246 identifying the relation.
7247 arg5 is the ID of the backend which created the temporary relation for a
7248 local buffer, or <symbol>INVALID_PROC_NUMBER</symbol> (-1) for a shared buffer.</entry>
7249 </row>
7250 <row>
7251 <entry><literal>smgr-md-write-done</literal></entry>
7252 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, int, int)</literal></entry>
7253 <entry>Probe that fires when a block write is complete.
7254 arg0 and arg1 contain the fork and block numbers of the page.
7255 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
7256 identifying the relation.
7257 arg5 is the ID of the backend which created the temporary relation for a
7258 local buffer, or <symbol>INVALID_PROC_NUMBER</symbol> (-1) for a shared buffer.
7259 arg6 is the number of bytes actually written, while arg7 is the number
7260 requested (if these are different it indicates a short write).</entry>
7261 </row>
7262 <row>
7263 <entry><literal>sort-start</literal></entry>
7264 <entry><literal>(int, bool, int, int, bool, int)</literal></entry>
7265 <entry>Probe that fires when a sort operation is started.
7266 arg0 indicates heap, index or datum sort.
7267 arg1 is true for unique-value enforcement.
7268 arg2 is the number of key columns.
7269 arg3 is the number of kilobytes of work memory allowed.
7270 arg4 is true if random access to the sort result is required.
7271 arg5 indicates serial when <literal>0</literal>, parallel worker when
7272 <literal>1</literal>, or parallel leader when <literal>2</literal>.</entry>
7273 </row>
7274 <row>
7275 <entry><literal>sort-done</literal></entry>
7276 <entry><literal>(bool, long)</literal></entry>
7277 <entry>Probe that fires when a sort is complete.
7278 arg0 is true for external sort, false for internal sort.
7279 arg1 is the number of disk blocks used for an external sort,
7280 or kilobytes of memory used for an internal sort.</entry>
7281 </row>
7282 <row>
7283 <entry><literal>lwlock-acquire</literal></entry>
7284 <entry><literal>(char *, LWLockMode)</literal></entry>
7285 <entry>Probe that fires when an LWLock has been acquired.
7286 arg0 is the LWLock's tranche.
7287 arg1 is the requested lock mode, either exclusive or shared.</entry>
7288 </row>
7289 <row>
7290 <entry><literal>lwlock-release</literal></entry>
7291 <entry><literal>(char *)</literal></entry>
7292 <entry>Probe that fires when an LWLock has been released (but note
7293 that any released waiters have not yet been awakened).
7294 arg0 is the LWLock's tranche.</entry>
7295 </row>
7296 <row>
7297 <entry><literal>lwlock-wait-start</literal></entry>
7298 <entry><literal>(char *, LWLockMode)</literal></entry>
7299 <entry>Probe that fires when an LWLock was not immediately available and
7300 a server process has begun to wait for the lock to become available.
7301 arg0 is the LWLock's tranche.
7302 arg1 is the requested lock mode, either exclusive or shared.</entry>
7303 </row>
7304 <row>
7305 <entry><literal>lwlock-wait-done</literal></entry>
7306 <entry><literal>(char *, LWLockMode)</literal></entry>
7307 <entry>Probe that fires when a server process has been released from its
7308 wait for an LWLock (it does not actually have the lock yet).
7309 arg0 is the LWLock's tranche.
7310 arg1 is the requested lock mode, either exclusive or shared.</entry>
7311 </row>
7312 <row>
7313 <entry><literal>lwlock-condacquire</literal></entry>
7314 <entry><literal>(char *, LWLockMode)</literal></entry>
7315 <entry>Probe that fires when an LWLock was successfully acquired when the
7316 caller specified no waiting.
7317 arg0 is the LWLock's tranche.
7318 arg1 is the requested lock mode, either exclusive or shared.</entry>
7319 </row>
7320 <row>
7321 <entry><literal>lwlock-condacquire-fail</literal></entry>
7322 <entry><literal>(char *, LWLockMode)</literal></entry>
7323 <entry>Probe that fires when an LWLock was not successfully acquired when
7324 the caller specified no waiting.
7325 arg0 is the LWLock's tranche.
7326 arg1 is the requested lock mode, either exclusive or shared.</entry>
7327 </row>
7328 <row>
7329 <entry><literal>lock-wait-start</literal></entry>
7330 <entry><literal>(unsigned int, unsigned int, unsigned int, unsigned int, unsigned int, LOCKMODE)</literal></entry>
7331 <entry>Probe that fires when a request for a heavyweight lock (lmgr lock)
7332 has begun to wait because the lock is not available.
7333 arg0 through arg3 are the tag fields identifying the object being
7334 locked. arg4 indicates the type of object being locked.
7335 arg5 indicates the lock type being requested.</entry>
7336 </row>
7337 <row>
7338 <entry><literal>lock-wait-done</literal></entry>
7339 <entry><literal>(unsigned int, unsigned int, unsigned int, unsigned int, unsigned int, LOCKMODE)</literal></entry>
7340 <entry>Probe that fires when a request for a heavyweight lock (lmgr lock)
7341 has finished waiting (i.e., has acquired the lock).
7342 The arguments are the same as for <literal>lock-wait-start</literal>.</entry>
7343 </row>
7344 <row>
7345 <entry><literal>deadlock-found</literal></entry>
7346 <entry><literal>()</literal></entry>
7347 <entry>Probe that fires when a deadlock is found by the deadlock
7348 detector.</entry>
7349 </row>
7350
7351 </tbody>
7352 </tgroup>
7353 </table>
7354
7355 <table id="typedefs-table">
7356 <title>Defined Types Used in Probe Parameters</title>
7357 <tgroup cols="2">
7358 <thead>
7359 <row>
7360 <entry>Type</entry>
7361 <entry>Definition</entry>
7362 </row>
7363 </thead>
7364
7365 <tbody>
7366
7367 <row>
7368 <entry><type>LocalTransactionId</type></entry>
7369 <entry><type>unsigned int</type></entry>
7370 </row>
7371 <row>
7372 <entry><type>LWLockMode</type></entry>
7373 <entry><type>int</type></entry>
7374 </row>
7375 <row>
7376 <entry><type>LOCKMODE</type></entry>
7377 <entry><type>int</type></entry>
7378 </row>
7379 <row>
7380 <entry><type>BlockNumber</type></entry>
7381 <entry><type>unsigned int</type></entry>
7382 </row>
7383 <row>
7384 <entry><type>Oid</type></entry>
7385 <entry><type>unsigned int</type></entry>
7386 </row>
7387 <row>
7388 <entry><type>ForkNumber</type></entry>
7389 <entry><type>int</type></entry>
7390 </row>
7391 <row>
7392 <entry><type>bool</type></entry>
7393 <entry><type>unsigned char</type></entry>
7394 </row>
7395
7396 </tbody>
7397 </tgroup>
7398 </table>
7399
7400
7401 </sect2>
7402
7403 <sect2 id="using-trace-points">
7404 <title>Using Probes</title>
7405
7406 <para>
7407 The example below shows a DTrace script for analyzing transaction
7408 counts in the system, as an alternative to snapshotting
7409 <structname>pg_stat_database</structname> before and after a performance test:
7410 <programlisting>
7411 #!/usr/sbin/dtrace -qs
7412
7413 postgresql$1:::transaction-start
7414 {
7415 @start["Start"] = count();
7416 self->ts = timestamp;
7417 }
7418
7419 postgresql$1:::transaction-abort
7420 {
7421 @abort["Abort"] = count();
7422 }
7423
7424 postgresql$1:::transaction-commit
7425 /self->ts/
7426 {
7427 @commit["Commit"] = count();
7428 @time["Total time (ns)"] = sum(timestamp - self->ts);
7429 self->ts=0;
7430 }
7431 </programlisting>
7432 When executed, the example D script gives output such as:
7433 <screen>
7434 # ./txn_count.d `pgrep -n postgres` or ./txn_count.d &lt;PID&gt;
7435 ^C
7436
7437 Start 71
7438 Commit 70
7439 Total time (ns) 2312105013
7440 </screen>
7441 </para>
7442
7443 <note>
7444 <para>
7445 SystemTap uses a different notation for trace scripts than DTrace does,
7446 even though the underlying trace points are compatible. One point worth
7447 noting is that at this writing, SystemTap scripts must reference probe
7448 names using double underscores in place of hyphens. This is expected to
7449 be fixed in future SystemTap releases.
7450 </para>
7451 </note>
7452
7453 <para>
7454 You should remember that DTrace scripts need to be carefully written and
7455 debugged, otherwise the trace information collected might
7456 be meaningless. In most cases where problems are found it is the
7457 instrumentation that is at fault, not the underlying system. When
7458 discussing information found using dynamic tracing, be sure to enclose
7459 the script used to allow that too to be checked and discussed.
7460 </para>
7461 </sect2>
7462
7463 <sect2 id="defining-trace-points">
7464 <title>Defining New Probes</title>
7465
7466 <para>
7467 New probes can be defined within the code wherever the developer
7468 desires, though this will require a recompilation. Below are the steps
7469 for inserting new probes:
7470 </para>
7471
7472 <procedure>
7473 <step>
7474 <para>
7475 Decide on probe names and data to be made available through the probes
7476 </para>
7477 </step>
7478
7479 <step>
7480 <para>
7481 Add the probe definitions to <filename>src/backend/utils/probes.d</filename>
7482 </para>
7483 </step>
7484
7485 <step>
7486 <para>
7487 Include <filename>pg_trace.h</filename> if it is not already present in the
7488 module(s) containing the probe points, and insert
7489 <literal>TRACE_POSTGRESQL</literal> probe macros at the desired locations
7490 in the source code
7491 </para>
7492 </step>
7493
7494 <step>
7495 <para>
7496 Recompile and verify that the new probes are available
7497 </para>
7498 </step>
7499 </procedure>
7500
7501 <formalpara>
7502 <title>Example:</title>
7503 <para>
7504 Here is an example of how you would add a probe to trace all new
7505 transactions by transaction ID.
7506 </para>
7507 </formalpara>
7508
7509 <procedure>
7510 <step>
7511 <para>
7512 Decide that the probe will be named <literal>transaction-start</literal> and
7513 requires a parameter of type <type>LocalTransactionId</type>
7514 </para>
7515 </step>
7516
7517 <step>
7518 <para>
7519 Add the probe definition to <filename>src/backend/utils/probes.d</filename>:
7520 <programlisting>
7521 probe transaction__start(LocalTransactionId);
7522 </programlisting>
7523 Note the use of the double underline in the probe name. In a DTrace
7524 script using the probe, the double underline needs to be replaced with a
7525 hyphen, so <literal>transaction-start</literal> is the name to document for
7526 users.
7527 </para>
7528 </step>
7529
7530 <step>
7531 <para>
7532 At compile time, <literal>transaction__start</literal> is converted to a macro
7533 called <literal>TRACE_POSTGRESQL_TRANSACTION_START</literal> (notice the
7534 underscores are single here), which is available by including
7535 <filename>pg_trace.h</filename>. Add the macro call to the appropriate location
7536 in the source code. In this case, it looks like the following:
7537
7538 <programlisting>
7539 TRACE_POSTGRESQL_TRANSACTION_START(vxid.localTransactionId);
7540 </programlisting>
7541 </para>
7542 </step>
7543
7544 <step>
7545 <para>
7546 After recompiling and running the new binary, check that your newly added
7547 probe is available by executing the following DTrace command. You
7548 should see similar output:
7549 <screen>
7550 # dtrace -ln transaction-start
7551 ID PROVIDER MODULE FUNCTION NAME
7552 18705 postgresql49878 postgres StartTransactionCommand transaction-start
7553 18755 postgresql49877 postgres StartTransactionCommand transaction-start
7554 18805 postgresql49876 postgres StartTransactionCommand transaction-start
7555 18855 postgresql49875 postgres StartTransactionCommand transaction-start
7556 18986 postgresql49873 postgres StartTransactionCommand transaction-start
7557 </screen>
7558 </para>
7559 </step>
7560 </procedure>
7561
7562 <para>
7563 There are a few things to be careful about when adding trace macros
7564 to the C code:
7565
7566 <itemizedlist>
7567 <listitem>
7568 <para>
7569 You should take care that the data types specified for a probe's
7570 parameters match the data types of the variables used in the macro.
7571 Otherwise, you will get compilation errors.
7572 </para>
7573 </listitem>
7574
7575
7576 <listitem>
7577 <para>
7578 On most platforms, if <productname>PostgreSQL</productname> is
7579 built with <option>--enable-dtrace</option>, the arguments to a trace
7580 macro will be evaluated whenever control passes through the
7581 macro, <emphasis>even if no tracing is being done</emphasis>. This is
7582 usually not worth worrying about if you are just reporting the
7583 values of a few local variables. But beware of putting expensive
7584 function calls into the arguments. If you need to do that,
7585 consider protecting the macro with a check to see if the trace
7586 is actually enabled:
7587
7588 <programlisting>
7589 if (TRACE_POSTGRESQL_TRANSACTION_START_ENABLED())
7590 TRACE_POSTGRESQL_TRANSACTION_START(some_function(...));
7591 </programlisting>
7592
7593 Each trace macro has a corresponding <literal>ENABLED</literal> macro.
7594 </para>
7595 </listitem>
7596 </itemizedlist>
7597
7598 </para>
7599
7600 </sect2>
7601
7602 </sect1>
7603
7604 <sect1 id="diskusage">
7605 <title>Monitoring Disk Usage</title>
7606
7607 <para>
7608 This section discusses how to monitor the disk usage of a
7609 <productname>PostgreSQL</productname> database system.
7610 </para>
7611
7612 <sect2 id="disk-usage">
7613 <title>Determining Disk Usage</title>
7614
7615 <indexterm zone="disk-usage">
7616 <primary>disk usage</primary>
7617 </indexterm>
7618
7619 <para>
7620 Each table has a primary heap disk file where most of the data is
7621 stored. If the table has any columns with potentially-wide values,
7622 there also might be a <acronym>TOAST</acronym> file associated with the table,
7623 which is used to store values too wide to fit comfortably in the main
7624 table (see <xref linkend="storage-toast"/>). There will be one valid index
7625 on the <acronym>TOAST</acronym> table, if present. There also might be indexes
7626 associated with the base table. Each table and index is stored in a
7627 separate disk file &mdash; possibly more than one file, if the file would
7628 exceed one gigabyte. Naming conventions for these files are described
7629 in <xref linkend="storage-file-layout"/>.
7630 </para>
7631
7632 <para>
7633 You can monitor disk space in three ways:
7634 using the SQL functions listed in <xref linkend="functions-admin-dbsize"/>,
7635 using the <xref linkend="oid2name"/> module, or
7636 using manual inspection of the system catalogs.
7637 The SQL functions are the easiest to use and are generally recommended.
7638 The remainder of this section shows how to do it by inspection of the
7639 system catalogs.
7640 </para>
7641
7642 <para>
7643 Using <application>psql</application> on a recently vacuumed or analyzed
7644 database, you can issue queries to see the disk usage of any table:
7645 <programlisting>
7646 SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'customer';
7647
7648 pg_relation_filepath | relpages
7649 ----------------------+----------
7650 base/16384/16806 | 60
7651 (1 row)
7652 </programlisting>
7653 Each page is typically 8 kilobytes. (Remember, <structfield>relpages</structfield>
7654 is only updated by <command>VACUUM</command>, <command>ANALYZE</command>, and
7655 a few DDL commands such as <command>CREATE INDEX</command>.) The file path name
7656 is of interest if you want to examine the table's disk file directly.
7657 </para>
7658
7659 <para>
7660 To show the space used by <acronym>TOAST</acronym> tables, use a query
7661 like the following:
7662 <programlisting>
7663 SELECT relname, relpages
7664 FROM pg_class,
7665 (SELECT reltoastrelid
7666 FROM pg_class
7667 WHERE relname = 'customer') AS ss
7668 WHERE oid = ss.reltoastrelid OR
7669 oid = (SELECT indexrelid
7670 FROM pg_index
7671 WHERE indrelid = ss.reltoastrelid)
7672 ORDER BY relname;
7673
7674 relname | relpages
7675 ----------------------+----------
7676 pg_toast_16806 | 0
7677 pg_toast_16806_index | 1
7678 </programlisting>
7679 </para>
7680
7681 <para>
7682 You can easily display index sizes, too:
7683 <programlisting>
7684 SELECT c2.relname, c2.relpages
7685 FROM pg_class c, pg_class c2, pg_index i
7686 WHERE c.relname = 'customer' AND
7687 c.oid = i.indrelid AND
7688 c2.oid = i.indexrelid
7689 ORDER BY c2.relname;
7690
7691 relname | relpages
7692 -------------------+----------
7693 customer_id_index | 26
7694 </programlisting>
7695 </para>
7696
7697 <para>
7698 It is easy to find your largest tables and indexes using this
7699 information:
7700 <programlisting>
7701 SELECT relname, relpages
7702 FROM pg_class
7703 ORDER BY relpages DESC;
7704
7705 relname | relpages
7706 ----------------------+----------
7707 bigtable | 3290
7708 customer | 3144
7709 </programlisting>
7710 </para>
7711 </sect2>
7712
7713 <sect2 id="disk-full">
7714 <title>Disk Full Failure</title>
7715
7716 <para>
7717 The most important disk monitoring task of a database administrator
7718 is to make sure the disk doesn't become full. A filled data disk will
7719 not result in data corruption, but it might prevent useful activity
7720 from occurring. If the disk holding the WAL files grows full, database
7721 server panic and consequent shutdown might occur.
7722 </para>
7723
7724 <para>
7725 If you cannot free up additional space on the disk by deleting
7726 other things, you can move some of the database files to other file
7727 systems by making use of tablespaces. See <xref
7728 linkend="manage-ag-tablespaces"/> for more information about that.
7729 </para>
7730
7731 <tip>
7732 <para>
7733 Some file systems perform badly when they are almost full, so do
7734 not wait until the disk is completely full to take action.
7735 </para>
7736 </tip>
7737
7738 <para>
7739 If your system supports per-user disk quotas, then the database
7740 will naturally be subject to whatever quota is placed on the user
7741 the server runs as. Exceeding the quota will have the same bad
7742 effects as running out of disk space entirely.
7743 </para>
7744 </sect2>
7745 </sect1>
7746
7747 </chapter>