1 <!-- doc/src/sgml/monitoring.sgml -->
3 <chapter id=
"monitoring">
4 <title>Monitoring Database Activity
</title>
6 <indexterm zone=
"monitoring">
7 <primary>monitoring
</primary>
8 <secondary>database activity
</secondary>
11 <indexterm zone=
"monitoring">
12 <primary>database activity
</primary>
13 <secondary>monitoring
</secondary>
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.
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
36 <sect1 id=
"monitoring-ps">
37 <title>Standard Unix Tools
</title>
39 <indexterm zone=
"monitoring-ps">
41 <secondary>to monitor activity
</secondary>
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
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
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.)
70 processes is a server process handling one client connection. Each such
71 process sets its command line display in the form
74 postgres:
<replaceable>user
</replaceable> <replaceable>database
</replaceable> <replaceable>host
</replaceable> <replaceable>activity
</replaceable>
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.)
93 If
<xref linkend=
"guc-cluster-name"/> has been configured the
94 cluster name will also be shown in
<command>ps
</command> output:
96 $ psql -c 'SHOW cluster_name'
102 $ ps aux|grep server1
103 postgres
27093 0.0 0.0 30096 2752 ? Ss
11:
34 0:
00 postgres: server1: background writer
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.
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>
130 <sect1 id=
"monitoring-stats">
131 <title>The Cumulative Statistics System
</title>
133 <indexterm zone=
"monitoring-stats">
134 <primary>statistics
</primary>
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.
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.
155 <sect2 id=
"monitoring-stats-setup">
156 <title>Statistics Collection Configuration
</title>
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.)
167 The parameter
<xref linkend=
"guc-track-activities"/> enables monitoring
168 of the current command being executed by any server process.
172 The parameter
<xref linkend=
"guc-track-counts"/> controls whether
173 cumulative statistics are collected about table and index accesses.
177 The parameter
<xref linkend=
"guc-track-functions"/> enables tracking of
178 usage of user-defined functions.
182 The parameter
<xref linkend=
"guc-track-io-timing"/> enables monitoring
183 of block read and write times.
187 The parameter
<xref linkend=
"guc-track-wal-io-timing"/> enables monitoring
192 Normally these parameters are set in
<filename>postgresql.conf
</filename> so
193 that they apply to all server processes, but it is possible to turn
194 them on or off in individual sessions using the
<xref
195 linkend=
"sql-set"/> command. (To prevent
196 ordinary users from hiding their activity from the administrator,
197 only superusers are allowed to change these parameters with
198 <command>SET
</command>.)
202 Cumulative statistics are collected in shared memory. Every
203 <productname>PostgreSQL
</productname> process collects statistics locally,
204 then updates the shared data at appropriate intervals. When a server,
205 including a physical replica, shuts down cleanly, a permanent copy of the
206 statistics data is stored in the
<filename>pg_stat
</filename> subdirectory,
207 so that statistics can be retained across server restarts. In contrast,
208 when starting from an unclean shutdown (e.g., after an immediate shutdown,
209 a server crash, starting from a base backup, and point-in-time recovery),
210 all statistics counters are reset.
215 <sect2 id=
"monitoring-stats-views">
216 <title>Viewing Statistics
</title>
219 Several predefined views, listed in
<xref
220 linkend=
"monitoring-stats-dynamic-views-table"/>, are available to show
221 the current state of the system. There are also several other
222 views, listed in
<xref
223 linkend=
"monitoring-stats-views-table"/>, available to show the accumulated
224 statistics. Alternatively, one can
225 build custom views using the underlying cumulative statistics functions, as
226 discussed in
<xref linkend=
"monitoring-stats-functions"/>.
230 When using the cumulative statistics views and functions to monitor
231 collected data, it is important to realize that the information does not
232 update instantaneously. Each individual server process flushes out
233 accumulated statistics to shared memory just before going idle, but not
234 more frequently than once per
<varname>PGSTAT_MIN_INTERVAL
</varname>
235 milliseconds (
1 second unless altered while building the server); so a
236 query or transaction still in progress does not affect the displayed totals
237 and the displayed information lags behind actual activity. However,
238 current-query information collected by
<varname>track_activities
</varname>
239 is always up-to-date.
243 Another important point is that when a server process is asked to display
244 any of the accumulated statistics, accessed values are cached until the end
245 of its current transaction in the default configuration. So the statistics
246 will show static information as long as you continue the current
247 transaction. Similarly, information about the current queries of all
248 sessions is collected when any such information is first requested within a
249 transaction, and the same information will be displayed throughout the
250 transaction. This is a feature, not a bug, because it allows you to perform
251 several queries on the statistics and correlate the results without
252 worrying that the numbers are changing underneath you.
254 When analyzing statistics interactively, or with expensive queries, the
255 time delta between accesses to individual statistics can lead to
256 significant skew in the cached statistics. To minimize skew,
257 <varname>stats_fetch_consistency
</varname> can be set to
258 <literal>snapshot
</literal>, at the price of increased memory usage for
259 caching not-needed statistics data. Conversely, if it's known that
260 statistics are only accessed once, caching accessed statistics is
261 unnecessary and can be avoided by setting
262 <varname>stats_fetch_consistency
</varname> to
<literal>none
</literal>.
264 You can invoke
<function>pg_stat_clear_snapshot
</function>() to discard the
265 current transaction's statistics snapshot or cached values (if any). The
266 next use of statistical information will (when in snapshot mode) cause a
267 new snapshot to be built or (when in cache mode) accessed statistics to be
272 A transaction can also see its own statistics (not yet flushed out to the
273 shared memory statistics) in the views
274 <structname>pg_stat_xact_all_tables
</structname>,
275 <structname>pg_stat_xact_sys_tables
</structname>,
276 <structname>pg_stat_xact_user_tables
</structname>, and
277 <structname>pg_stat_xact_user_functions
</structname>. These numbers do not act as
278 stated above; instead they update continuously throughout the transaction.
282 Some of the information in the dynamic statistics views shown in
<xref
283 linkend=
"monitoring-stats-dynamic-views-table"/> is security restricted.
284 Ordinary users can only see all the information about their own sessions
285 (sessions belonging to a role that they are a member of). In rows about
286 other sessions, many columns will be null. Note, however, that the
287 existence of a session and its general properties such as its sessions user
288 and database are visible to all users. Superusers and roles with privileges of
289 built-in role
<literal>pg_read_all_stats
</literal> (see also
<xref
290 linkend=
"predefined-roles"/>) can see all the information about all sessions.
293 <table id=
"monitoring-stats-dynamic-views-table">
294 <title>Dynamic Statistics Views
</title>
299 <entry>View Name
</entry>
300 <entry>Description
</entry>
307 <structname>pg_stat_activity
</structname>
308 <indexterm><primary>pg_stat_activity
</primary></indexterm>
311 One row per server process, showing information related to
312 the current activity of that process, such as state and current query.
313 See
<link linkend=
"monitoring-pg-stat-activity-view">
314 <structname>pg_stat_activity
</structname></link> for details.
319 <entry><structname>pg_stat_replication
</structname><indexterm><primary>pg_stat_replication
</primary></indexterm></entry>
320 <entry>One row per WAL sender process, showing statistics about
321 replication to that sender's connected standby server.
322 See
<link linkend=
"monitoring-pg-stat-replication-view">
323 <structname>pg_stat_replication
</structname></link> for details.
328 <entry><structname>pg_stat_wal_receiver
</structname><indexterm><primary>pg_stat_wal_receiver
</primary></indexterm></entry>
329 <entry>Only one row, showing statistics about the WAL receiver from
330 that receiver's connected server.
331 See
<link linkend=
"monitoring-pg-stat-wal-receiver-view">
332 <structname>pg_stat_wal_receiver
</structname></link> for details.
337 <entry><structname>pg_stat_recovery_prefetch
</structname><indexterm><primary>pg_stat_recovery_prefetch
</primary></indexterm></entry>
338 <entry>Only one row, showing statistics about blocks prefetched during recovery.
339 See
<link linkend=
"monitoring-pg-stat-recovery-prefetch">
340 <structname>pg_stat_recovery_prefetch
</structname></link> for details.
345 <entry><structname>pg_stat_subscription
</structname><indexterm><primary>pg_stat_subscription
</primary></indexterm></entry>
346 <entry>At least one row per subscription, showing information about
347 the subscription workers.
348 See
<link linkend=
"monitoring-pg-stat-subscription">
349 <structname>pg_stat_subscription
</structname></link> for details.
354 <entry><structname>pg_stat_ssl
</structname><indexterm><primary>pg_stat_ssl
</primary></indexterm></entry>
355 <entry>One row per connection (regular and replication), showing information about
356 SSL used on this connection.
357 See
<link linkend=
"monitoring-pg-stat-ssl-view">
358 <structname>pg_stat_ssl
</structname></link> for details.
363 <entry><structname>pg_stat_gssapi
</structname><indexterm><primary>pg_stat_gssapi
</primary></indexterm></entry>
364 <entry>One row per connection (regular and replication), showing information about
365 GSSAPI authentication and encryption used on this connection.
366 See
<link linkend=
"monitoring-pg-stat-gssapi-view">
367 <structname>pg_stat_gssapi
</structname></link> for details.
372 <entry><structname>pg_stat_progress_analyze
</structname><indexterm><primary>pg_stat_progress_analyze
</primary></indexterm></entry>
373 <entry>One row for each backend (including autovacuum worker processes) running
374 <command>ANALYZE
</command>, showing current progress.
375 See
<xref linkend=
"analyze-progress-reporting"/>.
380 <entry><structname>pg_stat_progress_create_index
</structname><indexterm><primary>pg_stat_progress_create_index
</primary></indexterm></entry>
381 <entry>One row for each backend running
<command>CREATE INDEX
</command> or
<command>REINDEX
</command>, showing
383 See
<xref linkend=
"create-index-progress-reporting"/>.
388 <entry><structname>pg_stat_progress_vacuum
</structname><indexterm><primary>pg_stat_progress_vacuum
</primary></indexterm></entry>
389 <entry>One row for each backend (including autovacuum worker processes) running
390 <command>VACUUM
</command>, showing current progress.
391 See
<xref linkend=
"vacuum-progress-reporting"/>.
396 <entry><structname>pg_stat_progress_cluster
</structname><indexterm><primary>pg_stat_progress_cluster
</primary></indexterm></entry>
397 <entry>One row for each backend running
398 <command>CLUSTER
</command> or
<command>VACUUM FULL
</command>, showing current progress.
399 See
<xref linkend=
"cluster-progress-reporting"/>.
404 <entry><structname>pg_stat_progress_basebackup
</structname><indexterm><primary>pg_stat_progress_basebackup
</primary></indexterm></entry>
405 <entry>One row for each WAL sender process streaming a base backup,
406 showing current progress.
407 See
<xref linkend=
"basebackup-progress-reporting"/>.
412 <entry><structname>pg_stat_progress_copy
</structname><indexterm><primary>pg_stat_progress_copy
</primary></indexterm></entry>
413 <entry>One row for each backend running
<command>COPY
</command>, showing current progress.
414 See
<xref linkend=
"copy-progress-reporting"/>.
421 <table id=
"monitoring-stats-views-table">
422 <title>Collected Statistics Views
</title>
427 <entry>View Name
</entry>
428 <entry>Description
</entry>
434 <!-- everything related to global objects, alphabetically -->
437 <entry><structname>pg_stat_archiver
</structname><indexterm><primary>pg_stat_archiver
</primary></indexterm></entry>
438 <entry>One row only, showing statistics about the
439 WAL archiver process's activity. See
440 <link linkend=
"monitoring-pg-stat-archiver-view">
441 <structname>pg_stat_archiver
</structname></link> for details.
446 <entry><structname>pg_stat_bgwriter
</structname><indexterm><primary>pg_stat_bgwriter
</primary></indexterm></entry>
447 <entry>One row only, showing statistics about the
448 background writer process's activity. See
449 <link linkend=
"monitoring-pg-stat-bgwriter-view">
450 <structname>pg_stat_bgwriter
</structname></link> for details.
455 <entry><structname>pg_stat_database
</structname><indexterm><primary>pg_stat_database
</primary></indexterm></entry>
456 <entry>One row per database, showing database-wide statistics. See
457 <link linkend=
"monitoring-pg-stat-database-view">
458 <structname>pg_stat_database
</structname></link> for details.
463 <entry><structname>pg_stat_database_conflicts
</structname><indexterm><primary>pg_stat_database_conflicts
</primary></indexterm></entry>
465 One row per database, showing database-wide statistics about
466 query cancels due to conflict with recovery on standby servers.
467 See
<link linkend=
"monitoring-pg-stat-database-conflicts-view">
468 <structname>pg_stat_database_conflicts
</structname></link> for details.
473 <entry><structname>pg_stat_io
</structname><indexterm><primary>pg_stat_io
</primary></indexterm></entry>
475 One row for each combination of backend type, context, and target object
476 containing cluster-wide I/O statistics.
477 See
<link linkend=
"monitoring-pg-stat-io-view">
478 <structname>pg_stat_io
</structname></link> for details.
483 <entry><structname>pg_stat_replication_slots
</structname><indexterm><primary>pg_stat_replication_slots
</primary></indexterm></entry>
484 <entry>One row per replication slot, showing statistics about the
485 replication slot's usage. See
486 <link linkend=
"monitoring-pg-stat-replication-slots-view">
487 <structname>pg_stat_replication_slots
</structname></link> for details.
492 <entry><structname>pg_stat_slru
</structname><indexterm><primary>pg_stat_slru
</primary></indexterm></entry>
493 <entry>One row per SLRU, showing statistics of operations. See
494 <link linkend=
"monitoring-pg-stat-slru-view">
495 <structname>pg_stat_slru
</structname></link> for details.
500 <entry><structname>pg_stat_subscription_stats
</structname><indexterm><primary>pg_stat_subscription_stats
</primary></indexterm></entry>
501 <entry>One row per subscription, showing statistics about errors.
502 See
<link linkend=
"monitoring-pg-stat-subscription-stats">
503 <structname>pg_stat_subscription_stats
</structname></link> for details.
508 <entry><structname>pg_stat_wal
</structname><indexterm><primary>pg_stat_wal
</primary></indexterm></entry>
509 <entry>One row only, showing statistics about WAL activity. See
510 <link linkend=
"monitoring-pg-stat-wal-view">
511 <structname>pg_stat_wal
</structname></link> for details.
515 <!-- all "stat" for schema objects, by "importance" -->
518 <entry><structname>pg_stat_all_tables
</structname><indexterm><primary>pg_stat_all_tables
</primary></indexterm></entry>
520 One row for each table in the current database, showing statistics
521 about accesses to that specific table.
522 See
<link linkend=
"monitoring-pg-stat-all-tables-view">
523 <structname>pg_stat_all_tables
</structname></link> for details.
528 <entry><structname>pg_stat_sys_tables
</structname><indexterm><primary>pg_stat_sys_tables
</primary></indexterm></entry>
529 <entry>Same as
<structname>pg_stat_all_tables
</structname>, except that only
530 system tables are shown.
</entry>
534 <entry><structname>pg_stat_user_tables
</structname><indexterm><primary>pg_stat_user_tables
</primary></indexterm></entry>
535 <entry>Same as
<structname>pg_stat_all_tables
</structname>, except that only user
536 tables are shown.
</entry>
540 <entry><structname>pg_stat_xact_all_tables
</structname><indexterm><primary>pg_stat_xact_all_tables
</primary></indexterm></entry>
541 <entry>Similar to
<structname>pg_stat_all_tables
</structname>, but counts actions
542 taken so far within the current transaction (which are
<emphasis>not
</emphasis>
543 yet included in
<structname>pg_stat_all_tables
</structname> and related views).
544 The columns for numbers of live and dead rows and vacuum and
545 analyze actions are not present in this view.
</entry>
549 <entry><structname>pg_stat_xact_sys_tables
</structname><indexterm><primary>pg_stat_xact_sys_tables
</primary></indexterm></entry>
550 <entry>Same as
<structname>pg_stat_xact_all_tables
</structname>, except that only
551 system tables are shown.
</entry>
555 <entry><structname>pg_stat_xact_user_tables
</structname><indexterm><primary>pg_stat_xact_user_tables
</primary></indexterm></entry>
556 <entry>Same as
<structname>pg_stat_xact_all_tables
</structname>, except that only
557 user tables are shown.
</entry>
561 <entry><structname>pg_stat_all_indexes
</structname><indexterm><primary>pg_stat_all_indexes
</primary></indexterm></entry>
563 One row for each index in the current database, showing statistics
564 about accesses to that specific index.
565 See
<link linkend=
"monitoring-pg-stat-all-indexes-view">
566 <structname>pg_stat_all_indexes
</structname></link> for details.
571 <entry><structname>pg_stat_sys_indexes
</structname><indexterm><primary>pg_stat_sys_indexes
</primary></indexterm></entry>
572 <entry>Same as
<structname>pg_stat_all_indexes
</structname>, except that only
573 indexes on system tables are shown.
</entry>
577 <entry><structname>pg_stat_user_indexes
</structname><indexterm><primary>pg_stat_user_indexes
</primary></indexterm></entry>
578 <entry>Same as
<structname>pg_stat_all_indexes
</structname>, except that only
579 indexes on user tables are shown.
</entry>
583 <entry><structname>pg_stat_user_functions
</structname><indexterm><primary>pg_stat_user_functions
</primary></indexterm></entry>
585 One row for each tracked function, showing statistics
586 about executions of that function. See
587 <link linkend=
"monitoring-pg-stat-user-functions-view">
588 <structname>pg_stat_user_functions
</structname></link> for details.
593 <entry><structname>pg_stat_xact_user_functions
</structname><indexterm><primary>pg_stat_xact_user_functions
</primary></indexterm></entry>
594 <entry>Similar to
<structname>pg_stat_user_functions
</structname>, but counts only
595 calls during the current transaction (which are
<emphasis>not
</emphasis>
596 yet included in
<structname>pg_stat_user_functions
</structname>).
</entry>
599 <!-- all "statio" for schema objects, by "importance" -->
602 <entry><structname>pg_statio_all_tables
</structname><indexterm><primary>pg_statio_all_tables
</primary></indexterm></entry>
604 One row for each table in the current database, showing statistics
605 about I/O on that specific table.
606 See
<link linkend=
"monitoring-pg-statio-all-tables-view">
607 <structname>pg_statio_all_tables
</structname></link> for details.
612 <entry><structname>pg_statio_sys_tables
</structname><indexterm><primary>pg_statio_sys_tables
</primary></indexterm></entry>
613 <entry>Same as
<structname>pg_statio_all_tables
</structname>, except that only
614 system tables are shown.
</entry>
618 <entry><structname>pg_statio_user_tables
</structname><indexterm><primary>pg_statio_user_tables
</primary></indexterm></entry>
619 <entry>Same as
<structname>pg_statio_all_tables
</structname>, except that only
620 user tables are shown.
</entry>
624 <entry><structname>pg_statio_all_indexes
</structname><indexterm><primary>pg_statio_all_indexes
</primary></indexterm></entry>
626 One row for each index in the current database,
627 showing statistics about I/O on that specific index.
628 See
<link linkend=
"monitoring-pg-statio-all-indexes-view">
629 <structname>pg_statio_all_indexes
</structname></link> for details.
634 <entry><structname>pg_statio_sys_indexes
</structname><indexterm><primary>pg_statio_sys_indexes
</primary></indexterm></entry>
635 <entry>Same as
<structname>pg_statio_all_indexes
</structname>, except that only
636 indexes on system tables are shown.
</entry>
640 <entry><structname>pg_statio_user_indexes
</structname><indexterm><primary>pg_statio_user_indexes
</primary></indexterm></entry>
641 <entry>Same as
<structname>pg_statio_all_indexes
</structname>, except that only
642 indexes on user tables are shown.
</entry>
646 <entry><structname>pg_statio_all_sequences
</structname><indexterm><primary>pg_statio_all_sequences
</primary></indexterm></entry>
648 One row for each sequence in the current database,
649 showing statistics about I/O on that specific sequence.
650 See
<link linkend=
"monitoring-pg-statio-all-sequences-view">
651 <structname>pg_statio_all_sequences
</structname></link> for details.
656 <entry><structname>pg_statio_sys_sequences
</structname><indexterm><primary>pg_statio_sys_sequences
</primary></indexterm></entry>
657 <entry>Same as
<structname>pg_statio_all_sequences
</structname>, except that only
658 system sequences are shown. (Presently, no system sequences are defined,
659 so this view is always empty.)
</entry>
663 <entry><structname>pg_statio_user_sequences
</structname><indexterm><primary>pg_statio_user_sequences
</primary></indexterm></entry>
664 <entry>Same as
<structname>pg_statio_all_sequences
</structname>, except that only
665 user sequences are shown.
</entry>
673 The per-index statistics are particularly useful to determine which
674 indexes are being used and how effective they are.
678 The
<structname>pg_stat_io
</structname> and
679 <structname>pg_statio_
</structname> set of views are useful for determining
680 the effectiveness of the buffer cache. They can be used to calculate a cache
681 hit ratio. Note that while
<productname>PostgreSQL
</productname>'s I/O
682 statistics capture most instances in which the kernel was invoked in order
683 to perform I/O, they do not differentiate between data which had to be
684 fetched from disk and that which already resided in the kernel page cache.
685 Users are advised to use the
<productname>PostgreSQL
</productname>
686 statistics views in combination with operating system utilities for a more
687 complete picture of their database's I/O performance.
692 <sect2 id=
"monitoring-pg-stat-activity-view">
693 <title><structname>pg_stat_activity
</structname></title>
696 <primary>pg_stat_activity
</primary>
700 The
<structname>pg_stat_activity
</structname> view will have one row
701 per server process, showing information related to
702 the current activity of that process.
705 <table id=
"pg-stat-activity-view" xreflabel=
"pg_stat_activity">
706 <title><structname>pg_stat_activity
</structname> View
</title>
710 <entry role=
"catalog_table_entry"><para role=
"column_definition">
721 <entry role=
"catalog_table_entry"><para role=
"column_definition">
722 <structfield>datid
</structfield> <type>oid
</type>
725 OID of the database this backend is connected to
730 <entry role=
"catalog_table_entry"><para role=
"column_definition">
731 <structfield>datname
</structfield> <type>name
</type>
734 Name of the database this backend is connected to
739 <entry role=
"catalog_table_entry"><para role=
"column_definition">
740 <structfield>pid
</structfield> <type>integer
</type>
743 Process ID of this backend
748 <entry role=
"catalog_table_entry"><para role=
"column_definition">
749 <structfield>leader_pid
</structfield> <type>integer
</type>
752 Process ID of the parallel group leader if this process is a parallel
753 query worker, or process ID of the leader apply worker if this process
754 is a parallel apply worker.
<literal>NULL
</literal> indicates that this
755 process is a parallel group leader or leader apply worker, or does not
756 participate in any parallel operation.
761 <entry role=
"catalog_table_entry"><para role=
"column_definition">
762 <structfield>usesysid
</structfield> <type>oid
</type>
765 OID of the user logged into this backend
770 <entry role=
"catalog_table_entry"><para role=
"column_definition">
771 <structfield>usename
</structfield> <type>name
</type>
774 Name of the user logged into this backend
779 <entry role=
"catalog_table_entry"><para role=
"column_definition">
780 <structfield>application_name
</structfield> <type>text
</type>
783 Name of the application that is connected
789 <entry role=
"catalog_table_entry"><para role=
"column_definition">
790 <structfield>client_addr
</structfield> <type>inet
</type>
793 IP address of the client connected to this backend.
794 If this field is null, it indicates either that the client is
795 connected via a Unix socket on the server machine or that this is an
796 internal process such as autovacuum.
801 <entry role=
"catalog_table_entry"><para role=
"column_definition">
802 <structfield>client_hostname
</structfield> <type>text
</type>
805 Host name of the connected client, as reported by a
806 reverse DNS lookup of
<structfield>client_addr
</structfield>. This field will
807 only be non-null for IP connections, and only when
<xref linkend=
"guc-log-hostname"/> is enabled.
812 <entry role=
"catalog_table_entry"><para role=
"column_definition">
813 <structfield>client_port
</structfield> <type>integer
</type>
816 TCP port number that the client is using for communication
817 with this backend, or
<literal>-
1</literal> if a Unix socket is used.
818 If this field is null, it indicates that this is an internal server process.
823 <entry role=
"catalog_table_entry"><para role=
"column_definition">
824 <structfield>backend_start
</structfield> <type>timestamp with time zone
</type>
827 Time when this process was started. For client backends,
828 this is the time the client connected to the server.
833 <entry role=
"catalog_table_entry"><para role=
"column_definition">
834 <structfield>xact_start
</structfield> <type>timestamp with time zone
</type>
837 Time when this process' current transaction was started, or null
838 if no transaction is active. If the current
839 query is the first of its transaction, this column is equal to the
840 <structfield>query_start
</structfield> column.
845 <entry role=
"catalog_table_entry"><para role=
"column_definition">
846 <structfield>query_start
</structfield> <type>timestamp with time zone
</type>
849 Time when the currently active query was started, or if
850 <structfield>state
</structfield> is not
<literal>active
</literal>, when the last query
856 <entry role=
"catalog_table_entry"><para role=
"column_definition">
857 <structfield>state_change
</structfield> <type>timestamp with time zone
</type>
860 Time when the
<structfield>state
</structfield> was last changed
865 <entry role=
"catalog_table_entry"><para role=
"column_definition">
866 <structfield>wait_event_type
</structfield> <type>text
</type>
869 The type of event for which the backend is waiting, if any;
870 otherwise NULL. See
<xref linkend=
"wait-event-table"/>.
875 <entry role=
"catalog_table_entry"><para role=
"column_definition">
876 <structfield>wait_event
</structfield> <type>text
</type>
879 Wait event name if backend is currently waiting, otherwise NULL.
880 See
<xref linkend=
"wait-event-activity-table"/> through
881 <xref linkend=
"wait-event-timeout-table"/>.
886 <entry role=
"catalog_table_entry"><para role=
"column_definition">
887 <structfield>state
</structfield> <type>text
</type>
890 Current overall state of this backend.
895 <literal>active
</literal>: The backend is executing a query.
900 <literal>idle
</literal>: The backend is waiting for a new client command.
905 <literal>idle in transaction
</literal>: The backend is in a transaction,
906 but is not currently executing a query.
911 <literal>idle in transaction (aborted)
</literal>: This state is similar to
912 <literal>idle in transaction
</literal>, except one of the statements in
913 the transaction caused an error.
918 <literal>fastpath function call
</literal>: The backend is executing a
924 <literal>disabled
</literal>: This state is reported if
<xref linkend=
"guc-track-activities"/> is disabled in this backend.
932 <entry role=
"catalog_table_entry"><para role=
"column_definition">
933 <structfield>backend_xid
</structfield> <type>xid
</type>
936 Top-level transaction identifier of this backend, if any; see
937 <xref linkend=
"transaction-id"/>.
942 <entry role=
"catalog_table_entry"><para role=
"column_definition">
943 <structfield>backend_xmin
</structfield> <type>xid
</type>
946 The current backend's
<literal>xmin
</literal> horizon.
951 <entry role=
"catalog_table_entry"><para role=
"column_definition">
952 <structfield>query_id
</structfield> <type>bigint
</type>
955 Identifier of this backend's most recent query. If
956 <structfield>state
</structfield> is
<literal>active
</literal> this
957 field shows the identifier of the currently executing query. In
958 all other states, it shows the identifier of last query that was
959 executed. Query identifiers are not computed by default so this
960 field will be null unless
<xref linkend=
"guc-compute-query-id"/>
961 parameter is enabled or a third-party module that computes query
962 identifiers is configured.
967 <entry role=
"catalog_table_entry"><para role=
"column_definition">
968 <structfield>query
</structfield> <type>text
</type>
971 Text of this backend's most recent query. If
972 <structfield>state
</structfield> is
<literal>active
</literal> this field shows the
973 currently executing query. In all other states, it shows the last query
974 that was executed. By default the query text is truncated at
1024
975 bytes; this value can be changed via the parameter
976 <xref linkend=
"guc-track-activity-query-size"/>.
981 <entry role=
"catalog_table_entry"><para role=
"column_definition">
982 <structfield>backend_type
</structfield> <type>text
</type>
985 Type of current backend. Possible types are
986 <literal>autovacuum launcher
</literal>,
<literal>autovacuum worker
</literal>,
987 <literal>logical replication launcher
</literal>,
988 <literal>logical replication worker
</literal>,
989 <literal>parallel worker
</literal>,
<literal>background writer
</literal>,
990 <literal>client backend
</literal>,
<literal>checkpointer
</literal>,
991 <literal>archiver
</literal>,
<literal>standalone backend
</literal>,
992 <literal>startup
</literal>,
<literal>walreceiver
</literal>,
993 <literal>walsender
</literal> and
<literal>walwriter
</literal>.
994 In addition, background workers registered by extensions may have
1004 The
<structfield>wait_event
</structfield> and
<structfield>state
</structfield> columns are
1005 independent. If a backend is in the
<literal>active
</literal> state,
1006 it may or may not be
<literal>waiting
</literal> on some event. If the state
1007 is
<literal>active
</literal> and
<structfield>wait_event
</structfield> is non-null, it
1008 means that a query is being executed, but is being blocked somewhere
1013 <table id=
"wait-event-table">
1014 <title>Wait Event Types
</title>
1018 <entry>Wait Event Type
</entry>
1019 <entry>Description
</entry>
1025 <entry><literal>Activity
</literal></entry>
1026 <entry>The server process is idle. This event type indicates a process
1027 waiting for activity in its main processing loop.
1028 <literal>wait_event
</literal> will identify the specific wait point;
1029 see
<xref linkend=
"wait-event-activity-table"/>.
1033 <entry><literal>BufferPin
</literal></entry>
1034 <entry>The server process is waiting for exclusive access to
1035 a data buffer. Buffer pin waits can be protracted if
1036 another process holds an open cursor that last read data from the
1037 buffer in question. See
<xref linkend=
"wait-event-bufferpin-table"/>.
1041 <entry><literal>Client
</literal></entry>
1042 <entry>The server process is waiting for activity on a socket
1043 connected to a user application. Thus, the server expects something
1044 to happen that is independent of its internal processes.
1045 <literal>wait_event
</literal> will identify the specific wait point;
1046 see
<xref linkend=
"wait-event-client-table"/>.
1050 <entry><literal>Extension
</literal></entry>
1051 <entry>The server process is waiting for some condition defined by an
1053 See
<xref linkend=
"wait-event-extension-table"/>.
1057 <entry><literal>IO
</literal></entry>
1058 <entry>The server process is waiting for an I/O operation to complete.
1059 <literal>wait_event
</literal> will identify the specific wait point;
1060 see
<xref linkend=
"wait-event-io-table"/>.
1064 <entry><literal>IPC
</literal></entry>
1065 <entry>The server process is waiting for some interaction with
1066 another server process.
<literal>wait_event
</literal> will
1067 identify the specific wait point;
1068 see
<xref linkend=
"wait-event-ipc-table"/>.
1072 <entry><literal>Lock
</literal></entry>
1073 <entry>The server process is waiting for a heavyweight lock.
1074 Heavyweight locks, also known as lock manager locks or simply locks,
1075 primarily protect SQL-visible objects such as tables. However,
1076 they are also used to ensure mutual exclusion for certain internal
1077 operations such as relation extension.
<literal>wait_event
</literal>
1078 will identify the type of lock awaited;
1079 see
<xref linkend=
"wait-event-lock-table"/>.
1083 <entry><literal>LWLock
</literal></entry>
1084 <entry> The server process is waiting for a lightweight lock.
1085 Most such locks protect a particular data structure in shared memory.
1086 <literal>wait_event
</literal> will contain a name identifying the purpose
1087 of the lightweight lock. (Some locks have specific names; others
1088 are part of a group of locks each with a similar purpose.)
1089 See
<xref linkend=
"wait-event-lwlock-table"/>.
1093 <entry><literal>Timeout
</literal></entry>
1094 <entry>The server process is waiting for a timeout
1095 to expire.
<literal>wait_event
</literal> will identify the specific wait
1096 point; see
<xref linkend=
"wait-event-timeout-table"/>.
1106 Here is an example of how wait events can be viewed:
1109 SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;
1110 pid | wait_event_type | wait_event
1111 ------+-----------------+------------
1112 2540 | Lock | relation
1113 6644 | LWLock | ProcArray
1120 Extensions can add
<literal>LWLock
</literal> types to the list shown in
1121 <xref linkend=
"wait-event-lwlock-table"/>. In some cases, the name
1122 assigned by an extension will not be available in all server processes;
1123 so an
<literal>LWLock
</literal> wait event might be reported as
1124 just
<quote><literal>extension
</literal></quote> rather than the
1125 extension-assigned name.
1130 <sect2 id=
"monitoring-pg-stat-replication-view">
1131 <title><structname>pg_stat_replication
</structname></title>
1134 <primary>pg_stat_replication
</primary>
1138 The
<structname>pg_stat_replication
</structname> view will contain one row
1139 per WAL sender process, showing statistics about replication to that
1140 sender's connected standby server. Only directly connected standbys are
1141 listed; no information is available about downstream standby servers.
1144 <table id=
"pg-stat-replication-view" xreflabel=
"pg_stat_replication">
1145 <title><structname>pg_stat_replication
</structname> View
</title>
1149 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1160 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1161 <structfield>pid
</structfield> <type>integer
</type>
1164 Process ID of a WAL sender process
1169 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1170 <structfield>usesysid
</structfield> <type>oid
</type>
1173 OID of the user logged into this WAL sender process
1178 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1179 <structfield>usename
</structfield> <type>name
</type>
1182 Name of the user logged into this WAL sender process
1187 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1188 <structfield>application_name
</structfield> <type>text
</type>
1191 Name of the application that is connected
1197 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1198 <structfield>client_addr
</structfield> <type>inet
</type>
1201 IP address of the client connected to this WAL sender.
1202 If this field is null, it indicates that the client is
1203 connected via a Unix socket on the server machine.
1208 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1209 <structfield>client_hostname
</structfield> <type>text
</type>
1212 Host name of the connected client, as reported by a
1213 reverse DNS lookup of
<structfield>client_addr
</structfield>. This field will
1214 only be non-null for IP connections, and only when
<xref linkend=
"guc-log-hostname"/> is enabled.
1219 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1220 <structfield>client_port
</structfield> <type>integer
</type>
1223 TCP port number that the client is using for communication
1224 with this WAL sender, or
<literal>-
1</literal> if a Unix socket is used
1229 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1230 <structfield>backend_start
</structfield> <type>timestamp with time zone
</type>
1233 Time when this process was started, i.e., when the
1234 client connected to this WAL sender
1239 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1240 <structfield>backend_xmin
</structfield> <type>xid
</type>
1243 This standby's
<literal>xmin
</literal> horizon reported
1244 by
<xref linkend=
"guc-hot-standby-feedback"/>.
1249 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1250 <structfield>state
</structfield> <type>text
</type>
1253 Current WAL sender state.
1254 Possible values are:
1258 <literal>startup
</literal>: This WAL sender is starting up.
1263 <literal>catchup
</literal>: This WAL sender's connected standby is
1264 catching up with the primary.
1269 <literal>streaming
</literal>: This WAL sender is streaming changes
1270 after its connected standby server has caught up with the primary.
1275 <literal>backup
</literal>: This WAL sender is sending a backup.
1280 <literal>stopping
</literal>: This WAL sender is stopping.
1288 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1289 <structfield>sent_lsn
</structfield> <type>pg_lsn
</type>
1292 Last write-ahead log location sent on this connection
1297 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1298 <structfield>write_lsn
</structfield> <type>pg_lsn
</type>
1301 Last write-ahead log location written to disk by this standby
1307 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1308 <structfield>flush_lsn
</structfield> <type>pg_lsn
</type>
1311 Last write-ahead log location flushed to disk by this standby
1317 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1318 <structfield>replay_lsn
</structfield> <type>pg_lsn
</type>
1321 Last write-ahead log location replayed into the database on this
1327 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1328 <structfield>write_lag
</structfield> <type>interval
</type>
1331 Time elapsed between flushing recent WAL locally and receiving
1332 notification that this standby server has written it (but not yet
1333 flushed it or applied it). This can be used to gauge the delay that
1334 <literal>synchronous_commit
</literal> level
1335 <literal>remote_write
</literal> incurred while committing if this
1336 server was configured as a synchronous standby.
1341 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1342 <structfield>flush_lag
</structfield> <type>interval
</type>
1345 Time elapsed between flushing recent WAL locally and receiving
1346 notification that this standby server has written and flushed it
1347 (but not yet applied it). This can be used to gauge the delay that
1348 <literal>synchronous_commit
</literal> level
1349 <literal>on
</literal> incurred while committing if this
1350 server was configured as a synchronous standby.
1355 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1356 <structfield>replay_lag
</structfield> <type>interval
</type>
1359 Time elapsed between flushing recent WAL locally and receiving
1360 notification that this standby server has written, flushed and
1361 applied it. This can be used to gauge the delay that
1362 <literal>synchronous_commit
</literal> level
1363 <literal>remote_apply
</literal> incurred while committing if this
1364 server was configured as a synchronous standby.
1369 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1370 <structfield>sync_priority
</structfield> <type>integer
</type>
1373 Priority of this standby server for being chosen as the
1374 synchronous standby in a priority-based synchronous replication.
1375 This has no effect in a quorum-based synchronous replication.
1380 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1381 <structfield>sync_state
</structfield> <type>text
</type>
1384 Synchronous state of this standby server.
1385 Possible values are:
1389 <literal>async
</literal>: This standby server is asynchronous.
1394 <literal>potential
</literal>: This standby server is now asynchronous,
1395 but can potentially become synchronous if one of current
1396 synchronous ones fails.
1401 <literal>sync
</literal>: This standby server is synchronous.
1406 <literal>quorum
</literal>: This standby server is considered as a candidate
1407 for quorum standbys.
1415 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1416 <structfield>reply_time
</structfield> <type>timestamp with time zone
</type>
1419 Send time of last reply message received from standby server
1427 The lag times reported in the
<structname>pg_stat_replication
</structname>
1428 view are measurements of the time taken for recent WAL to be written,
1429 flushed and replayed and for the sender to know about it. These times
1430 represent the commit delay that was (or would have been) introduced by each
1431 synchronous commit level, if the remote server was configured as a
1432 synchronous standby. For an asynchronous standby, the
1433 <structfield>replay_lag
</structfield> column approximates the delay
1434 before recent transactions became visible to queries. If the standby
1435 server has entirely caught up with the sending server and there is no more
1436 WAL activity, the most recently measured lag times will continue to be
1437 displayed for a short time and then show NULL.
1441 Lag times work automatically for physical replication. Logical decoding
1442 plugins may optionally emit tracking messages; if they do not, the tracking
1443 mechanism will simply display NULL lag.
1448 The reported lag times are not predictions of how long it will take for
1449 the standby to catch up with the sending server assuming the current
1450 rate of replay. Such a system would show similar times while new WAL is
1451 being generated, but would differ when the sender becomes idle. In
1452 particular, when the standby has caught up completely,
1453 <structname>pg_stat_replication
</structname> shows the time taken to
1454 write, flush and replay the most recent reported WAL location rather than
1455 zero as some users might expect. This is consistent with the goal of
1456 measuring synchronous commit and transaction visibility delays for
1457 recent write transactions.
1458 To reduce confusion for users expecting a different model of lag, the
1459 lag columns revert to NULL after a short time on a fully replayed idle
1460 system. Monitoring systems should choose whether to represent this
1461 as missing data, zero or continue to display the last known value.
1467 <sect2 id=
"monitoring-pg-stat-replication-slots-view">
1468 <title><structname>pg_stat_replication_slots
</structname></title>
1471 <primary>pg_stat_replication_slots
</primary>
1475 The
<structname>pg_stat_replication_slots
</structname> view will contain
1476 one row per logical replication slot, showing statistics about its usage.
1479 <table id=
"pg-stat-replication-slots-view" xreflabel=
"pg_stat_replication_slots">
1480 <title><structname>pg_stat_replication_slots
</structname> View
</title>
1484 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1495 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1496 <structfield>slot_name
</structfield> <type>text
</type>
1499 A unique, cluster-wide identifier for the replication slot
1504 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1505 <structfield>spill_txns
</structfield> <type>bigint
</type>
1508 Number of transactions spilled to disk once the memory used by
1509 logical decoding to decode changes from WAL has exceeded
1510 <literal>logical_decoding_work_mem
</literal>. The counter gets
1511 incremented for both top-level transactions and subtransactions.
1516 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1517 <structfield>spill_count
</structfield> <type>bigint
</type>
1520 Number of times transactions were spilled to disk while decoding
1521 changes from WAL for this slot. This counter is incremented each time
1522 a transaction is spilled, and the same transaction may be spilled
1528 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1529 <structfield>spill_bytes
</structfield> <type>bigint
</type>
1532 Amount of decoded transaction data spilled to disk while performing
1533 decoding of changes from WAL for this slot. This and other spill
1534 counters can be used to gauge the I/O which occurred during logical
1535 decoding and allow tuning
<literal>logical_decoding_work_mem
</literal>.
1540 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1541 <structfield>stream_txns
</structfield> <type>bigint
</type>
1544 Number of in-progress transactions streamed to the decoding output
1545 plugin after the memory used by logical decoding to decode changes
1546 from WAL for this slot has exceeded
1547 <literal>logical_decoding_work_mem
</literal>. Streaming only
1548 works with top-level transactions (subtransactions can't be streamed
1549 independently), so the counter is not incremented for subtransactions.
1554 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1555 <structfield>stream_count
</structfield><type>bigint
</type>
1558 Number of times in-progress transactions were streamed to the decoding
1559 output plugin while decoding changes from WAL for this slot. This
1560 counter is incremented each time a transaction is streamed, and the
1561 same transaction may be streamed multiple times.
1566 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1567 <structfield>stream_bytes
</structfield><type>bigint
</type>
1570 Amount of transaction data decoded for streaming in-progress
1571 transactions to the decoding output plugin while decoding changes from
1572 WAL for this slot. This and other streaming counters for this slot can
1573 be used to tune
<literal>logical_decoding_work_mem
</literal>.
1579 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1580 <structfield>total_txns
</structfield> <type>bigint
</type>
1583 Number of decoded transactions sent to the decoding output plugin for
1584 this slot. This counts top-level transactions only, and is not incremented
1585 for subtransactions. Note that this includes the transactions that are
1586 streamed and/or spilled.
1591 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1592 <structfield>total_bytes
</structfield><type>bigint
</type>
1595 Amount of transaction data decoded for sending transactions to the
1596 decoding output plugin while decoding changes from WAL for this slot.
1597 Note that this includes data that is streamed and/or spilled.
1603 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1604 <structfield>stats_reset
</structfield> <type>timestamp with time zone
</type>
1607 Time at which these statistics were last reset
1616 <sect2 id=
"monitoring-pg-stat-wal-receiver-view">
1617 <title><structname>pg_stat_wal_receiver
</structname></title>
1620 <primary>pg_stat_wal_receiver
</primary>
1624 The
<structname>pg_stat_wal_receiver
</structname> view will contain only
1625 one row, showing statistics about the WAL receiver from that receiver's
1629 <table id=
"pg-stat-wal-receiver-view" xreflabel=
"pg_stat_wal_receiver">
1630 <title><structname>pg_stat_wal_receiver
</structname> View
</title>
1634 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1645 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1646 <structfield>pid
</structfield> <type>integer
</type>
1649 Process ID of the WAL receiver process
1654 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1655 <structfield>status
</structfield> <type>text
</type>
1658 Activity status of the WAL receiver process
1663 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1664 <structfield>receive_start_lsn
</structfield> <type>pg_lsn
</type>
1667 First write-ahead log location used when WAL receiver is
1673 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1674 <structfield>receive_start_tli
</structfield> <type>integer
</type>
1677 First timeline number used when WAL receiver is started
1682 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1683 <structfield>written_lsn
</structfield> <type>pg_lsn
</type>
1686 Last write-ahead log location already received and written to disk,
1687 but not flushed. This should not be used for data integrity checks.
1692 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1693 <structfield>flushed_lsn
</structfield> <type>pg_lsn
</type>
1696 Last write-ahead log location already received and flushed to
1697 disk, the initial value of this field being the first log location used
1698 when WAL receiver is started
1703 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1704 <structfield>received_tli
</structfield> <type>integer
</type>
1707 Timeline number of last write-ahead log location received and
1708 flushed to disk, the initial value of this field being the timeline
1709 number of the first log location used when WAL receiver is started
1714 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1715 <structfield>last_msg_send_time
</structfield> <type>timestamp with time zone
</type>
1718 Send time of last message received from origin WAL sender
1723 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1724 <structfield>last_msg_receipt_time
</structfield> <type>timestamp with time zone
</type>
1727 Receipt time of last message received from origin WAL sender
1732 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1733 <structfield>latest_end_lsn
</structfield> <type>pg_lsn
</type>
1736 Last write-ahead log location reported to origin WAL sender
1741 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1742 <structfield>latest_end_time
</structfield> <type>timestamp with time zone
</type>
1745 Time of last write-ahead log location reported to origin WAL sender
1750 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1751 <structfield>slot_name
</structfield> <type>text
</type>
1754 Replication slot name used by this WAL receiver
1759 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1760 <structfield>sender_host
</structfield> <type>text
</type>
1763 Host of the
<productname>PostgreSQL
</productname> instance
1764 this WAL receiver is connected to. This can be a host name,
1765 an IP address, or a directory path if the connection is via
1766 Unix socket. (The path case can be distinguished because it
1767 will always be an absolute path, beginning with
<literal>/
</literal>.)
1772 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1773 <structfield>sender_port
</structfield> <type>integer
</type>
1776 Port number of the
<productname>PostgreSQL
</productname> instance
1777 this WAL receiver is connected to.
1782 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1783 <structfield>conninfo
</structfield> <type>text
</type>
1786 Connection string used by this WAL receiver,
1787 with security-sensitive fields obfuscated.
1796 <sect2 id=
"monitoring-pg-stat-recovery-prefetch">
1797 <title><structname>pg_stat_recovery_prefetch
</structname></title>
1800 <primary>pg_stat_recovery_prefetch
</primary>
1804 The
<structname>pg_stat_recovery_prefetch
</structname> view will contain
1805 only one row. The columns
<structfield>wal_distance
</structfield>,
1806 <structfield>block_distance
</structfield> and
1807 <structfield>io_depth
</structfield> show current values, and the
1808 other columns show cumulative counters that can be reset
1809 with the
<function>pg_stat_reset_shared
</function> function.
1812 <table id=
"pg-stat-recovery-prefetch-view" xreflabel=
"pg_stat_recovery_prefetch">
1813 <title><structname>pg_stat_recovery_prefetch
</structname> View
</title>
1817 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1828 <entry role=
"catalog_table_entry">
1829 <para role=
"column_definition">
1830 <structfield>stats_reset
</structfield> <type>timestamp with time zone
</type>
1833 Time at which these statistics were last reset
1839 <entry role=
"catalog_table_entry">
1840 <para role=
"column_definition">
1841 <structfield>prefetch
</structfield> <type>bigint
</type>
1844 Number of blocks prefetched because they were not in the buffer pool
1850 <entry role=
"catalog_table_entry">
1851 <para role=
"column_definition">
1852 <structfield>hit
</structfield> <type>bigint
</type>
1855 Number of blocks not prefetched because they were already in the buffer pool
1861 <entry role=
"catalog_table_entry">
1862 <para role=
"column_definition">
1863 <structfield>skip_init
</structfield> <type>bigint
</type>
1866 Number of blocks not prefetched because they would be zero-initialized
1872 <entry role=
"catalog_table_entry">
1873 <para role=
"column_definition">
1874 <structfield>skip_new
</structfield> <type>bigint
</type>
1877 Number of blocks not prefetched because they didn't exist yet
1883 <entry role=
"catalog_table_entry">
1884 <para role=
"column_definition">
1885 <structfield>skip_fpw
</structfield> <type>bigint
</type>
1888 Number of blocks not prefetched because a full page image was included in the WAL
1894 <entry role=
"catalog_table_entry">
1895 <para role=
"column_definition">
1896 <structfield>skip_rep
</structfield> <type>bigint
</type>
1899 Number of blocks not prefetched because they were already recently prefetched
1905 <entry role=
"catalog_table_entry">
1906 <para role=
"column_definition">
1907 <structfield>wal_distance
</structfield> <type>int
</type>
1910 How many bytes ahead the prefetcher is looking
1916 <entry role=
"catalog_table_entry">
1917 <para role=
"column_definition">
1918 <structfield>block_distance
</structfield> <type>int
</type>
1921 How many blocks ahead the prefetcher is looking
1927 <entry role=
"catalog_table_entry">
1928 <para role=
"column_definition">
1929 <structfield>io_depth
</structfield> <type>int
</type>
1932 How many prefetches have been initiated but are not yet known to have completed
1942 <sect2 id=
"monitoring-pg-stat-subscription">
1943 <title><structname>pg_stat_subscription
</structname></title>
1946 <primary>pg_stat_subscription
</primary>
1949 <table id=
"pg-stat-subscription" xreflabel=
"pg_stat_subscription">
1950 <title><structname>pg_stat_subscription
</structname> View
</title>
1954 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1965 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1966 <structfield>subid
</structfield> <type>oid
</type>
1969 OID of the subscription
1974 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1975 <structfield>subname
</structfield> <type>name
</type>
1978 Name of the subscription
1983 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1984 <structfield>pid
</structfield> <type>integer
</type>
1987 Process ID of the subscription worker process
1992 <entry role=
"catalog_table_entry"><para role=
"column_definition">
1993 <structfield>leader_pid
</structfield> <type>integer
</type>
1996 Process ID of the leader apply worker if this process is a parallel
1997 apply worker; NULL if this process is a leader apply worker or a
1998 synchronization worker
2003 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2004 <structfield>relid
</structfield> <type>oid
</type>
2007 OID of the relation that the worker is synchronizing; NULL for the
2008 leader apply worker and parallel apply workers
2013 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2014 <structfield>received_lsn
</structfield> <type>pg_lsn
</type>
2017 Last write-ahead log location received, the initial value of
2018 this field being
0; NULL for parallel apply workers
2023 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2024 <structfield>last_msg_send_time
</structfield> <type>timestamp with time zone
</type>
2027 Send time of last message received from origin WAL sender; NULL for
2028 parallel apply workers
2033 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2034 <structfield>last_msg_receipt_time
</structfield> <type>timestamp with time zone
</type>
2037 Receipt time of last message received from origin WAL sender; NULL for
2038 parallel apply workers
2043 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2044 <structfield>latest_end_lsn
</structfield> <type>pg_lsn
</type>
2047 Last write-ahead log location reported to origin WAL sender; NULL for
2048 parallel apply workers
2053 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2054 <structfield>latest_end_time
</structfield> <type>timestamp with time zone
</type>
2057 Time of last write-ahead log location reported to origin WAL
2058 sender; NULL for parallel apply workers
2067 <sect2 id=
"monitoring-pg-stat-subscription-stats">
2068 <title><structname>pg_stat_subscription_stats
</structname></title>
2071 <primary>pg_stat_subscription_stats
</primary>
2075 The
<structname>pg_stat_subscription_stats
</structname> view will contain
2076 one row per subscription.
2079 <table id=
"pg-stat-subscription-stats" xreflabel=
"pg_stat_subscription_stats">
2080 <title><structname>pg_stat_subscription_stats
</structname> View
</title>
2084 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2095 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2096 <structfield>subid
</structfield> <type>oid
</type>
2099 OID of the subscription
2104 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2105 <structfield>subname
</structfield> <type>name
</type>
2108 Name of the subscription
2113 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2114 <structfield>apply_error_count
</structfield> <type>bigint
</type>
2117 Number of times an error occurred while applying changes
2122 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2123 <structfield>sync_error_count
</structfield> <type>bigint
</type>
2126 Number of times an error occurred during the initial table
2132 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2133 <structfield>stats_reset
</structfield> <type>timestamp with time zone
</type>
2136 Time at which these statistics were last reset
2145 <sect2 id=
"monitoring-pg-stat-ssl-view">
2146 <title><structname>pg_stat_ssl
</structname></title>
2149 <primary>pg_stat_ssl
</primary>
2153 The
<structname>pg_stat_ssl
</structname> view will contain one row per
2154 backend or WAL sender process, showing statistics about SSL usage on
2155 this connection. It can be joined to
<structname>pg_stat_activity
</structname>
2156 or
<structname>pg_stat_replication
</structname> on the
2157 <structfield>pid
</structfield> column to get more details about the
2161 <table id=
"pg-stat-ssl-view" xreflabel=
"pg_stat_ssl">
2162 <title><structname>pg_stat_ssl
</structname> View
</title>
2166 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2177 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2178 <structfield>pid
</structfield> <type>integer
</type>
2181 Process ID of a backend or WAL sender process
2186 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2187 <structfield>ssl
</structfield> <type>boolean
</type>
2190 True if SSL is used on this connection
2195 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2196 <structfield>version
</structfield> <type>text
</type>
2199 Version of SSL in use, or NULL if SSL is not in use
2205 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2206 <structfield>cipher
</structfield> <type>text
</type>
2209 Name of SSL cipher in use, or NULL if SSL is not in use
2215 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2216 <structfield>bits
</structfield> <type>integer
</type>
2219 Number of bits in the encryption algorithm used, or NULL
2220 if SSL is not used on this connection
2225 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2226 <structfield>client_dn
</structfield> <type>text
</type>
2229 Distinguished Name (DN) field from the client certificate
2230 used, or NULL if no client certificate was supplied or if SSL
2231 is not in use on this connection. This field is truncated if the
2232 DN field is longer than
<symbol>NAMEDATALEN
</symbol> (
64 characters
2233 in a standard build).
2238 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2239 <structfield>client_serial
</structfield> <type>numeric
</type>
2242 Serial number of the client certificate, or NULL if no client
2243 certificate was supplied or if SSL is not in use on this connection. The
2244 combination of certificate serial number and certificate issuer uniquely
2245 identifies a certificate (unless the issuer erroneously reuses serial
2251 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2252 <structfield>issuer_dn
</structfield> <type>text
</type>
2255 DN of the issuer of the client certificate, or NULL if no client
2256 certificate was supplied or if SSL is not in use on this connection.
2257 This field is truncated like
<structfield>client_dn
</structfield>.
2266 <sect2 id=
"monitoring-pg-stat-gssapi-view">
2267 <title><structname>pg_stat_gssapi
</structname></title>
2270 <primary>pg_stat_gssapi
</primary>
2274 The
<structname>pg_stat_gssapi
</structname> view will contain one row per
2275 backend, showing information about GSSAPI usage on this connection. It can
2276 be joined to
<structname>pg_stat_activity
</structname> or
2277 <structname>pg_stat_replication
</structname> on the
2278 <structfield>pid
</structfield> column to get more details about the
2282 <table id=
"pg-stat-gssapi-view" xreflabel=
"pg_stat_gssapi">
2283 <title><structname>pg_stat_gssapi
</structname> View
</title>
2287 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2298 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2299 <structfield>pid
</structfield> <type>integer
</type>
2302 Process ID of a backend
2307 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2308 <structfield>gss_authenticated
</structfield> <type>boolean
</type>
2311 True if GSSAPI authentication was used for this connection
2316 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2317 <structfield>principal
</structfield> <type>text
</type>
2320 Principal used to authenticate this connection, or NULL
2321 if GSSAPI was not used to authenticate this connection. This
2322 field is truncated if the principal is longer than
2323 <symbol>NAMEDATALEN
</symbol> (
64 characters in a standard build).
2328 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2329 <structfield>encrypted
</structfield> <type>boolean
</type>
2332 True if GSSAPI encryption is in use on this connection
2337 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2338 <structfield>credentials_delegated
</structfield> <type>boolean
</type>
2341 True if GSSAPI credentials were delegated on this connection.
2350 <sect2 id=
"monitoring-pg-stat-archiver-view">
2351 <title><structname>pg_stat_archiver
</structname></title>
2354 <primary>pg_stat_archiver
</primary>
2358 The
<structname>pg_stat_archiver
</structname> view will always have a
2359 single row, containing data about the archiver process of the cluster.
2362 <table id=
"pg-stat-archiver-view" xreflabel=
"pg_stat_archiver">
2363 <title><structname>pg_stat_archiver
</structname> View
</title>
2367 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2378 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2379 <structfield>archived_count
</structfield> <type>bigint
</type>
2382 Number of WAL files that have been successfully archived
2387 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2388 <structfield>last_archived_wal
</structfield> <type>text
</type>
2391 Name of the WAL file most recently successfully archived
2396 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2397 <structfield>last_archived_time
</structfield> <type>timestamp with time zone
</type>
2400 Time of the most recent successful archive operation
2405 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2406 <structfield>failed_count
</structfield> <type>bigint
</type>
2409 Number of failed attempts for archiving WAL files
2414 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2415 <structfield>last_failed_wal
</structfield> <type>text
</type>
2418 Name of the WAL file of the most recent failed archival operation
2423 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2424 <structfield>last_failed_time
</structfield> <type>timestamp with time zone
</type>
2427 Time of the most recent failed archival operation
2432 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2433 <structfield>stats_reset
</structfield> <type>timestamp with time zone
</type>
2436 Time at which these statistics were last reset
2444 Normally, WAL files are archived in order, oldest to newest, but that is
2445 not guaranteed, and does not hold under special circumstances like when
2446 promoting a standby or after crash recovery. Therefore it is not safe to
2447 assume that all files older than
2448 <structfield>last_archived_wal
</structfield> have also been successfully
2453 <sect2 id=
"monitoring-pg-stat-io-view">
2454 <title><structname>pg_stat_io
</structname></title>
2457 <primary>pg_stat_io
</primary>
2461 The
<structname>pg_stat_io
</structname> view will contain one row for each
2462 combination of backend type, target I/O object, and I/O context, showing
2463 cluster-wide I/O statistics. Combinations which do not make sense are
2468 Currently, I/O on relations (e.g. tables, indexes) is tracked. However,
2469 relation I/O which bypasses shared buffers (e.g. when moving a table from one
2470 tablespace to another) is currently not tracked.
2473 <table id=
"pg-stat-io-view" xreflabel=
"pg_stat_io">
2474 <title><structname>pg_stat_io
</structname> View
</title>
2478 <entry role=
"catalog_table_entry">
2479 <para role=
"column_definition">
2490 <entry role=
"catalog_table_entry">
2491 <para role=
"column_definition">
2492 <structfield>backend_type
</structfield> <type>text
</type>
2495 Type of backend (e.g. background worker, autovacuum worker). See
<link
2496 linkend=
"monitoring-pg-stat-activity-view">
2497 <structname>pg_stat_activity
</structname></link> for more information
2498 on
<varname>backend_type
</varname>s. Some
2499 <varname>backend_type
</varname>s do not accumulate I/O operation
2500 statistics and will not be included in the view.
2506 <entry role=
"catalog_table_entry">
2507 <para role=
"column_definition">
2508 <structfield>object
</structfield> <type>text
</type>
2511 Target object of an I/O operation. Possible values are:
2515 <literal>relation
</literal>: Permanent relations.
2520 <literal>temp relation
</literal>: Temporary relations.
2529 <entry role=
"catalog_table_entry">
2530 <para role=
"column_definition">
2531 <structfield>context
</structfield> <type>text
</type>
2534 The context of an I/O operation. Possible values are:
2539 <literal>normal
</literal>: The default or standard
2540 <varname>context
</varname> for a type of I/O operation. For
2541 example, by default, relation data is read into and written out from
2542 shared buffers. Thus, reads and writes of relation data to and from
2543 shared buffers are tracked in
<varname>context
</varname>
2544 <literal>normal
</literal>.
2549 <literal>vacuum
</literal>: I/O operations performed outside of shared
2550 buffers while vacuuming and analyzing permanent relations. Temporary
2551 table vacuums use the same local buffer pool as other temporary table
2552 IO operations and are tracked in
<varname>context
</varname>
2553 <literal>normal
</literal>.
2558 <literal>bulkread
</literal>: Certain large read I/O operations
2559 done outside of shared buffers, for example, a sequential scan of a
2565 <literal>bulkwrite
</literal>: Certain large write I/O operations
2566 done outside of shared buffers, such as
<command>COPY
</command>.
2574 <entry role=
"catalog_table_entry">
2575 <para role=
"column_definition">
2576 <structfield>reads
</structfield> <type>bigint
</type>
2579 Number of read operations, each of the size specified in
2580 <varname>op_bytes
</varname>.
2586 <entry role=
"catalog_table_entry">
2587 <para role=
"column_definition">
2588 <structfield>read_time
</structfield> <type>double precision
</type>
2591 Time spent in read operations in milliseconds (if
2592 <xref linkend=
"guc-track-io-timing"/> is enabled, otherwise zero)
2598 <entry role=
"catalog_table_entry">
2599 <para role=
"column_definition">
2600 <structfield>writes
</structfield> <type>bigint
</type>
2603 Number of write operations, each of the size specified in
2604 <varname>op_bytes
</varname>.
2610 <entry role=
"catalog_table_entry">
2611 <para role=
"column_definition">
2612 <structfield>write_time
</structfield> <type>double precision
</type>
2615 Time spent in write operations in milliseconds (if
2616 <xref linkend=
"guc-track-io-timing"/> is enabled, otherwise zero)
2622 <entry role=
"catalog_table_entry">
2623 <para role=
"column_definition">
2624 <structfield>writebacks
</structfield> <type>bigint
</type>
2627 Number of units of size
<varname>op_bytes
</varname> which the process
2628 requested the kernel write out to permanent storage.
2634 <entry role=
"catalog_table_entry">
2635 <para role=
"column_definition">
2636 <structfield>writeback_time
</structfield> <type>double precision
</type>
2639 Time spent in writeback operations in milliseconds (if
2640 <xref linkend=
"guc-track-io-timing"/> is enabled, otherwise zero). This
2641 includes the time spent queueing write-out requests and, potentially,
2642 the time spent to write out the dirty data.
2648 <entry role=
"catalog_table_entry">
2649 <para role=
"column_definition">
2650 <structfield>extends
</structfield> <type>bigint
</type>
2653 Number of relation extend operations, each of the size specified in
2654 <varname>op_bytes
</varname>.
2660 <entry role=
"catalog_table_entry">
2661 <para role=
"column_definition">
2662 <structfield>extend_time
</structfield> <type>double precision
</type>
2665 Time spent in extend operations in milliseconds (if
2666 <xref linkend=
"guc-track-io-timing"/> is enabled, otherwise zero)
2672 <entry role=
"catalog_table_entry">
2673 <para role=
"column_definition">
2674 <structfield>op_bytes
</structfield> <type>bigint
</type>
2677 The number of bytes per unit of I/O read, written, or extended.
2680 Relation data reads, writes, and extends are done in
2681 <varname>block_size
</varname> units, derived from the build-time
2682 parameter
<symbol>BLCKSZ
</symbol>, which is
<literal>8192</literal> by
2689 <entry role=
"catalog_table_entry">
2690 <para role=
"column_definition">
2691 <structfield>hits
</structfield> <type>bigint
</type>
2694 The number of times a desired block was found in a shared buffer.
2700 <entry role=
"catalog_table_entry">
2701 <para role=
"column_definition">
2702 <structfield>evictions
</structfield> <type>bigint
</type>
2705 Number of times a block has been written out from a shared or local
2706 buffer in order to make it available for another use.
2709 In
<varname>context
</varname> <literal>normal
</literal>, this counts
2710 the number of times a block was evicted from a buffer and replaced with
2711 another block. In
<varname>context
</varname>s
2712 <literal>bulkwrite
</literal>,
<literal>bulkread
</literal>, and
2713 <literal>vacuum
</literal>, this counts the number of times a block was
2714 evicted from shared buffers in order to add the shared buffer to a
2715 separate, size-limited ring buffer for use in a bulk I/O operation.
2721 <entry role=
"catalog_table_entry">
2722 <para role=
"column_definition">
2723 <structfield>reuses
</structfield> <type>bigint
</type>
2726 The number of times an existing buffer in a size-limited ring buffer
2727 outside of shared buffers was reused as part of an I/O operation in the
2728 <literal>bulkread
</literal>,
<literal>bulkwrite
</literal>, or
2729 <literal>vacuum
</literal> <varname>context
</varname>s.
2735 <entry role=
"catalog_table_entry">
2736 <para role=
"column_definition">
2737 <structfield>fsyncs
</structfield> <type>bigint
</type>
2740 Number of
<literal>fsync
</literal> calls. These are only tracked in
2741 <varname>context
</varname> <literal>normal
</literal>.
2747 <entry role=
"catalog_table_entry">
2748 <para role=
"column_definition">
2749 <structfield>fsync_time
</structfield> <type>double precision
</type>
2752 Time spent in fsync operations in milliseconds (if
2753 <xref linkend=
"guc-track-io-timing"/> is enabled, otherwise zero)
2759 <entry role=
"catalog_table_entry">
2760 <para role=
"column_definition">
2761 <structfield>stats_reset
</structfield> <type>timestamp with time zone
</type>
2764 Time at which these statistics were last reset.
2773 Some backend types never perform I/O operations on some I/O objects and/or
2774 in some I/O contexts. These rows are omitted from the view. For example, the
2775 checkpointer does not checkpoint temporary tables, so there will be no rows
2776 for
<varname>backend_type
</varname> <literal>checkpointer
</literal> and
2777 <varname>object
</varname> <literal>temp relation
</literal>.
2781 In addition, some I/O operations will never be performed either by certain
2782 backend types or on certain I/O objects and/or in certain I/O contexts.
2783 These cells will be NULL. For example, temporary tables are not
2784 <literal>fsync
</literal>ed, so
<varname>fsyncs
</varname> will be NULL for
2785 <varname>object
</varname> <literal>temp relation
</literal>. Also, the
2786 background writer does not perform reads, so
<varname>reads
</varname> will
2787 be NULL in rows for
<varname>backend_type
</varname> <literal>background
2792 <structname>pg_stat_io
</structname> can be used to inform database tuning.
2797 A high
<varname>evictions
</varname> count can indicate that shared
2798 buffers should be increased.
2803 Client backends rely on the checkpointer to ensure data is persisted to
2804 permanent storage. Large numbers of
<varname>fsyncs
</varname> by
2805 <literal>client backend
</literal>s could indicate a misconfiguration of
2806 shared buffers or of the checkpointer. More information on configuring
2807 the checkpointer can be found in
<xref linkend=
"wal-configuration"/>.
2812 Normally, client backends should be able to rely on auxiliary processes
2813 like the checkpointer and the background writer to write out dirty data
2814 as much as possible. Large numbers of writes by client backends could
2815 indicate a misconfiguration of shared buffers or of the checkpointer.
2816 More information on configuring the checkpointer can be found in
<xref
2817 linkend=
"wal-configuration"/>.
2825 Columns tracking I/O time will only be non-zero when
2826 <xref linkend=
"guc-track-io-timing"/> is enabled. The user should be
2827 careful when referencing these columns in combination with their
2828 corresponding IO operations in case
<varname>track_io_timing
</varname>
2829 was not enabled for the entire time since the last stats reset.
2837 <sect2 id=
"monitoring-pg-stat-bgwriter-view">
2838 <title><structname>pg_stat_bgwriter
</structname></title>
2841 <primary>pg_stat_bgwriter
</primary>
2845 The
<structname>pg_stat_bgwriter
</structname> view will always have a
2846 single row, containing global data for the cluster.
2849 <table id=
"pg-stat-bgwriter-view" xreflabel=
"pg_stat_bgwriter">
2850 <title><structname>pg_stat_bgwriter
</structname> View
</title>
2854 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2865 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2866 <structfield>checkpoints_timed
</structfield> <type>bigint
</type>
2869 Number of scheduled checkpoints that have been performed
2874 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2875 <structfield>checkpoints_req
</structfield> <type>bigint
</type>
2878 Number of requested checkpoints that have been performed
2883 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2884 <structfield>checkpoint_write_time
</structfield> <type>double precision
</type>
2887 Total amount of time that has been spent in the portion of
2888 checkpoint processing where files are written to disk, in milliseconds
2893 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2894 <structfield>checkpoint_sync_time
</structfield> <type>double precision
</type>
2897 Total amount of time that has been spent in the portion of
2898 checkpoint processing where files are synchronized to disk, in
2904 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2905 <structfield>buffers_checkpoint
</structfield> <type>bigint
</type>
2908 Number of buffers written during checkpoints
2913 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2914 <structfield>buffers_clean
</structfield> <type>bigint
</type>
2917 Number of buffers written by the background writer
2922 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2923 <structfield>maxwritten_clean
</structfield> <type>bigint
</type>
2926 Number of times the background writer stopped a cleaning
2927 scan because it had written too many buffers
2932 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2933 <structfield>buffers_backend
</structfield> <type>bigint
</type>
2936 Number of buffers written directly by a backend
2941 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2942 <structfield>buffers_backend_fsync
</structfield> <type>bigint
</type>
2945 Number of times a backend had to execute its own
2946 <function>fsync
</function> call (normally the background writer handles those
2947 even when the backend does its own write)
2952 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2953 <structfield>buffers_alloc
</structfield> <type>bigint
</type>
2956 Number of buffers allocated
2961 <entry role=
"catalog_table_entry"><para role=
"column_definition">
2962 <structfield>stats_reset
</structfield> <type>timestamp with time zone
</type>
2965 Time at which these statistics were last reset
2974 <sect2 id=
"monitoring-pg-stat-wal-view">
2975 <title><structname>pg_stat_wal
</structname></title>
2978 <primary>pg_stat_wal
</primary>
2982 The
<structname>pg_stat_wal
</structname> view will always have a
2983 single row, containing data about WAL activity of the cluster.
2986 <table id=
"pg-stat-wal-view" xreflabel=
"pg_stat_wal">
2987 <title><structname>pg_stat_wal
</structname> View
</title>
2991 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3002 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3003 <structfield>wal_records
</structfield> <type>bigint
</type>
3006 Total number of WAL records generated
3011 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3012 <structfield>wal_fpi
</structfield> <type>bigint
</type>
3015 Total number of WAL full page images generated
3020 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3021 <structfield>wal_bytes
</structfield> <type>numeric
</type>
3024 Total amount of WAL generated in bytes
3029 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3030 <structfield>wal_buffers_full
</structfield> <type>bigint
</type>
3033 Number of times WAL data was written to disk because WAL buffers became full
3038 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3039 <structfield>wal_write
</structfield> <type>bigint
</type>
3042 Number of times WAL buffers were written out to disk via
3043 <function>XLogWrite
</function> request.
3044 See
<xref linkend=
"wal-configuration"/> for more information about
3045 the internal WAL function
<function>XLogWrite
</function>.
3050 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3051 <structfield>wal_sync
</structfield> <type>bigint
</type>
3054 Number of times WAL files were synced to disk via
3055 <function>issue_xlog_fsync
</function> request
3056 (if
<xref linkend=
"guc-fsync"/> is
<literal>on
</literal> and
3057 <xref linkend=
"guc-wal-sync-method"/> is either
3058 <literal>fdatasync
</literal>,
<literal>fsync
</literal> or
3059 <literal>fsync_writethrough
</literal>, otherwise zero).
3060 See
<xref linkend=
"wal-configuration"/> for more information about
3061 the internal WAL function
<function>issue_xlog_fsync
</function>.
3066 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3067 <structfield>wal_write_time
</structfield> <type>double precision
</type>
3070 Total amount of time spent writing WAL buffers to disk via
3071 <function>XLogWrite
</function> request, in milliseconds
3072 (if
<xref linkend=
"guc-track-wal-io-timing"/> is enabled,
3073 otherwise zero). This includes the sync time when
3074 <varname>wal_sync_method
</varname> is either
3075 <literal>open_datasync
</literal> or
<literal>open_sync
</literal>.
3080 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3081 <structfield>wal_sync_time
</structfield> <type>double precision
</type>
3084 Total amount of time spent syncing WAL files to disk via
3085 <function>issue_xlog_fsync
</function> request, in milliseconds
3086 (if
<varname>track_wal_io_timing
</varname> is enabled,
3087 <varname>fsync
</varname> is
<literal>on
</literal>, and
3088 <varname>wal_sync_method
</varname> is either
3089 <literal>fdatasync
</literal>,
<literal>fsync
</literal> or
3090 <literal>fsync_writethrough
</literal>, otherwise zero).
3095 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3096 <structfield>stats_reset
</structfield> <type>timestamp with time zone
</type>
3099 Time at which these statistics were last reset
3108 <sect2 id=
"monitoring-pg-stat-database-view">
3109 <title><structname>pg_stat_database
</structname></title>
3112 <primary>pg_stat_database
</primary>
3116 The
<structname>pg_stat_database
</structname> view will contain one row
3117 for each database in the cluster, plus one for shared objects, showing
3118 database-wide statistics.
3121 <table id=
"pg-stat-database-view" xreflabel=
"pg_stat_database">
3122 <title><structname>pg_stat_database
</structname> View
</title>
3126 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3137 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3138 <structfield>datid
</structfield> <type>oid
</type>
3141 OID of this database, or
0 for objects belonging to a shared
3147 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3148 <structfield>datname
</structfield> <type>name
</type>
3151 Name of this database, or
<literal>NULL
</literal> for shared
3157 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3158 <structfield>numbackends
</structfield> <type>integer
</type>
3161 Number of backends currently connected to this database, or
3162 <literal>NULL
</literal> for shared objects. This is the only column
3163 in this view that returns a value reflecting current state; all other
3164 columns return the accumulated values since the last reset.
3169 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3170 <structfield>xact_commit
</structfield> <type>bigint
</type>
3173 Number of transactions in this database that have been
3179 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3180 <structfield>xact_rollback
</structfield> <type>bigint
</type>
3183 Number of transactions in this database that have been
3189 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3190 <structfield>blks_read
</structfield> <type>bigint
</type>
3193 Number of disk blocks read in this database
3198 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3199 <structfield>blks_hit
</structfield> <type>bigint
</type>
3202 Number of times disk blocks were found already in the buffer
3203 cache, so that a read was not necessary (this only includes hits in the
3204 PostgreSQL buffer cache, not the operating system's file system cache)
3209 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3210 <structfield>tup_returned
</structfield> <type>bigint
</type>
3213 Number of live rows fetched by sequential scans and index entries returned by index scans in this database
3218 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3219 <structfield>tup_fetched
</structfield> <type>bigint
</type>
3222 Number of live rows fetched by index scans in this database
3227 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3228 <structfield>tup_inserted
</structfield> <type>bigint
</type>
3231 Number of rows inserted by queries in this database
3236 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3237 <structfield>tup_updated
</structfield> <type>bigint
</type>
3240 Number of rows updated by queries in this database
3245 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3246 <structfield>tup_deleted
</structfield> <type>bigint
</type>
3249 Number of rows deleted by queries in this database
3254 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3255 <structfield>conflicts
</structfield> <type>bigint
</type>
3258 Number of queries canceled due to conflicts with recovery
3259 in this database. (Conflicts occur only on standby servers; see
3260 <link linkend=
"monitoring-pg-stat-database-conflicts-view">
3261 <structname>pg_stat_database_conflicts
</structname></link> for details.)
3266 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3267 <structfield>temp_files
</structfield> <type>bigint
</type>
3270 Number of temporary files created by queries in this database.
3271 All temporary files are counted, regardless of why the temporary file
3272 was created (e.g., sorting or hashing), and regardless of the
3273 <xref linkend=
"guc-log-temp-files"/> setting.
3278 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3279 <structfield>temp_bytes
</structfield> <type>bigint
</type>
3282 Total amount of data written to temporary files by queries in
3283 this database. All temporary files are counted, regardless of why
3284 the temporary file was created, and
3285 regardless of the
<xref linkend=
"guc-log-temp-files"/> setting.
3290 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3291 <structfield>deadlocks
</structfield> <type>bigint
</type>
3294 Number of deadlocks detected in this database
3299 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3300 <structfield>checksum_failures
</structfield> <type>bigint
</type>
3303 Number of data page checksum failures detected in this
3304 database (or on a shared object), or NULL if data checksums are not
3310 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3311 <structfield>checksum_last_failure
</structfield> <type>timestamp with time zone
</type>
3314 Time at which the last data page checksum failure was detected in
3315 this database (or on a shared object), or NULL if data checksums are not
3321 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3322 <structfield>blk_read_time
</structfield> <type>double precision
</type>
3325 Time spent reading data file blocks by backends in this database,
3326 in milliseconds (if
<xref linkend=
"guc-track-io-timing"/> is enabled,
3332 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3333 <structfield>blk_write_time
</structfield> <type>double precision
</type>
3336 Time spent writing data file blocks by backends in this database,
3337 in milliseconds (if
<xref linkend=
"guc-track-io-timing"/> is enabled,
3343 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3344 <structfield>session_time
</structfield> <type>double precision
</type>
3347 Time spent by database sessions in this database, in milliseconds
3348 (note that statistics are only updated when the state of a session
3349 changes, so if sessions have been idle for a long time, this idle time
3355 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3356 <structfield>active_time
</structfield> <type>double precision
</type>
3359 Time spent executing SQL statements in this database, in milliseconds
3360 (this corresponds to the states
<literal>active
</literal> and
3361 <literal>fastpath function call
</literal> in
3362 <link linkend=
"monitoring-pg-stat-activity-view">
3363 <structname>pg_stat_activity
</structname></link>)
3368 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3369 <structfield>idle_in_transaction_time
</structfield> <type>double precision
</type>
3372 Time spent idling while in a transaction in this database, in milliseconds
3373 (this corresponds to the states
<literal>idle in transaction
</literal> and
3374 <literal>idle in transaction (aborted)
</literal> in
3375 <link linkend=
"monitoring-pg-stat-activity-view">
3376 <structname>pg_stat_activity
</structname></link>)
3381 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3382 <structfield>sessions
</structfield> <type>bigint
</type>
3385 Total number of sessions established to this database
3390 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3391 <structfield>sessions_abandoned
</structfield> <type>bigint
</type>
3394 Number of database sessions to this database that were terminated
3395 because connection to the client was lost
3400 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3401 <structfield>sessions_fatal
</structfield> <type>bigint
</type>
3404 Number of database sessions to this database that were terminated
3410 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3411 <structfield>sessions_killed
</structfield> <type>bigint
</type>
3414 Number of database sessions to this database that were terminated
3415 by operator intervention
3420 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3421 <structfield>stats_reset
</structfield> <type>timestamp with time zone
</type>
3424 Time at which these statistics were last reset
3433 <sect2 id=
"monitoring-pg-stat-database-conflicts-view">
3434 <title><structname>pg_stat_database_conflicts
</structname></title>
3437 <primary>pg_stat_database_conflicts
</primary>
3441 The
<structname>pg_stat_database_conflicts
</structname> view will contain
3442 one row per database, showing database-wide statistics about
3443 query cancels occurring due to conflicts with recovery on standby servers.
3444 This view will only contain information on standby servers, since
3445 conflicts do not occur on primary servers.
3448 <table id=
"pg-stat-database-conflicts-view" xreflabel=
"pg_stat_database_conflicts">
3449 <title><structname>pg_stat_database_conflicts
</structname> View
</title>
3453 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3464 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3465 <structfield>datid
</structfield> <type>oid
</type>
3473 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3474 <structfield>datname
</structfield> <type>name
</type>
3477 Name of this database
3482 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3483 <structfield>confl_tablespace
</structfield> <type>bigint
</type>
3486 Number of queries in this database that have been canceled due to
3492 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3493 <structfield>confl_lock
</structfield> <type>bigint
</type>
3496 Number of queries in this database that have been canceled due to
3502 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3503 <structfield>confl_snapshot
</structfield> <type>bigint
</type>
3506 Number of queries in this database that have been canceled due to
3512 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3513 <structfield>confl_bufferpin
</structfield> <type>bigint
</type>
3516 Number of queries in this database that have been canceled due to
3522 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3523 <structfield>confl_deadlock
</structfield> <type>bigint
</type>
3526 Number of queries in this database that have been canceled due to
3532 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3533 <structfield>confl_active_logicalslot
</structfield> <type>bigint
</type>
3536 Number of uses of logical slots in this database that have been
3537 canceled due to old snapshots or too low a
<xref linkend=
"guc-wal-level"/>
3547 <sect2 id=
"monitoring-pg-stat-all-tables-view">
3548 <title><structname>pg_stat_all_tables
</structname></title>
3551 <primary>pg_stat_all_tables
</primary>
3555 The
<structname>pg_stat_all_tables
</structname> view will contain
3556 one row for each table in the current database (including TOAST
3557 tables), showing statistics about accesses to that specific table. The
3558 <structname>pg_stat_user_tables
</structname> and
3559 <structname>pg_stat_sys_tables
</structname> views
3560 contain the same information,
3561 but filtered to only show user and system tables respectively.
3564 <table id=
"pg-stat-all-tables-view" xreflabel=
"pg_stat_all_tables">
3565 <title><structname>pg_stat_all_tables
</structname> View
</title>
3569 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3580 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3581 <structfield>relid
</structfield> <type>oid
</type>
3589 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3590 <structfield>schemaname
</structfield> <type>name
</type>
3593 Name of the schema that this table is in
3598 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3599 <structfield>relname
</structfield> <type>name
</type>
3607 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3608 <structfield>seq_scan
</structfield> <type>bigint
</type>
3611 Number of sequential scans initiated on this table
3616 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3617 <structfield>last_seq_scan
</structfield> <type>timestamp with time zone
</type>
3620 The time of the last sequential scan on this table, based on the
3621 most recent transaction stop time
3626 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3627 <structfield>seq_tup_read
</structfield> <type>bigint
</type>
3630 Number of live rows fetched by sequential scans
3635 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3636 <structfield>idx_scan
</structfield> <type>bigint
</type>
3639 Number of index scans initiated on this table
3644 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3645 <structfield>last_idx_scan
</structfield> <type>timestamp with time zone
</type>
3648 The time of the last index scan on this table, based on the
3649 most recent transaction stop time
3654 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3655 <structfield>idx_tup_fetch
</structfield> <type>bigint
</type>
3658 Number of live rows fetched by index scans
3663 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3664 <structfield>n_tup_ins
</structfield> <type>bigint
</type>
3667 Total number of rows inserted
3672 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3673 <structfield>n_tup_upd
</structfield> <type>bigint
</type>
3676 Total number of rows updated. (This includes row updates
3677 counted in
<structfield>n_tup_hot_upd
</structfield> and
3678 <structfield>n_tup_newpage_upd
</structfield>, and remaining
3679 non-
<acronym>HOT
</acronym> updates.)
3684 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3685 <structfield>n_tup_del
</structfield> <type>bigint
</type>
3688 Total number of rows deleted
3693 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3694 <structfield>n_tup_hot_upd
</structfield> <type>bigint
</type>
3697 Number of rows
<link linkend=
"storage-hot">HOT updated
</link>.
3698 These are updates where no successor versions are required in
3704 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3705 <structfield>n_tup_newpage_upd
</structfield> <type>bigint
</type>
3708 Number of rows updated where the successor version goes onto a
3709 <emphasis>new
</emphasis> heap page, leaving behind an original
3711 <link linkend=
"storage-tuple-layout"><structfield>t_ctid
</structfield>
3712 field
</link> that points to a different heap page. These are
3713 always non-
<acronym>HOT
</acronym> updates.
3718 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3719 <structfield>n_live_tup
</structfield> <type>bigint
</type>
3722 Estimated number of live rows
3727 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3728 <structfield>n_dead_tup
</structfield> <type>bigint
</type>
3731 Estimated number of dead rows
3736 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3737 <structfield>n_mod_since_analyze
</structfield> <type>bigint
</type>
3740 Estimated number of rows modified since this table was last analyzed
3745 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3746 <structfield>n_ins_since_vacuum
</structfield> <type>bigint
</type>
3749 Estimated number of rows inserted since this table was last vacuumed
3754 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3755 <structfield>last_vacuum
</structfield> <type>timestamp with time zone
</type>
3758 Last time at which this table was manually vacuumed
3759 (not counting
<command>VACUUM FULL
</command>)
3764 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3765 <structfield>last_autovacuum
</structfield> <type>timestamp with time zone
</type>
3768 Last time at which this table was vacuumed by the autovacuum
3774 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3775 <structfield>last_analyze
</structfield> <type>timestamp with time zone
</type>
3778 Last time at which this table was manually analyzed
3783 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3784 <structfield>last_autoanalyze
</structfield> <type>timestamp with time zone
</type>
3787 Last time at which this table was analyzed by the autovacuum
3793 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3794 <structfield>vacuum_count
</structfield> <type>bigint
</type>
3797 Number of times this table has been manually vacuumed
3798 (not counting
<command>VACUUM FULL
</command>)
3803 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3804 <structfield>autovacuum_count
</structfield> <type>bigint
</type>
3807 Number of times this table has been vacuumed by the autovacuum
3813 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3814 <structfield>analyze_count
</structfield> <type>bigint
</type>
3817 Number of times this table has been manually analyzed
3822 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3823 <structfield>autoanalyze_count
</structfield> <type>bigint
</type>
3826 Number of times this table has been analyzed by the autovacuum
3836 <sect2 id=
"monitoring-pg-stat-all-indexes-view">
3837 <title><structname>pg_stat_all_indexes
</structname></title>
3840 <primary>pg_stat_all_indexes
</primary>
3844 The
<structname>pg_stat_all_indexes
</structname> view will contain
3845 one row for each index in the current database,
3846 showing statistics about accesses to that specific index. The
3847 <structname>pg_stat_user_indexes
</structname> and
3848 <structname>pg_stat_sys_indexes
</structname> views
3849 contain the same information,
3850 but filtered to only show user and system indexes respectively.
3853 <table id=
"pg-stat-all-indexes-view" xreflabel=
"pg_stat_all_indexes">
3854 <title><structname>pg_stat_all_indexes
</structname> View
</title>
3858 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3869 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3870 <structfield>relid
</structfield> <type>oid
</type>
3873 OID of the table for this index
3878 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3879 <structfield>indexrelid
</structfield> <type>oid
</type>
3887 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3888 <structfield>schemaname
</structfield> <type>name
</type>
3891 Name of the schema this index is in
3896 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3897 <structfield>relname
</structfield> <type>name
</type>
3900 Name of the table for this index
3905 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3906 <structfield>indexrelname
</structfield> <type>name
</type>
3914 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3915 <structfield>idx_scan
</structfield> <type>bigint
</type>
3918 Number of index scans initiated on this index
3923 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3924 <structfield>last_idx_scan
</structfield> <type>timestamp with time zone
</type>
3927 The time of the last scan on this index, based on the
3928 most recent transaction stop time
3933 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3934 <structfield>idx_tup_read
</structfield> <type>bigint
</type>
3937 Number of index entries returned by scans on this index
3942 <entry role=
"catalog_table_entry"><para role=
"column_definition">
3943 <structfield>idx_tup_fetch
</structfield> <type>bigint
</type>
3946 Number of live table rows fetched by simple index scans using this
3955 Indexes can be used by simple index scans,
<quote>bitmap
</quote> index scans,
3956 and the optimizer. In a bitmap scan
3957 the output of several indexes can be combined via AND or OR rules,
3958 so it is difficult to associate individual heap row fetches
3959 with specific indexes when a bitmap scan is used. Therefore, a bitmap
3961 <structname>pg_stat_all_indexes
</structname>.
<structfield>idx_tup_read
</structfield>
3962 count(s) for the index(es) it uses, and it increments the
3963 <structname>pg_stat_all_tables
</structname>.
<structfield>idx_tup_fetch
</structfield>
3964 count for the table, but it does not affect
3965 <structname>pg_stat_all_indexes
</structname>.
<structfield>idx_tup_fetch
</structfield>.
3966 The optimizer also accesses indexes to check for supplied constants
3967 whose values are outside the recorded range of the optimizer statistics
3968 because the optimizer statistics might be stale.
3973 The
<structfield>idx_tup_read
</structfield> and
<structfield>idx_tup_fetch
</structfield> counts
3974 can be different even without any use of bitmap scans,
3975 because
<structfield>idx_tup_read
</structfield> counts
3976 index entries retrieved from the index while
<structfield>idx_tup_fetch
</structfield>
3977 counts live rows fetched from the table. The latter will be less if any
3978 dead or not-yet-committed rows are fetched using the index, or if any
3979 heap fetches are avoided by means of an index-only scan.
3985 <sect2 id=
"monitoring-pg-statio-all-tables-view">
3986 <title><structname>pg_statio_all_tables
</structname></title>
3989 <primary>pg_statio_all_tables
</primary>
3993 The
<structname>pg_statio_all_tables
</structname> view will contain
3994 one row for each table in the current database (including TOAST
3995 tables), showing statistics about I/O on that specific table. The
3996 <structname>pg_statio_user_tables
</structname> and
3997 <structname>pg_statio_sys_tables
</structname> views
3998 contain the same information,
3999 but filtered to only show user and system tables respectively.
4002 <table id=
"pg-statio-all-tables-view" xreflabel=
"pg_statio_all_tables">
4003 <title><structname>pg_statio_all_tables
</structname> View
</title>
4007 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4018 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4019 <structfield>relid
</structfield> <type>oid
</type>
4027 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4028 <structfield>schemaname
</structfield> <type>name
</type>
4031 Name of the schema that this table is in
4036 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4037 <structfield>relname
</structfield> <type>name
</type>
4045 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4046 <structfield>heap_blks_read
</structfield> <type>bigint
</type>
4049 Number of disk blocks read from this table
4054 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4055 <structfield>heap_blks_hit
</structfield> <type>bigint
</type>
4058 Number of buffer hits in this table
4063 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4064 <structfield>idx_blks_read
</structfield> <type>bigint
</type>
4067 Number of disk blocks read from all indexes on this table
4072 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4073 <structfield>idx_blks_hit
</structfield> <type>bigint
</type>
4076 Number of buffer hits in all indexes on this table
4081 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4082 <structfield>toast_blks_read
</structfield> <type>bigint
</type>
4085 Number of disk blocks read from this table's TOAST table (if any)
4090 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4091 <structfield>toast_blks_hit
</structfield> <type>bigint
</type>
4094 Number of buffer hits in this table's TOAST table (if any)
4099 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4100 <structfield>tidx_blks_read
</structfield> <type>bigint
</type>
4103 Number of disk blocks read from this table's TOAST table indexes (if any)
4108 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4109 <structfield>tidx_blks_hit
</structfield> <type>bigint
</type>
4112 Number of buffer hits in this table's TOAST table indexes (if any)
4121 <sect2 id=
"monitoring-pg-statio-all-indexes-view">
4122 <title><structname>pg_statio_all_indexes
</structname></title>
4125 <primary>pg_statio_all_indexes
</primary>
4129 The
<structname>pg_statio_all_indexes
</structname> view will contain
4130 one row for each index in the current database,
4131 showing statistics about I/O on that specific index. The
4132 <structname>pg_statio_user_indexes
</structname> and
4133 <structname>pg_statio_sys_indexes
</structname> views
4134 contain the same information,
4135 but filtered to only show user and system indexes respectively.
4138 <table id=
"pg-statio-all-indexes-view" xreflabel=
"pg_statio_all_indexes">
4139 <title><structname>pg_statio_all_indexes
</structname> View
</title>
4143 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4154 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4155 <structfield>relid
</structfield> <type>oid
</type>
4158 OID of the table for this index
4163 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4164 <structfield>indexrelid
</structfield> <type>oid
</type>
4172 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4173 <structfield>schemaname
</structfield> <type>name
</type>
4176 Name of the schema this index is in
4181 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4182 <structfield>relname
</structfield> <type>name
</type>
4185 Name of the table for this index
4190 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4191 <structfield>indexrelname
</structfield> <type>name
</type>
4199 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4200 <structfield>idx_blks_read
</structfield> <type>bigint
</type>
4203 Number of disk blocks read from this index
4208 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4209 <structfield>idx_blks_hit
</structfield> <type>bigint
</type>
4212 Number of buffer hits in this index
4221 <sect2 id=
"monitoring-pg-statio-all-sequences-view">
4222 <title><structname>pg_statio_all_sequences
</structname></title>
4225 <primary>pg_statio_all_sequences
</primary>
4229 The
<structname>pg_statio_all_sequences
</structname> view will contain
4230 one row for each sequence in the current database,
4231 showing statistics about I/O on that specific sequence.
4234 <table id=
"pg-statio-all-sequences-view" xreflabel=
"pg_statio_all_sequences">
4235 <title><structname>pg_statio_all_sequences
</structname> View
</title>
4239 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4250 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4251 <structfield>relid
</structfield> <type>oid
</type>
4259 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4260 <structfield>schemaname
</structfield> <type>name
</type>
4263 Name of the schema this sequence is in
4268 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4269 <structfield>relname
</structfield> <type>name
</type>
4272 Name of this sequence
4277 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4278 <structfield>blks_read
</structfield> <type>bigint
</type>
4281 Number of disk blocks read from this sequence
4286 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4287 <structfield>blks_hit
</structfield> <type>bigint
</type>
4290 Number of buffer hits in this sequence
4299 <sect2 id=
"monitoring-pg-stat-user-functions-view">
4300 <title><structname>pg_stat_user_functions
</structname></title>
4303 <primary>pg_stat_user_functions
</primary>
4307 The
<structname>pg_stat_user_functions
</structname> view will contain
4308 one row for each tracked function, showing statistics about executions of
4309 that function. The
<xref linkend=
"guc-track-functions"/> parameter
4310 controls exactly which functions are tracked.
4313 <table id=
"pg-stat-user-functions-view" xreflabel=
"pg_stat_user_functions">
4314 <title><structname>pg_stat_user_functions
</structname> View
</title>
4318 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4329 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4330 <structfield>funcid
</structfield> <type>oid
</type>
4338 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4339 <structfield>schemaname
</structfield> <type>name
</type>
4342 Name of the schema this function is in
4347 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4348 <structfield>funcname
</structfield> <type>name
</type>
4351 Name of this function
4356 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4357 <structfield>calls
</structfield> <type>bigint
</type>
4360 Number of times this function has been called
4365 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4366 <structfield>total_time
</structfield> <type>double precision
</type>
4369 Total time spent in this function and all other functions
4370 called by it, in milliseconds
4375 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4376 <structfield>self_time
</structfield> <type>double precision
</type>
4379 Total time spent in this function itself, not including
4380 other functions called by it, in milliseconds
4389 <sect2 id=
"monitoring-pg-stat-slru-view">
4390 <title><structname>pg_stat_slru
</structname></title>
4393 <primary>SLRU
</primary>
4397 <primary>pg_stat_slru
</primary>
4401 <productname>PostgreSQL
</productname> accesses certain on-disk information
4402 via
<firstterm>SLRU
</firstterm> (simple least-recently-used) caches.
4403 The
<structname>pg_stat_slru
</structname> view will contain
4404 one row for each tracked SLRU cache, showing statistics about access
4408 <table id=
"pg-stat-slru-view" xreflabel=
"pg_stat_slru">
4409 <title><structname>pg_stat_slru
</structname> View
</title>
4413 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4424 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4425 <structfield>name
</structfield> <type>text
</type>
4433 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4434 <structfield>blks_zeroed
</structfield> <type>bigint
</type>
4437 Number of blocks zeroed during initializations
4442 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4443 <structfield>blks_hit
</structfield> <type>bigint
</type>
4446 Number of times disk blocks were found already in the SLRU,
4447 so that a read was not necessary (this only includes hits in the
4448 SLRU, not the operating system's file system cache)
4453 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4454 <structfield>blks_read
</structfield> <type>bigint
</type>
4457 Number of disk blocks read for this SLRU
4462 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4463 <structfield>blks_written
</structfield> <type>bigint
</type>
4466 Number of disk blocks written for this SLRU
4471 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4472 <structfield>blks_exists
</structfield> <type>bigint
</type>
4475 Number of blocks checked for existence for this SLRU
4480 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4481 <structfield>flushes
</structfield> <type>bigint
</type>
4484 Number of flushes of dirty data for this SLRU
4489 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4490 <structfield>truncates
</structfield> <type>bigint
</type>
4493 Number of truncates for this SLRU
4498 <entry role=
"catalog_table_entry"><para role=
"column_definition">
4499 <structfield>stats_reset
</structfield> <type>timestamp with time zone
</type>
4502 Time at which these statistics were last reset
4511 <sect2 id=
"monitoring-stats-functions">
4512 <title>Statistics Functions
</title>
4515 Other ways of looking at the statistics can be set up by writing
4516 queries that use the same underlying statistics access functions used by
4517 the standard views shown above. For details such as the functions' names,
4518 consult the definitions of the standard views. (For example, in
4519 <application>psql
</application> you could issue
<literal>\d+ pg_stat_activity
</literal>.)
4520 The access functions for per-database statistics take a database OID as an
4521 argument to identify which database to report on.
4522 The per-table and per-index functions take a table or index OID.
4523 The functions for per-function statistics take a function OID.
4524 Note that only tables, indexes, and functions in the current database
4525 can be seen with these functions.
4529 Additional functions related to the cumulative statistics system are listed
4530 in
<xref linkend=
"monitoring-stats-funcs-table"/>.
4533 <table id=
"monitoring-stats-funcs-table">
4534 <title>Additional Statistics Functions
</title>
4538 <entry role=
"func_table_entry"><para role=
"func_signature">
4549 <!-- See also the entry for this in func.sgml -->
4550 <entry role=
"func_table_entry"><para role=
"func_signature">
4551 <function>pg_backend_pid
</function> ()
4552 <returnvalue>integer
</returnvalue>
4555 Returns the process ID of the server process attached to the current
4561 <entry role=
"func_table_entry"><para role=
"func_signature">
4563 <primary>pg_stat_get_activity
</primary>
4565 <function>pg_stat_get_activity
</function> (
<type>integer
</type> )
4566 <returnvalue>setof record
</returnvalue>
4569 Returns a record of information about the backend with the specified
4570 process ID, or one record for each active backend in the system
4571 if
<literal>NULL
</literal> is specified. The fields returned are a
4572 subset of those in the
<structname>pg_stat_activity
</structname> view.
4577 <entry role=
"func_table_entry"><para role=
"func_signature">
4579 <primary>pg_stat_get_snapshot_timestamp
</primary>
4581 <function>pg_stat_get_snapshot_timestamp
</function> ()
4582 <returnvalue>timestamp with time zone
</returnvalue>
4585 Returns the timestamp of the current statistics snapshot, or NULL if
4586 no statistics snapshot has been taken. A snapshot is taken the first
4587 time cumulative statistics are accessed in a transaction if
4588 <varname>stats_fetch_consistency
</varname> is set to
4589 <literal>snapshot
</literal>
4594 <entry role=
"func_table_entry"><para role=
"func_signature">
4596 <primary>pg_stat_get_xact_blocks_fetched
</primary>
4598 <function>pg_stat_get_xact_blocks_fetched
</function> (
<type>oid
</type> )
4599 <returnvalue>bigint
</returnvalue>
4602 Returns the number of block read requests for table or index, in the
4603 current transaction. This number minus
4604 <function>pg_stat_get_xact_blocks_hit
</function> gives the number of
4605 kernel
<function>read()
</function> calls; the number of actual
4606 physical reads is usually lower due to kernel-level buffering.
4611 <entry role=
"func_table_entry"><para role=
"func_signature">
4613 <primary>pg_stat_get_xact_blocks_hit
</primary>
4615 <function>pg_stat_get_xact_blocks_hit
</function> (
<type>oid
</type> )
4616 <returnvalue>bigint
</returnvalue>
4619 Returns the number of block read requests for table or index, in the
4620 current transaction, found in cache (not triggering kernel
4621 <function>read()
</function> calls).
4626 <entry role=
"func_table_entry"><para role=
"func_signature">
4628 <primary>pg_stat_clear_snapshot
</primary>
4630 <function>pg_stat_clear_snapshot
</function> ()
4631 <returnvalue>void
</returnvalue>
4634 Discards the current statistics snapshot or cached information.
4639 <entry role=
"func_table_entry"><para role=
"func_signature">
4641 <primary>pg_stat_reset
</primary>
4643 <function>pg_stat_reset
</function> ()
4644 <returnvalue>void
</returnvalue>
4647 Resets all statistics counters for the current database to zero.
4650 This function is restricted to superusers by default, but other users
4651 can be granted EXECUTE to run the function.
4656 <entry role=
"func_table_entry"><para role=
"func_signature">
4658 <primary>pg_stat_reset_shared
</primary>
4660 <function>pg_stat_reset_shared
</function> (
<type>text
</type> )
4661 <returnvalue>void
</returnvalue>
4664 Resets some cluster-wide statistics counters to zero, depending on the
4665 argument. The argument can be
<literal>bgwriter
</literal> to reset
4666 all the counters shown in
4667 the
<structname>pg_stat_bgwriter
</structname>
4668 view,
<literal>archiver
</literal> to reset all the counters shown in
4669 the
<structname>pg_stat_archiver
</structname> view,
4670 <literal>io
</literal> to reset all the counters shown in the
4671 <structname>pg_stat_io
</structname> view,
4672 <literal>wal
</literal> to reset all the counters shown in the
4673 <structname>pg_stat_wal
</structname> view or
4674 <literal>recovery_prefetch
</literal> to reset all the counters shown
4675 in the
<structname>pg_stat_recovery_prefetch
</structname> view.
4678 This function is restricted to superusers by default, but other users
4679 can be granted EXECUTE to run the function.
4684 <entry role=
"func_table_entry"><para role=
"func_signature">
4686 <primary>pg_stat_reset_single_table_counters
</primary>
4688 <function>pg_stat_reset_single_table_counters
</function> (
<type>oid
</type> )
4689 <returnvalue>void
</returnvalue>
4692 Resets statistics for a single table or index in the current database
4693 or shared across all databases in the cluster to zero.
4696 This function is restricted to superusers by default, but other users
4697 can be granted EXECUTE to run the function.
4702 <entry role=
"func_table_entry"><para role=
"func_signature">
4704 <primary>pg_stat_reset_single_function_counters
</primary>
4706 <function>pg_stat_reset_single_function_counters
</function> (
<type>oid
</type> )
4707 <returnvalue>void
</returnvalue>
4710 Resets statistics for a single function in the current database to
4714 This function is restricted to superusers by default, but other users
4715 can be granted EXECUTE to run the function.
4720 <entry role=
"func_table_entry"><para role=
"func_signature">
4722 <primary>pg_stat_reset_slru
</primary>
4724 <function>pg_stat_reset_slru
</function> (
<type>text
</type> )
4725 <returnvalue>void
</returnvalue>
4728 Resets statistics to zero for a single SLRU cache, or for all SLRUs in
4729 the cluster. If the argument is NULL, all counters shown in
4730 the
<structname>pg_stat_slru
</structname> view for all SLRU caches are
4731 reset. The argument can be one of
4732 <literal>CommitTs
</literal>,
4733 <literal>MultiXactMember
</literal>,
4734 <literal>MultiXactOffset
</literal>,
4735 <literal>Notify
</literal>,
4736 <literal>Serial
</literal>,
4737 <literal>Subtrans
</literal>, or
4738 <literal>Xact
</literal>
4739 to reset the counters for only that entry.
4740 If the argument is
<literal>other
</literal> (or indeed, any
4741 unrecognized name), then the counters for all other SLRU caches, such
4742 as extension-defined caches, are reset.
4745 This function is restricted to superusers by default, but other users
4746 can be granted EXECUTE to run the function.
4751 <entry role=
"func_table_entry"><para role=
"func_signature">
4753 <primary>pg_stat_reset_replication_slot
</primary>
4755 <function>pg_stat_reset_replication_slot
</function> (
<type>text
</type> )
4756 <returnvalue>void
</returnvalue>
4759 Resets statistics of the replication slot defined by the argument. If
4760 the argument is
<literal>NULL
</literal>, resets statistics for all
4761 the replication slots.
4764 This function is restricted to superusers by default, but other users
4765 can be granted EXECUTE to run the function.
4770 <entry role=
"func_table_entry"><para role=
"func_signature">
4772 <primary>pg_stat_reset_subscription_stats
</primary>
4774 <function>pg_stat_reset_subscription_stats
</function> (
<type>oid
</type> )
4775 <returnvalue>void
</returnvalue>
4778 Resets statistics for a single subscription shown in the
4779 <structname>pg_stat_subscription_stats
</structname> view to zero. If
4780 the argument is
<literal>NULL
</literal>, reset statistics for all
4784 This function is restricted to superusers by default, but other users
4785 can be granted EXECUTE to run the function.
4794 Using
<function>pg_stat_reset()
</function> also resets counters that
4795 autovacuum uses to determine when to trigger a vacuum or an analyze.
4796 Resetting these counters can cause autovacuum to not perform necessary
4797 work, which can cause problems such as table bloat or out-dated
4798 table statistics. A database-wide
<command>ANALYZE
</command> is
4799 recommended after the statistics have been reset.
4804 <function>pg_stat_get_activity
</function>, the underlying function of
4805 the
<structname>pg_stat_activity
</structname> view, returns a set of records
4806 containing all the available information about each backend process.
4807 Sometimes it may be more convenient to obtain just a subset of this
4808 information. In such cases, another set of per-backend statistics
4809 access functions can be used; these are shown in
<xref
4810 linkend=
"monitoring-stats-backend-funcs-table"/>.
4811 These access functions use the session's backend ID number, which is a
4812 small positive integer that is distinct from the backend ID of any
4813 concurrent session, although a session's ID can be recycled as soon as
4814 it exits. The backend ID is used, among other things, to identify the
4815 session's temporary schema if it has one.
4816 The function
<function>pg_stat_get_backend_idset
</function> provides a
4817 convenient way to list all the active backends' ID numbers for
4818 invoking these functions. For example, to show the
<acronym>PID
</acronym>s and
4819 current queries of all backends:
4822 SELECT pg_stat_get_backend_pid(backendid) AS pid,
4823 pg_stat_get_backend_activity(backendid) AS query
4824 FROM pg_stat_get_backend_idset() AS backendid;
4828 <table id=
"monitoring-stats-backend-funcs-table">
4829 <title>Per-Backend Statistics Functions
</title>
4833 <entry role=
"func_table_entry"><para role=
"func_signature">
4844 <entry role=
"func_table_entry"><para role=
"func_signature">
4846 <primary>pg_stat_get_backend_activity
</primary>
4848 <function>pg_stat_get_backend_activity
</function> (
<type>integer
</type> )
4849 <returnvalue>text
</returnvalue>
4852 Returns the text of this backend's most recent query.
4857 <entry role=
"func_table_entry"><para role=
"func_signature">
4859 <primary>pg_stat_get_backend_activity_start
</primary>
4861 <function>pg_stat_get_backend_activity_start
</function> (
<type>integer
</type> )
4862 <returnvalue>timestamp with time zone
</returnvalue>
4865 Returns the time when the backend's most recent query was started.
4870 <entry role=
"func_table_entry"><para role=
"func_signature">
4872 <primary>pg_stat_get_backend_client_addr
</primary>
4874 <function>pg_stat_get_backend_client_addr
</function> (
<type>integer
</type> )
4875 <returnvalue>inet
</returnvalue>
4878 Returns the IP address of the client connected to this backend.
4883 <entry role=
"func_table_entry"><para role=
"func_signature">
4885 <primary>pg_stat_get_backend_client_port
</primary>
4887 <function>pg_stat_get_backend_client_port
</function> (
<type>integer
</type> )
4888 <returnvalue>integer
</returnvalue>
4891 Returns the TCP port number that the client is using for communication.
4896 <entry role=
"func_table_entry"><para role=
"func_signature">
4898 <primary>pg_stat_get_backend_dbid
</primary>
4900 <function>pg_stat_get_backend_dbid
</function> (
<type>integer
</type> )
4901 <returnvalue>oid
</returnvalue>
4904 Returns the OID of the database this backend is connected to.
4909 <entry role=
"func_table_entry"><para role=
"func_signature">
4911 <primary>pg_stat_get_backend_idset
</primary>
4913 <function>pg_stat_get_backend_idset
</function> ()
4914 <returnvalue>setof integer
</returnvalue>
4917 Returns the set of currently active backend ID numbers.
4922 <entry role=
"func_table_entry"><para role=
"func_signature">
4924 <primary>pg_stat_get_backend_pid
</primary>
4926 <function>pg_stat_get_backend_pid
</function> (
<type>integer
</type> )
4927 <returnvalue>integer
</returnvalue>
4930 Returns the process ID of this backend.
4935 <entry role=
"func_table_entry"><para role=
"func_signature">
4937 <primary>pg_stat_get_backend_start
</primary>
4939 <function>pg_stat_get_backend_start
</function> (
<type>integer
</type> )
4940 <returnvalue>timestamp with time zone
</returnvalue>
4943 Returns the time when this process was started.
4948 <entry role=
"func_table_entry"><para role=
"func_signature">
4950 <primary>pg_stat_get_backend_subxact
</primary>
4952 <function>pg_stat_get_backend_subxact
</function> (
<type>integer
</type> )
4953 <returnvalue>record
</returnvalue>
4956 Returns a record of information about the subtransactions of the
4957 backend with the specified ID.
4958 The fields returned are
<parameter>subxact_count
</parameter>, which
4959 is the number of subtransactions in the backend's subtransaction cache,
4960 and
<parameter>subxact_overflow
</parameter>, which indicates whether
4961 the backend's subtransaction cache is overflowed or not.
4966 <entry role=
"func_table_entry"><para role=
"func_signature">
4968 <primary>pg_stat_get_backend_userid
</primary>
4970 <function>pg_stat_get_backend_userid
</function> (
<type>integer
</type> )
4971 <returnvalue>oid
</returnvalue>
4974 Returns the OID of the user logged into this backend.
4979 <entry role=
"func_table_entry"><para role=
"func_signature">
4981 <primary>pg_stat_get_backend_wait_event
</primary>
4983 <function>pg_stat_get_backend_wait_event
</function> (
<type>integer
</type> )
4984 <returnvalue>text
</returnvalue>
4987 Returns the wait event name if this backend is currently waiting,
4988 otherwise NULL. See
<xref linkend=
"wait-event-activity-table"/> through
4989 <xref linkend=
"wait-event-timeout-table"/>.
4994 <entry role=
"func_table_entry"><para role=
"func_signature">
4996 <primary>pg_stat_get_backend_wait_event_type
</primary>
4998 <function>pg_stat_get_backend_wait_event_type
</function> (
<type>integer
</type> )
4999 <returnvalue>text
</returnvalue>
5002 Returns the wait event type name if this backend is currently waiting,
5003 otherwise NULL. See
<xref linkend=
"wait-event-table"/> for details.
5008 <entry role=
"func_table_entry"><para role=
"func_signature">
5010 <primary>pg_stat_get_backend_xact_start
</primary>
5012 <function>pg_stat_get_backend_xact_start
</function> (
<type>integer
</type> )
5013 <returnvalue>timestamp with time zone
</returnvalue>
5016 Returns the time when the backend's current transaction was started.
5026 <sect1 id=
"monitoring-locks">
5027 <title>Viewing Locks
</title>
5029 <indexterm zone=
"monitoring-locks">
5030 <primary>lock
</primary>
5031 <secondary>monitoring
</secondary>
5035 Another useful tool for monitoring database activity is the
5036 <structname>pg_locks
</structname> system table. It allows the
5037 database administrator to view information about the outstanding
5038 locks in the lock manager. For example, this capability can be used
5044 View all the locks currently outstanding, all the locks on
5045 relations in a particular database, all the locks on a
5046 particular relation, or all the locks held by a particular
5047 <productname>PostgreSQL
</productname> session.
5053 Determine the relation in the current database with the most
5054 ungranted locks (which might be a source of contention among
5061 Determine the effect of lock contention on overall database
5062 performance, as well as the extent to which contention varies
5063 with overall database traffic.
5068 Details of the
<structname>pg_locks
</structname> view appear in
5069 <xref linkend=
"view-pg-locks"/>.
5070 For more information on locking and managing concurrency with
5071 <productname>PostgreSQL
</productname>, refer to
<xref linkend=
"mvcc"/>.
5075 <sect1 id=
"progress-reporting">
5076 <title>Progress Reporting
</title>
5079 <productname>PostgreSQL
</productname> has the ability to report the progress of
5080 certain commands during command execution. Currently, the only commands
5081 which support progress reporting are
<command>ANALYZE
</command>,
5082 <command>CLUSTER
</command>,
5083 <command>CREATE INDEX
</command>,
<command>VACUUM
</command>,
5084 <command>COPY
</command>,
5085 and
<xref linkend=
"protocol-replication-base-backup"/> (i.e., replication
5086 command that
<xref linkend=
"app-pgbasebackup"/> issues to take
5088 This may be expanded in the future.
5091 <sect2 id=
"analyze-progress-reporting">
5092 <title>ANALYZE Progress Reporting
</title>
5095 <primary>pg_stat_progress_analyze
</primary>
5099 Whenever
<command>ANALYZE
</command> is running, the
5100 <structname>pg_stat_progress_analyze
</structname> view will contain a
5101 row for each backend that is currently running that command. The tables
5102 below describe the information that will be reported and provide
5103 information about how to interpret it.
5106 <table id=
"pg-stat-progress-analyze-view" xreflabel=
"pg_stat_progress_analyze">
5107 <title><structname>pg_stat_progress_analyze
</structname> View
</title>
5111 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5122 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5123 <structfield>pid
</structfield> <type>integer
</type>
5126 Process ID of backend.
5131 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5132 <structfield>datid
</structfield> <type>oid
</type>
5135 OID of the database to which this backend is connected.
5140 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5141 <structfield>datname
</structfield> <type>name
</type>
5144 Name of the database to which this backend is connected.
5149 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5150 <structfield>relid
</structfield> <type>oid
</type>
5153 OID of the table being analyzed.
5158 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5159 <structfield>phase
</structfield> <type>text
</type>
5162 Current processing phase. See
<xref linkend=
"analyze-phases"/>.
5167 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5168 <structfield>sample_blks_total
</structfield> <type>bigint
</type>
5171 Total number of heap blocks that will be sampled.
5176 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5177 <structfield>sample_blks_scanned
</structfield> <type>bigint
</type>
5180 Number of heap blocks scanned.
5185 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5186 <structfield>ext_stats_total
</structfield> <type>bigint
</type>
5189 Number of extended statistics.
5194 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5195 <structfield>ext_stats_computed
</structfield> <type>bigint
</type>
5198 Number of extended statistics computed. This counter only advances
5199 when the phase is
<literal>computing extended statistics
</literal>.
5204 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5205 <structfield>child_tables_total
</structfield> <type>bigint
</type>
5208 Number of child tables.
5213 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5214 <structfield>child_tables_done
</structfield> <type>bigint
</type>
5217 Number of child tables scanned. This counter only advances when the
5218 phase is
<literal>acquiring inherited sample rows
</literal>.
5223 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5224 <structfield>current_child_table_relid
</structfield> <type>oid
</type>
5227 OID of the child table currently being scanned. This field is
5228 only valid when the phase is
5229 <literal>acquiring inherited sample rows
</literal>.
5236 <table id=
"analyze-phases">
5237 <title>ANALYZE Phases
</title>
5239 <colspec colname=
"col1" colwidth=
"1*"/>
5240 <colspec colname=
"col2" colwidth=
"2*"/>
5243 <entry>Phase
</entry>
5244 <entry>Description
</entry>
5249 <entry><literal>initializing
</literal></entry>
5251 The command is preparing to begin scanning the heap. This phase is
5252 expected to be very brief.
5256 <entry><literal>acquiring sample rows
</literal></entry>
5258 The command is currently scanning the table given by
5259 <structfield>relid
</structfield> to obtain sample rows.
5263 <entry><literal>acquiring inherited sample rows
</literal></entry>
5265 The command is currently scanning child tables to obtain sample rows.
5266 Columns
<structfield>child_tables_total
</structfield>,
5267 <structfield>child_tables_done
</structfield>, and
5268 <structfield>current_child_table_relid
</structfield> contain the
5269 progress information for this phase.
5273 <entry><literal>computing statistics
</literal></entry>
5275 The command is computing statistics from the sample rows obtained
5276 during the table scan.
5280 <entry><literal>computing extended statistics
</literal></entry>
5282 The command is computing extended statistics from the sample rows
5283 obtained during the table scan.
5287 <entry><literal>finalizing analyze
</literal></entry>
5289 The command is updating
<structname>pg_class
</structname>. When this
5290 phase is completed,
<command>ANALYZE
</command> will end.
5299 Note that when
<command>ANALYZE
</command> is run on a partitioned table,
5300 all of its partitions are also recursively analyzed.
5301 In that case,
<command>ANALYZE
</command>
5302 progress is reported first for the parent table, whereby its inheritance
5303 statistics are collected, followed by that for each partition.
5308 <sect2 id=
"cluster-progress-reporting">
5309 <title>CLUSTER Progress Reporting
</title>
5312 <primary>pg_stat_progress_cluster
</primary>
5316 Whenever
<command>CLUSTER
</command> or
<command>VACUUM FULL
</command> is
5317 running, the
<structname>pg_stat_progress_cluster
</structname> view will
5318 contain a row for each backend that is currently running either command.
5319 The tables below describe the information that will be reported and
5320 provide information about how to interpret it.
5323 <table id=
"pg-stat-progress-cluster-view" xreflabel=
"pg_stat_progress_cluster">
5324 <title><structname>pg_stat_progress_cluster
</structname> View
</title>
5328 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5339 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5340 <structfield>pid
</structfield> <type>integer
</type>
5343 Process ID of backend.
5348 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5349 <structfield>datid
</structfield> <type>oid
</type>
5352 OID of the database to which this backend is connected.
5357 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5358 <structfield>datname
</structfield> <type>name
</type>
5361 Name of the database to which this backend is connected.
5366 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5367 <structfield>relid
</structfield> <type>oid
</type>
5370 OID of the table being clustered.
5375 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5376 <structfield>command
</structfield> <type>text
</type>
5379 The command that is running. Either
<literal>CLUSTER
</literal> or
<literal>VACUUM FULL
</literal>.
5384 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5385 <structfield>phase
</structfield> <type>text
</type>
5388 Current processing phase. See
<xref linkend=
"cluster-phases"/>.
5393 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5394 <structfield>cluster_index_relid
</structfield> <type>oid
</type>
5397 If the table is being scanned using an index, this is the OID of the
5398 index being used; otherwise, it is zero.
5403 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5404 <structfield>heap_tuples_scanned
</structfield> <type>bigint
</type>
5407 Number of heap tuples scanned.
5408 This counter only advances when the phase is
5409 <literal>seq scanning heap
</literal>,
5410 <literal>index scanning heap
</literal>
5411 or
<literal>writing new heap
</literal>.
5416 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5417 <structfield>heap_tuples_written
</structfield> <type>bigint
</type>
5420 Number of heap tuples written.
5421 This counter only advances when the phase is
5422 <literal>seq scanning heap
</literal>,
5423 <literal>index scanning heap
</literal>
5424 or
<literal>writing new heap
</literal>.
5429 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5430 <structfield>heap_blks_total
</structfield> <type>bigint
</type>
5433 Total number of heap blocks in the table. This number is reported
5434 as of the beginning of
<literal>seq scanning heap
</literal>.
5439 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5440 <structfield>heap_blks_scanned
</structfield> <type>bigint
</type>
5443 Number of heap blocks scanned. This counter only advances when the
5444 phase is
<literal>seq scanning heap
</literal>.
5449 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5450 <structfield>index_rebuild_count
</structfield> <type>bigint
</type>
5453 Number of indexes rebuilt. This counter only advances when the phase
5454 is
<literal>rebuilding index
</literal>.
5461 <table id=
"cluster-phases">
5462 <title>CLUSTER and VACUUM FULL Phases
</title>
5464 <colspec colname=
"col1" colwidth=
"1*"/>
5465 <colspec colname=
"col2" colwidth=
"2*"/>
5468 <entry>Phase
</entry>
5469 <entry>Description
</entry>
5475 <entry><literal>initializing
</literal></entry>
5477 The command is preparing to begin scanning the heap. This phase is
5478 expected to be very brief.
5482 <entry><literal>seq scanning heap
</literal></entry>
5484 The command is currently scanning the table using a sequential scan.
5488 <entry><literal>index scanning heap
</literal></entry>
5490 <command>CLUSTER
</command> is currently scanning the table using an index scan.
5494 <entry><literal>sorting tuples
</literal></entry>
5496 <command>CLUSTER
</command> is currently sorting tuples.
5500 <entry><literal>writing new heap
</literal></entry>
5502 <command>CLUSTER
</command> is currently writing the new heap.
5506 <entry><literal>swapping relation files
</literal></entry>
5508 The command is currently swapping newly-built files into place.
5512 <entry><literal>rebuilding index
</literal></entry>
5514 The command is currently rebuilding an index.
5518 <entry><literal>performing final cleanup
</literal></entry>
5520 The command is performing final cleanup. When this phase is
5521 completed,
<command>CLUSTER
</command>
5522 or
<command>VACUUM FULL
</command> will end.
5530 <sect2 id=
"copy-progress-reporting">
5531 <title>COPY Progress Reporting
</title>
5534 <primary>pg_stat_progress_copy
</primary>
5538 Whenever
<command>COPY
</command> is running, the
5539 <structname>pg_stat_progress_copy
</structname> view will contain one row
5540 for each backend that is currently running a
<command>COPY
</command> command.
5541 The table below describes the information that will be reported and provides
5542 information about how to interpret it.
5545 <table id=
"pg-stat-progress-copy-view" xreflabel=
"pg_stat_progress_copy">
5546 <title><structname>pg_stat_progress_copy
</structname> View
</title>
5550 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5561 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5562 <structfield>pid
</structfield> <type>integer
</type>
5565 Process ID of backend.
5570 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5571 <structfield>datid
</structfield> <type>oid
</type>
5574 OID of the database to which this backend is connected.
5579 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5580 <structfield>datname
</structfield> <type>name
</type>
5583 Name of the database to which this backend is connected.
5588 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5589 <structfield>relid
</structfield> <type>oid
</type>
5592 OID of the table on which the
<command>COPY
</command> command is
5593 executed. It is set to
<literal>0</literal> if copying from a
5594 <command>SELECT
</command> query.
5599 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5600 <structfield>command
</structfield> <type>text
</type>
5603 The command that is running:
<literal>COPY FROM
</literal>, or
5604 <literal>COPY TO
</literal>.
5609 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5610 <structfield>type
</structfield> <type>text
</type>
5613 The io type that the data is read from or written to:
5614 <literal>FILE
</literal>,
<literal>PROGRAM
</literal>,
5615 <literal>PIPE
</literal> (for
<command>COPY FROM STDIN
</command> and
5616 <command>COPY TO STDOUT
</command>), or
<literal>CALLBACK
</literal>
5617 (used for example during the initial table synchronization in
5618 logical replication).
5623 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5624 <structfield>bytes_processed
</structfield> <type>bigint
</type>
5627 Number of bytes already processed by
<command>COPY
</command> command.
5632 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5633 <structfield>bytes_total
</structfield> <type>bigint
</type>
5636 Size of source file for
<command>COPY FROM
</command> command in bytes.
5637 It is set to
<literal>0</literal> if not available.
5642 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5643 <structfield>tuples_processed
</structfield> <type>bigint
</type>
5646 Number of tuples already processed by
<command>COPY
</command> command.
5651 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5652 <structfield>tuples_excluded
</structfield> <type>bigint
</type>
5655 Number of tuples not processed because they were excluded by the
5656 <command>WHERE
</command> clause of the
<command>COPY
</command> command.
5664 <sect2 id=
"create-index-progress-reporting">
5665 <title>CREATE INDEX Progress Reporting
</title>
5668 <primary>pg_stat_progress_create_index
</primary>
5672 Whenever
<command>CREATE INDEX
</command> or
<command>REINDEX
</command> is running, the
5673 <structname>pg_stat_progress_create_index
</structname> view will contain
5674 one row for each backend that is currently creating indexes. The tables
5675 below describe the information that will be reported and provide information
5676 about how to interpret it.
5679 <table id=
"pg-stat-progress-create-index-view" xreflabel=
"pg_stat_progress_create_index">
5680 <title><structname>pg_stat_progress_create_index
</structname> View
</title>
5684 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5695 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5696 <structfield>pid
</structfield> <type>integer
</type>
5699 Process ID of the backend creating indexes.
5704 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5705 <structfield>datid
</structfield> <type>oid
</type>
5708 OID of the database to which this backend is connected.
5713 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5714 <structfield>datname
</structfield> <type>name
</type>
5717 Name of the database to which this backend is connected.
5722 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5723 <structfield>relid
</structfield> <type>oid
</type>
5726 OID of the table on which the index is being created.
5731 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5732 <structfield>index_relid
</structfield> <type>oid
</type>
5735 OID of the index being created or reindexed. During a
5736 non-concurrent
<command>CREATE INDEX
</command>, this is
0.
5741 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5742 <structfield>command
</structfield> <type>text
</type>
5745 Specific command type:
<literal>CREATE INDEX
</literal>,
5746 <literal>CREATE INDEX CONCURRENTLY
</literal>,
5747 <literal>REINDEX
</literal>, or
<literal>REINDEX CONCURRENTLY
</literal>.
5752 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5753 <structfield>phase
</structfield> <type>text
</type>
5756 Current processing phase of index creation. See
<xref linkend=
"create-index-phases"/>.
5761 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5762 <structfield>lockers_total
</structfield> <type>bigint
</type>
5765 Total number of lockers to wait for, when applicable.
5770 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5771 <structfield>lockers_done
</structfield> <type>bigint
</type>
5774 Number of lockers already waited for.
5779 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5780 <structfield>current_locker_pid
</structfield> <type>bigint
</type>
5783 Process ID of the locker currently being waited for.
5788 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5789 <structfield>blocks_total
</structfield> <type>bigint
</type>
5792 Total number of blocks to be processed in the current phase.
5797 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5798 <structfield>blocks_done
</structfield> <type>bigint
</type>
5801 Number of blocks already processed in the current phase.
5806 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5807 <structfield>tuples_total
</structfield> <type>bigint
</type>
5810 Total number of tuples to be processed in the current phase.
5815 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5816 <structfield>tuples_done
</structfield> <type>bigint
</type>
5819 Number of tuples already processed in the current phase.
5824 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5825 <structfield>partitions_total
</structfield> <type>bigint
</type>
5828 Total number of partitions on which the index is to be created
5829 or attached, including both direct and indirect partitions.
5830 <literal>0</literal> during a
<literal>REINDEX
</literal>, or when
5831 the index is not partitioned.
5836 <entry role=
"catalog_table_entry"><para role=
"column_definition">
5837 <structfield>partitions_done
</structfield> <type>bigint
</type>
5840 Number of partitions on which the index has already been created
5841 or attached, including both direct and indirect partitions.
5842 <literal>0</literal> during a
<literal>REINDEX
</literal>, or when
5843 the index is not partitioned.
5850 <table id=
"create-index-phases">
5851 <title>CREATE INDEX Phases
</title>
5853 <colspec colname=
"col1" colwidth=
"1*"/>
5854 <colspec colname=
"col2" colwidth=
"2*"/>
5857 <entry>Phase
</entry>
5858 <entry>Description
</entry>
5863 <entry><literal>initializing
</literal></entry>
5865 <command>CREATE INDEX
</command> or
<command>REINDEX
</command> is preparing to create the index. This
5866 phase is expected to be very brief.
5870 <entry><literal>waiting for writers before build
</literal></entry>
5872 <command>CREATE INDEX CONCURRENTLY
</command> or
<command>REINDEX CONCURRENTLY
</command> is waiting for transactions
5873 with write locks that can potentially see the table to finish.
5874 This phase is skipped when not in concurrent mode.
5875 Columns
<structname>lockers_total
</structname>,
<structname>lockers_done
</structname>
5876 and
<structname>current_locker_pid
</structname> contain the progress
5877 information for this phase.
5881 <entry><literal>building index
</literal></entry>
5883 The index is being built by the access method-specific code. In this phase,
5884 access methods that support progress reporting fill in their own progress data,
5885 and the subphase is indicated in this column. Typically,
5886 <structname>blocks_total
</structname> and
<structname>blocks_done
</structname>
5887 will contain progress data, as well as potentially
5888 <structname>tuples_total
</structname> and
<structname>tuples_done
</structname>.
5892 <entry><literal>waiting for writers before validation
</literal></entry>
5894 <command>CREATE INDEX CONCURRENTLY
</command> or
<command>REINDEX CONCURRENTLY
</command> is waiting for transactions
5895 with write locks that can potentially write into the table to finish.
5896 This phase is skipped when not in concurrent mode.
5897 Columns
<structname>lockers_total
</structname>,
<structname>lockers_done
</structname>
5898 and
<structname>current_locker_pid
</structname> contain the progress
5899 information for this phase.
5903 <entry><literal>index validation: scanning index
</literal></entry>
5905 <command>CREATE INDEX CONCURRENTLY
</command> is scanning the index searching
5906 for tuples that need to be validated.
5907 This phase is skipped when not in concurrent mode.
5908 Columns
<structname>blocks_total
</structname> (set to the total size of the index)
5909 and
<structname>blocks_done
</structname> contain the progress information for this phase.
5913 <entry><literal>index validation: sorting tuples
</literal></entry>
5915 <command>CREATE INDEX CONCURRENTLY
</command> is sorting the output of the
5916 index scanning phase.
5920 <entry><literal>index validation: scanning table
</literal></entry>
5922 <command>CREATE INDEX CONCURRENTLY
</command> is scanning the table
5923 to validate the index tuples collected in the previous two phases.
5924 This phase is skipped when not in concurrent mode.
5925 Columns
<structname>blocks_total
</structname> (set to the total size of the table)
5926 and
<structname>blocks_done
</structname> contain the progress information for this phase.
5930 <entry><literal>waiting for old snapshots
</literal></entry>
5932 <command>CREATE INDEX CONCURRENTLY
</command> or
<command>REINDEX CONCURRENTLY
</command> is waiting for transactions
5933 that can potentially see the table to release their snapshots. This
5934 phase is skipped when not in concurrent mode.
5935 Columns
<structname>lockers_total
</structname>,
<structname>lockers_done
</structname>
5936 and
<structname>current_locker_pid
</structname> contain the progress
5937 information for this phase.
5941 <entry><literal>waiting for readers before marking dead
</literal></entry>
5943 <command>REINDEX CONCURRENTLY
</command> is waiting for transactions
5944 with read locks on the table to finish, before marking the old index dead.
5945 This phase is skipped when not in concurrent mode.
5946 Columns
<structname>lockers_total
</structname>,
<structname>lockers_done
</structname>
5947 and
<structname>current_locker_pid
</structname> contain the progress
5948 information for this phase.
5952 <entry><literal>waiting for readers before dropping
</literal></entry>
5954 <command>REINDEX CONCURRENTLY
</command> is waiting for transactions
5955 with read locks on the table to finish, before dropping the old index.
5956 This phase is skipped when not in concurrent mode.
5957 Columns
<structname>lockers_total
</structname>,
<structname>lockers_done
</structname>
5958 and
<structname>current_locker_pid
</structname> contain the progress
5959 information for this phase.
5968 <sect2 id=
"vacuum-progress-reporting">
5969 <title>VACUUM Progress Reporting
</title>
5972 <primary>pg_stat_progress_vacuum
</primary>
5976 Whenever
<command>VACUUM
</command> is running, the
5977 <structname>pg_stat_progress_vacuum
</structname> view will contain
5978 one row for each backend (including autovacuum worker processes) that is
5979 currently vacuuming. The tables below describe the information
5980 that will be reported and provide information about how to interpret it.
5981 Progress for
<command>VACUUM FULL
</command> commands is reported via
5982 <structname>pg_stat_progress_cluster
</structname>
5983 because both
<command>VACUUM FULL
</command> and
<command>CLUSTER
</command>
5984 rewrite the table, while regular
<command>VACUUM
</command> only modifies it
5985 in place. See
<xref linkend=
"cluster-progress-reporting"/>.
5988 <table id=
"pg-stat-progress-vacuum-view" xreflabel=
"pg_stat_progress_vacuum">
5989 <title><structname>pg_stat_progress_vacuum
</structname> View
</title>
5993 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6004 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6005 <structfield>pid
</structfield> <type>integer
</type>
6008 Process ID of backend.
6013 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6014 <structfield>datid
</structfield> <type>oid
</type>
6017 OID of the database to which this backend is connected.
6022 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6023 <structfield>datname
</structfield> <type>name
</type>
6026 Name of the database to which this backend is connected.
6031 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6032 <structfield>relid
</structfield> <type>oid
</type>
6035 OID of the table being vacuumed.
6040 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6041 <structfield>phase
</structfield> <type>text
</type>
6044 Current processing phase of vacuum. See
<xref linkend=
"vacuum-phases"/>.
6049 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6050 <structfield>heap_blks_total
</structfield> <type>bigint
</type>
6053 Total number of heap blocks in the table. This number is reported
6054 as of the beginning of the scan; blocks added later will not be (and
6055 need not be) visited by this
<command>VACUUM
</command>.
6060 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6061 <structfield>heap_blks_scanned
</structfield> <type>bigint
</type>
6064 Number of heap blocks scanned. Because the
6065 <link linkend=
"storage-vm">visibility map
</link> is used to optimize scans,
6066 some blocks will be skipped without inspection; skipped blocks are
6067 included in this total, so that this number will eventually become
6068 equal to
<structfield>heap_blks_total
</structfield> when the vacuum is complete.
6069 This counter only advances when the phase is
<literal>scanning heap
</literal>.
6074 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6075 <structfield>heap_blks_vacuumed
</structfield> <type>bigint
</type>
6078 Number of heap blocks vacuumed. Unless the table has no indexes, this
6079 counter only advances when the phase is
<literal>vacuuming heap
</literal>.
6080 Blocks that contain no dead tuples are skipped, so the counter may
6081 sometimes skip forward in large increments.
6086 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6087 <structfield>index_vacuum_count
</structfield> <type>bigint
</type>
6090 Number of completed index vacuum cycles.
6095 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6096 <structfield>max_dead_tuples
</structfield> <type>bigint
</type>
6099 Number of dead tuples that we can store before needing to perform
6100 an index vacuum cycle, based on
6101 <xref linkend=
"guc-maintenance-work-mem"/>.
6106 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6107 <structfield>num_dead_tuples
</structfield> <type>bigint
</type>
6110 Number of dead tuples collected since the last index vacuum cycle.
6117 <table id=
"vacuum-phases">
6118 <title>VACUUM Phases
</title>
6120 <colspec colname=
"col1" colwidth=
"1*"/>
6121 <colspec colname=
"col2" colwidth=
"2*"/>
6124 <entry>Phase
</entry>
6125 <entry>Description
</entry>
6131 <entry><literal>initializing
</literal></entry>
6133 <command>VACUUM
</command> is preparing to begin scanning the heap. This
6134 phase is expected to be very brief.
6138 <entry><literal>scanning heap
</literal></entry>
6140 <command>VACUUM
</command> is currently scanning the heap. It will prune and
6141 defragment each page if required, and possibly perform freezing
6142 activity. The
<structfield>heap_blks_scanned
</structfield> column can be used
6143 to monitor the progress of the scan.
6147 <entry><literal>vacuuming indexes
</literal></entry>
6149 <command>VACUUM
</command> is currently vacuuming the indexes. If a table has
6150 any indexes, this will happen at least once per vacuum, after the heap
6151 has been completely scanned. It may happen multiple times per vacuum
6152 if
<xref linkend=
"guc-maintenance-work-mem"/> (or, in the case of autovacuum,
6153 <xref linkend=
"guc-autovacuum-work-mem"/> if set) is insufficient to store
6154 the number of dead tuples found.
6158 <entry><literal>vacuuming heap
</literal></entry>
6160 <command>VACUUM
</command> is currently vacuuming the heap. Vacuuming the heap
6161 is distinct from scanning the heap, and occurs after each instance of
6162 vacuuming indexes. If
<structfield>heap_blks_scanned
</structfield> is less than
6163 <structfield>heap_blks_total
</structfield>, the system will return to scanning
6164 the heap after this phase is completed; otherwise, it will begin
6165 cleaning up indexes after this phase is completed.
6169 <entry><literal>cleaning up indexes
</literal></entry>
6171 <command>VACUUM
</command> is currently cleaning up indexes. This occurs after
6172 the heap has been completely scanned and all vacuuming of the indexes
6173 and the heap has been completed.
6177 <entry><literal>truncating heap
</literal></entry>
6179 <command>VACUUM
</command> is currently truncating the heap so as to return
6180 empty pages at the end of the relation to the operating system. This
6181 occurs after cleaning up indexes.
6185 <entry><literal>performing final cleanup
</literal></entry>
6187 <command>VACUUM
</command> is performing final cleanup. During this phase,
6188 <command>VACUUM
</command> will vacuum the free space map, update statistics
6189 in
<literal>pg_class
</literal>, and report statistics to the cumulative
6190 statistics system. When this phase is completed,
<command>VACUUM
</command> will end.
6198 <sect2 id=
"basebackup-progress-reporting">
6199 <title>Base Backup Progress Reporting
</title>
6202 <primary>pg_stat_progress_basebackup
</primary>
6206 Whenever an application like
<application>pg_basebackup
</application>
6207 is taking a base backup, the
6208 <structname>pg_stat_progress_basebackup
</structname>
6209 view will contain a row for each WAL sender process that is currently
6210 running the
<command>BASE_BACKUP
</command> replication command
6211 and streaming the backup. The tables below describe the information
6212 that will be reported and provide information about how to interpret it.
6215 <table id=
"pg-stat-progress-basebackup-view" xreflabel=
"pg_stat_progress_basebackup">
6216 <title><structname>pg_stat_progress_basebackup
</structname> View
</title>
6220 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6231 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6232 <structfield>pid
</structfield> <type>integer
</type>
6235 Process ID of a WAL sender process.
6240 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6241 <structfield>phase
</structfield> <type>text
</type>
6244 Current processing phase. See
<xref linkend=
"basebackup-phases"/>.
6249 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6250 <structfield>backup_total
</structfield> <type>bigint
</type>
6253 Total amount of data that will be streamed. This is estimated and
6254 reported as of the beginning of
6255 <literal>streaming database files
</literal> phase. Note that
6256 this is only an approximation since the database
6257 may change during
<literal>streaming database files
</literal> phase
6258 and WAL log may be included in the backup later. This is always
6259 the same value as
<structfield>backup_streamed
</structfield>
6260 once the amount of data streamed exceeds the estimated
6261 total size. If the estimation is disabled in
6262 <application>pg_basebackup
</application>
6263 (i.e.,
<literal>--no-estimate-size
</literal> option is specified),
6264 this is
<literal>NULL
</literal>.
6269 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6270 <structfield>backup_streamed
</structfield> <type>bigint
</type>
6273 Amount of data streamed. This counter only advances
6274 when the phase is
<literal>streaming database files
</literal> or
6275 <literal>transferring wal files
</literal>.
6280 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6281 <structfield>tablespaces_total
</structfield> <type>bigint
</type>
6284 Total number of tablespaces that will be streamed.
6289 <entry role=
"catalog_table_entry"><para role=
"column_definition">
6290 <structfield>tablespaces_streamed
</structfield> <type>bigint
</type>
6293 Number of tablespaces streamed. This counter only
6294 advances when the phase is
<literal>streaming database files
</literal>.
6301 <table id=
"basebackup-phases">
6302 <title>Base Backup Phases
</title>
6304 <colspec colname=
"col1" colwidth=
"1*"/>
6305 <colspec colname=
"col2" colwidth=
"2*"/>
6308 <entry>Phase
</entry>
6309 <entry>Description
</entry>
6314 <entry><literal>initializing
</literal></entry>
6316 The WAL sender process is preparing to begin the backup.
6317 This phase is expected to be very brief.
6321 <entry><literal>waiting for checkpoint to finish
</literal></entry>
6323 The WAL sender process is currently performing
6324 <function>pg_backup_start
</function> to prepare to
6325 take a base backup, and waiting for the start-of-backup
6326 checkpoint to finish.
6330 <entry><literal>estimating backup size
</literal></entry>
6332 The WAL sender process is currently estimating the total amount
6333 of database files that will be streamed as a base backup.
6337 <entry><literal>streaming database files
</literal></entry>
6339 The WAL sender process is currently streaming database files
6344 <entry><literal>waiting for wal archiving to finish
</literal></entry>
6346 The WAL sender process is currently performing
6347 <function>pg_backup_stop
</function> to finish the backup,
6348 and waiting for all the WAL files required for the base backup
6349 to be successfully archived.
6350 If either
<literal>--wal-method=none
</literal> or
6351 <literal>--wal-method=stream
</literal> is specified in
6352 <application>pg_basebackup
</application>, the backup will end
6353 when this phase is completed.
6357 <entry><literal>transferring wal files
</literal></entry>
6359 The WAL sender process is currently transferring all WAL logs
6360 generated during the backup. This phase occurs after
6361 <literal>waiting for wal archiving to finish
</literal> phase if
6362 <literal>--wal-method=fetch
</literal> is specified in
6363 <application>pg_basebackup
</application>. The backup will end
6364 when this phase is completed.
6375 <sect1 id=
"dynamic-trace">
6376 <title>Dynamic Tracing
</title>
6378 <indexterm zone=
"dynamic-trace">
6379 <primary>DTrace
</primary>
6383 <productname>PostgreSQL
</productname> provides facilities to support
6384 dynamic tracing of the database server. This allows an external
6385 utility to be called at specific points in the code and thereby trace
6390 A number of probes or trace points are already inserted into the source
6391 code. These probes are intended to be used by database developers and
6392 administrators. By default the probes are not compiled into
6393 <productname>PostgreSQL
</productname>; the user needs to explicitly tell
6394 the configure script to make the probes available.
6399 <ulink url=
"https://en.wikipedia.org/wiki/DTrace">DTrace
</ulink>
6400 utility is supported, which, at the time of this writing, is available
6401 on Solaris, macOS, FreeBSD, NetBSD, and Oracle Linux. The
6402 <ulink url=
"https://sourceware.org/systemtap/">SystemTap
</ulink> project
6403 for Linux provides a DTrace equivalent and can also be used. Supporting other dynamic
6404 tracing utilities is theoretically possible by changing the definitions for
6405 the macros in
<filename>src/include/utils/probes.h
</filename>.
6408 <sect2 id=
"compiling-for-trace">
6409 <title>Compiling for Dynamic Tracing
</title>
6412 By default, probes are not available, so you will need to
6413 explicitly tell the configure script to make the probes available
6414 in
<productname>PostgreSQL
</productname>. To include DTrace support
6415 specify
<option>--enable-dtrace
</option> to configure. See
<xref
6416 linkend=
"configure-options-devel"/> for further information.
6420 <sect2 id=
"trace-points">
6421 <title>Built-in Probes
</title>
6424 A number of standard probes are provided in the source code,
6425 as shown in
<xref linkend=
"dtrace-probe-point-table"/>;
6426 <xref linkend=
"typedefs-table"/>
6427 shows the types used in the probes. More probes can certainly be
6428 added to enhance
<productname>PostgreSQL
</productname>'s observability.
6431 <table id=
"dtrace-probe-point-table">
6432 <title>Built-in DTrace Probes
</title>
6434 <colspec colname=
"col1" colwidth=
"2*"/>
6435 <colspec colname=
"col2" colwidth=
"3*"/>
6436 <colspec colname=
"col3" colwidth=
"3*"/>
6440 <entry>Parameters
</entry>
6441 <entry>Description
</entry>
6448 <entry><literal>transaction-start
</literal></entry>
6449 <entry><literal>(LocalTransactionId)
</literal></entry>
6450 <entry>Probe that fires at the start of a new transaction.
6451 arg0 is the transaction ID.
</entry>
6454 <entry><literal>transaction-commit
</literal></entry>
6455 <entry><literal>(LocalTransactionId)
</literal></entry>
6456 <entry>Probe that fires when a transaction completes successfully.
6457 arg0 is the transaction ID.
</entry>
6460 <entry><literal>transaction-abort
</literal></entry>
6461 <entry><literal>(LocalTransactionId)
</literal></entry>
6462 <entry>Probe that fires when a transaction completes unsuccessfully.
6463 arg0 is the transaction ID.
</entry>
6466 <entry><literal>query-start
</literal></entry>
6467 <entry><literal>(const char *)
</literal></entry>
6468 <entry>Probe that fires when the processing of a query is started.
6469 arg0 is the query string.
</entry>
6472 <entry><literal>query-done
</literal></entry>
6473 <entry><literal>(const char *)
</literal></entry>
6474 <entry>Probe that fires when the processing of a query is complete.
6475 arg0 is the query string.
</entry>
6478 <entry><literal>query-parse-start
</literal></entry>
6479 <entry><literal>(const char *)
</literal></entry>
6480 <entry>Probe that fires when the parsing of a query is started.
6481 arg0 is the query string.
</entry>
6484 <entry><literal>query-parse-done
</literal></entry>
6485 <entry><literal>(const char *)
</literal></entry>
6486 <entry>Probe that fires when the parsing of a query is complete.
6487 arg0 is the query string.
</entry>
6490 <entry><literal>query-rewrite-start
</literal></entry>
6491 <entry><literal>(const char *)
</literal></entry>
6492 <entry>Probe that fires when the rewriting of a query is started.
6493 arg0 is the query string.
</entry>
6496 <entry><literal>query-rewrite-done
</literal></entry>
6497 <entry><literal>(const char *)
</literal></entry>
6498 <entry>Probe that fires when the rewriting of a query is complete.
6499 arg0 is the query string.
</entry>
6502 <entry><literal>query-plan-start
</literal></entry>
6503 <entry><literal>()
</literal></entry>
6504 <entry>Probe that fires when the planning of a query is started.
</entry>
6507 <entry><literal>query-plan-done
</literal></entry>
6508 <entry><literal>()
</literal></entry>
6509 <entry>Probe that fires when the planning of a query is complete.
</entry>
6512 <entry><literal>query-execute-start
</literal></entry>
6513 <entry><literal>()
</literal></entry>
6514 <entry>Probe that fires when the execution of a query is started.
</entry>
6517 <entry><literal>query-execute-done
</literal></entry>
6518 <entry><literal>()
</literal></entry>
6519 <entry>Probe that fires when the execution of a query is complete.
</entry>
6522 <entry><literal>statement-status
</literal></entry>
6523 <entry><literal>(const char *)
</literal></entry>
6524 <entry>Probe that fires anytime the server process updates its
6525 <structname>pg_stat_activity
</structname>.
<structfield>status
</structfield>.
6526 arg0 is the new status string.
</entry>
6529 <entry><literal>checkpoint-start
</literal></entry>
6530 <entry><literal>(int)
</literal></entry>
6531 <entry>Probe that fires when a checkpoint is started.
6532 arg0 holds the bitwise flags used to distinguish different checkpoint
6533 types, such as shutdown, immediate or force.
</entry>
6536 <entry><literal>checkpoint-done
</literal></entry>
6537 <entry><literal>(int, int, int, int, int)
</literal></entry>
6538 <entry>Probe that fires when a checkpoint is complete.
6539 (The probes listed next fire in sequence during checkpoint processing.)
6540 arg0 is the number of buffers written. arg1 is the total number of
6541 buffers. arg2, arg3 and arg4 contain the number of WAL files added,
6542 removed and recycled respectively.
</entry>
6545 <entry><literal>clog-checkpoint-start
</literal></entry>
6546 <entry><literal>(bool)
</literal></entry>
6547 <entry>Probe that fires when the CLOG portion of a checkpoint is started.
6548 arg0 is true for normal checkpoint, false for shutdown
6552 <entry><literal>clog-checkpoint-done
</literal></entry>
6553 <entry><literal>(bool)
</literal></entry>
6554 <entry>Probe that fires when the CLOG portion of a checkpoint is
6555 complete. arg0 has the same meaning as for
<literal>clog-checkpoint-start
</literal>.
</entry>
6558 <entry><literal>subtrans-checkpoint-start
</literal></entry>
6559 <entry><literal>(bool)
</literal></entry>
6560 <entry>Probe that fires when the SUBTRANS portion of a checkpoint is
6562 arg0 is true for normal checkpoint, false for shutdown
6566 <entry><literal>subtrans-checkpoint-done
</literal></entry>
6567 <entry><literal>(bool)
</literal></entry>
6568 <entry>Probe that fires when the SUBTRANS portion of a checkpoint is
6569 complete. arg0 has the same meaning as for
6570 <literal>subtrans-checkpoint-start
</literal>.
</entry>
6573 <entry><literal>multixact-checkpoint-start
</literal></entry>
6574 <entry><literal>(bool)
</literal></entry>
6575 <entry>Probe that fires when the MultiXact portion of a checkpoint is
6577 arg0 is true for normal checkpoint, false for shutdown
6581 <entry><literal>multixact-checkpoint-done
</literal></entry>
6582 <entry><literal>(bool)
</literal></entry>
6583 <entry>Probe that fires when the MultiXact portion of a checkpoint is
6584 complete. arg0 has the same meaning as for
6585 <literal>multixact-checkpoint-start
</literal>.
</entry>
6588 <entry><literal>buffer-checkpoint-start
</literal></entry>
6589 <entry><literal>(int)
</literal></entry>
6590 <entry>Probe that fires when the buffer-writing portion of a checkpoint
6592 arg0 holds the bitwise flags used to distinguish different checkpoint
6593 types, such as shutdown, immediate or force.
</entry>
6596 <entry><literal>buffer-sync-start
</literal></entry>
6597 <entry><literal>(int, int)
</literal></entry>
6598 <entry>Probe that fires when we begin to write dirty buffers during
6599 checkpoint (after identifying which buffers must be written).
6600 arg0 is the total number of buffers.
6601 arg1 is the number that are currently dirty and need to be written.
</entry>
6604 <entry><literal>buffer-sync-written
</literal></entry>
6605 <entry><literal>(int)
</literal></entry>
6606 <entry>Probe that fires after each buffer is written during checkpoint.
6607 arg0 is the ID number of the buffer.
</entry>
6610 <entry><literal>buffer-sync-done
</literal></entry>
6611 <entry><literal>(int, int, int)
</literal></entry>
6612 <entry>Probe that fires when all dirty buffers have been written.
6613 arg0 is the total number of buffers.
6614 arg1 is the number of buffers actually written by the checkpoint process.
6615 arg2 is the number that were expected to be written (arg1 of
6616 <literal>buffer-sync-start
</literal>); any difference reflects other processes flushing
6617 buffers during the checkpoint.
</entry>
6620 <entry><literal>buffer-checkpoint-sync-start
</literal></entry>
6621 <entry><literal>()
</literal></entry>
6622 <entry>Probe that fires after dirty buffers have been written to the
6623 kernel, and before starting to issue fsync requests.
</entry>
6626 <entry><literal>buffer-checkpoint-done
</literal></entry>
6627 <entry><literal>()
</literal></entry>
6628 <entry>Probe that fires when syncing of buffers to disk is
6632 <entry><literal>twophase-checkpoint-start
</literal></entry>
6633 <entry><literal>()
</literal></entry>
6634 <entry>Probe that fires when the two-phase portion of a checkpoint is
6638 <entry><literal>twophase-checkpoint-done
</literal></entry>
6639 <entry><literal>()
</literal></entry>
6640 <entry>Probe that fires when the two-phase portion of a checkpoint is
6644 <entry><literal>buffer-extend-start
</literal></entry>
6645 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, unsigned int)
</literal></entry>
6646 <entry>Probe that fires when a relation extension starts.
6647 arg0 contains the fork to be extended. arg1, arg2, and arg3 contain the
6648 tablespace, database, and relation OIDs identifying the relation. arg4
6649 is the ID of the backend which created the temporary relation for a
6650 local buffer, or
<symbol>InvalidBackendId
</symbol> (-
1) for a shared
6651 buffer. arg5 is the number of blocks the caller would like to extend
6655 <entry><literal>buffer-extend-done
</literal></entry>
6656 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, unsigned int, BlockNumber)
</literal></entry>
6657 <entry>Probe that fires when a relation extension is complete.
6658 arg0 contains the fork to be extended. arg1, arg2, and arg3 contain the
6659 tablespace, database, and relation OIDs identifying the relation. arg4
6660 is the ID of the backend which created the temporary relation for a
6661 local buffer, or
<symbol>InvalidBackendId
</symbol> (-
1) for a shared
6662 buffer. arg5 is the number of blocks the relation was extended by, this
6663 can be less than the number in the
6664 <literal>buffer-extend-start
</literal> due to resource
6665 constraints. arg6 contains the BlockNumber of the first new
6669 <entry><literal>buffer-read-start
</literal></entry>
6670 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int)
</literal></entry>
6671 <entry>Probe that fires when a buffer read is started.
6672 arg0 and arg1 contain the fork and block numbers of the page.
6673 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
6674 identifying the relation.
6675 arg5 is the ID of the backend which created the temporary relation for a
6676 local buffer, or
<symbol>InvalidBackendId
</symbol> (-
1) for a shared buffer.
6680 <entry><literal>buffer-read-done
</literal></entry>
6681 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, bool)
</literal></entry>
6682 <entry>Probe that fires when a buffer read is complete.
6683 arg0 and arg1 contain the fork and block numbers of the page.
6684 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
6685 identifying the relation.
6686 arg5 is the ID of the backend which created the temporary relation for a
6687 local buffer, or
<symbol>InvalidBackendId
</symbol> (-
1) for a shared buffer.
6688 arg6 is true if the buffer was found in the pool, false if not.
</entry>
6691 <entry><literal>buffer-flush-start
</literal></entry>
6692 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid)
</literal></entry>
6693 <entry>Probe that fires before issuing any write request for a shared
6695 arg0 and arg1 contain the fork and block numbers of the page.
6696 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
6697 identifying the relation.
</entry>
6700 <entry><literal>buffer-flush-done
</literal></entry>
6701 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid)
</literal></entry>
6702 <entry>Probe that fires when a write request is complete. (Note
6703 that this just reflects the time to pass the data to the kernel;
6704 it's typically not actually been written to disk yet.)
6705 The arguments are the same as for
<literal>buffer-flush-start
</literal>.
</entry>
6708 <entry><literal>wal-buffer-write-dirty-start
</literal></entry>
6709 <entry><literal>()
</literal></entry>
6710 <entry>Probe that fires when a server process begins to write a
6711 dirty WAL buffer because no more WAL buffer space is available.
6712 (If this happens often, it implies that
6713 <xref linkend=
"guc-wal-buffers"/> is too small.)
</entry>
6716 <entry><literal>wal-buffer-write-dirty-done
</literal></entry>
6717 <entry><literal>()
</literal></entry>
6718 <entry>Probe that fires when a dirty WAL buffer write is complete.
</entry>
6721 <entry><literal>wal-insert
</literal></entry>
6722 <entry><literal>(unsigned char, unsigned char)
</literal></entry>
6723 <entry>Probe that fires when a WAL record is inserted.
6724 arg0 is the resource manager (rmid) for the record.
6725 arg1 contains the info flags.
</entry>
6728 <entry><literal>wal-switch
</literal></entry>
6729 <entry><literal>()
</literal></entry>
6730 <entry>Probe that fires when a WAL segment switch is requested.
</entry>
6733 <entry><literal>smgr-md-read-start
</literal></entry>
6734 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int)
</literal></entry>
6735 <entry>Probe that fires when beginning to read a block from a relation.
6736 arg0 and arg1 contain the fork and block numbers of the page.
6737 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
6738 identifying the relation.
6739 arg5 is the ID of the backend which created the temporary relation for a
6740 local buffer, or
<symbol>InvalidBackendId
</symbol> (-
1) for a shared buffer.
</entry>
6743 <entry><literal>smgr-md-read-done
</literal></entry>
6744 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, int, int)
</literal></entry>
6745 <entry>Probe that fires when a block read is complete.
6746 arg0 and arg1 contain the fork and block numbers of the page.
6747 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
6748 identifying the relation.
6749 arg5 is the ID of the backend which created the temporary relation for a
6750 local buffer, or
<symbol>InvalidBackendId
</symbol> (-
1) for a shared buffer.
6751 arg6 is the number of bytes actually read, while arg7 is the number
6752 requested (if these are different it indicates trouble).
</entry>
6755 <entry><literal>smgr-md-write-start
</literal></entry>
6756 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int)
</literal></entry>
6757 <entry>Probe that fires when beginning to write a block to a relation.
6758 arg0 and arg1 contain the fork and block numbers of the page.
6759 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
6760 identifying the relation.
6761 arg5 is the ID of the backend which created the temporary relation for a
6762 local buffer, or
<symbol>InvalidBackendId
</symbol> (-
1) for a shared buffer.
</entry>
6765 <entry><literal>smgr-md-write-done
</literal></entry>
6766 <entry><literal>(ForkNumber, BlockNumber, Oid, Oid, Oid, int, int, int)
</literal></entry>
6767 <entry>Probe that fires when a block write is complete.
6768 arg0 and arg1 contain the fork and block numbers of the page.
6769 arg2, arg3, and arg4 contain the tablespace, database, and relation OIDs
6770 identifying the relation.
6771 arg5 is the ID of the backend which created the temporary relation for a
6772 local buffer, or
<symbol>InvalidBackendId
</symbol> (-
1) for a shared buffer.
6773 arg6 is the number of bytes actually written, while arg7 is the number
6774 requested (if these are different it indicates trouble).
</entry>
6777 <entry><literal>sort-start
</literal></entry>
6778 <entry><literal>(int, bool, int, int, bool, int)
</literal></entry>
6779 <entry>Probe that fires when a sort operation is started.
6780 arg0 indicates heap, index or datum sort.
6781 arg1 is true for unique-value enforcement.
6782 arg2 is the number of key columns.
6783 arg3 is the number of kilobytes of work memory allowed.
6784 arg4 is true if random access to the sort result is required.
6785 arg5 indicates serial when
<literal>0</literal>, parallel worker when
6786 <literal>1</literal>, or parallel leader when
<literal>2</literal>.
</entry>
6789 <entry><literal>sort-done
</literal></entry>
6790 <entry><literal>(bool, long)
</literal></entry>
6791 <entry>Probe that fires when a sort is complete.
6792 arg0 is true for external sort, false for internal sort.
6793 arg1 is the number of disk blocks used for an external sort,
6794 or kilobytes of memory used for an internal sort.
</entry>
6797 <entry><literal>lwlock-acquire
</literal></entry>
6798 <entry><literal>(char *, LWLockMode)
</literal></entry>
6799 <entry>Probe that fires when an LWLock has been acquired.
6800 arg0 is the LWLock's tranche.
6801 arg1 is the requested lock mode, either exclusive or shared.
</entry>
6804 <entry><literal>lwlock-release
</literal></entry>
6805 <entry><literal>(char *)
</literal></entry>
6806 <entry>Probe that fires when an LWLock has been released (but note
6807 that any released waiters have not yet been awakened).
6808 arg0 is the LWLock's tranche.
</entry>
6811 <entry><literal>lwlock-wait-start
</literal></entry>
6812 <entry><literal>(char *, LWLockMode)
</literal></entry>
6813 <entry>Probe that fires when an LWLock was not immediately available and
6814 a server process has begun to wait for the lock to become available.
6815 arg0 is the LWLock's tranche.
6816 arg1 is the requested lock mode, either exclusive or shared.
</entry>
6819 <entry><literal>lwlock-wait-done
</literal></entry>
6820 <entry><literal>(char *, LWLockMode)
</literal></entry>
6821 <entry>Probe that fires when a server process has been released from its
6822 wait for an LWLock (it does not actually have the lock yet).
6823 arg0 is the LWLock's tranche.
6824 arg1 is the requested lock mode, either exclusive or shared.
</entry>
6827 <entry><literal>lwlock-condacquire
</literal></entry>
6828 <entry><literal>(char *, LWLockMode)
</literal></entry>
6829 <entry>Probe that fires when an LWLock was successfully acquired when the
6830 caller specified no waiting.
6831 arg0 is the LWLock's tranche.
6832 arg1 is the requested lock mode, either exclusive or shared.
</entry>
6835 <entry><literal>lwlock-condacquire-fail
</literal></entry>
6836 <entry><literal>(char *, LWLockMode)
</literal></entry>
6837 <entry>Probe that fires when an LWLock was not successfully acquired when
6838 the caller specified no waiting.
6839 arg0 is the LWLock's tranche.
6840 arg1 is the requested lock mode, either exclusive or shared.
</entry>
6843 <entry><literal>lock-wait-start
</literal></entry>
6844 <entry><literal>(unsigned int, unsigned int, unsigned int, unsigned int, unsigned int, LOCKMODE)
</literal></entry>
6845 <entry>Probe that fires when a request for a heavyweight lock (lmgr lock)
6846 has begun to wait because the lock is not available.
6847 arg0 through arg3 are the tag fields identifying the object being
6848 locked. arg4 indicates the type of object being locked.
6849 arg5 indicates the lock type being requested.
</entry>
6852 <entry><literal>lock-wait-done
</literal></entry>
6853 <entry><literal>(unsigned int, unsigned int, unsigned int, unsigned int, unsigned int, LOCKMODE)
</literal></entry>
6854 <entry>Probe that fires when a request for a heavyweight lock (lmgr lock)
6855 has finished waiting (i.e., has acquired the lock).
6856 The arguments are the same as for
<literal>lock-wait-start
</literal>.
</entry>
6859 <entry><literal>deadlock-found
</literal></entry>
6860 <entry><literal>()
</literal></entry>
6861 <entry>Probe that fires when a deadlock is found by the deadlock
6869 <table id=
"typedefs-table">
6870 <title>Defined Types Used in Probe Parameters
</title>
6875 <entry>Definition
</entry>
6882 <entry><type>LocalTransactionId
</type></entry>
6883 <entry><type>unsigned int
</type></entry>
6886 <entry><type>LWLockMode
</type></entry>
6887 <entry><type>int
</type></entry>
6890 <entry><type>LOCKMODE
</type></entry>
6891 <entry><type>int
</type></entry>
6894 <entry><type>BlockNumber
</type></entry>
6895 <entry><type>unsigned int
</type></entry>
6898 <entry><type>Oid
</type></entry>
6899 <entry><type>unsigned int
</type></entry>
6902 <entry><type>ForkNumber
</type></entry>
6903 <entry><type>int
</type></entry>
6906 <entry><type>bool
</type></entry>
6907 <entry><type>unsigned char
</type></entry>
6917 <sect2 id=
"using-trace-points">
6918 <title>Using Probes
</title>
6921 The example below shows a DTrace script for analyzing transaction
6922 counts in the system, as an alternative to snapshotting
6923 <structname>pg_stat_database
</structname> before and after a performance test:
6925 #!/usr/sbin/dtrace -qs
6927 postgresql$
1:::transaction-start
6929 @start[
"Start"] = count();
6930 self-
>ts = timestamp;
6933 postgresql$
1:::transaction-abort
6935 @abort[
"Abort"] = count();
6938 postgresql$
1:::transaction-commit
6941 @commit[
"Commit"] = count();
6942 @time[
"Total time (ns)"] = sum(timestamp - self-
>ts);
6946 When executed, the example D script gives output such as:
6948 # ./txn_count.d `pgrep -n postgres` or ./txn_count.d
<PID
>
6953 Total time (ns)
2312105013
6959 SystemTap uses a different notation for trace scripts than DTrace does,
6960 even though the underlying trace points are compatible. One point worth
6961 noting is that at this writing, SystemTap scripts must reference probe
6962 names using double underscores in place of hyphens. This is expected to
6963 be fixed in future SystemTap releases.
6968 You should remember that DTrace scripts need to be carefully written and
6969 debugged, otherwise the trace information collected might
6970 be meaningless. In most cases where problems are found it is the
6971 instrumentation that is at fault, not the underlying system. When
6972 discussing information found using dynamic tracing, be sure to enclose
6973 the script used to allow that too to be checked and discussed.
6977 <sect2 id=
"defining-trace-points">
6978 <title>Defining New Probes
</title>
6981 New probes can be defined within the code wherever the developer
6982 desires, though this will require a recompilation. Below are the steps
6983 for inserting new probes:
6989 Decide on probe names and data to be made available through the probes
6995 Add the probe definitions to
<filename>src/backend/utils/probes.d
</filename>
7001 Include
<filename>pg_trace.h
</filename> if it is not already present in the
7002 module(s) containing the probe points, and insert
7003 <literal>TRACE_POSTGRESQL
</literal> probe macros at the desired locations
7010 Recompile and verify that the new probes are available
7016 <title>Example:
</title>
7018 Here is an example of how you would add a probe to trace all new
7019 transactions by transaction ID.
7026 Decide that the probe will be named
<literal>transaction-start
</literal> and
7027 requires a parameter of type
<type>LocalTransactionId
</type>
7033 Add the probe definition to
<filename>src/backend/utils/probes.d
</filename>:
7035 probe transaction__start(LocalTransactionId);
7037 Note the use of the double underline in the probe name. In a DTrace
7038 script using the probe, the double underline needs to be replaced with a
7039 hyphen, so
<literal>transaction-start
</literal> is the name to document for
7046 At compile time,
<literal>transaction__start
</literal> is converted to a macro
7047 called
<literal>TRACE_POSTGRESQL_TRANSACTION_START
</literal> (notice the
7048 underscores are single here), which is available by including
7049 <filename>pg_trace.h
</filename>. Add the macro call to the appropriate location
7050 in the source code. In this case, it looks like the following:
7053 TRACE_POSTGRESQL_TRANSACTION_START(vxid.localTransactionId);
7060 After recompiling and running the new binary, check that your newly added
7061 probe is available by executing the following DTrace command. You
7062 should see similar output:
7064 # dtrace -ln transaction-start
7065 ID PROVIDER MODULE FUNCTION NAME
7066 18705 postgresql49878 postgres StartTransactionCommand transaction-start
7067 18755 postgresql49877 postgres StartTransactionCommand transaction-start
7068 18805 postgresql49876 postgres StartTransactionCommand transaction-start
7069 18855 postgresql49875 postgres StartTransactionCommand transaction-start
7070 18986 postgresql49873 postgres StartTransactionCommand transaction-start
7077 There are a few things to be careful about when adding trace macros
7083 You should take care that the data types specified for a probe's
7084 parameters match the data types of the variables used in the macro.
7085 Otherwise, you will get compilation errors.
7092 On most platforms, if
<productname>PostgreSQL
</productname> is
7093 built with
<option>--enable-dtrace
</option>, the arguments to a trace
7094 macro will be evaluated whenever control passes through the
7095 macro,
<emphasis>even if no tracing is being done
</emphasis>. This is
7096 usually not worth worrying about if you are just reporting the
7097 values of a few local variables. But beware of putting expensive
7098 function calls into the arguments. If you need to do that,
7099 consider protecting the macro with a check to see if the trace
7100 is actually enabled:
7103 if (TRACE_POSTGRESQL_TRANSACTION_START_ENABLED())
7104 TRACE_POSTGRESQL_TRANSACTION_START(some_function(...));
7107 Each trace macro has a corresponding
<literal>ENABLED
</literal> macro.