Re: Autovacuum on sys tables

Hello Inzamam,

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