/aurora-dsql

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.

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

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:

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

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

   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

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

Recovery from Failed Migration

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