Recently someone asked on Slack about what is transaction wraparound. Full answer is a bit too much for slack reply, but I can try to explain it in here.
Tag: vacuum
Waiting for PostgreSQL 17 – Allow \watch queries to stop on minimum rows returned
On 29th of August 2023, Daniel Gustafsson committed patch:
Allow \watch queries to stop on minimum rows returned When running a repeat query with \watch in psql, it can be helpful to be able to stop the watch process when the query no longer returns the expected amount of rows. An example would be to watch for the presence of a certain event in pg_stat_activity and stopping when the event is no longer present, or to watch an index creation and stop when the index is created. This adds a min_rows=MIN parameter to \watch which can be set to a non-negative integer, and the watch query will stop executing when it returns less than MIN rows. Author: Greg Sabino Mullane <htamfids@gmail.com> Reviewed-by: Michael Paquier <michael@paquier.xyz> Reviewed-by: Daniel Gustafsson <daniel@yesql.se> Discussion: https://postgr.es/m/CAKAnmmKStATuddYxP71L+p0DHtp9Rvjze3XRoy0Dyw67VQ45UA@mail.gmail.com
Continue reading Waiting for PostgreSQL 17 – Allow \watch queries to stop on minimum rows returned
When to use VACUUM FULL
Well, the short answer is: NEVER. But given how often I see people ask about it, I'll try to expand my answer a bit…
Waiting for PostgreSQL 16 – Add grantable MAINTAIN privilege and pg_maintain role.
On 14th of December 2022, Jeff Davis committed patch:
Add grantable MAINTAIN privilege and pg_maintain role. Allows VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, CLUSTER, and LOCK TABLE. Effectively reverts 4441fc704d. Instead of creating separate privileges for VACUUM, ANALYZE, and other maintenance commands, group them together under a single MAINTAIN privilege. Author: Nathan Bossart Discussion: https://postgr.es/m/20221212210136.GA449764@nathanxps13 Discussion: https://postgr.es/m/45224.1670476523@sss.pgh.pa.us
Continue reading Waiting for PostgreSQL 16 – Add grantable MAINTAIN privilege and pg_maintain role.
Nothing compares ….
Warsaw PostgreSQL Users Group (facebook , meetup) uploaded a song to youtube: Nothing Compares To VACUUM 🙂
Waiting for PostgreSQL 13 – Allow vacuum command to process indexes in parallel.
On 20th of January 2020, Amit Kapila committed patch:
Allow vacuum command to process indexes in parallel. This feature allows the vacuum to leverage multiple CPUs in order to process indexes. This enables us to perform index vacuuming and index cleanup with background workers. This adds a PARALLEL option to VACUUM command where the user can specify the number of workers that can be used to perform the command which is limited by the number of indexes on a table. Specifying zero as a number of workers will disable parallelism. This option can't be used with the FULL option. Each index is processed by at most one vacuum process. Therefore parallel vacuum can be used when the table has at least two indexes. The parallel degree is either specified by the user or determined based on the number of indexes that the table has, and further limited by max_parallel_maintenance_workers. The index can participate in parallel vacuum iff it's size is greater than min_parallel_index_scan_size. Author: Masahiko Sawada and Amit Kapila Mahendra Singh and Sergei Kornilov Discussion: https://postgr.es/m/CAD21AoDTPMgzSkV4E3SFo1CH_x50bf5PqZFQf4jmqjk-C03BWg@mail.gmail.com https://postgr.es/m/CAA4eK1J-VoR9gzS5E75pcD-OH0mEyCdp8RihcwKrcuw7J-Q0+w@mail.gmail.com
Continue reading Waiting for PostgreSQL 13 – Allow vacuum command to process indexes in parallel.
Waiting for PostgreSQL 12 – Allow VACUUM to be run with index cleanup disabled.
On 4th of April 2019, Robert Haas committed patch:
Allow VACUUM to be run with index cleanup disabled. This commit adds a new reloption, vacuum_index_cleanup, which controls whether index cleanup is performed for a particular relation by default. It also adds a new option to the VACUUM command, INDEX_CLEANUP, which can be used to override the reloption. If neither the reloption nor the VACUUM option is used, the default is true, as before. Masahiko Sawada, reviewed and tested by Nathan Bossart, Alvaro Herrera, Kyotaro Horiguchi, Darafei Praliaskouski, and me. The wording of the documentation is mostly due to me. Discussion: http://postgr.es/m/CAD21AoAt5R3DNUZSjOoXDUY=naYPUOuffVsRzuTYMz29yLzQCA@mail.gmail.com
Continue reading Waiting for PostgreSQL 12 – Allow VACUUM to be run with index cleanup disabled.
Waiting for 9.6 – Add simple VACUUM progress reporting.
On 15th of March, Robert Haas committed patch:
Add simple VACUUM progress reporting. There's a lot more that could be done here yet - in particular, this reports only very coarse-grained information about the index vacuuming phase - but even as it stands, the new pg_stat_progress_vacuum can tell you quite a bit about what a long-running vacuum is actually doing. Amit Langote and Robert Haas, based on earlier work by Vinayak Pokale and Rahila Syed.
Continue reading Waiting for 9.6 – Add simple VACUUM progress reporting.
Waiting for 9.6 – Add a generic command progress reporting facility.
On 9th of March, Robert Haas committed patch:
Add a generic command progress reporting facility. Using this facility, any utility command can report the target relation upon which it is operating, if there is one, and up to 10 64-bit counters; the intent of this is that users should be able to figure out what a utility command is doing without having to resort to ugly hacks like attaching strace to a backend. As a demonstration, this adds very crude reporting to lazy vacuum; we just report the target relation and nothing else. A forthcoming patch will make VACUUM report a bunch of additional data that will make this much more interesting. But this gets the basic framework in place. Vinayak Pokale, Rahila Syed, Amit Langote, Robert Haas, reviewed by Kyotaro Horiguchi, Jim Nasby, Thom Brown, Masahiko Sawada, Fujii Masao, and Masanori Oyama.
Continue reading Waiting for 9.6 – Add a generic command progress reporting facility.
Waiting for 9.5 – vacuumdb: enable parallel mode
On 23rd of January, Alvaro Herrera committed patch:
vacuumdb: enable parallel mode This mode allows vacuumdb to open several server connections to vacuum or analyze several tables simultaneously. Author: Dilip Kumar. Some reworking by Álvaro Herrera Reviewed by: Jeff Janes, Amit Kapila, Magnus Hagander, Andres Freund
Continue reading Waiting for 9.5 – vacuumdb: enable parallel mode