Search code examples
postgresqlpgbouncer

The difference of pgBouncer pooling types

I was reading about pgBouncer and couldn't completely understand how different types of pooling work:

Session pooling
Most polite method. When a client connects, a server connection will be assigned to it for the whole duration the client stays connected. When the client disconnects, the server connection will be put back into the pool. This is the default method.

Transaction pooling
A server connection is assigned to a client only during a transaction. When pgbouncer notices that transaction is over, the server connection will be put back into the pool.

Statement pooling
Most aggressive method. The server connection will be put back into the pool immediately after a query completes. Multi-statement transactions are disallowed in this mode as they would break.
  1. Is it correct that ...pgbouncer notices that transaction is over... in Transaction pooling description means that COMMIT or ROLLBACK was executed, or there is something else?

Let's watch at following query:

BEGIN                     -- 1
SELECT * FROM test_table  -- 2
SELECT * FROM test_table  -- 3
COMMIT                    -- 4
SELECT * FROM test_table  -- 5

If I use session pooling, all 5 lines will be executed and the client will stay connected after it.

  1. Is it correct that connection will be stopped after 4-th line if transaction pooling is used?
  2. What is the difference between query and statement in the description of Statement pooling?

UPDATE: If I send following query using transaction pooling:

SELECT * FROM test_table
SELECT * FROM test_table 
... -- repeat many times
SELECT * FROM test_table --last

will connection be put back to pool after "last" or the sequence of statements can be divided into transactions without using BEGIN-COMMIT?

Solution

  • With transaction pooling, the connection will go back into the pool after step 4, but it will not be "stopped". Step 5 could be executed through a different database connections.

    "Query" means "statement" in the description of statement pooling.

    In your last example, both transaction and statement pooling can run each statement on a different connection (remember that PostgreSQL uses autocommit, so each statement runs in its own transaction by default).