Use PgBouncer for centralized database connection pooling

[nscuro]

As we have multiple services accessing a single PostgreSQL database, it doesn't make sense that every instance maintains its own database connection pool. The database server can only handle so many concurrent connections, we'll likely need a way to serialize access to avoid excessive contention.

The go-to solution for this is PgBouncer, which can be deployed "in front of" PostgreSQL, to handle connection pooling for multiple applications.

I did some initial testing, but ran into very poor performance. Highly likely I did something wrong, so needs more research.

---

What I did so far is adding bitnami/pgbouncer to docker-compose.yml:

pgbouncer:
  image: bitnami/pgbouncer:latest
  container_name: dt-pgbouncer
  depends_on:
  - postgres
  environment:
    POSTGRESQL_HOST: "dt-postgres"
    POSTGRESQL_PORT: "5432"
    POSTGRESQL_USERNAME: "dtrack"
    POSTGRESQL_PASSWORD: "dtrack"
    POSTGRESQL_DATABASE: "dtrack"
    PGBOUNCER_DATABASE: "dtrack"
    PGBOUNCER_PORT: "6432"
    # PGBOUNCER_DEFAULT_POOL_SIZE: "30"
    PGBOUNCER_IGNORE_STARTUP_PARAMETERS: "extra_float_digits"
  restart: unless-stopped

And replacing the JDBC URLs of all services to point to PgBouncer instead of PostgreSQL directly:

jdbc:postgresql://dt-pgbouncer:6432/dtrack

Further, for the API server, I disabled application-side connection pooling with:

ALPINE_DATABASE_POOL_ENABLED: "false"

And for Quarkus-based services with:

QUARKUS_DATASOURCE_JDBC_POOLING_ENABLED: "false"

Once running, connecting to PgBouncer can be done like this:

$ docker exec -it dt-pgbouncer psql -p6432 -Udtrack pgbouncer
Password for user dtrack: dtrack

It is then possible to issue PgBouncer commands:

pgbouncer=# SHOW DATABASES;
   name    |    host     | port | database  | force_user | pool_size | min_pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled
-----------+-------------+------+-----------+------------+-----------+---------------+--------------+-----------+-----------------+---------------------+--------+----------
 dtrack    | dt-postgres | 5432 | dtrack    |            |        30 |             0 |            0 |           |               0 |                  21 |      0 |        0
 pgbouncer |             | 6432 | pgbouncer | pgbouncer  |         2 |             0 |            0 | statement |               0 |                   0 |      0 |        0
(2 rows)

pgbouncer=# SHOW STATS;
 database  | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time
-----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
 dtrack    |           152158 |            306275 |      135167308 |  124634411 |      9452081690 |       7480151386 |          921049 |            189 |             305 |    62508 |   117654 |         51561 |          27930 |             4
 pgbouncer |                2 |                 2 |              0 |          0 |               0 |                0 |               0 |              0 |               0 |        0 |        0 |             0 |              0 |             0
(2 rows)

With this setup, everything "works", but gets really slow under load, e.g. when uploading lots of BOMs.

[nscuro]

Alternative connection poolers we can experiment with:

• odyssey
• pgagroal
• pgcat
• pgpool

[nscuro]

PgBouncer 1.21.0 was just released which includes support for prepared statements. The missing PS support could explain the bad performance when I originally tested the setup.