PostgreSQL Health Monitoring with Python: Connections, Bloat, Vacuum, and Locks (Free CLI Tool)

Why PostgreSQL Health Monitoring is Critical

PostgreSQL is the most advanced open-source relational database, powering millions of applications from small startups to Fortune 500 companies. Unlike simpler databases, PostgreSQL requires active maintenance — its MVCC (Multi-Version Concurrency Control) architecture means that deleted or updated rows are not immediately reclaimed. Without proper vacuuming, tables accumulate "dead tuples" that degrade query performance and waste disk space.

Connection management is equally critical. PostgreSQL has a hard limit on connections (default 100), and each connection consumes significant memory. Connection pool exhaustion is one of the most common production PostgreSQL incidents, causing application-wide outages with cryptic "too many connections" errors.

dargslan-postgres-health monitors all critical PostgreSQL health metrics using standard SQL queries through the psql CLI. It checks connection usage, table bloat, vacuum freshness, lock contention, replication lag, and long-running queries.

Install dargslan-postgres-health

pip install dargslan-postgres-health

Zero Python dependencies. Requires psql CLI (included with postgresql-client package). Connects using standard PGHOST, PGUSER, PGPASSWORD environment variables.

CLI Usage

# Full health report
dargslan-pg report

# Connection statistics
dargslan-pg connections

# Database sizes
dargslan-pg databases

# Table bloat analysis
dargslan-pg bloat

# Vacuum status
dargslan-pg vacuum

# Waiting locks
dargslan-pg locks

# Long-running queries
dargslan-pg queries

# Replication status
dargslan-pg replication

# Connect to specific server
dargslan-pg report -H db.example.com -U postgres -d myapp

# JSON output
dargslan-pg json

Python API

from dargslan_postgres_health import PostgresHealth

ph = PostgresHealth(host="localhost", user="postgres", dbname="myapp")

# Connection status
conn = ph.connection_status()
print(f"Connections: {conn[\"total_connections\"]}/{conn[\"max_connections\"]} ({conn[\"usage_percent\"]}%)")

# Database sizes
for db in ph.database_sizes():
    print(f"{db[\"database\"]}: {db[\"size_mb\"]:.1f} MB")

# Table bloat
for t in ph.table_bloat():
    print(f"{t[\"table\"]}: {t[\"dead_percent\"]}% dead tuples")

# Lock contention
locks = ph.active_locks()
print(f"Waiting locks: {locks}")

# Full audit
issues = ph.audit()
for i in issues:
    print(f"[{i[\"severity\"]}] {i[\"message\"]}")

Key Health Metrics

• Connection usage > 80% — WARNING: Consider connection pooling (PgBouncer)
• Dead tuples > 20% — WARNING: Table needs vacuuming
• Waiting locks > 5 — WARNING: Check for lock contention patterns
• Long-running queries > 30s — INFO: May indicate missing indexes
• Replication lag > 1s — WARNING: Check network and I/O

Download the PostgreSQL Health Cheat Sheet

Get our PostgreSQL Health Check Cheat Sheet — covering monitoring queries, vacuum management, connection pooling, and lock analysis.

Related Tools

Browse all database Python tools at dargslan.com. Our PostgreSQL administration eBooks cover performance tuning, replication, and high availability.