Pgx Pool + Pgbouncer with multiple read dbs

[shabemdadi]

We have a Pgbouncer that provides connection pooling to a master db as well as several read replicas

We currently have a python application, that implements pooling logic to route certain requests to either the read replicas or the primary.

We want to move this logic to a golang application, using pgx

From glancing at various forums (like here #970) - it looks like we can (and should) use pgx pool + pgbouncer, but is there a recommended way for how we can ensure that certain requests go to the read replicas vs others going to the primary? Do we have to create a pool per db, then have some application logic to round robin across the read pools for example to select one? Or is there something more "built in" to use?

[jackc]

There is no builtin mechanism to direct queries to specific servers.

[shabemdadi]

Got it thanks - and with respect to the usage of pgx pool + pgbouncer

For each request coming into our service, should we be acquiring a connection from the pool and then running the query/exec on that? or doing the query/exec on the pool itself

Basically do we do this:

rows, err := thisPool.Query(...)

vs

		conn, err := thisPool.Acquire(context.Background())
		rows, err := conn.Query(...)

[jackc]

If it is a single query or you are running multiple queries in a single transaction, then run it directly on the pool.

Typically, you would only manually acquire a connection if you have multiple queries that need to run on the same connection that are not in the same transaction.