From: | Inzamam Shafiq <inzamam(dot)shafiq(at)hotmail(dot)com> |
---|---|
To: | Thomas Boussekey <thomas(dot)boussekey(at)gmail(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Autovacuum on sys tables |
Date: | 2022-12-19 06:12:29 |
Message-ID: | AM9P251MB0330F9629060DF4A146F337498E59@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks Thomas for the response,
It means if I run VACUUM ANALYZE on pg_attribute or pg_class table, it is completely safe? Can you please also please confirm what is meant by "mid-level" vacuum?
Regards,
Inzamam Shafiq
Sr. DBA
________________________________
From: Thomas Boussekey <thomas(dot)boussekey(at)gmail(dot)com>
Sent: Sunday, December 18, 2022 4:01 PM
To: Inzamam Shafiq <inzamam(dot)shafiq(at)hotmail(dot)com>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Autovacuum on sys tables
Hello Inzamam,
Le sam. 17 déc. 2022 à 08:16, Inzamam Shafiq <inzamam(dot)shafiq(at)hotmail(dot)com<mailto:inzamam(dot)shafiq(at)hotmail(dot)com>> a écrit :
Dear Experts,
Hope you are doing well.
I have a question that autovacuum is running on sys tables like pg_class, pg_attribute, is it a normal thing? Further, what is dead tuples are not removed from Autovacuum, can we remove the dead tuples using FULL VACUUMM or pg_repack on sys tables?
pg_repack cannot run on system tables, it will FAIL with an explicit error message explaining the limitation.
Each time you perform DDL operations (CREATE, DROP, ALTER), rows are inserted/updated or deleted into the system tables : pg_class, pg_attribute ...
Autovacuum operations perform "low-level" operations, it can be interesting to perform "middle-level" vacuum with VACUUM ANALYZE... that is not blocking, but will be more a resource-consuming operation than autovacuum.
Performing VACUUM FULL operation will block access to these pillar tables of your database.
If your application/users can handle it, go ahead!
At work on this kind of operation, I set a statement_timeout, in order to properly stop the process if it is over a defined amount of time.
Hope this helps,
Thomas
Thank you.
Regards,
Inzamam Shafiq
Sr. DBA
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Boussekey | 2022-12-19 08:31:27 | Re: Autovacuum on sys tables |
Previous Message | Thomas Boussekey | 2022-12-18 11:01:52 | Re: Autovacuum on sys tables |