Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r4zXn-00BIrR-UN for pgsql-admin@arkaria.postgresql.org; Mon, 20 Nov 2023 08:22:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r4zXl-006bUI-Bx for pgsql-admin@arkaria.postgresql.org; Mon, 20 Nov 2023 08:22:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r4zXl-006bS8-05 for pgsql-admin@lists.postgresql.org; Mon, 20 Nov 2023 08:22:09 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r4zXj-007Nyb-8k for pgsql-admin@lists.postgresql.org; Mon, 20 Nov 2023 08:22:08 +0000 Content-Type: multipart/alternative; boundary="------------dlGXERSYGBeSwPAvYdQMxZbP" Message-ID: Date: Mon, 20 Nov 2023 10:22:04 +0200 MIME-Version: 1.0 Subject: Re: Why did my PostgreSQL tables double in size? Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Achilleas Mantzios - cloud In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------dlGXERSYGBeSwPAvYdQMxZbP Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/20/23 05:22, Ron Johnson wrote: > On Sun, Nov 19, 2023 at 5:24 PM Abdullah Ergin > wrote: > > During this period, I believe the unchecked increase in table size > was due to the auto vacuum parameter being turned off. What should > I do to prevent this from affecting the 12th month and beyond? > > Make sure not to comment out "autovacuum = on"? > > Create a cron job that regularly runs psql -Xtc "SHOW AUTOVACUUM", and > sends an email if it's set to off? And be sure to check no table has unexpectedly individual autovacuum turned off : select pg_class.oid::regclass,relkind, opts.* from pg_class JOIN LATERAL pg_options_to_table(reloptions) opts ON (true) where relkind not in ('i','v','S') AND option_name ~* '.*autovacuum_enabled' AND option_value='false' --------------dlGXERSYGBeSwPAvYdQMxZbP Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 11/20/23 05:22, Ron Johnson wrote:
On Sun, Nov 19, 2023 at 5:24 PM Abdullah Ergin <abdullaherginwork@gmail.com> wrote:
During this period, I believe the unchecked increase in table size was due to the auto vacuum parameter being turned off. What should I do to prevent this from affecting the 12th month and beyond?
 
Make sure not to comment out "autovacuum = on"?

Create a cron job that regularly runs psql -Xtc "SHOW AUTOVACUUM", and sends an email if it's set to off?

And be sure to check no table has unexpectedly individual autovacuum turned off :

select pg_class.oid::regclass,relkind, opts.* from pg_class JOIN LATERAL pg_options_to_table(reloptions) opts ON (true) where relkind not in ('i','v','S') AND option_name ~* '.*autovacuum_enabled' AND option_value='false'

 
--------------dlGXERSYGBeSwPAvYdQMxZbP--