MariaDB S3 Storage Engine: Archiving Cold Tables to Cloud Object Storage in Pakistan

Enterprise databases face an inevitable storage lifecycle dilemma: transactional data accumulates rapidly over time. In e-commerce platforms, banking systems, and regulatory environments in Pakistan, legal mandates often require retaining five to seven years of transaction logs, customer audit trails, and fiscal ledger data.

Keeping multi-terabyte historical tables on primary enterprise NVMe drives is prohibitively expensive and degrades database maintenance operations—slowing down daily database dumps, extending backup windows, and inflating buffer pool sizes. Conversely, exporting historical records into offline CSV files or separate cold warehouses strips software engineers of the ability to execute instant ad-hoc SQL queries when compliance audits or customer support disputes arise.

The MariaDB S3 Storage Engine delivers the optimal middle ground: transparent tiered storage. It allows MariaDB to store compressed, read-only tables directly inside any S3-compatible object storage (such as AWS S3, Wasabi, Cloudflare R2, or private on-premise MinIO clusters) while keeping them fully accessible to standard SQL queries.

Deployed on high-speed Dedicated Servers, the MariaDB S3 engine slashes storage costs by up to 90% without sacrificing SQL querying accessibility.

---

1. How the MariaDB S3 Engine Operates

Unlike file-level backup solutions, the S3 engine is a native MariaDB storage engine (like InnoDB or Aria).

                      [ Client Application ]
                                |
                   (Standard SQL: SELECT ... )
                                |
                 +--------------v---------------+
                 |   MariaDB Database Server    |
                 |     (Local NVMe Node)        |
                 +--------------+---------------+
                                |
             +------------------+------------------+
             |                                     |
    [ InnoDB Engine ]                      [ S3 Engine (ha_s3) ]
     - Active Data (2026)                   - Cold Archives (2020-2025)
     - Read/Write (NVMe)                    - Read-Only, Compressed
     - High IOPS Transactions               - Local Memory Pagecache
             |                                     |
      (/var/lib/mysql)                      (HTTPS TLS 1.3 / API)
                                                   |
                                    +--------------v---------------+
                                    | Remote Object Storage (S3)   |
                                    | (Wasabi / R2 / MinIO Cluster)|
                                    +------------------------------+

1. Local Metadata Only: The database server maintains only table definition files (.frm) and index metadata locally on disk.
2. Compressed Remote Chunks: Table data rows are split into compressed blocks (typically 4MB) and uploaded to the remote object storage bucket.
3. Local Cache Pruning: When an application queries an S3 table, MariaDB streams only the necessary chunk files into a local in-memory pagecache (s3_pagecache_buffer_size), discarding them once the query completes.

---

2. Installing and Configuring the S3 Engine Plugin

The S3 engine is included in MariaDB Community and Enterprise packages for RHEL/AlmaLinux 9 and Ubuntu/Debian:

# Install the MariaDB S3 storage engine package
dnf install -y MariaDB-s3-engine

Configure your S3 credentials in the MariaDB server configuration file:

# /etc/my.cnf.d/s3.cnf
[mariadb]
# Enable the S3 storage engine plugin
plugin-load-add = ha_s3

# S3 Storage Configuration (Using Wasabi or AWS S3)
s3_access_key = "AKIAIOSFODNN7EXAMPLE"
s3_secret_key = "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
s3_bucket     = "nextgen-mariadb-cold-tier-pk"
s3_region     = "eu-central-1"
s3_host_name  = "s3.eu-central-1.wasabisys.com"
s3_protocol_version = "Auto"

# In-Memory Query Pagecache (Caches remote blocks during active queries)
s3_pagecache_buffer_size = 1G
s3_block_size            = 4M
s3_replicate_cache_size  = 256M

Restart or reload the MariaDB daemon to initialize the plugin:

systemctl restart mariadb

Verify that the S3 engine is active:

SHOW ENGINES;

Ensure S3 appears with Support: YES.

---

3. Archiving Existing Tables to S3

Migrating an existing historical table (such as audit_logs_2023) to S3 is executed in a single atomic SQL command:

USE enterprise_erp;

-- Inspect table engine prior to migration
SHOW TABLE STATUS LIKE 'audit_logs_2023'\G

-- Convert table directly to S3 storage
ALTER TABLE audit_logs_2023 ENGINE=S3;

What Happens Behind the Scenes:

1. MariaDB reads the rows from InnoDB, builds the compressed columnar and data blocks, and streams them via HTTPS directly to your S3 bucket under the prefix enterprise_erp/audit_logs_2023/.
2. The local multi-gigabyte .ibd data file on your NVMe array is deleted, immediately freeing up local disk space.
3. The table remains instantly queryable by your reporting dashboards and CRM tools!

---

4. Querying S3 Tables with Standard SQL

Because S3 tables are first-class database objects, your developers and BI tools do not need to change their code:

-- Querying a cold table stored entirely in S3 object storage
SELECT 
    DATE(created_at) AS log_date,
    action_type,
    COUNT(*) AS total_events
FROM 
    audit_logs_2023
WHERE 
    created_at BETWEEN '2023-06-01' AND '2023-06-30'
    AND action_type = 'SECURITY_POLICY_UPDATE'
GROUP BY 
    log_date, action_type;

MariaDB calculates which 4MB chunks contain the date range requested, fetches only those specific objects from the S3 bucket into the 1GB s3_pagecache_buffer_size, and returns the results.

---

5. Automated Tiering via Partitioned Tables

For large continuously growing datasets (such as application event logs), the optimal design combines InnoDB for the active month and S3 for older historical partitions.

While direct partition-level mixed engines are restricted in some MariaDB releases, you can implement seamless tiering using a unified UNION View:

-- Step 1: Create active transactional table on NVMe
CREATE TABLE audit_logs_active (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(64) NOT NULL,
    created_at DATETIME NOT NULL
) ENGINE=InnoDB;

-- Step 2: Create historical tables archived to S3
-- audit_logs_2024 (ENGINE=S3)
-- audit_logs_2025 (ENGINE=S3)

-- Step 3: Present a unified view to developers
CREATE VIEW audit_logs_all AS
SELECT * FROM audit_logs_active
UNION ALL
SELECT * FROM audit_logs_2025
UNION ALL
SELECT * FROM audit_logs_2024;

Application writes (INSERT) go exclusively to audit_logs_active. At the end of each year, a scheduled script runs ALTER TABLE audit_logs_YYYY ENGINE=S3;, seamlessly transitioning the data to low-cost cloud storage without altering frontend application code.

---

6. Architecture Comparison: Local NVMe vs. MariaDB S3 Engine

Architecture Metric	Local NVMe InnoDB Storage	MariaDB S3 Storage Engine
Storage Cost (Per TB/Mo)	$50 - $80 (Enterprise NVMe)	$5 - $6 (S3/Wasabi Hot Storage)
Write Capability	Read / Write (Transactional)	Read-Only (Immutable Archive)
Compression Ratio	2:1 (InnoDB Page Compression)	5:1 to 8:1 (Block Compression)
Impact on Local Backups	Included in massive daily dumps	Excluded from local dumps (Already safe in cloud)
Query Mechanism	Standard SQL	Standard SQL (Transparent)
Query Latency	Sub-millisecond	50ms - 250ms (Network stream dependent)

By implementing the MariaDB S3 engine on dedicated bare-metal infrastructure in Dedicated Servers in Pakistan, enterprises can retain years of searchable records, slash storage costs, and eliminate disk capacity exhaustion permanently.