Introduce pgbouncer to improve the cost of connection overhead,
improving the idle and short-lived connections at the database server.
The pgbouncer service runs as a systemd unit listening on port 6432,
relaying connections to the postgres server over a Unix domain socket.
The configuration is done by the `configure` postgres rhizome, which
sets up the `pgbouncer.ini`. `initialize-empty-database` creates the
pgbouncer role and related items to perform authentication passthrough,
discussed below.
User authentication is handled via a passthrough mechanism, by creating
and configuring a dedicated user called `pgbouncer` as the `auth_user`.
This user uses a `SECURITY DEFINER` function to validate connections for
other users. Authentication for all users is handled in according to the
HBA configuration specified in `pg_hba.conf`. Ref: [1], [2]
In the current state, we keep most of the default settings in place,
esp. for connection pooling behaviour. As we perform more rigorous
performance testing, these settings are subject to be tweaked in future
patches.
[1]: https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/
[2]: https://www.crunchydata.com/blog/pgbouncer-scram-authentication-postgresql
d3acd72b4d