Optimizing Data Storage: InnoDB vs. ARCHIVE for Hot/Cold Tiering

Understanding database internals, beyond simply querying data, is crucial for building scalable systems. This article demystifies the contrasting philosophies behind InnoDB and ARCHIVE storage engines in MySQL and introduces a practical approach to combine them using table partitioning for effective hot/cold data tiering.

InnoDB: Page-Based Transactional Storage

InnoDB, the default MySQL engine, is optimized for high-speed, multi-user, read/write transactional workloads. Its architecture is built on a strict, multi-tiered hierarchy designed for performance and data integrity. Key aspects include:

• Tablespaces: High-level logical containers, with `innodb_file_per_table` separating each table into its own `.ibd` file.
• Extents: 1MB chunks of contiguous pages to prevent fragmentation and keep sequential data physically close on disk.
• Pages: The atomic unit of I/O (default 16KB). InnoDB loads entire pages into the Buffer Pool for reads/writes.
• Rows: Stored tightly within Data Pages, physically sorted by their Primary Key due to the Clustered Index B+Tree.

ARCHIVE: Stream-Based Compressed Storage

In stark contrast, the ARCHIVE engine is designed for immutable, append-only data like system logs or audit trails, where updates are unnecessary and storage footprint is critical. It forsakes traditional page structures and secondary indexes for extreme compression and efficiency.

• No Fixed Pages: Data is treated as a continuous, append-only binary byte stream in an `.arz` file.
• On-the-Fly Stream Compression: Utilizes `zlib` compression, stripping spaces and optimizing NULL handling, achieving 3:1 to 10:1 compression ratios.
• Index Trade-off: Supports only an `AUTO_INCREMENT` primary key; no secondary indexes are allowed to maintain a tiny footprint.

Hot/Cold Data Tiering with Partition Exchange

A powerful architectural pattern combines these engines into a Hot/Cold Data Tiering System. Hot, recent data stays in InnoDB for fast access and updates, while older, less frequently accessed cold data is moved to more cost-effective storage. Since MySQL doesn't allow mixed engines in one table, Partition Exchange is used, enabling near-instantaneous metadata-only swaps of millions of rows.

CREATE TABLE system_logs (
  log_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  log_date DATE NOT NULL,
  subsystem VARCHAR(50) NOT NULL,
  message TEXT NOT NULL,
  PRIMARY KEY (log_id, log_date)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(log_date)) (
  PARTITION p_past_month VALUES LESS THAN (TO_DAYS('2026-05-01')),
  PARTITION p_current_month VALUES LESS THAN (TO_DAYS('2026-06-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Example of Partition Exchange:
CREATE TABLE system_logs_stage LIKE system_logs;
ALTER TABLE system_logs_stage REMOVE PARTITIONING;
ALTER TABLE system_logs EXCHANGE PARTITION p_past_month WITH TABLE system_logs_stage;
ALTER TABLE system_logs DROP PARTITION p_past_month;