On Sun, Apr 19, 2020 at 9:25 AM Olivier Gautherot <ogautherot@xxxxxxxxxxxxx> wrote:
Hi Ankush,On Sun, Apr 19, 2020 at 9:16 AM Ankush Chawla <ankushchawla03@xxxxxxxxx> wrote:thanks for replyThen what would be way if we want some tables to be skipped for autovacuum.What do you think, what should we do?This will enable for older version to stay.VACUUM FREEZE means that you will put a lock on the table and release it after the job is completed. It is the most effective option but requires a kind of down time for all queries on the table.
VACUUM FREEZE does not lock the table. You're thinking of a VACUUM FULL, which completely rewrites the table. You can continue using a table while VACUUM FREEZE is running, but how effective it is is all dependent on what other transactions are running at the time the vacuum runs. But that's true for any vacuum run, not just freeze. A freeze forces vacuum to inspect every page and mark each tuple frozen, even if it had already been marked frozen in the past. It's a more aggressive action that isn't actually needed much anymore in modern postgres versions unless you're cleaning up a database that hadn't been having vacuum run properly in the past. But once you get things tuned properly, you should almost never need to run a forced FREEZE vacuum.
If you refer to "older version to stay", you may intend to access deleted data (i.e. when you run an UPDATE, you actually run an INSERT and a DELETE). Deleted data is not accessible. If you need to have access to historical data, I'd suggest creating a history table keeping track of all INSERT, UPDATE and DELETE.If you plan to reduce the impact of the VACUUM in terms of performance and lock-time, I would recommend, on the contrary, to run it often, so that it has a minimal amount of work to do. Disabling vacuum will increase your probability of filling your tables with unnecessary bloat. We evaluated the option of running a vacuum daily on a table with millions of rows and quickly came to the conclusion that it was a really bad idea.
On Sun, Apr 19, 2020 at 4:05 AM Dave Bolt <dave@xxxxxxxxxxxxxx> wrote:Hi Arnav,
Could be wrong here, in which case someone will jump in.
My understanding is that freeze only applies while doing the vacuum, and then only when doing a manual vacuum.
I looked at the documentation at https://www.postgresql.org/docs/current/sql-vacuum.html and https://www.postgresql.org/docs/current/routine-vacuuming.html to try to confirm, but did feel a little confused.
Hope this helps a bit.
Dave
If you are on at least version 9.6 of PostgreSQL, when a page has all of its tuples marked as FROZEN, autovacuum will automatically be able to skip over those pages. So if you have old tables that get no updates/deletes anymore, autovacuum should be able to go over them very quickly the next time it comes around to them to get things in order to prevent transaction id exhaustion.
I've written a bit more about managing txid exhaustion here if that is your major concern - https://info.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql
You can also tune autovacuum on a per-table basis if needed. I would not recommend ever turning off autovacuum unless you've worked out the exact schedule of a manual vacuums needed for your environment. You simply cannot stop vacuuming all-together on a database that has active writes happening - https://www.keithf4.com/per-table-autovacuum-tuning/
Keith Fiske
