PostgreSQL Diagnostics Script: Debug Database Performance Issues

Neutrinos Platform
,
1

PostgreSQL Diagnostics Tool: Debug Database Performance Issues

Introduction

When your application experiences performance issues, database problems are often the culprit. Identifying slow queries, connection bottlenecks, or resource constraints typically requires running multiple SQL queries and analyzing scattered metrics.

This tool provides a single command to collect comprehensive PostgreSQL diagnostics, helping you quickly identify and resolve database performance issues.

Quick Access: :link:Download from Bitbucket

What Problem Does This Solve?

When debugging database performance, you typically face:

  • Manual Query Execution - Running dozens of diagnostic queries by hand
  • Missing Context - Not knowing what metrics to check
  • Time-Consuming - Collecting data from multiple system views
  • Incomplete Picture - Missing critical statistics
  • No Historical Record - Results disappear after the session

The Old Way

-- Manually run multiple queries
SELECT * FROM pg_stat_activity WHERE state = 'active';
SELECT * FROM pg_locks WHERE NOT granted;
SELECT pg_database_size(current_database());
-- ... 15+ more queries to run manually ...
-- Copy/paste results somewhere
-- Try to correlate the data

The New Way

node pg-debug.js

# You get:
# - Long-running queries with full SQL text
# - Connection statistics
# - Database and table sizes
# - Cache hit ratios
# - Blocking locks
# - Formatted console output + JSON export

The Solution: Automated PostgreSQL Diagnostics

A Node.js tool that automatically collects and analyzes PostgreSQL performance metrics.

What You Get

  1. Long-Running Queries - Identifies slow queries with full context
  2. Connection Statistics - Active, idle, and waiting connections
  3. Database Sizes - Space usage across databases
  4. Table Sizes - Largest tables with indexes
  5. Cache Hit Ratio - Buffer cache efficiency (with recommendations)
  6. Blocking Locks - Queries preventing other queries from executing
  7. Query Statistics - Most hit and slowest queries (with pg_stat_statements)
  8. Formatted Output - Beautiful console tables + JSON export

Database Requirements

Built-in Features (No Extensions Needed)

The tool uses standard PostgreSQL system views:

  • pg_stat_activity - Activity monitoring
  • pg_stat_database - Database statistics
  • pg_locks - Lock monitoring
  • pg_statio_user_tables - Cache statistics

All available in PostgreSQL 10+

Optional Extension

  • pg_stat_statements - Enhanced query statistics (optional, not required)

Getting Started

1. Install

cd js-scripts/postgres-diagnostics
npm install

2. Configure

cp config.example.json config.json
# Edit config.json with your database credentials

Configuration:

{
  "database": {
    "host": "your-db-host.com",
    "port": 5432,
    "database": "your_database",
    "user": "your_user",
    "password": "your_password",
    "ssl": true
  },
  "thresholds": {
    "longRunningQueryMinutes": 5,
    "topQueriesLimit": 10
  }
}

3. Run

node pg-debug.js

Usage Examples

Basic Diagnostics

node pg-debug.js

Check for Long-Running Queries

# Find queries running longer than 1 minute
node pg-debug.js --minutes 1

Custom Output

# Save to specific directory
node pg-debug.js --output ./prod-diagnostics

# JSON only (for automation)
node pg-debug.js --no-console

# Console only (for quick checks)
node pg-debug.js --no-json

Production Configuration

# Use production config with strict thresholds
node pg-debug.js --config ./prod-config.json --minutes 2 --limit 20

Output

Console Output

================================================================================
  PostgreSQL Diagnostics Report
================================================================================
  Database: ALPHA_CASE_SERVICE@mvl-dev-db.postgres.database.azure.com
  Timestamp: 2025-12-02T12:52:39.057Z
================================================================================

Long-Running Queries (> 1 min)
-------------------------------
┌────────┬─────────────────┬───────────────┬────────────┬────────────────────┐
│ PID    │ Duration        │ User          │ State      │ Wait Event         │
├────────┼─────────────────┼───────────────┼────────────┼────────────────────┤
│ 16083  │ 00:01:13.338    │ appuser       │ active     │ Timeout:PgSleep    │
└────────┴─────────────────┴───────────────┴────────────┴────────────────────┘

  Full Query Details:
  ──────────────────────────────────────────────────────────────────────────
  Query #1 (PID: 16083)
  Started: 2025-12-02 12:51:25
  Duration: 00:01:13.338
  User: appuser | App: myapp | State: active
  SQL:
    SELECT 
      c.id, c.name, c.status,
      COUNT(t.id) as task_count
    FROM cases c
    LEFT JOIN tasks t ON t.case_id = c.id
    WHERE c.created_at > '2025-01-01'
    GROUP BY c.id, c.name, c.status;
  ──────────────────────────────────────────────────────────────────────────

Connection Statistics
---------------------
  Total Connections: 66
  Active: 1
  Idle: 59
  Idle in Transaction: 0
  Waiting: 0

Cache Hit Ratio
---------------
  Cache Hit Ratio: 99.94%  ✓ Excellent

Database Sizes (Top 10)
-----------------------
┌────────────────────────────────┬───────────┐
│ Database                       │ Size      │
├────────────────────────────────┼───────────┤
│ alpha_load_test_base_092024    │ 18 GB     │
│ ALPHA_CASE_SERVICE             │ 2068 MB   │
└────────────────────────────────┴───────────┘

Table Sizes (Top 10)
--------------------
┌──────────────────────────┬──────────────────────┬───────────┐
│ Schema                   │ Table                │ Size      │
├──────────────────────────┼──────────────────────┼───────────┤
│ case_manager_schema      │ case_instance_data   │ 1222 MB   │
│ case_manager_schema      │ audit                │ 307 MB    │
└──────────────────────────┴──────────────────────┴───────────┘

Blocking Locks
--------------
  No blocking locks found ✓

JSON Output

See sample-output.json in the repository for a complete example.

Real-World Use Cases

1. Application Slowdown Investigation

Scenario: Users report the application is slow.

node pg-debug.js --minutes 1
# Identifies 3 queries running for 5+ minutes
# All doing full table scans on case_instance_data
# Solution: Add missing indexes

2. Connection Pool Exhaustion

Scenario: Application can’t connect to database.

node pg-debug.js
# Connection Stats show: 100 active, 0 idle
# All connections in "idle in transaction" state
# Solution: Fix application connection leak

3. Disk Space Issues

Scenario: Database running out of space.

node pg-debug.js
# Database Sizes: 45GB database
# Table Sizes: audit table is 38GB
# Solution: Implement audit log archival

4. Post-Deployment Health Check

# After deploying new code
node pg-debug.js --output ./post-deploy-checks
# Check for new long-running queries
# Verify cache hit ratio hasn't dropped
# Ensure no blocking locks appeared

Key Features

SSL Support

Multiple SSL configuration options for cloud databases:

{
  "database": {
    "ssl": true  // Simple SSL for Azure/AWS/GCP
  }
}

Or with certificates:

{
  "database": {
    "ssl": {
      "enabled": true,
      "rejectUnauthorized": true,
      "certPath": "./certs/ca.pem"
    }
  }
}

Command-Line Options

Option Description Example
--minutes <n> Long-running query threshold --minutes 10
--limit <n> Top queries limit --limit 20
--config <path> Custom config file --config prod.json
--output <path> Output directory --output ./diagnostics
--no-console Disable console output --no-console
--no-json Disable JSON export --no-json

Diagnostics Explained

Long-Running Queries

Queries exceeding the threshold may indicate:

  • Missing indexes
  • Inefficient SQL
  • Lock contention
  • Large dataset processing

Action: Review with EXPLAIN ANALYZE and optimize.

Cache Hit Ratio

Percentage of data reads from memory vs disk:

  • > 99% - Excellent
  • 90-99% - Good
  • < 90% - Consider increasing shared_buffers

Blocking Locks

Shows queries preventing others from executing:

  • Review blocking queries
  • Consider query optimization
  • Adjust lock timeout settings

Troubleshooting

“Connection failed: SSL off”

For cloud databases, enable SSL:

{
  "database": {
    "ssl": true
  }
}

“Permission denied”

Grant read permissions:

GRANT pg_read_all_stats TO your_user;

“pg_stat_statements not available”

This is normal. The tool works without it. To enable (optional):

CREATE EXTENSION pg_stat_statements;

Best Practices

1. Regular Health Checks

# Daily diagnostic runs
0 9 * * * cd /path/to/postgres-diagnostics && node pg-debug.js --no-console

2. Alert on Thresholds

# Check and alert if issues found
node pg-debug.js --no-console
# Parse JSON output and send alerts if needed

3. Post-Deployment Verification

# After each deployment
node pg-debug.js --minutes 2 --output ./deploy-$(date +%Y%m%d)

4. Incident Response

# When issues occur, collect diagnostics immediately
node pg-debug.js --minutes 1 --output ./incident-$(date +%Y%m%d-%H%M)

Security

  • Read-Only: All queries are read-only, no modifications
  • Credential Safety: Config files excluded from git
  • Minimal Permissions: Only needs pg_read_all_stats role
  • SSL Support: Secure connections to cloud databases

Additional Resources

Summary

What you get:

  • Single command database diagnostics
  • Comprehensive performance metrics
  • Long-running query details with full SQL
  • Cache and connection statistics
  • Formatted console + JSON output
  • Works with all major PostgreSQL providers

Next steps:

  1. Visit the repository
  2. Install and configure
  3. Run your first diagnostic
  4. Review the output and optimize

Questions?

Post in the community forum or report issues in the repository.

Happy debugging!

Last Updated: December 2025
Version: 1.0
Compatibility: PostgreSQL 10+, Node.js 14+
Repository: :link:Bitbucket

2 Likes