Message-ID: <2f793dec73ab3798414574bda751e668d9d47ed8.camel@cybertec.at>
Subject: Re: Avoid Wraparound Failures
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Loles <lolesft@gmail.com>
Cc: Ron <ronljohnsonjr@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Sat, 26 Mar 2022 09:03:40 +0100
In-Reply-To: 
 <CA+FWGK7_eXOXw6b4UHoOwsCJdgo=ac07tAACU3dL7mMntDU3Bg@mail.gmail.com>
References: 
	<CA+FWGK6HmRx8=-2wZkw-9a6a36f6iEGgAH=bN1rNicbFgzas5A@mail.gmail.com>
	 <965a52fe849ec6e5759d349b4063b5077353f24b.camel@cybertec.at>
	 <e94006fe-b953-720e-c6b8-a7f76145fa55@gmail.com>
	 <c3a1bd51686c32f399b7f23e48aec4820e8bad15.camel@cybertec.at>
	 <CA+FWGK7_eXOXw6b4UHoOwsCJdgo=ac07tAACU3dL7mMntDU3Bg@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/2f793dec73ab3798414574bda751e668d9d47ed8.camel%40cybertec.at>
Precedence: bulk

On Sat, 2022-03-26 at 07:47 +0100, Loles wrote:
> So, if I have understood it correctly:
> 
> 1) set autovacuum_vacuum_cost_delay from 20 to 2

It is not strictly required, but that will keep autovacuum from taking
forever to process your big tables.

It has no influence on manual VACUUM though.

> 2) execute, only for the largest tables, vacuum command:
> 
>    vacuum tab1;
>    vacuum tab2;
> 
>    and so on

If you don't want to wait for autovacuum to do it automatically, yes.

> 3) Sleep peacefully :)

Definitely.  This is normal behavior.

> Another doubt: query filtered relkind = 'r' but there are also sequences, views, catalog tables... that are also close to 200 million. 

Sequences and views are don't need VACUUM.
Materialized views and catalog tables do.

> Vacuum the catalog tables too or is it not necessary?

Same as all other tables: do it manually or let autovacuum do it automatically.

> I understand that the views, sequences and everything that depends on the table will improve by vacuuming that table.

There is no influence on sequences.

VACUUM can improve the performance of some queries, but mostly it is about keeping your tables
healthy and in good shape.  Views may benefit just like all other queries do.

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