pg-index-health is an embeddable schema linter for PostgreSQL that detects common anti-patterns and promotes best practices.

What is this?

pg-index-health is a Java library designed to analyze PostgreSQL database schemas and help developers build efficient, reliable applications.

It scans database structures to identify common schema-level pitfalls that often go unnoticed until they cause problems in production.

By embedding pg-index-health into your CI/CD pipeline, you can proactively catch schema design flaws, enforce consistency, and adhere to PostgreSQL best practices before they impact performance or reliability.

Available checks

A list of all available checks (rules/diagnostics) can be found here.

Installation

Using Gradle:

implementation 'io.github.mfvanek:pg-index-health:0.20.1'

with Kotlin DSL

implementation("io.github.mfvanek:pg-index-health:0.20.1")

Using Maven:

<dependency>
  <groupId>io.github.mfvanek</groupId>
  <artifactId>pg-index-health</artifactId>
  <version>0.20.1</version>
</dependency>

How to use

There are two main scenarios of using pg-index-health in your projects:

• unit\functional testing (see standard test in section below) locally and in CI for compliance of the database structure with best practices;
• collecting indexes, tables and sequences health data and bloat monitoring in runtime on production.

All these cases are covered with examples in the pg-index-health-demo project.

Integration with Spring Boot

There is a Spring Boot starter pg-index-health-test-starter for unit/integration testing as well.
More examples you can find in pg-index-health-demo project.

Starter installation

Using Gradle:

testImplementation 'io.github.mfvanek:pg-index-health-test-starter:0.20.1'

with Kotlin DSL

testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.20.1")

Using Maven:

<dependency>
    <groupId>io.github.mfvanek</groupId>
    <artifactId>pg-index-health-test-starter</artifactId>
    <version>0.20.1</version>
    <scope>test</scope>
</dependency>

Standard test

Add a standard test to your project as shown below. Ideally, all checks should pass and return an empty result.

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import io.github.mfvanek.pg.model.predicates.SkipByColumnNamePredicate;
import io.github.mfvanek.pg.model.predicates.SkipFlywayTablesPredicate;
import io.github.mfvanek.pg.model.predicates.SkipIndexesByNamePredicate;
import io.github.mfvanek.pg.model.predicates.SkipLiquibaseTablesPredicate;
import io.github.mfvanek.pg.model.predicates.SkipTablesByNamePredicate;
import org.jspecify.annotations.NonNull;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;
import java.util.function.Predicate;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends @NonNull DbObject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        // Predicates allow you to customize the list of permanent exceptions for your project.
        // Just filter out the database objects that you definitely don't want to see in the check results.
        // Do not use predicates for temporary exceptions or recording deviations in the database structure.
        final Predicate<DbObject> exclusions = SkipLiquibaseTablesPredicate.ofDefault()
            .and(SkipFlywayTablesPredicate.ofDefault()) // if you use Flyway
            .and(SkipTablesByNamePredicate.ofName("my_awesome_table"))
            .and(SkipIndexesByNamePredicate.ofName("my_awesome_index"))
            .and(SkipByColumnNamePredicate.ofName("id"));

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c ->
                assertThat(c.check(exclusions))
                    .as(c.getDiagnostic().name())
                    .isEmpty());
    }
}

with Kotlin

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost
import io.github.mfvanek.pg.core.checks.common.Diagnostic
import io.github.mfvanek.pg.model.dbobject.DbObject
import io.github.mfvanek.pg.model.predicates.SkipByColumnNamePredicate
import io.github.mfvanek.pg.model.predicates.SkipFlywayTablesPredicate
import io.github.mfvanek.pg.model.predicates.SkipIndexesByNamePredicate
import io.github.mfvanek.pg.model.predicates.SkipLiquibaseTablesPredicate
import io.github.mfvanek.pg.model.predicates.SkipTablesByNamePredicate
import org.assertj.core.api.Assertions.assertThat
import org.junit.jupiter.api.Test
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.ActiveProfiles

@SpringBootTest
@ActiveProfiles("test")
internal class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private lateinit var checks: List<DatabaseCheckOnHost<out DbObject>>

    @Test
    fun checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.entries.toTypedArray())

        // Predicates allow you to customize the list of permanent exceptions for your project.
        // Just filter out the database objects that you definitely don't want to see in the check results.
        // Do not use predicates for temporary exceptions or recording deviations in the database structure.
        val exclusions = SkipLiquibaseTablesPredicate.ofDefault()
            .and(SkipFlywayTablesPredicate.ofDefault()) // if you use Flyway
            .and(SkipTablesByNamePredicate.ofName("my_awesome_table"))
            .and(SkipIndexesByNamePredicate.ofName("my_awesome_index"))
            .and(SkipByColumnNamePredicate.ofName("id"))

        checks
            .filter { it.isStatic }
            .forEach {
                assertThat(it.check(exclusions))
                    .`as`(it.diagnostic.name)
                    .isEmpty()
            }
    }
}

Recording deviations in the database structure

All deviations in the database structure found during checks can be recorded in the code and fixed later.

with Java

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.column.Column;
import io.github.mfvanek.pg.model.column.ColumnWithSerialType;
import io.github.mfvanek.pg.model.context.PgContext;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import io.github.mfvanek.pg.model.table.Table;
import org.assertj.core.api.ListAssert;
import org.jspecify.annotations.NonNull;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.InstanceOfAssertFactories.list;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends @NonNull DbObject>> checks;

    @Test
    void checksShouldWorkForAdditionalSchema() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        final PgContext ctx = PgContext.of("additional_schema");
        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> {
                final ListAssert<? extends DbObject> listAssert = assertThat(c.check(ctx))
                    .as(c.getDiagnostic().name());

                switch (c.getDiagnostic()) {
                    case TABLES_WITHOUT_DESCRIPTION, TABLES_NOT_LINKED_TO_OTHERS -> listAssert
                        .hasSize(1)
                        .asInstanceOf(list(Table.class))
                        .containsExactly(Table.of(ctx, "additional_table"));

                    case COLUMNS_WITHOUT_DESCRIPTION -> listAssert
                        .hasSize(2)
                        .asInstanceOf(list(Column.class))
                        .containsExactly(
                            Column.ofNotNull(ctx, "additional_table", "id"),
                            Column.ofNotNull(ctx, "additional_table", "name")
                        );

                    case PRIMARY_KEYS_WITH_SERIAL_TYPES -> listAssert
                        .hasSize(1)
                        .asInstanceOf(list(ColumnWithSerialType.class))
                        .containsExactly(
                            ColumnWithSerialType.ofBigSerial(ctx, Column.ofNotNull(ctx, "additional_table", "id"), "additional_table_id_seq")
                        );

                    default -> listAssert.isEmpty();
                }
            });
    }
}

Spring Boot compatibility

Spring Boot	Min JDK	pg-index-health-test-starter
2.4.x	8	0.3.x — 0.4.x
2.5.x	8	0.5.x — 0.6.x
2.6.x	8	0.7.x
2.7.x	11	0.8.x — 0.15.x
3.3.x	17	0.20.x

Spring Boot 3 compatibility

• Starting from 0.9.0.1 added support for Spring Boot 3.0
• Starting from 0.10.2 added support for Spring Boot 3.2

Supported PostgreSQL versions

Support for previous versions of PostgreSQL

Compatibility with PostgreSQL versions 10, 11 and 12 is no longer guaranteed, but it is very likely.
We focus only on the currently maintained versions of PostgreSQL.
For more information please see PostgreSQL Versioning Policy.

Supported Java versions

Supports Java 17 and above.

• For Java 11 compatible version take a look at release 0.15.0 and lower.
• For Java 8 compatible version take a look at release 0.7.0 and lower.

Articles and publications

In English

• pg-index-health – a static analysis tool for you PostgreSQL database

In Russian

• pg-index-health – static analysis of the structure of PostgreSQL databases
• Index health in PostgreSQL through the eyes of a Java developer
• DBA: finding useless indexes
• The series of articles "Static analysis of the database structure"

Questions, issues, feature requests and contributions

• If you have any question or a problem with the library, please file an issue.
• Contributions are always welcome! Please see contributing guide for more details.
• We utilize Testcontainers for testing pg-index-health. So you need to have Docker installed on your machine.

Similar solutions

• PostgreSQL Wiki - Don't Do This — a short list of common mistakes.
• db_verifier — scripts to check the database structure for errors or non-recommended practices.
• SchemaCrawler Linter — a free database schema linter.
• Squawk — a linter for Postgres migrations.
• schemalint — a Postgres databases schema linter.
• index-digest — a database linter for MySQL and MariaDB.
• Azimutt — a linter for database.