From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Loles <lolesft@gmail.com>
Cc: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Avoid Wraparound Failures
Date: Sat, 26 Mar 2022 09:03:40 +0100
Message-ID: <2f793dec73ab3798414574bda751e668d9d47ed8.camel@cybertec.at> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, lolesft@gmail.com, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Avoid Wraparound Failures
In-Reply-To: <2f793dec73ab3798414574bda751e668d9d47ed8.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox