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

Intelligent Vacuum Monitoring: Tracks table bloat with context-aware thresholds and TOAST overhead detection
Index Usage Analysis: Identifies unused or bloated indexes with efficiency metrics and specific recommendations
Materialized View Performance: Monitors refresh times and bottlenecks (I/O, CPU, memory) with historical trending
Adaptive Precision: Automatically leverages pgstattuple and pg_stat_statements when available, with graceful fallbacks
Human-readable Insights: Provides clear status indicators (Red/Yellow/Green), severity scores (1-5), and actionable recommendations
Dashboard-Ready Outputs: Structured JSON format ideal for integration with monitoring dashboards
Low Overhead: Minimal impact on database performance with configurable collection frequencies

📊 Example Output

[
  {
    "metric": "Table Vacuum Health",
    "table_name": "public.large_table",
    "status": "Red",
    "severity": 5,
    "insight": "Table bloat is critically high; TOAST overhead is significant.",
    "action": "Run VACUUM FULL or CLUSTER; review large fields for TOAST impact.",
    "last_vacuum": "2025-03-10 14:30:22",
    "last_autovacuum": "2025-03-12 02:15:44",
    "vacuum_count": 127,
    "dead_tuples": 1543267,
    "live_tuples": 4329856,
    "table_size": "2.3 GB",
    "bloat_estimate": "42% (precise)",
    "details": {
      "median_bloat": "15%",
      "bloat_stddev": "8%",
      "toast_bloat": "22%",
      "explanation": "Includes TOAST bloat in total health assessment."
    }
  }
]

📋 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

🛠️ Installation

See the Installation Guide for detailed installation instructions, including manual installation steps.

📘 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()');

🖥️ Dashboard Integration

The JSON output format makes integration with monitoring dashboards straightforward:

Grafana: Use a PostgreSQL data source with JSON parsing
Pganalyze: Import as custom metrics
Custom dashboards: Use the structured JSON for consistency

🔄 Update Frequency Recommendations

Database Characteristics	Recommended Update Frequency
High-write production	Hourly
Mixed read/write	Every 6 hours
Read-heavy, stable	Daily
Development/staging	On-demand or daily

🔧 Advanced Configuration

Adjust permissions as needed:

-- 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;

Include in schema backups:

pg_dump -s -n public mydatabase > schema.sql

📝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Fork the repository
Create your feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m 'Add some amazing feature')
Push to the branch (git push origin feature/amazing-feature)
Open a Pull Request

📜 License

This project is licensed under the MIT License - see the LICENSE file for details.

📊 Technical Details

The extension consists of:

Tables:
- c77_dbh_vacuum_stats: Table vacuum statistics
- c77_dbh_index_stats: Index usage and bloat statistics
- c77_dbh_mv_stats: Materialized view refresh metrics
Functions:
- Data collection functions (e.g., c77_dbh_update_vacuum_stats())
- Health assessment functions (e.g., c77_dbh_get_vacuum_health())
- Event trigger functions for materialized views
Event Triggers:
- Automatically tracks materialized view operations

🙏 Acknowledgments

Inspired by statistical process control principles
Initial development facilitated by the PostgreSQL community