Enable PgBouncer for Production

[darunrs]

• Create a Confluence guide to troubleshoot and manage pgBouncer parameters: connection timeout, default pool size, max connection size and how to specify this per user
• Remove the local pooler from the runner
• Deploy to production

[darunrs]

What has been done:

• Deploy code preparing Runner for using pgBouncer.
• Create Infra PR to start up pgBouncer on Runner startup.
• Create pgbouncer role as admin role with password pgbouncer
• Create security definer function as pgbouncer role
  • I got access errors when I created function as admin role. Maybe if I also added admin with access to pg_shadow, this would work too? Haven't tested this yet.
• Add cloudsql.pg_shadow_select_role to postgres instance for pgbouncer role
• Add postgres url to pgbouncer startup script

What needs to be done:

• Configuring pgBouncer to work properly
• Replace pgBouncer role password with a proper password
• Configure startup script to somehow read the secret value
• Replicating manual steps in prod

[eduohe]

I did some benchmarks and here is the doc with details pgBouncer on QueryAPI.

TLDR; pgBouncer not only solves the max connection issue, but we can see improvements in performance (TPS - transactions per second)