Beware of making database queries in Goroutines
The past couple days I've been struggling to patch an issue in a client's codebase wherein PostgreSQL is reporting the following repeatedly in my error tracker:
pq: sorry, too many clients already
pq: remaining connection slots are reserved for non-replication superuser connections
In an earlier post, I
hypothesized that perhaps I wasn't closing connections I'd opened using
db.Query
. While I did find some instances of this, I found that the actual
culprit was opening database connections inside of Goroutines created and run in
a for
loop:
for _, user := range users {
go doStuff(user)
}
func doStuff(user User) {
rows, err := db.Query("SELECT * FROM cars where user_id=$1;", user.Id)
defer rows.Close()
}
The above example would work just fine if not for running doStuff
in
concurrent Goroutines. PostgreSQL would execute the queries in series, closing
the previous connection before opening a new one. But when we tell Go to execute
them in parallel, open connections pile up and bad things happen.
So: If Postgres is complaining that you've got too many concurrent connections, think about the architecture of your application. Is there some place where you might be trying to execute queries in parallel? Is there any way you can execute the queries in series? Or perhaps complete your queries ahead of the concurrent processing?
If you've struggled with having too many concurrent open connections in your Go application, I'd love to hear how you overcame the problem.