digoal
2023-10-26
PostgreSQL , PolarDB , pgbouncer , transaction mode , session mode , prepared statement , simple query , SQL注入
pgbouncer 1.21 开始支持 prepared statement in 事务模式
为什么使用prepared statement?
- 1、prepared statement可以大幅度提升高并发SQL的qps, 减少硬解析带来的cpu损耗. (特别是复杂一点的请求, 对CPU的开销还是比较大的)
- 2、prepared statement提升了安全性, 降低了SQL注入的风险.
所以大多数的开发者都会选择使用prepared statement代替simple query.
以往要支持prepared statement, 我们不得不使用pgbouncer的会话模式, 但是这就使得它作为减少数据库连接的作用荡然无存, 因为应用有多少个连接就要开多少个数据库连接.
使用 pgbouncer事务模式才可以大幅度节省高并发业务的连接数. 因为事务结束后, 这个pgbouncer - db的连接就可以被其他事务请求复用.
pgbouncer 1.21 开始支持了prepared statement in 事务模式(设置 max_prepared_statements in the pgbouncer.ini), 确实是应用的福音.
期待未来pgbouncer能支持多线程, 会更有竞争力.
《PostgreSQL 转义、UNICODE、与SQL注入》
https://www.crunchydata.com/blog/prepared-statements-in-transaction-mode-for-pgbouncer
https://github.com/pgbouncer/pgbouncer
PgBouncer support protocol-level prepared statements.
PgBouncer is version 1.21 or newer, we can set max_prepared_statements = xxx in the pgbouncer.ini
DBI version 1.644, DBD::Pg version 3.17.0, Postgres version 160000
Connection 1: backend PID is 15017
Connection 2: backend PID is 15018
Connection 1 prepared statement PGBOUNCER_1: SELECT count(*) FROM pg_class WHERE reltuples > $1
Forcing client connection to switch their backend PIDs
Connection 1: backend PID is 15018
Connection 2: backend PID is 15017
Connection 2 prepared statement PGBOUNCER_1: SELECT count(*) FROM pg_class WHERE reltuples > $1
Connection 1 prepared statement PGBOUNCER_1: SELECT count(*) FROM pg_class WHERE reltuples > $1
Success!
