Re: Autovacuum on sys tables
On 1/21/23 07:58, Marc wrote: This is a test. Received. Apologies but 19/12 we are no longer receiving the list mails -- Adrian Klaver adrian.kla...@aklaver.com
Re: Autovacuum on sys tables
This is a test. Apologies but 19/12 we are no longer receiving the list mails
Re: Autovacuum on sys tables
Hi, Le lun. 19 déc. 2022 à 07:12, Inzamam Shafiq a écrit : > 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? > To clarify my last message, VACUUM ANALYZE is a trade-off between autovacuum, that can be considered as a lazy non-blocking operation, and VACUUM FULL (eager & blocking one). The `mid-level` in my previous mail was used to pinpoint an intermediate blacking & resource consumption situation. VACUUM ANALYZE will: - remove dead tuples definition - refresh statistics (can improve execution plans for queries) Have a look at this website, it explains that better than me ;) : https://www.interdb.jp/pg/pgsql06.html > > Regards, > > *Inzamam Shafiq* > *Sr. DBA* > -- > *From:* Thomas Boussekey > *Sent:* Sunday, December 18, 2022 4:01 PM > *To:* Inzamam Shafiq > *Cc:* pgsql-general@lists.postgresql.org < > pgsql-general@lists.postgresql.org> > *Subject:* Re: Autovacuum on sys tables > > Hello Inzamam, > > Le sam. 17 déc. 2022 à 08:16, Inzamam Shafiq > 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* > >
Re: Autovacuum on sys tables
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 Sent: Sunday, December 18, 2022 4:01 PM To: Inzamam Shafiq Cc: pgsql-general@lists.postgresql.org Subject: Re: Autovacuum on sys tables Hello Inzamam, Le sam. 17 déc. 2022 à 08:16, Inzamam Shafiq mailto:inzamam.sha...@hotmail.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
Re: Autovacuum on sys tables
Hello Inzamam, Le sam. 17 déc. 2022 à 08:16, Inzamam Shafiq 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* >
Autovacuum on sys tables
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? Thank you. Regards, Inzamam Shafiq Sr. DBA
