c77_dbh - PostgreSQL Database Health Extension

A lightweight, intelligent PostgreSQL extension for database health monitoring and maintenance. Provides actionable insights through JSON-formatted metrics on table bloat, index efficiency, materialized view performance, and more.

🔍 Overview

c77_dbh embeds health monitoring directly into your PostgreSQL database, eliminating the need for external monitoring tools. It uses statistical analysis to establish dynamic, database-specific thresholds for various health indicators, enabling both technical and non-technical users to identify and address performance issues.

✨ Features

📊 Example Output

📋 Requirements

• PostgreSQL 13 or higher
• Optional but recommended:
  • pgstattuple extension for precise bloat measurements
  • pg_stat_statements extension for enhanced query performance insights
  • pg_cron extension for automated statistics collection

📘 Usage

Basic Setup

After installation, enable the extension in your database:

CREATE EXTENSION c77_dbh;

Collecting Statistics

Initialize the statistics tables:

-- Update table and vacuum statistics
SELECT c77_dbh_update_vacuum_stats();

-- Update index statistics (including TOAST indexes)
SELECT c77_dbh_update_index_stats();

Monitoring Health

Query health metrics:

-- Table health with bloat estimates and recommendations
SELECT * FROM c77_dbh_get_vacuum_health();

-- Index health analysis
SELECT * FROM c77_dbh_get_index_health();

-- TOAST index specific analysis
SELECT * FROM c77_dbh_get_toast_health();

-- Materialized view refresh performance
SELECT * FROM c77_dbh_get_mv_health();

Automation

With pg_cron extension:

-- Update vacuum stats hourly
SELECT cron.schedule('update_vacuum_stats', '0 * * * *', 
                    'SELECT c77_dbh_update_vacuum_stats()');

-- Update index stats hourly
SELECT cron.schedule('update_index_stats', '0 * * * *', 
                    'SELECT c77_dbh_update_index_stats()');

🔄 Update Frequency Recommendations

🖥️ Dashboard Integration

The JSON output format makes integration with monitoring dashboards straightforward:

🔧 Advanced Configuration

Permissions Setup

-- Grant read access to a reporting role
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role;

-- Grant full access to admin roles
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_role;

Schema Backup Integration

# Include in schema backups
pg_dump -s -n public mydatabase > schema.sql

📊 Technical Details

The extension consists of:

Tables

• c77_dbh_vacuum_stats: Table vacuum statistics and bloat measurements
• c77_dbh_index_stats: Index usage and bloat statistics
• c77_dbh_mv_stats: Materialized view refresh metrics and performance data

Functions

• Data collection functions: c77_dbh_update_vacuum_stats(), c77_dbh_update_index_stats()
• Health assessment functions: c77_dbh_get_vacuum_health(), c77_dbh_get_index_health()
• Event trigger functions: Automatic tracking for materialized views

Event Triggers

• Materialized view operations: Automatically tracks refresh operations and performance

🎯 Health Metrics Deep Dive

Table Vacuum Health

Index Health Analysis

Materialized View Performance

-- Example materialized view health output
{
  "metric": "Materialized View Performance",
  "view_name": "public.sales_summary",
  "status": "Yellow",
  "severity": 3,
  "insight": "Refresh times trending upward; consider optimization",
  "action": "Review refresh query performance and consider incremental refresh",
  "last_refresh": "2025-06-12 08:30:15",
  "refresh_duration": "00:03:42",
  "rows_affected": 1234567,
  "trend": "increasing",
  "details": {
    "avg_refresh_time": "00:02:45",
    "max_refresh_time": "00:05:12",
    "bottleneck": "I/O intensive operations"
  }
}

🔍 Statistical Analysis

Dynamic Thresholds

The extension uses statistical process control principles to establish intelligent thresholds:

Severity Scoring

Health issues are scored on a 1-5 scale:

• 1 (Low): Green - Minor issues, monitor only
• 2-3 (Medium): Yellow - Should be addressed during maintenance windows
• 4-5 (High): Red - Requires immediate attention

🚀 Performance Optimization

Efficient Data Collection

-- Optimize statistics collection for large databases
-- The extension automatically uses sampling for large tables

-- Check collection performance
SELECT 
    table_name,
    collection_time,
    sample_size,
    accuracy_estimate
FROM c77_dbh_collection_stats 
ORDER BY collection_time DESC;

Resource Management

🛠️ Troubleshooting

Common Issues

-- Check if statistics have been collected
SELECT COUNT(*) FROM c77_dbh_vacuum_stats;
SELECT COUNT(*) FROM c77_dbh_index_stats;

-- If empty, run collection manually
SELECT c77_dbh_update_vacuum_stats();
SELECT c77_dbh_update_index_stats();

Performance Tuning

• Large databases: Increase sampling intervals and use schema-specific collection
• High-frequency updates: Consider reducing collection frequency during peak hours
• Resource constraints: Use the lightweight collection mode for resource-constrained environments

🎯 Integration Examples

Grafana Dashboard Query

-- Query for Grafana visualization
SELECT 
    table_name,
    CASE status 
        WHEN 'Red' THEN 3
        WHEN 'Yellow' THEN 2
        WHEN 'Green' THEN 1
    END as status_code,
    severity,
    insight
FROM c77_dbh_get_vacuum_health()
WHERE status != 'Green'
ORDER BY severity DESC;

Alert Integration

-- Query for alerting systems
SELECT 
    'Database Health Alert' as alert_type,
    table_name,
    status,
    severity,
    insight,
    action,
    NOW() as alert_time
FROM c77_dbh_get_vacuum_health()
WHERE severity >= 4
UNION ALL
SELECT 
    'Index Health Alert',
    index_name,
    status,
    severity,
    insight,
    action,
    NOW()
FROM c77_dbh_get_index_health()
WHERE severity >= 4;

🔄 Maintenance

Regular Maintenance Tasks

• Weekly: Review high-severity health issues
• Monthly: Analyze trends and adjust thresholds if needed
• Quarterly: Archive old statistics and optimize storage

Statistics Cleanup

-- Clean up old statistics (older than 30 days)
DELETE FROM c77_dbh_vacuum_stats 
WHERE collected_at < NOW() - INTERVAL '30 days';

DELETE FROM c77_dbh_index_stats 
WHERE collected_at < NOW() - INTERVAL '30 days';

Intelligent database health monitoring made simple!

This extension brings enterprise-grade database monitoring directly into PostgreSQL, providing actionable insights without the complexity of external tools.

View Git Repository →