# Common DBA tasks for Amazon RDS for PostgreSQL Common DBA tasks for RDS for PostgreSQL Database administrators (DBAs) perform a variety of tasks when administering an Amazon RDS for PostgreSQL DB instance. If you're a DBA already familiar with PostgreSQL, you need to be aware of some of the important differences between running PostgreSQL on your hardware and RDS for PostgreSQL. For example, because it's a managed service, Amazon RDS doesn't allow shell access to your DB instances. That means that you don't have direct access to `pg_hba.conf` and other configuration files. For RDS for PostgreSQL, changes that are typically made to the PostgreSQL configuration file of an on-premises instance are made to a custom DB parameter group associated with the RDS for PostgreSQL DB instance. For more information, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). You also can't access log files in the same way that you do with an on-premises PostgreSQL instance. To learn more about logging, see [ RDS for PostgreSQL database log files](USER_LogAccess.Concepts.PostgreSQL.md). As another example, you don't have access to the PostgreSQL `superuser` account. On RDS for PostgreSQL, the `rds_superuser` role is the most highly privileged role, and it's granted to `postgres` at set up time. Whether you're familiar with using PostgreSQL on-premises or completely new to RDS for PostgreSQL, we recommend that you understand the `rds_superuser` role, and how to work with roles, users, groups, and permissions. For more information, see [Understanding PostgreSQL roles and permissions](Appendix.PostgreSQL.CommonDBATasks.Roles.md). Following are some common DBA tasks for RDS for PostgreSQL. **Topics** + [ # Collations supported in RDS for PostgreSQL ](PostgreSQL-Collations.md) + [ # Understanding PostgreSQL roles and permissions ](Appendix.PostgreSQL.CommonDBATasks.Roles.md) + [ # Dead connection handling in PostgreSQL ](Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.md) + [ # Working with PostgreSQL autovacuum on Amazon RDS for PostgreSQL ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md) + [ # Managing high object counts in Amazon RDS for PostgreSQL ](PostgreSQL.HighObjectCount.md) + [ # Managing TOAST OID contention in Amazon RDS for PostgreSQL ](Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.md) + [ ## Working with logging mechanisms supported by RDS for PostgreSQL ](#Appendix.PostgreSQL.CommonDBATasks.Auditing) + [ # Managing temporary files with PostgreSQL ](PostgreSQL.ManagingTempFiles.md) + [ ## Using pgBadger for log analysis with PostgreSQL ](#Appendix.PostgreSQL.CommonDBATasks.Badger) + [ ## Using PGSnapper for monitoring PostgreSQL ](#Appendix.PostgreSQL.CommonDBATasks.Snapper) + [ # Managing custom casts in RDS for PostgreSQL ](PostgreSQL.CustomCasts.md) + [ # Best Practices for Parallel Queries in RDS for PostgreSQL ](PostgreSQL.ParallelQueries.md) + [ # Working with parameters on your RDS for PostgreSQL DB instance ](Appendix.PostgreSQL.CommonDBATasks.Parameters.md) # Collations supported in RDS for PostgreSQL Collations supported in RDS for PostgreSQL Collations are set of rules that determine how character strings stored in the database are sorted and compared. Collations play a fundamental role in the computer system and are included as part of the operating system. Collations change over time when new characters are added to languages or when ordering rules change. Collation libraries define specific rules and algorithms for a collation. The most popular collation libraries used within PostgreSQL are GNU C (glibc) and Internationalization components for Unicode (ICU). By default, RDS for PostgreSQL uses the glibc collation that includes unicode character sort orders for multi-byte character sequences. When you create a new DB instance in RDS for PostgreSQL , it checks the operating system for the available collation. The PostgreSQL parameters of the `CREATE DATABASE` command `LC_COLLATE` and `LC_CTYPE` are used to specify a collation, which stands as the default collation in that database. Alternatively, you can also use the `LOCALE` parameter in `CREATE DATABASE` to set these parameters. This determines the default collation for character strings in the database and the rules for classifying characters as letters, numbers, or symbols. You can also choose a collation to use on a column, index, or on a query. RDS for PostgreSQL depends on the glibc library in the operating system for collation support. RDS for PostgreSQL instance is periodically updated with the latest versions of the operating system. These updates sometimes include a newer version of the glibc library. Rarely, newer versions of glibc change the sort order or collation of some characters, which can cause the data to sort differently or produce invalid index entries. If you discover sort order issues for collation during an update, you might need to rebuild the indexes. To reduce the possible impacts of the glibc updates, RDS for PostgreSQL now includes an independent default collation library. This collation library is available in RDS for PostgreSQL 14.6, 13.9, 12.13, 11.18, 10.23 and newer minor version releases. It is compatible with glibc 2.26-59.amzn2, and provides sort order stability to prevent incorrect query results. # Understanding PostgreSQL roles and permissions Understanding PostgreSQL roles and permissions When you create an RDS for PostgreSQL DB instance using the AWS Management Console, an administrator account is created at the same time. By default, its name is `postgres`, as shown in the following screenshot: ![\[The default login identity for Credentials in the Create database page is postgres.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/default-login-identity-apg-rpg.png) You can choose another name rather than accept the default (`postgres`). If you do, the name you choose must start with a letter and be between 1 and 16 alphanumeric characters. For simplicity's sake, we refer to this main user account by its default value (`postgres`) throughout this guide. If you use the `create-db-instance` AWS CLI rather than the AWS Management Console, you create the name by passing it with the `master-username` parameter in the command. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). Whether you use the AWS Management Console, the AWS CLI, or the Amazon RDS API, and whether you use the default `postgres` name or choose a different name, this first database user account is a member of the `rds_superuser` group and has `rds_superuser` privileges. **Topics** + [ # Understanding the rds\$1superuser role ](Appendix.PostgreSQL.CommonDBATasks.Roles.rds_superuser.md) + [ # Controlling user access to the PostgreSQL database ](Appendix.PostgreSQL.CommonDBATasks.Access.md) + [ # Delegating and controlling user password management ](Appendix.PostgreSQL.CommonDBATasks.RestrictPasswordMgmt.md) + [ # Using SCRAM for PostgreSQL password encryption ](PostgreSQL_Password_Encryption_configuration.md) # Understanding the rds\$1superuser role Understanding the rds\$1superuser role In PostgreSQL, a *role* can define a user, a group, or a set of specific permissions granted to a group or user for various objects in the database. PostgreSQL commands to `CREATE USER` and `CREATE GROUP` have been replaced by the more general, `CREATE ROLE` with specific properties to distinguish database users. A database user can be thought of as a role with the LOGIN privilege. **Note** The `CREATE USER` and `CREATE GROUP` commands can still be used. For more information, see [Database Roles](https://www.postgresql.org/docs/current/user-manag.html) in the PostgreSQL documentation. The `postgres` user is the most highly privileged database user on your RDS for PostgreSQL DB instance. It has the characteristics defined by the following `CREATE ROLE` statement. ``` CREATE ROLE postgres WITH LOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION VALID UNTIL 'infinity' ``` The properties `NOSUPERUSER`, `NOREPLICATION`, `INHERIT`, and `VALID UNTIL 'infinity'` are the default options for CREATE ROLE, unless otherwise specified. By default, `postgres` has privileges granted to the `rds_superuser` role, and permissions to create roles and databases. The `rds_superuser` role allows the `postgres` user to do the following: + Add extensions that are available for use with Amazon RDS. For more information, see [Working with PostgreSQL features supported by Amazon RDS for PostgreSQL](PostgreSQL.Concepts.General.FeatureSupport.md) + Create roles for users and grant privileges to users. For more information, see [CREATE ROLE](https://www.postgresql.org/docs/current/sql-createrole.html) and [GRANT](https://www.postgresql.org/docs/14/sql-grant.html) in the PostgreSQL documentation. + Create databases. For more information, see [CREATE DATABASE](https://www.postgresql.org/docs/14/sql-createdatabase.html) in the PostgreSQL documentation. + Grant `rds_superuser` privileges to user roles that don't have these privileges, and revoke privileges as needed. We recommend that you grant this role only to those users who perform superuser tasks. In other words, you can grant this role to database administrators (DBAs) or system administrators. + Grant (and revoke) the `rds_replication` role to database users that don't have the `rds_superuser` role. + Grant (and revoke) the `rds_password` role to database users that don't have the `rds_superuser` role. + Obtain status information about all database connections by using the `pg_stat_activity` view. When needed, `rds_superuser` can stop any connections by using `pg_terminate_backend` or `pg_cancel_backend`. In the `CREATE ROLE postgres...` statement, you can see that the `postgres` user role specifically disallows PostgreSQL `superuser` permissions. RDS for PostgreSQL is a managed service, so you can't access the host OS, and you can't connect using the PostgreSQL `superuser` account. Many of the tasks that require `superuser` access on a stand-alone PostgreSQL are managed automatically by Amazon RDS. For more information about granting privileges, see [GRANT](http://www.postgresql.org/docs/current/sql-grant.html) in the PostgreSQL documentation. The `rds_superuser` role is one of several *predefined* roles in an RDS for PostgreSQL DB instance. **Note** In PostgreSQL 13 and earlier releases, *predefined* roles are known as *default* roles. In the following list, you find some of the other predefined roles that are created automatically for a new RDS for PostgreSQL DB instance. Predefined roles and their privileges can't be changed. You can't drop, rename, or modify privileges for these predefined roles. Attempting to do so results in an error. + **rds\$1password** – A role that can change passwords and set up password constraints for database users. The `rds_superuser` role is granted with this role by default, and can grant the role to database users. For more information, see [Controlling user access to the PostgreSQL databaseControlling user access to PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Access.md). + For RDS for PostgreSQL versions older than 14, `rds_password` role can change passwords and set up password constraints for database users and users with `rds_superuser` role. From RDS for PostgreSQL version 14 and later, `rds_password` role can change passwords and set up password constraints only for database users. Only users with `rds_superuser` role can perform these actions on other users with `rds_superuser` role. + **rdsadmin** – A role that's created to handle many of the management tasks that the administrator with `superuser` privileges would perform on a standalone PostgreSQL database. This role is used internally by RDS for PostgreSQL for many management tasks. + **rdstopmgr** – A role that's used internally by Amazon RDS to support Multi-AZ deployments. + **rds\$1reserved** – A role that's used internally by Amazon RDS to reserve database connections. # Viewing roles and their privileges Viewing roles and privileges You can view predefined roles and their privileges in your RDS for PostgreSQL DB instance using different commands depending on your PostgreSQL version. To see all predefined roles, you can connect to your RDS for PostgreSQL DB instance and run following commands using the `psql`. **For `psql` 15 and earlier versions** Connect to your RDS for PostgreSQL DB instance and use the `\du` command in psql: ``` postgres=> \du List of roles Role name | Attributes | Member of -----------------+------------------------------------------------------------+------------------------------------------------------ postgres | Create role, Create DB +| {rds_superuser} | Password valid until infinity | rds_ad | Cannot login | {} rds_iam | Cannot login | {} rds_password | Cannot login | {} rds_replication | Cannot login | {} rds_superuser | Cannot login | {pg_monitor,pg_signal_backend,rds_password,rds_replication} rdsadmin | Superuser, Create role, Create DB, Replication, Bypass RLS+| {} | Password valid until infinity | ``` **For `psql` 16 and later versions** ``` postgres=> \drg+ List of role grants Role name | Member of | Options | Grantor ---------------+-----------------------------+---------------------+---------- postgres | rds_superuser | INHERIT, SET | rdsadmin rds_superuser | pg_checkpoint | ADMIN, INHERIT, SET | rdsadmin rds_superuser | pg_monitor | ADMIN, INHERIT, SET | rdsadmin rds_superuser | pg_signal_backend | ADMIN, INHERIT, SET | rdsadmin rds_superuser | pg_use_reserved_connections | ADMIN, INHERIT, SET | rdsadmin rds_superuser | rds_password | ADMIN, INHERIT, SET | rdsadmin rds_superuser | rds_replication | ADMIN, INHERIT, SET | rdsadmin ``` To check role membership without version dependency, you can use the following SQL query: ``` SELECT m.rolname AS "Role name", r.rolname AS "Member of" FROM pg_catalog.pg_roles m JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid) LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid) LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid) WHERE m.rolname !~ '^pg_' ORDER BY 1, 2; ``` In the output, you can see that `rds_superuser` isn't a database user role (it can't login), but it has the privileges of many other roles. You can also see that database user `postgres` is a member of the `rds_superuser` role. As mentioned previously, `postgres` is the default value in the Amazon RDS console's **Create database** page. If you chose another name, that name is shown in the list of roles instead. # Controlling user access to the PostgreSQL database Controlling user access to PostgreSQL New databases in PostgreSQL are always created with a default set of privileges in the database's `public` schema that allow all database users and roles to create objects. These privileges allow database users to connect to the database, for example, and create temporary tables while connected. To better control user access to the databases instances that you create on your RDS for PostgreSQL DB instance, we recommend that you revoke these default `public` privileges. After doing so, you then grant specific privileges for database users on a more granular basis, as shown in the following procedure. **To set up roles and privileges for a new database instance** Suppose you're setting up a database on a newly created RDS for PostgreSQL DB instance for use by several researchers, all of whom need read-write access to the database. 1. Use `psql` (or pgAdmin) to connect to your RDS for PostgreSQL DB instance: ``` psql --host=your-db-instance.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password ``` When prompted, enter your password. The `psql` client connects and displays the default administrative connection database, `postgres=>`, as the prompt. 1. To prevent database users from creating objects in the `public` schema, do the following: ``` postgres=> REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE ``` 1. Next, you create a new database instance: ``` postgres=> CREATE DATABASE lab_db; CREATE DATABASE ``` 1. Revoke all privileges from the `PUBLIC` schema on this new database. ``` postgres=> REVOKE ALL ON DATABASE lab_db FROM public; REVOKE ``` 1. Create a role for database users. ``` postgres=> CREATE ROLE lab_tech; CREATE ROLE ``` 1. Give database users that have this role the ability to connect to the database. ``` postgres=> GRANT CONNECT ON DATABASE lab_db TO lab_tech; GRANT ``` 1. Grant all users with the `lab_tech` role all privileges on this database. ``` postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_db TO lab_tech; GRANT ``` 1. Create database users, as follows: ``` postgres=> CREATE ROLE lab_user1 LOGIN PASSWORD 'change_me'; CREATE ROLE postgres=> CREATE ROLE lab_user2 LOGIN PASSWORD 'change_me'; CREATE ROLE ``` 1. Grant these two users the privileges associated with the lab\$1tech role: ``` postgres=> GRANT lab_tech TO lab_user1; GRANT ROLE postgres=> GRANT lab_tech TO lab_user2; GRANT ROLE ``` At this point, `lab_user1` and `lab_user2` can connect to the `lab_db` database. This example doesn't follow best practices for enterprise usage, which might include creating multiple database instances, different schemas, and granting limited permissions. For more complete information and additional scenarios, see [Managing PostgreSQL Users and Roles](https://aws.amazon.com/blogs//database/managing-postgresql-users-and-roles/). For more information about privileges in PostgreSQL databases, see the [GRANT](https://www.postgresql.org/docs/current/static/sql-grant.html) command in the PostgreSQL documentation. # Delegating and controlling user password management Delegating and controlling user password management As a DBA, you might want to delegate the management of user passwords. Or, you might want to prevent database users from changing their passwords or reconfiguring password constraints, such as password lifetime. To ensure that only the database users that you choose can change password settings, you can turn on the restricted password management feature. When you activate this feature, only those database users that have been granted the `rds_password` role can manage passwords. **Note** To use restricted password management, your RDS for PostgreSQL DB instance must be running PostgreSQL 10.6 or higher. By default, this feature is `off`, as shown in the following: ``` postgres=> SHOW rds.restrict_password_commands; rds.restrict_password_commands -------------------------------- off (1 row) ``` To turn on this feature, you use a custom parameter group and change the setting for `rds.restrict_password_commands` to 1. Be sure to reboot your RDS for PostgreSQL DB instance so that the setting takes effect. With this feature active, `rds_password` privileges are needed for the following SQL commands: ``` CREATE ROLE myrole WITH PASSWORD 'mypassword'; CREATE ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2023-01-01'; ALTER ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2023-01-01'; ALTER ROLE myrole WITH PASSWORD 'mypassword'; ALTER ROLE myrole VALID UNTIL '2023-01-01'; ALTER ROLE myrole RENAME TO myrole2; ``` Renaming a role (`ALTER ROLE myrole RENAME TO newname`) is also restricted if the password uses the MD5 hashing algorithm. With this feature active, attempting any of these SQL commands without the `rds_password` role permissions generates the following error: ``` ERROR: must be a member of rds_password to alter passwords ``` We recommend that you grant the `rds_password` to only a few roles that you use solely for password management. If you grant `rds_password` privileges to database users that don't have `rds_superuser` privileges, you need to also grant them the `CREATEROLE` attribute. Make sure that you verify password requirements such as expiration and needed complexity on the client side. If you use your own client-side utility for password related changes, the utility needs to be a member of `rds_password` and have `CREATE ROLE` privileges. # Using SCRAM for PostgreSQL password encryption Using SCRAM for PostgreSQL password encryption The *Salted Challenge Response Authentication Mechanism (SCRAM)* is an alternative to PostgreSQL's default message digest (MD5) algorithm for encrypting passwords. The SCRAM authentication mechanism is considered more secure than MD5. To learn more about these two different approaches to securing passwords, see [Password Authentication](https://www.postgresql.org/docs/14/auth-password.html) in the PostgreSQL documentation. We recommend that you use SCRAM rather than MD5 as the password encryption scheme for your RDS for PostgreSQL DB instance. It's a cryptographic challenge-response mechanism that uses the scram-sha-256 algorithm for password authentication and encryption. You might need to update libraries for your client applications to support SCRAM. For example, JDBC versions before 42.2.0 don't support SCRAM. For more information, see [PostgreSQL JDBC Driver](https://jdbc.postgresql.org/changelogs/2018-01-17-42.2.0-release/) in the PostgreSQL JDBC Driver documentation. For a list of other PostgreSQL drivers and SCRAM support, see [List of drivers](https://wiki.postgresql.org/wiki/List_of_drivers) in the PostgreSQL documentation. RDS for PostgreSQL version 13.1 and higher support scram-sha-256. These versions also let you configure your DB instance to require SCRAM, as discussed in the following procedures. ## Setting up RDS for PostgreSQL DB instance to require SCRAM Setting up your DB instance to require SCRAM you can require the RDS for PostgreSQL DB instance to accept only passwords that use the scram-sha-256 algorithm. **Important** For existing RDS Proxies with PostgreSQL databases, if you modify the database authentication to use `SCRAM` only, the proxy becomes unavailable for up to 60 seconds. To avoid the issue, do one of the following: Ensure that the database allows both `SCRAM` and `MD5` authentication. To use only `SCRAM` authentication, create a new proxy, migrate your application traffic to the new proxy, then delete the proxy previously associated with the database. Before making changes to your system, be sure you understand the complete process, as follows: + Get information about all roles and password encryption for all database users. + Double-check the parameter settings for your RDS for PostgreSQL DB instance for the parameters that control password encryption. + If your RDS for PostgreSQL DB instance uses a default parameter group, you need to create a custom DB parameter group and apply it to your RDS for PostgreSQL DB instance so that you can modify parameters when needed. If your RDS for PostgreSQL DB instance uses a custom parameter group, you can modify the necessary parameters later in the process, as needed. + Change the `password_encryption` parameter to `scram-sha-256`. + Notify all database users that they need to update their passwords. Do the same for your `postgres` account. The new passwords are encrypted and stored using the scram-sha-256 algorithm. + Verify that all passwords are encrypted using as the type of encryption. + If all passwords use scram-sha-256, you can change the `rds.accepted_password_auth_method` parameter from `md5+scram` to `scram-sha-256`. **Warning** After you change `rds.accepted_password_auth_method` to scram-sha-256 alone, any users (roles) with `md5`–encrypted passwords can't connect. ### Getting ready to require SCRAM for your RDS for PostgreSQL DB instance Getting ready to require SCRAM Before making any changes to your RDS for PostgreSQL DB instance, check all existing database user accounts. Also, check the type of encryption used for passwords. You can do these tasks by using the `rds_tools` extension. To see which PostgreSQL versions support `rds_tools`, see [Extension versions for Amazon RDS for PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html). **To get a list of database users (roles) and password encryption methods** 1. Use `psql` to connect to your RDS for PostgreSQL DB instance, as shown in the following. ``` psql --host=db-name.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password ``` 1. Install the `rds_tools` extension. ``` postgres=> CREATE EXTENSION rds_tools; CREATE EXTENSION ``` 1. Get a listing of roles and encryption. ``` postgres=> SELECT * FROM rds_tools.role_password_encryption_type(); ``` You see output similar to the following. ``` rolname | encryption_type ----------------------+----------------- pg_monitor | pg_read_all_settings | pg_read_all_stats | pg_stat_scan_tables | pg_signal_backend | lab_tester | md5 user_465 | md5 postgres | md5 (8 rows) ``` ### Creating a custom DB parameter group Creating a custom DB parameter group **Note** If your RDS for PostgreSQL DB instance already uses a custom parameter group, you don't need to create a new one. For an overview of parameter groups for Amazon RDS, see [Working with parameters on your RDS for PostgreSQL DB instance](Appendix.PostgreSQL.CommonDBATasks.Parameters.md). The password encryption type used for passwords is set in one parameter, `password_encryption`. The encryption that the RDS for PostgreSQL DB instance allows is set in another parameter, `rds.accepted_password_auth_method`. Changing either of these from the default values requires that you create a custom DB parameter group and apply it to your instance. You can also use the AWS Management Console or the RDS API to create a custom DB parameter group. For more information, see You can now associate the custom parameter group with your DB instance. **To create a custom DB parameter group** 1. Use the `[create-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-parameter-group.html) ` CLI command to create the custom DB parameter group. This example uses `postgres13` as the source for this custom parameter group. For Linux, macOS, or Unix: ``` aws rds create-db-parameter-group --db-parameter-group-name 'docs-lab-scram-passwords' \ --db-parameter-group-family postgres13 --description 'Custom parameter group for SCRAM' ``` For Windows: ``` aws rds create-db-parameter-group --db-parameter-group-name "docs-lab-scram-passwords" ^ --db-parameter-group-family postgres13 --description "Custom DB parameter group for SCRAM" ``` 1. Use the `[modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html)` CLI command to apply this custom parameter group to your RDS for PostgreSQL DB cluster. For Linux, macOS, or Unix: ``` aws rds modify-db-instance --db-instance-identifier 'your-instance-name' \ --db-parameter-group-name "docs-lab-scram-passwords ``` For Windows: ``` aws rds modify-db-instance --db-instance-identifier "your-instance-name" ^ --db-parameter-group-name "docs-lab-scram-passwords ``` To resynchronize your RDS for PostgreSQL DB instance with your custom DB parameter group, you need to reboot the primary and all other instances of the cluster. To minimize impact to your users, schedule this to occur during your regular maintenance window. ### Configuring password encryption to use SCRAM Configuring parameters for SCRAM The password encryption mechanism used by an RDS for PostgreSQL DB instance is set in the DB parameter group in the `password_encryption` parameter. Allowed values are unset, `md5`, or `scram-sha-256`. The default value depends on the RDS for PostgreSQL version, as follows: + RDS for PostgreSQL 14 and above – Default is `scram-sha-256` + RDS for PostgreSQL 13 – Default is `md5` With a custom DB parameter group attached to your RDS for PostgreSQL DB instance, you can modify values for the password encryption parameter. ![\[Following, the RDS console shows the default values for the password_encryption parameters for RDS for PostgreSQL.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/rpg-pwd-encryption-md5-scram-1.png) **To change password encryption setting to scram-sha-256** + Change the value of password encryption to scram-sha-256, as shown following. The change can be applied immediately because the parameter is dynamic, so a restart isn't required for the change to take effect. For Linux, macOS, or Unix: ``` aws rds modify-db-parameter-group --db-parameter-group-name \ 'docs-lab-scram-passwords' --parameters 'ParameterName=password_encryption,ParameterValue=scram-sha-256,ApplyMethod=immediate' ``` For Windows: ``` aws rds modify-db-parameter-group --db-parameter-group-name ^ "docs-lab-scram-passwords" --parameters "ParameterName=password_encryption,ParameterValue=scram-sha-256,ApplyMethod=immediate" ``` ### Migrating passwords for user roles to SCRAM Migrating passwords for user roles to SCRAM You can migrate passwords for user roles to SCRAM as described following. **To migrate database user (role) passwords from MD5 to SCRAM** 1. Log in as the administrator user (default user name, `postgres`) as shown following. ``` psql --host=db-name.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password ``` 1. Check the setting of the `password_encryption` parameter on your RDS for PostgreSQL DB instance by using the following command. ``` postgres=> SHOW password_encryption; password_encryption --------------------- md5 (1 row) ``` 1. Change the value of this parameter to scram-sha-256. For more information, see [Configuring password encryption to use SCRAM](#PostgreSQL_Password_Encryption_configuration.configure-password-encryption). 1. Check the value again to make sure that it's now set to `scram-sha-256`, as follows. ``` postgres=> SHOW password_encryption; password_encryption --------------------- scram-sha-256 (1 row) ``` 1. Notify all database users to change their passwords. Be sure to also change your own password for account `postgres` (the database user with `rds_superuser` privileges). ``` labdb=> ALTER ROLE postgres WITH LOGIN PASSWORD 'change_me'; ALTER ROLE ``` 1. Repeat the process for all databases on your RDS for PostgreSQL DB instance. ### Changing parameter to require SCRAM Changing parameter to require SCRAM This is the final step in the process. After you make the change in the following procedure, any user accounts (roles) that still use `md5` encryption for passwords can't log in to the RDS for PostgreSQL DB instance. The `rds.accepted_password_auth_method` specifies the encryption method that the RDS for PostgreSQL DB instance accepts for a user password during the login process. The default value is `md5+scram`, meaning that either method is accepted. In the following image, you can find the default setting for this parameter. ![\[The RDS console showing the default and allowed values for the rds.accepted_password_auth_method parameters.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pwd-encryption-md5-scram-2.png) The allowed values for this parameter are `md5+scram` or `scram` alone. Changing this parameter value to `scram` makes this a requirement. **To change the parameter value to require SCRAM authentication for passwords** 1. Verify that all database user passwords for all databases on your RDS for PostgreSQL DB instance use `scram-sha-256` for password encryption. To do so, query `rds_tools` for the role (user) and encryption type, as follows. ``` postgres=> SELECT * FROM rds_tools.role_password_encryption_type(); rolname | encryption_type ----------------------+----------------- pg_monitor | pg_read_all_settings | pg_read_all_stats | pg_stat_scan_tables | pg_signal_backend | lab_tester | scram-sha-256 user_465 | scram-sha-256 postgres | scram-sha-256 ( rows) ``` 1. Repeat the query across all DB instances in your RDS for PostgreSQL DB instance. If all passwords use scram-sha-256, you can proceed. 1. Change the value of the accepted password authentication to scram-sha-256, as follows. For Linux, macOS, or Unix: ``` aws rds modify-db-parameter-group --db-parameter-group-name 'docs-lab-scram-passwords' \ --parameters 'ParameterName=rds.accepted_password_auth_method,ParameterValue=scram,ApplyMethod=immediate' ``` For Windows: ``` aws rds modify-db-parameter-group --db-parameter-group-name "docs-lab-scram-passwords" ^ --parameters "ParameterName=rds.accepted_password_auth_method,ParameterValue=scram,ApplyMethod=immediate" ``` # Dead connection handling in PostgreSQL Dead connection handling in PostgreSQL Dead connections occur when a database session remains active on the server despite the client application having abandoned or terminated abnormally. This situation typically arises when client processes crash or terminate unexpectedly without properly closing their database connections or canceling ongoing requests. PostgreSQL efficiently identifies and cleans up dead connections when server processes are idle or attempt to send data to clients. However, detection is challenging for sessions that are idle, waiting for client input, or actively running queries. To handle these scenarios, PostgreSQL provides `tcp_keepalives_*`, `tcp_user_timeout`, and `client_connection_check_interval` parameters. **Topics** + [ ## Understanding TCP keepalive ](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.Understanding) + [ ## Key TCP keepalive parameters in RDS for PostgreSQL ](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.Parameters) + [ ## Use cases for TCP keepalive settings ](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.UseCases) + [ ## Best practices ](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.BestPractices) ## Understanding TCP keepalive TCP Keepalive is a protocol-level mechanism that helps maintain and verify connection integrity. Each TCP connection maintains kernel-level settings that govern keepalive behavior. When the keepalive timer expires, the system does the following: + Sends a probe packet with no data and the ACK flag set. + Expects a response from the remote endpoint according to TCP/IP specifications. + Manages connection state based on the response or lack thereof. ## Key TCP keepalive parameters in RDS for PostgreSQL | Parameter | Description | Default values | | --- |--- |--- | | tcp\$1keepalives\$1idle | Specifies number of seconds of inactivity before sending keepalive message. | 300 | | tcp\$1keepalives\$1interval | Specifies number of seconds between retransmissions of unacknowledged keepalive messages. | 30 | | tcp\$1keepalives\$1count | Maximum lost keepalive messages before declaring connection dead | 2 | | tcp\$1user\$1timeout | Specifies how long (in Milliseconds) unacknowledged data can remain before forcibly closing the connection. | 0 | | client\$1connection\$1check\$1interval | Sets the interval (in Milliseconds) for checking client connection status during long-running queries. This ensures quicker detection of closed connections. | 0 | ## Use cases for TCP keepalive settings ### Keeping idle sessions alive To prevent idle connections from being terminated by firewalls or routers due to inactivity: + Configure `tcp_keepalives_idle` to send keepalive packets at regular intervals. ### Detecting dead connections To detect dead connections promptly: + Adjust `tcp_keepalives_idle`, `tcp_keepalives_interval`, and `tcp_keepalives_count`. For example, with Aurora PostgreSQL defaults, it takes about a minute (2 probes × 30 seconds) to detect a dead connection. Lowering these values can speed up detection. + Use `tcp_user_timeout` to specify the maximum wait time for an acknowledgment. TCP keepalive settings help the kernel detect dead connections, but PostgreSQL may not act until the socket is used. If a session is running a long query, dead connections might only be detected after query completion. In PostgreSQL 14 and higher versions, `client_connection_check_interval` can expedite dead connection detection by periodically polling the socket during query execution. ## Best practices + **Set reasonable keepalive intervals:** Tune `tcp_user_timeout`, `tcp_keepalives_idle`, `tcp_keepalives_count` and `tcp_keepalives_interval` to balance detection speed and resource use. + **Optimize for your environment:** Align settings with network behavior, firewall policies, and session needs. + **Leverage PostgreSQL features:** Use `client_connection_check_interval` in PostgreSQL 14 and higher versions for efficient connection checks. # Working with PostgreSQL autovacuum on Amazon RDS for PostgreSQL Working with PostgreSQL autovacuum We strongly recommend that you use the autovacuum feature to maintain the health of your PostgreSQL DB instance. Autovacuum automates the start of the VACUUM and the ANALYZE commands. It checks for tables with a large number of inserted, updated, or deleted tuples. After this check, it reclaims storage by removing obsolete data or tuples from the PostgreSQL database. By default, autovacuum is turned on for the RDS for PostgreSQL DB instances that you create using any of the default PostgreSQL DB parameter groups. Other configuration parameters associated with the autovacuum feature are also set by default. Because these defaults are somewhat generic, you can benefit from tuning some of the parameters associated with the autovacuum feature for your specific workload. Following, you can find more information about the autovacuum and how to tune some of its parameters on your RDS for PostgreSQL DB instance. For high-level information, see [Best practices for working with PostgreSQL](CHAP_BestPractices.md#CHAP_BestPractices.PostgreSQL). **Topics** + [ ## Allocating memory for autovacuum ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.WorkMemory) + [ ## Reducing the likelihood of transaction ID wraparound ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming) + [ # Determining if the tables in your database need vacuuming ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.NeedVacuuming.md) + [ # Determining which tables are currently eligible for autovacuum ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.EligibleTables.md) + [ # Determining if autovacuum is currently running and for how long ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AutovacuumRunning.md) + [ # Performing a manual vacuum freeze ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md) + [ # Reindexing a table when autovacuum is running ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Reindexing.md) + [ # Managing autovacuum with large indexes ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.md) + [ # Other parameters that affect autovacuum ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.OtherParms.md) + [ # Setting table-level autovacuum parameters ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.TableParameters.md) + [ # Logging autovacuum and vacuum activities ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Logging.md) + [ # Understanding the behavior of autovacuum with invalid databases ](appendix.postgresql.commondbatasks.autovacuumbehavior.md) + [ # Identify and resolve aggressive vacuum blockers in RDS for PostgreSQL ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.md) ## Allocating memory for autovacuum One of the most important parameters influencing autovacuum performance is the [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM) parameter. In RDS for PostgreSQL versions 14 and prior, the `autovacuum_work_mem` parameter is set to -1, indicating that the setting of `maintenance_work_mem` is used instead. For all other versions, `autovacuum_work_mem` is determined by GREATEST(\$1DBInstanceClassMemory/32768\$1, 65536). Manual vacuum operations always use the `maintenance_work_mem` setting, with a default setting of GREATEST(\$1DBInstanceClassMemory/63963136\$11024\$1, 65536), and it can also be adjusted at the session level using the `SET` command for more targeted manual `VACUUM` operations. The `autovacuum_work_mem` determines memory for autovacuum to hold identifiers of dead tuples (`pg_stat_all_tables.n_dead_tup`) for vacuuming indexes. When doing calculations to determine the `autovacuum_work_mem` parameter's value, be aware of the following: + If you set the parameter too low, the vacuum process might have to scan the table multiple times to complete its work. Such multiple scans can have a negative impact on performance. For larger instances, setting `maintenance_work_mem` or `autovacuum_work_mem` to at least 1 GB can improve the performance of vacuuming tables with a high number of dead tuples. However, in PostgreSQL versions 16 and prior, vacuum’s memory usage is capped at 1 GB, which is sufficient to process approximately 179 million dead tuples in a single pass. If a table has more dead tuples than this, vacuum will need to make multiple passes through the table's indexes, significantly increasing the time required. Starting with PostgreSQL version 17, there isn't a limit of 1 GB, and autovacuum can process more than 179 million tuples by using radix trees. A tuple identifier is 6 bytes in size. To estimate the memory needed for vacuuming an index of a table, query `pg_stat_all_tables.n_dead_tup` to find the number of dead tuples, then multiply this number by 6 to determine the memory required for vacuuming the index in a single pass. You may use the following query: ``` SELECT relname AS table_name, n_dead_tup, pg_size_pretty(n_dead_tup * 6) AS estimated_memory FROM pg_stat_all_tables WHERE relname = 'name_of_the_table'; ``` + The `autovacuum_work_mem` parameter works in conjunction with the `autovacuum_max_workers` parameter. Each worker among `autovacuum_max_workers` can use the memory that you allocate. If you have many small tables, allocate more `autovacuum_max_workers` and less `autovacuum_work_mem`. If you have large tables (larger than 100 GB), allocate more memory and fewer worker processes. You need to have enough memory allocated to succeed on your biggest table. Thus, make sure that the combination of worker processes and memory equals the total memory that you want to allocate. ## Reducing the likelihood of transaction ID wraparound In some cases, parameter group settings related to autovacuum might not be aggressive enough to prevent transaction ID wraparound. To address this, RDS for PostgreSQL provides a mechanism that adapts the autovacuum parameter values automatically. *Adaptive autovacuum* is a feature for RDS for PostgreSQL. A detailed explanation of [TransactionID wraparound](https://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND) is found in the PostgreSQL documentation. Adaptive autovacuum is turned on by default for RDS for PostgreSQL instances with the dynamic parameter `rds.adaptive_autovacuum` set to ON. We strongly recommend that you keep this turned on. However, to turn off adaptive autovacuum parameter tuning, set the `rds.adaptive_autovacuum` parameter to 0 or OFF. Transaction ID wraparound is still possible even when Amazon RDS Amazon RDS tunes the autovacuum parameters. We encourage you to implement an Amazon CloudWatch alarm for transaction ID wraparound. For more information, see the post [Implement an early warning system for transaction ID wraparound in RDS for PostgreSQL](https://aws.amazon.com/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/) on the AWS Database Blog. With adaptive autovacuum parameter tuning turned on, Amazon RDS begins adjusting autovacuum parameters when the CloudWatch metric `MaximumUsedTransactionIDs` reaches the value of the `autovacuum_freeze_max_age` parameter or 500,000,000, whichever is greater. Amazon RDS continues to adjust parameters for autovacuum if a table continues to trend toward transaction ID wraparound. Each of these adjustments dedicates more resources to autovacuum to avoid wraparound. Amazon RDS updates the following autovacuum-related parameters: + [autovacuum\$1vacuum\$1cost\$1delay](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY) + [ autovacuum\$1vacuum\$1cost\$1limit](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT) + [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM) + [autovacuum\$1naptime](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-NAPTIME) RDS modifies these parameters only if the new value makes autovacuum more aggressive. The parameters are modified in memory on the DB instance. The values in the parameter group aren't changed. To view the current in-memory settings, use the PostgreSQL [SHOW](https://www.postgresql.org/docs/current/sql-show.html) SQL command. When Amazon RDS modifies any of these autovacuum parameters, it generates an event for the affected DB instance. This event is visible on the AWS Management Console and through the Amazon RDS API. After the `MaximumUsedTransactionIDs` CloudWatch metric returns below the threshold, Amazon RDS resets the autovacuum-related parameters in memory back to the values specified in the parameter group. It then generates another event corresponding to this change. # Determining if the tables in your database need vacuuming You can use the following query to show the number of unfrozen transactions in a database. The `datfrozenxid` column of a database's `pg_database` row is a lower bound on the normal transaction IDs appearing in that database. This column is the minimum of the per-table `relfrozenxid` values within the database. ``` SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20; ``` For example, the results of running the preceding query might be the following. ``` datname | age mydb | 1771757888 template0 | 1721757888 template1 | 1721757888 rdsadmin | 1694008527 postgres | 1693881061 (5 rows) ``` When the age of a database reaches 2 billion transaction IDs, transaction ID (XID) wraparound occurs and the database becomes read-only. You can use this query to produce a metric and run a few times a day. By default, autovacuum is set to keep the age of transactions to no more than 200,000,000 ([https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE)). A sample monitoring strategy might look like this: + Set the `autovacuum_freeze_max_age` value to 200 million transactions. + If a table reaches 500 million unfrozen transactions, that triggers a low-severity alarm. This isn't an unreasonable value, but it can indicate that autovacuum isn't keeping up. + If a table ages to 1 billion, this should be treated as an alarm to take action on. In general, you want to keep ages closer to `autovacuum_freeze_max_age` for performance reasons. We recommend that you investigate using the recommendations that follow. + If a table reaches 1.5 billion unvacuumed transactions, that triggers a high-severity alarm. Depending on how quickly your database uses transaction IDs, this alarm can indicate that the system is running out of time to run autovacuum. In this case, we recommend that you resolve this immediately. If a table is constantly breaching these thresholds, modify your autovacuum parameters further. By default, using VACUUM manually (which has cost-based delays disabled) is more aggressive than using the default autovacuum, but it is also more intrusive to the system as a whole. We recommend the following: + Be aware and turn on a monitoring mechanism so that you are aware of the age of your oldest transactions. For information on creating a process that warns you about transaction ID wraparound, see the AWS Database Blog post [Implement an early warning system for transaction ID wraparound in Amazon RDS for PostgreSQL](https://aws.amazon.com/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/). + For busier tables, perform a manual vacuum freeze regularly during a maintenance window, in addition to relying on autovacuum. For information on performing a manual vacuum freeze, see [Performing a manual vacuum freeze](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md). # Determining which tables are currently eligible for autovacuum Often, it is one or two tables in need of vacuuming. Tables whose `relfrozenxid` value is greater than the number of transactions in `autovacuum_freeze_max_age` are always targeted by autovacuum. Otherwise, if the number of tuples made obsolete since the last VACUUM exceeds the vacuum threshold, the table is vacuumed. The [autovacuum threshold](https://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM) is defined as: ``` Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples ``` where the `vacuum base threshold` is `autovacuum_vacuum_threshold`, the `vacuum scale factor` is `autovacuum_vacuum_scale_factor`, and the `number of tuples` is `pg_class.reltuples`. While you are connected to your database, run the following query to see a list of tables that autovacuum sees as eligible for vacuuming. ``` WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold'), vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'), fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'), sto AS (select opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation, pg_size_pretty(pg_table_size(c.oid)) as table_size, age(relfrozenxid) as xid_age, coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age, (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1) left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid WHERE c.relkind = 'r' and nspname <> 'pg_catalog' AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup) ORDER BY age(relfrozenxid) DESC LIMIT 50; ``` # Determining if autovacuum is currently running and for how long If you need to manually vacuum a table, make sure to determine if autovacuum is currently running. If it is, you might need to adjust parameters to make it run more efficiently, or turn off autovacuum temporarily so that you can manually run VACUUM. Use the following query to determine if autovacuum is running, how long it has been running, and if it is waiting on another session. ``` SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start; ``` After running the query, you should see output similar to the following. ``` datname | usename | pid | state | wait_event | xact_runtime | query --------+----------+-------+--------+------------+-------------------------+-------------------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+ | | | | | | FROM pg_stat_activity + | | | | | | WHERE query like '%VACUUM%' + | | | | | | ORDER BY xact_start; + ``` Several issues can cause a long-running autovacuum session (that is, multiple days long). The most common issue is that your [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) parameter value is set too low for the size of the table or rate of updates. We recommend that you use the following formula to set the `maintenance_work_mem` parameter value. ``` GREATEST({DBInstanceClassMemory/63963136*1024},65536) ``` Short running autovacuum sessions can also indicate problems: + It can indicate that there aren't enough `autovacuum_max_workers` for your workload. In this case, you need to indicate the number of workers. + It can indicate that there is an index corruption (autovacuum crashes and restarts on the same relation but makes no progress). In this case, run a manual `vacuum freeze verbose table` to see the exact cause. # Performing a manual vacuum freeze You might want to perform a manual vacuum on a table that has a vacuum process already running. This is useful if you have identified a table with an age approaching 2 billion transactions (or above any threshold you are monitoring). The following steps are guidelines, with several variations to the process. For example, during testing, suppose that you find that the [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) parameter value is set too small and that you need to take immediate action on a table. However, perhaps you don't want to bounce the instance at the moment. Using the queries in previous sections, you determine which table is the problem and notice a long running autovacuum session. You know that you need to change the `maintenance_work_mem` parameter setting, but you also need to take immediate action and vacuum the table in question. The following procedure shows what to do in this situation. **To manually perform a vacuum freeze** 1. Open two sessions to the database containing the table you want to vacuum. For the second session, use "screen" or another utility that maintains the session if your connection is dropped. 1. In session one, get the process ID (PID) of the autovacuum session running on the table. Run the following query to get the PID of the autovacuum session. ``` SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start; ``` 1. In session two, calculate the amount of memory that you need for this operation. In this example, we determine that we can afford to use up to 2 GB of memory for this operation, so we set [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) for the current session to 2 GB. ``` SET maintenance_work_mem='2 GB'; SET ``` 1. In session two, issue a `vacuum freeze verbose` command for the table. The verbose setting is useful because, although there is no progress report for this in PostgreSQL currently, you can see activity. ``` \timing on Timing is on. vacuum freeze verbose pgbench_branches; ``` ``` INFO: vacuuming "public.pgbench_branches" INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions in 43 out of 43 pages DETAIL: 0 dead row versions cannot be removed yet. There were 9347 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Time: 2.765 ms ``` 1. In session one, if autovacuum was blocking the vacuum session, `pg_stat_activity` shows that waiting is `T` for your vacuum session. In this case, end the autovacuum process as follows. ``` SELECT pg_terminate_backend('the_pid'); ``` **Note** Some lower versions of Amazon RDS can't terminate an autovacuum process using the preceding command and fail with the following error: `ERROR: 42501: must be a superuser to terminate superuser process LOCATION: pg_terminate_backend, signalfuncs.c:227`. At this point, your session begins. Autovacuum restarts immediately because this table is probably the highest on its list of work. 1. Initiate your `vacuum freeze verbose` command in session two, and then end the autovacuum process in session one. # Reindexing a table when autovacuum is running If an index has become corrupt, autovacuum continues to process the table and fails. If you attempt a manual vacuum in this situation, you receive an error message like the following. ``` postgres=> vacuum freeze pgbench_branches; ERROR: index "pgbench_branches_test_index" contains unexpected zero page at block 30521 HINT: Please REINDEX it. ``` When the index is corrupted and autovacuum is attempting to run on the table, you contend with an already running autovacuum session. When you issue a [REINDEX](https://www.postgresql.org/docs/current/static/sql-reindex.html) command, you take out an exclusive lock on the table. Write operations are blocked, and also read operations that use that specific index. **To reindex a table when autovacuum is running on the table** 1. Open two sessions to the database containing the table that you want to vacuum. For the second session, use "screen" or another utility that maintains the session if your connection is dropped. 1. In session one, get the PID of the autovacuum session running on the table. Run the following query to get the PID of the autovacuum session. ``` SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start; ``` 1. In session two, issue the reindex command. ``` \timing on Timing is on. reindex index pgbench_branches_test_index; REINDEX Time: 9.966 ms ``` 1. In session one, if autovacuum was blocking the process, you see in `pg_stat_activity` that waiting is "T" for your vacuum session. In this case, you end the autovacuum process. ``` SELECT pg_terminate_backend('the_pid'); ``` At this point, your session begins. It's important to note that autovacuum restarts immediately because this table is probably the highest on its list of work. 1. Initiate your command in session two, and then end the autovacuum process in session 1. # Managing autovacuum with large indexes As part of its operation, *autovacuum* performs several [ vacuum phases](https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PHASES) while running on a table. Before the table is cleaned up, all of its indexes are first vacuumed. When removing multiple large indexes, this phase consumes a significant amount of time and resources. Therefore, as a best practice, be sure to control the number of indexes on a table and eliminate unused indexes. For this process, first check the overall index size. Then, determine if there are potentially unused indexes that can be removed as shown in the following examples. **To check the size of the table and its indexes** ``` postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts')); pg_size_pretty 6404 MB (1 row) ``` ``` postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts')); pg_size_pretty 11 GB (1 row) ``` In this example, the size of indexes is larger than the table. This difference can cause performance issues as the indexes are bloated or unused, which impacts the autovacuum as well as insert operations. **To check for unused indexes** Using the [https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW) view, you can check how frequently an index is used with the `idx_scan` column. In the following example, the unused indexes have the `idx_scan` value of `0`. ``` postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc; relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+------------------+-----------------------+----------+--------------+--------------- 16433 | 16454 | public | pgbench_accounts | index_f | 6 | 6 | 0 16433 | 16450 | public | pgbench_accounts | index_b | 3 | 199999 | 0 16433 | 16447 | public | pgbench_accounts | pgbench_accounts_pkey | 0 | 0 | 0 16433 | 16452 | public | pgbench_accounts | index_d | 0 | 0 | 0 16433 | 16453 | public | pgbench_accounts | index_e | 0 | 0 | 0 16433 | 16451 | public | pgbench_accounts | index_c | 0 | 0 | 0 16433 | 16449 | public | pgbench_accounts | index_a | 0 | 0 | 0 (7 rows) ``` ``` postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc; schemaname | relname | indexrelname | idx_scan ------------+------------------+-----------------------+---------- public | pgbench_accounts | index_f | 6 public | pgbench_accounts | index_b | 3 public | pgbench_accounts | pgbench_accounts_pkey | 0 public | pgbench_accounts | index_d | 0 public | pgbench_accounts | index_e | 0 public | pgbench_accounts | index_c | 0 public | pgbench_accounts | index_a | 0 (7 rows) ``` **Note** These statistics are incremental from the time that the statistics are reset. Suppose you have an index that is only used at the end of a business quarter or just for a specific report. It's possible that this index hasn't been used since the statistics were reset. For more information, see [Statistics Functions](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-FUNCTIONS). Indexes that are used to enforce uniqueness won't have scans performed and shouldn't be identified as unused indexes. To identify the unused indexes, you should have in-depth knowledge of the application and its queries. To check when the stats were last reset for a database, use [ https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW]( https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW) ``` postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres'; datname | stats_reset ----------+------------------------------- postgres | 2022-11-17 08:58:11.427224+00 (1 row) ``` ## Vacuuming a table as quickly as possible **RDS for PostgreSQL 12 and higher** If you have too many indexes in a large table, your DB instance could be nearing transaction ID wraparound (XID), which is when the XID counter wraps around to zero. Left unchecked, this situation could result in data loss. However, you can quickly vacuum the table without cleaning up the indexes. In RDS for PostgreSQL 12 and higher, you can use VACUUM with the [https://www.postgresql.org/docs/current/sql-vacuum.html](https://www.postgresql.org/docs/current/sql-vacuum.html) clause. ``` postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts; INFO: vacuuming "public.pgbench_accounts" INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s. ``` If an autovacuum session is already running, you must terminate it to begin the manual VACUUM. For information on performing a manual vacuum freeze, see [Performing a manual vacuum freeze](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md) **Note** Skipping index cleanup regularly causes index bloat, which degrades scan performance. The index retains dead rows, and the table retains dead line pointers. As a result, `pg_stat_all_tables.n_dead_tup` increases until autovacuum or a manual VACUUM with index cleanup runs. As a best practice, use this procedure only to prevent transaction ID wraparound. **RDS for PostgreSQL 11 and older** However, in RDS for PostgreSQL 11 and lower versions, the only way to allow vacuum to complete faster is to reduce the number of indexes on a table. Dropping an index can affect query plans. We recommend that you drop unused indexes first, then drop the indexes when XID wraparound is very near. After the vacuum process completes, you can recreate these indexes. # Other parameters that affect autovacuum The following query shows the values of some of the parameters that directly affect autovacuum and its behavior. The [autovacuum parameters](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html) are described fully in the PostgreSQL documentation. ``` SELECT name, setting, unit, short_desc FROM pg_settings WHERE name IN ( 'autovacuum_max_workers', 'autovacuum_analyze_scale_factor', 'autovacuum_naptime', 'autovacuum_analyze_threshold', 'autovacuum_analyze_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_cost_delay', 'autovacuum_vacuum_cost_limit', 'vacuum_cost_limit', 'autovacuum_freeze_max_age', 'maintenance_work_mem', 'vacuum_freeze_min_age'); ``` While these all affect autovacuum, some of the most important ones are: + [maintenance\$1work\$1mem](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE_WORK_MEM) + [autovacuum\$1freeze\$1max\$1age](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE) + [autovacuum\$1max\$1workers](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS) + [autovacuum\$1vacuum\$1cost\$1delay](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY) + [ autovacuum\$1vacuum\$1cost\$1limit](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT) # Setting table-level autovacuum parameters You can set autovacuum-related [storage parameters](https://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS) at a table level, which can be better than altering the behavior of the entire database. For large tables, you might need to set aggressive settings and you might not want to make autovacuum behave that way for all tables. The following query shows which tables currently have table-level options in place. ``` SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT null; ``` An example where this might be useful is on tables that are much larger than the rest of your tables. Suppose that you have one 300-GB table and 30 other tables less than 1 GB. In this case, you might set some specific parameters for your large table so you don't alter the behavior of your entire system. ``` ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0); ``` Doing this turns off the cost-based autovacuum delay for this table at the expense of more resource usage on your system. Normally, autovacuum pauses for `autovacuum_vacuum_cost_delay` each time `autovacuum_cost_limit` is reached. For more details, see the PostgreSQL documentation about [cost-based vacuuming](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST). # Logging autovacuum and vacuum activities Information about autovacuum activities is sent to the `postgresql.log` based on the level specified in the `rds.force_autovacuum_logging_level` parameter. Following are the values allowed for this parameter and the PostgreSQL versions for which that value is the default setting: + `disabled` (PostgreSQL 10, PostgreSQL 9.6) + `debug5`, `debug4`, `debug3`, `debug2`, `debug1` + `info` (PostgreSQL 12, PostgreSQL 11) + `notice` + `warning` (PostgreSQL 13 and above) + `error`, log, `fatal`, `panic` The `rds.force_autovacuum_logging_level` works with the `log_autovacuum_min_duration` parameter. The `log_autovacuum_min_duration` parameter's value is the threshold (in milliseconds) above which autovacuum actions get logged. A setting of `-1` logs nothing, while a setting of 0 logs all actions. As with `rds.force_autovacuum_logging_level`, default values for `log_autovacuum_min_duration` are version dependent, as follows: + `10000 ms` – PostgreSQL 14, PostgreSQL 13, PostgreSQL 12, and PostgreSQL 11 + `(empty)` – No default value for PostgreSQL 10 and PostgreSQL 9.6 We recommend that you set `rds.force_autovacuum_logging_level` to `WARNING`. We also recommend that you set `log_autovacuum_min_duration` to a value from 1000 to 5000. A setting of 5000 logs activity that takes longer than 5,000 milliseconds. Any setting other than -1 also logs messages if the autovacuum action is skipped because of a conflicting lock or concurrently dropped relations. For more information, see [Automatic Vacuuming](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html) in the PostgreSQL documentation. To troubleshoot issues, you can change the `rds.force_autovacuum_logging_level` parameter to one of the debug levels, from `debug1` up to `debug5` for the most verbose information. We recommend that you use debug settings for short periods of time and for troubleshooting purposes only. To learn more, see [When to log](https://www.postgresql.org/docs/current/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN) in the PostgreSQL documentation. **Note** PostgreSQL allows the `rds_superuser` account to view autovacuum sessions in `pg_stat_activity`. For example, you can identify and end an autovacuum session that is blocking a command from running, or running slower than a manually issued vacuum command. # Understanding the behavior of autovacuum with invalid databases Understanding the behavior of autovacuum with invalid databases A new value `-2` is introduced into the `datconnlimit` column in the `pg_database` catalog to indicate databases that have been interrupted in the middle of the DROP DATABASE operation as invalid. This new value is available from the following RDS for PostgreSQL versions: + 15.4 and all higher versions + 14.9 and higher versions + 13.12 and higher versions + 12.16 and higher versions + 11.21 and higher versions Invalid databases do not affect autovacuum's ability to freeze functionality for valid databases. Autovacuum ignores invalid databases. Consequently, regular vacuum operations will continue to function properly and efficiently for all valid databases in your PostgreSQL environment. **Topics** + [ ## Monitoring transaction ID ](#appendix.postgresql.commondbatasks.autovacuum.monitorxid) + [ ## Adjusting the monitoring query ](#appendix.postgresql.commondbatasks.autovacuum.monitoradjust) + [ ## Resolving invalid database issue ](#appendix.postgresql.commondbatasks.autovacuum.connissue) ## Monitoring transaction ID The `age(datfrozenxid)` function is commonly used to monitor the transaction ID (XID) age of databases to prevent transaction ID wraparound. Since invalid databases are excluded from autovacuum, their transaction ID (XID) counter can reach the maximum value of `2 billion`, wrap around to `- 2 billion`, and continue this cycle indefinitely. A typical query to monitor Transaction ID wraparound might look like: ``` SELECT max(age(datfrozenxid)) FROM pg_database; ``` However, with the introduction of the -2 value for `datconnlimit`, invalid databases can skew the results of this query. Since these databases are not valid and should not be part of regular maintenance checks, they can cause false positives, leading you to believe that the `age(datfrozenxid)` is higher than it actually is. ## Adjusting the monitoring query To ensure accurate monitoring, you should adjust your monitoring query to exclude invalid databases. Follow this recommended query: ``` SELECT max(age(datfrozenxid)) FROM pg_database WHERE datconnlimit <> -2; ``` This query ensures that only valid databases are considered in the `age(datfrozenxid)` calculation, providing a true reflection of the transaction ID age across your PostgreSQL environment. ## Resolving invalid database issue When attempting to connect to an invalid database, you may encounter an error message similar to the following: ``` postgres=> \c db1 connection to server at "mydb.xxxxxxxxxx.us-west-2.rds.amazonaws.com" (xx.xx.xx.xxx), port xxxx failed: FATAL: cannot connect to invalid database "db1" HINT: Use DROP DATABASE to drop invalid databases. Previous connection kept ``` Additionally, if the `log_min_messages` parameter is set to `DEBUG2` or higher, you may notice the following log entries indicating that the autovacuum process is skipping the invalid database: ``` 2024-07-30 05:59:00 UTC::@:[32000]:DEBUG: autovacuum: skipping invalid database "db6" 2024-07-30 05:59:00 UTC::@:[32000]:DEBUG: autovacuum: skipping invalid database "db1" ``` To resolve the issue, follow the `HINT` provided during the connection attempt. Connect to any valid database using your RDS master account or a database account with the `rds_superuser` role, and drop invalid database(s). ``` SELECT 'DROP DATABASE ' || quote_ident(datname) || ';' FROM pg_database WHERE datconnlimit = -2 \gexec ``` # Identify and resolve aggressive vacuum blockers in RDS for PostgreSQL Identifying vacuum blockers In PostgreSQL, vacuuming is vital for ensuring database health as it reclaims storage and prevents [transaction ID wraparound](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND) issues. However, there are times when vacuuming can be prevented from operating as desired, which can result in performance degradation, storage bloat, and even impact availability of your DB instance by transaction ID wraparound. Therefore, identifying and resolving these issues are essential for optimal database performance and availability. Read [Understanding autovacuum in Amazon RDS for PostgreSQL environments](https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/) to learn more about autovacuum. The `postgres_get_av_diag()` function helps identify issues that either prevent or delay the aggressive vacuum progress. Suggestions are provided, which may include commands to resolve the issue where it is identifiable or guidance for further diagnostics where the issue is not identifiable. Aggressive vacuum blockers are reported when the age exceeds RDS' [adaptive autovacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming) threshold of 500 million transaction IDs. **What is the age of the transaction ID?** The `age()` function for transaction IDs calculates the number of transactions that have occurred since the oldest unfrozen transaction ID for a database (`pg_database.datfrozenxid`) or table (`pg_class.relfrozenxid`). This value indicates database activity since the last aggressive vacuum operation and highlights the likely workload for upcoming VACUUM processes. **What is an aggressive vacuum?** An aggressive VACUUM operation conducts a comprehensive scan of all pages within a table, including those typically skipped during regular VACUUMs. This thorough scan aims to "freeze" transaction IDs approaching their maximum age, effectively preventing a situation known as [transaction ID wraparound](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND). For `postgres_get_av_diag()` to report blockers, the blocker must be at least 500 million transactions old. **Topics** + [ # Installing autovacuum monitoring and diagnostic tools in RDS for PostgreSQL ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Installation.md) + [ # Functions of postgres\$1get\$1av\$1diag() in RDS for PostgreSQL ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Functions.md) + [ # Resolving identifiable vacuum blockers in RDS for PostgreSQL ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md) + [ # Resolving unidentifiable vacuum blockers in RDS for PostgreSQL ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Unidentifiable_blockers.md) + [ # Resolving vacuum performance issues in RDS for PostgreSQL ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Performance.md) + [ # Explanation of the NOTICE messages in RDS for PostgreSQL ](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md) # Installing autovacuum monitoring and diagnostic tools in RDS for PostgreSQL Installing autovacuum monitoring tools The `postgres_get_av_diag()` function is currently available in the following RDS for PostgreSQL versions: + 17.2 and higher 17 versions + 16.7 and higher 16 versions + 15.11 and higher 15 versions + 14.16 and higher 14 versions + 13.19 and higher 13 versions In order to use `postgres_get_av_diag()`, create the `rds_tools` extension. ``` postgres=> CREATE EXTENSION rds_tools ; CREATE EXTENSION ``` Verify that the extension is installed. ``` postgres=> \dx rds_tools List of installed extensions Name | Version | Schema | Description ----------+---------+-----------+---------------------------------------------------------- rds_tools | 1.8 | rds_tools | miscellaneous administrative functions for RDS PostgreSQL 1 row ``` Verify that the function is created. ``` postgres=> SELECT proname function_name, pronamespace::regnamespace function_schema, proowner::regrole function_owner FROM pg_proc WHERE proname = 'postgres_get_av_diag'; function_name | function_schema | function_owner ----------------------+-----------------+---------------- postgres_get_av_diag | rds_tools | rds_superuser (1 row) ``` # Functions of postgres\$1get\$1av\$1diag() in RDS for PostgreSQL Functions of postgres\$1get\$1av\$1diag() The `postgres_get_av_diag()` function retrieves diagnostic information about autovacuum processes that are blocking or lagging behind in a RDS for PostgreSQL database. The query needs to be executed in the database with the oldest transaction ID for accurate results. For more information about using the database with the oldest transaction ID, see [Not connected to the database with the age of oldest transaction ID](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md) ``` SELECT blocker, DATABASE, blocker_identifier, wait_event, TO_CHAR(autovacuum_lagging_by, 'FM9,999,999,999') AS autovacuum_lagging_by, suggestion, suggested_action FROM ( SELECT * FROM rds_tools.postgres_get_av_diag () ORDER BY autovacuum_lagging_by DESC) q; ``` The `postgres_get_av_diag()` function returns a table with the following information: **blocker** Specifies the category of database activity that is blocking the vacuum. + [Active statement](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Active_statement) + [Idle in transaction](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Idle_in_transaction) + [Prepared transaction](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Prepared_transaction) + [Logical replication slot](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Logical_replication_slot) + [Read replica with physical replication slot](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas) + [Read replica with streaming replication](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas) + [Temporary tables](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Temporary_tables) **database** Specifies the name of the database where applicable and supported. This is the database in which the activity is ongoing and blocking or will block the autovacuum. This is the database you are required to connect to and take action. **blocker\$1identifier** Specifies the identifier of the activity that is blocking or will block the autovacuum. The identifier can be a process ID along with a SQL statement, a prepared transaction, an IP address of a read replica, and the name of the replication slot, either logical or physical. **wait\$1event** Specifies the [wait event](PostgreSQL.Tuning.md) of the blocking session and is applicable for the following blockers: + Active statement + Idle in transaction **autovacum\$1lagging\$1by** Specifies the number of transactions that autovacuum is lagging behind in its backlog work per category. **suggestion** Specifies suggestions to resolve the blocker. These instructions include the name of the database in which the activity exists where applicable, the Process ID (PID) of the session where applicable, and the action to be taken. **suggested\$1action** Suggests the action that needs to be taken to resolve the blocker. # Resolving identifiable vacuum blockers in RDS for PostgreSQL Resolving identifiable vacuum blockers Autovacuum performs aggressive vacuums and lowers the age of transaction IDs to below the threshold specified by the `autovacuum_freeze_max_age` parameter of your RDS instance. You can track this age using the Amazon CloudWatch metric `MaximumUsedTransactionIDs`. To find the setting of `autovacuum_freeze_max_age` (which has a default of 200 million transaction IDs) for your Amazon RDS instance, you can use the following query: ``` SELECT TO_CHAR(setting::bigint, 'FM9,999,999,999') autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'; ``` Note that `postgres_get_av_diag()` only checks for aggressive vacuum blockers when the age exceeds Amazon RDS’ [adaptive autovacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming) threshold of 500 million transaction IDs. For `postgres_get_av_diag()` to detect blockers, the blocker must be at least 500 million transactions old. The `postgres_get_av_diag()` function identifies the following types of blockers: **Topics** + [ ## Active statement ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Active_statement) + [ ## Idle in transaction ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Idle_in_transaction) + [ ## Prepared transaction ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Prepared_transaction) + [ ## Logical replication slot ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Logical_replication_slot) + [ ## Read replicas ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas) + [ ## Temporary tables ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Temporary_tables) ## Active statement In PostgreSQL, an active statement is an SQL statement that is currently being executed by the database. This includes queries, transactions, or any operations in progress. When monitoring via `pg_stat_activity`, the state column indicates that the process with the corresponding PID is active. The `postgres_get_av_diag()` function displays output similar to the following when it identifies a statement that is an active statement. ``` blocker | Active statement database | my_database blocker_identifier | SELECT pg_sleep(20000); wait_event | Timeout:PgSleep autovacuum_lagging_by | 568,600,871 suggestion | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. suggested_action | {"SELECT pg_terminate_backend (29621);"} ``` **Suggested action** Following the guidance in the `suggestion` column, the user can connect to the database where the active statement is present and, as specified in the `suggested_action` column, it's advisable to carefully review the option to terminate the session. If termination is safe, you may use the `pg_terminate_backend()` function to terminate the session. This action can be performed by an administrator (such as the RDS master account) or a user with the required `pg_terminate_backend()` privilege. **Warning** A terminated session will undo (`ROLLBACK`) changes it made. Depending on your requirements, you may want to rerun the statement. However, it is recommended to do so only after the autovacuum process has finished its aggressive vacuum operation. ## Idle in transaction An idle in transaction statement refers to any session that has opened an explicit transaction (such as by issuing a `BEGIN` statement), performed some work, and is now waiting for the client to either pass more work or signal the end of the transaction by issuing a `COMMIT`, `ROLLBACK`, or `END` (which would result in an implicit `COMMIT`). The `postgres_get_av_diag()` function displays output similar to the following when it identifies an `idle in transaction` statement as a blocker. ``` blocker | idle in transaction database | my_database blocker_identifier | INSERT INTO tt SELECT * FROM tt; wait_event | Client:ClientRead autovacuum_lagging_by | 1,237,201,759 suggestion | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. suggested_action | {"SELECT pg_terminate_backend (28438);"} ``` **Suggested action** As indicated in the `suggestion` column, you can connect to the database where the idle in transaction session is present and terminate the session using the `pg_terminate_backend()` function. The user can be your admin (RDS master account) user or a user with the `pg_terminate_backend()` privilege. **Warning** A terminated session will undo (`ROLLBACK`) changes it made. Depending on your requirements, you may want to rerun the statement. However, it is recommended to do so only after the autovacuum process has finished its aggressive vacuum operation. ## Prepared transaction PostgreSQL allows transactions that are part of a two-phase commit strategy called [prepared transactions](https://www.postgresql.org/docs/current/sql-prepare-transaction.html). These are enabled by setting the `max_prepared_transactions` parameter to a non-zero value. Prepared transactions are designed to ensure that a transaction is durable and remains available even after database crashes, restarts, or client disconnections. Like regular transactions, they are assigned a transaction ID and can affect the autovacuum. If left in a prepared state, autovacuum cannot perform freeezing and it can lead to transaction ID wraparound. When transactions are left prepared indefinitely without being resolved by a transaction manager, they become orphaned prepared transactions. The only way to fix this is to either commit or rollback the transaction using the `COMMIT PREPARED` or `ROLLBACK PREPARED` commands, respectively. **Note** Be aware that a backup taken during a prepared transaction will still contain that transaction after restoration. Refer to the following information about how to locate and close such transactions. The `postgres_get_av_diag()` function displays the following output when it identifies a blocker that is a prepared transaction. ``` blocker | Prepared transaction database | my_database blocker_identifier | myptx wait_event | Not applicable autovacuum_lagging_by | 1,805,802,632 suggestion | Connect to database "my_database" and consider either COMMIT or ROLLBACK the prepared transaction using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. suggested_action | {"COMMIT PREPARED 'myptx';",[OR],"ROLLBACK PREPARED 'myptx';"} ``` **Suggested action** As mentioned in the suggestion column, connect to the database where the prepared transaction is located. Based on the `suggested_action` column, carefully review whether to perform either `COMMIT` or `ROLLBACK`, and the the appropiate the action. To monitor prepared transactions in general, PostgreSQL offers a catalog view called `pg_prepared_xacts`. You can use the following query to find prepared transactions. ``` SELECT gid, prepared, owner, database, transaction AS oldest_xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC; ``` ## Logical replication slot The purpose of a replication slot is to hold unconsumed changes until they are replicated to a target server. For more information, see PostgreSQL's [Logical replication](https://www.postgresql.org/docs/current/logical-replication.html). There are two types of logical replication slots. **Inactive logical replication slots** When replication is terminated, unconsumed transaction logs can't be removed, and the replication slot becomes inactive. Although an inactive logical replication slot isn't currently used by a subscriber, it remains on the server, leading to the retention of WAL files and preventing the removal of old transaction logs. This can increase disk usage and specifically block autovacuum from cleaning up internal catalog tables, as the system must preserve LSN information from being overwritten. If not addressed, this can result in catalog bloat, performance degradation, and an increased risk of wraparound vacuum, potentially causing transaction downtime. **Active but slow logical replication slots** Sometimes removal of dead tuples of catalog is delayed due to the performance degradation of logical replication. This delay in replication slows down updating the `catalog_xmin` and can lead to catalog bloat and wraparound vacuum. The `postgres_get_av_diag()` function displays output similar to the following when it finds a logical replication slot as a blocker. ``` blocker | Logical replication slot database | my_database blocker_identifier | slot1 wait_event | Not applicable autovacuum_lagging_by | 1,940,103,068 suggestion | Ensure replication is active and resolve any lag for the slot if active. If inactive, consider dropping it using the command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. suggested_action | {"SELECT pg_drop_replication_slot('slot1') FROM pg_replication_slots WHERE active = 'f';"} ``` **Suggested action** To resolve this problem, check the replication configuration for issues with the target schema or data that might be terminating the apply process. The most common reasons are the following: + Missing columns + Incompatible data type + Data mismatch + Missing table If the problem is related to infrastructure issues: + Network issues - [How do I resolve issues with an Amazon RDS DB in an incompatible network state?](https://repost.aws/knowledge-center/rds-incompatible-network). + Database or DB instance is not available due to the following reasons: + Replica instance is out of storage - Review [Amazon RDS DB instances run out of storage](https://repost.aws/knowledge-center/rds-out-of-storage) for information about adding storage. + Incompatible-parameters - Review [How can I fix an Amazon RDS DB instance that is stuck in the incompatible-parameters status?](https://repost.aws/knowledge-center/rds-incompatible-parameters) for more information about how you can resolve the issue. If your instance is outside the AWS network or on AWS EC2, consult your administrator on how to resolve the availability or infrastructure-related issues. **Dropping the inactive slot** **Warning** Caution: Before dropping a replication slot, carefully ensure that it has no ongoing replication, is inactive, and is in an unrecoverable state. Dropping a slot prematurely could disrupt replication or cause data loss. After confirming that the replication slot is no longer needed, drop it to allow autovacuum to continue. The condition `active = 'f'` ensures that only an inactive slot is dropped. ``` SELECT pg_drop_replication_slot('slot1') WHERE active ='f' ``` ## Read replicas When the `hot_standby_feedback` setting is enabled for [Amazon RDS read replicas](USER_PostgreSQL.Replication.ReadReplicas.md), it prevents autovacuum on the primary database from removing dead rows that might still be needed by queries running on the read replica. This affects all types of physical read replicas including those managed with or without replication slots. This behavior is necessary because queries running on the standby replica require those rows to remain available on the primary preventing [query conflicts](https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT) and cancellations. **Read replica with physical replication slot** Read replicas with physical replication slots significantly enhance the reliability and stability of replication in RDS for PostgreSQL. These slots ensure the primary database retains essential Write-Ahead Log files until the replica processes them, maintaining data consistency even during network disruptions. Beginning with RDS for PostgreSQL version 14, all replicas utilize replication slots. In earlier versions, only cross-Region replicas used replication slots. The `postgres_get_av_diag()` function displays output similar to the following when it finds a read replica with physical replication slot as the blocker. ``` blocker | Read replica with physical replication slot database | blocker_identifier | rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxxx wait_event | Not applicable autovacuum_lagging_by | 554,080,689 suggestion | Run the following query on the replica "rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxx" to find the long running query: | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377; | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. + | suggested_action | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;"," + | [OR] + | ","Disable hot_standby_feedback"," + | [OR] + | ","Delete the read replica if not needed"} ``` **Read replica with streaming replication** Amazon RDS allows setting up read replicas without a physical replication slot in older versions, up to version 13. This approach reduces overhead by allowing the primary to recycle WAL files more aggressively, which is advantageous in environments with limited disk space and can tolerate occasional ReplicaLag. However, without a slot, the standby must remain in sync to avoid missing WAL files. Amazon RDS uses archived WAL files to help the replica catch up if it falls behind, but this process requires careful monitoring and can be slow. The `postgres_get_av_diag()` function displays output similar to the following when it finds a streaming read replica as the blocker. ``` blocker | Read replica with streaming replication slot database | Not applicable blocker_identifier | xx.x.x.xxx/xx wait_event | Not applicable autovacuum_lagging_by | 610,146,760 suggestion | Run the following query on the replica "xx.x.x.xxx" to find the long running query: + | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343; + | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. + | suggested_action | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;"," + | [OR] + | ","Disable hot_standby_feedback"," + | [OR] + | ","Delete the read replica if not needed"} ``` **Suggested action** As recommended in the `suggested_action` column, carefully review these options to unblock autovacuum. + **Terminate the query** – Following the guidance in the suggestion column, you can connect to the read replica, as specified in the suggested\$1action column, it's advisable to carefully review the option to terminate the session. If termination is deemed safe, you may use the `pg_terminate_backend()` function to terminate the session. This action can be performed by an administrator (such as the RDS master account) or a user with the required pg\$1terminate\$1backend() privilege. You may run the following SQL command on the read replica to terminate the query that is preventing the vacuum on the primary from cleaning up old rows. The value of `backend_xmin` is reported in the function’s output: ``` SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = backend_xmin; ``` + **Disable hot standby feedback** – Consider disabling the `hot_standby_feedback` parameter if it's causing significant vacuum delays. The `hot_standby_feedback` parameter allows a read replica to inform the primary about its query activity, preventing the primary from vacuuming tables or rows that are in use on the standby. While this ensures query stability on the standby, it can significantly delay vacuuming on the primary. Disabling this feature allows the primary to proceed with vacuuming without waiting for the standby to catch up. However, this can lead to query cancellations or failures on the standby if it attempts to access rows that have been vacuumed by the primary. + **Delete the read replica if not needed** – If the read replica is no longer necessary, you can delete it. This will remove the associated replication overhead and allow the primary to recycle transaction logs without being held back by the replica. ## Temporary tables [Temporary tables](https://www.postgresql.org/docs/current/sql-createtable.html), created using the `TEMPORARY` keyword, reside in the temp schema, for example pg\$1temp\$1xxx, and are only accessible to the session that created them. Temporary tables are dropped when the session ends. However, these tables are invisible to PostgreSQL's autovacuum process, and must be manually vacuumed by the session that created them. Trying to vacuum the temp table from another session has no effect. In unusual circumstances, a temporary table exists without an active session owning it. If the owning session ends unexpectedly due to a fatal crash, network issue, or similar event, the temporary table might not be cleaned up, leaving it behind as an "orphaned" table. When the PostgreSQL autovacuum process detects an orphaned temporary table, it logs the following message: ``` LOG: autovacuum: found orphan temp table \"%s\".\"%s\" in database \"%s\" ``` The `postgres_get_av_diag()` function displays output similar to the following when it identifies a temporary table as a blocker. For the function to correctly show the output related to temporary tables, it needs to be executed within the same database where those tables exist. ``` blocker | Temporary table database | my_database blocker_identifier | pg_temp_14.ttemp wait_event | Not applicable autovacuum_lagging_by | 1,805,802,632 suggestion | Connect to database "my_database". Review carefully, you may consider dropping temporary table using command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. suggested_action | {"DROP TABLE ttemp;"} ``` **Suggested action** Follow the instructions provided in the `suggestion` column of the output to identify and remove the temporary table that is preventing autovacuum from running. Use the following command to drop the temporary table reported by `postgres_get_av_diag()`. Replace the table name based on the output provided by the `postgres_get_av_diag()` function. ``` DROP TABLE my_temp_schema.my_temp_table; ``` The following query can be used to identify temporary tables: ``` SELECT oid, relname, relnamespace::regnamespace, age(relfrozenxid) FROM pg_class WHERE relpersistence = 't' ORDER BY age(relfrozenxid) DESC; ``` # Resolving unidentifiable vacuum blockers in RDS for PostgreSQL Resolving unidentifiable vacuum blockers This section explores additional reasons that can prevent vacuuming from making progress. These issues are currently not directly identifiable by the `postgres_get_av_diag()` function. **Topics** + [ ## Invalid pages ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Invalid_pages) + [ ## Index inconsistency ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Index_inconsistency) + [ ## Exceptionally high transaction rate ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.High_transaction_rate) ## Invalid pages An invalid page error occurs when PostgreSQL detects a mismatch in a page’s checksum while accessing that page. The contents are unreadable, preventing autovacuum from freezing tuples. This effectively stops the cleanup process. The following error is written into PostgreSQL’s log: ``` WARNING: page verification failed, calculated checksum YYYYY but expected XXXX ERROR: invalid page in block ZZZZZ of relation base/XXXXX/XXXXX CONTEXT: automatic vacuum of table myschema.mytable ``` **Determine the object type** ``` ERROR: invalid page in block 4305910 of relation base/16403/186752608 WARNING: page verification failed, calculated checksum 50065 but expected 60033 ``` From the error message, the path `base/16403/186752608` provides the following information: + "base" is the directory name under the PostgreSQL data directory. + "16403" is the database OID, which you can look up in the `pg_database` system catalog. + "186752608" is the `relfilenode`, which you can use to look up the schema and object name in the `pg_class` system catalog. By checking the output of the following query in the impacted database, you can determine the object type. The following query retrieves object information for oid: 186752608. Replace the OID with the one relevant to the error you encountered. ``` SELECT relname AS object_name, relkind AS object_type, nspname AS schema_name FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.oid = 186752608; ``` For more information, see the PostgreSQL documentation [https://www.postgresql.org/docs/current/catalog-pg-class.html](https://www.postgresql.org/docs/current/catalog-pg-class.html) for all the supported object types, noted by the `relkind` column in `pg_class`. **Guidance** The most effective solution for this issue depends on the configuration of your specific Amazon RDS instance and the type of data impacted by the inconsistent page. **If the object type is an index:** Rebuilding the index is recommended. + **Using the `CONCURRENTLY` option** – Prior to PostgreSQL version 12, rebuilding an index required an exclusive table lock, restricting access to the table. With PostgreSQL version 12, and later versions, the `CONCURRENTLY` option allows for row-level locking, significantly improving the table's availability. Following is the command: ``` REINDEX INDEX ix_name CONCURRENTLY; ``` While `CONCURRENTLY` is less disruptive, it can be slower on busy tables. Consider building the index during low-traffic periods if possible. For more information, see the PostgreSQL [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) documentation. + **Using the `INDEX_CLEANUP FALSE` option** – If the indexes are large and estimated to require a significant amount of time to finish, you can unblock autovacuum by executing a manual `VACUUM FREEZE` while excluding indexes. This functionality is available in PostgreSQL version 12 and later versions. Bypassing indexes will allow you to skip the vacuum process of the inconsistent index and mitigate the wraparound issue. However, this will not resolve the underlying invalid page problem. To fully address and resolve the invalid page issue, you will still need to rebuild the index. **If the object type is a materialized view:** If an invalid page error occurs on a materialized view, login to the impacted database and refresh it to resolve the invalid page: Refresh the materialized view: ``` REFRESH MATERIALIZED VIEW schema_name.materialized_view_name; ``` If refreshing fails, try recreating: ``` DROP MATERIALIZED VIEW schema_name.materialized_view_name; CREATE MATERIALIZED VIEW schema_name.materialized_view_name AS query; ``` Refreshing or recreating the materialized view restores it without impacting the underlying table data. **For all other object types:** For all other object types, reach out to AWS support. ## Index inconsistency A logically inconsistent index can prevent autovacuum from making progress. The following errors or similar errors are logged during either the vacuum phase of the index or when the index is accessed by SQL statements. ``` ERROR: right sibling's left-link doesn't match:block 5 links to 10 instead of expected 2 in index ix_name ``` ``` ERROR: failed to re-find parent key in index "XXXXXXXXXX" for deletion target page XXX CONTEXT: while vacuuming index index_name of relation schema.table ``` **Guidance** Rebuild the index or skip indexes using `INDEX_CLEANUP` on manual `VACUUM FREEZE`. For information about how to rebuild the index, see [If the object type is an index](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Invalid_pages). + **Using the CONCURRENTLY option** – Prior to PostgreSQL version 12, rebuilding an index required an exclusive table lock, restricting access to the table. With PostgreSQL version 12, and later versions, the CONCURRENTLY option allows for row-level locking, significantly improving the table's availability. Following is the command: ``` REINDEX INDEX ix_name CONCURRENTLY; ``` While CONCURRENTLY is less disruptive, it can be slower on busy tables. Consider building the index during low-traffic periods if possible. For more information, see [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) in *PostgreSQL* documentation. + **Using the INDEX\$1CLEANUP FALSE option** – If the indexes are large and estimated to require a significant amount of time to finish, you can unblock autovacuum by executing a manual VACUUM FREEZE while excluding indexes. This functionality is available in PostgreSQL version 12 and later versions. Bypassing indexes will allow you to skip the vacuum process of the inconsistent index and mitigate the wraparound issue. However, this will not resolve the underlying invalid page problem. To fully address and resolve the invalid page issue, you will still need to rebuild the index. ## Exceptionally high transaction rate In PostgreSQL, high transaction rates can significantly impact autovacuum's performance, leading to slower cleanup of dead tuples and increased risk of transaction ID wraparound. You can monitor the transaction rate by measuring the difference in `max(age(datfrozenxid))` between two time periods, typically per second. Additionally, you can use the following counter metrics from RDS Performance Insights to measure the transaction rate (the sum of xact\$1commit and xact\$1rollback) which is the total number of transactions. | Counter | Type | Unit | Metric | | --- | --- | --- | --- | | xact\$1commit | Transactions | Commits per second | db.Transactions.xact\$1commit | | xact\$1rollback | Transactions | Rollbacks per second | db.Transactions.xact\$1rollback | A rapid increase indicates a high transaction load, which can overwhelm autovacuum, causing bloat, lock contention, and potential performance issues. This can negatively impact the autovacuum process in a couple of ways: + **Table Activity:** The specific table being vacuumed could be experiencing a high volume of transactions, causing delays. + **System Resources** The overall system might be overloaded, making it difficult for autovacuum to access the necessary resources to function efficiently. Consider the following strategies for allowing autovacuum to operate more effectively and keep up with its tasks: 1. Reduce the transaction rate if possible. Consider to batch or group similar transactions where feasible. 1. Target frequently updated tables with manual `VACUUM FREEZE` operation nightly, weekly, or biweekly during off-peak hours. 1. Consider scaling up your instance class to allocate more system resources to handle the high transaction volume and autovacuum. # Resolving vacuum performance issues in RDS for PostgreSQL Resolving vacuum performance issues This section discusses factors that often contribute to slower vacuum performance and how to address these issues. **Topics** + [ ## Vacuum large indexes ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Large_indexes) + [ ## Too many tables or databases to vacuum ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Multiple_tables) + [ ## Aggressive vacuum (to prevent wraparound) is running ](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Aggressive_vacuum) ## Vacuum large indexes VACUUM operates through sequential phases: initialization, heap scanning, index and heap vacuuming, index cleanup, heap truncation, and final cleanup. During the heap scan, the process prunes pages, defragments and freezes them. After completing the heap scan, VACUUM cleans indexes, returns empty pages to the operating system, and performs final cleanup tasks like vacuuming the free space map and updating statistics. Index vacuuming may require multiple passes when `maintenance_work_mem` (or `autovacuum_work_mem`) is insufficient to process the index. In PostgreSQL 16 and earlier, a 1 GB memory limit for storing dead tuple IDs often forced multiple passes on large indexes. PostgreSQL 17 introduces `TidStore`, which dynamically allocates memory instead of using a single-allocation array. This removes the 1 GB constraint, uses memory more efficiently, and reduces the need for multiple index scans per each index. Large indexes may still require multiple passes in PostgreSQL 17 if available memory can't accommodate the entire index processing at once. Typically, larger indexes contain more dead tuples that require multiple passes. **Detecting slow vacuum operations** The `postgres_get_av_diag()` function can detect when vacuum operations are running slowly due to insufficient memory. For more information on this function, see [Installing autovacuum monitoring and diagnostic tools in RDS for PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Installation.md). The `postgres_get_av_diag()` function issues the following notices when the available memory is not enough to complete the index vacuuming in a single pass. **`rds_tools` 1.8** ``` NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow. ``` ``` NOTICE: The current setting of autovacuum_work_mem is "XXX" and might not be sufficient. Consider increasing the setting, and if necessary, scaling up the Amazon RDS instance class for more memory. Additionally, review the possibility of manual vacuum with exclusion of indexes using (VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;). ``` **`rds_tools` 1.9** ``` NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow. ``` ``` NOTICE: The current setting of autovacuum_work_mem is XX might not be sufficient. Consider increasing the setting to XXX, and if necessary, scaling up the RDS instance class for more memory. The suggested value is an estimate based on the current number of dead tuples for the table being vacuumed, which might not fully reflect the latest state. Additionally, review the possibility of manual vacuum with exclusion of indexes using (VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;). For more information, see [Working with PostgreSQL autovacuum in the Amazon Amazon RDS User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html) . ``` **Note** The `postgres_get_av_diag()` function relies on `pg_stat_all_tables.n_dead_tup` for estimating the amount of memory required for index vacuuming. When the `postgres_get_av_diag()` function identifies a slow vacuum operation that requires multiple index scans due to insufficient `autovacuum_work_mem`, it will generate the following message: ``` NOTICE: Your vacuum is performing multiple index scans due to insufficient autovacuum_work_mem:XXX for index vacuuming. For more information, see [Working with PostgreSQL autovacuum in the Amazon Amazon RDS User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html). ``` **Guidance** You can apply the following workarounds using manual `VACUUM FREEZE` to speed up freezing the table. **Increase the memory for vacuuming** As suggested by the `postgres_get_av_diag()` function, it's advisable to increase the `autovacuum_work_mem` parameter to address potential memory constraints at the instance level. While `autovacuum_work_mem` is a dynamic parameter, it's important to note that for the new memory setting to take effect, the autovacuum daemon needs to restart its workers. To accomplish this: 1. Confirm that the new setting is in place. 1. Terminate the processes currently running autovacuum. This approach ensures that the adjusted memory allocation is applied to new autovacuum operations. For more immediate results, consider manually performing a `VACUUM FREEZE` operation with an increased `maintenance_work_mem` setting within your session: ``` SET maintenance_work_mem TO '1GB'; VACUUM FREEZE VERBOSE table_name; ``` If you're using Amazon RDS and find that you need additional memory to support higher values for `maintenance_work_mem` or `autovacuum_work_mem`, consider upgrading to an instance class with more memory. This can provide the necessary resources to enhance both manual and automatic vacuum operations, leading to improved overall vacuum and database performance. **Disable INDEX\$1CLEANUP** Manual `VACUUM` in PostgreSQL version 12 and later allows skipping the index cleanup phase, while emergency autovacuum in PostgreSQL version 14 and later does this automatically based on the [https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE) parameter. **Warning** Skipping index cleanup can lead to index bloat and negatively impact query performance. To mitigate this, consider reindexing or vacuuming affected indexes during a maintenance window. For additional guidance on handling large indexes, refer to the documentation on [Managing autovacuum with large indexes](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.md). **Parallel index vacuuming** Starting with PostgreSQL 13, indexes can be vacuumed and cleaned in parallel by default using manual `VACUUM`, with one vacuum worker process assigned to each index. However, for PostgreSQL to determine if a vacuum operation qualifies for parallel execution, specific criteria must be met: + There must be at least two indexes. + The `max_parallel_maintenance_workers` parameter should be set to at least 2. + The index size must exceed the `min_parallel_index_scan_size` limit, which defaults to 512KB. You can adjust the `max_parallel_maintenance_workers` setting based on the number of vCPUs available on your Amazon RDS instance and the number of indexes on the table to optimize vacuuming turnaround time. For more information, see [Parallel vacuuming in Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL](https://aws.amazon.com/blogs/database/parallel-vacuuming-in-amazon-rds-for-postgresql-and-amazon-aurora-postgresql/). ## Too many tables or databases to vacuum As mentioned in PostgreSQL's [The Autovacuum Daemon](https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM') documentation, the autovacuum daemon operates through multiple processes. This includes a persistent autovacuum launcher responsible for starting autovacuum worker processes for each database within the system. The launcher schedules these workers to initiate approximately every `autovacuum_naptime` seconds per database. With 'N' databases, a new worker begins roughly every [`autovacuum_naptime`/N seconds]. However, the total number of concurrent workers is limited by the `autovacuum_max_workers` setting. If the number of databases or tables requiring vacuuming exceeds this limit, the next database or table will be processed as soon as a worker becomes available. When many large tables or databases require vacuuming concurrently, all available autovacuum workers can become occupied for an extended duration, delaying maintenance on other tables and databases. In environments with high transaction rates, this bottleneck can quickly escalate and potentially lead to wraparound vacuum issues within your Amazon RDS instance. When `postgres_get_av_diag()` detects a high number of tables or databases, it provides the following recommendation: ``` NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow. ``` ``` NOTICE: The current setting of autovacuum_max_workers:3 might not be sufficient. Consider increasing the setting and, if necessary, consider scaling up the Amazon RDS instance class for more workers. ``` **Guidance** **Increase autovacuum\$1max\$1workers** To expedite the vacuuming, we recommend adjusting the `autovacuum_max_workers` parameter to allow more concurrent autovacuum workers. If performance bottlenecks persist, consider scaling up your Amazon RDS instance to a class with more vCPUs, which can further improve the parallel processing capabilities. ## Aggressive vacuum (to prevent wraparound) is running The age of the database (MaximumUsedTransactionIDs) in PostgreSQL only decreases when an aggressive vacuum (to prevent wraparound) is successfully completed. Until this vacuum finishes, the age will continue to increase depending on the transaction rate. The `postgres_get_av_diag()` function generates the following `NOTICE` when it detects an aggressive vacuum. However, it only triggers this output after the vacuum has been active for at least two minutes. ``` NOTICE: Your database is currently running aggressive vacuum to prevent wraparound, monitor autovacuum performance. ``` For more information about aggressive vacuum, see [When an aggressive vacuum is already running](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md). You can verify if an aggressive vacuum is in progress with the following query: ``` SELECT a.xact_start AS start_time, v.datname "database", a.query, a.wait_event, v.pid, v.phase, v.relid::regclass, pg_size_pretty(pg_relation_size(v.relid)) AS heap_size, ( SELECT string_agg(pg_size_pretty(pg_relation_size(i.indexrelid)) || ':' || i.indexrelid::regclass || chr(10), ', ') FROM pg_index i WHERE i.indrelid = v.relid ) AS index_sizes, trunc(v.heap_blks_scanned * 100 / NULLIF(v.heap_blks_total, 0)) AS step1_scan_pct, v.index_vacuum_count || '/' || ( SELECT count(*) FROM pg_index i WHERE i.indrelid = v.relid ) AS step2_vacuum_indexes, trunc(v.heap_blks_vacuumed * 100 / NULLIF(v.heap_blks_total, 0)) AS step3_vacuum_pct, age(CURRENT_TIMESTAMP, a.xact_start) AS total_time_spent_sofar FROM pg_stat_activity a INNER JOIN pg_stat_progress_vacuum v ON v.pid = a.pid; ``` You can determine if it's an aggressive vacuum (to prevent wraparound) by checking the query column in the output. The phrase "to prevent wraparound" indicates that it is an aggressive vacuum. ``` query | autovacuum: VACUUM public.t3 (to prevent wraparound) ``` For example, suppose you have a blocker at transaction age 1 billion and a table requiring an aggressive vacuum to prevent wraparound at the same transaction age. Additionally, there's another blocker at transaction age 750 million. After clearing the blocker at transaction age 1 billion, the transaction age won't immediately drop to 750 million. It will remain high until the table needing the aggressive vacuum or any transaction with an age over 750 million is completed. During this period, the transaction age of your PostgreSQL cluster will continue to rise. Once the vacuum process is completed, the transaction age will drop to 750 million but will start increasing again until further vacuuming is finished. This cycle will continue as long as these conditions persist, until the transaction age eventually drops to the level configured for your Amazon RDS instance, specified by `autovacuum_freeze_max_age`. # Explanation of the NOTICE messages in RDS for PostgreSQL Explanation of the NOTICE messages The `postgres_get_av_diag()` function provides the following NOTICE messages: **When the age has not reached the monitoring threshold yet** The monitoring threshold for `postgres_get_av_diag()` to identify blockers is 500 million transactions by default. If `postgres_get_av_diag()` generates the following NOTICE, it indicates that the transaction age has not yet reached this threshold. ``` NOTICE: postgres_get_av_diag() checks for blockers that prevent aggressive vacuums only, it does so only after exceeding dvb_threshold which is 500,000,000 and age of this PostgreSQL cluster is currently at 2. ``` **Not connected to the database with the age of oldest transaction ID** The `postgres_get_av_diag()` function provides the most accurate output when connected to the database with the oldest transaction ID age. The database with the oldest transaction ID age reported by `postgres_get_av_diag()` will be different than “my\$1database” in your case. If you are not connected to the correct database, the following NOTICE is generated: ``` NOTICE: You are not connected to the database with the age of oldest transaction ID. Connect to my_database database and run postgres_get_av_diag() for accurate reporting. ``` Connecting to the database with the oldest transaction age is important for the following reasons: + **Identifying temporary table blockers:** Because the metadata for temporary tables is specific to each database, they are typically found in the database where they are created. However, if a temporary table happens to be the top blocker and resides in the database with the oldest transaction, this could be misleading. Connecting to the correct database ensures the accurate identification of the temporary table blocker. + **Diagnosing slow vacuums:** The index metadata and table count information are database-specific and necessary for diagnosing slow vacuum issues. **Database with oldest transaction by age is on an rdsadmin or template0 database** In certain cases, the `rdsadmin` or `template0` databases may be identified as the database with the oldest transaction ID age. If this happens, `postgres_get_av_diag()` will issue the following NOTICE: ``` NOTICE: The database with the age of oldest transaction ID is rdsadmin or template0, reach out to support if the reported blocker is in rdsadmin or template0. ``` Verify that the listed blocker is not originating from either of these two databases. If the blocker is reported to be present in either `rdsadmin` or `template0`, contact support as these databases are not user-accessible and require intervention. It is highly unlikely for either the `rdsadmin` or `template0` database to contain a top blocker. **When an aggressive vacuum is already running** The `postgres_get_av_diag()` function is designed to report when an aggressive vacuum process is running, but it only triggers this output after the vacuum has been active for at least 1 minute. This intentional delay helps reduce the chances of false positives. By waiting, the function ensures that only effective, significant vacuums are reported, leading to more accurate and reliable monitoring of vacuum activity. The `postgres_get_av_diag()` function generates the following NOTICE when it detects one or more aggressive vacuums in progress. ``` NOTICE: Your database is currently running aggressive vacuum to prevent wraparound, monitor autovacuum performance. ``` As indicated in the NOTICE, continue to monitor the performance of vacuum. For more information about aggressive vacuum see [Aggressive vacuum (to prevent wraparound) is running](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Performance.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Aggressive_vacuum) **When autovacuum is off** The `postgres_get_av_diag()` function generates the following NOTICE if autovacuum is disabled on your database instance: ``` NOTICE: Autovacuum is OFF, we strongly recommend to enable it, no restart is necessary. ``` Autovacuum is a critical feature of your RDS for PostgreSQL DB instance that ensures smooth database operation. It automatically removes old row versions, reclaims storage space, and prevents table bloat, helping to keep tables and indexes efficient for optimal performance. Additionally, it protects against transaction ID wraparound, which can halt transactions on your Amazon RDS instance. Disabling autovacuum can lead to long-term declines in database performance and stability. We suggest you to keep it on all the times. For more information, see [Understanding autovacuum in RDS for PostgreSQL environments](https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/). Turning off autovacuum doesn't stop aggressive vacuums. These will still occur once your tables hit the `autovacuum_freeze_max_age` threshold. **The number of transactions remaining is critically low** The `postgres_get_av_diag()` function generates the following NOTICE when a wraparound vacuum is imminent. This NOTICE is issued when your Amazon RDS instance is 100 million transactions away from potentially rejecting new transactions. ``` WARNING: Number of transactions remaining is critically low, resolve issues with autovacuum or perform manual VACUUM FREEZE before your instance stops accepting transactions. ``` Your immediate action is required to avoid database downtime. You should closely monitor your vacuuming operations and consider manually initiating a `VACUUM FREEZE` on the affected database to prevent transaction failures. # Managing high object counts in Amazon RDS for PostgreSQL While PostgreSQL limitations are theoretical, having extremely high object counts in a database will cause noticeable performance impact to various operations. This documentation covers several common object types that, when having a high total count can lead to several possible impacts. The following table provides a summary of object types and their potential impacts: **Object types and potential impacts** | Type of Object | Autovacuum | Logical Replication | Major Version Upgrade | pg\$1dump / pg\$1restore | General Performance | Instance Restart | | --- | --- | --- | --- | --- | --- | --- | | [Relations](#PostgreSQL.HighObjectCount.Relations) | x | | x | x | x | | | [Temporary tables](#PostgreSQL.HighObjectCount.TempTables) | x | | | | x | | | [Unlogged tables](#PostgreSQL.HighObjectCount.UnloggedTables) | | x | | | | x | | [Partitions](#PostgreSQL.HighObjectCount.Partitions) | | | | | x | | | [Temporary files](#PostgreSQL.HighObjectCount.TempFiles) | | | | | x | | | [Sequences](#PostgreSQL.HighObjectCount.Sequences) | | x | | | | | | [Large objects](#PostgreSQL.HighObjectCount.LargeObjects) | | x | x | | | | ## Relations There is not a specific hard limit regarding the number of tables in a PostgreSQL database. The theoretical limit is extremely high, but there are other practical limits that need to be kept in mind during database design. **Impact: Autovacuum falling behind** Autovacuum can struggle to keep up with transaction ID growth or table bloat due to lack of workers compared to amount of work. **Recommended action:** There are several factors for tuning autovacuum to keep up properly with a given number of tables and given workload. See [Best practices for working with PostgreSQL autovacuum](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html) for suggestions on how to determine appropriate autovacuum settings. Use the [postgres\$1get\$1av\$1diag utility](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Functions.html) to monitor problems with transaction ID growth. **Impact: Major version upgrade / pg\$1dump and restore** Amazon RDS uses the "--link" option during pg\$1upgrade execution to avoid having to make copies of datafiles, the schema metadata is still required to be restored into the new version of the database. Even with parallel pg\$1restore, if there are a significant number of relations this will increase the amount of downtime. **Impact: General performance degradation** General performance degradation due to catalog size. Each table and its associated columns will add to `pg_attribute`, `pg_class` and `pg_depend` tables which are frequently used in normal database operations. There won't be a specific wait event visible, but shared buffer efficiency will be impacted. **Recommended action:** Regularly check table bloat for these specific tables and occasionally perform a `VACUUM FULL` on these specific tables. Be aware that `VACUUM FULL` on catalog tables requires an `ACCESS EXCLUSIVE` lock which means no other queries will be able to access them until the operation completes. **Impact: File descriptor exhaustion** Error: "out of file descriptors: Too many open files in system; release and retry". The PostgreSQL parameter `max_files_per_process` determines how many files each process can open. If there are a high number of connections joining a high number of tables, it is possible to hit this limit. **Recommended action:** + Lowering the value of the parameter `max_files_per_process` may help alleviate this error. Each process and subprocess (for example, parallel query) can open this number of files, and if the queries are joining several tables, this limit can be exhausted. + Reduce the overall number of connections and use a connection pooler such as [Amazon RDS Proxy](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html) or other solutions such as PgBouncer. To learn more, see the [PgBouncer website](https://www.pgbouncer.org/). **Impact: Inode exhaustion** Error: "No space left on device". If this is observed when there is plenty of storage free space, this is caused by running out of inodes. [Amazon RDS Enhanced Monitoring](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html) provides visibility for inodes in use and the maximum number available for your host. **Approximate threshold:** [Millions](#PostgreSQL.HighObjectCount.Note) ## Temporary tables Using temporary tables is useful for test data or intermediate results and is a common pattern seen in many database engines. The implications of heavy use in PostgreSQL must be understood to avoid some of the pitfalls. Each temporary table create and drop will add rows to system catalog tables, which when they become bloated, will cause general performance issues. **Impact: Autovacuum falling behind** Temporary tables aren't vacuumed by autovacuum but will hold on to transaction IDs during their existence and can lead to wraparound if not removed. **Recommended action:** Temporary tables will live for the duration of the session that created them or can be dropped manually. A best practice of avoiding long-running transactions with temporary tables will prevent these tables from contributing to maximum used transaction ID growth. **Impact: General performance degradation** General performance degradation due to catalog size. When sessions continuously create and drop temporary tables, it will add to `pg_attribute`, `pg_class` and `pg_depend` tables which are frequently used in normal database operations. There won't be a specific wait event visible, but shared buffer efficiency will be impacted. **Recommended action:** + Regularly check table bloat for these specific tables and occasionally perform a `VACUUM FULL` on these specific tables. Be aware that `VACUUM FULL` on catalog tables requires an `ACCESS EXCLUSIVE` lock which means no other queries will be able to access them until the operation completes. + If temporary tables are heavily used, prior to a major version upgrade, a `VACUUM FULL` of these specific catalog tables is highly recommended to reduce downtime. **General best practices:** + Reduce the use of temporary tables by using common table expressions to produce intermediate results. These can sometimes complicate the queries needed, but will eliminate the impacts listed above. + Reuse temporary tables by using the `TRUNCATE` command to clear the contents instead of doing drop/create steps. This will also eliminate the problem of transaction ID growth caused by temporary tables. **Approximate threshold:** [Tens of thousands](#PostgreSQL.HighObjectCount.Note) ## Unlogged tables Unlogged tables can offer performance gains as they won't generate any WAL information. They must be used carefully as they offer no durability during database crash recovery as they will be truncated. This is an expensive operation in PostgreSQL as each unlogged table is truncated serially. While this operation is fast for a low number of unlogged tables, when they number in the thousands it can start to add notable delay during startup. **Impact: Logical replication** Unlogged tables are generally not included in logical replication, including [Blue/Green Deployments](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html), because logical replication relies on the WAL to capture and transfer changes. **Impact: Extended downtime during recovery** During any database state that involves database crash recovery such as Multi-AZ reboot with failover, Amazon RDS point-in-time recovery, and Amazon RDS major version upgrade, the serialized operation of truncating the unlogged tables will occur. This can lead to a much higher downtime experience than expected. **Recommended action:** + Minimize the use of unlogged tables only to data which is acceptable to lose during database crash recovery operations. + Minimize the use of unlogged tables as the current behavior of serial truncation can cause startup of a database to take a significant amount of time. **General best practices:** + Unlogged tables are not crash safe. Initiating a point-in-time recovery, which involves crash recovery, takes a significant time in PostgreSQL because this is a serial process that truncates each table. **Approximate threshold:** [Thousands](#PostgreSQL.HighObjectCount.Note) ## Partitions Partitioning can increase query performance and provide a logical organization of data. In ideal scenarios, partitioning is organized so that partition pruning can be used during query planning and execution. Using too many partitions can have negative impacts on query performance and database maintenance. The choice of how to partition a table should be made carefully, as the performance of query planning and execution can be negatively affected by poor design. See [PostgreSQL documentation](https://www.postgresql.org/docs/current/ddl-partitioning.html) for details about partitioning. **Impact: General performance degradation** Sometimes planning time overhead will increase and explain plans for your queries will become more complicated, making it difficult to identify tuning opportunities. For PostgreSQL versions earlier than 18, many partitions with high workload can lead to `LWLock:LockManager` waits. **Recommended action:** Determine a minimum number of partitions that will allow you to complete both the organization of your data while at the same time providing performant query execution. **Impact: Maintenance complexity** Very high number of partitions will introduce maintenance difficulties like pre-creation and removal. Autovacuum will treat partitions as normal relations and have to perform regular cleanup, therefore requiring enough workers to complete the task. **Recommended action:** + Ensure you precreate partitions so that workload isn't blocked when a new partition is needed (for example, monthly based partitions) and old partitions are rolled off. + Ensure you have enough autovacuum workers to perform normal cleanup maintenance of all partitions. **Approximate threshold:** [Hundreds](#PostgreSQL.HighObjectCount.Note) ## Temporary files Different than temporary tables mentioned above, temporary files are created by PostgreSQL when a complex query might perform several sort or hash operations at the same time, with each operation using instance memory to store results up to the value specified in the `work_mem` parameter. When the instance memory is not sufficient, temporary files are created to store the results. See [Managing temporary files](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.ManagingTempFiles.html) for more details on temporary files. If your workload generates high numbers of these files, there can be several impacts. **Impact: File descriptor exhaustion** Error: "out of file descriptors: Too many open files in system; release and retry". The PostgreSQL parameter `max_files_per_process` determines how many files each process can open. If there are a high number of connections joining a high number of tables, it is possible to hit this limit. **Recommended action:** + Lowering the value of the parameter `max_files_per_process` may help alleviate this error. Each process and subprocess (for example, parallel query) can open this number of files, and if the queries are joining several tables, this limit can be exhausted. + Reduce the overall number of connections and use a connection pooler such as [Amazon RDS Proxy](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html) or other solutions such as PgBouncer. To learn more, see the [PgBouncer website](https://www.pgbouncer.org/). **Impact: Inode exhaustion** Error: "No space left on device". If this is observed when there is plenty of storage free space, this is caused by running out of inodes. [Amazon RDS Enhanced Monitoring](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html) provides visibility for inodes in use and the maximum number available for your host. **General best practices:** + Monitor your temp file usage with [Performance Insights](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html). + Tune queries that are generating significant temporary files to see if it's possible to reduce the total number of temp files. **Approximate threshold:** [Thousands](#PostgreSQL.HighObjectCount.Note) ## Sequences Sequences are the underlying object used for auto-incrementing columns in PostgreSQL and they provide uniqueness and a key for the data. These can be used on individual tables with no consequence during normal operations with one exception of logical replication. In PostgreSQL, logical replication does not currently replicate a sequence's current value to any subscriber. To learn more, see the [Restrictions page in PostgreSQL documentation](https://www.postgresql.org/docs/current/logical-replication-restrictions.html). **Impact: Extended switchover time** If you plan to use [Amazon RDS Blue/Green Deployments](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html) for any type of configuration change or upgrade, it is important to understand the impact of a high number of sequences on switchover. One of the last phases of a switchover will synchronize the current value of sequences, and if there are several thousand, this will increase the overall switchover time. **Recommended action:** If your database workload would allow for the use of a shared UUID instead of a sequence-per-table approach, this would cut down on the synchronization step during a switchover. **Approximate threshold:** [Thousands](#PostgreSQL.HighObjectCount.Note) ## Large objects Large objects are stored in a single system table named pg\$1largeobject. Each large object also has an entry in the system table pg\$1largeobject\$1metadata. These objects are created, modified and cleaned up much differently than standard relations. Large objects are not handled by autovacuum and must be periodically cleaned up via a separate process called vacuumlo. See managing large objects with the lo module for examples on managing large objects. **Impact: Logical replication** Large objects are not currently replicated in PostgreSQL during logical replication. To learn more, see the [Restrictions page in PostgreSQL documentation](https://www.postgresql.org/docs/current/logical-replication-restrictions.html). In a [Blue/Green](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html) configuration, this means large objects in the blue environment aren't replicated to the green environment. **Impact: Major version upgrade** An upgrade can run out of memory and fail if there are millions of large objects and the instance cannot handle them during an upgrade. The PostgreSQL major version upgrade process comprises of two broad phases: dumping the schema via pg\$1dump and restoring it through pg\$1restore. If your database has millions of large objects you need to ensure your instance has sufficient memory to handle the pg\$1dump and pg\$1restore during an upgrade and scale it to a larger instance type. **General best practices:** + Regularly use the vacuumlo utility to remove any orphaned large objects you may have. + Consider using the BYTEA datatype for storing your large objects in the database. **Approximate threshold:** [Millions](#PostgreSQL.HighObjectCount.Note) ## Approximate thresholds The approximate thresholds mentioned in this topic are only used to provide an estimate of how far a particular resource can scale. They represent the general range where the described impacts become more likely, but actual behavior depends on your specific workload, instance size, and configuration. While it may be possible to exceed these estimates, care and maintenance must be adhered to so as to avoid the impacts listed. # Managing TOAST OID contention in Amazon RDS for PostgreSQL Managing TOAST OID contention TOAST (The Oversized-Attribute Storage Technique) is a PostgreSQL feature designed to handle large data values that exceed the typical 8KB database block size. PostgreSQL doesn't allow physical rows to span multiple blocks. The block size acts as an upper limit on row size. TOAST overcomes this restriction by splitting large field values into smaller chunks. It stores them separately in a dedicated TOAST table linked to the main table. For more information, see the [PostgreSQL TOAST storage mechanism and implementation documentation](https://www.postgresql.org/docs/current/storage-toast.html). **Topics** + [ ## Understanding TOAST operations ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.HowWorks) + [ ## Identifying performance challenges ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.PerformanceChallenges) + [ ## Recommendations ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Recommendations) + [ ## Monitoring ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Monitoring) ## Understanding TOAST operations TOAST performs compression and stores large field values out of line. TOAST assigns a unique OID (Object Identifier) to each chunk of oversized data stored in the TOAST table. The main table stores the TOAST value ID and relation ID on the page to reference the corresponding row in the TOAST table. This allows PostgreSQL to efficiently locate and manage these TOAST chunks. However, as the TOAST table grows, the system risks exhausting available OIDs, leading to both performance degradation and potential downtime due to OID depletion. ### Object identifiers in TOAST An Object Identifier (OID) is a system-wide unique identifier used by PostgreSQL to reference database objects like tables, indexes, and functions. These identifiers play a vital role in PostgreSQL's internal operations, allowing the database to efficiently locate and manage objects. For tables with eligible data sets for toasting, PostgreSQL assigns OIDs to uniquely identify each chunk of oversized data stored in the associated TOAST table. The system associates each chunk with a `chunk_id`, which helps PostgreSQL organize and locate these chunks efficiently within the TOAST table. ## Identifying performance challenges PostgreSQL's OID management relies on a global 32-bit counter so that it wraps around after generating 4 billion unique values. While the database cluster shares this counter, OID allocation involves two steps during TOAST operations: + **Global counter for allocation** – The global counter assigns a new OID across the cluster. + **Local search for conflicts** – The TOAST table ensures the new OID does not conflict with existing OIDs already used in that specific table. Performance degradation can occur when: + The TOAST table has high fragmentation or dense OID usage, leading to delays in assigning the OID. + The system frequently allocates and reuses OIDs in environments with high data churn or wide tables that use TOAST extensively. For more information, see the [PostgreSQL TOAST table size limits and OID allocation documentation](https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit): A global counter generates the OIDs and wraps around every 4 billion values, so that from time to time, the system generates an already-used value again. PostgreSQL detects that and tries again with the next OID. A slow INSERT could occur if there is a very long run of used OID values with no gaps in the TOAST table. These challenges become more pronounced as the OID space fills, leading to slower inserts and updates. ### Identifying the problem + Simple `INSERT` statements take significantly longer than usual in an inconsistent and random manner. + Delays occur only for `INSERT` and `UPDATE` statements involving TOAST operations. + The following log entries appear in PostgreSQL logs when the system struggles to find available OIDs in TOAST tables: ``` LOG: still searching for an unused OID in relation "pg_toast_20815" DETAIL: OID candidates have been checked 1000000 times, but no unused OID has been found yet. ``` + Performance Insights indicates a high number of average active sessions (AAS) associated with `LWLock:buffer_io` and `LWLock:OidGenLock` wait events. You can run the following SQL query to identify long-running INSERT transactions with wait events: ``` SELECT datname AS database_name, usename AS database_user, pid, now() - pg_stat_activity.xact_start AS transaction_duration, concat(wait_event_type, ':', wait_event) AS wait_event, substr(query, 1, 30) AS TRANSACTION, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.xact_start) > INTERVAL '60 seconds' AND state IN ('active', 'idle in transaction', 'idle in transaction (aborted)', 'fastpath function call', 'disabled') AND pid <> pg_backend_pid() AND lower(query) LIKE '%insert%' ORDER BY transaction_duration DESC; ``` Example query results displaying INSERT operations with extended wait times: ``` database_name | database_user | pid | transaction_duration | wait_event | transaction | state ---------------+-----------------+-------+----------------------+---------------------+--------------------------------+-------- postgres | db_admin_user| 70965 | 00:10:19.484061 | LWLock:buffer_io | INSERT INTO "products" (......... | active postgres | db_admin_user| 69878 | 00:06:14.976037 | LWLock:buffer_io | INSERT INTO "products" (......... | active postgres | db_admin_user| 68937 | 00:05:13.942847 | : | INSERT INTO "products" (......... | active ``` ### Isolating the problem + **Test small insert** – Insert a record smaller than the `toast_tuple_target` threshold. Remember that compression is applied before TOAST storage. If this operates without performance issues, the problem is related to TOAST operations. + **Test new table** – Create a new table with the same structure and insert a record larger than `toast_tuple_target`. If this works without issues, the problem is localized to the original table's OID allocation. ## Recommendations The following approaches can help resolve TOAST OID contention issues. + **Data cleanup and archive** – Review and delete any obsolete or unnecessary data to free up OIDs for future use, or archive the data. Consider the following limitations: + Limited scalability, as future cleanup might not always be possible. + Possible long-running VACUUM operation to remove the resulting dead tuples. + **Write to a new table** – Create a new table for future inserts and use a `UNION ALL` view to combine old and new data for queries. This view presents the combined data from both old and new tables, allowing queries to access them as a single table. Consider the following limitations: + Updates on the old table might still cause OID exhaustion. + **Partition or Shard** – Partition the table or shard data for better scalability and performance. Consider the following limitations: + Increased complexity in query logic and maintenance, potential need for application changes to handle partitioned data correctly. ## Monitoring ### Using system tables You can use PostgreSQL's system tables to monitor growth of OID usage. **Warning** Depending on the number of OIDs in the TOAST table, it may take time to complete. We recommend that you schedule monitoring during off-business hours to minimize impact. The following anonymous block counts the number of distinct OIDs used in each TOAST table and displays the parent table information: ``` DO $$ DECLARE r record; o bigint; parent_table text; parent_schema text; BEGIN SET LOCAL client_min_messages TO notice; FOR r IN SELECT c.oid, c.oid::regclass AS toast_table FROM pg_class c WHERE c.relkind = 't' AND c.relowner != 10 LOOP -- Fetch the number of distinct used OIDs (chunk IDs) from the TOAST table EXECUTE 'SELECT COUNT(DISTINCT chunk_id) FROM ' || r.toast_table INTO o; -- If there are used OIDs, find the associated parent table and its schema IF o <> 0 THEN SELECT n.nspname, c.relname INTO parent_schema, parent_table FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.reltoastrelid = r.oid; -- Raise a concise NOTICE message RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Number of used OIDs: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999'); END IF; END LOOP; END $$; ``` Example output displaying OID usage statistics by TOAST table: ``` NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Number of used OIDs: 45,623,317 NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Number of used OIDs: 10,000 NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Number of used OIDs: 1,000,000 DO ``` The following anonymous block retrieves the maximum assigned OID for each non-empty TOAST table: ``` DO $$ DECLARE r record; o bigint; parent_table text; parent_schema text; BEGIN SET LOCAL client_min_messages TO notice; FOR r IN SELECT c.oid, c.oid::regclass AS toast_table FROM pg_class c WHERE c.relkind = 't' AND c.relowner != 10 LOOP -- Fetch the max(chunk_id) from the TOAST table EXECUTE 'SELECT max(chunk_id) FROM ' || r.toast_table INTO o; -- If there's at least one TOASTed chunk, find the associated parent table and its schema IF o IS NOT NULL THEN SELECT n.nspname, c.relname INTO parent_schema, parent_table FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.reltoastrelid = r.oid; -- Raise a concise NOTICE message RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Max chunk_id: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999'); END IF; END LOOP; END $$; ``` Example output displaying maximum chunk IDs for TOAST tables: ``` NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Max chunk_id: 45,639,907 NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Max chunk_id: 45,649,929 NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Max chunk_id: 46,649,935 DO ``` ### Using Performance Insights The wait events `LWLock:buffer_io` and `LWLock:OidGenLock` appear in Performance Insights during operations that require assigning new Object Identifiers (OIDs). High Average Active Sessions (AAS) for these events typically point to contention during OID assignment and resource management. This is particularly common in environments with high data churn, extensive large data usage, or frequent object creation. #### LWLock:buffer\$1io `LWLock:buffer_io` is a wait event that occurs when a PostgreSQL session is waiting for I/O operations on a shared buffer to complete. This typically happens when the database reads data from disk into memory or writes modified pages from memory to disk. The `BufferIO` wait event ensures consistency by preventing multiple processes from accessing or modifying the same buffer while I/O operations are in progress. High occurrences of this wait event may indicate disk bottlenecks or excessive I/O activity in the database workload. During TOAST operations: + PostgreSQL allocates OIDs for large objects and ensures their uniqueness by scanning the TOAST table's index. + Large TOAST indexes may require accessing multiple pages to verify OID uniqueness. This results in increased disk I/O, especially when the buffer pool cannot cache all required pages. The size of the index directly affects the number of buffer pages that need to be accessed during these operations. Even if the index is not bloated, its sheer size can increase buffer I/O, particularly in high-concurrency or high-churn environments. For more information, see [LWLock:BufferIO wait event troubleshooting guide](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.lwlockbufferio.html). #### LWLock:OidGenLock `OidGenLock` is a wait event that occurs when a PostgreSQL session is waiting to allocate a new object identifier (OID). This lock ensures that OIDs are generated sequentially and safely, allowing only one process to generate OIDs at a time. During TOAST operations: + **OID allocation for chunks in TOAST table** – PostgreSQL assigns OIDs to chunks in TOAST tables when managing large data records. Each OID must be unique to prevent conflicts in the system catalog. + **High concurrency** – Since access to OID generator is sequential, when multiple sessions are concurrently creating objects that require OIDs, contention for `OidGenLock` can occur. This increases the likelihood of sessions waiting for OID allocation to complete. + **Dependency on system catalog access** – Allocating OIDs requires updates to shared system catalog tables like `pg_class` and `pg_type`. If these tables experience heavy activity (due to frequent DDL operations), it can increase lock contention for `OidGenLock`. + **High OID allocation demand** – TOAST heavy workloads with large data records require constant OID allocation, increasing contention. Additional factors that increase OID contention: + **Frequent object creation** – Workloads that frequently create and drop objects, such as temporary tables, amplify contention on the global OID counter. + **Global counter locking** – The global OID counter is accessed serially to ensure uniqueness, creating a single point of contention in high-concurrency environments. ## Working with logging mechanisms supported by RDS for PostgreSQL Logging mechanisms There are several parameters, extensions, and other configurable items that you can set to log activities that occur on your PostgreSQL DB instance. These include the following: + The `log_statement` parameter can be used to log user activity in your PostgreSQL database. To learn more about RDS for PostgreSQL logging and how to monitor the logs, see [ RDS for PostgreSQL database log files](USER_LogAccess.Concepts.PostgreSQL.md). + The `rds.force_admin_logging_level` parameter logs actions by the Amazon RDS internal user (rdsadmin) in the databases on the DB instance. It writes the output to the PostgreSQL error log. Allowed values are `disabled`, `debug5`, `debug4`, `debug3`, `debug2`, `debug1`, `info`, `notice`, `warning`, `error`, log, `fatal`, and `panic`. The default value is `disabled`. + The `rds.force_autovacuum_logging_level` parameter can be set to capture various autovacuum operations in the PostgreSQL error log. For more information, see [Logging autovacuum and vacuum activities](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Logging.md). + The PostgreSQL Audit (pgAudit) extension can be installed and configured to capture activities at the session level or at the object level. For more information, see [Using pgAudit to log database activity](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md). + The `log_fdw` extension makes it possible for you to access the database engine log using SQL. For more information, see [Using the log\$1fdw extension to access the DB log using SQL](CHAP_PostgreSQL.Extensions.log_fdw.md). + The `pg_stat_statements` library is specified as the default for the `shared_preload_libraries` parameter in RDS for PostgreSQL version 10 and higher. It's this library that you can use to analyze running queries. Be sure that `pg_stat_statements` is set in your DB parameter group. For more information about monitoring your RDS for PostgreSQL DB instance using the information that this library provides, see [SQL statistics for RDS PostgreSQL](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.PostgreSQL.md). + The `log_hostname` parameter captures to the log the hostname of each client connection. For RDS for PostgreSQL version 12 and higher versions, this parameter is set to `off` by default. If you turn it on, be sure to monitor session connection times. When turned on, the service uses the domain name system (DNS) reverse lookup request to get the hostname of the client that's making the connection and add it to the PostgreSQL log. This has a noticeable impact during session connection. We recommend that you turn on this parameter for troubleshooting purposes only. In general terms, the point of logging is so that the DBA can monitor, tune performance, and troubleshoot. Many of the logs are uploaded automatically to Amazon CloudWatch or Performance Insights. Here, they're sorted and grouped to provide complete metrics for your DB instance. To learn more about Amazon RDS monitoring and metrics, see [Monitoring metrics in an Amazon RDS instance](CHAP_Monitoring.md). # Managing temporary files with PostgreSQL Managing temporary files with PostgreSQL In PostgreSQL, a complex query might perform several sort or hash operations at the same time, with each operation using instance memory to store results up to the value specified in the [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM) parameter. When the instance memory is not sufficient, temporary files are created to store the results. These are written to disk to complete the query execution. Later, these files are automatically removed after the query completes. In RDS for PostgreSQL, these files are stored in Amazon EBS on the data volume. For more information, see [Amazon RDS DB instance storage](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html). You can monitor the `FreeStorageSpace` metric published in CloudWatch to make sure that your DB instance has enough free storage space. For more information, see [https://repost.aws/knowledge-center/storage-full-rds-cloudwatch-alarm](https://repost.aws/knowledge-center/storage-full-rds-cloudwatch-alarm). We recommend using Amazon RDS Optimized Read instances for workloads involving multiple concurrent queries that increase the usage of temporary files. These instances use local Non-Volatile Memory Express (NVMe) based solid state drive (SSD) block-level storage to place the temporary files. For more information, see [Improving query performance for RDS for PostgreSQL with Amazon RDS Optimized Reads](USER_PostgreSQL.optimizedreads.md). You can use the following parameters and functions to manage the temporary files in your instance. + **[https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK](https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK)** – This parameter cancels any query exceeding the size of temp\$1files in KB. This limit prevents any query from running endlessly and consuming disk space with temporary files. You can estimate the value using the results from the `log_temp_files` parameter. As a best practice, examine the workload behavior and set the limit according to the estimation. The following example shows how a query is canceled when it exceeds the limit. ``` postgres=>select * from pgbench_accounts, pg_class, big_table; ``` ``` ERROR: temporary file size exceeds temp_file_limit (64kB) ``` + **[https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TEMP-FILES](https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TEMP-FILES)** – This parameter sends messages to the postgresql.log when the temporary files of a session are removed. This parameter produces logs after a query successfully completes. Therefore, it might not help in troubleshooting active, long-running queries. The following example shows that when the query successfully completes, the entries are logged in the postgresql.log file while the temporary files are cleaned up. ``` 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.5", size 140353536 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10; 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.4", size 180428800 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10; ``` + **[https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE)** – This function that is available from RDS for PostgreSQL 13 and above provides visibility into the current temporary file usage. The completed query doesn't appear in the results of the function. In the following example, you can view the results of this function. ``` postgres=>select * from pg_ls_tmpdir(); ``` ``` name | size | modification -----------------+------------+------------------------ pgsql_tmp8355.1 | 1072250880 | 2023-02-06 22:54:56+00 pgsql_tmp8351.0 | 1072250880 | 2023-02-06 22:54:43+00 pgsql_tmp8327.0 | 1072250880 | 2023-02-06 22:54:56+00 pgsql_tmp8351.1 | 703168512 | 2023-02-06 22:54:56+00 pgsql_tmp8355.0 | 1072250880 | 2023-02-06 22:54:00+00 pgsql_tmp8328.1 | 835031040 | 2023-02-06 22:54:56+00 pgsql_tmp8328.0 | 1072250880 | 2023-02-06 22:54:40+00 (7 rows) ``` ``` postgres=>select query from pg_stat_activity where pid = 8355; query ---------------------------------------------------------------------------------------- select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid (1 row) ``` The file name includes the processing ID (PID) of the session that generated the temporary file. A more advanced query, such as in the following example, performs a sum of the temporary files for each PID. ``` postgres=>select replace(left(name, strpos(name, '.')-1),'pgsql_tmp','') as pid, count(*), sum(size) from pg_ls_tmpdir() group by pid; ``` ``` pid | count | sum ------+------------------- 8355 | 2 | 2144501760 8351 | 2 | 2090770432 8327 | 1 | 1072250880 8328 | 2 | 2144501760 (4 rows) ``` + **`[ pg\$1stat\$1statements](https://www.postgresql.org/docs/current/pgstatstatements.html)`** – If you activate the pg\$1stat\$1statements parameter, then you can view the average temporary file usage per call. You can identify the query\$1id of the query and use it to examine the temporary file usage as shown in the following example. ``` postgres=>select queryid from pg_stat_statements where query like 'select a.aid from pgbench%'; ``` ``` queryid ---------------------- -7170349228837045701 (1 row) ``` ``` postgres=>select queryid, substr(query,1,25), calls, temp_blks_read/calls temp_blks_read_per_call, temp_blks_written/calls temp_blks_written_per_call from pg_stat_statements where queryid = -7170349228837045701; ``` ``` queryid | substr | calls | temp_blks_read_per_call | temp_blks_written_per_call ----------------------+---------------------------+-------+-------------------------+---------------------------- -7170349228837045701 | select a.aid from pgbench | 50 | 239226 | 388678 (1 row) ``` + **`[Performance Insights](https://aws.amazon.com/rds/performance-insights/)`** – In the Performance Insights dashboard, you can view temporary file usage by turning on the metrics **temp\$1bytes** and **temp\$1files**. Then, you can see the average of both of these metrics and see how they correspond to the query workload. The view within Performance Insights doesn't show specifically the queries that are generating the temporary files. However, when you combine Performance Insights with the query shown for `pg_ls_tmpdir`, you can troubleshoot, analyze, and determine the changes in your query workload. For more information about how to analyze metrics and queries with Performance Insights, see [Analyzing metrics with the Performance Insights dashboard](USER_PerfInsights.UsingDashboard.md). For an example of viewing temporary file usage with Performance Insights, see [Viewing temporary file usage with Performance Insights](PostgreSQL.ManagingTempFiles.Example.md) # Viewing temporary file usage with Performance Insights You can use Performance Insights to view temporary file usage by turning on the metrics **temp\$1bytes** and **temp\$1files**. The view in Performance Insights doesn't show the specific queries that generate temporary files, however, when you combine Performance Insights with the query shown for `pg_ls_tmpdir`, you can troubleshoot, analyze, and determine the changes in your query workload. 1. In the Performance Insights dashboard, choose **Manage Metrics**. 1. Choose **Database metrics**, and select the **temp\$1bytes** and **temp\$1files** metrics as shown in the following image. ![\[Metrics displayed in the graph.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/rpg_mantempfiles_metrics.png) 1. In the **Top SQL** tab, choose the **Preferences** icon. 1. In the **Preferences** window, turn on the following statistics to appear in the **Top SQL**tab and choose **Continue**. + Temp writes/sec + Temp reads/sec + Tmp blk write/call + Tmp blk read/call 1. The temporary file is broken out when combined with the query shown for `pg_ls_tmpdir`, as shown in the following example. ![\[Query that displays the temporary file usage.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/rpg_mantempfiles_query.png) The `IO:BufFileRead` and `IO:BufFileWrite` events occur when the top queries in your workload often create temporary files. You can use Performance Insights to identify top queries waiting on `IO:BufFileRead` and `IO:BufFileWrite` by reviewing Average Active Session (AAS) in Database Load and Top SQL sections. ![\[IO:BufFileRead and IO:BufFileWrite in the graph.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perfinsights_IOBufFile.png) For more information on how to analyze top queries and load by wait event with Performance Insights, see [Overview of the Top SQL tab](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.md#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL). You should identify and tune the queries that cause increase in temporary file usage and related wait events. For more information on these wait events and remediation, see [ IO:BufFileRead and IO:BufFileWrite](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.iobuffile.html). **Note** The [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM) parameter controls when the sort operation runs out of memory and results are written into temporary files. We recommend that you don't change the setting of this parameter higher than the default value because it would permit every database session to consume more memory. Also, a single session that performs complex joins and sorts can perform parallel operations in which each operation consumes memory. As a best practice, when you have a large report with multiple joins and sorts, set this parameter at the session level by using the `SET work_mem` command. Then the change is only applied to the current session and doesn't change the value globally. ## Using pgBadger for log analysis with PostgreSQL Using pgBadger for log analysis with PostgreSQL You can use a log analyzer such as [pgBadger](http://dalibo.github.io/pgbadger/) to analyze PostgreSQL logs. The pgBadger documentation states that the %l pattern (the log line for the session or process) should be a part of the prefix. However, if you provide the current RDS `log_line_prefix` as a parameter to pgBadger it should still produce a report. For example, the following command correctly formats an Amazon RDS for PostgreSQL log file dated 2014-02-04 using pgBadger. ``` ./pgbadger -f stderr -p '%t:%r:%u@%d:[%p]:' postgresql.log.2014-02-04-00 ``` ## Using PGSnapper for monitoring PostgreSQL Using PGSnapper for monitoring PostgreSQL You can use PGSnapper to assist with periodic collection of Amazon RDS for PostgreSQL performance-related statistics and metrics. For more information, see[ Monitor Amazon RDS for PostgreSQL performance using PGSnapper](https://aws.amazon.com/blogs/database/monitor-amazon-rds-for-postgresql-and-amazon-aurora-postgresql-performance-using-pgsnapper/). # Managing custom casts in RDS for PostgreSQL Managing custom casts **Type casting** in PostgreSQL is the process of converting a value from one data type to another. PostgreSQL provides built-in casts for many common conversions, but you can also create custom casts to define how specific type conversions should behave. A cast specifies how to perform a conversion from one data type to another. For example, converting text `'123'` to integer `123`, or numeric `45.67` to text `'45.67'`. For comprehensive information about PostgreSQL casting concepts and syntax, refer to the [PostgreSQL CREATE CAST Documentation](https://www.postgresql.org/docs/current/sql-createcast.html). Starting with RDS for PostgreSQL versions 13.23, 14.20, 15.15, 16.11, 17.7, and 18.1, you can use the rds\$1casts extension to install additional casts for built-in types, while still being able to create your own casts for custom types. **Topics** + [ ## Installing and using the rds\$1casts extension ](#PostgreSQL.CustomCasts.Installing) + [ ## Supported casts ](#PostgreSQL.CustomCasts.Supported) + [ ## Creating or dropping casts ](#PostgreSQL.CustomCasts.Creating) + [ ## Creating custom casts with proper context strategy ](#PostgreSQL.CustomCasts.BestPractices) ## Installing and using the rds\$1casts extension To create the `rds_casts` extension, connect to your RDS for PostgreSQL DB instance as an `rds_superuser` and run the following command: ``` CREATE EXTENSION IF NOT EXISTS rds_casts; ``` ## Supported casts Create the extension in each database where you want to use custom casts. After creating the extension, use the following command to view all available casts: ``` SELECT * FROM rds_casts.list_supported_casts(); ``` This function lists the available cast combinations (source type, target type, coercion context, and cast function). For example, if you want to create `text` to `numeric` as an `implicit` cast. You can use the following query to find if the cast is available to create: ``` SELECT * FROM rds_casts.list_supported_casts() WHERE source_type = 'text' AND target_type = 'numeric'; id | source_type | target_type | qualified_function | coercion_context ----+-------------+-------------+--------------------------------------+------------------ 10 | text | numeric | rds_casts.rds_text_to_numeric_custom | implicit 11 | text | numeric | rds_casts.rds_text_to_numeric_custom | assignment 13 | text | numeric | rds_casts.rds_text_to_numeric_custom | explicit 20 | text | numeric | rds_casts.rds_text_to_numeric_inout | implicit 21 | text | numeric | rds_casts.rds_text_to_numeric_inout | assignment 23 | text | numeric | rds_casts.rds_text_to_numeric_inout | explicit ``` The rds\$1casts extension provides two types of conversion functions for each cast: + *\$1inout functions* - Use PostgreSQL's standard I/O conversion mechanism, behaving identically to casts created with the INOUT method + *\$1custom functions* - Provide enhanced conversion logic that handles edge cases, such as converting empty strings to NULL values to avoid conversion errors The `inout` functions replicate PostgreSQL's native casting behavior, while `custom` functions extend this functionality by handling scenarios that standard INOUT casts cannot accommodate, such as converting empty strings to integers. ## Creating or dropping casts You can create and drop supported casts using two methods: ### Cast creation **Method 1: Using native CREATE CAST command** ``` CREATE CAST (text AS numeric) WITH FUNCTION rds_casts.rds_text_to_numeric_custom AS IMPLICIT; ``` **Method 2: Using the rds\$1casts.create\$1cast function** ``` SELECT rds_casts.create_cast(10); ``` The `create_cast` function takes the ID from the `list_supported_casts()` output. This method is simpler and ensures you're using the correct function and context combination. This id is guaranteed to remain the same across different postgres versions. To verify the cast was created successfully, query the pg\$1cast system catalog: ``` SELECT oid, castsource::regtype, casttarget::regtype, castfunc::regproc, castcontext, castmethod FROM pg_cast WHERE castsource = 'text'::regtype AND casttarget = 'numeric'::regtype; oid | castsource | casttarget | castfunc | castcontext | castmethod --------+------------+------------+--------------------------------------+-------------+------------ 356372 | text | numeric | rds_casts.rds_text_to_numeric_custom | i | f ``` The `castcontext` column shows: `e` for EXPLICIT, `a` for ASSIGNMENT, or `i` for IMPLICIT. ### Dropping casts **Method 1: Using DROP CAST command** ``` DROP CAST IF EXISTS (text AS numeric); ``` **Method 2: Using the rds\$1casts.drop\$1cast function** ``` SELECT rds_casts.drop_cast(10); ``` The `drop_cast` function takes the same ID used when creating the cast. This method ensures you're dropping the exact cast that was created with the corresponding ID. ## Creating custom casts with proper context strategy When creating multiple casts for integer types, operator ambiguity errors can occur if all casts are created as IMPLICIT. The following example demonstrates this issue by creating two implicit casts from text to different integer widths: ``` -- Creating multiple IMPLICIT casts causes ambiguity postgres=> CREATE CAST (text AS int4) WITH FUNCTION rds_casts.rds_text_to_int4_custom(text) AS IMPLICIT; CREATE CAST postgres=> CREATE CAST (text AS int8) WITH FUNCTION rds_casts.rds_text_to_int8_custom(text) AS IMPLICIT; CREATE CAST postgres=> CREATE TABLE test_cast(col int); CREATE TABLE postgres=> INSERT INTO test_cast VALUES ('123'::text); INSERT 0 1 postgres=> SELECT * FROM test_cast WHERE col='123'::text; ERROR: operator is not unique: integer = text LINE 1: SELECT * FROM test_cast WHERE col='123'::text; ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. ``` The error occurs because PostgreSQL cannot determine which implicit cast to use when comparing an integer column with a text value. Both the int4 and int8 implicit casts are valid candidates, creating ambiguity. To avoid this operator ambiguity, use ASSIGNMENT context for smaller integer widths and IMPLICIT context for larger integer widths: ``` -- Use ASSIGNMENT for smaller integer widths CREATE CAST (text AS int2) WITH FUNCTION rds_casts.rds_text_to_int2_custom(text) AS ASSIGNMENT; CREATE CAST (text AS int4) WITH FUNCTION rds_casts.rds_text_to_int4_custom(text) AS ASSIGNMENT; -- Use IMPLICIT for larger integer widths CREATE CAST (text AS int8) WITH FUNCTION rds_casts.rds_text_to_int8_custom(text) AS IMPLICIT; postgres=> INSERT INTO test_cast VALUES ('123'::text); INSERT 0 1 postgres=> SELECT * FROM test_cast WHERE col='123'::text; col ----- 123 (1 row) ``` With this strategy, only the int8 cast is implicit, so PostgreSQL can unambiguously determine which cast to use. # Best Practices for Parallel Queries in RDS for PostgreSQL Parallel query execution is a feature in PostgreSQL that allows a single SQL query to be broken into smaller tasks that are processed simultaneously by multiple background worker processes. Instead of executing a query entirely in a single backend process, PostgreSQL can distribute parts of the query, such as scans, joins, aggregations, or sorting, across multiple CPU cores. The *leader process* coordinates this execution and gathers the results from the *parallel workers*. However, for most production workloads, especially high-concurrency OLTP systems, we recommend disabling automatic parallel query execution. While parallelism can accelerate queries on large datasets in analytics or reporting workloads, it introduces significant risks that often outweigh the benefits in busy production environments. Parallel execution also introduces significant overhead. Each parallel worker is a full PostgreSQL backend process, which requires process forking (copying memory structures and initializing process state) and authentication (consuming connection slots from your `max_connections` limit). Each worker also consumes its own memory, including `work_mem` for sorting and hashing operations, with multiple workers per query, memory usage multiplies quickly (e.g., 4 workers × 64MB `work_mem` = 256MB per query). As a result, parallel queries can consume considerably more system resources than single-process queries. If not tuned properly, they may lead to CPU saturation (multiple workers overwhelming available processing capacity), increased context switching (the operating system frequently switching between numerous worker processes, adding overhead and reducing throughput), or connection exhaustion (since each parallel worker consumes a connection slot, a single query with 4 workers uses 5 connections total, 1 leader \$1 4 workers, which can quickly exhaust your connection pool under high concurrency, preventing new client connections and causing application failures). These issues are particularly severe under high-concurrency workloads where multiple queries may attempt parallel execution simultaneously. PostgreSQL decides whether to use parallelism based on cost estimates. In some cases, the planner may automatically switch to a parallel plan if it appears cheaper even when it's not ideal in practice. This can happen if index statistics are outdated or if bloat makes sequential scans appear more attractive than index lookups. Because of this behavior, automatic parallel plans can sometimes introduce regressions in query performance or system stability. To get the most benefit from parallel queries in RDS for PostgreSQL, it's important to test and tune them based on your workload, monitor system impact, and disable automatic parallel plan selection in favor of query-level control. ## Configuration Parameters PostgreSQL uses several parameters to control the behavior and availability of parallel queries. Understanding and tuning these is critical to achieving predictable performance: | Parameter | Description | Default | | --- | --- | --- | | max\$1parallel\$1workers | Maximum number of background worker processes that can run in total | GREATEST(\$1DBInstanceVCPU/2,8) | | max\$1parallel\$1workers\$1per\$1gather | Maximum number of workers per query plan node (e.g., per Gather) | 2 | | parallel\$1setup\$1cost | Planner cost added for initiating parallel query infrastructure | 1000 | | parallel\$1tuple\$1cost | Cost per tuple processed in parallel mode (impacts planner decision) | 0.1 | | force\$1parallel\$1mode | Forces planner to test parallel plans (off, on, regress) | off | ### Key Considerations + `max_parallel_workers` controls the total pool of parallel workers. If set too low, some queries may fall back to serial execution. + `max_parallel_workers_per_gather` affects how many workers a single query can use. A higher value increases concurrency, but also resource usage. + `parallel_setup_cost` and `parallel_tuple_cost` affect the planner's cost model. Lowering these can make parallel plans more likely to be chosen. + `force_parallel_mode` is useful for testing but should not be used in production unless necessary. **Note** The default value of the `max_parallel_workers` parameter is dynamically calculated based on instance size using the formula `GREATEST($DBInstanceVCPU/2, 8)`. This means that when you scale your DB instance to a larger compute size with more vCPUs, the maximum number of available parallel workers will automatically increase. As a result, queries that previously executed serially or with limited parallelism may suddenly utilize more parallel workers after a scale-up operation, potentially leading to unexpected increases in connection usage, CPU utilization, and memory consumption. It's important to monitor parallel query behavior after any compute scaling event and adjust `max_parallel_workers_per_gather` if necessary to maintain predictable resource usage. ## Identify Parallel Queries Usage Queries may flip to parallel plans based on data distribution or statistics. For example: ``` SELECT count(*) FROM customers WHERE last_login < now() - interval '6 months'; ``` This query might use an index for recent data, but switch to a parallel sequential scan for historical data. You can log query execution plans by loading the `auto_explain` module. To learn more, see [Logging execution plans of queries](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#) in the AWS knowledge center. You can monitor [CloudWatch Database Insights](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/Database-Insights-Database-Instance-Dashboard.html) for Parallel Query related wait events. To learn more about Parallel Query related wait events, go through [IPC:parallel wait events](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-ipc-parallel.html) From PostgreSQL version 18, you can monitor parallel worker activity using new columns in [https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW) and [https://www.postgresql.org/docs/current/pgstatstatements.html](https://www.postgresql.org/docs/current/pgstatstatements.html): + `parallel_workers_to_launch`: Number of parallel workers planned to be launched + `parallel_workers_launched`: Number of parallel workers actually launched These metrics help identify discrepancies between planned and actual parallelism, which can indicate resource constraints or configuration issues. Use the following queries to monitor parallel execution: For Database-level parallel worker metrics: ``` SELECT datname, parallel_workers_to_launch, parallel_workers_launched FROM pg_stat_database WHERE datname = current_database(); ``` For Query-level parallel worker metrics ``` SELECT query, parallel_workers_to_launch, parallel_workers_launched FROM pg_stat_statements ORDER BY parallel_workers_launched; ``` ## How to Control Parallelism There are several ways to control query parallelism, each designed for different scenarios and requirements. To disable automatic parallelism globally, [modify your parameter group](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.Modifying.html) to set: ``` max_parallel_workers_per_gather = 0; ``` For persistent, user-specific settings, the ALTER ROLE command provides a way to set parameters that will apply to all future sessions for a particular user. For example: `ALTER ROLE username SET max_parallel_workers_per_gather = 4;` ensures that every time this user connects to the database, their sessions will use this parallel worker setting when required. Session-level control can be achieved using the SET command, which modifies parameters for the duration of the current database session. This is particularly useful when you need to temporarily adjust settings without affecting other users or future sessions. Once set, these parameters remain in effect until explicitly reset or until the session ends. The commands are straightforward: ``` SET max_parallel_workers_per_gather = 4; -- Run your queries RESET max_parallel_workers_per_gather; ``` For even more granular control, SET LOCAL allows you to modify parameters for a single transaction. This is ideal when you need to adjust settings for a specific set of queries within a transaction, after which the settings automatically revert to their previous values. This approach helps prevent unintended effects on other operations within the same session. ## Diagnosing Parallel Query Behavior Use `EXPLAIN (ANALYZE, VERBOSE)` to confirm whether a query used parallel execution: + Look for nodes such as `Gather`, `Gather Merge`, or `Parallel Seq Scan`. + Compare plans with and without parallelism. To disable parallelism temporarily for comparison: ``` SET max_parallel_workers_per_gather = 0; EXPLAIN ANALYZE ; RESET max_parallel_workers_per_gather; ``` # Working with parameters on your RDS for PostgreSQL DB instance Working with parameters In some cases, you might create an RDS for PostgreSQL DB instance without specifying a custom parameter group. If so, your DB instance is created using the default parameter group for the version of PostgreSQL that you choose. For example, suppose that you create an RDS for PostgreSQL DB instance using PostgreSQL 13.3. In this case, the DB instance is created using the values in the parameter group for PostgreSQL 13 releases, `default.postgres13`. You can also create your own custom DB parameter group. You need to do this if you want to modify any settings for the RDS for PostgreSQL DB instance from their default values. To learn how, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). You can track the settings on your RDS for PostgreSQL DB instance in several different ways. You can use the AWS Management Console, the AWS CLI, or the Amazon RDS API. You can also query the values from the PostgreSQL `pg_settings` table of your instance, as shown following. ``` SELECT name, setting, boot_val, reset_val, unit FROM pg_settings ORDER BY name; ``` To learn more about the values returned from this query, see [https://www.postgresql.org/docs/current/view-pg-settings.html](https://www.postgresql.org/docs/current/view-pg-settings.html) in the PostgreSQL documentation. Be especially careful when changing the settings for `max_connections` and `shared_buffers` on your RDS for PostgreSQL DB instance. For example, suppose that you modify settings for `max_connections` or `shared_buffers` and you use values that are too high for your actual workload. In this case, your RDS for PostgreSQL DB instance won't start. If this happens, you see an error such as the following in the `postgres.log`. ``` 2018-09-18 21:13:15 UTC::@:[8097]:FATAL: could not map anonymous shared memory: Cannot allocate memory 2018-09-18 21:13:15 UTC::@:[8097]:HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 3514134274048 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections. ``` However, you can't change any values of the settings contained in the default RDS for PostgreSQL DB parameter groups. To change settings for any parameters, first create a custom DB parameter group. Then change the settings in that custom group, and then apply the custom parameter group to your RDS for PostgreSQL DB instance. To learn more, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). There are two types of parameters in RDS for PostgreSQL. + **Static parameters** – Static parameters require that the RDS for PostgreSQL DB instance be rebooted after a change so that the new value can take effect. + **Dynamic parameters** – Dynamic parameters don't require a reboot after changing their settings. **Note** If your RDS for PostgreSQL DB instance is using your own custom DB parameter group, you can change the values of dynamic parameters on the running DB instance. You can do this by using the AWS Management Console, the AWS CLI, or the Amazon RDS API. If you have privileges to do so, you can also change parameter values by using the `ALTER DATABASE`, `ALTER ROLE`, and `SET` commands. ## RDS for PostgreSQL DB instance parameter list The following table lists some (but not all) parameters available in an RDS for PostgreSQL DB instance. To view all available parameters, you use the [describe-db-parameters](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-parameters.html) AWS CLI command. For example, to get the list of all parameters available in the default parameter group for RDS for PostgreSQL version 13, run the following. ``` aws rds describe-db-parameters --db-parameter-group-name default.postgres13 ``` You can also use the Console. Choose **Parameter groups** from the Amazon RDS menu, and then choose the parameter group from those available in your AWS Region. | Parameter name | Apply\$1Type | Description | | --- | --- | --- | | `application_name` | Dynamic | Sets the application name to be reported in statistics and logs. | | `archive_command` | Dynamic | Sets the shell command that will be called to archive a WAL file. | | `array_nulls` | Dynamic | Enables input of NULL elements in arrays. | | `authentication_timeout` | Dynamic | Sets the maximum allowed time to complete client authentication. | | `autovacuum` | Dynamic | Starts the autovacuum subprocess. | | `autovacuum_analyze_scale_factor` | Dynamic | Number of tuple inserts, updates, or deletes before analyze as a fraction of reltuples. | | `autovacuum_analyze_threshold` | Dynamic | Minimum number of tuple inserts, updates, or deletes before analyze. | | `autovacuum_freeze_max_age` | Static | Age at which to autovacuum a table to prevent transaction ID wraparound. | | `autovacuum_naptime` | Dynamic | Time to sleep between autovacuum runs. | | `autovacuum_max_workers` | Static | Sets the maximum number of simultaneously running autovacuum worker processes. | | `autovacuum_vacuum_cost_delay` | Dynamic | Vacuum cost delay, in milliseconds, for autovacuum. | | `autovacuum_vacuum_cost_limit` | Dynamic | Vacuum cost amount available before napping, for autovacuum. | | `autovacuum_vacuum_scale_factor` | Dynamic | Number of tuple updates or deletes before vacuum as a fraction of reltuples. | | `autovacuum_vacuum_threshold` | Dynamic | Minimum number of tuple updates or deletes before vacuum. | | `backslash_quote` | Dynamic | Sets whether a backslash (\$1) is allowed in string literals. | | `bgwriter_delay` | Dynamic | Background writer sleep time between rounds. | | `bgwriter_lru_maxpages` | Dynamic | Background writer maximum number of LRU pages to flush per round. | | `bgwriter_lru_multiplier` | Dynamic | Multiple of the average buffer usage to free per round. | | `bytea_output` | Dynamic | Sets the output format for bytes. | | `check_function_bodies` | Dynamic | Checks function bodies during CREATE FUNCTION. | | `checkpoint_completion_target` | Dynamic | Time spent flushing dirty buffers during checkpoint, as a fraction of the checkpoint interval. | | `checkpoint_segments` | Dynamic | Sets the maximum distance in log segments between automatic write-ahead log (WAL) checkpoints. | | `checkpoint_timeout` | Dynamic | Sets the maximum time between automatic WAL checkpoints. | | `checkpoint_warning` | Dynamic | Enables warnings if checkpoint segments are filled more frequently than this. | | `client_connection_check_interval` | Dynamic | Sets the time interval between checks for disconnection while running queries. | | `client_encoding` | Dynamic | Sets the client's character set encoding. | | `client_min_messages` | Dynamic | Sets the message levels that are sent to the client. | | `commit_delay` | Dynamic | Sets the delay in microseconds between transaction commit and flushing WAL to disk. | | `commit_siblings` | Dynamic | Sets the minimum concurrent open transactions before performing commit\$1delay. | | `constraint_exclusion` | Dynamic | Enables the planner to use constraints to optimize queries. | | `cpu_index_tuple_cost` | Dynamic | Sets the planner's estimate of the cost of processing each index entry during an index scan. | | `cpu_operator_cost` | Dynamic | Sets the planner's estimate of the cost of processing each operator or function call. | | `cpu_tuple_cost` | Dynamic | Sets the planner's estimate of the cost of processing each tuple (row). | | `cursor_tuple_fraction` | Dynamic | Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved. | | `datestyle` | Dynamic | Sets the display format for date and time values. | | `deadlock_timeout` | Dynamic | Sets the time to wait on a lock before checking for deadlock. | | `debug_pretty_print` | Dynamic | Indents parse and plan tree displays. | | `debug_print_parse` | Dynamic | Logs each query's parse tree. | | `debug_print_plan` | Dynamic | Logs each query's execution plan. | | `debug_print_rewritten` | Dynamic | Logs each query's rewritten parse tree. | | `default_statistics_target` | Dynamic | Sets the default statistics target. | | `default_tablespace` | Dynamic | Sets the default tablespace to create tables and indexes in. | | `default_transaction_deferrable` | Dynamic | Sets the default deferrable status of new transactions. | | `default_transaction_isolation` | Dynamic | Sets the transaction isolation level of each new transaction. | | `default_transaction_read_only` | Dynamic | Sets the default read-only status of new transactions. | | `default_with_oids` | Dynamic | Creates new tables with object IDs (OIDs) by default. | | `effective_cache_size` | Dynamic | Sets the planner's assumption about the size of the disk cache. | | `effective_io_concurrency` | Dynamic | Number of simultaneous requests that can be handled efficiently by the disk subsystem. | | `enable_bitmapscan` | Dynamic | Enables the planner's use of bitmap-scan plans. | | `enable_hashagg` | Dynamic | Enables the planner's use of hashed aggregation plans. | | `enable_hashjoin` | Dynamic | Enables the planner's use of hash join plans. | | `enable_indexscan` | Dynamic | Enables the planner's use of index-scan plans. | | `enable_material` | Dynamic | Enables the planner's use of materialization. | | `enable_mergejoin` | Dynamic | Enables the planner's use of merge join plans. | | `enable_nestloop` | Dynamic | Enables the planner's use of nested-loop join plans. | | `enable_seqscan` | Dynamic | Enables the planner's use of sequential-scan plans. | | `enable_sort` | Dynamic | Enables the planner's use of explicit sort steps. | | `enable_tidscan` | Dynamic | Enables the planner's use of TID scan plans. | | `escape_string_warning` | Dynamic | Warns about backslash (\$1) escapes in ordinary string literals. | | `extra_float_digits` | Dynamic | Sets the number of digits displayed for floating-point values. | | `from_collapse_limit` | Dynamic | Sets the FROM-list size beyond which subqueries are not collapsed. | | `fsync` | Dynamic | Forces synchronization of updates to disk. | | `full_page_writes` | Dynamic | Writes full pages to WAL when first modified after a checkpoint. | | `geqo` | Dynamic | Enables genetic query optimization. | | `geqo_effort` | Dynamic | GEQO: effort is used to set the default for other GEQO parameters. | | `geqo_generations` | Dynamic | GEQO: number of iterations of the algorithm. | | `geqo_pool_size` | Dynamic | GEQO: number of individuals in the population. | | `geqo_seed` | Dynamic | GEQO: seed for random path selection. | | `geqo_selection_bias` | Dynamic | GEQO: selective pressure within the population. | | `geqo_threshold` | Dynamic | Sets the threshold of FROM items beyond which GEQO is used. | | `gin_fuzzy_search_limit` | Dynamic | Sets the maximum allowed result for exact search by GIN. | | `hot_standby_feedback` | Dynamic | Determines whether a hot standby sends feedback messages to the primary or upstream standby. | | `intervalstyle` | Dynamic | Sets the display format for interval values. | | `join_collapse_limit` | Dynamic | Sets the FROM-list size beyond which JOIN constructs are not flattened. | | `lc_messages` | Dynamic | Sets the language in which messages are displayed. | | `lc_monetary` | Dynamic | Sets the locale for formatting monetary amounts. | | `lc_numeric` | Dynamic | Sets the locale for formatting numbers. | | `lc_time` | Dynamic | Sets the locale for formatting date and time values. | | `log_autovacuum_min_duration` | Dynamic | Sets the minimum running time above which autovacuum actions will be logged. | | `log_checkpoints` | Dynamic | Logs each checkpoint. | | `log_connections` | Dynamic | Logs each successful connection. | | `log_disconnections` | Dynamic | Logs end of a session, including duration. | | `log_duration` | Dynamic | Logs the duration of each completed SQL statement. | | `log_error_verbosity` | Dynamic | Sets the verbosity of logged messages. | | `log_executor_stats` | Dynamic | Writes executor performance statistics to the server log. | | `log_filename` | Dynamic | Sets the file name pattern for log files. | | `log_file_mode` | Dynamic | Sets file permissions for log files. Default value is 0644. | | `log_hostname` | Dynamic | Logs the host name in the connection logs. As of PostgreSQL 12 and later versions, this parameter is 'off' by default. When turned on, the connection uses DNS reverse-lookup to get the hostname that gets captured to the connection logs. If you turn on this parameter, you should monitor the impact that it has on the time it takes to establish connections. | | `log_line_prefix ` | Dynamic | Controls information prefixed to each log line. | | `log_lock_waits` | Dynamic | Logs long lock waits. | | `log_min_duration_statement` | Dynamic | Sets the minimum running time above which statements will be logged. | | `log_min_error_statement` | Dynamic | Causes all statements generating an error at or above this level to be logged. | | `log_min_messages` | Dynamic | Sets the message levels that are logged. | | `log_parser_stats` | Dynamic | Writes parser performance statistics to the server log. | | `log_planner_stats` | Dynamic | Writes planner performance statistics to the server log. | | `log_rotation_age` | Dynamic | Automatic log file rotation will occur after N minutes. | | `log_rotation_size` | Dynamic | Automatic log file rotation will occur after N kilobytes. | | `log_statement` | Dynamic | Sets the type of statements logged. | | `log_statement_stats` | Dynamic | Writes cumulative performance statistics to the server log. | | `log_temp_files` | Dynamic | Logs the use of temporary files larger than this number of kilobytes. | | `log_timezone` | Dynamic | Sets the time zone to use in log messages. | | `log_truncate_on_rotation` | Dynamic | Truncate existing log files of same name during log rotation. | | `logging_collector` | Static | Start a subprocess to capture stderr output and/or csvlogs into log files. | | `maintenance_work_mem` | Dynamic | Sets the maximum memory to be used for maintenance operations. | | `max_connections` | Static | Sets the maximum number of concurrent connections. | | `max_files_per_process` | Static | Sets the maximum number of simultaneously open files for each server process. | | `max_locks_per_transaction` | Static | Sets the maximum number of locks per transaction. | | `max_pred_locks_per_transaction` | Static | Sets the maximum number of predicate locks per transaction. | | `max_prepared_transactions` | Static | Sets the maximum number of simultaneously prepared transactions. | | `max_stack_depth` | Dynamic | Sets the maximum stack depth, in kilobytes. | | `max_standby_archive_delay` | Dynamic | Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data. | | `max_standby_streaming_delay` | Dynamic | Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data. | | max\$1wal\$1size | Dynamic | Sets the WAL size (MB) that triggers a checkpoint. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Parameters.html) Use the following command on your Amazon RDS for PostgreSQL DB instance to see its current value: 
SHOW max_wal_size;
| | min\$1wal\$1size | Dynamic | Sets the minimum size to shrink the WAL to. For PostgreSQL version 9.6 and earlier, min\$1wal\$1size is in units of 16 MB. For PostgreSQL version 10 and later, min\$1wal\$1size is in units of 1 MB. | | `quote_all_identifiers` | Dynamic | Adds quotes (") to all identifiers when generating SQL fragments. | | `random_page_cost` | Dynamic | Sets the planner's estimate of the cost of a non-sequentially fetched disk page. This parameter has no value unless query plan management (QPM) is turned on. When QPM is on, the default value for this parameter 4. | | rds.adaptive\$1autovacuum | Dynamic | Automatically tunes the autovacuum parameters whenever the transaction ID thresholds are exceeded. | | rds.force\$1ssl | Dynamic | Requires the use of SSL connections. The default value is set to 1 (on) for RDS for PostgreSQL version 15. All other RDS for PostgreSQL major version 14 and older have the default value set to 0 (off). | | `rds.local_volume_spill_enabled` | Static | Enables writing logical spill files to the local volume. | | `rds.log_retention_period` | Dynamic | Sets log retention such that Amazon RDS deletes PostgreSQL logs that are older than n minutes. | | rds.rds\$1superuser\$1reserved\$1connections | Static | Sets the number of connection slots reserved for rds\$1superusers. This parameter is only available in versions 15 and earlier. For more information, see the PostgreSQL documentation [reserved\$1connections](https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-RESERVED-CONNECTIONS). | | `rds.replica_identity_full` | Dynamic | When you set this parameter to `on`, it overrides the replica identity setting to `FULL` for all database tables. This means all column values are written to the write ahead log (WAL), regardless of your `REPLICA IDENTITY FULL` settings. Turning on this parameter may increase your database instance IOPS due to the additional WAL logging. | | rds.restrict\$1password\$1commands | Static | Restricts who can manage passwords to users with the rds\$1password role. Set this parameter to 1 to enable password restriction. The default is 0. | | `search_path` | Dynamic | Sets the schema search order for names that are not schema-qualified. | | `seq_page_cost` | Dynamic | Sets the planner's estimate of the cost of a sequentially fetched disk page. | | `session_replication_role` | Dynamic | Sets the sessions behavior for triggers and rewrite rules. | | `shared_buffers` | Static | Sets the number of shared memory buffers used by the server. | | `shared_preload_libraries ` | Static | Lists the shared libraries to preload into the RDS for PostgreSQL DB instance. Supported values include auto\$1explain, orafce, pgaudit, pglogical, pg\$1bigm, pg\$1cron, pg\$1hint\$1plan, pg\$1prewarm, pg\$1similarity, pg\$1stat\$1statements, pg\$1tle, pg\$1transport, plprofiler, and plrust. | | `ssl` | Dynamic | Enables SSL connections. | | `sql_inheritance` | Dynamic | Causes subtables to be included by default in various commands. | | `ssl_renegotiation_limit` | Dynamic | Sets the amount of traffic to send and receive before renegotiating the encryption keys. | | `standard_conforming_strings` | Dynamic | Causes ... strings to treat backslashes literally. | | `statement_timeout` | Dynamic | Sets the maximum allowed duration of any statement. | | `synchronize_seqscans` | Dynamic | Enables synchronized sequential scans. | | `synchronous_commit` | Dynamic | Sets the current transactions synchronization level. | | `tcp_keepalives_count` | Dynamic | Maximum number of TCP keepalive retransmits. | | `tcp_keepalives_idle` | Dynamic | Time between issuing TCP keepalives. | | `tcp_keepalives_interval` | Dynamic | Time between TCP keepalive retransmits. | | `temp_buffers` | Dynamic | Sets the maximum number of temporary buffers used by each session. | | temp\$1file\$1limit | Dynamic | Sets the maximum size in KB to which the temporary files can grow. | | `temp_tablespaces` | Dynamic | Sets the tablespaces to use for temporary tables and sort files. | | `timezone` | Dynamic | Sets the time zone for displaying and interpreting time stamps. The Internet Assigned Numbers Authority (IANA) publishes new time zones at [ https://www.iana.org/time-zones](https://www.iana.org/time-zones) several times a year. Every time RDS releases a new minor maintenance release of PostgreSQL, it ships with the latest time zone data at the time of the release. When you use the latest RDS for PostgreSQL versions, you have recent time zone data from RDS. To ensure that your DB instance has recent time zone data, we recommend upgrading to a higher DB engine version. You can't modify the time zone tables in PostgreSQL DB instances manually. RDS doesn't modify or reset the time zone data of running DB instances. New time zone data is installed only when you perform a database engine version upgrade. | | `track_activities` | Dynamic | Collects information about running commands. | | `track_activity_query_size` | Static | Sets the size reserved for pg\$1stat\$1activity.current\$1query, in bytes. | | `track_counts` | Dynamic | Collects statistics on database activity. | | `track_functions` | Dynamic | Collects function-level statistics on database activity. | | `track_io_timing` | Dynamic | Collects timing statistics on database I/O activity. | | `transaction_deferrable` | Dynamic | Indicates whether to defer a read-only serializable transaction until it can be started with no possible serialization failures. | | `transaction_isolation` | Dynamic | Sets the current transactions isolation level. | | `transaction_read_only` | Dynamic | Sets the current transactions read-only status. | | `transform_null_equals` | Dynamic | Treats expr=NULL as expr IS NULL. | | `update_process_title` | Dynamic | Updates the process title to show the active SQL command. | | `vacuum_cost_delay` | Dynamic | Vacuum cost delay in milliseconds. | | `vacuum_cost_limit` | Dynamic | Vacuum cost amount available before napping. | | `vacuum_cost_page_dirty` | Dynamic | Vacuum cost for a page dirtied by vacuum. | | `vacuum_cost_page_hit` | Dynamic | Vacuum cost for a page found in the buffer cache. | | `vacuum_cost_page_miss` | Dynamic | Vacuum cost for a page not found in the buffer cache. | | `vacuum_defer_cleanup_age` | Dynamic | Number of transactions by which vacuum and hot cleanup should be deferred, if any. | | `vacuum_freeze_min_age` | Dynamic | Minimum age at which vacuum should freeze a table row. | | `vacuum_freeze_table_age` | Dynamic | Age at which vacuum should scan a whole table to freeze tuples. | | `wal_buffers` | Static | Sets the number of disk-page buffers in shared memory for WAL. | | `wal_writer_delay` | Dynamic | WAL writer sleep time between WAL flushes. | | `work_mem` | Dynamic | Sets the maximum memory to be used for query workspaces. | | `xmlbinary` | Dynamic | Sets how binary values are to be encoded in XML. | | `xmloption` | Dynamic | Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments. | Amazon RDS uses the default PostgreSQL units for all parameters. The following table shows the PostgreSQL default unit for each parameter. | Parameter name | Unit | | --- | --- | | `archive_timeout` | s | | `authentication_timeout` | s | | `autovacuum_naptime` | s | | `autovacuum_vacuum_cost_delay` | ms | | `bgwriter_delay` | ms | | `checkpoint_timeout` | s | | `checkpoint_warning` | s | | `deadlock_timeout` | ms | | `effective_cache_size` | 8 KB | | `lock_timeout` | ms | | `log_autovacuum_min_duration` | ms | | `log_min_duration_statement` | ms | | `log_rotation_age` | minutes | | `log_rotation_size` | KB | | `log_temp_files` | KB | | `maintenance_work_mem` | KB | | `max_stack_depth` | KB | | `max_standby_archive_delay` | ms | | `max_standby_streaming_delay` | ms | | `post_auth_delay` | s | | `pre_auth_delay` | s | | `segment_size` | 8 KB | | `shared_buffers` | 8 KB | | `statement_timeout` | ms | | `ssl_renegotiation_limit` | KB | | `tcp_keepalives_idle` | s | | `tcp_keepalives_interval` | s | | `temp_file_limit` | KB | | `work_mem` | KB | | `temp_buffers` | 8 KB | | `vacuum_cost_delay` | ms | | `wal_buffers` | 8 KB | | `wal_receiver_timeout` | ms | | `wal_segment_size` | B | | `wal_sender_timeout` | ms | | `wal_writer_delay` | ms | | `wal_receiver_status_interval` | s |