Best practices for managing pgxpool.Conn with net/http.Handler

[hmoazzem]

The Postgres middleware acquires a connection from default pool for each (authorized) http request and attaches the connection to the request context

conn, err := defaultPool.Acquire(r.Context())
// ... ...
ctx = context.WithValue(ctx, pgo.PgConnCtxKey, conn)
ctx = context.WithValue(ctx, pgo.PgRoleCtxKey, pgRole)

In the request context, the middleware also sets Postgres role using which queries on Postgres are performed. The role is derived from authorization methods:

• extracted from the OIDC token
• BasicAuth username
• default anonymous user (for public/unauthenticated requests)

What are the performance considerations of attaching pgxpool.Conn in EACH request context?

Then the http handlers obtain connection with ConnWithRole()

pgo/postgres.go

Line 49 in d4c7870

func ConnWithRole(r *http.Request) (*oidc.IntrospectionResponse, *pgxpool.Conn, *pgconn.PgError) {

This may not be optimal, because ConnWithRole() performs separate database actions to set role etc.

setRoleQuery := fmt.Sprintf("SET ROLE %s;", role)
setReqClaimsQuery := fmt.Sprintf("SET request.oidc.claims TO '%s';", escapedClaimsJSON)
combinedQuery := setRoleQuery + setReqClaimsQuery
_, execErr := conn.Exec(context.Background(), combinedQuery)

Can we batch this exec action together with (to be executed just before) the actual query?

Finally, pgxutil helpers in pgo by default releases the connection with

defer conn.Release()

it can be prevented by supplying the optional keepConn arg in pgo helper functions. This is helpful, for example, when you need multiple queries to process a request.

How'd we optimise connection acquisition and release?

[hmoazzem]

From @jackc himself (via email), quoted verbatim

Glad you find pgx useful!

Your pgo project looks interesting. My primary suggestion relates to managing the connections.

I would suggest considering handling the acquisition of connection, setting of role, and resetting of role as some sort of middleware or custom handler type that makes the connection available to the handler. I've done this myself both by putting the connection in the request context and making a custom handler type that directly passes the connection as an argument. I've done more magical things before, but I usually end up wanting it a little more explicit.

Right now the connection is acquired and released with every database action. I noted the keepConn argument which presumably is designed to avoid this, but I didn't actually see how a kept connection is released. As far as I can tell it is leaked. Maybe it is handled elsewhere, but, at the least, that path is not obvious. If it was handled via middleware you could guarantee that the connection wouldn't leak even in case of panic.

It would also reduce the need for custom wrappers for pgxutil or any other libraries you wanted to use.

It would make it less magical, but that's usually a good thing in Go.

[jackc]

What are the performance considerations of attaching pgxpool.Conn in EACH request context?

The context operations are very cheap. The only thing that I see could be a little more expensive than desired is that the connection is held for the entire duration of the request. If your handlers were doing other non-database things that could be undesirable.

Can we batch this exec action together with (to be executed just before) the actual query?

Yes. It would be possible. But the interface may not be as easy to use. And I'm not sure off the top of my head what the state of the connection would be if the query bundled with the set queries had an error. You'd want to be very sure that those variables were reset properly.