6 ms·

can you elaborate?

anarazel 6y ago
> can you elaborate? On what exactly? The freeze map bit I referenced above is the following commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fd31cd265138019dcccc9b5fe53043670898bc9f https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Commit a892234f830e832110f63fc0a2afce2fb21d1584 gave us enough infrastructure to avoid vacuuming pages where every tuple on the page is already frozen. So, replace the notion of a scan_all or whole-table vacuum with the less onerous notion of an "aggressive" vacuum, which will pages that are all-visible, but still skip those that are all-frozen. This should greatly reduce the cost of anti-wraparound vacuuming on large clusters where the majority of data is never touched between one cycle and the next, because we'll no longer have to read all of those pages only to find out that we don't need to do anything with them. This means that an anti-wraparound vacuum (automatically started, even when autovacuum is disabled) is cheaper than it used to be. Still not necessarily cheap, as indexes still need to be scanned (but see below). Some of the additional changes were (reverse chronological order): * 2020-03-28 - "Trigger autovacuum based on number of INSERTs" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b07642dbcd8d5de05f0ee1dbb72dd6760dd30436 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This is important because it will reduce the cost of an eventual index wraparound, as there will be less work in a later anti-wraparound vacuum * 2020-01-20 - "Allow vacuum command to process indexes in parallel." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=40d964ec997f64227bc0ff5e058dc4a5770a70a9 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... As index processing can be expensive (no equivalent to scanning only changed parts of table), processing them in parallel can greatly reduce the time for a vacuum. Note that this isn't yet done by autovacuum. * 2019-04-04 - "Allow VACUUM to be run with index cleanup disabled." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a96c41feec6b6616eb9d5baee9a9e08c20533c38 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This can be extremely useful for manual vacuum when getting close to a wraparound, since the index processing step is not necessary to stave of wraparound. * 2018-04-04 - "Skip full index scan during cleanup of B-tree indexes when possible" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=857f9c36cda520030381bd8c2af20adf0ce0e1d4 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This can make vacuums for pretty clean tables vastly cheaper. There's also a number of changes that make indexes smaller / less likely to bloat. That in turn makes vacuuming more efficient. E.g. * https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0d861bbb702f8aa05c2a4e3f1650e7e8df8c8c27 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... * https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dd299df8 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Edit: formatting (gah, why is HN formatting so limited)
- craigkerstiens 6y ago
  Just chiming in to say, Andres above can answer this far more in detail than I can. I suspect most people that do hit this today 1. have not nearly as a bad pain from it and 2. recover in generally much more quickly than previously. Today vs. say Postgres 9.6 is a huge difference.
- shay_ker 6y ago
  awesome, that's an above and beyond answer! i really appreciate the detail!

can you elaborate?

> can you elaborate? On what exactly? The freeze map bit I referenced above is the following commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fd31cd265138019dcccc9b5fe53043670898bc9f https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Commit a892234f830e832110f63fc0a2afce2fb21d1584 gave us enough infrastructure to avoid vacuuming pages where every tuple on the page is already frozen. So, replace the notion of a scan_all or whole-table vacuum with the less onerous notion of an "aggressive" vacuum, which will pages that are all-visible, but still skip those that are all-frozen. This should greatly reduce the cost of anti-wraparound vacuuming on large clusters where the majority of data is never touched between one cycle and the next, because we'll no longer have to read all of those pages only to find out that we don't need to do anything with them. This means that an anti-wraparound vacuum (automatically started, even when autovacuum is disabled) is cheaper than it used to be. Still not necessarily cheap, as indexes still need to be scanned (but see below). Some of the additional changes were (reverse chronological order): * 2020-03-28 - "Trigger autovacuum based on number of INSERTs" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b07642dbcd8d5de05f0ee1dbb72dd6760dd30436 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This is important because it will reduce the cost of an eventual index wraparound, as there will be less work in a later anti-wraparound vacuum * 2020-01-20 - "Allow vacuum command to process indexes in parallel." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=40d964ec997f64227bc0ff5e058dc4a5770a70a9 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... As index processing can be expensive (no equivalent to scanning only changed parts of table), processing them in parallel can greatly reduce the time for a vacuum. Note that this isn't yet done by autovacuum. * 2019-04-04 - "Allow VACUUM to be run with index cleanup disabled." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a96c41feec6b6616eb9d5baee9a9e08c20533c38 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This can be extremely useful for manual vacuum when getting close to a wraparound, since the index processing step is not necessary to stave of wraparound. * 2018-04-04 - "Skip full index scan during cleanup of B-tree indexes when possible" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=857f9c36cda520030381bd8c2af20adf0ce0e1d4 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... This can make vacuums for pretty clean tables vastly cheaper. There's also a number of changes that make indexes smaller / less likely to bloat. That in turn makes vacuuming more efficient. E.g. * https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0d861bbb702f8aa05c2a4e3f1650e7e8df8c8c27 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... * https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dd299df8 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Edit: formatting (gah, why is HN formatting so limited)

craigkerstiens 6y ago
Just chiming in to say, Andres above can answer this far more in detail than I can. I suspect most people that do hit this today 1. have not nearly as a bad pain from it and 2. recover in generally much more quickly than previously. Today vs. say Postgres 9.6 is a huge difference.
shay_ker 6y ago
awesome, that's an above and beyond answer! i really appreciate the detail!