# DDL Migrations: Batched Migration Pattern

**REQUIRED for tables exceeding 3,000 rows.**

For the full Table Recreation Pattern and verify & swap steps, see [overview.md](overview.md).

---

## Batch Size Rules

- **PREFER batches of 500-1,000 rows** for optimal performance
- Smaller batches reduce lock contention and enable better concurrency

---

## OFFSET-Based Batching

```sql
SELECT COUNT(*) as total FROM target_table;
-- Calculate: batches_needed = CEIL(total / 1000)

-- Batch 1
INSERT INTO target_table_new (id, col1, col2)
SELECT id, col1, col2 FROM target_table
ORDER BY id LIMIT 1000 OFFSET 0;

-- Batch 2
INSERT INTO target_table_new (id, col1, col2)
SELECT id, col1, col2 FROM target_table
ORDER BY id LIMIT 1000 OFFSET 1000;
-- Continue until all rows migrated...
```

---

## Cursor-Based Batching (Preferred for Large Tables)

Better performance than OFFSET for very large tables:

```sql
-- First batch
INSERT INTO target_table_new (id, col1, col2)
SELECT id, col1, col2 FROM target_table
ORDER BY id LIMIT 1000;

-- Get last processed ID
SELECT MAX(id) as last_id FROM target_table_new;

-- Subsequent batches
INSERT INTO target_table_new (id, col1, col2)
SELECT id, col1, col2 FROM target_table
WHERE id > 'last_processed_id'
ORDER BY id LIMIT 1000;
```

---

## Progress Tracking

```sql
SELECT (SELECT COUNT(*) FROM target_table_new) as migrated,
       (SELECT COUNT(*) FROM target_table) as total;
```

---

## Error Handling

### Pre-Migration Checks

1. **Verify table exists**

   ```sql
   SELECT table_name FROM information_schema.tables
   WHERE table_schema = 'public' AND table_name = 'target_table';
   ```

2. **Verify DDL permissions**

### Data Validation Errors

**MUST abort migration and report** when:

- Type conversion would fail
- Value truncation would occur
- NOT NULL constraint would be violated

```sql
-- Find problematic rows
SELECT id, problematic_column FROM target_table
WHERE problematic_column !~ '^-?[0-9]+$' LIMIT 100;
```

### Recovery from Failed Migration

```sql
-- Check table state
SELECT table_name FROM information_schema.tables
WHERE table_name IN ('target_table', 'target_table_new');
```

- **Both tables exist:** Original safe → `DROP TABLE IF EXISTS target_table_new` and restart
- **Only new table exists:** Verify count, then complete rename