Question 239713 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search