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

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

1. Invalid (broken) indexes (sql).
2. Duplicated (completely identical) indexes (sql).
3. Intersected (partially identical) indexes (sql).
4. Unused indexes (sql).
5. Foreign keys without associated indexes (sql).
6. Indexes with null values (sql).
7. Tables with missing indexes (sql).
8. Tables without primary key (sql).
9. Indexes bloat (sql).
10. Tables bloat (sql).
11. Tables without description (sql).
12. Columns without description (sql).
13. Columns with json type (sql).
14. Columns of serial types that are not primary keys (sql).
15. Functions without description (sql).

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

How does it work?

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

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

Using Maven:

<dependency>
  <groupId>io.github.mfvanek</groupId>
  <artifactId>pg-index-health</artifactId>
  <version>0.10.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-spring-boot-demo project.

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.

Related projects

pg_analyse - a set of tools to gather useful information from PostgreSQL, written in Python, with command line interface.

Acknowledgements

Supported by JetBrains with Licenses for Open Source Development