Upgrading from 7.4 to 14.2 gives you 18.2 years worth of fixes (57 of them)

List of changes:

• ⇑ Upgrade to 8.1.1 released on 2005-12-12 - docs

  • Prevent autovacuum from crashing during ANALYZE of expression index (Alvaro)

• ⇑ Upgrade to 8.1.2 released on 2006-01-09 - docs

  • Fix autovacuum crash when processing expression indexes

• ⇑ Upgrade to 8.1.4 released on 2006-05-23 - docs

  • Make autovacuum visible in pg_stat_activity (Alvaro)

• ⇑ Upgrade to 8.1.5 released on 2006-10-16 - docs

  • Fix autovacuum's calculation that decides whether ANALYZE is needed (Alvaro)

• ⇑ Upgrade to 8.2 released on 2006-12-05 - docs

  • Remove routine autovacuum server log entries (Bruce)

    pg_stat_activity now shows autovacuum activity.

• ⇑ Upgrade to 8.2.2 released on 2007-02-05 - docs

  • Fix bug that could cause the statistics collector to hang on Windows (Magnus)

    This would in turn lead to autovacuum not working.

• ⇑ Upgrade to 8.3 released on 2008-02-04 - docs

  • Autovacuum is now enabled by default (Alvaro)

    Several changes were made to eliminate disadvantages of having autovacuum enabled, thereby justifying the change in default. Several other autovacuum parameter defaults were also modified.

  • Support multiple concurrent autovacuum processes (Alvaro, Itagaki Takahiro)

    This allows multiple vacuums to run concurrently. This prevents vacuuming of a large table from delaying vacuuming of smaller tables.

  • Add log_autovacuum_min_duration parameter to support configurable logging of autovacuum activity (Simon, Alvaro)

  • Autovacuum now reports its activity start time in pg_stat_activity (Tom)

• ⇑ Upgrade to 8.3.4 released on 2008-09-22 - docs

  • Prevent autovacuum from crashing if the table it's currently checking is deleted at just the wrong time (Alvaro)

• ⇑ Upgrade to 8.3.6 released on 2009-02-02 - docs

  • Fix possible mis-signaling in autovacuum (Heikki)

• ⇑ Upgrade to 8.4 released on 2009-07-01 - docs

  • Add ability to specify per-relation autovacuum and TOAST parameters in CREATE TABLE (Alvaro, Euler Taveira de Oliveira)

    Autovacuum options used to be stored in a system table.

• ⇑ Upgrade to 9.0 released on 2010-09-20 - docs

  • Improve ANALYZE to support inheritance-tree statistics (Tom Lane)

    This is particularly useful for partitioned tables. However, autovacuum does not yet automatically re-analyze parent tables when child tables change.

  • Improve autovacuum's detection of when re-analyze is necessary (Tom Lane)

• ⇑ Upgrade to 9.0.1 released on 2010-10-04 - docs

  • Prevent show_session_authorization() from crashing within autovacuum processes (Tom Lane)

• ⇑ Upgrade to 9.0.2 released on 2010-12-16 - docs

  • Fix persistent slowdown of autovacuum workers when multiple workers remain active for a long time (Tom Lane)

    The effective vacuum_cost_limit for an autovacuum worker could drop to nearly zero if it processed enough tables, causing it to run extremely slowly.

  • Fix long-term memory leak in autovacuum launcher (Alvaro Herrera)

• ⇑ Upgrade to 9.1 released on 2011-09-12 - docs

  • Prevent autovacuum from waiting if it cannot acquire a table lock (Robert Haas)

    It will try to vacuum that table later.

• ⇑ Upgrade to 9.1.2 released on 2011-12-05 - docs

  • Prevent autovacuum transactions from running in serializable mode (Tom Lane)

    Autovacuum formerly used the cluster-wide default transaction isolation level, but there is no need for it to use anything higher than READ COMMITTED, and using SERIALIZABLE could result in unnecessary delays for other processes.

• ⇑ Upgrade to 9.1.4 released on 2012-06-04 - docs

  • Ensure autovacuum worker processes perform stack depth checking properly (Heikki Linnakangas)

    Previously, infinite recursion in a function invoked by auto-ANALYZE could crash worker processes.

• ⇑ Upgrade to 9.1.5 released on 2012-08-17 - docs

  • Only allow autovacuum to be auto-canceled by a directly blocked process (Tom Lane)

    The original coding could allow inconsistent behavior in some cases; in particular, an autovacuum could get canceled after less than deadlock_timeout grace period.

  • Improve logging of autovacuum cancels (Robert Haas)

• ⇑ Upgrade to 9.2 released on 2012-09-10 - docs

  • Make logging of autovacuum I/O activity more verbose (Greg Smith, Noah Misch)

    This logging is triggered by log_autovacuum_min_duration.

• ⇑ Upgrade to 9.2.2 released on 2012-12-06 - docs

  • Fix WaitLatch() to return promptly when the requested timeout expires (Jeff Janes, Tom Lane)

    With the previous coding, a steady stream of non-wait-terminating interrupts could delay return from WaitLatch() indefinitely. This has been shown to be a problem for the autovacuum launcher process, and might cause trouble elsewhere as well.

• ⇑ Upgrade to 9.2.3 released on 2013-02-07 - docs

  • Fix performance problems with autovacuum truncation in busy workloads (Jan Wieck)

    Truncation of empty pages at the end of a table requires exclusive lock, but autovacuum was coded to fail (and release the table lock) when there are conflicting lock requests. Under load, it is easily possible that truncation would never occur, resulting in table bloat. Fix by performing a partial truncation, releasing the lock, then attempting to re-acquire the lock and continue. This fix also greatly reduces the average time before autovacuum releases the lock after a conflicting request arrives.

  • Fix error in vacuum_freeze_table_age implementation (Andres Freund)

    In installations that have existed for more than vacuum_freeze_min_age transactions, this mistake prevented autovacuum from using partial-table scans, so that a full-table scan would always happen instead.

• ⇑ Upgrade to 9.3.2 released on 2013-12-05 - docs

  • Fix VACUUM's tests to see whether it can update relfrozenxid (Andres Freund)

    In some cases VACUUM (either manual or autovacuum) could incorrectly advance a table's relfrozenxid value, allowing tuples to escape freezing, causing those rows to become invisible once 2^31 transactions have elapsed. The probability of data loss is fairly low since multiple incorrect advancements would need to happen before actual loss occurs, but it's not zero. In 9.2.0 and later, the probability of loss is higher, and it's also possible to get “could not access status of transaction” errors as a consequence of this bug. Users upgrading from releases 9.0.4 or 8.4.8 or earlier are not affected, but all later versions contain the bug.

    The issue can be ameliorated by, after upgrading, vacuuming all tables in all databases while having vacuum_freeze_table_age set to zero. This will fix any latent corruption but will not be able to fix all pre-existing data errors. However, an installation can be presumed safe after performing this vacuuming if it has executed fewer than 2^31 update transactions in its lifetime (check this with SELECT txid_current() < 2^31).

  • Fix full-table-vacuum request mechanism for MultiXactIds (Andres Freund)

    This bug could result in large amounts of useless autovacuum activity.

• ⇑ Upgrade to 9.3.5 released on 2014-07-24 - docs

  • In pg_upgrade, preserve pg_database.datminmxid and pg_class.relminmxid values from the old cluster, or insert reasonable values when upgrading from pre-9.3; also defend against unreasonable values in the core server (Bruce Momjian, Álvaro Herrera, Tom Lane)

    These changes prevent scenarios in which autovacuum might insist on scanning the entire cluster's contents immediately upon starting the new cluster, or in which tracking of unfrozen MXID values might be disabled completely.

• ⇑ Upgrade to 9.4 released on 2014-12-18 - docs

  • Add autovacuum_work_mem configuration parameter to control the amount of memory used by autovacuum workers (Peter Geoghegan)

• ⇑ Upgrade to 9.4.2 released on 2015-05-22 - docs

  • Protect against wraparound of multixact member IDs (Álvaro Herrera, Robert Haas, Thomas Munro)

    Under certain usage patterns, the existing defenses against this might be insufficient, allowing pg_multixact/members files to be removed too early, resulting in data loss. The fix for this includes modifying the server to fail transactions that would result in overwriting old multixact member ID data, and improving autovacuum to ensure it will act proactively to prevent multixact member ID wraparound, as it does for transaction ID wraparound.

  • Fix autovacuum launcher's possible failure to shut down, if an error occurs after it receives SIGTERM (Álvaro Herrera)

• ⇑ Upgrade to 9.4.5 released on 2015-10-08 - docs

  • Do not print a WARNING when an autovacuum worker is already gone when we attempt to signal it, and reduce log verbosity for such signals (Tom Lane)

  • Prevent autovacuum launcher from sleeping unduly long if the server clock is moved backwards a large amount (Álvaro Herrera)

• ⇑ Upgrade to 9.5 released on 2016-01-07 - docs

  • Add per-table autovacuum logging control via new log_autovacuum_min_duration storage parameter (Michael Paquier)

  • Allow autovacuum workers to respond to configuration parameter changes during a run (Michael Paquier)

• ⇑ Upgrade to 9.5.4 released on 2016-08-11 - docs

  • Ensure that backends see up-to-date statistics for shared catalogs (Tom Lane)

    The statistics collector failed to update the statistics file for shared catalogs after a request from a regular backend. This problem was partially masked because the autovacuum launcher regularly makes requests that did cause such updates; however, it became obvious with autovacuum disabled.

  • Prevent autovacuum from starting multiple workers for the same shared catalog (Álvaro Herrera)

    Normally this isn't much of a problem because the vacuum doesn't take long anyway; but in the case of a severely bloated catalog, it could result in all but one worker uselessly waiting instead of doing useful work on other tables.

• ⇑ Upgrade to 9.6 released on 2016-09-29 - docs

  • Reduce the lock strength needed by ALTER TABLE when setting fillfactor and autovacuum-related relation options (Fabrízio de Royes Mello, Simon Riggs)

• ⇑ Upgrade to 9.6.2 released on 2017-02-09 - docs

  • Avoid constantly respawning the autovacuum launcher in a corner case (Amit Khandekar)

    This fix avoids problems when autovacuum is nominally off and there are some tables that require freezing, but all such tables are already being processed by autovacuum workers.

• ⇑ Upgrade to 10.1 released on 2017-11-09 - docs

  • Fix autovacuum's “work item” logic to prevent possible crashes and silent loss of work items (Álvaro Herrera)

• ⇑ Upgrade to 10.2 released on 2018-02-08 - docs

  • Fix crash in autovacuum when extended statistics are defined for a table but can't be computed (Álvaro Herrera)

• ⇑ Upgrade to 10.3 released on 2018-03-01 - docs

  • Avoid use of insecure search_path settings in pg_dump and other client programs (Noah Misch, Tom Lane)

    pg_dump, pg_upgrade, vacuumdb and other PostgreSQL-provided applications were themselves vulnerable to the type of hijacking described in the previous changelog entry; since these applications are commonly run by superusers, they present particularly attractive targets. To make them secure whether or not the installation as a whole has been secured, modify them to include only the pg_catalog schema in their search_path settings. Autovacuum worker processes now do the same, as well.

    In cases where user-provided functions are indirectly executed by these programs — for example, user-provided functions in index expressions — the tighter search_path may result in errors, which will need to be corrected by adjusting those user-provided functions to not assume anything about what search path they are invoked under. That has always been good practice, but now it will be necessary for correct behavior. (CVE-2018-1058)

• ⇑ Upgrade to 10.4 released on 2018-05-10 - docs

  • Reduce locking during autovacuum worker scheduling (Jeff Janes)

    The previous behavior caused drastic loss of potential worker concurrency in databases with many tables.

• ⇑ Upgrade to 11.3 released on 2019-05-09 - docs

  • Report correct relation name in autovacuum's pg_stat_activity display during BRIN summarize operations (Álvaro Herrera)

• ⇑ Upgrade to 12 released on 2019-10-03 - docs

  • Reduce the default value of autovacuum_vacuum_cost_delay to 2ms (Tom Lane)

    This allows autovacuum operations to proceed faster by default.

  • Allow modifications of system catalogs' options using ALTER TABLE (Peter Eisentraut)

    Modifications of catalogs' reloptions and autovacuum settings are now supported. (Setting allow_system_table_mods is still required.)

• ⇑ Upgrade to 12.3 released on 2020-05-14 - docs

  • Remove ill-considered skip of “redundant” anti-wraparound vacuums (Michael Paquier)

    This avoids a corner case where autovacuum could get into a loop of repeatedly trying and then skipping the same vacuum job.

  • Avoid failure if autovacuum tries to access a just-dropped temporary schema (Tom Lane)

    This hazard only arises if a superuser manually drops a temporary schema; which isn't normal practice, but should work.

• ⇑ Upgrade to 13 released on 2020-09-24 - docs

  • Allow inserts, not only updates and deletes, to trigger vacuuming activity in autovacuum (Laurenz Albe, Darafei Praliaskouski)

    Previously, insert-only activity would trigger auto-analyze but not auto-vacuum, on the grounds that there could not be any dead tuples to remove. However, a vacuum scan has other useful side-effects such as setting page-all-visible bits, which improves the efficiency of index-only scans. Also, allowing an insert-only table to receive periodic vacuuming helps to spread out the work of “freezing” old tuples, so that there is not suddenly a large amount of freezing work to do when the entire table reaches the anti-wraparound threshold all at once.

    If necessary, this behavior can be adjusted with the new parameters autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor, or the equivalent table storage options.

  • Allow EXPLAIN, auto_explain, autovacuum, and pg_stat_statements to track WAL usage statistics (Kirill Bychik, Julien Rouhaud)

• ⇑ Upgrade to 13.3 released on 2021-05-13 - docs

  • Disable the vacuum_cleanup_index_scale_factor parameter and storage option (Peter Geoghegan)

    The notion of tracking “stale” index statistics proved to interact badly with the autovacuum_vacuum_insert_threshold parameter, resulting in unnecessary full-index scans and consequent degradation of autovacuum performance. The latter mechanism seems superior, so remove the stale-statistics logic. The control parameter for that, vacuum_cleanup_index_scale_factor, will be removed entirely in v14. In v13, it remains present to avoid breaking existing configuration files, but it no longer does anything.

• ⇑ Upgrade to 14 released on 2021-09-30 - docs

  • Add per-index information to autovacuum logging output (Masahiko Sawada)

• ⇑ Upgrade to 14.1 released on 2021-11-11 - docs

  • Ensure that parallel VACUUM doesn't miss any indexes (Peter Geoghegan, Masahiko Sawada)

    A parallel VACUUM would fail to process indexes that are below the min_parallel_index_scan_size cutoff, if the table also has at least two indexes that are above that size. This could result in those indexes becoming corrupt, since they'd still contain references to any heap entries removed by the VACUUM; subsequent queries using such indexes would be likely to return rows they shouldn't. This problem does not affect autovacuum, since it doesn't use parallel vacuuming. However, it is advisable to reindex any manually-vacuumed tables that have the right mix of index sizes.

  • Allow the autovacuum launcher process to respond to pg_log_backend_memory_contexts() requests more quickly (Koyu Tanigawa)

• ⇑ Upgrade to 14.2 released on 2022-02-10 - docs

  • Build extended statistics for partitioned tables (Justin Pryzby)

    A previous bug fix disabled building of extended statistics for old-style inheritance trees, but it also prevented building them for partitioned tables, which was an unnecessary restriction. This change allows ANALYZE to compute values for statistics objects for partitioned tables. (But note that autovacuum does not process partitioned tables as such, so you must periodically issue manual ANALYZE on the partitioned table if you want to maintain such statistics.)

• source repo
• author info
• meta-info