Thread

1. Autovacuum on sys tables

   Inzamam Shafiq <inzamam.shafiq@hotmail.com> — 2022-12-17T07:15:46Z

   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
   

2. Re: Autovacuum on sys tables

   Thomas Boussekey <thomas.boussekey@gmail.com> — 2022-12-18T11:01:52Z

   Hello Inzamam,
   
   Le sam. 17 déc. 2022 à 08:16, Inzamam Shafiq <inzamam.shafiq@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*
   >
   

3. Re: Autovacuum on sys tables

   Inzamam Shafiq <inzamam.shafiq@hotmail.com> — 2022-12-19T06:12:29Z

   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.boussekey@gmail.com>
   Sent: Sunday, December 18, 2022 4:01 PM
   To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
   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 <inzamam.shafiq@hotmail.com<mailto:inzamam.shafiq@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
   

4. Re: Autovacuum on sys tables

   Thomas Boussekey <thomas.boussekey@gmail.com> — 2022-12-19T08:31:27Z

   Hi,
   
   Le lun. 19 déc. 2022 à 07:12, Inzamam Shafiq <inzamam.shafiq@hotmail.com> 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 <thomas.boussekey@gmail.com>
   > *Sent:* Sunday, December 18, 2022 4:01 PM
   > *To:* Inzamam Shafiq <inzamam.shafiq@hotmail.com>
   > *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 <inzamam.shafiq@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*
   >
   >
   

5. Re: Autovacuum on sys tables

   postgres@arcict.com — 2023-01-21T15:58:30Z

   This is a test.
   
   Apologies but 19/12 we are no longer receiving the list mails
   
   
   
   
   

6. Re: Autovacuum on sys tables

   Adrian Klaver <adrian.klaver@aklaver.com> — 2023-01-21T16:15:09Z

   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.klaver@aklaver.com