1<!-- doc/src/sgml/maintenance.sgml --> 2 3<chapter id="maintenance"> 4 <title>Routine Database Maintenance Tasks</title> 5 6 <indexterm zone="maintenance"> 7 <primary>maintenance</primary> 8 </indexterm> 9 10 <indexterm zone="maintenance"> 11 <primary>routine maintenance</primary> 12 </indexterm> 13 14 <para> 15 <productname>PostgreSQL</productname>, like any database software, requires that certain tasks 16 be performed regularly to achieve optimum performance. The tasks 17 discussed here are <emphasis>required</emphasis>, but they 18 are repetitive in nature and can easily be automated using standard 19 tools such as <application>cron</application> scripts or 20 Windows' <application>Task Scheduler</application>. It is the database 21 administrator's responsibility to set up appropriate scripts, and to 22 check that they execute successfully. 23 </para> 24 25 <para> 26 One obvious maintenance task is the creation of backup copies of the data on a 27 regular schedule. Without a recent backup, you have no chance of recovery 28 after a catastrophe (disk failure, fire, mistakenly dropping a critical 29 table, etc.). The backup and recovery mechanisms available in 30 <productname>PostgreSQL</productname> are discussed at length in 31 <xref linkend="backup"/>. 32 </para> 33 34 <para> 35 The other main category of maintenance task is periodic <quote>vacuuming</quote> 36 of the database. This activity is discussed in 37 <xref linkend="routine-vacuuming"/>. Closely related to this is updating 38 the statistics that will be used by the query planner, as discussed in 39 <xref linkend="vacuum-for-statistics"/>. 40 </para> 41 42 <para> 43 Another task that might need periodic attention is log file management. 44 This is discussed in <xref linkend="logfile-maintenance"/>. 45 </para> 46 47 <para> 48 <ulink 49 url="https://bucardo.org/check_postgres/"><application>check_postgres</application></ulink> 50 is available for monitoring database health and reporting unusual 51 conditions. <application>check_postgres</application> integrates with 52 Nagios and MRTG, but can be run standalone too. 53 </para> 54 55 <para> 56 <productname>PostgreSQL</productname> is low-maintenance compared 57 to some other database management systems. Nonetheless, 58 appropriate attention to these tasks will go far towards ensuring a 59 pleasant and productive experience with the system. 60 </para> 61 62 <sect1 id="routine-vacuuming"> 63 <title>Routine Vacuuming</title> 64 65 <indexterm zone="routine-vacuuming"> 66 <primary>vacuum</primary> 67 </indexterm> 68 69 <para> 70 <productname>PostgreSQL</productname> databases require periodic 71 maintenance known as <firstterm>vacuuming</firstterm>. For many installations, it 72 is sufficient to let vacuuming be performed by the <firstterm>autovacuum 73 daemon</firstterm>, which is described in <xref linkend="autovacuum"/>. You might 74 need to adjust the autovacuuming parameters described there to obtain best 75 results for your situation. Some database administrators will want to 76 supplement or replace the daemon's activities with manually-managed 77 <command>VACUUM</command> commands, which typically are executed according to a 78 schedule by <application>cron</application> or <application>Task 79 Scheduler</application> scripts. To set up manually-managed vacuuming properly, 80 it is essential to understand the issues discussed in the next few 81 subsections. Administrators who rely on autovacuuming may still wish 82 to skim this material to help them understand and adjust autovacuuming. 83 </para> 84 85 <sect2 id="vacuum-basics"> 86 <title>Vacuuming Basics</title> 87 88 <para> 89 <productname>PostgreSQL</productname>'s 90 <xref linkend="sql-vacuum"/> command has to 91 process each table on a regular basis for several reasons: 92 93 <orderedlist> 94 <listitem> 95 <simpara>To recover or reuse disk space occupied by updated or deleted 96 rows.</simpara> 97 </listitem> 98 99 <listitem> 100 <simpara>To update data statistics used by the 101 <productname>PostgreSQL</productname> query planner.</simpara> 102 </listitem> 103 104 <listitem> 105 <simpara>To update the visibility map, which speeds 106 up <link linkend="indexes-index-only-scans">index-only 107 scans</link>.</simpara> 108 </listitem> 109 110 <listitem> 111 <simpara>To protect against loss of very old data due to 112 <firstterm>transaction ID wraparound</firstterm> or 113 <firstterm>multixact ID wraparound</firstterm>.</simpara> 114 </listitem> 115 </orderedlist> 116 117 Each of these reasons dictates performing <command>VACUUM</command> operations 118 of varying frequency and scope, as explained in the following subsections. 119 </para> 120 121 <para> 122 There are two variants of <command>VACUUM</command>: standard <command>VACUUM</command> 123 and <command>VACUUM FULL</command>. <command>VACUUM FULL</command> can reclaim more 124 disk space but runs much more slowly. Also, 125 the standard form of <command>VACUUM</command> can run in parallel with production 126 database operations. (Commands such as <command>SELECT</command>, 127 <command>INSERT</command>, <command>UPDATE</command>, and 128 <command>DELETE</command> will continue to function normally, though you 129 will not be able to modify the definition of a table with commands such as 130 <command>ALTER TABLE</command> while it is being vacuumed.) 131 <command>VACUUM FULL</command> requires an 132 <literal>ACCESS EXCLUSIVE</literal> lock on the table it is 133 working on, and therefore cannot be done in parallel with other use 134 of the table. Generally, therefore, 135 administrators should strive to use standard <command>VACUUM</command> and 136 avoid <command>VACUUM FULL</command>. 137 </para> 138 139 <para> 140 <command>VACUUM</command> creates a substantial amount of I/O 141 traffic, which can cause poor performance for other active sessions. 142 There are configuration parameters that can be adjusted to reduce the 143 performance impact of background vacuuming — see 144 <xref linkend="runtime-config-resource-vacuum-cost"/>. 145 </para> 146 </sect2> 147 148 <sect2 id="vacuum-for-space-recovery"> 149 <title>Recovering Disk Space</title> 150 151 <indexterm zone="vacuum-for-space-recovery"> 152 <primary>disk space</primary> 153 </indexterm> 154 155 <para> 156 In <productname>PostgreSQL</productname>, an 157 <command>UPDATE</command> or <command>DELETE</command> of a row does not 158 immediately remove the old version of the row. 159 This approach is necessary to gain the benefits of multiversion 160 concurrency control (<acronym>MVCC</acronym>, see <xref linkend="mvcc"/>): the row version 161 must not be deleted while it is still potentially visible to other 162 transactions. But eventually, an outdated or deleted row version is no 163 longer of interest to any transaction. The space it occupies must then be 164 reclaimed for reuse by new rows, to avoid unbounded growth of disk 165 space requirements. This is done by running <command>VACUUM</command>. 166 </para> 167 168 <para> 169 The standard form of <command>VACUUM</command> removes dead row 170 versions in tables and indexes and marks the space available for 171 future reuse. However, it will not return the space to the operating 172 system, except in the special case where one or more pages at the 173 end of a table become entirely free and an exclusive table lock can be 174 easily obtained. In contrast, <command>VACUUM FULL</command> actively compacts 175 tables by writing a complete new version of the table file with no dead 176 space. This minimizes the size of the table, but can take a long time. 177 It also requires extra disk space for the new copy of the table, until 178 the operation completes. 179 </para> 180 181 <para> 182 The usual goal of routine vacuuming is to do standard <command>VACUUM</command>s 183 often enough to avoid needing <command>VACUUM FULL</command>. The 184 autovacuum daemon attempts to work this way, and in fact will 185 never issue <command>VACUUM FULL</command>. In this approach, the idea 186 is not to keep tables at their minimum size, but to maintain steady-state 187 usage of disk space: each table occupies space equivalent to its 188 minimum size plus however much space gets used up between vacuum runs. 189 Although <command>VACUUM FULL</command> can be used to shrink a table back 190 to its minimum size and return the disk space to the operating system, 191 there is not much point in this if the table will just grow again in the 192 future. Thus, moderately-frequent standard <command>VACUUM</command> runs are a 193 better approach than infrequent <command>VACUUM FULL</command> runs for 194 maintaining heavily-updated tables. 195 </para> 196 197 <para> 198 Some administrators prefer to schedule vacuuming themselves, for example 199 doing all the work at night when load is low. 200 The difficulty with doing vacuuming according to a fixed schedule 201 is that if a table has an unexpected spike in update activity, it may 202 get bloated to the point that <command>VACUUM FULL</command> is really necessary 203 to reclaim space. Using the autovacuum daemon alleviates this problem, 204 since the daemon schedules vacuuming dynamically in response to update 205 activity. It is unwise to disable the daemon completely unless you 206 have an extremely predictable workload. One possible compromise is 207 to set the daemon's parameters so that it will only react to unusually 208 heavy update activity, thus keeping things from getting out of hand, 209 while scheduled <command>VACUUM</command>s are expected to do the bulk of the 210 work when the load is typical. 211 </para> 212 213 <para> 214 For those not using autovacuum, a typical approach is to schedule a 215 database-wide <command>VACUUM</command> once a day during a low-usage period, 216 supplemented by more frequent vacuuming of heavily-updated tables as 217 necessary. (Some installations with extremely high update rates vacuum 218 their busiest tables as often as once every few minutes.) If you have 219 multiple databases in a cluster, don't forget to 220 <command>VACUUM</command> each one; the program <xref 221 linkend="app-vacuumdb"/> might be helpful. 222 </para> 223 224 <tip> 225 <para> 226 Plain <command>VACUUM</command> may not be satisfactory when 227 a table contains large numbers of dead row versions as a result of 228 massive update or delete activity. If you have such a table and 229 you need to reclaim the excess disk space it occupies, you will need 230 to use <command>VACUUM FULL</command>, or alternatively 231 <xref linkend="sql-cluster"/> 232 or one of the table-rewriting variants of 233 <xref linkend="sql-altertable"/>. 234 These commands rewrite an entire new copy of the table and build 235 new indexes for it. All these options require an 236 <literal>ACCESS EXCLUSIVE</literal> lock. Note that 237 they also temporarily use extra disk space approximately equal to the size 238 of the table, since the old copies of the table and indexes can't be 239 released until the new ones are complete. 240 </para> 241 </tip> 242 243 <tip> 244 <para> 245 If you have a table whose entire contents are deleted on a periodic 246 basis, consider doing it with 247 <xref linkend="sql-truncate"/> rather 248 than using <command>DELETE</command> followed by 249 <command>VACUUM</command>. <command>TRUNCATE</command> removes the 250 entire content of the table immediately, without requiring a 251 subsequent <command>VACUUM</command> or <command>VACUUM 252 FULL</command> to reclaim the now-unused disk space. 253 The disadvantage is that strict MVCC semantics are violated. 254 </para> 255 </tip> 256 </sect2> 257 258 <sect2 id="vacuum-for-statistics"> 259 <title>Updating Planner Statistics</title> 260 261 <indexterm zone="vacuum-for-statistics"> 262 <primary>statistics</primary> 263 <secondary>of the planner</secondary> 264 </indexterm> 265 266 <indexterm zone="vacuum-for-statistics"> 267 <primary>ANALYZE</primary> 268 </indexterm> 269 270 <para> 271 The <productname>PostgreSQL</productname> query planner relies on 272 statistical information about the contents of tables in order to 273 generate good plans for queries. These statistics are gathered by 274 the <xref linkend="sql-analyze"/> command, 275 which can be invoked by itself or 276 as an optional step in <command>VACUUM</command>. It is important to have 277 reasonably accurate statistics, otherwise poor choices of plans might 278 degrade database performance. 279 </para> 280 281 <para> 282 The autovacuum daemon, if enabled, will automatically issue 283 <command>ANALYZE</command> commands whenever the content of a table has 284 changed sufficiently. However, administrators might prefer to rely 285 on manually-scheduled <command>ANALYZE</command> operations, particularly 286 if it is known that update activity on a table will not affect the 287 statistics of <quote>interesting</quote> columns. The daemon schedules 288 <command>ANALYZE</command> strictly as a function of the number of rows 289 inserted or updated; it has no knowledge of whether that will lead 290 to meaningful statistical changes. 291 </para> 292 293 <para> 294 As with vacuuming for space recovery, frequent updates of statistics 295 are more useful for heavily-updated tables than for seldom-updated 296 ones. But even for a heavily-updated table, there might be no need for 297 statistics updates if the statistical distribution of the data is 298 not changing much. A simple rule of thumb is to think about how much 299 the minimum and maximum values of the columns in the table change. 300 For example, a <type>timestamp</type> column that contains the time 301 of row update will have a constantly-increasing maximum value as 302 rows are added and updated; such a column will probably need more 303 frequent statistics updates than, say, a column containing URLs for 304 pages accessed on a website. The URL column might receive changes just 305 as often, but the statistical distribution of its values probably 306 changes relatively slowly. 307 </para> 308 309 <para> 310 It is possible to run <command>ANALYZE</command> on specific tables and even 311 just specific columns of a table, so the flexibility exists to update some 312 statistics more frequently than others if your application requires it. 313 In practice, however, it is usually best to just analyze the entire 314 database, because it is a fast operation. <command>ANALYZE</command> uses a 315 statistically random sampling of the rows of a table rather than reading 316 every single row. 317 </para> 318 319 <tip> 320 <para> 321 Although per-column tweaking of <command>ANALYZE</command> frequency might not be 322 very productive, you might find it worthwhile to do per-column 323 adjustment of the level of detail of the statistics collected by 324 <command>ANALYZE</command>. Columns that are heavily used in <literal>WHERE</literal> 325 clauses and have highly irregular data distributions might require a 326 finer-grain data histogram than other columns. See <command>ALTER TABLE 327 SET STATISTICS</command>, or change the database-wide default using the <xref 328 linkend="guc-default-statistics-target"/> configuration parameter. 329 </para> 330 331 <para> 332 Also, by default there is limited information available about 333 the selectivity of functions. However, if you create an expression 334 index that uses a function call, useful statistics will be 335 gathered about the function, which can greatly improve query 336 plans that use the expression index. 337 </para> 338 </tip> 339 340 <tip> 341 <para> 342 The autovacuum daemon does not issue <command>ANALYZE</command> commands for 343 foreign tables, since it has no means of determining how often that 344 might be useful. If your queries require statistics on foreign tables 345 for proper planning, it's a good idea to run manually-managed 346 <command>ANALYZE</command> commands on those tables on a suitable schedule. 347 </para> 348 </tip> 349 </sect2> 350 351 <sect2 id="vacuum-for-visibility-map"> 352 <title>Updating the Visibility Map</title> 353 354 <para> 355 Vacuum maintains a <link linkend="storage-vm">visibility map</link> for each 356 table to keep track of which pages contain only tuples that are known to be 357 visible to all active transactions (and all future transactions, until the 358 page is again modified). This has two purposes. First, vacuum 359 itself can skip such pages on the next run, since there is nothing to 360 clean up. 361 </para> 362 363 <para> 364 Second, it allows <productname>PostgreSQL</productname> to answer some 365 queries using only the index, without reference to the underlying table. 366 Since <productname>PostgreSQL</productname> indexes don't contain tuple 367 visibility information, a normal index scan fetches the heap tuple for each 368 matching index entry, to check whether it should be seen by the current 369 transaction. 370 An <link linkend="indexes-index-only-scans"><firstterm>index-only 371 scan</firstterm></link>, on the other hand, checks the visibility map first. 372 If it's known that all tuples on the page are 373 visible, the heap fetch can be skipped. This is most useful on 374 large data sets where the visibility map can prevent disk accesses. 375 The visibility map is vastly smaller than the heap, so it can easily be 376 cached even when the heap is very large. 377 </para> 378 </sect2> 379 380 <sect2 id="vacuum-for-wraparound"> 381 <title>Preventing Transaction ID Wraparound Failures</title> 382 383 <indexterm zone="vacuum-for-wraparound"> 384 <primary>transaction ID</primary> 385 <secondary>wraparound</secondary> 386 </indexterm> 387 388 <indexterm> 389 <primary>wraparound</primary> 390 <secondary>of transaction IDs</secondary> 391 </indexterm> 392 393 <para> 394 <productname>PostgreSQL</productname>'s 395 <link linkend="mvcc-intro">MVCC</link> transaction semantics 396 depend on being able to compare transaction ID (<acronym>XID</acronym>) 397 numbers: a row version with an insertion XID greater than the current 398 transaction's XID is <quote>in the future</quote> and should not be visible 399 to the current transaction. But since transaction IDs have limited size 400 (32 bits) a cluster that runs for a long time (more 401 than 4 billion transactions) would suffer <firstterm>transaction ID 402 wraparound</firstterm>: the XID counter wraps around to zero, and all of a sudden 403 transactions that were in the past appear to be in the future — which 404 means their output become invisible. In short, catastrophic data loss. 405 (Actually the data is still there, but that's cold comfort if you cannot 406 get at it.) To avoid this, it is necessary to vacuum every table 407 in every database at least once every two billion transactions. 408 </para> 409 410 <para> 411 The reason that periodic vacuuming solves the problem is that 412 <command>VACUUM</command> will mark rows as <emphasis>frozen</emphasis>, indicating that 413 they were inserted by a transaction that committed sufficiently far in 414 the past that the effects of the inserting transaction are certain to be 415 visible to all current and future transactions. 416 Normal XIDs are 417 compared using modulo-2<superscript>32</superscript> arithmetic. This means 418 that for every normal XID, there are two billion XIDs that are 419 <quote>older</quote> and two billion that are <quote>newer</quote>; another 420 way to say it is that the normal XID space is circular with no 421 endpoint. Therefore, once a row version has been created with a particular 422 normal XID, the row version will appear to be <quote>in the past</quote> for 423 the next two billion transactions, no matter which normal XID we are 424 talking about. If the row version still exists after more than two billion 425 transactions, it will suddenly appear to be in the future. To 426 prevent this, <productname>PostgreSQL</productname> reserves a special XID, 427 <literal>FrozenTransactionId</literal>, which does not follow the normal XID 428 comparison rules and is always considered older 429 than every normal XID. 430 Frozen row versions are treated as if the inserting XID were 431 <literal>FrozenTransactionId</literal>, so that they will appear to be 432 <quote>in the past</quote> to all normal transactions regardless of wraparound 433 issues, and so such row versions will be valid until deleted, no matter 434 how long that is. 435 </para> 436 437 <note> 438 <para> 439 In <productname>PostgreSQL</productname> versions before 9.4, freezing was 440 implemented by actually replacing a row's insertion XID 441 with <literal>FrozenTransactionId</literal>, which was visible in the 442 row's <structname>xmin</structname> system column. Newer versions just set a flag 443 bit, preserving the row's original <structname>xmin</structname> for possible 444 forensic use. However, rows with <structname>xmin</structname> equal 445 to <literal>FrozenTransactionId</literal> (2) may still be found 446 in databases <application>pg_upgrade</application>'d from pre-9.4 versions. 447 </para> 448 <para> 449 Also, system catalogs may contain rows with <structname>xmin</structname> equal 450 to <literal>BootstrapTransactionId</literal> (1), indicating that they were 451 inserted during the first phase of <application>initdb</application>. 452 Like <literal>FrozenTransactionId</literal>, this special XID is treated as 453 older than every normal XID. 454 </para> 455 </note> 456 457 <para> 458 <xref linkend="guc-vacuum-freeze-min-age"/> 459 controls how old an XID value has to be before rows bearing that XID will be 460 frozen. Increasing this setting may avoid unnecessary work if the 461 rows that would otherwise be frozen will soon be modified again, 462 but decreasing this setting increases 463 the number of transactions that can elapse before the table must be 464 vacuumed again. 465 </para> 466 467 <para> 468 <command>VACUUM</command> uses the <link linkend="storage-vm">visibility map</link> 469 to determine which pages of a table must be scanned. Normally, it 470 will skip pages that don't have any dead row versions even if those pages 471 might still have row versions with old XID values. Therefore, normal 472 <command>VACUUM</command>s won't always freeze every old row version in the table. 473 Periodically, <command>VACUUM</command> will perform an <firstterm>aggressive 474 vacuum</firstterm>, skipping only those pages which contain neither dead rows nor 475 any unfrozen XID or MXID values. 476 <xref linkend="guc-vacuum-freeze-table-age"/> 477 controls when <command>VACUUM</command> does that: all-visible but not all-frozen 478 pages are scanned if the number of transactions that have passed since the 479 last such scan is greater than <varname>vacuum_freeze_table_age</varname> minus 480 <varname>vacuum_freeze_min_age</varname>. Setting 481 <varname>vacuum_freeze_table_age</varname> to 0 forces <command>VACUUM</command> to 482 use this more aggressive strategy for all scans. 483 </para> 484 485 <para> 486 The maximum time that a table can go unvacuumed is two billion 487 transactions minus the <varname>vacuum_freeze_min_age</varname> value at 488 the time of the last aggressive vacuum. If it were to go 489 unvacuumed for longer than 490 that, data loss could result. To ensure that this does not happen, 491 autovacuum is invoked on any table that might contain unfrozen rows with 492 XIDs older than the age specified by the configuration parameter <xref 493 linkend="guc-autovacuum-freeze-max-age"/>. (This will happen even if 494 autovacuum is disabled.) 495 </para> 496 497 <para> 498 This implies that if a table is not otherwise vacuumed, 499 autovacuum will be invoked on it approximately once every 500 <varname>autovacuum_freeze_max_age</varname> minus 501 <varname>vacuum_freeze_min_age</varname> transactions. 502 For tables that are regularly vacuumed for space reclamation purposes, 503 this is of little importance. However, for static tables 504 (including tables that receive inserts, but no updates or deletes), 505 there is no need to vacuum for space reclamation, so it can 506 be useful to try to maximize the interval between forced autovacuums 507 on very large static tables. Obviously one can do this either by 508 increasing <varname>autovacuum_freeze_max_age</varname> or decreasing 509 <varname>vacuum_freeze_min_age</varname>. 510 </para> 511 512 <para> 513 The effective maximum for <varname>vacuum_freeze_table_age</varname> is 0.95 * 514 <varname>autovacuum_freeze_max_age</varname>; a setting higher than that will be 515 capped to the maximum. A value higher than 516 <varname>autovacuum_freeze_max_age</varname> wouldn't make sense because an 517 anti-wraparound autovacuum would be triggered at that point anyway, and 518 the 0.95 multiplier leaves some breathing room to run a manual 519 <command>VACUUM</command> before that happens. As a rule of thumb, 520 <command>vacuum_freeze_table_age</command> should be set to a value somewhat 521 below <varname>autovacuum_freeze_max_age</varname>, leaving enough gap so that 522 a regularly scheduled <command>VACUUM</command> or an autovacuum triggered by 523 normal delete and update activity is run in that window. Setting it too 524 close could lead to anti-wraparound autovacuums, even though the table 525 was recently vacuumed to reclaim space, whereas lower values lead to more 526 frequent aggressive vacuuming. 527 </para> 528 529 <para> 530 The sole disadvantage of increasing <varname>autovacuum_freeze_max_age</varname> 531 (and <varname>vacuum_freeze_table_age</varname> along with it) is that 532 the <filename>pg_xact</filename> and <filename>pg_commit_ts</filename> 533 subdirectories of the database cluster will take more space, because it 534 must store the commit status and (if <varname>track_commit_timestamp</varname> is 535 enabled) timestamp of all transactions back to 536 the <varname>autovacuum_freeze_max_age</varname> horizon. The commit status uses 537 two bits per transaction, so if 538 <varname>autovacuum_freeze_max_age</varname> is set to its maximum allowed value 539 of two billion, <filename>pg_xact</filename> can be expected to grow to about half 540 a gigabyte and <filename>pg_commit_ts</filename> to about 20GB. If this 541 is trivial compared to your total database size, 542 setting <varname>autovacuum_freeze_max_age</varname> to its maximum allowed value 543 is recommended. Otherwise, set it depending on what you are willing to 544 allow for <filename>pg_xact</filename> and <filename>pg_commit_ts</filename> storage. 545 (The default, 200 million transactions, translates to about 50MB 546 of <filename>pg_xact</filename> storage and about 2GB of <filename>pg_commit_ts</filename> 547 storage.) 548 </para> 549 550 <para> 551 One disadvantage of decreasing <varname>vacuum_freeze_min_age</varname> is that 552 it might cause <command>VACUUM</command> to do useless work: freezing a row 553 version is a waste of time if the row is modified 554 soon thereafter (causing it to acquire a new XID). So the setting should 555 be large enough that rows are not frozen until they are unlikely to change 556 any more. 557 </para> 558 559 <para> 560 To track the age of the oldest unfrozen XIDs in a database, 561 <command>VACUUM</command> stores XID 562 statistics in the system tables <structname>pg_class</structname> and 563 <structname>pg_database</structname>. In particular, 564 the <structfield>relfrozenxid</structfield> column of a table's 565 <structname>pg_class</structname> row contains the freeze cutoff XID that was used 566 by the last aggressive <command>VACUUM</command> for that table. All rows 567 inserted by transactions with XIDs older than this cutoff XID are 568 guaranteed to have been frozen. Similarly, 569 the <structfield>datfrozenxid</structfield> column of a database's 570 <structname>pg_database</structname> row is a lower bound on the unfrozen XIDs 571 appearing in that database — it is just the minimum of the 572 per-table <structfield>relfrozenxid</structfield> values within the database. 573 A convenient way to 574 examine this information is to execute queries such as: 575 576<programlisting> 577SELECT c.oid::regclass as table_name, 578 greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age 579FROM pg_class c 580LEFT JOIN pg_class t ON c.reltoastrelid = t.oid 581WHERE c.relkind IN ('r', 'm'); 582 583SELECT datname, age(datfrozenxid) FROM pg_database; 584</programlisting> 585 586 The <literal>age</literal> column measures the number of transactions from the 587 cutoff XID to the current transaction's XID. 588 </para> 589 590 <para> 591 <command>VACUUM</command> normally only scans pages that have been modified 592 since the last vacuum, but <structfield>relfrozenxid</structfield> can only be 593 advanced when every page of the table 594 that might contain unfrozen XIDs is scanned. This happens when 595 <structfield>relfrozenxid</structfield> is more than 596 <varname>vacuum_freeze_table_age</varname> transactions old, when 597 <command>VACUUM</command>'s <literal>FREEZE</literal> option is used, or when all 598 pages that are not already all-frozen happen to 599 require vacuuming to remove dead row versions. When <command>VACUUM</command> 600 scans every page in the table that is not already all-frozen, it should 601 set <literal>age(relfrozenxid)</literal> to a value just a little more than the 602 <varname>vacuum_freeze_min_age</varname> setting 603 that was used (more by the number of transactions started since the 604 <command>VACUUM</command> started). If no <structfield>relfrozenxid</structfield>-advancing 605 <command>VACUUM</command> is issued on the table until 606 <varname>autovacuum_freeze_max_age</varname> is reached, an autovacuum will soon 607 be forced for the table. 608 </para> 609 610 <para> 611 If for some reason autovacuum fails to clear old XIDs from a table, the 612 system will begin to emit warning messages like this when the database's 613 oldest XIDs reach eleven million transactions from the wraparound point: 614 615<programlisting> 616WARNING: database "mydb" must be vacuumed within 10985967 transactions 617HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database. 618</programlisting> 619 620 (A manual <command>VACUUM</command> should fix the problem, as suggested by the 621 hint; but note that the <command>VACUUM</command> must be performed by a 622 superuser, else it will fail to process system catalogs and thus not 623 be able to advance the database's <structfield>datfrozenxid</structfield>.) 624 If these warnings are 625 ignored, the system will shut down and refuse to start any new 626 transactions once there are fewer than 1 million transactions left 627 until wraparound: 628 629<programlisting> 630ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb" 631HINT: Stop the postmaster and vacuum that database in single-user mode. 632</programlisting> 633 634 The 1-million-transaction safety margin exists to let the 635 administrator recover without data loss, by manually executing the 636 required <command>VACUUM</command> commands. However, since the system will not 637 execute commands once it has gone into the safety shutdown mode, 638 the only way to do this is to stop the server and start the server in single-user 639 mode to execute <command>VACUUM</command>. The shutdown mode is not enforced 640 in single-user mode. See the <xref linkend="app-postgres"/> reference 641 page for details about using single-user mode. 642 </para> 643 644 <sect3 id="vacuum-for-multixact-wraparound"> 645 <title>Multixacts and Wraparound</title> 646 647 <indexterm> 648 <primary>MultiXactId</primary> 649 </indexterm> 650 651 <indexterm> 652 <primary>wraparound</primary> 653 <secondary>of multixact IDs</secondary> 654 </indexterm> 655 656 <para> 657 <firstterm>Multixact IDs</firstterm> are used to support row locking by 658 multiple transactions. Since there is only limited space in a tuple 659 header to store lock information, that information is encoded as 660 a <quote>multiple transaction ID</quote>, or multixact ID for short, 661 whenever there is more than one transaction concurrently locking a 662 row. Information about which transaction IDs are included in any 663 particular multixact ID is stored separately in 664 the <filename>pg_multixact</filename> subdirectory, and only the multixact ID 665 appears in the <structfield>xmax</structfield> field in the tuple header. 666 Like transaction IDs, multixact IDs are implemented as a 667 32-bit counter and corresponding storage, all of which requires 668 careful aging management, storage cleanup, and wraparound handling. 669 There is a separate storage area which holds the list of members in 670 each multixact, which also uses a 32-bit counter and which must also 671 be managed. 672 </para> 673 674 <para> 675 Whenever <command>VACUUM</command> scans any part of a table, it will replace 676 any multixact ID it encounters which is older than 677 <xref linkend="guc-vacuum-multixact-freeze-min-age"/> 678 by a different value, which can be the zero value, a single 679 transaction ID, or a newer multixact ID. For each table, 680 <structname>pg_class</structname>.<structfield>relminmxid</structfield> stores the oldest 681 possible multixact ID still appearing in any tuple of that table. 682 If this value is older than 683 <xref linkend="guc-vacuum-multixact-freeze-table-age"/>, an aggressive 684 vacuum is forced. As discussed in the previous section, an aggressive 685 vacuum means that only those pages which are known to be all-frozen will 686 be skipped. <function>mxid_age()</function> can be used on 687 <structname>pg_class</structname>.<structfield>relminmxid</structfield> to find its age. 688 </para> 689 690 <para> 691 Aggressive <command>VACUUM</command> scans, regardless of 692 what causes them, enable advancing the value for that table. 693 Eventually, as all tables in all databases are scanned and their 694 oldest multixact values are advanced, on-disk storage for older 695 multixacts can be removed. 696 </para> 697 698 <para> 699 As a safety device, an aggressive vacuum scan will occur for any table 700 whose multixact-age is greater than 701 <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>. Aggressive 702 vacuum scans will also occur progressively for all tables, starting with 703 those that have the oldest multixact-age, if the amount of used member 704 storage space exceeds the amount 50% of the addressable storage space. 705 Both of these kinds of aggressive scans will occur even if autovacuum is 706 nominally disabled. 707 </para> 708 </sect3> 709 </sect2> 710 711 <sect2 id="autovacuum"> 712 <title>The Autovacuum Daemon</title> 713 714 <indexterm> 715 <primary>autovacuum</primary> 716 <secondary>general information</secondary> 717 </indexterm> 718 <para> 719 <productname>PostgreSQL</productname> has an optional but highly 720 recommended feature called <firstterm>autovacuum</firstterm>, 721 whose purpose is to automate the execution of 722 <command>VACUUM</command> and <command>ANALYZE </command> commands. 723 When enabled, autovacuum checks for 724 tables that have had a large number of inserted, updated or deleted 725 tuples. These checks use the statistics collection facility; 726 therefore, autovacuum cannot be used unless <xref 727 linkend="guc-track-counts"/> is set to <literal>true</literal>. 728 In the default configuration, autovacuuming is enabled and the related 729 configuration parameters are appropriately set. 730 </para> 731 732 <para> 733 The <quote>autovacuum daemon</quote> actually consists of multiple processes. 734 There is a persistent daemon process, called the 735 <firstterm>autovacuum launcher</firstterm>, which is in charge of starting 736 <firstterm>autovacuum worker</firstterm> processes for all databases. The 737 launcher will distribute the work across time, attempting to start one 738 worker within each database every <xref linkend="guc-autovacuum-naptime"/> 739 seconds. (Therefore, if the installation has <replaceable>N</replaceable> databases, 740 a new worker will be launched every 741 <varname>autovacuum_naptime</varname>/<replaceable>N</replaceable> seconds.) 742 A maximum of <xref linkend="guc-autovacuum-max-workers"/> worker processes 743 are allowed to run at the same time. If there are more than 744 <varname>autovacuum_max_workers</varname> databases to be processed, 745 the next database will be processed as soon as the first worker finishes. 746 Each worker process will check each table within its database and 747 execute <command>VACUUM</command> and/or <command>ANALYZE</command> as needed. 748 <xref linkend="guc-log-autovacuum-min-duration"/> can be set to monitor 749 autovacuum workers' activity. 750 </para> 751 752 <para> 753 If several large tables all become eligible for vacuuming in a short 754 amount of time, all autovacuum workers might become occupied with 755 vacuuming those tables for a long period. This would result 756 in other tables and databases not being vacuumed until a worker becomes 757 available. There is no limit on how many workers might be in a 758 single database, but workers do try to avoid repeating work that has 759 already been done by other workers. Note that the number of running 760 workers does not count towards <xref linkend="guc-max-connections"/> or 761 <xref linkend="guc-superuser-reserved-connections"/> limits. 762 </para> 763 764 <para> 765 Tables whose <structfield>relfrozenxid</structfield> value is more than 766 <xref linkend="guc-autovacuum-freeze-max-age"/> transactions old are always 767 vacuumed (this also applies to those tables whose freeze max age has 768 been modified via storage parameters; see below). Otherwise, if the 769 number of tuples obsoleted since the last 770 <command>VACUUM</command> exceeds the <quote>vacuum threshold</quote>, the 771 table is vacuumed. The vacuum threshold is defined as: 772<programlisting> 773vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples 774</programlisting> 775 where the vacuum base threshold is 776 <xref linkend="guc-autovacuum-vacuum-threshold"/>, 777 the vacuum scale factor is 778 <xref linkend="guc-autovacuum-vacuum-scale-factor"/>, 779 and the number of tuples is 780 <structname>pg_class</structname>.<structfield>reltuples</structfield>. 781 </para> 782 783 <para> 784 The table is also vacuumed if the number of tuples inserted since the last 785 vacuum has exceeded the defined insert threshold, which is defined as: 786<programlisting> 787vacuum insert threshold = vacuum base insert threshold + vacuum insert scale factor * number of tuples 788</programlisting> 789 where the vacuum insert base threshold is 790 <xref linkend="guc-autovacuum-vacuum-insert-threshold"/>, 791 and vacuum insert scale factor is 792 <xref linkend="guc-autovacuum-vacuum-insert-scale-factor"/>. 793 Such vacuums may allow portions of the table to be marked as 794 <firstterm>all visible</firstterm> and also allow tuples to be frozen, which 795 can reduce the work required in subsequent vacuums. 796 For tables which receive <command>INSERT</command> operations but no or 797 almost no <command>UPDATE</command>/<command>DELETE</command> operations, 798 it may be beneficial to lower the table's 799 <xref linkend="reloption-autovacuum-freeze-min-age"/> as this may allow 800 tuples to be frozen by earlier vacuums. The number of obsolete tuples and 801 the number of inserted tuples are obtained from the statistics collector; 802 it is a semi-accurate count updated by each <command>UPDATE</command>, 803 <command>DELETE</command> and <command>INSERT</command> operation. (It is 804 only semi-accurate because some information might be lost under heavy 805 load.) If the <structfield>relfrozenxid</structfield> value of the table 806 is more than <varname>vacuum_freeze_table_age</varname> transactions old, 807 an aggressive vacuum is performed to freeze old tuples and advance 808 <structfield>relfrozenxid</structfield>; otherwise, only pages that have been modified 809 since the last vacuum are scanned. 810 </para> 811 812 <para> 813 For analyze, a similar condition is used: the threshold, defined as: 814<programlisting> 815analyze threshold = analyze base threshold + analyze scale factor * number of tuples 816</programlisting> 817 is compared to the total number of tuples inserted, updated, or deleted 818 since the last <command>ANALYZE</command>. 819 </para> 820 821 <para> 822 Temporary tables cannot be accessed by autovacuum. Therefore, 823 appropriate vacuum and analyze operations should be performed via 824 session SQL commands. 825 </para> 826 827 <para> 828 The default thresholds and scale factors are taken from 829 <filename>postgresql.conf</filename>, but it is possible to override them 830 (and many other autovacuum control parameters) on a per-table basis; see 831 <xref linkend="sql-createtable-storage-parameters"/> for more information. 832 If a setting has been changed via a table's storage parameters, that value 833 is used when processing that table; otherwise the global settings are 834 used. See <xref linkend="runtime-config-autovacuum"/> for more details on 835 the global settings. 836 </para> 837 838 <para> 839 When multiple workers are running, the autovacuum cost delay parameters 840 (see <xref linkend="runtime-config-resource-vacuum-cost"/>) are 841 <quote>balanced</quote> among all the running workers, so that the 842 total I/O impact on the system is the same regardless of the number 843 of workers actually running. However, any workers processing tables whose 844 per-table <literal>autovacuum_vacuum_cost_delay</literal> or 845 <literal>autovacuum_vacuum_cost_limit</literal> storage parameters have been set 846 are not considered in the balancing algorithm. 847 </para> 848 849 <para> 850 Autovacuum workers generally don't block other commands. If a process 851 attempts to acquire a lock that conflicts with the 852 <literal>SHARE UPDATE EXCLUSIVE</literal> lock held by autovacuum, lock 853 acquisition will interrupt the autovacuum. For conflicting lock modes, 854 see <xref linkend="table-lock-compatibility"/>. However, if the autovacuum 855 is running to prevent transaction ID wraparound (i.e., the autovacuum query 856 name in the <structname>pg_stat_activity</structname> view ends with 857 <literal>(to prevent wraparound)</literal>), the autovacuum is not 858 automatically interrupted. 859 </para> 860 861 <warning> 862 <para> 863 Regularly running commands that acquire locks conflicting with a 864 <literal>SHARE UPDATE EXCLUSIVE</literal> lock (e.g., ANALYZE) can 865 effectively prevent autovacuums from ever completing. 866 </para> 867 </warning> 868 </sect2> 869 </sect1> 870 871 872 <sect1 id="routine-reindex"> 873 <title>Routine Reindexing</title> 874 875 <indexterm zone="routine-reindex"> 876 <primary>reindex</primary> 877 </indexterm> 878 879 <para> 880 In some situations it is worthwhile to rebuild indexes periodically 881 with the <xref linkend="sql-reindex"/> command or a series of individual 882 rebuilding steps. 883 884 </para> 885 886 <para> 887 B-tree index pages that have become completely empty are reclaimed for 888 re-use. However, there is still a possibility 889 of inefficient use of space: if all but a few index keys on a page have 890 been deleted, the page remains allocated. Therefore, a usage 891 pattern in which most, but not all, keys in each range are eventually 892 deleted will see poor use of space. For such usage patterns, 893 periodic reindexing is recommended. 894 </para> 895 896 <para> 897 The potential for bloat in non-B-tree indexes has not been well 898 researched. It is a good idea to periodically monitor the index's physical 899 size when using any non-B-tree index type. 900 </para> 901 902 <para> 903 Also, for B-tree indexes, a freshly-constructed index is slightly faster to 904 access than one that has been updated many times because logically 905 adjacent pages are usually also physically adjacent in a newly built index. 906 (This consideration does not apply to non-B-tree indexes.) It 907 might be worthwhile to reindex periodically just to improve access speed. 908 </para> 909 910 <para> 911 <xref linkend="sql-reindex"/> can be used safely and easily in all cases. 912 This command requires an <literal>ACCESS EXCLUSIVE</literal> lock by 913 default, hence it is often preferable to execute it with its 914 <literal>CONCURRENTLY</literal> option, which requires only a 915 <literal>SHARE UPDATE EXCLUSIVE</literal> lock. 916 </para> 917 </sect1> 918 919 920 <sect1 id="logfile-maintenance"> 921 <title>Log File Maintenance</title> 922 923 <indexterm zone="logfile-maintenance"> 924 <primary>server log</primary> 925 <secondary>log file maintenance</secondary> 926 </indexterm> 927 928 <para> 929 It is a good idea to save the database server's log output 930 somewhere, rather than just discarding it via <filename>/dev/null</filename>. 931 The log output is invaluable when diagnosing 932 problems. However, the log output tends to be voluminous 933 (especially at higher debug levels) so you won't want to save it 934 indefinitely. You need to <emphasis>rotate</emphasis> the log files so that 935 new log files are started and old ones removed after a reasonable 936 period of time. 937 </para> 938 939 <para> 940 If you simply direct the <systemitem>stderr</systemitem> of 941 <command>postgres</command> into a 942 file, you will have log output, but 943 the only way to truncate the log file is to stop and restart 944 the server. This might be acceptable if you are using 945 <productname>PostgreSQL</productname> in a development environment, 946 but few production servers would find this behavior acceptable. 947 </para> 948 949 <para> 950 A better approach is to send the server's 951 <systemitem>stderr</systemitem> output to some type of log rotation program. 952 There is a built-in log rotation facility, which you can use by 953 setting the configuration parameter <varname>logging_collector</varname> to 954 <literal>true</literal> in <filename>postgresql.conf</filename>. The control 955 parameters for this program are described in <xref 956 linkend="runtime-config-logging-where"/>. You can also use this approach 957 to capture the log data in machine readable <acronym>CSV</acronym> 958 (comma-separated values) format. 959 </para> 960 961 <para> 962 Alternatively, you might prefer to use an external log rotation 963 program if you have one that you are already using with other 964 server software. For example, the <application>rotatelogs</application> 965 tool included in the <productname>Apache</productname> distribution 966 can be used with <productname>PostgreSQL</productname>. One way to 967 do this is to pipe the server's 968 <systemitem>stderr</systemitem> output to the desired program. 969 If you start the server with 970 <command>pg_ctl</command>, then <systemitem>stderr</systemitem> 971 is already redirected to <systemitem>stdout</systemitem>, so you just need a 972 pipe command, for example: 973 974<programlisting> 975pg_ctl start | rotatelogs /var/log/pgsql_log 86400 976</programlisting> 977 </para> 978 979 <para> 980 You can combine these approaches by setting up <application>logrotate</application> 981 to collect log files produced by <productname>PostgreSQL</productname> built-in 982 logging collector. In this case, the logging collector defines the names and 983 location of the log files, while <application>logrotate</application> 984 periodically archives these files. When initiating log rotation, 985 <application>logrotate</application> must ensure that the application 986 sends further output to the new file. This is commonly done with a 987 <literal>postrotate</literal> script that sends a <literal>SIGHUP</literal> 988 signal to the application, which then reopens the log file. 989 In <productname>PostgreSQL</productname>, you can run <command>pg_ctl</command> 990 with the <literal>logrotate</literal> option instead. When the server receives 991 this command, the server either switches to a new log file or reopens the 992 existing file, depending on the logging configuration 993 (see <xref linkend="runtime-config-logging-where"/>). 994 </para> 995 996 <note> 997 <para> 998 When using static log file names, the server might fail to reopen the log 999 file if the max open file limit is reached or a file table overflow occurs. 1000 In this case, log messages are sent to the old log file until a 1001 successful log rotation. If <application>logrotate</application> is 1002 configured to compress the log file and delete it, the server may lose 1003 the messages logged in this time frame. To avoid this issue, you can 1004 configure the logging collector to dynamically assign log file names 1005 and use a <literal>prerotate</literal> script to ignore open log files. 1006 </para> 1007 </note> 1008 1009 <para> 1010 Another production-grade approach to managing log output is to 1011 send it to <application>syslog</application> and let 1012 <application>syslog</application> deal with file rotation. To do this, set the 1013 configuration parameter <varname>log_destination</varname> to <literal>syslog</literal> 1014 (to log to <application>syslog</application> only) in 1015 <filename>postgresql.conf</filename>. Then you can send a <literal>SIGHUP</literal> 1016 signal to the <application>syslog</application> daemon whenever you want to force it 1017 to start writing a new log file. If you want to automate log 1018 rotation, the <application>logrotate</application> program can be 1019 configured to work with log files from 1020 <application>syslog</application>. 1021 </para> 1022 1023 <para> 1024 On many systems, however, <application>syslog</application> is not very reliable, 1025 particularly with large log messages; it might truncate or drop messages 1026 just when you need them the most. Also, on <productname>Linux</productname>, 1027 <application>syslog</application> will flush each message to disk, yielding poor 1028 performance. (You can use a <quote><literal>-</literal></quote> at the start of the file name 1029 in the <application>syslog</application> configuration file to disable syncing.) 1030 </para> 1031 1032 <para> 1033 Note that all the solutions described above take care of starting new 1034 log files at configurable intervals, but they do not handle deletion 1035 of old, no-longer-useful log files. You will probably want to set 1036 up a batch job to periodically delete old log files. Another possibility 1037 is to configure the rotation program so that old log files are overwritten 1038 cyclically. 1039 </para> 1040 1041 <para> 1042 <ulink url="https://pgbadger.darold.net/"><productname>pgBadger</productname></ulink> 1043 is an external project that does sophisticated log file analysis. 1044 <ulink 1045 url="https://bucardo.org/check_postgres/"><productname>check_postgres</productname></ulink> 1046 provides Nagios alerts when important messages appear in the log 1047 files, as well as detection of many other extraordinary conditions. 1048 </para> 1049 </sect1> 1050</chapter> 1051