Performance Optimization

Optimize migration performance in your GitOps workflow.

Performance Optimization

Batch Large Data Migrations

For DML on large tables, process in batches:

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

-- 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);

Online Schema Migration

Configure gh-ost for zero-downtime MySQL migrations

Optimize Index Creation

Create indexes concurrently when possible:

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

-- Add timing to migration
\timing on
-- Migration SQL here
\timing off

Set expectations for production:

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

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

Troubleshooting

Solutions for common GitOps issues

Online Schema Migration

Configure gh-ost for zero-downtime changes

GitOps Overview

Return to GitOps overview