Choosing an open source database management system

MySQL

MySQL [1] was initially released in 1995 and acquired by Oracle in 2010. As part of the Linux, Apache, MySQL, PHP (LAMP) stack, MySQL is one of the more popular DBMSs. MySQL ranked second in the Databases category for all respondents on the 2023 Stack Overflow Survey and first for those learning to code [2].

Offering features typical of an RDBMS, MySQL uses a client/server system with a multithreaded SQL server, MySQL Server. MySQL Server supports different back ends, client programs and libraries, administrative tools, and APIs. It is a transaction-safe, ACID-compliant RDBMS that offers features such as full commit, rollback, crash recovery, and row-level locking.

MySQL has the advantage of being quick to install and easy to learn and manage. It is capable of handling a large volume of data and is compatible with most operating systems.

MySQL delivers high availability and disaster recovery thanks to its fully integrated replication technologies. To handle read scaling and report query offloading, MySQL relies on simple synchronous replication, while it uses asynchronous replication for high availability.

In terms of scalability, MySQL's native replication architecture lets it scale to meet demands. In fact, Facebook uses MySQL [1] to scale applications to meet its users' needs. You can also use MySQL as an embedded multithreaded library that you can link to standalone applications, making it smaller, faster, and easier to manage.

For security, MySQL uses built-in data masking and dynamic columns. It is popular with e-commerce, web-based transactions, data warehousing, and online transaction processing (OTP). It can even be used with some online analytical processing (OLAP) workloads.

MySQL Community Edition [3] is free and open source under a GPL license and boasts a large active community of open source developers, but it does not receive MySQL support. If you plan to embed MySQL in a commercial app, you will need a commercial license. The proprietary MySQL Enterprise Edition provides additional components for monitoring and online backup, high availability and security enhancements, and is built to interface with other enterprise-grade tools.

MariaDB

When Oracle acquired MySQL in 2010, MySQL founder, Michael Widenius, created MariaDB [4] as a fork of MySQL. Today, MariaDB has evolved beyond being a drop-in replacement to MySQL. It has added many new features, but it still maintains a high level of compatibility with MySQL. Consequently, most applications that work with MySQL work with MariaDB.

MariaDB supports modern SQL features such as common table expressions and temporal data tables. It also offers a large number of JSON functions to handle unstructured data [5]. Because MariaDB uses a dynamic thread pool, which allows threads to be retired, it benefits from improved speed, enhanced replication, and faster updates.

You can extend the MariaDB front end beyond pure transactional processing with pluggable storage engines from InnoDB, MyRocks, Aria, ColumnStore, and other third-party engines. With the MariaDB ColumnStore plugin, you can perform columnar analytics or hybrid smart transactions.

A Galera cluster engine allows for replication and state transfer. In terms of high availability, MariaDB MaxScale (available under a BSL license) can be used to provide a database proxy with failover and transaction replay capabilities.

MariaDB use cases include web and mobile apps, data warehousing, and data analysis. Companies such as Wikipedia, WordPress.com, and Google all use MariaDB.

MariaDB operates under a GPLv2 license and promises to remain open source. You can freely copy it into your project's local package repositories, which makes the deployment process easier and limits licensing obligations. MariaDB Community Server is free, but the subscription-based MariaDB Enterprise Server is recommended for production environments.

PostgreSQL

PostgreSQL [6], an object-relational DBMS (ORDBMS), is known for proven architecture, reliability, data integrity, a robust feature set, and extensibility. Originally part of the POSTGRES project at UC Berkeley, it has over 35 years of active development. In the Stack Overflow Developer 2023 Survey, it took first place in the Databases category among all respondents as well professional developers [2].

While PostgreSQL uses SQL, it extends the query language by adding features for safely storing and scaling complicated workloads. It conforms to the current SQL standard unless the standard contradicts traditional features or leads to poor architectural decisions. While most required SQL features are supported, they may have a slightly different syntax or function in PostgreSQL.

PostgreSQL can interact with both relational and object-oriented databases and offers features not traditionally available in RDBMSs. Because PostgreSQL supports complex objects, table inheritance, and additional data types beyond JSON, it can handle more complex workloads and schema designs. As a result, PostgreSQL can perform complex queries in a large database more quickly.

ACID-compliant since 2001, PostgreSQL achieves consistency through Multi-Version Concurrency Control (MVCC) and Write-Ahead-Logging (WAL). It offers asynchronous replication, failover, full redundancy, full-text database searches, and native support for JSON-style storage, key-value storage, and XML. In addition, you can customize PostgreSQL with plugins and extensions.

PostgreSQL scales well but has a heavier install footprint than MariaDB or MySQL. Use cases include complex data analysis, data science, AI-related capabilities, finance, manufacturing, and geographic information systems (GIS).

PostgreSQL is open source under the PostgreSQL License, which is similar to an MIT or BSD license.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy Linux Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

  • In-Memory DBMS

    Thanks to powerful hardware, in-memory databases run without accessing mass memory devices, which means they handle transactions and evaluations at high speed, introducing a paradigm shift in the database market.

  • PostgreSQL

    PostgreSQL, an open source object-relational database management system known for its reliability and extensibility, offers a robust feature set. If you are new to PostgreSQL, we help you get started with some of its most useful features.

  • MySQL Workbench 5.1

    A small database is easy to plan on paper, but the structure quickly becomes more complex as you add more elements. MySQL Workbench can help you keep the tables arranged.

  • The sys admin's daily grind: Adminer

    Sys admin columnist Charly freely admits that he doesn't like SQL and phpMyAdmin any more than he does COBOL. Instead, meet his new best friend; the slim, attractive database tool known as Adminer.

  • MariaDB Creators Launch Non-Profit Foundation

    New organization will oversee interoperability and standards for the popular open source database.

comments powered by Disqus