search

jackc / pgx

PostgreSQL driver and toolkit for Go
MIT License
9.95k stars 808 forks source link

pgxpool: different MaxConnLifetime for fallback hosts #2073

Open redbaron opened 1 week ago

redbaron commented 1 week ago

Is your feature request related to a problem? Please describe.

Multihost (HA) fallbacks might not be an equal option to make a connection to. When used as a true fallbacks, app might prefer to use them only as long as "primary" host remains unavailable. One way to achieve it is to lower MaxConnLifetime for connections established to fallback hosts: they will be closed more often and reconnection attempt will try primary host first.

Describe the solution you'd like

Minimal implementation would be to introduce pool_fallback_max_conn_lifetime (as well as the rest of pool configs probably) and then wire it up to be respected by pgxpool. Because fallbacks are at the connection level, pool might need a way to inspect which host was actually used to establish connection if it is not available already.

Alternative implementations

Probably something can be cobbled up together with pgxpool.Config.AfterConnect and AfterRelease to implement tracking which connections was established to which hosts (assuming host info is available somewhere in the Conn) and then returning false from AfterRelease for connections to fallback which exceeded lifetime intended for fallback connections. It is not pretty and requires reimplementing connection age tracking done by pgxpool already.

jackc commented 1 week ago

I'm not sure that baking custom fallback handling directly into pgx/pgxpool is a good idea. However, I would be more open to adding features that make it more convenient for the applications to implement exactly the behavior they want.

I think AfterRelease (and possibly AfterConnect) actually is the right approach. One newer feature that could help with this is https://pkg.go.dev/github.com/jackc/pgx/v5@v5.6.0/pgconn#PgConn.CustomData. With that you can associate arbitrary values with a connection. I would expect it to be less than 10 lines of code overall to have an AfterConnect that stored the current time in CustomData and a AfterRelease that checked the age.

Beyond that, one thing I've thought of is each layer above *pgconn.PgConn storing a reference to itself in CustomData. That is, a *pgconn.PgConn would have a reference to the *pgx.Conn and the *pgxpool.Conn. I haven't implemented it yet, there are few subtleties regarding what should happen when a connection is Hijacked.

Possible, some of the internal statistics for a pgxpool.Conn could be made public as well.