pg-cleanse

A CLI tool for inspecting PostgreSQL database health issues.

What It Detects

Blocked Queries - Queries waiting on locks held by other processes. These cause performance bottlenecks and application timeouts.

Zombie Transactions - Connections stuck in "idle in transaction" state. These hold locks, prevent vacuum from reclaiming space, and can lead to table bloat.

Dead Rows - Tables with high dead tuple counts from UPDATE/DELETE operations. Excessive dead rows slow down queries and waste storage until VACUUM runs.

Installation

go build -o pginspect .

Configuration

Set the following environment variables:

DB_HOST     - Database host (default: localhost)
DB_PORT     - Database port (default: 5432)
DB_USER     - Database user (required)
DB_PASSWORD - Database password
DB_NAME     - Database name (required)
DB_SSLMODE  - SSL mode (default: disable)

Usage

# Check for blocked queries
./pginspect blocked

# Check for zombie transactions
./pginspect zombie

# Check for dead rows
./pginspect dead

# Check for dead rows exceeding a threshold
./pginspect dead --threshold 10

Each command exits with status code 1 if issues are found.