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
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.
On Mon, Jul 27, 2020 at 5:35 PM Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx> wrote:
Thank you so much Keith.We will try manual vacuum as you suggested.I read something like this but did not give the right attention to it.About pgstattuple was a bit confusing its free area information when we compared to pg_freespace.
Can you please explain this difference?
De: Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx>
Enviado: segunda-feira, 27 de julho de 2020 17:59
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 4:36 PM Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx> wrote:
Thank you, David.So can i assume that autovacuum only release dead tuples when it ends its execution?
Autovacuum is not finishing and is running for days.We did not find a parameter to limit autovacuum executing time.Do you have any recomendation?
Thank you.Sidney.
De: David G. Johnston <david.g.johnston@xxxxxxxxx>
Enviado: segunda-feira, 27 de julho de 2020 17:17
Para: Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still growsOn Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx> wrote:
Hi, we have a 9.6.5 postgresAutovacuum is set with default parameters and is running on this table for days without naping or stopping.
3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound)
(1 rows)
Its probably best avoid drawing conclusions here until the anti-wraparound vacuum has completed and you’ve updated to the latest minor version of PostgreSQL.
David J.
Correct, the tuples will only be released once the autovacuum transaction has committed.
If it's been running for days without being committed, I would recommend cancelling it and immediately running a manual vacuum before it starts up again. You'll have to be quick and double-check that your manual vacuum is the one running and not getting blocked by the wraparound vac starting up again. You can check this by looking at pg_stat_activity. You may see the wraparound vacuum try and start up again as well, but it should be blocked by yours and typically cancel out once yours finishes.A manual vacuum runs with different, more aggressive settings than autovacuum. So you should see this finish much sooner. May still take a few hours, but it shouldn't be much longer. The biggest culprit with autovacuum taking a long time on a given table, besides there being a lot of bloat, is typically autovacuum_vacuum_cost_delay, which can cause autovacuum to pause if autovacuum_vacuum_cost_limit is reached. With a manual vacuum, this delay is disabled (it uses vaccum_cost_delay which is by default 0). You can see more in this section of the documentation - https://www.postgresql.org/docs/9.6/runtime-config-autovacuum.html
You may want to adjust the autovac cost delay (lower it from 2 to 1 maybe). I believe you can do this on a per-table basis vs changing it for the entire database. I would not recommend completely disabling the delay until you at least get your system fully vacuumed and in a better state. I'd also recommend some more aggressive vacuuming be configured for your given table as well, so it doesn't accumulate so many dead rows before vacuum is able to run. I wrote about how to do this here - https://www.keithf4.com/per-table-autovacuum-tuning/
After it finishes running, in addition to upgrading to the latest version of 9.6, I would recommend checking the table & indexes for bloat. You can use the pgstattuple contrib module or this script which makes it a little more user-friendly https://github.com/keithf4/pg_bloat_check
--
