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