RDS Proxy Pinning

Question

We have included RDS proxy  in our lambda function but lot of pinning of session is happening  and we receive one of log as follows:

Adding my observations here related to the pinning your observed during load test.

I see that pinning was happening doing the load test with the below reason:

2024-12-09 09:35:49,399 [ClientIOThread-7-1] WARN: SERVICE_CUSTOMER: [proxyEndpoint=default] [clientConnection=1234] The client session was pinned to the database connection [dbConnection=1234] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: SQL changed session settings that the proxy doesn't track. Consider moving session configuration to the proxy's initialization query. Digest: "insert into . (log_id, source_system) values(**nextval($1),** $2) returning log_id;".

@timestamp

1733736949399

2024-12-09 09:36:05,468 [ClientIOThread-7-1] WARN: SERVICE_CUSTOMER: [proxyEndpoint=default] [clientConnection=3434] The client session was pinned to the database connection [dbConnection=3434] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: SQL changed session settings that the proxy doesn't track. Consider moving session configuration to the proxy's initialization query. Digest: "insert into . (log_id, source_system) values(**nextval($1),** $2) returning log_id;".

@timestamp

1733736965468

Is next Val causes pinning . Documents says it causes pinning then what could be solution for this ?

re:Post Agent · Answer

Yes, the use of `nextval()` function in PostgreSQL can indeed cause pinning in RDS Proxy. This is because `nextval()` changes the session state, which RDS Proxy cannot safely share across different client sessions.

The reason for this pinning is that sequence operations like `nextval()` modify the session's state, and RDS Proxy cannot guarantee that this state change is appropriate for other sessions. As a result, it pins the connection to ensure that the sequence state remains consistent for the current session.

To address this issue and reduce pinning, you have a few options:

1. Use an initialization query: You can set up an initialization query for the proxy to establish the initial session state without interfering with transaction-level multiplexing. This way, you can pre-fetch sequence values or set up other session-specific configurations.

2. Batch sequence value generation: Instead of generating sequence values for each insert, you could generate a batch of sequence values at the beginning of your transaction and use them for multiple inserts. This reduces the frequency of `nextval()` calls.

3. Use application-generated IDs: If possible, consider generating unique identifiers in your application logic instead of relying on database sequences. This approach can help avoid pinning altogether.

4. Optimize your connection handling: Ensure that your application efficiently manages database connections, closing them promptly when no longer needed to release pinned sessions.

5. Monitor and analyze: Use Amazon CloudWatch metrics to monitor the frequency of pinning and identify patterns or specific queries causing excessive pinning.

Remember, while completely eliminating pinning might not be possible, these strategies can help reduce its occurrence and improve the overall efficiency of your RDS Proxy usage.  
**Sources**  
[Avoiding pinning an RDS Proxy - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-pinning.html)  
[Avoiding pinning an RDS Proxy - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-proxy-pinning.html)  
[RDS Proxy concepts and terminology - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.howitworks.html)  
[Connecting to a database through RDS Proxy - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-connecting.html)

RDS Proxy Pinning

Add your answer

Relevant content