Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k3JUB-0001uE-Li for pgsql-admin@arkaria.postgresql.org; Wed, 05 Aug 2020 13:29:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k3JU9-0004t5-Rz for pgsql-admin@arkaria.postgresql.org; Wed, 05 Aug 2020 13:29:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k3JU9-0004rt-K3 for pgsql-admin@lists.postgresql.org; Wed, 05 Aug 2020 13:29:37 +0000 Received: from mout.perfora.net ([74.208.4.194]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k3JU2-0001UI-Qx for pgsql-admin@lists.postgresql.org; Wed, 05 Aug 2020 13:29:36 +0000 Received: from [192.168.1.143] ([98.169.208.7]) by mrelay.perfora.net (mreueus004 [74.208.5.2]) with ESMTPSA (Nemesis) id 1MORpi-1kNiiC3RfS-00Pxmz; Wed, 05 Aug 2020 15:29:22 +0200 Subject: Re: How can i be certain autovacuum is causing reuse if table still grows To: Keith Fiske Cc: Scott Ribe , Sidney Aloisio Ferreira Pryor , Guillaume Lelarge , Ron , "pgsql-admin@lists.postgresql.org" References: <2500ca1c-312e-c495-ad98-1b593d030c71@gmail.com> From: MichaelDBA Message-ID: <23135c5d-3a17-108a-e59b-a7fa8446fec0@sqlexec.com> Date: Wed, 5 Aug 2020 09:29:17 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/7.0.25 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------45680F60639C0DDC80DE392E" Content-Language: en-US X-Provags-ID: V03:K1:+6Fwd8oR8HcjW5W0KPctrolP1ed9XOOFvkjIPCT/azqpm2rSZNY Sm4UWUjwJKWR4+FNbT5nIGMaXNwv8x29vLymWp53fr2LtdSbb47aAz5GSXZTaXJ9gcUC6Nl AyzfdcMe6y+TxXQ6oDWGpTGdARG5Po39D6Kc9VWOszY0GyDb7R8WyjkJ5CKfbIbemMSTR+T vr91La+2K2Cd1lIy1XPpg== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:QPhC1XWgkso=:GNaZsk5U4iXxi4mY1l/dQW BeStQjWBfsRN9dHz4v0zVxtnqd9eVfCp99oqzYGePMcQ7AHsvG94fREt1iaidHlGyTlIEEFCB FAfMTgvLglymtrqeCNyteMQl+/cch16xss5cdetvxBYvm11cYEpXGYU4/VocJ2SseBJHEXU5/ nK9uXbdo4s5ZzAnlImeGgV44+a6pxhiswA4AJ1E/ca+N9bOwrk69Cn51XKBMhG3TvhQ8vmv3y SW/8DXpw8GF+h2dpIiRjmp8DgPVYzgyhK0DeNRtfqCORyYu0pqnIDM02gBZo2OSc5EWnOvhr9 TjjpUSeluyO7qCt5tKv67etF0XwkxIZrpGe/OwdBM8WGCi5rXDvHXDv7N7hb/jzKa+myxpH0M XusIbKJ4Mml25Y/ffnz8URrMN8fl6hnD3OV19BJBfB/386hJH7vUKWl9qTS9ityFOrxLLa955 OP2Ya2k0suhCnWOpSOV+jCSTjKHLn+B1rTaCIZbWTDQlbUqyvv0bWsg+R/zqkDUfzwZnpKwQn CvHPuXp5nRBjxIkAQaImgmrlzRQwYIyLBSWvcZXVUXX3lNCXNQld24/86BMMY08g9yexi7iei rQ0dZzTQ8KR8HrzcjUvSgZl5er0R7Rlq9674NQMwZWNujIilJ9b0Y4S7B97BYuR9A/XqLNb0+ iOn5JVLgPAqaSRnPOcJWolQilXzOpcf90PSjAmnPp4U8S7RulfGZtttXpmHASy22NJIyLrerr c32B3GaUKqGXMhrsa5cBPAiaqxtgyJn0mgE7F/OBWm3+GTqLwslTsEGWv8Hszx9Udgt8iApA1 Nxjp/lnWgr3YGirJvhGo9zLciEWtEirnAeoNZWSrC9npbIgmpnf0rdelGteWUZ4yrtVaWvd List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------45680F60639C0DDC80DE392E Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi Keith, I think statistics are also reset and not persistent through restarts when the pg_ctl option "-m immediate" is used when shutting down PG since it forces recovery mode when starting PG back up.  By the way, for PostgreSQL Aurora, statistics are reset automatically every time aurora forces PG to restart or reboot or the user selects that option manually.  I reported this as a bug with Aurora a few months ago.  They said it was not a bug, but a feature that the might implement in the future.  YUK. Regards, Michael Vitale Keith Fiske wrote on 8/4/2020 6:25 PM: > > > On Tue, Aug 4, 2020 at 3:54 PM Scott Ribe > wrote: > > > On Aug 4, 2020, at 12:31 PM, Sidney Aloisio Ferreira Pryor > > wrote: > > > > Beside this, i saw on pg_stat_tables and the vacuum and > autovacuum count for this table is 0. > > Those values are not persistent; they are only since the last > start of PG. > > > This is not correct. Statistics are persistent through restarts. They > are only reset during specific cases such as a crash or a major > version upgrade. They can also be reset at any time manually. > > -- > Keith Fiske > Senior Database Engineer > Crunchy Data - http://crunchydata.com --------------45680F60639C0DDC80DE392E Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Hi Keith,

I think statistics are also reset and not persistent through restarts when the pg_ctl option "-m immediate" is used when shutting down PG since it forces recovery mode when starting PG back up.  By the way, for PostgreSQL Aurora, statistics are reset automatically every time aurora forces PG to restart or reboot or the user selects that option manually.  I reported this as a bug with Aurora a few months ago.  They said it was not a bug, but a feature that the might implement in the future.  YUK.

Regards,
Michael Vitale


Keith Fiske wrote on 8/4/2020 6:25 PM:


On Tue, Aug 4, 2020 at 3:54 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Aug 4, 2020, at 12:31 PM, Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br> wrote:
>
> Beside this, i saw on pg_stat_tables and the vacuum and autovacuum count for this table is 0.

Those values are not persistent; they are only since the last start of PG.


This is not correct. Statistics are persistent through restarts. They are only reset during specific cases such as a crash or a major version upgrade. They can also be reset at any time manually.

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

--------------45680F60639C0DDC80DE392E--