Subject: Re: Why is my table not autovacuuming?
To: Don Seiler <don@seiler.us>, Phil Frost <phil@postmates.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
References: 
 <CAHJZqBAaXKi2+n4zNFYkjOCLY+ABpuC_BSMBKHfHfs0QqPNjbA@mail.gmail.com>
 <CAC6ry0+NJMoKXxn9KQxcj+GP_DoMynxa6etxc7wVC3B2Mh1zZA@mail.gmail.com>
 <CAHJZqBAwDCsznV03OA8V7n7bKfJhx9Dx-fgmp8RLXfqdQJzq5w@mail.gmail.com>
From: Joe Conway <mail@joeconway.com>
Openpgp: id=4EA5B4659ED59CC9538139A5332B7E68B699434A
Message-ID: <73557173-6cdb-6318-c7a5-e531d385e397@joeconway.com>
Date: Mon, 28 Aug 2017 09:13:03 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.2.1
MIME-Version: 1.0
In-Reply-To: 
 <CAHJZqBAwDCsznV03OA8V7n7bKfJhx9Dx-fgmp8RLXfqdQJzq5w@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="suksnhRaU2UvDoCeGCG63tTEc3oEDhkCj"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--suksnhRaU2UvDoCeGCG63tTEc3oEDhkCj
From: Joe Conway <mail@joeconway.com>
To: Don Seiler <don@seiler.us>, Phil Frost <phil@postmates.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Message-ID: <73557173-6cdb-6318-c7a5-e531d385e397@joeconway.com>
Subject: Re: [ADMIN] Why is my table not autovacuuming?
References: <CAHJZqBAaXKi2+n4zNFYkjOCLY+ABpuC_BSMBKHfHfs0QqPNjbA@mail.gmail.com>
 <CAC6ry0+NJMoKXxn9KQxcj+GP_DoMynxa6etxc7wVC3B2Mh1zZA@mail.gmail.com>
 <CAHJZqBAwDCsznV03OA8V7n7bKfJhx9Dx-fgmp8RLXfqdQJzq5w@mail.gmail.com>
In-Reply-To: <CAHJZqBAwDCsznV03OA8V7n7bKfJhx9Dx-fgmp8RLXfqdQJzq5w@mail.gmail.com>
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=utf-8

On 08/28/2017 08:59 AM, Don Seiler wrote:
> So the autovacuum daemon knows that there are 71M rows, even though
> pg_stat_all_tables said only 170K? Is it using pg_class.reltuples? Is
> pg_stat_all_tables not really a reliable source of stats information?

See:
file:///opt/src/pgsql-git/master/src/backend/postmaster/autovacuum.c

 * threshold =3D vac_base_thresh + vac_scale_factor * reltuples

pg_stat_all_tables only shows information known by the stats collector,
which only knows about activity since the last stats reset (which can
happen, for example on "immediate" shutdown or server crash), not
absolute numbers (although reltuples is only an estimate too).

--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


--suksnhRaU2UvDoCeGCG63tTEc3oEDhkCj
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQIcBAEBCAAGBQJZpEEPAAoJEDfy90M199hl5IoP/02kK3wG5Bo9VaFrxLhmsrbU
F/UboGblJHiQJpvDLdQe4EVsOHnXsJaN4ppRcQXi4vq8GwXzrs202y+csCtfKM6W
IhtGu/cik7sstkyVwKO62s+OvwhUODxtEFPh1JdZRDQbA6UYqREnBWMa+Hdn6WJc
FTzochaaMoVipUaWFLeQamOp5+6f2P06l3eqUUtm3k+2JS/al0yc1dvjR2vH0ucM
7E9cHhnJ1wxQniWvtnmNx2bN0hlDbE4J0qt/G+SeAnxmOX9HY5JB40xp4heh2YXR
8CqDUo+wdnidKlgaGBw8HGF2Md3Mfstpf0AJJIeBSxptOFMuotsPL0w3OUVD3kWG
RysJwnUqwc9gpDtmr94DUp545TCXtxHx8G7aE3ViQ7Nhv/gCmtn31D4GmWEHLspV
ExVEX/MREdDtl6CvLMVhK31AAXdPgqi/QJmWZD7GdHgfw0bcKeX79mQPkvV6Rn7S
jIwxzbSxb/O9f7OUZ/MXBqZmgwD4nTPttfCA7o33b50TcOQfDcs4Y4DeAS9hAHA3
OquWZN2rlgyoRVa4jf1c1QW4EtLSLG64+Qqu5ZlLlxspRgNFi48Ys4nBi3Wxhs+O
t/VuEe2cw2gRtSrei28B0DzQoqUfkivPVyuHNsPu2JSOV3EFSIj5bjRGvTumVVRg
a+YcVBKQfWCXCkKobNYJ
=kZYr
-----END PGP SIGNATURE-----

--suksnhRaU2UvDoCeGCG63tTEc3oEDhkCj--