Postgres has a built-in performance monitor. Here is how to use it.

Before paying for an APM or database monitoring tool, founders can use Postgres's internal pg_stat_* views to identify unused indexes and slow queries with simple SQL.

Every unused index in a PostgreSQL database adds write latency to every INSERT, UPDATE, and DELETE on its table. This overhead is a quiet tax on performance. A developer, Kalpesh Parihar, documented a playbook for identifying this bloat at his company using only the tools already built into the database.

The core insight is that Postgres continuously tracks its own performance. This data lives in a family of system views, accessible via standard SQL. No external agents, extensions, or paid services are required for an initial diagnosis. Reading these statistics is a simple SELECT query against a production replica.

The data is already there

Postgres collects detailed usage statistics in a family of system views prefixed with pg_stat_. These views provide a high-level overview of table and index activity since the last time statistics were reset. The author of the source material highlights four key views for initial investigation.

• pg_stat_user_tables: Tracks per-table scans, row-level activity (inserts, updates, deletes), and vacuum information.
• pg_stat_user_indexes: Contains per-index scan counts. This is the primary tool for finding unused indexes.
• pg_statio_user_indexes: Shows index block reads from disk versus hits from the cache, a key indicator of memory pressure.
• pg_index, pg_class, pg_stats: These are metadata views that provide context, linking statistics back to specific tables and columns.

The author reports that this approach required only a read-only database user, making it safe to run against a production replica without performance impact or write risk.

First, find the unused indexes

Indexes are not free. They consume disk space and, more critically, require updates during write operations. An index that is never or rarely used for reads is pure overhead. By querying the pg_stat_user_indexes view, a team can identify indexes with a low idx_scan count. An index with zero scans since the last statistics reset is a primary candidate for removal.

This single query can yield significant performance gains on write-heavy tables by eliminating unnecessary work. The process is straightforward: identify indexes with low or zero usage, confirm they are not required for unique constraints or specific, infrequent jobs, and then drop them.

Then, check cache hit rates

After removing bloat, the next step is to analyze the efficiency of the remaining indexes. The pg_statio_user_indexes view provides data on how often an index is served from memory (a cache hit) versus being read from disk. A low cache-hit ratio on a frequently scanned index indicates that the database's working set might exceed available memory. This can be a signal to increase server memory, tune memory allocation settings, or revise queries to be more efficient.

What we'd change

The playbook described is an excellent starting point for manual, episodic investigation. It is not a complete monitoring system. The primary limitation is that the pg_stat_* counters reset when a server restarts or when they are manually reset by a superuser. A single query provides a snapshot, not a trend.

To make this playbook more robust, a team should collect this data periodically. A simple cron job that runs the queries and stores the output in another table or a time-series database would provide historical context. This allows for tracking index usage over months, not just since the last reboot, making decisions like dropping an index much safer.

This method also lacks alerting. It identifies problems when an engineer actively looks for them. A more mature implementation would involve setting thresholds. For example, an alert could trigger if a critical index's cache hit rate drops below 95% for a sustained period. This moves the process from reactive analysis to proactive monitoring.

Finally, these views identify which indexes are problematic but not always why. They are a starting point. The definitive tool for deep query analysis remains EXPLAIN ANALYZE, which should be used to dissect the specific slow queries identified through this higher-level monitoring.

Landing

This approach is about more than saving a few hundred dollars on a monitoring tool. It represents a culture of operational discipline. By using the diagnostic tools built directly into core infrastructure, an engineering team develops a deeper understanding of its own systems. It is a capital-efficient strategy that prioritizes engineering solutions over SaaS subscriptions. For an early-stage company, this ability to sweat the details of the existing stack is a powerful and scalable advantage.

The investor read

This playbook is a strong positive signal for investors who value capital efficiency and deep technical competence, particularly in bootstrapped or seed-stage companies. It demonstrates a team that solves problems with engineering before budget, a leading indicator of a culture that can manage infrastructure costs effectively during scale-up. While not a product, this operational discipline is the opposite of a 'growth at all costs' mindset that papers over performance issues with oversized hardware or expensive SaaS tools. It suggests a team that understands its stack and can build a durable, profitable business without relying on excessive venture funding for operational expenses.

Pull quote: “Reading these statistics is a simple SELECT query against a production replica.”