Re: Vacuum Issues

On Thu, Mar 26, 2020 at 8:05 PM Jerry Sievers <gsievers19(at)comcast(dot)net>
wrote:

> Darron Harrison <darron(at)realtyserver(dot)com> writes:
>
> > I am currently seeing three WAL sender processes on the master, and
> > no stale replication connections on the slaves.
> >
> > To clarify, are you saying there should be two WAL sender processes
> > for each slave for a total of six?
> >
>
> Silly question...
>
> Have you verified that autovac is actually running? launcher still
> alive, not malconfig'd as to do nothing... etc?
>
> Silly question #2; supposing autovac is trying to do as intended, have
> you inspected the logs to insure that it's not erroring out on one or
> more tables, catalogs, indexes... etc?
>
> I've reade most of this thread and not seeing any indication if the
> problem is confined to just one object or several.
>
> FWIW
>
>
>
>
Also, to clarify, are you seeing actual dead rows not being recycled or
just free space not being recovered? These are two completely different
things. Actual dead tuples will show up for tables in the
pg_stat_all_tables system catalog under the n_dead_tup column. Note also
that this is just an estimate since the last time an analyze was run on
that table, also available from the same system catalog via the
last_analyze or last_autoanalyze columns. I'd recommend running an ANALYZE
on the table(s) in question and checking this catalog again.

For actual data on dead rows or whether this is just free space, you can
use the pgstattuple contrib module:
https://www.postgresql.org/docs/current/pgstattuple.html

For large tables, this can take a while to run because it is gathering the
actual statistics of used space, dead rows and free space, not just the
estimates that analyze does.

If it's actual dead tuples and vacuum is not clearing it up, you are
running an early version of 9.2 which is 20 release behind the latest
(9.2.24). There could very well be a bug if you've exhausted all other
possible reasons for the dead tuples sticking around. If you're not able to
upgrade to a more recent major version, I would highly recommend at least
upgrading to the latest 9.2.

If it's just free space (also called bloat), you will have to perform a
VACUUM FULL to full return the disk space to the operating system. This
will lock the table for the duration and completely rewrite it and all its
indexes. However, once complete, if the table was bloated you should see
your backups return to their normal runtimes.

I've also written another tool for making bloat monitoring easier if you
find this is a frequent problem: https://github.com/keithf4/pg_bloat_check

Keith