The Problem

When using AWS RDS Proxy for PostgreSQL for the first time, I see many pieces of the following logs

Consider moving session configuration to the proxy’s initialization query. Digest: “set extra_float_digits = $1”.

and

The client session was pinned to the database connection [dbConnection=2678093940]

AWS said

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.

For example, your application changes a session variable or configuration parameter. In this case, later statements can rely on the new variable or parameter to be in effect. Thus, when RDS Proxy processes requests to change session variables or configuration settings, it pins that session to the DB connection. That way, the session state remains in effect for all later transactions in the same session.

The Impact of Pinning

Pinning reduces the effectiveness of connection reuse (By default, RDS Proxy can reuse a connection after each transaction in your session.).

As the workloads grows, increased pinned connections forces me to scale up the databases to allow more database connections.

A Bit More Understanding About Pinning

In the prevalent application server architectures, we usually have application-side connection pools using HikariCP, DBCP etc.

When having multiple AP servers having application-side pooling, a Database Connection might be pinned to a particular Client Connection even the Client Connection is idle in the Application-side connection pooling.

Why we see "set extra_float_digits = $1" in the AWS RDS Proxy log? After a little effort, I see the PostgreSQL driver will set this depending on the assumed server version when trying to connect.

if (assumeVersion.getVersionNum() >= ServerVersion.v9_0.getVersionNum()) {
  // User is explicitly telling us this is a 9.0+ server so set properties here:
  paramList.add(new StartupParam("extra_float_digits", "3"));
  String appName = PGProperty.APPLICATION_NAME.getOrDefault(info);
  if (appName != null) {
    paramList.add(new StartupParam("application_name", appName));
  }
} else {
  // User has not explicitly told us that this is a 9.0+ server so stick to old default:
  paramList.add(new StartupParam("extra_float_digits", "2"));
}

How to avoid Pinning for AWS RDS Proxy for PostgreSQL

As the log suggested, we could set it in Initialization query in AWS RDS Proxy. For example, set extra_float_digits = 3 for PostgreSQL after V9.0 and set extra_float_digits = 2 for others. And must NOT forget to set the assumeMinServerVersion in the DataSource property for your client connection.

There are a number of interactions in PostgreSQL will cause pinning

Using SET commands

Using PREPARE, DISCARD, DEALLOCATE, or EXECUTE commands to manage prepared statements

…

😲 Prepared statement is very common in our application codes. Fortunately, AWS announced Amazon RDS Proxy multiplexing support for PostgreSQL Extended Query Protocol on 10 NOV 2023.

In the PostgreSQL JDBC Driver I used, PostgreSQL Extended Query Protocol is used by default. Thus before the above support released, we suffered from Pinning. Probably setting preferQueryMode to simple (instead of extended) will help.

If we don’t have application-side connection pooling (e.g. in serverless architecture), pinning will not be a problem, but we lost the benefit of reducing recurring connection establishment between our application (e.g. AWS Lambda) and RDS Proxy.