Recently I started learning about pgbouncer, but there are some things I do not understand about the parameter server_reset_query
.
Question 1: What is the main purpose of server_reset_query
?
Question 2: There are three modes of the pool_mode of pgbouncer: session ,transaction and
statement mode. When the pool_mode is session, we usually set the values of
server_reset_query to 'DISCARD ALL'. When the pool_mode is transaction
we usually set the values of server_reset_query to '',
I do not know the details about this, so can anybody explain this?
Best Answer
Since pgbouncer is a connection pooler, it will reuse one actual connection to the database server for potentially many client connections. It has to make sure that whatever session state the first client created is reset when the next client gets the connection. Otherwise the first client might do something like
SET statement_timeout = '5min'
, and that would apply to all subsequent clients who happen to be assigned that connection. To avoid that, pgbouncer issues the "server reset query" before handing out the server connection to a new client. As the documentation saysThe main reason this setting exists is that the
DISCARD ALL
statement, which was invented for this very purpose, was not available before PostgreSQL 8.3.The setting applies independently of the pool mode. Note that the client could issue a
SET
statement or something like that in any pool mode.The documentation also says
I think this is a faith-based approach. If you think your code is clean and you need the extra performance, go for it. I've been bitten by this, though. For example, if you use check_postgres to monitor your pgbouncer connections, it will issue a
SET statement_timeout
like I showed above and mess up your connections.