Supabase Database Health Check Prompt (MCP-Executable)

Please perform a comprehensive health check of this Supabase database using the Supabase MCP tools.

IMPORTANT: After analysis, present your findings and proposed migrations (if any). Wait for my confirmation before applying any changes to the database.

Follow these steps:

1. Database Connection Health

Query connection statistics to identify:

• Active connections and connection pool usage
• Idle connections that may need cleanup
• Connection limits and current utilization percentage
• Long-running queries or transactions

2. Lock Analysis

Check for locks and blocking queries:

• Active locks on tables
• Blocking queries and what they're blocking
• Lock wait times
• Deadlock history (if available)

3. Table and Index Bloat

Analyze table and index bloat to identify:

• Tables with high bloat percentage (wasted space)
• Indexes with excessive bloat
• Tables that need VACUUM or REINDEX
• Overall bloat impact on database size

4. Vacuum and Autovacuum Status

Check vacuum operations:

• Last vacuum/autovacuum time for each table
• Tables that haven't been vacuumed recently
• Autovacuum settings and activity
• Dead tuple counts per table

5. Database Size and Growth

Query database size statistics:

• Total database size
• Largest tables and their growth trends
• Total index size vs table size ratio
• Schema-level size breakdown

6. Transaction ID Health

Check for transaction ID wraparound risk:

• Current transaction ID age
• Tables close to wraparound threshold
• Age of oldest transaction
• Autovacuum freeze settings

7. Replication Status

If using replication, check:

• Replication lag (if applicable)
• Replication slot status
• WAL (Write-Ahead Log) status
• Standby server health

8. Cache Hit Ratios

Analyze cache performance:

• Buffer cache hit ratio (should be >99%)
• Index cache hit ratio
• Tables with low cache hit rates

9. Query Statistics

Review query performance patterns:

• Most frequently executed queries
• Slowest average query times
• Queries with highest total time
• Queries causing most cache misses

10. Storage and Disk Usage

Check storage health:

• Disk space usage and available space
• Tablespace utilization
• Temporary file usage
• Archive/backup storage

11. Create Remediation Plan

For each health issue found, create SQL migrations or maintenance tasks:

• High bloat: Generate VACUUM FULL or REINDEX commands
• Blocking queries: Identify queries to optimize or cancel
• Connection leaks: Suggest connection pool adjustments
• Transaction ID issues: Force vacuum freeze if needed
• Low cache hits: Suggest shared_buffers adjustments or query optimization

12. Present Plan and Wait for Confirmation

Before applying any changes:

1. Show all proposed SQL migrations or maintenance commands
2. Explain the health issue each action addresses
3. Highlight any potential downtime or performance impacts
4. Wait for explicit confirmation before proceeding

13. Apply Fixes (After Confirmation)

Once confirmed, use execute_sql or apply_migration to apply fixes:

• Use apply_migration for DDL changes (CREATE INDEX, ALTER TABLE, etc.)
• Note: VACUUM cannot be run via Supabase MCP tools (requires non-transactional context)
• For VACUUM operations: Document in SQL file, rely on autovacuum, or run manually via Supabase dashboard
• Apply any schema optimizations
• Save all SQL to your project's migration folder (internaldocs/sql/)
• Regenerate TypeScript types if schema changed using generate_typescript_types

14. Re-run Health Checks

After applying fixes, re-run health check queries to verify:

• Bloat percentages decreased
• Connections normalized
• Vacuum operations completed
• Cache hit ratios improved

---

Output Format

Please provide:

1. Executive summary: Overall database health status (Healthy/Warning/Critical)
2. Detailed findings: Organized by category with severity levels
3. Metrics: Key health metrics with benchmarks (e.g., cache hit ratio, bloat %)
4. Remediation plan: SQL commands or migrations for each issue
5. Maintenance recommendations: Suggested regular maintenance tasks

---

Health Check Frequency

Recommended schedule:

• Weekly: Connection health, locks, cache hit ratios
• Monthly: Bloat analysis, vacuum status, query statistics
• Quarterly: Full health check including all categories
• As needed: After major migrations or when experiencing issues

---

Notes

• Only use Supabase MCP tools (execute_sql, apply_migration, generate_typescript_types)
• Focus exclusively on database-level health (connections, bloat, vacuum, etc.)
• Important: VACUUM cannot be executed via MCP (transaction block limitation) - document for manual execution or rely on autovacuum
• Some operations (VACUUM FULL, REINDEX) may require maintenance windows
• Do not include application-level monitoring
• Be cautious with operations that lock tables

PostgreSQL Query Tips

When querying PostgreSQL system tables, use the correct column names:

• pg_stat_user_tables uses relname (not tablename)
• pg_stat_user_indexes uses relname and indexrelname (not tablename and indexname)
• Use pg_tables for tablename and pg_indexes for indexname