Pg Health Check

PostgreSQL Health Check

This skill guides a comprehensive health assessment of a PostgreSQL database using pgtuner-mcp tools, producing a scored report with prioritized action items.

When to Use This Skill

Use this skill when the user:

• Asks for a database health check or status report
• Reports general performance degradation without specific symptoms
• Wants a routine database checkup or audit
• Asks "is my database healthy?" or "what should I fix first?"
• Is setting up monitoring or wants to establish a baseline
• Mentions connection issues, high CPU, or unexplained slowness

MCP Resources Available

Before running full tool scans, the agent can use targeted resources:

• pgtuner://health/{check_type} -- Targeted health checks: connections, cache, locks, replication, bloat, all (lightweight, avoids running the full health tool)
• pgtuner://settings/{category} -- Settings by category: memory, checkpoint, wal, autovacuum, connections
• pgtuner://docs/workflows -- Recommended workflow patterns

Smart resource usage: After the full health check in Step 1, use targeted resources for follow-up instead of re-running the full tool. For example, if the locks dimension scores low, read pgtuner://health/locks for quick re-checks after remediation.

Related MCP Prompt

This skill corresponds to the health_check MCP Prompt. If the user triggers that prompt, follow this skill's workflow.

Prerequisites

• The pgtuner-mcp server must be connected with a valid DATABASE_URI
• Required: pg_stat_statements extension
• Recommended: monitoring user with access to pg_stat_activity, pg_stat_bgwriter, pg_locks

Agent Decision Tree

User asks for health check
  |
  +--> Quick check or full check?
  |    QUICK --> Read pgtuner://health/all resource (fast, no tool call)
  |    FULL  --> Start with Step 1 (check_database_health)
  |
  +--> After Step 1, which dimensions scored low?
       |
       +--> Connections < 90 --> Prioritize Step 2, then pg-connection-analysis skill
       +--> Cache < 90      --> Prioritize Step 5 (I/O), then pg-io-deep-dive skill
       +--> Locks < 90      --> Prioritize Step 2 + Step 3, then pg-lock-diagnosis skill
       +--> Wraparound < 90 --> URGENT: check pg-vacuum-tuning skill
       +--> Checkpoints < 90 --> Prioritize Step 5, then pg-config-tuning skill
       +--> All >= 90       --> Summary report, suggest pg-performance-baseline

Health Check Workflow

Step 1: Overall Health Assessment

Tool: check_database_health
Parameters:
  include_recommendations: true
  verbose: true

What this checks (8 health dimensions):

1. Connections: Usage ratio vs max_connections
2. Cache hit ratio: shared_buffers effectiveness
3. Lock contention: Blocking locks and deadlocks
4. Replication: Lag and slot status
5. Transaction wraparound: XID age approaching limits
6. Disk usage: Tablespace utilization
7. Background writer: Checkpoint and bgwriter efficiency
8. Checkpoint performance: Timing and frequency

Interpret the scores:

• 90-100 (Healthy): No immediate action needed
• 70-89 (Warning): Issues that should be addressed soon
• < 70 (Critical): Immediate attention required

Record the overall score and any dimensions scoring below 90.

Step 2: Active Query Analysis

Tool: get_active_queries
Parameters:
  include_idle: true          # Include idle-in-transaction
  include_system: false
  min_duration_seconds: 0     # Show all active queries

What to look for:

Step 3: Wait Event Analysis

Tool: analyze_wait_events
Parameters:
  active_only: true

Interpret wait event types:

Step 4: Configuration Review

Tool: review_settings
Parameters:
  category: "all"
  include_all_settings: false

Key settings to validate:

Step 5: Disk I/O Assessment (If health score < 90 or IO waits detected)

Use targeted analysis types based on which health dimension scored low:

For cache-related issues:

Tool: analyze_disk_io_patterns
Parameters:
  analysis_type: "buffer_pool"
  top_n: 20

For checkpoint-related issues:

Tool: analyze_disk_io_patterns
Parameters:
  analysis_type: "checkpoints"
  top_n: 10

For a comprehensive I/O overview:

Tool: analyze_disk_io_patterns
Parameters:
  analysis_type: "all"
  top_n: 20

What to look for:

• Buffer pool hit ratio < 99% for OLTP workloads
• Tables with high heap_blks_read relative to heap_blks_hit
• Excessive temp file usage (queries spilling to disk)
• Backend fsync count > 0 (not all writes going through checkpointer)
• Requested checkpoints > timed checkpoints (increase max_wal_size)

Step 6: Quick Bloat Scan (If vacuum metrics are concerning)

Tool: get_bloat_summary
Parameters:
  schema_name: "public"
  top_n: 10

What to look for:

• Tables with dead tuple ratio > 10%
• Tables approaching transaction wraparound
• Large tables that haven't been vacuumed recently

Output Format

Health Report Card

Critical Issues (Fix Now)

Numbered list with specific remediation steps and SQL commands.

Warnings (Fix Soon)

Issues that should be addressed within days/weeks.

Observations (Monitor)

Things that are currently OK but worth watching.

Recommended Configuration Changes

-- Present as ALTER SYSTEM commands with explanations
ALTER SYSTEM SET shared_buffers = '4GB';        -- Currently 128MB, server has 16GB RAM
ALTER SYSTEM SET max_wal_size = '4GB';           -- Reduce checkpoint frequency
-- Requires restart: shared_buffers
-- Reload only: max_wal_size (SELECT pg_reload_conf();)

Health Check Cheatsheet

Quick Triage Priority

1. Connection saturation > 90%: Immediate risk of outage
2. Transaction wraparound XID age > 1 billion: Risk of forced shutdown
3. Blocking locks > 30 seconds: Active user impact
4. Long idle-in-transaction: Blocks autovacuum, causes bloat
5. Cache hit ratio < 95%: Widespread performance degradation
6. Checkpoint frequency: Too many requested checkpoints waste I/O

When to Escalate

• XID age > 1.5 billion: Emergency, run manual VACUUM FREEZE
• Connections at 100%: Emergency, consider pg_terminate_backend() for idle connections
• Replication lag growing continuously: Replica may never catch up
• OOM killer active: Reduce shared_buffers or work_mem

Important Notes

• The health check is read-only and safe to run on production databases
• Health scores are relative: a score of 85 is acceptable for many workloads
• Run health checks regularly (weekly) to detect trends
• Compare results against previous baselines for meaningful trending
• Some settings require a PostgreSQL restart to take effect (marked with (restart) in pg_settings)

Iterative Verification

After applying remediation for any health dimension:

1. Read pgtuner://health/{check_type} for the specific dimension to verify improvement
2. For settings changes that require reload: verify with SHOW setting_name after pg_reload_conf()
3. For lock contention fixes: re-run get_active_queries with include_idle: true to confirm

When to Stop and Ask the User

• If all dimensions score > 90: "Your database is healthy. Would you like me to generate a baseline report for future comparison?"
• If wraparound risk is detected (XID age > 1 billion): "URGENT: Transaction wraparound risk detected. This requires immediate VACUUM FREEZE. Do you want me to provide the emergency procedure?"
• If configuration changes require restart: "The recommended shared_buffers change requires a PostgreSQL restart. When is your next maintenance window?"
• If the issue is outside database scope (e.g., network, application): "The database health metrics look fine. The performance issue may be in the application layer or infrastructure."

Cross-References to Other Skills

• Deep connection analysis: Use pg-connection-analysis skill
• Lock contention investigation: Use pg-lock-diagnosis skill
• I/O deep-dive: Use pg-io-deep-dive skill
• Configuration tuning: Use pg-config-tuning skill
• Vacuum/wraparound issues: Use pg-vacuum-tuning skill
• Bloat detected: Use pg-bloat-analysis skill
• Establishing a baseline: Use pg-performance-baseline skill

PostgreSQL Version Notes

Production Safety

• All tools used in this workflow are read-only
• No data or configuration is modified
• Health checks can be run during peak traffic without impact