Improve the performance of your AWS RDS Postgres Database by avoiding Session Pinning

What do I have in the application?

It just started as a simple microservice to persist some data and generate metrics around it. But as with any project many features were added over time to the application like quartz schedulers with simple triggers which also uses the database to store job details and triggers.

A complicated query runs against a view every 15 seconds to collect metrics. Obviously our first thought was tuning that massive complex query. The database schema itself is not complicated. After creating indexes around the columns used in the where clause, there was still no improvement in performance .

The explain plan of the query shows that execution time of the query is at around 30s which is not the greatest, but while tracking the query latency through application, it was almost at more than a minute.

Pro tip — With Hikari you can set a time limit for the the leak-detection-threshold property allowing you to see when a connection takes more than the set amount of time.

How did I identify the issue?

While trying to take a stab at the query performance, I monitored the cloud watch RDS metrics from AWS and noticed insane amount of write iOPS and read iOPS. The application itself is simple that for every transaction, would expect 3 writes to the database. Request per seconds was at around ~40RPS. So the expectation is to have 40 * 3 = 120 write iOPS, but were seeing around 2K iOPS, which is insanely high.

This DB performance degradation was causing a cascading effect that, the application’s processing speed slowed down. This resulted in a kafka lag because producers were producing faster than it could be consumed— till it gets to a point that everything get choked up and hang.

Now we have to find why there are so many writes?!

The backend’s most recent query can be found in the pg_stat_activity view query column. If state is active then the query field shows the currently executing query. In all other states, it shows the last query that was executed.

Out of 120 total connections almost 115 of them were executing the query SET application_name = "PostgreSQL JDBC Driver"

There is our problem! Before we dive in on how to fix this issue we need to understand some basic concepts with RDS proxy.

What is Session Pinning?

AWS RDS Proxy handles the infrastructure for connection pooling. Each proxy handles connections to a single RDS DB instance or Aurora DB cluster. These set of connections that a proxy keeps “open and available” for your database application to use, form the connection pool

All the statements within a single transaction always use the same underlying database connection. The connection becomes available for use by a different session when the transaction ends.

By default, RDS Proxy can reuse a connection after each transaction in your session. This transaction-level reuse is called multiplexing

When RDS Proxy temporarily removes a connection from the connection pool to reuse it, that operation is called borrowing the connection. When it’s safe to do so, RDS Proxy returns that connection to the connection pool.

In some cases, RDS Proxy can’t be sure that it’s safe to reuse a database connection outside of the current session. In these cases, it keeps the session on the same connection until the session ends. This fallback behavior is called pinning

By default, RDS Proxy detects when a transaction ends through the network protocol used by the database client application. Transaction detection doesn’t rely on keywords such as COMMIT or ROLLBACK appearing in the text of the SQL statement.

In some cases, RDS Proxy might detect a database request that makes it impractical to move your session to a different connection. In these cases, it turns off multiplexing for that connection for the remainder of your session. The same rule applies if RDS Proxy can’t be certain that multiplexing is practical for the session.

RDS Proxy automatically pins a client connection to a specific DB connection when it detects a session state change that isn’t appropriate for other sessions. Pinning reduces the effectiveness of connection reuse. If all or almost all of your connections experience pinning, consider modifying your application code or workload to reduce the conditions that cause the pinning.

How does the problem get resolved?

In my particular use case, I could see from pg_stat_activity table that, trying to SET the application name so often is the cause for pinning. Setting this parameter at the JDBC driver as a connection parameter will prevent the JDBC driver from performing an extra round trip during connection startup.

You can set this parameter at the url like this

jdbc:postgresql://myapplication-2020123456789.somedomain.region.rds.amazonaws.com:5432/dbname?ApplicationName=myapplicationname

Other causes of pinning and how to identify and avoid them?

• Any statement with a text size greater than 16KB cause the proxy to pin the session — avoid unnecessary database requests and be cautious of request size
• Using prepared statements, setting parameters, or resetting a parameter to its default — Set the JDBC connection parameter preferQueryMode to extendedForPrepared to avoid pinning. The extendedForPrepared ensures that the extended mode is used only for prepared statements
• Setting a user variable or a system variable (with some exceptions) causes the proxy to pin the session — set these variables as connection parameter at the JDBC url to avoid extra round trips. For example in PostgreSQL when using JDBC set the JDBC connection parameter assumeMinServerVersion to at least 9.0 to avoid pinning. Doing this prevents the JDBC driver from performing an extra round trip during connection startup when it runs SET extra_float_digits = 3
• Creating a temporary tables, sequences or views causes the proxy to pin the session
• Declaring cursors
• Discarding the session state
• Listening on a notification channel
• Loading a library module such as auto_explain
• Manipulating sequences using functions such as nextval() and setval()
• Interacting with locks using functions such as pg_advisory_lock() and pg_try_advisory_lock()

Reference — https://docs.aws.amazon.com/es_mx/AmazonRDS/latest/UserGuide/rds-proxy.html#rds-proxy-pinning

Thanks to Matt for reviewing the blog!