MariaDB Server Turns 15! Here Are 15 Reasons Why Developers and DBAs Love It

Posted on octubre 29, 2024 by
spacer

October 29, 2009 was a special day in the database community—it saw the first release of MariaDB Server. Started as a fork of the famous MySQL database, the MariaDB database has evolved and is now one of the most popular and trusted relational database management systems (RDBMS) in the world. This month, MariaDB celebrates its 15th anniversary, so I collected—from the many interactions I have had with practitioners—15 reasons why developers and DBAs love MariaDB.

Welcome to MariaDB monitor screenshot

 

For Application Developers

Let’s explore some of the features in MariaDB that application developers just love.

Reason 1: Dynamic Columns

MariaDB offers dynamic columns, which allow you to store different sets of columns on each row in a table. This feature gives you the flexibility to handle highly-evolving data structures without altering the schema.

Example:

CREATE TABLE products (
  name VARCHAR(100) PRIMARY KEY,  -- a common attribute for all assets
  dynamic_cols  BLOB  -- dynamic columns will be stored here
);

INSERT INTO products
VALUES
  ("T-shirt", COLUMN_CREATE("price", 40, "size", 'M')),  -- dynamic schema
  ("Laptop", COLUMN_CREATE('price', 3000, "ram", "36GB"));  -- dynamic schema

SELECT
  name,
  COLUMN_GET(dynamic_cols, "price" AS INT) AS price,
  COLUMN_GET(dynamic_cols, "size" AS CHAR) AS size,
  COLUMN_GET(dynamic_cols, "ram" AS CHAR) AS ram
FROM products;
+---------+-------+------+------+
| name    | price | size | ram  |
+---------+-------+------+------+
| Laptop  |  3000 | NULL | 36GB |
| T-shirt |    40 | M    | NULL |
+---------+-------+------+------+

Documentation: https://mariadb.com/kb/en/dynamic-columns/

 

Reason 2: Invisible Columns

Managing schema changes is definitely much easier with MariaDB’s invisible columns. You can add new invisible columns to a table, and these columns won’t appear in results of SQL queries unless explicitly requested, allowing for smoother transitions, for example, during new version deployments of your app.

Example:

CREATE OR REPLACE TABLE products (
  name VARCHAR(100) PRIMARY KEY
);

INSERT INTO products VALUES ("T-shirt"), ("Laptop");

SELECT * FROM products;  -- before
+---------+
| name    |
+---------+
| Laptop  |
| T-shirt |
+---------+

ALTER TABLE products ADD COLUMN column_for_new_app_version INT INVISIBLE;

SELECT * FROM products;  -- after (the new column is not visible)
+---------+
| name    |
+---------+
| Laptop  |
| T-shirt |
+---------+

INSERT INTO products(name, column_for_new_app_version)
VALUES ("MariaDB Server", 15);  -- insert value in invisible column: it works

SELECT * FROM products;
+----------------+
| name           |
+----------------+
| Laptop         |
| MariaDB Server |
| T-shirt        |
+----------------+

ALTER TABLE products
MODIFY COLUMN column_for_new_app_version INT;  -- make the column visible

SELECT * FROM products;
+----------------+----------------------------+
| name           | column_for_new_app_version |
+----------------+----------------------------+
| Laptop         |                       NULL |
| MariaDB Server |                         15 |
| T-shirt        |                       NULL |
+----------------+----------------------------+

Documentation: https://mariadb.com/kb/en/invisible-columns/

 

Reason 3: Instant ADD COLUMN

Adding columns without locking the table means you can alter schemas without causing downtime. MariaDB’s instant ADD COLUMN operation keeps your application running while the schema changes.

Example:

CREATE OR REPLACE TABLE products (
name VARCHAR(100) PRIMARY KEY
) ENGINE=InnoDB;  -- this feature is available in the InnoDB storage engine

INSERT INTO products
VALUES
("T-shirt"),
("Laptop"),
... many, many more, maybe many millions of rows ...
);

SET SESSION alter_algorithm='INSTANT';

ALTER TABLE products  -- O(1) instead of O(n) with n=number of rows
ADD COLUMN notes VARCHAR(500) DEFAULT "N/A";

Documentation: https://mariadb.com/kb/en/instant-add-column-for-innodb/

 

Reason 4: Better ACID Behavior

MariaDB’s innodb_snapshot_isolation configuration option provides more consistent ACID compliance, for example, by ensuring non-repeatable or non-monotonic reads are not allowed. It fixes issues that can still occur in MySQL (as of the time of writing this), enhancing data reliability. That’s why I like to say “MariaDB fixes MySQL”.

Example:

SET GLOBAL innodb_snapshot_isolation=ON;

Documentation: https://mariadb.com/kb/en/innodb-system-variables/#innodb_snapshot_isolation

 

Reason 5: Easy-to-Use JSON Functions

While other relational database systems may offer more JSON functions, MariaDB focuses on usability. Developers can easily manipulate JSON data without the complexity found in some other RDBMSs, making MariaDB an attractive choice for applications relying on JSON.

Example:

SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name": "Maria"}', '$.name')) as name;
+-------+
| name  |
+-------+
| Maria |
+-------+

Documentation: https://mariadb.com/kb/en/json-functions/

Webinar: Hybrid data model best practices: JSON + relational

 

Reason 6: Oracle/PostgreSQL/SQL Server Compatibility

MariaDB is highly compatible. You can migrate applications from Oracle, PostgreSQL, or SQL Server without having to change all of your application code, thanks to the built-in SQL compatibility mode in MariaDB. This feature reduces friction when you move between databases and minimizes code refactoring efforts.

Example:

CREATE TABLE "CUSTOMERS"(  -- double quotes
  "CUST_ID" NUMBER(8,0),  -- double quotes
  "CUST_NAME" VARCHAR2(50));  -- Oracle’s VARCHAR2 type

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"CUSTOMERS"(
  "CUST_ID" NUMBER(8,0),
  "CUST_NAME" VARCHAR2(50))' at line 1

SET SESSION sql_mode="Oracle";

CREATE TABLE "CUSTOMERS"(  -- double quotes
  "CUST_ID" NUMBER(8,0),  -- double quotes
  "CUST_NAME" VARCHAR2(50));  -- Oracle’s VARCHAR2 type

Query OK, 0 rows affected (0.365 sec)  -- it works now!

Documentation: https://mariadb.com/kb/en/sql-mode/

Video: Migrating Application Code to MariaDB Using SQL Mode and MaxScale

 

Reason 7: High-Performance Vector Search

Starting with MariaDB Server 11.6, you have fast and scalable vector search thanks to the industry standard and high-performant Hierarchical Navigable Small World (HNSW) algorithm. This makes the MariaDB database an excellent fit for AI applications that use retrieval-augmented generation (RAG), which demand efficient and quick searches across big datasets.

Example:

CREATE OR REPLACE TABLE products (
  name varchar(128),
  description varchar(2000),
  embedding BLOB NOT NULL,  -- vector embedding
  VECTOR INDEX (embedding)  -- vector indexing
);

-- vector search (similarity search)
SELECT name, description FROM products
ORDER BY VEC_DISTANCE(p.embedding, VEC_FromText('[0.3, 0,5, 0.1, 0.3]'))
LIMIT 10

Documentation: https://mariadb.com/kb/en/vectors/

 

For DBAs

Let’s now explore some MariaDB features that DBAs love. Keep in mind that in many organizations, developers take on DBA responsibilities, so often these features directly apply to application developers as well.

Reason 8: Online Schema Change

We already touched on instant ADD COLUMN operations. In fact, MariaDB supports online schema changes on most DDL operations (ALTER TABLE) without locking tables. This translates to minimal disruption for users and decreased downtime during database maintenance tasks.

Documentation: https://mariadb.com/kb/en/innodb-online-ddl-overview/

 

Reason 9: Simultaneous Purpose-Built Storage Engines

MariaDB Server supports multiple storage engines, letting you manage diverse workload types within a single RDBMS. You can optimize different tables for specific needs like read-heavy or write-heavy. Moreover, you can mix tables that use different storage engines in a single SQL query.

Documentation: https://mariadb.com/kb/en/storage-engines/

Webinar (must watch!): Improve performance and scalability with workload-optimized storage engines

 

Reason 10: Fine-Tuned Replication

MariaDB offers robust replication features, including parallel semi-synchronous replication. For those seeking even tighter consistency, MariaDB Server integrates with Galera for synchronous replication, providing flexibility for various high-availability configurations.

Documentation: https://mariadb.com/kb/en/standard-replication/

Video: Everything You Ever Wanted to Know About MariaDB Replication

 

Reason 11: MaxScale

MaxScale, MariaDB’s powerful database proxy, simplifies load balancing with read/write splitting, automatic failover, and even NoSQL support (for example, you can connect your MongoDB apps to MariaDB). It’s arguably one of the most advanced database proxies available, and it helps you implement scalability and high availability with ease.

Documentation: https://mariadb.com/kb/en/maxscale/

Video (must watch!): Database Proxies Crash Course

 

Reason 12: Enterprise Backup

Built-in enterprise-grade backup tools with non-blocking, incremental backup capabilities mean you can run backups without causing performance hits or even downtime. This is crucial for organizations that need zero-downtime operations and robust disaster recovery solutions.

Documentation: https://mariadb.com/kb/en/backing-up-and-restoring-databases/

 

Reason 13: Audit Plugin

MariaDB Server offers an audit plugin out of the box, which can track and log database activity. For those needing even more, the Enterprise Audit plugin extends these capabilities further for security and compliance purposes.

Documentation: https://mariadb.com/kb/en/mariadb-audit-plugin/

 

Loved by Both Developers and DBAs:

Reason 14: License

MariaDB Server is licensed under the GPL, ensuring that its source code will remain open and accessible to everyone. Always. Developers and DBAs appreciate the assurance of not being locked into proprietary solutions. The GPL also fosters innovation, allowing anyone to contribute or modify the database.

 

Reason 15: Support

Since MariaDB Server is released under the GPL, its open-source nature means that you can rest assured you will find a company (for example, us!) to implement that feature you need or fix that bug which is, well, bugging you. Fixes are incorporated into the official MariaDB Server code, making the whole community of worldwide users thrive.

 

Join the celebration!

What’s your favorite MariaDB feature? Did I miss something? Let’s us know on our social media channels: