Re: pgstattuple free_percent to high

Greetings Nicola,

* Nicola Contu (nicola(dot)contu(at)gmail(dot)com) wrote:
> I think tuning the autovacuum settings may increase performances and remove
> dead_tuples but as far as I know, the autovacuum runs a vacuum analyze.
> The vacuum analyze won't touch the free_percent of the table.

That's not entirely accurate. If all of the free space is at the *end*
of the relation then autovacuum will attempt to lock the relation and
truncate the table to give that free space back to the OS.

On a table where all of the rows are regularly updated, eventually the
"live" data should end up towards the front of the relation and the end
of the relation will be all dead tuples, allowing the truncate to
happen. If you have tuples at the end of the relation that aren't ever
updated but they're "live" then we won't be able to truncate.

The pg_freespacemap extension can be useful to see where the free space
is in the relation.

There are a few tools out there that aren't part of core PostgreSQL that
you could consider using such as pg_repack and pg_squeeze.

> So I'm trying to find a way to adjust the free percent for some tables
> without doing a manually full vacuum.
> We are now monitoring the free percent, so we may find the part of the code
> that can increase that value, but was wondering if there is anything on the
> postgres side to resolve this problem.

Having some free space in the relation isn't a 'problem' and is a good
thing because it means that new rows (from either INSERTs or UPDATEs)
have a place to go that doesn't require extending the relation (which
requires an additional lock as well as some additional work). As for
how much free space is good to have and how much is too much depends on
the specific workload.

Thanks!

Stephen