public inbox for [email protected]
help / color / mirror / Atom feedDo we perform maintenance on pg_catalog tables
7+ messages / 3 participants
[nested] [flat]
* Do we perform maintenance on pg_catalog tables
@ 2025-01-02 19:49 srijith s <[email protected]>
2025-01-02 21:15 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: srijith s @ 2025-01-02 19:49 UTC (permalink / raw)
To: pgsql-admin
Hello PostgreSQL Experts,
Do we perform postgres maintenance (vacuum/analyze/reindex) on pg_catalog
tables?
Thanks in Advance
Sree
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Do we perform maintenance on pg_catalog tables
2025-01-02 19:49 Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
@ 2025-01-02 21:15 ` Ron Johnson <[email protected]>
2025-01-03 00:18 ` Re: Do we perform maintenance on pg_catalog tables Peter Gram <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2025-01-02 21:15 UTC (permalink / raw)
To: pgsql-admin
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!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Do we perform maintenance on pg_catalog tables
2025-01-02 19:49 Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
2025-01-02 21:15 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
@ 2025-01-03 00:18 ` Peter Gram <[email protected]>
2025-01-03 01:27 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Peter Gram @ 2025-01-03 00:18 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-admin
Hi Ron
How do I know if my AUTOVACUUM is Reasonably-tuned ?
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!
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Do we perform maintenance on pg_catalog tables
2025-01-02 19:49 Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
2025-01-02 21:15 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
2025-01-03 00:18 ` Re: Do we perform maintenance on pg_catalog tables Peter Gram <[email protected]>
@ 2025-01-03 01:27 ` Ron Johnson <[email protected]>
2025-01-03 01:57 ` Re: Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2025-01-03 01:27 UTC (permalink / raw)
To: pgsql-admin
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!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Do we perform maintenance on pg_catalog tables
2025-01-02 19:49 Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
2025-01-02 21:15 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
2025-01-03 00:18 ` Re: Do we perform maintenance on pg_catalog tables Peter Gram <[email protected]>
2025-01-03 01:27 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
@ 2025-01-03 01:57 ` srijith s <[email protected]>
2025-01-03 07:27 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: srijith s @ 2025-01-03 01:57 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Do we perform maintenance on pg_catalog tables
2025-01-02 19:49 Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
2025-01-02 21:15 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
2025-01-03 00:18 ` Re: Do we perform maintenance on pg_catalog tables Peter Gram <[email protected]>
2025-01-03 01:27 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
2025-01-03 01:57 ` Re: Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
@ 2025-01-03 07:27 ` Ron Johnson <[email protected]>
2025-01-03 14:57 ` Re: Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2025-01-03 07:27 UTC (permalink / raw)
To: pgsql-admin
Percentages are not a reliable metric when dealing with small tables, and
pg_statistic is a pretty small table.
Here, for example, are the stats for that table's only index. Oh, no! 57%
fragmentation! But it's *less than 7MB*. I've got much bigger fish to fry
than worrying about tiny tables that are regularly autovacuumed.
TAPb=# select * from pgstatindex('pg_statistic_relid_att_inh_index');
-[ RECORD 1 ]------+--------
version | 4
tree_level | 2
index_size | 6602752
root_block_no | 412
internal_pages | 4
leaf_pages | 796
empty_pages | 0
deleted_pages | 5
avg_leaf_density | 36.67
leaf_fragmentation | 57.04
TAPb=# select relname, last_vacuum, last_autovacuum
from pg_stat_all_tables
where relname = 'pg_statistic';
relname | last_vacuum | last_autovacuum
--------------+-------------+-------------------------------
pg_statistic | | 2025-01-02 23:40:17.647375-05
(1 row)
TAPb=# select * from pgstattuple('pg_statistic');
-[ RECORD 1 ]------+---------
table_len | 55762944
tuple_count | 117619
tuple_len | 40846337
tuple_percent | 73.25
dead_tuple_count | 1990
dead_tuple_len | 446536
dead_tuple_percent | 0.8
free_space | 12954980
free_percent | 23.23
On Thu, Jan 2, 2025 at 8:58 PM srijith s <[email protected]> wrote:
> 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
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Do we perform maintenance on pg_catalog tables
2025-01-02 19:49 Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
2025-01-02 21:15 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
2025-01-03 00:18 ` Re: Do we perform maintenance on pg_catalog tables Peter Gram <[email protected]>
2025-01-03 01:27 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
2025-01-03 01:57 ` Re: Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
2025-01-03 07:27 ` Re: Do we perform maintenance on pg_catalog tables Ron Johnson <[email protected]>
@ 2025-01-03 14:57 ` srijith s <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: srijith s @ 2025-01-03 14:57 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-admin
Thanks Ron ☺️
On Fri, Jan 3, 2025 at 2:27 AM Ron Johnson <[email protected]> wrote:
>
> Percentages are not a reliable metric when dealing with small tables, and
> pg_statistic is a pretty small table.
>
> Here, for example, are the stats for that table's only index. Oh, no! 57%
> fragmentation! But it's *less than 7MB*. I've got much bigger fish to
> fry than worrying about tiny tables that are regularly autovacuumed.
>
> TAPb=# select * from pgstatindex('pg_statistic_relid_att_inh_index');
> -[ RECORD 1 ]------+--------
> version | 4
> tree_level | 2
> index_size | 6602752
> root_block_no | 412
> internal_pages | 4
> leaf_pages | 796
> empty_pages | 0
> deleted_pages | 5
> avg_leaf_density | 36.67
> leaf_fragmentation | 57.04
>
> TAPb=# select relname, last_vacuum, last_autovacuum
> from pg_stat_all_tables
> where relname = 'pg_statistic';
> relname | last_vacuum | last_autovacuum
> --------------+-------------+-------------------------------
> pg_statistic | | 2025-01-02 23:40:17.647375-05
> (1 row)
>
> TAPb=# select * from pgstattuple('pg_statistic');
> -[ RECORD 1 ]------+---------
> table_len | 55762944
> tuple_count | 117619
> tuple_len | 40846337
> tuple_percent | 73.25
> dead_tuple_count | 1990
> dead_tuple_len | 446536
> dead_tuple_percent | 0.8
> free_space | 12954980
> free_percent | 23.23
>
>
> On Thu, Jan 2, 2025 at 8:58 PM srijith s <[email protected]> wrote:
>
>> 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
>>
>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
--
Thanks & Regards
Srijith Sudhakaran
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-01-03 14:57 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-02 19:49 Do we perform maintenance on pg_catalog tables srijith s <[email protected]>
2025-01-02 21:15 ` Ron Johnson <[email protected]>
2025-01-03 00:18 ` Peter Gram <[email protected]>
2025-01-03 01:27 ` Ron Johnson <[email protected]>
2025-01-03 01:57 ` srijith s <[email protected]>
2025-01-03 07:27 ` Ron Johnson <[email protected]>
2025-01-03 14:57 ` srijith s <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox