# PostgreSQL Cleanup Script for bots_generatedclip

This script cleans up the `bots_generatedclip` table by:
1. Setting `prompt_text` column to empty string (`''`)
2. Setting the `prompt` key in the `metadata` JSONB field to empty string (`""`)

## Batch Size Recommendations

For a **9TB production table**, these are the recommended batch sizes:

### Configuration Parameters

| Parameter | Default | Recommended for 9TB | Description |
|-----------|---------|-------------------|-------------|
| `FETCH_BATCH_SIZE` | 10,000 | **10,000-20,000** | Number of IDs to fetch from DB at once |
| `UPDATE_BATCH_SIZE` | 500 | **500-1,000** | Number of records to update per transaction |
| `NUM_WORKERS` | CPU count | **8-16** | Number of parallel workers |

### Why These Batch Sizes?

1. **FETCH_BATCH_SIZE (10,000-20,000)**:
   - Fetches only IDs, not full records, so this can be larger
   - Uses ID-based cursor to avoid OFFSET performance issues
   - Optimized query only selects rows that need cleanup

2. **UPDATE_BATCH_SIZE (500-1,000)**:
   - Small enough to avoid long-running transactions
   - Prevents excessive lock contention on production
   - Each update transaction commits quickly
   - If something fails, only small batch needs retry

3. **NUM_WORKERS (8-16)**:
   - Parallelizes updates across multiple connections
   - Don't go too high to avoid overwhelming the database
   - Each worker gets its own connection

## Performance Estimates

For a 9TB table with ~billions of rows:
- **Speed**: ~5,000-10,000 records/sec (depends on DB instance size)
- **Time**: Could take several hours to days for full cleanup
- **Impact**: Minimal - small transactions, no table locks

## Usage

### 1. Test on a Small Subset First

Before running on the entire table, test with limited data:

```bash
# Test query to see how many records need cleanup
psql -h your-db-host -U your-user -d suno_main -c "
SELECT COUNT(*) FROM bots_generatedclip 
WHERE (prompt_text IS NOT NULL AND prompt_text != '') 
   OR (metadata->>'prompt' IS NOT NULL AND metadata->>'prompt' != '');
"
```

### 2. Run with Default Settings

```bash
export DATABASE_URL="postgresql://user:pass@host:5432/dbname"
go run cleanup.go
```

### 3. Run with Custom Batch Sizes

```bash
export DATABASE_URL="postgresql://user:pass@host:5432/dbname"
export NUM_WORKERS=12
export FETCH_BATCH_SIZE=15000
export UPDATE_BATCH_SIZE=1000

go run cleanup.go
```

### 4. Monitor Progress

The script outputs:
- Progress percentage
- Records processed/updated
- Speed (records/sec)
- ETA (estimated time remaining)
- Error count

Example output:
```
2025/10/07 12:00:00 Progress: 2.50% (50000/2000000) | Speed: 5500 rec/s | Updated: 50000 | Errors: 0 | ETA: 6m30s
```

## Database Impact Considerations

### ✅ Safe Features

- **ID-based cursor**: No OFFSET, avoids slow sequential scans
- **Small transactions**: Each batch commits separately
- **Row-level locks only**: No table locks
- **Optimized query**: Only selects rows that need cleanup
- **Graceful failure**: If worker crashes, only small batch lost

### ⚠️ Things to Monitor

1. **Replication Lag**: Updates generate WAL logs that replicas must process
2. **Disk I/O**: Updates require writing to disk
3. **Connection Pool**: Script uses NUM_WORKERS + 2 connections
4. **Autovacuum**: Large updates may trigger autovacuum

### Production Safety Tips

1. **Start Small**: Begin with 2-4 workers, monitor DB metrics
2. **Scale Up**: If DB handles load well, increase workers
3. **Off-Peak Hours**: Run during low-traffic periods if possible
4. **Monitor Metrics**:
   - CPU usage
   - Disk I/O
   - Connection count
   - Replication lag
5. **Pause if Needed**: Stop script (Ctrl+C), it will resume from last ID

## How It Works

1. **Fetches IDs**: Selects IDs of records that need cleanup using efficient query
2. **Batches Updates**: Groups IDs into batches of UPDATE_BATCH_SIZE
3. **Parallel Workers**: Multiple workers process batches concurrently
4. **Transactional**: Each batch is updated in a single transaction
5. **Cursor-based**: Uses `WHERE id > $lastID` to efficiently paginate

## SQL Query Used

```sql
-- Sets prompt_text to empty and sets 'prompt' key in JSONB to empty string
UPDATE bots_generatedclip 
SET prompt_text = '', 
    metadata = jsonb_set(metadata, '{prompt}', '""'::jsonb, true)
WHERE id = ANY($1)
```

The `jsonb_set()` function in PostgreSQL sets a value at the specified path in a JSONB object.

## Recovery from Interruption

If the script is interrupted (Ctrl+C, crash, etc.):
- Already committed batches are done
- Script can be restarted - it will skip already cleaned records
- Uses ID cursor, so it continues from where it left off

## Dry Run Option

To see what would be updated without making changes, you can modify the script to use a SELECT instead of UPDATE:

```sql
SELECT id FROM bots_generatedclip 
WHERE id = ANY($1)
  AND ((prompt_text IS NOT NULL AND prompt_text != '') 
       OR (metadata->>'prompt' IS NOT NULL AND metadata->>'prompt' != ''))
```

## Recommended Approach for 9TB Production Table

1. **Test on replica first** (if available)
2. **Start conservative**: 4 workers, 500 batch size
3. **Monitor for 15 minutes**: Check DB metrics
4. **Scale up if healthy**: Increase to 8-12 workers, 1000 batch size
5. **Run during off-peak**: Less impact on production traffic
6. **Set up alerts**: Monitor replication lag and DB CPU

## Rollback

⚠️ **Important**: This script **modifies data in place**. There is no automatic rollback.

Before running, consider:
1. Taking a snapshot/backup (for critical data)
2. Testing the exact query on a replica first
3. Verifying that clearing these fields is the intended behavior

To manually verify after running:
```sql
-- Check that prompt_text is empty
SELECT COUNT(*) FROM bots_generatedclip WHERE prompt_text != '';

-- Check that metadata 'prompt' field is empty
SELECT COUNT(*) FROM bots_generatedclip 
WHERE metadata->>'prompt' IS NOT NULL AND metadata->>'prompt' != '';
```

Both queries should return 0 when cleanup is complete.