Database Performance Monitoring Guide

​

Overview

​

Monitoring Schedule

​

Weekly Review (Every Monday)

1. Run monitoring queries
2. Review slow queries
3. Check connection status
4. Verify VACUUM status
5. Document findings
6. Create action items if needed

​

Monthly Review (First Monday of Month)

1. Complete weekly review
2. Trend analysis (compare to previous month)
3. Index usage audit
4. Storage growth review
5. Update baseline metrics if significant changes

​

Quarterly Review (January, April, July, October)

1. Complete monthly review
2. Full baseline capture
3. Review archival candidates
4. Performance optimization planning
5. Update documentation

​

Weekly Review Process

​

Step 1: Access Supabase Dashboard

1. Open Supabase Dashboard: https://app.supabase.com
2. Select the Encore OS project
3. Navigate to SQL Editor

​

Step 2: Run Monitoring Queries

1. Slow Queries - Queries with mean_exec_time > 1000ms
2. Unused Indexes - Indexes with idx_scan = 0
3. Connection Status - Active/idle connection counts
4. VACUUM Status - Tables with high dead tuple counts
5. Table Sizes - Track storage growth
6. Long-Running Queries - Queries running > 30 seconds
7. Cache Hit Ratio - Should be > 90%

​

Step 3: Analyze Results

​

Step 4: Document Findings

# Weekly Performance Review

**Date:** YYYY-MM-DD
**Reviewer:** [Name]

## Summary
- [ ] No issues found
- [ ] Issues identified (see below)

## Slow Queries
[List any new slow queries]

## Index Status
[Note any unused indexes or missing indexes]

## Connection Status
- Active: [N]
- Idle: [N]
- Idle in Transaction: [N]

## VACUUM Status
[List tables needing attention]

## Action Items
- [ ] [Action 1]
- [ ] [Action 2]

## Notes
[Additional observations]

​

Step 5: Create Action Items

1. Create GitHub issue for tracking
2. Assign to appropriate team member
3. Set priority based on impact
4. Link to weekly review report

​

Monitoring Queries Quick Reference

​

Slow Queries

SELECT LEFT(query, 100), calls, mean_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000
ORDER BY mean_exec_time DESC
LIMIT 20;

​

Connection Status

SELECT state, COUNT(*) 
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;

​

VACUUM Status

SELECT tablename, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

​

Database Size

SELECT pg_size_pretty(pg_database_size(current_database()));

​

Escalation Process

​

Level 1: Platform Engineer

• Weekly monitoring
• Minor optimizations
• Index additions

​

Level 2: Platform Team Lead

• Performance degradation > 20%
• New slow queries affecting users
• Connection pool issues

​

Level 3: Engineering Manager

• Database outage
• Critical performance issues
• Major architecture decisions

​

Tools & Resources

​

Scripts

​

Documentation

​

Dashboards

​

Troubleshooting

​

Slow Query Investigation

1. Get full query from pg_stat_statements
2. Run EXPLAIN ANALYZE on the query
3. Check for missing indexes
4. Review table statistics (ANALYZE)
5. Consider query rewrite if needed

​

High Connection Count

1. Check for connection leaks in application
2. Review idle connections
3. Check for long-running transactions
4. Verify connection pool settings

​

VACUUM Issues

1. Check auto-vacuum settings
2. Identify high-churn tables
3. Schedule manual VACUUM during low-traffic
4. Consider table partitioning for large tables

​

Maintenance Windows

• Index creation: Sunday 2-4 AM
• Manual VACUUM: Sunday 4-6 AM
• Major migrations: Weekend with advance notice

• Notify team via Slack #platform channel
• Update status page if user impact expected

​

References

• Supabase Performance Guide
• PostgreSQL Performance Tips
• Encore OS Performance Recommendations