Re: Vacuuming strategy - Mailing list pgsql-general

Why do you have a 4 minute timeout?  That seems counter-productive.

Oh, Is it less or more?

 You delete a bunch of tuples every night, so of course a vacuum full after that is going to return a lot of space.  But that space is probably just going to be needed again the next day.  If you don't do the vacuum full, does the *peak* space keep increasing, or does it stabilize?

I haven't tested to see if the space keeps on increasing.

I did pgstattupe() on one of the tables:

managed_target_stats=# select * from pgstattuple('xyz');

 table_len  | tuple_count | tuple_len  | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent

------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------

 5642526720 |    18880283 | 4042874387 |         71.65 |           122037 |       21495560 |               0.38 | 1239598044 |        21.97

This is one of Tables1 tables and this is after running for a week or so with the default autovacuum settings. The dead_tuple_percent look good. But the free_percent looks high. Is this normal?

Also when I enabled autovacuum logs, I saw the autovacuums triggering very rarely. May be that's the reason for too much free space? I am going to try with the following settings:

log_autovacuum_min_duration = 0

autovacuum_vacuum_scale_factor = 0

autovacuum_vacuum_threshold = 40000