Use when choosing a storage engine for a new table, migrating from MyISAM to InnoDB, understanding engine-specific behaviors, or reasoning about ACID guarantees on MariaDB 10.6+. Prevents the common mistake of using MyISAM for transactional data, ColumnStore for OLTP, MEMORY for persistent caches, or mixing engines without understanding cross-engine query limitations. Covers InnoDB (default, ACID), Aria (crash-safe MyISAM replacement, system tables), MyISAM (legacy), ColumnStore (analytical), Spider (sharding), CONNECT (federated), MEMORY (in-memory), and a deterministic decision tree per workload. Keywords: storage engine, InnoDB, Aria, MyISAM, ColumnStore, Spider, CONNECT, MEMORY, ENGINE=, which engine should I use, no transactions, table locking, row locking, ACID, MVCC, full-text search, columnar analytics, federation, sharding, in-memory cache, foreign keys missing, table corrupted on restart, slow concurrent inserts, why is my table locking, can I run analytics on InnoDB, federated table, CSV table, my
Use when choosing a storage engine for a new table, migrating from MyISAM to InnoDB, understanding engine-specific behaviors, or reasoning about ACID guarantees on MariaDB 10.6+. Prevents the common mistake of using MyISAM for transactional data, ColumnStore for OLTP, MEMORY for persistent caches, or mixing engines without understanding cross-engine query limitations. Covers InnoDB (default, ACID), Aria (crash-safe MyISAM replacement, system tables), MyISAM (legacy), ColumnStore (analytical), Spider (sharding), CONNECT (federated), MEMORY (in-memory), and a deterministic decision tree per workload. Keywords: storage engine, InnoDB, Aria, MyISAM, ColumnStore, Spider, CONNECT, MEMORY, ENGINE=, which engine should I use, no transactions, table locking, row locking, ACID, MVCC, full-text search, columnar analytics, federation, sharding, in-memory cache, foreign keys missing, table corrupted on restart, slow concurrent inserts, why is my table locking, can I run analytics on InnoDB, federated table, CSV table, my cache is gone after restart
license
MIT
compatibility
Designed for Claude Code. Requires MariaDB 10.6-LTS, 10.11-LTS, 11.x, 12.x.
metadata
{"author":"OpenAEC-Foundation","version":"1.0"}
mariadb-core-storage-engines
Deterministic guidance for picking and operating MariaDB storage engines.
Quick Reference
InnoDB is the right default 99% of the time. It is ACID, row-locked, MVCC-backed, foreign-key-aware, crash-safe, and the only engine to pick for new transactional workloads.
MyISAM lacks transactions, foreign keys, and crash-safety. NEVER use MyISAM for new tables. Use Aria as the MyISAM replacement when you need crash-safety on system-like data.
ColumnStore is analytical-only. It cannot serve OLTP, runs as a separate distribution, and is column-oriented. NEVER point an OLTP application at ColumnStore.
MEMORY engine loses all rows on server restart. Use it only for ephemeral lookup tables that can be re-populated from a source of truth.
Spider federates across MariaDB backends. Spider's high-availability features were removed in 10.7.5 ; combine Spider with Galera or replication for HA.
CONNECT federates external data sources (CSV, XML, JSON files, ODBC, JDBC, MongoDB). NEVER use CONNECT for high-throughput writes ; it is a federation layer, not a storage layer.
Decision Tree : Which Engine
Need ACID + foreign keys + concurrent writes ?
YES -> InnoDB (default, 99% of cases)
NO -> continue
Internal system table or on-disk temporary table ?
YES -> Aria (managed by MariaDB itself, do NOT pick Aria for user tables)
NO -> continue
Analytical OLAP scans over billions of rows ?
YES -> ColumnStore (separate distribution, never OLTP)
NO -> continue
Horizontally shard a single logical table across many MariaDB nodes ?
YES -> Spider (combine with Galera or replication for HA)
NO -> continue
Read external data files or other DBMS as a SQL table ?
YES -> CONNECT (CSV / XML / JSON / ODBC / JDBC / MongoDB)
NO -> continue
Ephemeral lookup table that can be lost on restart ?
YES -> MEMORY (HEAP) ; no TEXT / BLOB columns ; default HASH index
NO -> InnoDB
Legacy MyISAM table found in your schema ?
YES -> ALTER TABLE x ENGINE=InnoDB; (NEVER leave it on MyISAM)
ALWAYS state the engine explicitly in CREATE TABLE so the choice is visible in code review.
ALWAYS specify ENGINE=InnoDB even though it is the default. ALWAYS specify ROW_FORMAT=DYNAMIC so off-page storage of long variable-length columns is unambiguous. ALWAYS specify DEFAULT CHARSET=utf8mb4 ; stock 10.6 and 10.11 still ship with latin1 as the global default.
Pattern 2 : Engine swap from MyISAM to InnoDB
-- Audit a legacy schemaSELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND engine NOTIN ('InnoDB', 'Aria')
ORDERBY table_schema, table_name;
-- Convert one table, version-safeALTER TABLE legacy_orders ENGINE=InnoDB,
ALGORITHM=COPY, LOCK=SHARED;
NEVER use ALGORITHM=INSTANT for an engine swap ; engine change always requires a full table rewrite. NEVER swap an engine on a busy table during peak hours ; the table is fully locked for SHARED access during the COPY rewrite.
Pattern 3 : MEMORY for ephemeral lookup
-- 10.6+ : ephemeral session-state, lost on restartCREATE TABLE session_lookup (
session_token CHAR(64) NOT NULLPRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
expires_at INT UNSIGNED NOT NULL,
INDEX ix_user (user_id) USING HASH
) ENGINE=MEMORY
MAX_ROWS=100000;
NEVER store any value of record in MEMORY tables ; a restart wipes them. NEVER use TEXT or BLOB columns ; MEMORY rejects them. ALWAYS size MAX_ROWS to bound memory usage.
Pattern 4 : Aria for system-like crash-safe storage
-- 10.6+ : crash-safe but non-transactional ; use only for system-like tablesCREATE TABLE audit_archive (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
event_at TIMESTAMP(6) NOT NULL,
payload TEXT NOT NULL
) ENGINE=Aria
ROW_FORMAT=PAGE
TRANSACTIONAL=1;
ALWAYS know that TRANSACTIONAL=1 on Aria means "crash-safe", NOT "supports BEGIN / COMMIT". Aria has no MVCC and uses table-level locking. NEVER pick Aria for concurrent user data.
Pattern 5 : Spider sharding shell
-- 10.6+ : on the shard-router node
INSTALL SONAME 'ha_spider';
CREATE SERVER backend1
FOREIGN DATA WRAPPER mysql
OPTIONS (HOST '10.0.0.11', DATABASE 'shop', USER'spider', PASSWORD '...', PORT 3306);
CREATE TABLE order_shard (
id BIGINT UNSIGNED NOT NULL,
customer_id BIGINT UNSIGNED NOT NULL,
total_cents BIGINTNOT NULL,
PRIMARY KEY (id, customer_id)
) ENGINE=Spider
PARTITIONBY HASH(customer_id) (
PARTITION p0 COMMENT ='srv "backend1", table "orders_p0"',
PARTITION p1 COMMENT ='srv "backend2", table "orders_p1"'
);
ALWAYS pair Spider with Galera or replication on each backend for HA ; Spider's own HA was removed in 10.7.5. NEVER deploy Spider over a slow WAN without spider_connect_timeout and spider_net_read_timeout tuned.
Pattern 6 : CONNECT to an external CSV
-- 10.6+ : federate a CSV file as a SQL tableCREATE TABLE product_feed (
sku VARCHAR(64) NOT NULL,
name VARCHAR(255) NOT NULL,
price_cents BIGINTNOT NULL
) ENGINE=CONNECT
TABLE_TYPE=CSV
FILE_NAME='/var/feeds/products.csv'
SEP_CHAR=','
QUOTED=1
HEADER=1;
NEVER write heavy transactional traffic to a CONNECT table ; it has no real storage engine of its own. NEVER let untrusted user input control FILE_NAME ; that is a file-system read primitive.
ALWAYS treat ColumnStore as a separate distribution that is loaded from InnoDB via batch ETL or change-data-capture. NEVER point an OLTP application at ColumnStore for row-by-row UPDATE / DELETE ; performance is by design slow for that pattern.
Engine Feature Matrix (Quick Reference)
Engine
ACID
Locking
FK
MVCC
Crash-safe
Full-text
Spatial
Default
InnoDB
yes
row
yes
yes
yes (redo log)
yes (10.0.5+)
yes
YES (default since 10.2)
Aria
no
table
no
no
yes (PAGE row format)
yes
no
no
MyISAM
no
table
no
no
no
yes
yes
no (legacy)
ColumnStore
no
block
no
no
yes (snapshot)
no
no
no (separate dist)
Spider
depends on backend
depends
depends
depends
depends
depends
depends
no
CONNECT
no
none (read-mostly)
no
no
no
no
no
no
MEMORY
no
table
no
no
no (lost on restart)
no
no
no
Full method-level matrix in references/methods.md.
Cross-Engine Query Limits
ALWAYS know that a multi-statement transaction touching both InnoDB and MyISAM rows commits the MyISAM writes immediately and only the InnoDB writes participate in the transaction. NEVER mix engines inside a transactional boundary that needs all-or-nothing semantics.
ALWAYS know that foreign keys are forbidden in partitioned tables and forbidden across engines ; a FK can only point from one InnoDB table to another InnoDB table.
ALWAYS know that JOINing an InnoDB table to a CONNECT or Spider table forces a row-by-row pull through the federation layer ; the optimiser has no statistics on the remote side.
Anti-Patterns (See references/anti-patterns.md)
MyISAM for orders / users (no transactions, crash-loss)
ColumnStore for OLTP (write amplification, latency)
MEMORY for "cache" without considering restart-loss
Mixing engines in a JOIN that crosses transaction boundaries
Spider over slow WAN without connection pooling
Aria for high-concurrency writes (still table-locking semantics)
InnoDB with innodb_file_per_table=OFF (shared tablespace bloats)
ENGINE= without ROW_FORMAT specification on tables with long columns
Version Notes
MariaDB 10.2 : InnoDB became the global default ; DYNAMIC became the default ROW_FORMAT.
MariaDB 10.3 : InnoDB instant ADD COLUMN at the end of the table.
MariaDB 10.4 : InnoDB instant ADD COLUMN at any position, instant DROP COLUMN.
MariaDB 10.6 : atomic DDL via binary log ; ignored indexes ; Aria continues as system-table storage.
MariaDB 10.7.5 : Spider's built-in high-availability features removed (MDEV-28479).
MariaDB 11.6 : default charset migrated from latin1 to utf8mb4 ; affects new-table defaults across all engines.
See references/methods.md for the complete per-engine feature matrix, references/examples.md for full working examples per engine, and references/anti-patterns.md for in-depth anti-pattern walkthroughs.
