From: | Thomas Boussekey <thomas(dot)boussekey(at)gmail(dot)com> |
---|---|
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 |
Date: | 2022-12-19 08:31:27 |
Message-ID: | CALUeYmcJK4G1ygE6mVchZex=GJJg_=t+yTNjmRgsQgkA2mwUcw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Le lun. 19 déc. 2022 à 07:12, Inzamam Shafiq <inzamam(dot)shafiq(at)hotmail(dot)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(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>
> 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 | li jie | 2022-12-19 10:02:17 | Re: Support logical replication of DDLs |
Previous Message | Inzamam Shafiq | 2022-12-19 06:12:29 | Re: Autovacuum on sys tables |