PostgreSQL Performance Health Check & Monitoring Tool – Health Report v2.0

PostgreSQL Health Report v2.0 (Stored Functions + SQL Script + 60+ SQL Queries)

A professional PostgreSQL health check and monitoring tool that helps you detect performance issues, stability risks, and security problems before they turn into outages with FIX commands.

Built for DBAs, developers, and DevOps engineers who run PostgreSQL in production.

What This Does

This is not a dashboard.
This is not an AI agent.

This is a database-native PostgreSQL health check that operates your Postgres database using SQL. You can either run it as a standalone SQL script or use the stored function file to execute it inside or outside PostgreSQL, then access the results through views.

You get:

• Immediate visibility into database health
• Setup Automation alerts based on views output
• No external dependencies
• No manual script juggling

PostgreSQL Health Report v2.0.2 - Complete Section Overview

Section 1: Database Environment & Metadata

1. Database Metadata (server host, port, version, uptime, size, connections)

Section 2: Critical Alerts - Immediate Action Needed

1. Critical Bloat (>30% Dead Tuples)
2. Tables Never Analyzed
3. Stale Statistics (>20% modifications)
4. Foreign Keys Without Index
5. XID Wraparound Risk (Database Level)
6. Wraparound Risk (XID or Multixact) - Table Level

Section 3: Connection Pool Health

1. Connection Pool Status (utilization %)
2. Connection States Breakdown (active, idle, idle in transaction)
3. Idle Connections (>5 min)
4. Aborted Transactions
5. Long-Running Transactions (Holding Locks)

Section 4: Lock Contention & Blocking Queries

1. Blocking Locks (blocked PID by blocking PID)
2. Lock Types Summary (by mode and locktype)

Section 5: Query Performance & Slow Queries

1. pg_stat_statements Extension Status
2. Slow Queries (Avg >100ms) - PostgreSQL 13+ compatible
3. Active Long-Running Queries (>30 seconds)
4. Prepared Statement Leaks
5. Prepared Statement Summary (count and potential leaks)

Section 5B: Wait Events Analysis

6. Wait Events Summary
7. Active Queries by Wait Event
8. Wait Event Details (Top Waiting Queries)
9. I/O Timing Analysis (track_io_timing status)

Section 6: Buffer Cache Hit Ratio & I/O Efficiency

1. Overall Cache Performance (cache hit ratio %)
2. Table Cache Performance (Worst performers)

Section 7: Table Bloat & Dead Tuple Analysis

1. Most Bloated Tables (dead tuple %)

Section 8: Vacuum & Autovacuum Health

1. Vacuum Health Score (overall status)
2. Active Maintenance Operations (vacuum/analyze in progress)
3. High Modification Rate Tables (Bloat Risk)

Section 9: Index Health & Usage Analysis

1. Unused Indexes (Consider Dropping)
2. Low-Usage Indexes
3. Tables Needing Indexes (High Sequential Scans)
4. Seq Scan vs Index Scan Analysis
5. Index Efficiency (Rows Scanned vs Returned)
6. Duplicate Indexes (Same Columns)

Section 9B: Partitioning Health

7. Partitioned Table Summary
8. Partition Size Imbalance Detection
9. Missing Partition Indexes
10. Partition Constraints

Section 10: Disk Usage & Largest Tables

1. Largest Tables (total size, table size, index size)
2. JSONB Columns Missing GIN Index

Section 11: Replication Health & Lag

1. Replication Status (send lag, replay lag, time lag)
2. Replication Slots (active/inactive, lag)
3. WAL Archive Status (archived/failed count)
4. WAL Generation Rate (wal_level, total WAL)

Section 12: Transaction Performance & Throughput

1. Transaction Stats (commit, rollback, deadlocks, conflicts)

Section 13: Checkpoint & Background Writer Statistics

1. Checkpoint Stats (timed vs requested, write/sync time)
   • PostgreSQL 17+ compatible (pg_stat_checkpointer)
   • PostgreSQL <17 compatible (pg_stat_bgwriter)

Section 14: Database-Wide Summary

1. Overall Health Summary (tables, size, dead %, issues count)

Section 15: Recommended Immediate Actions

1. Priority-Based Fix Commands:
   • P1: Never Analyzed tables
   • P1: Critical Bloat (>30%)
   • P2: Very Stale Stats (>20%)
   • P3: Moderate Bloat (>10%)
   • P4: Minor Issues

Section 16: Security & Permissions

1. Superuser Accounts
2. Users with Excessive Privileges (CREATEDB, CREATEROLE)
3. Public Schema Grants (CREATE privilege)
4. Tables Accessible to PUBLIC
5. Password Policy Violations (no expiry, expired)
6. Unused Roles (can login but never connected)
7. SSL/Encryption Connection Status

Section Report Metadata

1. Execution Summary (execution time, version, database info)

Total: 16 Sections, 60+ Queries, 4 Views, 2 Helper Functions

Compatibility

Works on all major PostgreSQL versions and managed services -->

- PostgreSQL 12, 13, 14, 15, 16, 17

- Azure Database for PostgreSQL Flexible Server

- AWS Aurora, RDS PostgreSQL

- Google Cloud SQL

- Self-hosted PostgreSQL

Final Thought

Most PostgreSQL outages aren’t caused by unknown problems.
They’re caused by known problems that weren’t checked in time.

The PostgreSQL Health Report v2.0 turns scattered DBA knowledge into an automated, repeatable PostgreSQL health check and monitoring system.

👉 Audit your database today. Prevent the next Sev-1 tomorrow.