Connection pool timeout

[saurabh-zededa]

We have 15 services in our system and we are planning to migrate the DB layer from non-relational database to relation database (PostgreSQL).
We started migration with a read and write heavy service(let's call it service1) which does thousands of reads and writes per minute.
On our dev env we are running PostgreSQL with other bunch of services on a machine with 8 cpus and 32GB memory.
Right now only service1 is using PostgreSQL.
We have kept the default pool size and that is 10, with 8 cpus maximum connection in the pool can be 80.

Service1 has to read and write thousands of data per minute and while doing that queries are timing out with this error: pg: connection pool timeout.

Pool stat: Connection pool stat for service1 : Hits: 1359475, Misses: 158, Timeouts: 114563, TotalConns: 80, IdleConns: 1, StaleConns: 78

From pool stat it is clear that service1 is running out of connections.

-We are not using transaction or statement.
-Our queries are not long running, they are very simple read, update, insert and delete.
-No join queries.

Sample code that we are using:

db := pg.Connect(&pg.Options{
                Addr:            postgresConfig.Address,
                User:            postgresConfig.User,
                Password:        postgresConfig.Password,
                Database:        postgresConfig.Database,
                ApplicationName: postgresConfig.MicroserviceName,
                OnConnect: func(ctx context.Context, conn *pg.Conn) error {
                        _, err := conn.Exec("set search_path=?", postgresConfig.SchemaName)
                        if err != nil {
                                return fmt.Errorf(err.Error())
                        }
                        return nil
                },
        })
         
         We are using :
         Model method for all CRUD operation `func (db DB) Model(model ...interface{}) *Query`
         Model returns Query object and that implements all the underlying methods used for CRUD (Insert(), Select(), Update() and Delete())

     I looked at the go-pg code and connections are released once the query ends. 

Questions:

1. Why service1 is running out of connections ? with the amount of load it is handling does it need more connections/resource or number of connections is sufficient and I am missing something?

2. By default PostgreSQL supports 100 connections, in my case all of my services will connect with the PostgreSQL container so 100 connections will not be sufficient given that just one service is using 80 and that too is not sufficient for it. So up to how many connections is it safe to keep in the connection pool ? (of course we need to keep it in mind that as the number of connections increases memory usage also increases)

Please help me with this, Thanks 🙏

[vmihailenco]

with the amount of load it is handling does it need more connections/resource or number of connections is sufficient and I am missing something?

Have you checked disk / CPU utilization? iostat is pretty good at it. Disk util should be < 70%.

The only thing in go-pg that is worth checking is that you properly close transactions and statements (you say that you don't use them but still). Also Listener can be a problem, but I guess you are not using it.

So up to how many connections is it safe to keep in the connection pool ?

Make sure you are using PgBouncer / other connection pool. It is an easy but usually small win. default_pool_size should be 2 * num_of_cpus or less.

With PgBouncer you can have as many connections in go-pg pool as you want, but it almost never matters.

[saurabh-zededa]

Thanks @vmihailenco for quick response.
I do see issue with memory utilization. I will debug memory issue further.

I do not use transaction, statements or Listener.
I will try PgBouncer and share my result.

Thanks

[saurabh-zededa]

@vmihailenco Connection pooling using PgBouncer solved my issue.

Since go-pg library is also handling connection pool, then why my application is running out of connections when I am not using PgBouncer?

Why go-pg's connection pooling is not sufficient ?

[vmihailenco]

go-pg pool only improves client performance and doesn't / can't replace PgBouncer.

[ohmpatel1997]

whats that pgBouncer offers that go-pg is not offering?