超越查询优化：使用 SQLAlchemy 和 RDSProxy 的 Aurora Postgres 连接池

The main limitation of using an external proxy with transaction pooling is that Postgres’ session-based features will not work correctly. This means any kind of “SET XXXX” will not work. Also, historically, prepared statements have also been a problem. However, AWS introduced some enhancements to RDSProxy to support prepared statements in late 2023 and we never encountered any issues. We encountered several issues when rolling out RDSProxy in our test/prod environment:

使用外部代理和事务池的主要限制是 Postgres 的基于会话的功能将无法正常工作。这意味着任何类型的 SET XXXX 都将无法工作。此外，历史上，预处理语句也一直是一个问题。然而，AWS 在 2023 年底引入了一些增强功能，以支持预处理语句，我们从未遇到过任何问题。在我们的测试/生产环境中推出 RDSProxy 时，我们遇到了几个问题：

Statement timeout

语句超时

When connecting with the database using the proxy, we received the following error:

当使用代理连接数据库时，我们收到了以下错误：

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "aurora.reader.proxy.com" (10.0.146.4), port 5432 failed: FATAL: Feature not supported: RDS Proxy currently doesn't support command-line options.

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) 连接到服务器 "aurora.reader.proxy.com" (10.0.146.4)，端口 5432 失败：致命错误：不支持的功能：RDS Proxy 当前不支持命令行选项。

The reason for this was the following configuration in our settings files:

原因在于我们设置文件中的以下配置：

SQLALCHEMY_ENGINE_OPTIONS = {'connect_args': {'options': '-c statement_timeout={timeout}'.format(
timeout=str_env('POSTGRES_STATEMENT_TIMEOUT')
)}}

To overcome this issue, we decided to set the statement_timeout at the user level and removed the SQLALCHEMY_ENGINE_OPTIONS:

为了解决这个问题，我们决定在用户级别设置statement_timeout并移除了SQLALCHEMY_ENGINE_OPTIONS：

ALTER ROLE scott SET statement_timeout = '5s';

Session pinning

会话固定

This is probably the most important issue to be mindful of when moving to RDSProxy. Normally, RDSProxy optimizes database connections by multiplexing multiple client requests over fewer actual dat...