I would like to have some recommendations about connection pooling in PostgreSQL. We already have connection pooling using Pgbouncer behind an Azure LB. The pgbouncer itself is working pretty much good and later we planning to change pooling method to transaction. However we recently found that the nodejs client lib has connection pooling using pg.pool routed to LB. Now we have two layer of pooling one at application layer and other one at the db. Is it a good practice to have multiple layer of pooling? Or just pgbouncer enough to handle this considering the transaction pooling method? What are the things to consider which layer of pooling best for our system?

I've always used PgBouncer in session mode, but its performance isn't so good, as PHP scripts that run for a long time end up with a long session connected to the database (even for minutes), without releasing the connection for the other requests. Increased the number of connections in IDLE until reaching the limit of connections. I tried switching to Transaction mode, but for lack of knowledge, it turned out to be a bad idea. As I found out the hard way, some PostgreSQL functions require to be in the same session as the previous command, such as lastval(), currval() after an insert. Reading the PgBouncer documentation there is a list of functions that are not accepted in transaction mode, but I haven't found any documentation (even PostgreSQL) that says all functions that must run in the same session after an operation (insert, delete, update, etc. ). Is there any location with all these restrictions documented? > Note: As I use the CodeIgniter framework for PHP, the insert command > is executed separately from lastval(), currval(). That's why I can't > execute both in the same transaction with the database.

While the connection method is “md5”, I can manage pgbouncer connections with two different connection methods: users connected with certificate and users connected with password. Postgresql Version: psql (PostgreSQL) 16.6 (Ubuntu 16.6-1.pgdg22.04+1) Pgbouncer Version: PgBouncer 1.23.1 In this case I set pgbouncer and postgresql config: Postgresql password_encryption = md5 pg_hba.conf: host all all 127.0.0.1/32 md5 Pgbouncer pgbouncer.ini: client_tls_sslmode = allow client_tls_ca_file = /etc/ssl/postgresql/ca-cert.pem client_tls_key_file = /etc/ssl/postgresql/server-key.pem client_tls_cert_file = /etc/ssl/postgresql/server-cert.pem client_tls_ciphers = normal ignore_startup_parameters = extra_float_digits auth_type = hba auth_file = /etc/pgbouncer/userlist.txt auth_hba_file = /etc/pgbouncer/pg_hba.conf pg_hba.conf: hostssl dbname testuser 192.168.1.1/32 cert (setting of my certificate user) host dbname postgres 192.168.1.1/32 md5 (my user logged in with password) userlist.txt: "testuser" "md5…" "postgres" "md5…" In this case, both my certificate user and password user log in successfully. Login log for my certificate user (verbose = 0): 2025-03-23 00:31:41.972 +03 LOG C-0x55a542aaf720: dbname/testuser@192.168.1.1:56248 login attempt: db=dbname user=testuser tls=TLSv1.3/TLS_AES_256_GCM_SHA384 replication=no 2025-03-23 00:31:42.394 +03 LOG C-0x55a54288e620: dbname/testuser@192.168.1.1:56249 login attempt: db=dbname user=testuser tls=TLSv1.3/TLS_AES_256_GCM_SHA384 replication=no Logon log of my user logging in with password (verbose = 0): 2025-03-23 00:38:27.713 +03 LOG C-0x55a54288e8d8: dbname/postgres@192.168.1.1:56357 login attempt: db=dbname user=postgres tls=TLSv1.3/TLS_AES_256_GCM_SHA384 replication=no 2025-03-23 00:38:28.183 +03 LOG C-0x55a542891458: dbname/postgres@192.168.1.1:56358 login attempt: db=dbname user=postgres tls=TLSv1.3/TLS_AES_256_GCM_SHA384 replication=no Then I change the encryption method in postgresql to scram sha-256 and change the user's password to detect the new method. Then I paste the new method into the userlist file as it is: Postgresql password_encryption = scram-sha-256 pg_hba.conf: host all all 127.0.0.1/32 scram-sha-256 Pgbouncer pg_hba.conf: hostssl dbname testuser 192.168.1.1/32 cert (setting of my certificate user) host dbname postgres 192.168.1.1/32 scram-sha-256 (my user logged in with password) userlist.txt: "postgres" "SCRAM-SHA-256$4096:…" "testuser" "SCRAM-SHA-256$4096:…" After making the changes and reloading, I successfully connect with my user logged in with password. Session log: 2025-03-23 00:54:09.709 +03 LOG C-0x55a542890c30: dbname/postgres@192.168.1.1:56517 login attempt: db=dbname user=postgres tls=TLSv1.3/TLS_AES_256_GCM_SHA384 replication=no 2025-03-23 00:54:10.202 +03 LOG C-0x55a542aaf720: dbname/postgres@192.168.1.1:56518 login attempt: db=dbname user=postgres tls=TLSv1.3/TLS_AES_256_GCM_SHA384 replication=no But when I try to connect with my certificate user, I get the following error: 2025-03-23 01:01:14.424 +03 LOG C-0x55a542aadea8: dbname/testuser@192.168.1.1:56556 login attempt: db=dbname user=testuser tls=TLSv1.3/TLS_AES_256_GCM_SHA384 replication=no 2025-03-23 01:01:14.425 +03 LOG S-0x55a542ad01c0: dbname/testuser@127.0.0.1:5432 new connection to server (from 127.0.0.1:54000) 2025-03-23 01:01:14.430 +03 LOG S-0x55a542ad01c0: dbname/testuser@127.0.0.1:5432 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1 2025-03-23 01:01:14.431 +03 ERROR S-0x55a542ad01c0: dbname/testuser@127.0.0.1:5432 cannot do SCRAM authentication: password is SCRAM secret but client authentication did not provide SCRAM keys 2025-03-23 01:01:14.431 +03 LOG C-0x55a542aadea8: dbname/testuser@192.168.1.1:56556 closing because: server login failed: wrong password type (age=0s) 2025-03-23 01:01:14.431 +03 WARNING C-0x55a542aadea8: dbname/testuser@192.168.1.1:56556 pooler error: server login failed: wrong password type 2025-03-23 01:01:14.431 +03 LOG S-0x55a542ad01c0: dbname/testuser@127.0.0.1:5432 closing because: failed to answer authreq (age=0s) What is causing the problem? Is it not possible to enter certificates with Scram SHA-256 in Pgbouncer? Thanks.

My web server is deployed on Kubernetes with horizontal pod scaling and a separate, non auto-scaling, PostgreSQL service which runs a both a master and readonly replica nodes, with high-availability on failover. The web server is written in Go and uses PGX v5 for all interactions with PostgreSQL. This includes creating a connection pool (pgxpool) on pod startup, with N connections. N is such a number that when multiplied by the maximum number of pods is smaller than the maximum connections allowed by the PostgreSQL database. Usually N is between 10 and 20. In an effort to try and optimize performance under load, I notice that there is some contention over connection acquisition on pods, namely pool.acquire inside pgx is taking longer under high load. My assumption is that this is due to having the limit on N concurrent open transactions from the pod to the database. With each concurrent transaction (each request to the web server uses one transaction while it's handled) 'hogging' one connection, so whenever a new request to the server arrives, it needs to wait for one of the open transaction to finish if all N connections are in use. This scenario makes sense, especially when the HPA is scaled down as there are less pods and the same amount of maximum database connections (N) per pod. I'm experimenting with PGBouncer in an effort to address this but I see some unexpected results. My hypothesis was that enabling PGBouncer in 'transaction' mode would allow me to increase N, since the actual management of connection is abstracted away to PGBouncer. I configured PGBouncer to have about 80% of the max connections the DB can take (680 outnof 800) and increased N by a multiply of 5 (from 10 to 50). Looking at database metrics, I do observe less open connections to the database which is expected due to PGBouncer using connections in a smarter way. However, I also observe two unexpected behaviors: Increased CPU on the database. I imagine that this is due to PGBouncer process using resources, but it's in some scenarios it is not something I can accept. I think that perhaps reducing the number of connections dedicated to PGBouncer from 80% to a lower figure would help. The average duration of pool.acquire on the web server increased rather than decreasing. This is despite of increasing N to be sufficiently high - that is higher than the number of incoming concurrent transactions from each pod to the database. I've a strong feeling I'm doing something wrong. Should I stop using pgxpool in tandem with PGBouncer? Where can I look to pinpoint the reason for increased duration of connection acquisition from pgxpool?

I am getting pooler error: query_wait_timeout for different databases in the pgbouncer log 6 to 7 times a day . When the error occurs for masterdata database , it gives me a timeout in the application. The configuration is below :

: "md5"
  default_pool_size: 10
  max_client_conn: 5000
  max_db_connections: 400
  min_pool_size: 0
  pool_mode: "transaction"
  reserve_pool_size: 20
  reserve_pool_timeout: 1
  query_timeout: 0
  ignore_startup_parameters: "search_path, extra_float_digits"
  server_reset_query: "DISCARD ALL"
  server_reset_query_always: 1

As the query timeout is disabled (query_timeout:0) and the pool_mode is set to transaction, why am I still having this error? Do I need to set some min_pool_size for my masterdata database to eliminate the above error?

We're using a setup of one Postgresql master server and a several replicas to be able to withstand master failure. Connection to master server is made through PgBouncer.
Currently the idea is that replicas would serve reads when the master is down.
Is it a good idea to connect to replicas via PgBouncer during master failover/switchover? Should it be the same PgBouncer instance or a separate one (for fault tolerance)?
We build a web service and a set of background jobs, database transactions are typically short-lived. If there's an article explaining this, I would appreciate if you share a link

My question is similar to: how to spy on a transaction that is not yet committed in postgres for debugging What I tried: 1. I start a pgbouncer instance, and set the max-db-connection to 1 1. From my node application, i connect to pg-bouncer 1. I BEGIN a transaction, and inserted a few rows, i.e: INSERT INTO USERS VALUES ('john') 1. I end the application without COMMIT 1. I connect to pgbouncer from another client (i.e: Datagrip) Expected: I can continue from the uncommitted transaction, and SELECT * FROM USERS WHERE name = 'john', would retrieve the rows. But what happens is that PgBouncer would close the connection ( closing because: unclean server (age=0s)) when a client connects, since it knows that there is an uncommited transaction. Is there a way to make this hack work with/without using PgBouncer?

I have SQLAlchemy setup with pgbouncer, my pgboucer is configured to use session mode and my sqlalchemy engine config is as follows:

engine = create_async_engine(
    url=db_url,
    echo=False,
    pool_size=200,
    max_overflow=20,
    pool_timeout=30,
    pool_pre_ping=True,
    pool_recycle=1800,
    pool_reset_on_return=None,
    poolclass=AsyncAdaptedQueuePool,
    connect_args={"prepared_statement_cache_size": 0, 'server_settings': {'jit': 'off'}}
)
Session = sessionmaker(
    bind=engine,
    class_=AsyncSession,
    expire_on_commit=False,
    autoflush=False,
    autocommit=False,
)

My session usage is:

@asynccontextmanager
async def get_session_context() -> AsyncSession:  # type: ignore
    async with Session() as session:
        if session is None:
            raise Exception("Database session is None")
        try:
            yield session
        except Exception as e:
            LOGGER.error(pprint.pprint(e, indent=4, depth=4))
            await session.rollback()
            raise e
        finally:
            await session.close()

but I keep getting this error

.exceptions.ConnectionDoesNotExistError: connection was closed in the middle of operation

It seems sqlalchemy closes the database connection while it is still in use. The reason why I do not think the connection is getting closed from the pgbouncer side is because I tried using sqlalchemy with the database directly and I get the same error. it seems the connection is getting closed somehow.

I need some advice for upgrading pgbouncer. I couldn't find any document which tells me how to upgrade pgbouncer. I tried the command

apt install pgbouncer

I wonder if there is another good, efficient way of upgrading or not. What should your advice be to me for upgrading process?

Recently, our DB server got hit by high load. When I went to investigate, there were 49 queries from the same user for the same database. I checked our pgbouncer.ini, and sure enough, default_pool_size is set to 20 (we only turned it down to 20 fairly recently, it used to be higher). There are no per-database changes, and the only per-user change is not for the user in question. Now I am checking on pools that are larger than default_pool_size + reserve_pool_size (5), and it's happening fairly regularly. E.g. in the last five minutes, from SHOW POOLS;:

database                    |      user       | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait |  pool_mode  
 redacted_db                                    | redacted        |      1047 |          2 |        37 |       0 |       0 |         0 |        1 |       0 | transaction

I've checked SHOW CONFIGS to make sure the values are set as they are in pgbouncer.ini. Have I completely misunderstood how pgbouncer works? Or is there some nuance that I've missed?

my app is written in Go and I use PgBouncer as a connection pool to my Postgres Database. One downside of PgBouncer, in transaction mode, is that I can't use prepared statements. So I have 2 options, using session mode (which is bad) or to disable prepared statements from clients. Is it safe (SQL Injection) to not use prepared statements and use only Parameterized Query (eg. (Select item from products where id = ? , itemID) )

I'm trying to configure the latest pgbouncer to work with postgres 9. I can connect to my db using psql with the right password but when I use psql -p 6432 I can't connect with the error message of psql: ERROR: auth failed This seems like it might be caused by my userlist.txt file, but I'm not sure of that. I checked and all required files are owned fully by Postgres system user pgbouncer.ini

[databases]
postgres = host=localhost port=5433 auth_user=postgres dbname=postgres

[pgbouncer]
pidfile = /usr/local/pgbouncer-1.9.0/pgbouncer.pid
logfile = /usr/local/pgbouncer-1.9.0/log/pgbouncer.log

user = postgres

listen_addr = *
listen_port = 6432

auth_type = md5
auth_file = /usr/local/pgbouncer-1.9.0/etc/userlist.txt

Userlist.txt "postgres" "md5" Command used to start pgbouncer ./bin/pgbouncer -d etc/pgbouncer.ini Log output showing failure

2019-08-20 13:46:01.080 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43286 login attempt: db=postgres user=postgres tls=no
2019-08-20 13:46:01.080 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43286 closing because: client unexpected eof (age=0)
2019-08-20 13:46:06.980 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43414 login attempt: db=postgres user=postgres tls=no
2019-08-20 13:46:06.980 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43414 closing because: auth failed (age=0)
2019-08-20 13:46:06.980 16446 WARNING C-0x1028ce0: postgres/postgres@127.0.0.1:43414 pooler error: auth failed

We have 2 applications with ~10 instances of each. All these instances are connected to the same Postgres database. The applications uses a client pool (some applicative ORM providing a connection pool locally) We plan to install PgBouncer (one for all). - Is this a good plan for performances? - May I remove the application connection pool when I install PgBouncer?

I'm running a PostgreSQL cluster with PGBouncer and very often I see in pg_stat_activity that an app is connected to the database and stays in "idle in transaction" state for days. Obviously, this is a problem with the app. However, can this be also handled from the database side in terms of limiting the lifetime of a connection in idle state?

I have a server with two instances of PG Bouncer running on different ports. Both are using session mode to pool. I set up HA Proxy to load balance incoming connections between these two processes to distribute the load. I get "server closed the connection unexpectedly" intermittently when connecting through HA Proxy. If I connect directly to one of the PG Bouncer instances I do not receive these errors. Has anyone setup something similar to this?

Is it possible to specify pgbouncer reserve_pool_timeout in milliseconds, I have tried specifying it with '500 ms' but throwing an error and then tried specifying 0.5, this time no error but unable to find out it's really working or just disabled the timeout but reserve_pool are being utilised.

Please note: I'm still learning a lot of things about postgres. I'm asking for advice to install pgBouncer, but if you have any better suggestions what I could do, please tell me. I have a nodejs application on a single vps which uses pg-promise for interacting with a postgres db. pg-promise uses pg-pool so connection pooling is done on the application level. I'm planning to add a second vps to scale my application horizontally. I want to implement connection pooling on the database level by using pgBouncer. I'm installing pgBouncer on top of my Postgres 14 db on my vps, following the instructions on their website. Currently, I have a database with 2 users: 1) "admin", which has admin access to the database 2) "user1", which can only read, update, write, and delete data in tables, but it cannot do other stuff (like deleting tables) My SQL to setup this db and the users looks like this (simplified):

CREATE ROLE "admin" WITH LOGIN PASSWORD 'myadminpassword' SUPERUSER;
CREATE DATABASE "mydb" WITH OWNER "admin";
CREATE ROLE "user1" WITH LOGIN PASSWORD 'myuserpassword';
GRANT CONNECT ON DATABASE "mydb" TO "user1";

I then connect to the db "mydb" with "admin" and run the following SQL:

REVOKE ALL PRIVILEGES ON SCHEMA public FROM "user1";
GRANT USAGE ON SCHEMA public TO "user1";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "user1";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "user1";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "user1";

Now, in the authlist.txt file from pgBouncer, I have to add a user and an md5-hash of the password of the user of the postgres db. And I'm not sure which user I have to add. I use the "admin" user when I'm managing the database (I connect to the db using pgAdmin) like adding or deleting tables, columns, ..., but my application uses the "user1" user. Which user should I add to pgBouncer (I'm guessing "user1"). Second question: is my connection string (postgres://user1:myuserpassword@:5432/mydb) still the same? Or do I need to use another connection string to connect to pgBouncer? Thanks in advance! Edit: please note that my postgresql server is a separate vps than the vps which is running the nodejs application. I'm planning to install pgBouncer on the postgresql vps. Edit 2: I installed pgBouncer with the help of the docs and this tutorial . I HAVE NOT TESTED IT YET! /etc/pgbouncer/pgbouncer.ini:

[databases]
mydb host=localhost port=5432 dbname=mydb

[pgbouncer]
...
pool_mode = transaction
max_client_conn = 1000
auth_type = hba
auth_file = /etc/pgbouncer/pb_hba.conf
admin_users = admin
...

/etc/pgbouncer/pb_hba.conf

host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
host all all /32 scram-sha-256

I've added my application vps ip to the allowed ip's in pgBouncer's pb_hba.conf file so only this vps can connect to it. Edit 3: I got it working, my pg_hba.conf file for pgbouncer looks like this:

host all all /32 scram-sha-256
local all all scram-sha-256

I added the necessary credentials to userlist.txt

We have setup a streaming replication on Postgres 13 and it all works fine. Current setup is as follows. We have one Primary Postgres and one Secondary Postgres connected via streaming replication. This replication and cluster management of failover is managed by Patroni. We have PGBouncer for connection pooling and it is currently connected to PG Master. This works fine without issues. We wanted to use the secondary postgres for read purposes to reduce the read load on Primary. So we wanted to introduce PGPool in the eco system to get benefit of read/write splits. Couple of articles suggested that we could use both PGBouncer and PgPool together to get better results from both tools (https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/) . So now our setup adds PgPool-II between PgBouncer and PG with connection pooling disabled. This setup also works fine. However I am not able to understand about connection pooling. I could see many connections in pgpool when i do SHOW POOL_POOLS; - Who is holding on to the connections for connection pooling ? PgBouncer/PgPool ? - Why I see connections when I issue command show pool_pools in pgpool when connection pooling is disabled. - Is this setup right and scalable? What configurations I need to change to get better of both tools ?

Does anyone knows what this error below means? We running pgbouncer 1.21 on centos using transaction pooling mode. 2024-04-29 15:00:02.378 CST WARNING S-0x197b028: db/user_a@10.0.0.1:5432 got packet 'E' from server when not linked 2024-04-29 15:00:02.379 CST LOG S-0x197b028: db/user_a@10.0.0.1:5432 closing because: server conn crashed? (age=2409s) We faced this warning after enabling min_pool_size.

We are using authentication file mode in pgbouncer. The authentication part of Pgbouncer is so confusing. Would be helpful if anybody can throw some light on my below queries. - Are Pgbouncer connections **authenticated twice** at bouncer side and DB layer? - What's the use of authentication at Pgbouncer if it is using the **same credentials as database**? - Is it possible to use a **different set of user credentials** for the Pgbouncer authentication other than DB credentials itself?