pg-index-health is a Java library for analyzing and maintaining indexes and tables health in PostgreSQL databases.

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 11 and above
For Java 8 compatible version take a look at release 0.7.0 and lower

Available checks

All checks can be divided into 2 groups:

1. Runtime checks (those that make sense to perform only on a production database with real data and statistics).
   Runtime checks usually require aggregating data from all nodes in the cluster. This necessitated creating our own abstraction over the database connection.
2. Static checks (those can be run in tests on an empty database).
   All static checks can be performed at runtime as well.

pg-index-health allows you to detect the following problems:

№	Description	Type	Supports partitioning	SQL query
1	Invalid (broken) indexes	runtime/static	yes	sql
1	Duplicated (completely identical) indexes	static	no	sql
3	Intersected (partially identical) indexes	static	no	sql
4	Unused indexes	runtime	no	sql
5	Foreign keys without associated indexes	static	no	sql
6	Indexes with null values	static	yes	sql
7	Tables with missing indexes	runtime	yes	sql
8	Tables without primary key	static	yes	sql
9	Indexes bloat	runtime	no	sql
10	Tables bloat	runtime	no	sql
11	Tables without description	static	yes	sql
12	Columns without description	static	yes	sql
13	Columns with json type	static	no	sql
14	Columns of serial types that are not primary keys	static	no	sql
15	Functions without description	static	not applicable	sql
16	Indexes with boolean	static	no	sql
17	Tables with not valid constraints	runtime/static	no	sql
18	B-tree indexes on array columns	static	no	sql
19	Sequence overflow	runtime	not applicable	sql
20	Primary keys with serial types	static	yes	sql
21	Duplicated (completely identical) foreign keys	static	no	sql
22	Intersected (partially identical) foreign keys	static	no	sql
23	Possible object name overflow (identifiers with maximum length)	static	yes	sql
24	Tables not linked to other tables	static	yes	sql
25	Foreign keys with unmatched column type	static	no	sql

For raw sql queries see pg-index-health-sql project.

How does it work?

Static checks

Static checks are based on information schema/system catalogs. They work with finite database state (after all migrations are applied).

Runtime checks

pg_index_health utilizes the Cumulative Statistics System (formerly known as PostgreSQL's statistics collector).

You can call pg_stat_reset() on each host to reset all statistics counters for the current database to zero but the best way to do it is to use DatabaseManagement::resetStatistics() method.

Installation

Using Gradle:

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

with Kotlin DSL

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

Using Maven:

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

Articles and publications

In Russian

• 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"

How to use

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

• unit\functional testing (see standard test in section below);
• collecting indexes health data and monitoring bloat;
• analysis of database configuration.

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.14.4'

with Kotlin DSL

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

Using Maven:

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

Standard test

Add a standard test to your project as shown below. Ideally, all checks should work 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 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;

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

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

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

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c ->
                assertThat(c.check())
                    .as(c.getDiagnostic().name())
                    .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.14.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

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.