Hello,
We are running postgres 9.6.6 on centos 7.
We have a large DB (180GB) with about 1200 tables.
We have autovacuum set with default values and we are seeing that for some tables the free percent goes really high (51%) and we need to daily full vacuum those tables.
dbanme=# SELECT * FROM pgstattuple('tablename');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------
2119548928 | 526658 | 1023569149 | 48.29 | 0 | 0 | 0 | 1083485292 | 51.12
(1 row)
I guess this is because of long queries but I'm not really sure.
Do you know how to avoid this problem and what can cause it?
Do you think that increasing the autovacuum settings for those tables would alleviate the issue?
Thanks,
Nicola