pgstattuple free_percent to high

From: Nicola Contu <nicola(dot)contu(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Cc: Alessandro(dot)aste(at)gtt(dot)net
Subject: pgstattuple free_percent to high
Date: 2017-12-13 15:49:04
Message-ID: CAMTZZh3vNp_2LBkC8HHHKrSQ0J0nD8w23RdiKuuQFmKZW79ovA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2017-12-13 15:54:59 Re: Dependency tree to tie type/function deps to a table
Previous Message Olga Lytvynova-Bogdanova 2017-12-13 15:22:41 Varbit and toast