$ show mfvanek/pg-index-health
pg-index-health is an embeddable Java library and schema linter for PostgreSQL that detects common database anti-patterns and promotes best practices. It provides over 40 diagnostic checks covering duplicated/intersected indexes, bloat, missing indexes, inappropriate column types, naming violations, unlogged tables, sequence overflow, and many more. The library is designed for integration into applications (via Spring Boot starter) or use in CI pipelines to enforce schema quality. It supports both single-host and high-availability PostgreSQL clusters, with checks that can run statically (against schema only) or require accumulated statistics. The architecture is a multi-module Gradle project: `pg-index-health-model` defines immutable domain objects (Table, Index, Column, ForeignKey, etc.) with validation and composable predicates; `pg-index-health-core` implements host-level checks using a template pattern (`AbstractCheckOnHost`) with SQL queries loaded from a separate submodule; `pg-index-health` wraps these into cluster-level checks (`AbstractCheckOnCluster`) that aggregate results across primary and standby nodes; `pg-index-health-jdbc-connection` provides connection factories with primary host detection and multi-host URL parsing; `pg-index-health-generator` produces corrective SQL migrations; `pg-index-health-logger` formats results; and `pg-index-health-testing` offers Testcontainers-based test fixtures. Optional Jackson 2/3 modules enable JSON serialization of results. Key entry points include `StandardChecksOnHost` and `StandardChecksOnCluster` which enumerate all available checks, and the Spring Boot auto-configuration (`DatabaseStructureHealthAutoConfiguration`) that registers check beans. Data flows involve querying PostgreSQL system catalogs (`pg_catalog`, `pg_stat_*`), mapping result sets to domain objects via `ResultSetExtractor` implementations, applying exclusion predicates (e.g., skip Flyway/Liquibase tables, small tables, named objects), and returning lists of deviations. External dependencies are minimal: PostgreSQL JDBC driver, Apache DBCP2 connection pool, and Testcontainers for testing. The library is published to Maven Central and includes a BOM for dependency management.
The LLM identified 4 files worth highlighting for reuse:
Implements a composable predicate for excluding tables by name from check results. Extends an abstract base that supports both inclusion and exclusion lists, and can be combined with other predicates via `Predicate.and()`. This pattern is reusable in any project that needs to filter database objects (or any named entities) by a configurable set of names, and demonstrates clean separation of filtering logic from check execution.
Provides a static factory method `of()` that returns a `ResultSetExtractor<Index>` implementation. The extractor maps a JDBC `ResultSet` row to an immutable `Index` object, handling schema-qualified names and null values. This pattern is reusable across any JDBC-based project that needs to convert query results to domain objects, and demonstrates a clean, testable, and isolated mapping layer.
Parses PostgreSQL JDBC URLs that may contain multiple hosts (for HA clusters) and rewrites the `targetServerType` parameter to ensure connections are made to the primary host. Handles edge cases like IPv6 addresses, port specifications, and parameter ordering. This utility is reusable for any Java application that needs to connect to a PostgreSQL HA cluster and demonstrates robust string parsing without external dependencies.
Converts SQL queries with named parameters (e.g., `:schemaName`) to JDBC-compatible positional placeholders (`?`). Supports multiple occurrences of the same parameter name and handles edge cases like colons in strings. This utility is reusable in any Java project that needs to execute parameterized SQL without a full ORM, and demonstrates careful parsing logic with proper escaping.