Message-ID: <49a74a198068f02c507f2a5e4c533cc30d24adc6.camel@cybertec.at>
Subject: Re: Autovacuum script
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Avihai Shoham <avihai.shoham@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Thu, 07 Apr 2022 12:27:42 +0200
In-Reply-To: 
 <CA+=0ERct5Orv+GeERiROr4n+EXVi1=V+iQTnozb5qY+e_MaCFQ@mail.gmail.com>
References: 
	<CA+=0ERcG7nz7N-sEPDsKzNn+aMC+Rt2oy=sXe94ieoG61x-W2Q@mail.gmail.com>
	 <CA+=0ERct5Orv+GeERiROr4n+EXVi1=V+iQTnozb5qY+e_MaCFQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.40.4 (3.40.4-3.fc34) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/49a74a198068f02c507f2a5e4c533cc30d24adc6.camel%40cybertec.at>
Precedence: bulk

On Thu, 2022-04-07 at 12:36 +0300, Avihai Shoham wrote:
> I would like for you advice/sharing  about a python script that will run overnight and do autovacuum.
>  
> I'm sharing mine below and will happy if you have suggestion to improve it , or sharing yours in case you have.
> I'm not an expert of python or postgrsql so please understand :)
> 
> The script below do autovacuum for the biggest 10 tables
> -----------------------------------------------------------------------------------------------------------------------------------
> #!/usr/bin/python
> [...]
>           query = 'VACUUM FULL %s;' % (row[0])

I think that is quite a bad idea, for several reasons:

1. VACUUM (FULL) blocks read access to the table and is *not* a routine maintenance job
   It should be VACUUM instead.

2. It may not be your largest tables that require VACUUM

3. There already is an autovacuum daemon in PostgreSQL that takes care of that and
   has many parameters to adapt it to your needs.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com