Re: Do we perform maintenance on pg_catalog tables

On Thu, Jan 2, 2025 at 7:18 PM Peter Gram <peter(dot)m(dot)gram(at)gmail(dot)com> 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: peter(dot)m(dot)gram(at)gmail(dot)com
>
>
>
> On Fri, 3 Jan 2025 at 06.45, Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>> On Thu, Jan 2, 2025 at 2:50 PM srijith s <udbdoctor(at)gmail(dot)com> 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!