pg_signal_autovacuum_worker Role for Managing Autovacuum in Azure Database for PostgreSQL

Have you ever faced a situation where your database is taking longer than expected to execute a DDL command? It can be a frustrating experience when a pending command execution blocks your workflow.  If you’re wondering which process might be blocking your DDL command, there could be different reasons. One of the primary reasons could be when autovacuum process is executing for the table in the background. Let me explain this with the help of an example. 

Consider we have a table "accounts” in our Azure Database for PostgreSQL flexible server instance database. We want to add a field and execute an ALTER statement on it. When this statement is in the process of execution, you may observe the database has significantly slowed down or stopped responding to the read/write queries for a while, resulting in a latency spike. In this scenario, the first approach would be to make sure there are no concurrent processes working on the table before executing the ALTER statement. If one of these processes is autovacuum, you will have to wait until the autovacuum process is executed on the table, which will cause a delay in committing this statement. In managed services, giving a system-level access to users can cause severe security and database integrity concerns, so users cannot manage the critical background processes like autovacuum in a controlled manner. With the recent update, we have backported 'pg_signal_autovacuum_worker' role from PostgreSQL version 18, with this approach it is now possible to control the autovacuum process without giving system level access to the users by using 'pg_terminate_backend' command in Azure Database for PostgreSQL Flexible Server in PostgreSQL versions 15 and higher. This role helps non-super users interact with autovacuum process in a secure and controlled manner. 

Let’s learn more about the autovacuum process and how this new role 'pg_signal_autovacuum_worker' helps you to manage the process more securely with controlled access for non-super users. 

What is the autovacuum process?

PostgreSQL uses various maintenance processes to handle heavy server loads, one of them being  autovacuum. Autovacuum removes obsolete versions of tuples that were created by UPDATE or DELETE statements, and that aren’t accessible anymore by any outstanding transaction. The autovacuum_naptime and autovacuum_max_workers are important parameters for the autovacuum process. This process gets launched every autovacuum_naptime seconds, the autovacuum worker  gets triggered based on the number of proportion of dead tuples in the table. You can read more about the autovacuum process and how it’s triggered here. 

Common Problems with autovacuum

Autovacuum process is an important background process as it helps with regular cleanup of dead tuples. However if it conflicts with a long running transaction or in a write-heavy environment there can be following issues: 

1. Autovacuum Blocking DDL Operations:
   1. Normal Autovacuum: Autovacuum jobs cancel themselves after deadlock_timeout (default value is 1 second) when a user triggers DDL on the table. But in some cases, users can experience significant latency up to the value of deadlock_timeout while executing read/write queries on the table. 
   2. Wraparound protection Autovacuum: This type of autovacuum does not cancel itself and blocks DDL until the job is completed. 

2. Challenges with Updating Parameters in Ongoing Autovacuum Processes: Autovacuum configuration can be tuned using server parameters like autovacuum_naptime, autovacuum_max_workers, autovacuum_analyze_scale_factor etc. When you have an autovacuum process that is running for a long time, the changed parameters do not get reflected in already running autovacuum process. 

Azure Database for PostgreSQL - Flexible Server customers have faced the challenge of not being able to terminate the process securely with more granular control over the autovacuum process. If the autovacuum process is triggered simultaneously with a business-critical DDL process and users do not have controlled access on this process, it can lead to frustrating experience. In our latest release, we have implemented a solution that is secure, backward-compatible, and easy to integrate. 

New role pg_signal_autovacuum_worker

We addressed this challenge by backporting the ‘pg_signal_autovacuum_worker’ role (which will be introduced in PostgreSQL 18) to all Azure Database for PostgreSQL flexible server versions 15 and higher. The ‘pg_signal_autovacuum_worker’ role helps you manage autovacuum tasks more effectively and allows non-superusers to terminate the autovacuum processes.  Users get controlled access for the autovacuum process with this new role.  

Advantages of backporting the pg_signal_autovacuum_worker role

Backporting the 'pg_signal_autovacuum_worker' role has some important advantages:

• Compatibility: Backporting this role to previous versions ensures a seamless upgrade experience for existing PostgreSQL servers.
• Security: Users do not have access to this role by default. The role is granted to non-superusers without granting broader system-level access. It gives a more granular and secure way to manage autovacuum processes without compromising database integrity.

Steps to add pg_signal_autovacuum_worker role

1. Let's create a new user using the below command:

CREATE USER john WITH PASSWORD 'Pass123';

2. Next, grant 'pg_signal_autovacuum_worker' role to the newly created user

GRANT pg_signal_autovacuum_worker TO john;

3. Now, confirm if 'pg_signal_autovacuum_worker' role is assigned

SELECT oid,
       rolname
FROM   pg_roles
WHERE  Pg_has_role('john', oid, 'member');

 oid  |           rolname           
-------+-----------------------------
  8916 | pg_signal_autovacuum_worker
 24826 | john

4. Now 'john' without being a member of 'azure_superuser' can terminate the autovacuum process.

SELECT 
  Pg_terminate_backend(pid) 
FROM 
  pg_stat_activity 
WHERE 
  backend_type = 'autovacuum worker';

Conclusion

Managing autovacuum processes has always been tricky, especially for non-superusers who couldn’t directly control them in a managed service environment. Now with the introduction of the 'pg_signal_autovacuum_worker' role available in Azure Database for PostgreSQL flexible server, this is possible. Database users have a secure and flexible way to manage autovacuum process without needing superuser access, making database maintenance easier and more efficient.