search

mfvanek / pg-index-health

pg-index-health is a Java library for analyzing and maintaining indexes and tables health in Postgresql databases.
Apache License 2.0
128 stars 15 forks source link
gradle hacktoberfest health-check indexes java java-11 kotlin-dsl mutation-testing pitest postgres postgresql postgresql-monitoring sql testcontainers

readme

pg-index-health

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

Java CI Maven Central License: Apache 2.0 javadoc codecov

Bugs Vulnerabilities Code Smells Lines of Code Coverage

Mutation testing badge

Supported PostgreSQL versions

PostgreSQL 12 PostgreSQL 13 PostgreSQL 14 PostgreSQL 15 PostgreSQL 16

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

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.1'
with Kotlin DSL ```kotlin implementation("io.github.mfvanek:pg-index-health:0.13.1") ```

Using Maven:

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

Articles and publications

How to use

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

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.1'
with Kotlin DSL ```kotlin testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.13.1") ```

Using Maven:

<dependency>
    <groupId>io.github.mfvanek</groupId>
    <artifactId>pg-index-health-test-starter</artifactId>
    <version>0.13.1</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

Questions, issues, feature requests and contributions