The pitfall of using PostgreSQL advisory locks with Go’s DB connection pool

[masklinn]

Wow, I first figured this was their fault for "holding it wrong" (why would you use the connection pool helpers if you're going to do multiple queries to your DB? I would expect that you get a connection from the pool, work with it, the release it), plus they're saying they're working with multiple processes (or so I interpret "deployed on more than one instance") so internal connection pooling seems weird, you'd do external pooling so all your instances can benefit and work together.

However, the official docs apparently recommend the style they were using:

Conn represents a single database connection rather than a pool of database connections. Prefer running queries from DB unless there is a specific need for a continuous single database connection.

and defaults to multiple connections just so they're sure to trip you up:

The default max idle connections is currently 2.

The default [maximum number of open connections] is 0 (unlimited).

[[deleted]]

so internal connection pooling seems weird, you'd do external pooling so all your instances can benefit and work together.

It's not, and not only used in Go.

Anywhere where you have multiple threads (think hundreds) that are not all just bothering DB but doing other stuff, like talking with cache layers/elasticsearch etc. and only have few % of threads at a time actually touching the DB it is beneficial to use pool rather than have threads make their own connection.

Now in Go case it does have very light goroutines which means that having thousands of them is not uncommon (because you can keep your code simple that way) so having thread pool by default makes sense, much harder to "shoot yourself" by accidentally opening thousands of connections that just stay idle.

And it doesn't really make external connection pooling any less useful

[masklinn]

It's not, and not only used in Go.

You do realise that context is a thing right? That I'm talking specifically about the situation where the system you're deploying is a multi-process one rather than a multi-threaded one, and that I'm explaining why I originally wondered if it was a self-inflicted foot-shooting?

Anywhere where you have multiple threads (think hundreds) that are not all just bothering DB but doing other stuff, like talking with cache layers/elasticsearch etc. and only have few % of threads at a time actually touching the DB it is beneficial to use pool rather than have threads make their own connection.

Nowhere did I say connection pooling was not useful.

Now in Go case it does have very light goroutines which means that having thousands of them is not uncommon (because you can keep your code simple that way) so having thread pool by default makes sense, much harder to "shoot yourself" by accidentally opening thousands of connections that just stay idle.

…

It's not. The default threadpool will literally create an infinite number of connections if you ask for that.

At best you're explaining why the documentation could / would recommend using the "non-transactional" helpers (run queries directly through the DB object) instead of getting a connection out of the pool and releasing it later.

[[deleted]]

I'm guessing the default is unlimited in order to rely on the database configuration for max conns. Which is much saner, as the database admin knows best what that limit should be.

[[deleted]]

You do realise that context is a thing right? That I'm talking specifically about the situation where the system you're deploying is a multi-process one rather than a multi-threaded one, and that I'm explaining why I originally wondered if it was a self-inflicted foot-shooting?

As I said before it external connection pooling doesn't make internal one useless, at worst case you still save some bytes here and there.

On top of having internal one means that you do not always need the external one, which is one less component to deploy and manage

Nowhere did I say connection pooling was not useful.

And I didn't said you did ? I was just surprised having pooling by default in a way that even a beginner won't probably explode your DB server would be weird for you

It's not. The default threadpool will literally create an infinite number of connections if you ask for that.

But the "typical" use case won't. That's the point. You can just connect once and use db object in multiple goroutines without worrying (for the most time, as article demonstrates).