Re: pgstattuple free_percent to high

Hello,
anyone can help on this matter, Alessandro highlighted?

Thanks a lot,
Nicola

2017-12-16 13:40 GMT+01:00 Alessandro Aste <alessandro(dot)aste(at)gtt(dot)net>:

> 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.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
>