Overview

CRITICAL: Destructive Operations Warning

The Table Recreation Pattern involves DESTRUCTIVE operations that can result in DATA LOSS.

Table recreation requires dropping the original table, which is irreversible. If any step fails after the original table is dropped, data may be permanently lost.

Mandatory User Verification Requirements

Agents MUST obtain explicit user approval before executing migrations on live tables:

1. MUST present the complete migration plan to the user before any execution
2. MUST clearly state that this operation will DROP the original table
3. MUST confirm the user has a current backup or accepts the risk of data loss
4. MUST verify with the user at each checkpoint before proceeding:
   • Before creating the new table structure
   • Before beginning data migration
   • Before dropping the original table (CRITICAL CHECKPOINT)
   • Before renaming the new table
5. MUST NOT proceed with any destructive action without explicit user confirmation
6. MUST recommend performing migrations on non-production environments first

Risk Acknowledgment

Before proceeding, the user MUST confirm:

• Not done: They understand this is a destructive operation
• Not done: They have a backup of the table data (or accept the risk)
• Not done: They approve the agent to execute each step with verification
• Not done: They understand the migration cannot be automatically rolled back after DROP TABLE

Table Recreation Operations

The following ALTER TABLE operations MUST use the Table Recreation Pattern:

Note: The following operations ARE supported directly. Each is still subject to the one-DDL-per-transaction rule — issue each as its own psql -c invocation (or its own BEGIN/COMMIT block when scripted):

• ALTER TABLE ... RENAME COLUMN - Rename a column
• ALTER TABLE ... RENAME TO - Rename a table
• ALTER TABLE ... ADD COLUMN - Add a new column

CREATE INDEX ASYNC Syntax (DSQL)

DSQL accepts a narrow subset of standard PostgreSQL CREATE INDEX syntax. The skill enforces CREATE INDEX ASYNC everywhere; additional clauses behave as follows (validated against a live DSQL cluster):

Without ASYNC, DSQL rejects with ERROR: unsupported mode. please use CREATE INDEX ASYNC. — useful to grep for in failure logs.

If the migration source (e.g., a vanilla PostgreSQL dump) relies on partial indexes or non-btree access methods, the pattern MUST be rewritten — denormalize via a filter column, or add a CHECK constraint and a covering composite index. Document the workaround in the migration plan.

Table Recreation Pattern Overview

MUST follow this sequence with user verification at each step:

1. Plan & Confirm - MUST present migration plan and obtain user approval to proceed
2. Validate - Check data compatibility with new structure; MUST report findings to user
3. Create - Create new table with desired structure; MUST verify with user before execution
4. Migrate - Copy data (batched for tables > 3,000 rows); MUST report progress to user
5. Verify - Confirm row counts match; MUST present comparison to user
6. Swap - CRITICAL: MUST obtain explicit user confirmation before DROP TABLE
7. Re-index - Recreate indexes using ASYNC; MUST confirm completion with user

Transaction Rules

Defaults below; verify against the live limits via the AWS MCP Server’s aws___search_documentation if available (aurora dsql transaction limits), or read the DSQL documentation (opens in a new tab) directly:

• MUST batch migrations exceeding 3,000 row mutations
• PREFER batches of 500-1,000 rows for optimal throughput
• MUST respect 10 MiB data size per transaction
• MUST respect 5-minute transaction duration

Common Verify & Swap Pattern

All migrations end with this pattern (referenced in column-operations.md and constraint-operations.md).

CRITICAL: MUST obtain explicit user confirmation before DROP TABLE step.

Recovery — Row Counts Do Not Match

When target_table_new has fewer rows than target_table, treat the migration as incomplete. The original table still holds the authoritative data, so recovery is always possible — MUST NOT proceed with DROP TABLE until the counts agree.

1. Diagnose — find the missing rows by comparing ranges (for cursor-based migrations, query target_table for IDs greater than MAX(id) in target_table_new; for OFFSET-based, check which batch dropped rows by re-running the SELECT portion of each batch and comparing counts).

2. Retry the missing batches — insert only the gap rows into target_table_new. Filter out already-migrated rows to avoid PK collisions (which would roll back the entire batch):

   sqlCopy

   -- Cursor-based: only insert rows beyond what was already migrated
   INSERT INTO target_table_new (id, col1, col2)
   SELECT id, col1, col2 FROM target_table
   WHERE id > (SELECT COALESCE(MAX(id), 0) FROM target_table_new)
   ORDER BY id LIMIT 1000;
   
   -- For non-sequential gaps, use NOT EXISTS:
   INSERT INTO target_table_new (id, col1, col2)
   SELECT id, col1, col2 FROM target_table
   WHERE NOT EXISTS (
     SELECT 1 FROM target_table_new WHERE target_table_new.id = target_table.id
   )
   ORDER BY id LIMIT 1000;

3. If a type cast or constraint rejected rows — migration cannot complete until the data is reconciled. Fix the source data in target_table (or adjust the new table’s constraint), then re-run the missing batches.

4. Escape hatch — if diagnosis stalls, drop target_table_new and restart the migration from a clean slate. The original table is untouched, so no data is at risk.

Re-run the count comparison after each retry. Only proceed to DROP TABLE once COUNT(*) matches exactly.

Best Practices Summary

User Verification (CRITICAL)

• MUST present complete migration plan to user before any execution
• MUST obtain explicit user confirmation before DROP TABLE operations
• MUST verify with user at each checkpoint during migration
• MUST NOT proceed with destructive actions without explicit user approval
• MUST recommend testing migrations on non-production data first
• MUST confirm user has backup or accepts data loss risk

Technical Requirements

• MUST validate data compatibility before type changes
• MUST batch tables exceeding 3,000 rows
• MUST verify row counts before and after migration
• MUST recreate indexes after table swap using ASYNC
• MUST NOT drop original table until new table is verified
• PREFER cursor-based batching for very large tables
• PREFER batches of 500-1,000 rows for optimal throughput