> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Performance Monitoring Guide

> This guide establishes the performance monitoring process for the Encore OS database. Regular monitoring ensures optimal performance and early detection of iss…

**Version:** 1.0.0\
**Date:** 2026-01-16\
**Owner:** Platform Team

***

## Overview

This guide establishes the performance monitoring process for the Encore OS database. Regular monitoring ensures optimal performance and early detection of issues.

***

## Monitoring Schedule

### Weekly Review (Every Monday)

**Time:** Monday morning (before business hours)\
**Duration:** 30-45 minutes\
**Owner:** Platform Engineer on rotation

**Tasks:**

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)

**Time:** First Monday of each month\
**Duration:** 1-2 hours\
**Owner:** Platform Team Lead

**Tasks:**

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)

**Time:** First week of quarter\
**Duration:** 2-4 hours\
**Owner:** Platform Team

**Tasks:**

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](https://app.supabase.com)
2. Select the Encore OS project
3. Navigate to SQL Editor

### Step 2: Run Monitoring Queries

**Script Location:** `scripts/performance/monitoring-queries.sql`

Run each query section and document results:

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

**Red Flags to Watch For:**

| Issue               | Threshold                   | Action                           |
| ------------------- | --------------------------- | -------------------------------- |
| New slow query      | mean\_exec\_time > 1000ms   | Investigate, add index if needed |
| Unused index        | idx\_scan = 0 after 4 weeks | Consider removal                 |
| High dead tuples    | > 10% dead\_tuple\_percent  | Schedule manual VACUUM           |
| Low cache hit ratio | \< 90%                      | Investigate memory usage         |
| Growing table size  | > 20% growth in week        | Review for archival              |
| Idle in transaction | > 10 connections            | Investigate application issues   |

### Step 4: Document Findings

Create weekly review report:

**Location:** `reports/performance/weekly-review-YYYY-MM-DD.md`

**Template:**

```markdown theme={null}
# 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

If issues are found:

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

```sql theme={null}
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

```sql theme={null}
SELECT state, COUNT(*) 
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;
```

### VACUUM Status

```sql theme={null}
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

```sql theme={null}
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

| Script                 | Purpose           | Location             |
| ---------------------- | ----------------- | -------------------- |
| assessment-queries.sql | Full assessment   | scripts/performance/ |
| monitoring-queries.sql | Weekly monitoring | scripts/performance/ |

### Documentation

| Document                                  | Purpose            | Location             |
| ----------------------------------------- | ------------------ | -------------------- |
| SUPABASE\_PERFORMANCE\_RECOMMENDATIONS.md | Optimization guide | docs/migration/      |
| DATA\_ARCHIVAL\_PLAN.md                   | Archival strategy  | docs/migration/      |
| baseline-metrics-template.md              | Baseline capture   | reports/performance/ |

### Dashboards

| Dashboard          | Purpose            | Location                         |
| ------------------ | ------------------ | -------------------------------- |
| Supabase Dashboard | Database metrics   | app.supabase.com                 |
| System Health      | Application health | /settings/system-health/database |

***

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

**Recommended Times:**

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

**Notification:**

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

***

## References

* [Supabase Performance Guide](https://supabase.com/docs/guides/database/performance)
* [PostgreSQL Performance Tips](https://wiki.postgresql.org/wiki/Performance_Optimization)
* [Encore OS Performance Recommendations](/migration/SUPABASE_PERFORMANCE_RECOMMENDATIONS)

***

**Last Updated:** 2026-01-16\
**Review Schedule:** Quarterly