0

For few of the tables autovacuum is running running in parallel, one table process is locking other tables autovacuum process and performance of the other queries (select, insert, update) very slow or almost stopped.

in my postgressql.conf file, I did not change anything.

#autovacuum = on

below is screenshot for reference. If I kill SELECT pg_cancel_backend(<PID>) process, it is starting again. Is there anything I can do to safely stop the process. please advice what steps I can take here. I am using postgreSQL- 14

enter image description here

autovacuum: VACUUM

Raju
  • 27
  • 5
  • 1
    Let it finish. For an answer, please show your autovacuum parameters and the complete lines from `pg_stat_activity`. **No images**, formatted text please. – Laurenz Albe Mar 28 '23 at 15:38
  • 1
    Is this autovacuum 'for wraparound'? The query column of pg_stat_activity would tell you that. – jjanes Mar 28 '23 at 15:56

1 Answers1

0

The autovacuum is triggered when a high volume of write or delete operations is performed on a particular table. If your application frequently performs update or delete queries on the same table, consider fine-tuning the following Postgres parameters:

  1. autovacuum_vacuum_scale_factor: This parameter specifies the fraction of the table size to determine whether to trigger a VACUUM or not. The default value is 0.2 (20% of the table). This means if 20% of the records from a particular time period are updated or deleted, the autovacuum will be triggered.

  2. cost_limit and cost_delay: These parameters limit the amount of time spent on each vacuuming process. If the "cost_limit" is too low or "cost_delay" is set too high, it can cause Autovacuum to take longer to complete, potentially leading to contention and locks.

  3. If your database has a large number of tables and Autovacuum runs on all of them simultaneously, it can result in increased contention and locking.

Here are a few useful queries that might help you update these parameters for a specific table:

  1. Specify autovacuum parameters at the table level.

    ALTER TABLE <TABLE-NAME> SET (<POSTGRES PARAMETER>= <VALUE>);

  2. To check what parameters have been updated on tables using query

    SELECT relname, reloptions FROM pg_class WHERE relname in ('<TABLE NAME>');

  3. Check the last time autovacuum was triggered.

SELECT schemaname, relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM pg_stat_user_tables;

Akshay Bhadange
  • 73
  • 11