Re: vacuum TOAST tables

On Fri, 2023-04-21 at 04:37 +0000, senor wrote:
> PostgreSQL v11.19-1PGDG.rhel7 (upgrading to 12+ is so far only talk)
> CentOS 7.9
>
> If I understand correctly, autovacuum handles tables and their associated TOAST tables separately
> but a manual vacuum will also vacuum the TOAST.

That is correct.

> When manually vacuuming does it matter whether it's the main table or TOAST?

It makes a difference. As superuser you can directly VACUUM a toast table, and that will
no VACUUM the table it belongs to. However, if you VACUUM the main table, both tables
will be vacuumed, which is more work. So if you only need VACUUM on the toast table,
doing that directly will be cheaper.

> I've posted before about these same systems. It'll get to age(datfrozenxid) > 2,000,000,000 and
> is not able to keep up until I get it back down to under ~600000000. Then it starts humming along
> as if I "cleared" something.

That sounds unsavory. Did you set "autovacuum_freeze_max_age" to an extremely high value?

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