Golang

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.

If you're building a database-backed Golang application using PostgreSQL, you might come across one or both of the following errors:

pq: sorry, too many clients already

pq: remaining connection slots are reserved for non-replication superuser connections

Both of these errors are signs that you've tried opening more database connections than your PostgreSQL server can handle.

It's tempting to go into your PostgreSQL server configuration and increase the number of connections your server will accept. But that will only lead to performance problems, especially if you're running your PostgreSQL server on a smaller instance with less memory and CPU.

More likely than the database not accepting connections being the culprit is the possibility of your Golang code leaking database connections.

Wherever you open a query connection, you're responsible for deferring a Close() call on the resulting row set:

rows, err := db.Query("SELECT * FROM cars;")
defer rows.Close()

It's a good bet that somewhere, you're not closing a connection you've opened. Over time, this could result in your database connection pool being consumed by idle connections. Auditing your code for queries where you're not closing the connection afterward will help ensure your application can still connect to its database.

Depending on the size of your application, this process could take awhile. But it's a surefire way to get things moving in the right direction.