Managing large datasets can be a balancing act between performance and storage. Keeping your “hot” data lean is critical for speed and memory efficiency. But what about the “cold” data you still need to retain?
Partition Exchange Archiving a simple, production-ready strategy that lets you instantly move old data out of your primary tables without rewriting rows or causing major locks. It’s the smart way to archive.
🔥 What Is MySQL HeatWave
MySQL HeatWave is a fully managed MySQL service that unifies transactions, analytics, Lakehouse, machine learning, and generative AI in a single MySQL-compatible engine. It uses a massively parallel, in-memory engine to accelerate analytic queries, removing the need for separate ETL pipelines and external data warehouses.
🧠 The Strategy: Metadata-Only Archiving
This pattern takes advantage of MySQL’s ability to exchange table partitions with unpartitioned tables. It’s fast because the operation is metadata-only no physical row copies involved.
Here’s the high-level flow:
1. Partition your table by RANGE(order_date)
2. Exchange an old partition with an archive table
3. Dump the archive to disk or object storage (backup step)
4. Truncate the archived table (cleanup step)
⚙️ Step-by-Step Implementation
✅ 1. Create the Partitioned Table
We’ll start by defining the live orders table, partitioned monthly by order_date. We’ll use a single AUTO_INCREMENT primary key to keep things clean.
SQL:
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL,
total DECIMAL(10, 2) NOT NULL
)
PARTITION BY RANGE (TO_DAYS(order_date)) (
PARTITION p202201 VALUES LESS THAN (TO_DAYS(‘2022-02-01’)),
PARTITION p202202 VALUES LESS THAN (TO_DAYS(‘2022-03-01’)),
PARTITION p202203 VALUES LESS THAN (TO_DAYS(‘2022-04-01’)),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
💡 Partitioning by TO_DAYS(order_date) ensures consistency in range boundaries.
🔢 Seed Some Sample Data
SQL:
INSERT INTO orders (order_date, customer_id, total) VALUES
(‘2022-01-15’, 1, 100.00),
(‘2022-01-20’, 2, 200.00),
(‘2022-02-01’, 3, 300.00),
(‘2022-02-15’, 4, 400.00),
(‘2022-03-01’, 5, 500.00);
🗄️ 2. Create the Archive Table (No Indexes)
We’ll create the archive table using a simple trick: selecting from the orders table using a condition that always fails. This creates an unpartitioned, index-free clone ideal for cold storage or export.
SQL:
— Create archive table with same structure, no data or indexes
CREATE TABLE orders_archive AS SELECT * FROM orders WHERE NULL;
This gives you:
• ✅ Identical columns
• ❌ No rows
• ❌ No indexes
• ❌ No partitioning
• ✅ Full compatibility for EXCHANGE PARTITION
⚡ 3. Exchange the Partition (Instant & Lock-Free)
Now we’ll move an entire month’s data into the archive table instantly using a metadata-only operation:
SQL:
ALTER TABLE orders EXCHANGE PARTITION p202201 WITH TABLE orders_archive WITH VALIDATION;
Notes:
• WITH VALIDATION ensures only correct-range rows are exchanged.
• You can use WITHOUT VALIDATION in performance-critical pipelines if you’ve pre-validated the range.
🧪 4. Verify the Swap
After the exchange, confirm that the partition is empty and the archive table contains the correct data.
SQL:
— Check that the partition is now empty
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = ‘orders’;
— Check the archive table
SELECT COUNT(*) FROM orders_archive;
SELECT MIN(order_date), MAX(order_date) FROM orders_archive;
💾 5. Backup the Archive (MySQL Shell on a Jump VM)
This is the backup mechanism you must not skip.
Now that the archive table contains the old data, we can dump it to disk or cloud storage using MySQL Shell on a jump VM.
In MySQL Shell (JS mode):
JS:
util.dumpTables(
“test_arch”, /* schema */
[“orders_archive”], /* tables */
“/Folder/BackupFolder/”, /* dump target; could be a bucket mount or local path */
{ threads: 4, ocimds: true, compatibility: [“strip_definers”] }
);
This supports:
• ✅ Local disk
• ✅ Mounted NFS / OCI bucket via FUSE
• ✅ Direct upload to OCI Object Storage via instance principal (ocimds: true)
🧹 6. Clean Up or Flow to the Lake (Truncate Mechanism)
Once the archive is safely backed up, you can free the space so the same archive table can be reused:
SQL:
TRUNCATE TABLE orders_archive;
At this point:
• The live partition (e.g., January 2022) has been emptied via EXCHANGE.
• The historical data has been backed up to disk or object storage.
• The archive table is empty again, ready for the next partition exchange.
🚀 Why This Rocks with MySQL HeatWave
• ⚡ Instant performance: EXCHANGE PARTITION is metadata-only.
• 💪 Lock-safe: Avoids long-running DELETEs or row-by-row archival jobs.
• 🧠 Memory-efficient: Smaller in-memory tables = faster HeatWave analytics.
• 🔁 Easy automation: Monthly events + MySQL Shell jobs = zero-touch archiving.
• 📜 Compliance-friendly: Gives you a clear, repeatable archive strategy for regulators.
Start archiving smarter. Start archiving faster. 🔨🤖🔧