Performance issues with pgxpool

[Ayush1325]

Okay, so I just switched to using the pgx interface from database/sql for everything. I think database/sql has it's own default connection pool so I tried to use the pgxpool(v4) with pgx. It is just a basic program that finds all video files in a folder and all it's children. Anyway, its heavily uses goroutines. So here are the timings:

pgx with database/sql: 2 min 54 sec
pgxpool : 7 min 54 sec

The folder size is 412 GB with 1390 items. The documentation for pgxpool is pretty much non existent. So I'm currently using Exec() method for write queries and Query() for others. I'm a noob so well, it is probably my fault. Here is the link to the repo. So well, can anyone provide any suggestions or point to some sources on how to use pgx well?

[jackc]

The issue might be the connection pool size. It appears the code is starting a new Go routine for every item -- depending on the nature of your workload this could be ideal or it could be very inefficient. But regardless, pgxpool has fairly conservative default max size. I don't think database/sql has any default max size. Try setting pgxpool.Config.MaxConns to 50 or 100 and see if it makes a difference.

[kaijday]

@jackc Also having really poor performance with pgxpool.

Using just pgx a select on a table with 1 row is 60ms, but using pgxpool it is 800ms.

[jackc]

Do you have a test case I could see?

[vasilzhigilei]

I would be very interested in seeing this too!

[SkipUFO]

It's very interesting issue. Do you have more details or it is a dead issue with no reply?

[Ayush1325]

I am no longer working on the project since it was pretty much something to get more familiar with golang. In the end, it was somewhat solved by being smarter about when to start a new goroutine in my case just like @jackc mentioned.

[joakimkonig]

Is there a solution to this issue? I'm experiencing this issue right now, however, increasing MaxConns seems to have no effect on the performance. With Pgx inserts and queries take ∼0.2ms while with PgxPool anywhere from 30-60ms. Any ideas?

[jackc]

Perhaps try calling Acquire and then the query method on the conn instead of the query method directly on the pool. This will allow measuring the acquire and query time separately to more accurately determine where the delay is occurring.

Also, check out https://pkg.go.dev/github.com/jackc/pgx/v5@v5.0.0/pgxpool#Pool.Stat. That may provide additional insight.

[kienmatu]

For me also, trying to use acquire from a pool is insanely slower than a single traditional connection
My go mod:

github.com/gin-gonic/gin v1.9.0
github.com/jackc/pgx/v5 v5.3.1

I already made a benchmark with go-wrk, you can check it here