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

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

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

This is a test.

Apologies but 19/12 we are no longer receiving the list mails

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