Add support for pgbouncer to directly connect to asynchronous replica #550
Description
The Commercial Systems team has developed a data mesh that uses Trino as its main datasource. Trino can connects to several databases and provide an unified datasource for any data related operations that happens in the data mesh.
One of the databases that we want to make available through Trino is the ua-contracts postgres database. As we understand, the current performance Postgres setup will have two units: one synchronous and one asynchronous.
Given that potentially complex queries will be executed in Trino, we need to guarantee that the query execution through Trino will not affect the prod environment. For that to happen, Trino should only connect to the asynchronous replica through pgbouncer. That is because:
- If Trino connects to the synchronous replica, we might affect write performance due to complex analytical queries
- Postgres might kill the query execution if it detects that the synchronous replica is out of sync with Primary.
Due to that, we would need to configure pgbouncer to only connect to the asynchronous replica, as today it can make Trino connect to both synchronous and asynchronous replicas.