Maria (db) is 15 years old! 15 reasons to love her (or at least get to know her)
October 29, 2009 was a special day in the database community – the first release of MariaDB Server took place. MariaDB, originally created as a fork of MySQL after it was purchased Oracle Corporationhas evolved to become one of the most popular and trusted relational database management systems (RDBMS) in the world. MariaDB celebrates its 15th anniversary this month (October 2024), so I've compiled 15 reasons, from personal experience and experience, why developers and DBAs love MariaDB.
Application Developers
Let's take a look at some of the features of MariaDB that application developers simply adore.
Reason 1: Dynamic Columns
MariaDB offers dynamic columns, which allow you to store a different set of columns in each row of a table. This feature adds flexibility to work with rapidly changing data structures without changing 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
Invisible columns in MariaDB make schema changes easier. You can add new invisible columns to a table and they will not appear in the results of SQL queries unless explicitly requested, allowing for smoother transitions when, for example, updating your application.
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: Add Columns Instantly
Adding columns without locking a table means you can change schemas without causing downtime. Instant Column Adding Feature ADD COLUMN
MariaDB keeps your application running when 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(log n) 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 Compliance
MariaDB configuration option innodb_snapshot_isolation provides better ACID compliance, for example by ensuring that non-repeated or non-monotonic reads are not allowed. It fixes issues that may still occur in MySQL (as of this writing), improving 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 increase the number of JSON features, MariaDB focuses on usability. Developers can easily manipulate JSON data without the complexity of some other database systems, making MariaDB an attractive choice for applications that use 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: Compatibility with Oracle/PostgreSQL/SQL Server
MariaDB is highly compatible. You can migrate applications from Oracle, PostgreSQL or SQL Server without changing all the application code, thanks to MariaDB's built-in SQL compatibility mode. This feature reduces the cost of moving 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!
Query completed successfully, 0 rows affected (0.365 sec) – now working!
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, scalable vector search using the industry standard, high-performance Hierarchical Navigable Small World (HNSW) algorithm. This makes MariaDB well suited for AI applications using augmented search generation (RAG) that require efficient and fast search of large data sets.
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 Database Administrators
Let's now look at some of the MariaDB features that DBAs love. Remember that in many organizations developers take on the responsibilities of database administrators, so often these functions directly apply to application developers as well.
Reason 8: Fast schema changes
We have already mentioned the instant ADD COLUMN operations. In fact, MariaDB supports fast schema changes for most DDL operations (ALTER TABLE) without locking tables. This means minimal disruption to users and reduced downtime when performing database maintenance tasks.
Documentation: https://mariadb.com/kb/en/innodb-online-ddl-overview/
Reason 9: Concurrent use of different storage engines
MariaDB server supports multiple storage engines, allowing you to manage different types of workloads within a single DBMS. You can optimize different tables for specific needs, such as read- or write-intensive use. Moreover, you can mix tables using different storage engines in a single SQL query.
Documentation: https://mariadb.com/kb/en/storage-engines/
Webinar (must watch!): Increase performance and scalability with workload-optimized storage engines
Reason 10: Flexible replication
MariaDB offers robust replication features, including parallel semi-synchronous replication. For those looking for even stronger consistency, MariaDB Server integrates with Galera for synchronous replication, providing flexibility for a variety of high availability configurations.
Documentation: https://mariadb.com/kb/en/standard-replication/
Video: Everything you ever wanted to know about MariaDB replication
Reason 11: MaxScale proxy
MaxScale, a powerful MariaDB database proxy, simplifies load balancing with read/write splitting, automatic failover, and even supports NoSQL (you can connect your MongoDB applications to MariaDB, for example). This is perhaps one of the most advanced database proxy servers that helps you achieve scalability and high availability easily.
Documentation: https://mariadb.com/kb/en/maxscale/
Video (must watch!): Crash Course on Database Proxy Servers
Reason 12: Enterprise-grade backup
Built-in enterprise-grade backup tools with non-blocking incremental copy capabilities allow you to complete operations without impacting performance or stopping. This is important for organizations that require zero downtime operations and reliable disaster recovery solutions.
Documentation: https://mariadb.com/kb/en/backing-up-and-restoring-databases/
Reason 13: Audit plugin
MariaDB Server offers an auditing plugin out of the box that can monitor and log database user activity. For those who need even more functionality, the Enterprise Audit plugin extends these capabilities for security and compliance purposes.
Documentation: https://mariadb.com/kb/en/mariadb-audit-plugin/
Loved by both developers and database administrators:
Reason 14: License
MariaDB Server is licensed under the GPL, ensuring that its source code remains open source and available to everyone. Always. Developers and DBAs appreciate the peace of mind that they are not locked into proprietary solutions. The GPL also promotes innovation by allowing anyone to contribute to or change the database.
Reason 15: Support
Because MariaDB Server is released under the GPL, its open source nature means you can be sure to find a company (like us!) that implements a feature you need or fixes a bug that, well, annoys you. The fixes are included in the official MariaDB Server code, helping the entire community of users around the world to thrive.
Join the celebration!
You can join in the congratulations, ask questions or write comments on social networks and MariaDB communities.