5 MariaDB Features Worth Knowing About

MariaDB’s five most consequential features for a team running MySQL 8.0 are built-in vector search, system-versioned tables, application-time periods, per-table storage engine choice, and a utf8mb4 server default with UCA 14.0.0 collations, in place since MariaDB 11.6 and carried by the 11.8 LTS line.

If you are weighing a version decision on a MySQL 8.0 estate, MariaDB is one of the options worth putting on the list.

What follows covers each feature in turn: what it does and the SQL you would type. Version references point at MariaDB 11.8 LTS and MariaDB 12.3 LTS, the release most teams starting now would install.

How Does MariaDB Do Vector Search Without an Extension?

MariaDB stores and searches embeddings in the community server itself: a VECTOR(N) column, a VECTOR INDEX using a modified HNSW algorithm, and distance functions for cosine and euclidean similarity. The MariaDB Vector project page puts general availability at 11.8 LTS, and the VECTOR type reference caps a column at 16,383 dimensions. Nothing extra gets installed, and there is no second datastore sitting alongside your rows waiting to drift out of step with them.

CREATE TABLE support_articles (
  id         BIGINT UNSIGNED PRIMARY KEY,
  tenant_id  INT NOT NULL,
  body       TEXT,
  embedding  VECTOR(768) NOT NULL,
  VECTOR INDEX (embedding) M=8 DISTANCE=cosine
) ENGINE=InnoDB;

768 matches the output width of several widely used open-weight sentence embedding models; set it to whatever your model emits. Two constraints are worth reading off that DDL. The indexed column must be NOT NULL, and M accepts values from 3 to 200, with higher values buying accuracy at the cost of index size and write speed, per the create-table-with-vectors reference. The project page also states a limit of one vector index per table.

The server holds and searches vectors; it does not produce them. So the array comes from your embedding model and goes in as text:

INSERT INTO support_articles (id, tenant_id, body, embedding)
VALUES (1, 17, 'Resetting a device token',
        VEC_FromText('[0.021, -0.113, 0.447, ...]'));

Because the embeddings live in an ordinary InnoDB table, a WHERE clause and a similarity ranking execute as one transactional statement. A vector service bolted on beside the database cannot match that: scoping a nearest-neighbour search to a single tenant’s rows needs no join across two systems and no post-filtering of results you already paid to retrieve.

SELECT id, body
FROM support_articles
WHERE tenant_id = 17
ORDER BY VEC_DISTANCE_COSINE(embedding, VEC_FromText('[...]'))
LIMIT 10;

Why Does the Vector Index Get Skipped?

The index only comes into play when the query sorts on a plain distance call, using the metric the index was built for, and caps the rows with a LIMIT. Query a cosine-built index with the euclidean function and the reference documentation is explicit that the index cannot serve it and the query degrades to a full table scan:

-- index above was built with DISTANCE=cosine
SELECT id, body
FROM support_articles
WHERE tenant_id = 17
ORDER BY VEC_DISTANCE_EUCLIDEAN(embedding, VEC_FromText('[...]'))
LIMIT 10;

The generic VEC_DISTANCE avoids the trap by resolving to euclidean or cosine according to the underlying index, as described in the vector overview; those two metrics are the only ones supported. If recall at small LIMIT values matters, mhnsw_ef_search sets how many result candidates the index search keeps in play: 20 by default, settable from 1 to 10000. The search never looks for fewer than that, even when LIMIT asks for less, so raising it buys quality at the cost of search time.

For comparison, MySQL 8.0 has no VECTOR type; a vector type arrived in the 9.x series, and index-backed vector search is documented as a HeatWave capability, where HeatWave GenAI builds the indexes itself for vector columns that get queried often. In PostgreSQL the same job falls to pgvector, an extension you install and enable.

System-Versioned Tables: How Do You Query Last Tuesday?

A system-versioned table keeps every superseded version of each row inside the table itself, so a point-in-time read is a clause on a normal SELECT rather than an audit trigger writing into a history table you have to maintain. Because the old versions stay with the live ones, you can read the table as it stood at any past moment, trace what changed, and set two dates side by side.

CREATE TABLE subscription (
  account_id  BIGINT PRIMARY KEY,
  plan        VARCHAR(32),
  seats       INT
) WITH SYSTEM VERSIONING;

SELECT account_id, plan, seats
FROM subscription
FOR SYSTEM_TIME AS OF TIMESTAMP '2025-11-04 09:00:00'
WHERE account_id = 4021;

That is the whole feature. History is returned when FOR SYSTEM_TIME is specified, and the system-versioned tables reference documents AS OF, BETWEEN, FROM ... TO and ALL as the query forms. The explicit DDL form declares GENERATED ALWAYS AS ROW START and ROW END columns with a PERIOD FOR SYSTEM_TIME; the short form above stores the same information behind the ROW_START and ROW_END pseudo-columns. On InnoDB you can also version by transaction using BIGINT UNSIGNED row-start and row-end columns and read with FOR SYSTEM_TIME AS OF TRANSACTION. MariaDB’s own differences documentation lists temporal data tables among the capabilities MySQL has no counterpart for.

Application-Time Periods for Business Validity

System versioning records when the database was told something; an application-time period records when the fact was actually true. An application-time period is a span marked out by two temporal columns of matching type and width, and it versions your data at the application level rather than the server level. A price with a validity window is the canonical case.

CREATE TABLE price_list (
  sku        VARCHAR(32),
  price      DECIMAL(10,2),
  valid_from DATE,
  valid_to   DATE,
  PERIOD FOR validity(valid_from, valid_to),
  UNIQUE (sku, validity WITHOUT OVERLAPS)
);

UPDATE price_list FOR PORTION OF validity
  FROM '2026-01-01' TO '2026-04-01'
  SET price = 18.00
WHERE sku = 'KB-114';

Declaring a period forces both columns to NOT NULL and quietly adds a check that the first value falls before the second. WITHOUT OVERLAPS on a primary or unique key then rejects rows whose periods collide. That clause is documented from MariaDB 10.5.3; periods themselves, FOR PORTION included, shipped earlier, in MariaDB 10.4.3. Declare both a period and system versioning on one table and you get a bitemporal table, which runs both kinds of versioning on the same rows at once. That is what separates a price that was wrong on Tuesday from one that was merely entered late.

Can You Choose a Storage Engine Per Table?

MariaDB sets the storage engine per table, so transactional tables stay on InnoDB while other tables in the same schema use something else. Beyond the standard set, the differences documentation names ColumnStore for distributed analytical processing, MyRocks, the S3 engine for cloud archival, Aria as a MyISAM replacement, plus CONNECT, SEQUENCE, Spider, SphinxSE, FederatedX and OQGRAPH.

CREATE TABLE event_archive (
  id   BIGINT PRIMARY KEY,
  body TEXT
) ENGINE=Aria;

Several of these ship as separate plugin packages rather than being compiled into the server, so check the engine’s own documentation for the release you are installing before assuming a bare ENGINE= clause is enough.

utf8mb4 Defaults and Newer Collations

MariaDB moved its server default character set from latin1 to utf8mb4 in 11.6, and updated collations to UCA 14.0.0 along the way. If you upgrade between long-term support releases, 11.8 is where both land, and the 11.8 LTS release announcement of 8 June 2025 presents them as part of that release. This is MariaDB retiring a default of its own that predates emoji, not a point where it overtakes MySQL on the default itself. The collation side is the part that outlives the upgrade: sort order and comparison follow a newer Unicode collation algorithm than MySQL’s, so cross-checking sort results is worth doing if you compare output between the two.

MariaDB Features and the Releases They Landed In

One upgrade note worth carrying: the 11.8 release post records that the TIMESTAMP range extension needed no data conversion provided system-versioned tables are not in use, and names those tables as the one known complication, because the internal timestamp representation changed.

Pick the one of these five that maps to a problem you already have. If it is audit history, create a throwaway table WITH SYSTEM VERSIONING, change a row twice, and read it back with FOR SYSTEM_TIME AS OF; you will know within minutes whether it replaces the trigger you have been maintaining.

FAQs

Can I turn on system versioning for a table that already exists?

Yes. ALTER TABLE t ADD SYSTEM VERSIONING enables it on an existing table, and ALTER TABLE t DROP SYSTEM VERSIONING removes it, which deletes all history. Both rebuild the table, so they can be slow on large tables. Later schema changes depend on the system_versioning_alter_history setting: with ERROR, altering a versioned table fails; with KEEP, the alter succeeds but historical queries show the new table structure.

How do I stop a system-versioned table from growing forever?

Three documented options exist: prune with the DELETE HISTORY statement, which requires the DELETE HISTORY privilege; partition by SYSTEM_TIME and drop historical partitions, noting you cannot drop the current partition or the only historical one; or drop and re-add system versioning, which clears history at the cost of a table rebuild. TRUNCATE TABLE will not do the job either: the server refuses it with error 4137 so that the history survives.

Can I index one vector column for both cosine and euclidean distance in MariaDB?

No. A vector index is built for a single distance function, with cosine and euclidean (the default) as the valid values, and a search using a different distance function cannot use that index. MariaDB also permits only one vector index per table, so serving a second metric means dropping the index and rebuilding it with a different DISTANCE value rather than adding another one alongside it.

Does the utf8mb4 default in MariaDB 11.8 affect replication to older servers?

Yes. The default character set changed from latin1 to utf8mb4 and the default collation to utf8mb4_uca1400_ai_ci in MariaDB 11.6, and 11.8 is the first long-term support release carrying the change. Older releases do not have that collation, so a MariaDB 11.8 primary cannot replicate to a MariaDB 10.6 replica unless you point the server back at the old defaults.