pg-index-health-sql

pg-index-health-sql is a collection of SQL queries for analyzing PostgreSQL schemas, detecting issues and promoting best practices.

What is this?

pg-index-health-sql provides a curated set of raw SQL queries designed to identify common schema issues in PostgreSQL databases — such as redundant indexes, missing primary keys or inefficient table structures.

It enables lightweight, database-native analysis without requiring external libraries or frameworks, making it suitable for direct use in psql sessions, scripts, or monitoring dashboards.

Supported PostgreSQL versions

Support for previous versions of PostgreSQL

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.

Available checks

pg-index-health-sql 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).
16. Indexes with boolean (sql).
17. Tables with not valid constraints (sql).
18. B-tree indexes on array columns (sql).
19. Sequence overflow (sql).
20. Primary keys with serial types (sql).
21. Duplicated (completely identical) foreign keys (sql).
22. Intersected (partially identical) foreign keys (sql).
23. Objects with possible name overflow (sql).
24. Tables not linked to other tables (sql).
25. Foreign keys with unmatched column type (sql).
26. Tables with zero or one column (sql).
27. Objects whose names do not follow naming convention (sql).
28. Columns whose names do not follow naming convention (sql).
29. Primary keys with varchar columns instead of uuids (sql).
30. Columns with varchar(n) type (sql).
31. Indexes with unnecessary where-clause on not null column (sql).
32. Primary keys that are most likely natural keys (sql).
33. Columns with money type (sql).
34. Indexes with a timestamp in the middle (sql).

Local development

Linting

macOS/Linux

To run super-linter locally:

docker run \
  -e RUN_LOCAL=true \
  -e USE_FIND_ALGORITHM=true \
  -e VALIDATE_SQLFLUFF=true \
  -v $(pwd):/tmp/lint \
  ghcr.io/super-linter/super-linter:slim-v7.4.0

Windows

Use cmd on Windows:

docker run ^
  -e RUN_LOCAL=true ^
  -e USE_FIND_ALGORITHM=true ^
  -e VALIDATE_SQLFLUFF=true ^
  -v "%cd%":/tmp/lint ^
  ghcr.io/super-linter/super-linter:slim-v7.4.0

See https://github.com/super-linter/super-linter/blob/main/dependencies/python/sqlfluff.txt

docker run --rm ^
  -v "%cd%\.github\linters\.sqlfluff":/sql/.sqlfluff:ro ^
  -v "%cd%":/sql ^
  -e SQLFLUFF_CONFIG=/sql/.sqlfluff ^
  sqlfluff/sqlfluff:3.4.0 lint /sql