> ## Documentation Index
> Fetch the complete documentation index at: https://docs.bytebase.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Performance Optimization
Optimize migration performance in your GitOps workflow.
## Performance Optimization
### Batch Large Data Migrations
For DML on large tables, process in batches:
```sql theme={null}
-- 050__archive_old_logs_dml.sql
-- Instead of single large DELETE:
-- ❌ DELETE FROM logs WHERE created_at < '2024-01-01';
-- Use batched approach:
DO $$
DECLARE
batch_size INT := 10000;
deleted_count INT;
BEGIN
LOOP
DELETE FROM logs
WHERE id IN (
SELECT id FROM logs
WHERE created_at < '2024-01-01'
LIMIT batch_size
);
GET DIAGNOSTICS deleted_count = ROW_COUNT;
EXIT WHEN deleted_count < batch_size;
COMMIT; -- If supported
PERFORM pg_sleep(0.1); -- Throttle
END LOOP;
END $$;
```
**Benefits:**
* Reduces lock contention
* Prevents transaction log bloat
* Allows monitoring progress
* Can be paused/resumed
### Use Online Schema Changes for Large Tables
For MySQL tables > 1M rows:
```sql theme={null}
-- Instead of:
-- ❌ 010__add_index.sql
CREATE INDEX idx_users_email ON users(email);
-- Use gh-ost:
-- ✅ 010__add_index_ghost.sql
ALTER TABLE users ADD INDEX idx_users_email (email);
```
Configure gh-ost for zero-downtime MySQL migrations
### Optimize Index Creation
Create indexes concurrently when possible:
```sql theme={null}
-- PostgreSQL: Non-blocking index creation
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- MySQL: Use online DDL
ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE;
```
**Note:** `CONCURRENTLY` requires:
* Cannot be in transaction block
* Takes longer than regular index creation
* May fail if concurrent writes conflict
### Timing Considerations
**Best times for migrations:**
* ✅ Low-traffic hours (nights, weekends)
* ✅ During maintenance windows
* ✅ After application deployments
**Avoid:**
* ❌ Peak business hours
* ❌ During critical operations (month-end, sales events)
* ❌ Without communication to stakeholders
### Monitor Migration Performance
Track execution time during development:
```sql theme={null}
-- Add timing to migration
\timing on
-- Migration SQL here
\timing off
```
Set expectations for production:
```sql theme={null}
-- 015__large_migration.sql
-- Expected duration: 5-10 minutes
-- Locks: users table (write lock)
-- Tested on: 2M row table
```
## Performance Monitoring
### Track Migration Metrics
Monitor these metrics:
| Metric | Target | Alert Threshold |
| --------------------- | ---------- | --------------- |
| Migration duration | \< 5 min | > 10 min |
| Lock wait time | \< 1 sec | > 5 sec |
| Rows affected | Documented | > Expected |
| Rollback success rate | 100% | \< 100% |
### Database Health Checks
Before and after migrations:
```sql theme={null}
-- Check table sizes
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- Check index usage
SELECT
schemaname,
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 100
ORDER BY pg_relation_size(indexrelid) DESC;
```
***
## Next Steps
Solutions for common GitOps issues
Configure gh-ost for zero-downtime changes
Return to GitOps overview