All Aiven for PostgreSQL services have AUTOVACUUM run automatically to reclaim storage space and optimize the performance. In cases when AUTOVACUUM cannot run automatically, or manually, it’s because it cannot acquire the table lock to perform the operation. The reasons for not being able to acquire table lock for vacuuming can possibly be:
- There are long running transactions on the tables that preventing any other operations to obtain the lock on such tables. This can prevent vacuum from working normally and can greatly impact the performance of customer’s service.
- The customer may make heavy use of table locks that does not allow vacuum to get the lock after a period
- There is a connection that is stuck idle in transaction in either the primary or the replica that holds the lock. Modifying idle_in_transaction_session_timeout could help solve this issue for the customer.