wjones
78c8d4256c
- Moved all documentation to docs/ and updated README with categorized links and new docs/INDEX.md - Added HOW_TO_SWITCH_DATABASE.md and several new analysis/action docs - Introduced db-config.ps1 for centralized DB config; all scripts now use it for easy DB switching - Added db-quick.ps1 for interactive diagnostics and index management - Updated Add-All-Indexes.bat to use db-config.ps1 - Added Fix-ItemValues-Performance.ps1 to create 3 critical indexes on ItemValues, addressing 1.3B row seq scan issue - Updated performance_indexes.sql with new ItemValues indexes and ANALYZE - Updated diagnostics.sql and database_report.txt for improved output and clarity - All scripts and docs now reference the new config and index optimization workflow
113 lines
3.8 KiB
Markdown
113 lines
3.8 KiB
Markdown
# Merge Migrations - Instructions
|
|
|
|
## Why Merge?
|
|
|
|
You have 3 migrations that should be combined into one InitialCreate:
|
|
1. `20260226165957_InitialCreate` - Creates tables
|
|
2. `20260226170000_AddBasePerformanceIndexes` - Adds 18 base indexes
|
|
3. `20260227000000_AddSupplementaryIndexes` - Adds 5 supplementary indexes
|
|
|
|
Since you're on `pgsql_testing_branch` and likely haven't deployed to production yet, we can consolidate these.
|
|
|
|
## Option 1: Recommended - Use EF Core to Regenerate
|
|
|
|
This is the safest way and EF Core will generate everything correctly.
|
|
|
|
### Step 1: Drop and Recreate Test Database
|
|
|
|
```powershell
|
|
# Connect to postgres
|
|
psql -U jellyfin -d postgres
|
|
|
|
# Drop the test database
|
|
DROP DATABASE IF EXISTS jellyfin_testsdata;
|
|
DROP DATABASE IF EXISTS jellyfin;
|
|
|
|
# Create fresh database
|
|
CREATE DATABASE jellyfin;
|
|
\q
|
|
```
|
|
|
|
### Step 2: Delete Old Migrations
|
|
|
|
```powershell
|
|
# Delete the migration files
|
|
Remove-Item "src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\Migrations\20260226165957_InitialCreate.cs"
|
|
Remove-Item "src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\Migrations\20260226165957_InitialCreate.Designer.cs"
|
|
Remove-Item "src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\Migrations\20260226170000_AddBasePerformanceIndexes.cs"
|
|
Remove-Item "src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\Migrations\20260227000000_AddSupplementaryIndexes.cs"
|
|
Remove-Item "src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\Migrations\JellyfinDbContextModelSnapshot.cs"
|
|
```
|
|
|
|
### Step 3: Create New InitialCreate Migration
|
|
|
|
This migration should include everything (but without the indexes SQL since EF Core auto-generates those from the model).
|
|
|
|
However, since EF Core doesn't know about the custom-named indexes (baseitems_communityrating_idx, etc.), we need to add those via SQL.
|
|
|
|
Let me create a new consolidated migration file for you:
|
|
|
|
---
|
|
|
|
## Option 2: Quick Fix - Just Add Indexes to InitialCreate
|
|
|
|
Since the InitialCreate is huge, it's easier to append the index creation at the end.
|
|
|
|
### Step 1: Backup Current Migration
|
|
|
|
```powershell
|
|
Copy-Item "src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\Migrations\20260226165957_InitialCreate.cs" "src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\Migrations\20260226165957_InitialCreate.cs.backup"
|
|
```
|
|
|
|
### Step 2: Add Index Creation Code
|
|
|
|
I'll create a snippet you can add to the end of the `Up()` method in InitialCreate.
|
|
|
|
### Step 3: Delete Other Migrations
|
|
|
|
```powershell
|
|
Remove-Item "src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\Migrations\20260226170000_AddBasePerformanceIndexes.cs"
|
|
Remove-Item "src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\Migrations\20260227000000_AddSupplementaryIndexes.cs"
|
|
```
|
|
|
|
### Step 4: Update Model Snapshot
|
|
|
|
You'll need to regenerate the model snapshot since the migration timestamps changed.
|
|
|
|
---
|
|
|
|
## Option 3: Recommended for Testing - Just Use the SQL Scripts
|
|
|
|
Keep the migrations as-is, but create a single SQL script that runs all index creation.
|
|
|
|
This is the **fastest** and **safest** for your testing:
|
|
|
|
```powershell
|
|
# Create combined script
|
|
Get-Content sql\add_base_performance_indexes.sql, sql\schema_init\10_create_supplementary_indexes.sql | Out-File sql\all_indexes.sql
|
|
|
|
# Run it
|
|
psql -U jellyfin -d jellyfin -f sql\all_indexes.sql
|
|
```
|
|
|
|
---
|
|
|
|
## My Recommendation
|
|
|
|
**For testing (your current situation):**
|
|
|
|
Use **Option 3** - Keep migrations separate, use SQL scripts for quick testing.
|
|
|
|
**For production (later):**
|
|
|
|
Use **Option 1** - Regenerate a clean InitialCreate with EF Core, then add custom SQL for the special-named indexes.
|
|
|
|
---
|
|
|
|
Would you like me to:
|
|
1. ✅ Create a combined SQL script (Option 3 - fastest)
|
|
2. ✅ Create code snippet to add to InitialCreate (Option 2)
|
|
3. ✅ Create a new consolidated migration from scratch (Option 1 - cleanest but most work)
|
|
|
|
Let me know which approach you prefer!
|