CODEWITHSUNDEEP
postgresqlautovacuum
sinu john
sinu john

Reputation: 31

How can i tune autovacuum configs for pg toast tables?

I have a pg_toast table which has a lot of dead tuples accumulating. The autovacuum job on it runs for a very long time. I wanted to tune the autovacuum config, to make it more aggressive.

When i'm trying to set the config I get the following error:

ERROR:  permission denied: "pg_toast_16432" is a system catalog

Query used:

ALTER TABLE pg_toast.pg_toast_16432 SET (autovacuum_vacuum_scale_factor = 0);

Postgres version: 11

Can someone please suggest how to tune autoconfig on toast tables?

Upvotes: 1

Views: 603

Answers (2)

motoaima
motoaima

Reputation: 1

You cannot directly modify the autovacuum configuration for individual system catalog tables, however, you can consider using other performance optimization techniques like indexing and query optimization to reduce the need for aggressive autovacuuming.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247380

You can set a storage parameter on the table itself, as described in the documentation. If the table is called tab, run

ALTER TABLE tab SET (toast.autovacuum_vacuum_cost_delay = 0);

Note that setting autovacuum_vacuum_scale_factor to 0 won't do what you want. To speed up autovacuum, increase autovacuum_vacuum_cost_limit or reduce autovacuum_vacuum_cost_delay.

Upvotes: 0

Related Questions