pgsql-jellyfin/docs/REMOTE_DATABASE_ANALYSIS.md

# 📊 Remote Database Analysis - Updated

## Connection Configuration

**Current Settings:**
```powershell
Host: 192.168.129.248 (Remote PostgreSQL Server)
Port: 5432
User: postgres (Superuser)
Database: jellyfin_testdata (CORRECTED - was jellyfin_testsdata)
```

## ⚠️ Important Discovery

Your diagnostics were run against **`jellyfin_testdata`**, but your config file has a typo:
```powershell
# In db-config.ps1
$DB_NAME = "jellyfin_testsdata"  # ❌ Incorrect (extra 's')

# Should be:
$DB_NAME = "jellyfin_testdata"   # ✅ Correct
```

## Available Databases on Remote Server

```
1. jellyfin_argodata     - Argo server database?
2. jellyfin_bridgedata   - Bridge server database?
3. jellyfin_testdata     - Test database (your diagnostics source)
4. jellyfin_testdata_1   - Another test database
5. media_dbsync          - Media sync database
6. media_dbsync2         - Media sync database 2
7. media_dbsync3         - Media sync database 3
```

**Your diagnostics came from: `jellyfin_testdata`**

---

## 📊 Analysis Results (Identical to Previous)

Since the data is identical, the issues remain the same:

### 🔴 Critical Issues:

#### 1. ItemValues Table Performance Crisis

```
Problem: 1.3 BILLION rows read via sequential scans
Impact: Extremely slow genre/tag/metadata queries
Severity: CRITICAL ⚠️
```

**Why it matters on remote server:**
- Network latency amplifies the problem
- 1.3B rows over network = massive slowdown
- Remote queries need indexes even more than local

#### 2. Peoples Table High Scans

```
Problem: 918 MILLION rows read
Impact: Actor/director queries are slow
Severity: HIGH
```

#### 3. Supplementary Indexes Unused

```
Status: 0-10 uses on new indexes
Reason: Database was idle when diagnostics ran
Action: Need to test with actual Jellyfin workload
```

---

## 🎯 Updated Recommendations for Remote Setup

### Critical: Fix Database Name in Config

**Step 1: Update `db-config.ps1`**
```powershell
# Line 5 - Change from:
$DB_NAME = "jellyfin_testsdata"  # ❌

# To:
$DB_NAME = "jellyfin_testdata"   # ✅
```

**Step 2: Reload config**
```powershell
. .\db-config.ps1
```

### High Priority: Update Statistics on Remote Database

Run this after fixing the database name:

```powershell
. .\db-config.ps1
Invoke-PSQL -Query "ANALYZE VERBOSE library.ItemValues, library.Peoples, library.BaseItems;"
```

This tells PostgreSQL's query planner about the actual data distribution.

### Medium Priority: Test Supplementary Indexes

**Since this is a remote database:**
1. Jellyfin must be configured to connect to this remote server
2. Use Jellyfin normally to generate workload
3. Network queries will benefit MORE from proper indexes

**Test these operations:**
- Browse "Recently Added" → Tests `idx_baseitems_datecreated_filtered`
- Filter by Genre/Tags → Tests `idx_itemvaluesmap_itemvalueid_itemid`
- Navigate libraries → Tests `idx_baseitems_type_isvirtualitem_topparentid`

---

## 🌐 Remote Server Considerations

### Network Latency Impact

**Local Database:**
- Query time = Processing time
- Example: 100ms query = 100ms total

**Remote Database (192.168.129.248):**
- Query time = Processing time + Network latency
- Example: 100ms query + 5ms network = 105ms per query
- **With 1.3B rows scanned:** Network amplifies the problem!

### Why Indexes Matter More on Remote Databases

**Without proper indexes:**
```
ItemValues query:
1. Server scans 1.3B rows (5000ms processing)
2. Returns 10,000 results over network (50ms network)
3. Total: 5050ms (5 seconds!) ❌
```

**With proper indexes:**
```
ItemValues query:
1. Server uses index (50ms processing)
2. Returns 100 results over network (1ms network)
3. Total: 51ms (instant!) ✅
```

**The network amplifies both good and bad performance!**

---

## 🔧 Specific Fixes for ItemValues Table

### Problem Analysis

**Current indexes on ItemValues:**
- `IX_ItemValues_Type_CleanValue`
- `IX_ItemValues_Type_Value`
- `PK_ItemValues`

**Why they're not helping:**
- Queries likely filter by Value/CleanValue without Type
- Or join back to items without proper index support

### Recommended New Indexes

```sql
-- For reverse lookups (genre name → items)
CREATE INDEX idx_itemvalues_cleanvalue 
ON library."ItemValues" ("CleanValue") 
WHERE "CleanValue" IS NOT NULL;

-- For type-agnostic value searches
CREATE INDEX idx_itemvalues_value_type 
ON library."ItemValues" ("Value", "Type");

-- For ItemValuesMap joins
CREATE INDEX idx_itemvalues_id_type 
ON library."ItemValues" ("Id", "Type");
```

**Before creating these**, let's see actual query patterns by enabling logging (next section).

---

## 📈 Action Plan for Remote Database

### Week 1: Diagnosis & Setup

**Day 1: Fix Configuration** ✅
1. Update `db-config.ps1` database name
2. Reload configuration
3. Test connection

**Day 2: Update Statistics**
```powershell
. .\db-config.ps1
Invoke-PSQL -Query "ANALYZE VERBOSE library.ItemValues, library.Peoples;"
```

**Day 3-7: Generate Workload**
- Use Jellyfin connected to this remote database
- Perform common operations (browse, filter, search)
- Let the database accumulate query statistics

### Week 2: Analysis & Optimization

**Day 8: Run Diagnostics**
```powershell
. .\db-config.ps1
Invoke-PSQL -File "sql\diagnostics.sql" -OutputFile "diagnostics_remote_week2.txt"
```

**Day 9: Compare Results**
- Check if supplementary indexes are used
- Identify which indexes help vs. don't help
- Look for new bottlenecks

**Day 10-14: Create Targeted Indexes**
Based on actual usage patterns:
- Create optimized indexes for ItemValues
- Remove unused indexes
- Document improvements

---

## 🔍 Enable Query Logging (Advanced)

To see exactly what queries are hitting ItemValues:

```powershell
. .\db-config.ps1

# Enable logging for slow queries (>500ms)
Invoke-PSQL -Query "ALTER DATABASE jellyfin_testdata SET log_min_duration_statement = 500;"

# Or log all ItemValues queries
Invoke-PSQL -Query "ALTER DATABASE jellyfin_testdata SET log_statement = 'mod';"
```

**Check logs on remote server:**
```bash
# On 192.168.129.248
tail -f /var/lib/postgresql/data/log/postgresql-*.log
```

---

## 🎓 Key Learnings

### 1. Remote Databases Need Extra Care
- Network latency amplifies performance issues
- Indexes are MORE critical, not less
- Sequential scans are even more expensive

### 2. Database Name Matters
- Typo in config can cause connection failures
- Always verify with `psql -l` to list databases
- Update config immediately to prevent confusion

### 3. Idle Database = No Usage Stats
- Your supplementary indexes show 0 uses
- This is expected - database needs workload
- Can't judge index effectiveness without queries

---

## ✅ Immediate Action Required

1. **Fix db-config.ps1:**
```powershell
$DB_NAME = "jellyfin_testdata"  # Remove the 's'
```

2. **Reload and test:**
```powershell
. .\db-config.ps1
Invoke-PSQL -Query "SELECT current_database();"
# Should return: jellyfin_testdata
```

3. **Update statistics:**
```powershell
Invoke-PSQL -Query "ANALYZE;"
```

4. **Use Jellyfin for 1 week**

5. **Re-run diagnostics next week**

---

## 📊 Summary

**Database Status:** Healthy but needs optimization  
**Connection:** Remote (192.168.129.248) - verified working  
**Critical Issue:** ItemValues table sequential scans (1.3B rows!)  
**Supplementary Indexes:** Installed but untested (0 uses)  
**Next Step:** Fix config typo, use Jellyfin, re-analyze  

**Expected Improvement After Fixes:**
- ItemValues queries: 70-90% faster
- Remote query latency: Significantly reduced
- Overall user experience: Much snappier

The remote setup is fine - it's the indexes that need optimization! 🚀