public inbox for [email protected]
help / color / mirror / Atom feedFrom: srijith s <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-admin <[email protected]>
Subject: Re: Do we perform maintenance on pg_catalog tables
Date: Thu, 2 Jan 2025 20:57:24 -0500
Message-ID: <CACReZzyFn=EbgxM_aEv=EmbfJHs6eodrgKKJj9XOXset6XusGw@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaD7wvwfMJ9mi=RVAF9KwtMf5_9ksz8xdgp_rD1pTwhGfA@mail.gmail.com>
References: <CACReZzxJ0WfcJC9APM4so63aUP0JfpO0+SEHfeHu2i1vTmRB5A@mail.gmail.com>
<CANzqJaBj=Js=nu==8PafL_Vk729_C+TixPgWm+7vSm8Qa1xS0Q@mail.gmail.com>
<CAJ=80GU6HZE9XKc4vysXC21+Ou3svsN-q5_k12=fEL0_Up=6Dg@mail.gmail.com>
<CANzqJaD7wvwfMJ9mi=RVAF9KwtMf5_9ksz8xdgp_rD1pTwhGfA@mail.gmail.com>
Thanks Much for the explanation Ron
I used a query (found on the internet) to find the bloat on all tables in
the database, it showed 72% bloat_pct on *pg_catalog.pg_statistic* table,
i ran a full vacuum on this table, instead of bringing the bloat_pct down
it changed the bloat_pct 91%. Maybe I am not using the right query ?
Query I used to find the bloat
https://medium.com/@kemalozz/understanding-bloat-in-postgresql-and-how-to-manage-it-0ca1d6db0c78
On Thu, Jan 2, 2025 at 8:28 PM Ron Johnson <[email protected]> wrote:
> On Thu, Jan 2, 2025 at 7:18 PM Peter Gram <[email protected]> wrote:
>
>> Hi Ron
>>
>> How do I know if my AUTOVACUUM is Reasonably-tuned ?
>>
>
> If your queries are as fast as they can be, given proper index support. 😉
>
> Specifically, I'd look at pg_stat_all_tables
> columns relname, n_dead_tup, n_mod_since_analyze, GREATEST(last_vacuum,
> last_autovacuum) and GREATEST(last_analyze, last_autoanalyze).
>
> Run your system query a few times with EXPLAIN (BUFFERS), then "vacuumdb
> --all", and then run the queries again with EXPLAIN (BUFFERS).
>
> If they're the same speed, then your AUTOVACUUM settings are either Good
> Enough, or you got lucky and the tables were recently analyzed. If they're
> faster, then you need to reduce the AUTOVACUUM config variables.
>
> These are *my* settings for *our* workload. YMMV.
> autovacuum = on
> autovacuum_max_workers = 4
> autovacuum_vacuum_scale_factor = 0.015
> autovacuum_vacuum_threshold = 50
> autovacuum_vacuum_cost_delay = 4ms
> autovacuum_vacuum_cost_limit = 1000
> autovacuum_analyze_scale_factor = 0.015
> autovacuum_analyze_threshold = 50
>
>
>> Med venlig hilsen
>>
>> Peter Gram
>> Sæbyholmsvej 18
>> 2500 Valby
>>
>> Mobile: (+45) 5374 7107
>> Email: [email protected]
>>
>>
>>
>> On Fri, 3 Jan 2025 at 06.45, Ron Johnson <[email protected]> wrote:
>>
>>> On Thu, Jan 2, 2025 at 2:50 PM srijith s <[email protected]> wrote:
>>>
>>>>
>>>> Hello PostgreSQL Experts,
>>>>
>>>> Do we perform postgres maintenance (vacuum/analyze/reindex) on
>>>> pg_catalog tables?
>>>>
>>>
>>> Reasonably-tuned AUTOVACUUM config parameters should handle that for you.
>>>
>>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
--
Thanks & Regards
Srijith Sudhakaran
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Do we perform maintenance on pg_catalog tables
In-Reply-To: <CACReZzyFn=EbgxM_aEv=EmbfJHs6eodrgKKJj9XOXset6XusGw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox