Connection pool timeout with PgBouncer

[revdaalex]

We have a service that works in conjunction with go-pg + PgBouncer.
go-pg configuration:
opt := &pg.Options{ Addr: fmt.Sprintf("%s:%s", cfg.Server, cfg.Port), User: cfg.Username, Password: cfg.Password, Database: cfg.Database, MaxRetries: cfg.MaxRetries, PoolSize: cfg.MaxOpenConns (20), MaxConnAge: cfg.MaxConnAge (90s), }
According to PgBouncer statistics, 10-11 connections are used.
After some time of service operation, it stops working with the database.
Pool statistics:
{ "Hits": 66719, "Misses": 250, "Timeouts": 29, "TotalConns": 20, "IdleConns": 0, "StaleConns": 182 }
PgBouncer stops fixing connections from the service.
The service itself gets an error: Connection pool timeout
Only restarting the service helps.
Increasing the pool size doesn't help either.
What could be the problem?

[revdaalex]

Disk usage: 25%
Cpu usage: 11%

[vmihailenco]

Please show Pgbouncer and PostgreSQL logs. At least Pgbouncer logs should have some hints.

[vmihailenco]

Overall my guess is that your app is leaking connections, for example, you don't properly close transactions or listeners.

You can debug that by logging the result of db.PoolStats() (TotalConns/IdleConns).

[revdaalex]

When we start getting connection pool timeout error - statistic looks like this:

{
	"Hits": 30370,
	"Misses": 142,
	"Timeouts": 1439,
	"TotalConns": 20,
	"IdleConns": 0,
	"StaleConns": 109
}

There are no connections from the service in Pgbouncer when its happen
No pending transactions in database.
Transactions start and close in the middleware. In case of an error or even a panic, this works.

[vmihailenco]

TotalConns says that you have 20 connections which contradicts "There are no connections from the service in Pgbouncer when its happen".

[revdaalex]

This is how the drop in connections from the service looks like when we start getting an error Connection pool timeout.
Pool statistics show 20 connections
According to the Pgbouncer charts, only connections from cron job.
In pg_stat_activity there is also no information on connections from the service.
There are also no pending transactions.

[revdaalex]

Looks like PGBouncer uses "DISCARD ALL" and stops counting it as a connection, but the library continues to count it as an active connection and thus the pull overflows. I just watched how out of 9 connections the bouncer dropped to 4 through this mechanism, and the library continued to assume that it had 9 connections, and after the arrival of new requests, it simply added new connections.

[kidoman]

So what was the solution? How did you fix this? I am planning on using go-pg with PGB and was wondering if this will become the issue.

Also, how did you disable prepared statements?