pg-index-health is a Java library for analyzing and maintaining indexes and tables health in PostgreSQL databases.
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.
Supports Java 11 and above
For Java 8 compatible version take a look at release 0.7.0 and lower
All checks can be divided into 2 groups:
- 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. - 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.
Static checks are based on information schema/system catalogs.
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.
Using Gradle:
implementation 'io.github.mfvanek:pg-index-health:0.14.3'
with Kotlin DSL
implementation("io.github.mfvanek:pg-index-health:0.14.3")
Using Maven:
<dependency>
<groupId>io.github.mfvanek</groupId>
<artifactId>pg-index-health</artifactId>
<version>0.14.3</version>
</dependency>
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.
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.
Using Gradle:
testImplementation 'io.github.mfvanek:pg-index-health-test-starter:0.14.3'
with Kotlin DSL
testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.3")
Using Maven:
<dependency>
<groupId>io.github.mfvanek</groupId>
<artifactId>pg-index-health-test-starter</artifactId>
<version>0.14.3</version>
<scope>test</scope>
</dependency>
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 |
- 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
- 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.