Folio DB connections usage

Submitted	Dec 9, 2025
Approved
Status	proposed
Impact	medium
Arch Ticket	https://folio-org.atlassian.net/browse/FOLIO-4381
Prod Ticket

1 Overview
- 1.1 Problem statement
2 Tools
3 Spring modules
- 3.1 Default Configuration
- 3.2 Updated configuration
4 Vert.x (RMB) Modules
5 Conclusion
- 5.1 Spring modules
- 5.2 Proposed option

Overview

Both module types share a similar operational pattern: they submit database requests through an abstraction layer that borrows connections from a pool.

Spring/HikariCP modules: These modules are built on Spring and use HikariCP for connection pooling. HikariCP should automatically manage connection lifecycles using default variables; monitoring shows that not all of the modules have set the connection pool settings properly and rely on default settings.
Vert.x modules: These modules use Vert.x’s reactive PostgreSQL client (vertx-pg-client) with configurable PoolOptions.

This investigation aims to determine whether the leak stems from a shared logic flaw in our database interaction layer, incorrect pool configuration, or framework-specific usage mistakes. Understanding the root cause in both contexts is essential to prevent escalating resource consumption and maintain reliable access to database services.

Problem statement

It was observed that the current connection pooling implementation appears to be maintaining a large number of idle connections. New connections are being added to the pool (up to the maximum pool size setting), but idle connections are not being released back after use.

Tools

docker + docker compose to deploy a module
database: postgres:16-alpine
Grafana + Prometheus for observation
Postgres Exporter: prometheuscommunity/postgres-exporter:latest with command:
mp-postgres-exporter: container_name: mod-roles-kc-postgres-exporter image: prometheuscommunity/postgres-exporter:latest networks: [ module-perf-net ] profiles: [ mod-roles-keycloak-db ] ports: - "9187:9187" environment: DATA_SOURCE_NAME: "postgresql://${PGUSER:-postgres}:${POSTGRES_PASSWORD}@${DB_HOST}:${DB_PORT}/postgres?sslmode=disable" env_file: [ env/db.env ] depends_on: mp-db: condition: service_healthy command: - '--collector.database' - '--collector.stat_statements'

Spring modules

Some of the spring modules provide extended configuration for HikariCP:

Some of the spring modules use default configuration options:

mod-roles-keycloak used to simulate load and verify connection usage

Default Configuration

URL is updated according to: https://folio-org.atlassian.net/browse/FOLIO-4265

  datasource:
    username: ${DB_USERNAME:postgres}
    password: ${DB_PASSWORD:postgres}
    url: jdbc:postgresql://${DB_HOST:postgres}:${DB_PORT:5432}/${DB_DATABASE:postgres}?ApplicationName=${spring.application.name}

  mod-roles-keycloak:
    container_name: mod-roles-keycloak
    image: local/mod-roles-keycloak
    profiles: [ mod-roles-keycloak-db ]
    networks: [ module-perf-net ]
    env_file: [ env/okapi-modules.env ]
    deploy:
      resources:
        limits:
          cpus: 1.0
          memory: 1200m
        reservations:
          cpus: 0.5
          memory: 800m
    ports:
      - ${MOD_ROLES_KEYCLOAK_OUT_PORT}:8081
      - ${MOD_ROLES_KEYCLOAK_DEBUG_PORT}:5005
      - "18081:18081"
    environment:
      SECRET_STORE_TYPE: ${SECRET_STORE_TYPE}
      SECRET_STORE_VAULT_TOKEN: ${SECRET_STORE_VAULT_TOKEN}
      SECRET_STORE_VAULT_ADDRESS: ${SECRET_STORE_VAULT_ADDRESS}
      DB_QUERYTIMEOUT: 60000
      DB_MAXPOOLSIZE: 50
      DB_CHARSET: UTF-8
      DB_HOST=mp-db
      DB_PORT: 5432
      DB_DATABASE: okapi
      DB_USERNAME: okapi_rw
      DB_PASSWORD: ${OKAPI_DB_PASSWORD}

Even if DB_MAXPOOLSIZE is set to 50, the module does not create more than 10 connections; all 10 connections are open during application startup and change state from idle to active upon load. Default parameters are taken from: https://github.com/brettwooldridge/HikariCP/blob/dev/src/main/java/com/zaxxer/hikari/HikariConfig.java#L45

Updated configuration

spring:
  # ... other configuration properties
  datasource:
    username: ${DB_USERNAME:postgres}
    password: ${DB_PASSWORD:postgres}
    url: jdbc:postgresql://${DB_HOST:postgres}:${DB_PORT:5432}/${DB_DATABASE:postgres}?ApplicationName=${spring.application.name}
    hikari:
      maximum-pool-size: ${DB_MAXPOOLSIZE:10}
      minimum-idle: ${DB_MIN_IDLE:2}
      idle-timeout: ${DB_CONNECTIONRELEASEDELAY:600000}
      max-lifetime: ${DB_MAX_LIFETIME:1800000}
  # ... other configuration properties

mod-roles-keycloak:
  container_name: mod-roles-keycloak
  image: local/mod-roles-keycloak
  profiles: [ mod-roles-keycloak-db ]
  networks: [ module-perf-net ]
  env_file: [ env/okapi-modules.env ]
  deploy:
    resources:
      limits:
        cpus: 1.0
        memory: 1200m
      reservations:
        cpus: 0.5
        memory: 800m
  ports:
    - ${MOD_ROLES_KEYCLOAK_OUT_PORT}:8081
    - ${MOD_ROLES_KEYCLOAK_DEBUG_PORT}:5005
    - "18081:18081"
  environment:
    SECRET_STORE_TYPE: ${SECRET_STORE_TYPE}
    SECRET_STORE_VAULT_TOKEN: ${SECRET_STORE_VAULT_TOKEN}
    SECRET_STORE_VAULT_ADDRESS: ${SECRET_STORE_VAULT_ADDRESS}
    DB_QUERYTIMEOUT: 60000
    DB_MAXPOOLSIZE: 50
    DB_MIN_IDLE: 2
    DB_IDLE_TIMEOUT_MS: 30000 # 30 seconds
    DB_CHARSET: UTF-8

PostgreSQL connection metrics by application name

Vert.x (RMB) Modules

mod-users used to simulate load and verify connection usage

mod-users:
  container_name: mod-users
  image: local/mod-users
  profiles: [ mod-users-db ]
  networks: [ module-perf-net ]
  env_file: [ env/okapi-modules.env ]
  deploy:
    resources:
      limits:
        cpus: 1.0
        memory: 1200m
      reservations:
        cpus: 0.5
        memory: 800m
  ports:
    - ${MOD_USERS_OUT_PORT}:8081
    - ${MOD_USERS_DEBUG_PORT}:5005
    - ${MOD_USERS_JMX_PORT}:${MOD_USERS_JMX_PORT}
  environment:
    SECRET_STORE_TYPE: ${SECRET_STORE_TYPE}
    SECRET_STORE_VAULT_TOKEN: ${SECRET_STORE_VAULT_TOKEN}
    SECRET_STORE_VAULT_ADDRESS: ${SECRET_STORE_VAULT_ADDRESS}
    DB_QUERYTIMEOUT: 60000
    DB_MAXPOOLSIZE: 50
    DB_MIN_IDLE: 2
    DB_IDLE_TIMEOUT_MS: 30000 # 30 seconds
    DB_CHARSET: UTF-8

RMB modules can exceed the value when DB_MAXPOOLSIZE is set to 50 (441,384 requests performed across 5 tenants)

Adding the env variable DB_MAXSHAREDPOOLSIZE=50 to mod-users fixes an issue:

Vert.x modules can extend the pool based on the load to the DB_MAXPOOLSIZE; When the load stops, the module closes the idle connections.

Conclusion

The idle connection release issue was traced to the Spring-based modules and resolved by introducing additional configuration options and environment variables; once applied, the idle connections were released correctly. No defects were identified in the Vert.x implementation, which continues to behave as expected.

Spring modules

HikariCP controls connections, and configuration can be set in 3 ways:

Updating the application.yml to include the following parameters:
1. spring.datasource.hikari.maximum-pool-size: ${DB_MAXPOOLSIZE}
  The property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections.
2. spring.datasource.hikari.minimum-idle: ${DB_MIN_IDLE}
  The property controls the minimum number of idle connections that HikariCP tries to maintain in the pool, including both idle and in-use connections.
3. spring.datasource.hikari.idle-timeout: ${DB_CONNECTIONRELEASEDELAY:600000}
  This property controls the maximum amount of time (in milliseconds) that a connection is allowed to sit idle in the pool.
4. spring.datasource.hikari.max-lifetime: ${DB_MAX_LIFETIME:1800000}
  This property controls the maximum lifetime of a connection in the pool.
Updating the Folio Spring Base to use the same environment variables as Vert.x modules: https://github.com/folio-org/raml-module-builder?tab=readme-ov-file#environment-variables: https://github.com/folio-org/folio-spring-support/pull/292
Set the same properties using JAVA_OPTIONS values:
1. -Dspring.datasource.hikari.maximum-pool-size
2. -Dspring.datasource.hikari.minimum-idle
3. -Dspring.datasource.hikari.idle-timeout
4. -Dspring.datasource.hikari.max-lifetime
Set the same properties using environment variables:
1. SPRING_DATASOURCE_HIKARI_MAXIMUM_POOL_SIZE
2. SPRING_DATASOURCE_HIKARI_MINIMUM_IDLE
3. SPRING_DATASOURCE_HIKARI_IDLE_TIMEOUT
4. SPRING_DATASOURCE_HIKARI_MAX_LIFETIME

Option #1 provides more control for the teams to specify the default values and maintain them in the future.

Option #2 is more reliable in terms of maintainability - all modules will inherit configuration from folio-spring-base with predefined default values.

Options #3 or #4 are better suited for DevOps; these options will be different for Vert.x (RMB), Spring, Okapi, and other modules without uniformity in names.

Proposed option

Adopting the configuration updates in folio-spring-base offers the most maintainable, team-friendly solution. It centralizes settings in one place—much like RMB—reusing the same environment variables to streamline upkeep. Many of these properties are already present in existing deployments (e.g., eureka-snapshot, bugfest), so the transition should be straightforward.