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

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

with Kotlin DSL

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

Using Maven:

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

Articles and publications

• Index health in PostgreSQL through the eyes of a Java developer

How to use

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

• unit\functional testing;
• 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.13.3'

with Kotlin DSL

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

Using Maven:

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

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.13.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.