DependencyTrack
Use PgBouncer for centralized database connection pooling
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.
Alternative connection poolers we can experiment with:
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.