RE: How can i be certain autovacuum is causing reuse if table still grows

[Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx>]

Thank you for the explanation Keith.

Pgstattuple runs for 8 hours approximately before printing its result.

and pg_freespace runs for "only" 16 minutes so we presumed really that pgstattuple scans the relation and brings more accurate information.

The strange fact is that pg_freespace result is changing from day to day, even without vacuum or analyze finishing.

At day one we start monitoring it was saying it has 108GB and kept changing each day.

And the worse fact is the different values for free space: pgstattupple with 2.3TB free VS pg_freespace with 96GB.

But i just killed autovacuum and started a manual vacuum verbose.

and 20 seconds later a autovacuum started.

it seems they are both running together.

Both sessions gets row exclusive locks.

I think it is better disabling autovacuum, do you agree?

---

De: Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx>
Enviado: segunda-feira, 27 de julho de 2020 19:21
Para: Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx>
Cc: David G. Johnston <david.g.johnston@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows

 

On Mon, Jul 27, 2020 at 6:19 PM Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx> wrote:

As the documentation for pg_freespacemap says, it's values are not exact and they may not be up to date because you are viewing the statistics that were gathered during the last analyze, not the actual freespace map

 

Sorry, just wanted to clarify this. You are looking at the actual freespace map. It's that the values actually stored in it are not exact and based off of statistics gathering

 

https://www.postgresql.org/docs/9.6/pgfreespacemap.html

When you run pgstattuple, it is actually scanning the target object. Also note that scanning a table does not also scan its indexes, those must be done individually. So the free space reported by pgstattuple should be the actual free space, given as both byte size and % of the whole object. 

pg_freespacemap is used by the planner and several other internal parts of PG, so checking it must be quick. pgstattuple can take a while to run on larger objects, but it will give you 100% accurate results as of the time it ran. This is why I prefer it when checking for bloat since many of the queries you see out there also try and base their results on stats. While the query runs quick, I've often seen them be wildly wrong.

Also, I would not recommend partitioning simply to improve vacuuming. Especially if extensive tuning hasn't been tried first. Most times you can get per-table tuning working well enough to get autovacuum running properly. Especially on 9.6 and even more-so on PG11, where autovacuum has itself been improved.

-- 

Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com