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
isrelated 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
transactionsmodifying (update, insert) on those tables. What DEV is currently doing is create a sort of de-normalized
cachetable  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)
minutesholding 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/dbperspective to alleviate/fix the issue. 

Again, thanks so much.



Thanks and kind regards
Aste - alessandro.aste@gtt.net

-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: 14 December 2017 16:13
To: Nicola Contu <nicola.contu@gmail.com>
Cc: Rene Romero Benavides <rene.romero.b@gmail.com>; pgsql-general@lists.postgresql.org; Alessandro Aste
<alessandro.aste@gtt.net>
Subject: Re: pgstattuple free_percent to high

Greetings Nicola,

* Nicola Contu (nicola.contu@gmail.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
lockthe 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
therelation and the end of the relation will be all dead tuples, allowing the truncate to happen.  If you have tuples
atthe 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
eitherINSERTs or UPDATEs) have a place to go that doesn't require extending the relation (which requires an additional
lockas well as some additional work).  As for how much free space is good to have and how much is too much depends on
thespecific workload. 

Thanks!

Stephen