RE: pgstattuple free_percent to high

Stephen, Rene - Thanks!

Our experience teach us that above 20% of free space performance start to seriously deteriorate. I'm not sure if this is related to index or table fragmentation. We'll do our homework and we'll try to discover more.

However we have identified a process potentially causing the free space spike high but it's not related to long running transactions modifying (update, insert) on those tables. What DEV is currently doing is create a sort of de-normalized cache table like this:
* tablename is the table with the free space issue

INSERT INTO cache_table
SELECT *, table2.<col>, ...table<N>.col FROM tablename
JOIN table2 on ...
...
..
JOIN table<N> on....

So no updates/inserts/deletes to tablename are involved but that query may run for 15 (this is the AVG more or less) minutes holding an ACCESS SHARE lock (I guess).

We are wondering why we have that spike if we are not modifying the relation and what we can suggest DEV from the query/db perspective to alleviate/fix the issue.

Again, thanks so much.

Thanks and kind regards
Aste - alessandro(dot)aste(at)gtt(dot)net

-----Original Message-----
From: Stephen Frost [mailto:sfrost(at)snowman(dot)net]
Sent: 14 December 2017 16:13
To: Nicola Contu <nicola(dot)contu(at)gmail(dot)com>
Cc: Rene Romero Benavides <rene(dot)romero(dot)b(at)gmail(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org; Alessandro Aste <alessandro(dot)aste(at)gtt(dot)net>
Subject: 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