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 previous PostgreSQL versions 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 a 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 are not linked to other tables (sql).
25. Foreign keys with unmatched column type (sql).
26. Tables with zero or one columns (sql).
27. Objects whose names do not follow the naming convention (sql).
28. Columns whose names do not follow the 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).
35. Columns with a timestamp type (sql).
36. Tables where the primary key columns are not first (sql).
37. Tables that have all columns besides the primary key that are nullable (sql).
38. Columns with char(n) type (sql).
39. Tables with inheritance (sql).
40. Multicolumn foreign keys where at least one of the referencing columns is nullable (sql).
41. Tables with no data (sql).
42. Self-referenced foreign keys (sql).
43. Columns with large objects type (BLOB/CLOB) (sql).
44. Tables with incrementing column names (sql).
45. Columns with inconsistent types (the same name but different data types) (sql).
46. Unlogged tables (sql).
47. Unlogged sequences (sql).

Local development

Linting

SQLFluff is used to lint all SQL queries. The configuration is located in .sqlfluff.

macOS/Linux

docker run --rm \
  -v "$(pwd)":/sql \
  sqlfluff/sqlfluff:4.5.0 lint /sql/sql

Windows

Use cmd on Windows:

docker run --rm ^
  -v "%cd%":/sql ^
  sqlfluff/sqlfluff:4.5.0 lint /sql/sql