Postgresql – What makes the database pool concurrent-safe?

Are below reasons adequate to support the aforementioned statement?

1. True if properly configured. You can set up a pool with a insufficient amount of connections in it. Once it’s exhausted, further requests can be set up to fail or to wait – thus, connections will no longer be ready to be consumed when requested.
2. True if it’s the only source of connections.
3. True as far as the pool is concerned. You can use connections acquired from the pool in a non-thread-safe manner. A connection from a pool and a connection set up with pgx.Connect() are just the same – if you give the same one for multiple threads to share, they can intefere.

3(thread safety) can be built in a regular connection as well.

Thread safety mentioned in the "Getting started" section likely refers to what happens if multiple operators share the same, single connection. You can’t build in reliable safeties against these, without effectively ending up re-implementing session isolation that’s already offered. Examples include:

1. Worker A begins a transaction. At any point of A’s work, worker B can commit, rollback or cause the connection to enter an undefined state before A finishes. Also, until the transaction is committed, no changes from any worker sharing the connection are visible to anyone else, not sharing the connection.
2. Worker A sets a session or transaction level setting. Worker B is accidentally affected by it and is able to suddenly unset or overwrite it at any time.
3. Worker A creates a temp object. Operating in the same connection, worker B shares the same pg_temp namespace, so any attempt to perform a similar operation causes unintended interaction with A’s temp object on the db.
4. Worker A issues a long-running query. From worker B’s perspective, the db is unresponsive – the connection waits for results requested by A.

Any session-level thing becomes a potential friction point between workers. That being said, you can come up with workflows that use none of the thread-unsafe facilities of a session and find the random latencies introduced by 4 above, acceptable. It’d be still safer and more sustainable to use regular, thread-safe setup for those.

Are there any other currency reasons why we should be using pool instead of creating objects on the fly?

Thread safety in this context is guaranteed by letting each worker use their own session/connection. You can make each worker always begin by setting up their own instead of requesting a fresh, off-the-shelf, stock connection, but that’s just slower – the pool does it independently ahead of time, saving everyone time establishing, configuring and later disposing of them.

dbpool.QueryRow() passes your query to a clean connection fresh from the pool, runs it and immediately releases the connection back to it. Two concurrent threads can share a pool and their queries are guaranteed to end up in different connections, and not interfere. You can’t use it for anything relying on a transaction or something discardable. For that you need to dbpool.Acquire(), hold on to the acquired connection, re-use it to run the entire query chain on it, then conn.Release() back to the pool.