PostgreSQL's built-in system views can diagnose slow queries without extra tools

A developer at Atlas built a lightweight PostgreSQL Index Monitor after repeatedly running manual SQL queries to investigate database performance issues. PostgreSQL continuously logs performance data in system views such as pg_stat_user_tables and pg_stat_user_indexes, which can be queried with a basic read-only database user. These views reveal critical details like unused indexes, vacuum status, and write overhead — no APM agent, paid dashboard, or database extension required. The author highlights that unused indexes still consume disk space and slow down every write operation, making their identification an important optimization step. A key caveat noted is that scan counters are cumulative since the last statistics reset, so apparent zero-use indexes should be verified against the age of the stats before any index is dropped.