Connection Pool (HikariCP) and Flyway Migrations

Two things every real project needs: a tuned connection pool, and versioned database migrations instead of letting Hibernate change your schema.

HikariCP: the default connection pool

Opening a database connection is slow. A pool keeps a fixed number of open connections and lends them to your code. Spring Boot uses HikariCP by default.

spring.datasource.url=jdbc:postgresql://localhost:5432/shop
spring.datasource.username=app
spring.datasource.password=${DB_PASSWORD}

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000

• A bigger pool is not always faster. A small pool (10 to 20) is enough for most services.
• Do not make the pool larger than the database can handle, and remember that every application instance has its own pool.
• If you see 'Connection is not available, request timed out', look for slow queries or long transactions before you increase the pool.

Flyway: version control for your database

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<!-- Flyway 10+ needs a database module, for example for PostgreSQL: -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

src/main/resources/db/migration/
    V1__create_products.sql
    V2__add_category_column.sql
    V3__insert_default_data.sql
Naming: V<version>__<description>.sql   (two underscores)

-- V1__create_products.sql
CREATE TABLE products (
    id       BIGSERIAL PRIMARY KEY,
    name     VARCHAR(100)  NOT NULL,
    price    NUMERIC(10,2) NOT NULL
);

-- V2__add_category_column.sql
ALTER TABLE products ADD COLUMN category VARCHAR(50);

spring.flyway.enabled=true
spring.flyway.baseline-on-migrate=true      # for an existing database
spring.jpa.hibernate.ddl-auto=validate      # Hibernate only checks, Flyway changes

At startup Flyway checks the table flyway_schema_history, runs every new migration in order, and stores a checksum for each. Rules:

• Never edit a migration that was already applied. Flyway detects the changed checksum and fails. Create a new version instead.
• Keep migrations small and repeatable on every environment.
• Use R__name.sql for repeatable migrations such as views.

H2 console for local development

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb
# open http://localhost:8080/h2-console

How HikariCP works inside

HikariPool
  ├─ ConcurrentBag of PoolEntry (connection + state): NOT_IN_USE / IN_USE / REMOVED / RESERVED   (lock-free, thread-local fast path)
  ├─ housekeeper thread every 30 s:  retire connections older than maxLifetime (with random variance), keep minimumIdle, detect clock jumps
  └─ addConnectionExecutor thread: creates new connections in the background

getConnection():  1. take an idle connection from the bag  2. none? wait up to connectionTimeout (30 s), while creating one if below maximumPoolSize
                  3. timeout ─► SQLTransientConnectionException: Connection is not available, request timed out after 30000ms
close() on the returned proxy does NOT close the socket. It returns the connection to the pool.

Pool size: the formula and the trap

• Rule of thumb from the HikariCP wiki: connections = (core_count x 2) + effective_spindle_count. For a 4 core database server with SSD this gives about 9 to 10.
• The pool must be small because the DATABASE can only do useful work on a few queries at once. More connections mean more context switching, not more speed.
• Total connections = pool size x number of application instances. Ten instances with pool 20 open 200 connections. Check max_connections of the database (PostgreSQL default 100).
• If threads wait for connections, first find long transactions and slow queries. Bigger pools hide the problem and can overload the database.

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=10                       # fixed size pool (Hikari recommends min = max for steady load)
spring.datasource.hikari.leak-detection-threshold=20000        # log a stack trace if a connection is held longer than 20 s
spring.datasource.hikari.max-lifetime=1740000                  # 29 min: shorter than the database or proxy idle timeout (often 30 min)
spring.datasource.hikari.pool-name=shop-pool
spring.datasource.hikari.auto-commit=true
management.metrics.enable.hikaricp=true                        # hikaricp.connections.active / pending / timeout metrics

Two databases in one application

@Configuration
public class DataSourceConfig {

    @Bean @Primary
    @ConfigurationProperties("app.datasource.orders")
    public DataSourceProperties ordersProps() { return new DataSourceProperties(); }

    @Bean @Primary
    public DataSource ordersDataSource(@Qualifier("ordersProps") DataSourceProperties props) {
        return props.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("app.datasource.reports")
    public DataSourceProperties reportsProps() { return new DataSourceProperties(); }

    @Bean
    public DataSource reportsDataSource(@Qualifier("reportsProps") DataSourceProperties props) {
        return props.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean
    public JdbcTemplate reportsJdbc(@Qualifier("reportsDataSource") DataSource ds) { return new JdbcTemplate(ds); }
}
// For JPA on both, define an EntityManagerFactory and a TransactionManager per database with @EnableJpaRepositories(basePackages, entityManagerFactoryRef, transactionManagerRef).

Flyway workflow at startup

FlywayAutoConfiguration runs BEFORE the JPA EntityManagerFactory is created
  1. connect, acquire a lock (so two instances do not migrate at the same time)
  2. create table flyway_schema_history if missing
        columns: installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success
  3. scan locations (classpath:db/migration) ─► list of migrations V1, V2, V3 ... and R__ repeatables
  4. validate:  checksum of every APPLIED migration must equal the file. Changed file ─► FlywayValidateException (startup fails)
  5. run the pending versions in order, each in its own transaction (PostgreSQL supports transactional DDL, MySQL does not)
  6. record each result in flyway_schema_history
  7. Hibernate starts and (with ddl-auto=validate) checks that entities match the new schema

-- V3__add_orders_customer_index.sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id ON orders (customer_id);
-- CONCURRENTLY cannot run in a transaction: add   -- flyway:executeInTransaction=false   style config (executeInTransaction=false in a .conf file) for this file

spring.flyway.locations=classpath:db/migration,classpath:db/migration/{vendor}   # {vendor} = postgresql, mysql ... for special SQL
spring.flyway.validate-on-migrate=true
spring.flyway.clean-disabled=true            # NEVER allow clean (drops everything) outside local development
spring.flyway.out-of-order=false
spring.flyway.baseline-on-migrate=true
spring.flyway.baseline-version=1

Zero-downtime migrations: expand and contract

Goal: rename column  customer_name -> full_name  while old and new application versions run together (rolling update)
 Release 1 (expand):   ADD COLUMN full_name;  app writes BOTH columns, reads customer_name
 Migration:            backfill:  UPDATE ... SET full_name = customer_name WHERE full_name IS NULL   (in batches)
 Release 2:            app reads full_name (still writes both)
 Release 3 (contract): stop writing customer_name;   Migration: DROP COLUMN customer_name
Rules: only backward compatible steps per release. Never rename or drop in the same release that stops using the column.

• Flyway commands for problems: flyway repair (fix the history after removing a failed migration or changing a checksum on purpose), flyway info, flyway baseline.
• Test migrations against a real database of the same type in CI (Testcontainers), starting from an empty database AND from a copy of production structure.
• Keep DDL and data fixes in separate migrations, and make long data migrations batch based to avoid long locks.

Common mistake: Editing an old Flyway migration file after it ran on a database. The checksum no longer matches and startup fails. Always add a new versioned migration.

Interview questions

What is the default pool in Spring Boot?

HikariCP, chosen because it is small and very fast.

Flyway or Liquibase?

Both work well. Flyway uses plain SQL files and is simpler. Liquibase uses XML, YAML or SQL changelogs with more features like rollback.

Why not use ddl-auto=update?

It cannot handle renames, data changes or rollbacks safely and gives no history. Migrations are repeatable and reviewable.

What does 'Connection is not available, request timed out' mean?

All connections are in use and a thread waited connectionTimeout without getting one. Look for slow queries, long transactions, connection leaks (use leak-detection-threshold) or a pool that is too small for the real concurrency.

Why does Flyway fail with 'Validate failed: Migration checksum mismatch'?

An already applied migration file was changed. Restore the original file and add a new migration, or use flyway repair only when the change was intentional and safe.