Reclaiming Space for Truncated Aurora MySQL Tables

When working with Aurora MySQL, it’s common to assume that running a TRUNCATE TABLE will completely clear out space and return your database to its pre-load size. Unfortunately, that’s not quite how it works — and it often surprises people during large migrations.

Here’s why truncating doesn’t always free space, and what you should do instead.

TRUNCATE vs. Space Reclamation

In MySQL (and Aurora MySQL), TRUNCATE TABLE is a fast operation that:

• Deletes all rows from a table.
• Resets the auto-increment counter.
• Removes and recreates the underlying table definition internally.

However, with InnoDB tables, the physical storage file is not always shrunk automatically. Instead:

• Pages inside the tablespace are marked as free.
• The file size on disk (or Aurora’s volume usage) often stays the same.

That means your database volume won’t shrink even if the table is empty.

The Role of OPTIMIZE TABLE

To actually reclaim space after large deletes or truncations, you need to run:

OPTIMIZE TABLE your_table;

What this does:

• Creates a new copy of the table with active rows.
• Rebuilds indexes.
• Frees up unused pages and defragments data.
• Releases the unused space back to Aurora’s volume.

In practice, many users see 20–40% of their allocated storage reclaimed after running OPTIMIZE TABLE on heavily churned datasets.

Aurora-Specific Gotchas

1. Shared Storage Model
   Aurora uses a distributed storage layer, so file-per-table semantics can be a bit confusing. Even with innodb_file_per_table=ON, the space isn’t automatically released at the cluster volume level until the table is rebuilt.
2. Performance Impact
   • OPTIMIZE TABLE is blocking for writes and can run for minutes to hours on very large tables.
   • Plan downtime or run during low-traffic windows.
3. Monitoring
   Watch the VolumeBytesUsed CloudWatch metric before and after running OPTIMIZE. If you only truncate, the metric won’t move. After optimize, you’ll see a real decrease.

Best Practices

• For staging or migrations: Always follow TRUNCATE with OPTIMIZE TABLE to reclaim disk space.
• For production: Schedule optimizations for off-peak hours, and test on smaller tables first.
• For ongoing operations: If your workload does lots of churn (bulk loads/deletes), regular optimization may be necessary.

Final Takeaway

In Aurora MySQL, TRUNCATE TABLE clears rows but doesn’t guarantee space reclamation. To actually shrink your database footprint, you need to rebuild the table with OPTIMIZE TABLE. Think of it as the “vacuum” step — without it, your cluster will keep carrying dead weight.