Choose Your RDBMS Flavor: MySQL or a Better Clone
By Jack M. Germain
A relational database management system, otherwise known as RDBMS, is a huge part of what happens in the cloud. This data management system is based on the relational model. It is one of the primary database models in widespread use today.
Relational databases in general are structured to recognize relations among stored items of information. They are easy to extend (i.e., add a new data category) without having to modify all existing applications. Each relational database is structured around a set of tables containing data crammed into predefined categories.
This structure is essential for relational databases. Each table is an element of a relation to some other data. It holds one or more data categories in columns. Each row uses an unique data instance for the categories defined by the columns.
Perhaps the most highly recognizable relational database product is Oracle’s MySQL. It offers one of the most comprehensive and advanced feature sets found in data management tools. It provides users with some of the highest levels of scalability, security, reliability, and uptime.
But despite MySQL’s popularity as the RDBMS frontrunner, it is not the only player in the relational cloud database game. Several impressive alternatives exist, such as Postgre SQL, SQLite, and MariaDB.
Of course, no single database product is the perfect option for every business need. This overview highlights the differences, so you can better decide which product is your ideal choice.
A popular open-source RDBMS, PostreSQL is more commonly known as Postgres. This database is designed by engineers for engineers. That suggests its hardcore reputation for reliability and accuracy.
PostgreSQL is ACID-compliant (Atomicity, Consistency, Isolation, Durability) and transactional. This means database transactions guarantee validity even in the event of errors or power failures.
This free, open-source community-driven software is overseen by the PostgreSQL Global Development Group headed by Bruce Momjian. The PostgreSQL community is largely regarded as world’s largest independent open-source development community.
Postgres emphasizes extensibility and standards compliance. It has solid reliability in securely storing data and returning it in response to requests from other software applications. It handles workloads ranging from small, single-machine applications to large, data-warehousing operations with many concurrent users.
Linux distributions typically have PostgreSQL available in supplied packages. The Mac OS X has PostgreSQL server as its standard default database in the server edition and includes PostgreSQL client tools in the Mac desktop edition.
PostgreSQL has numerous top-drawer features that make it a stand-out alternative to MySQL. These include both updatable and materialized views, triggers, and foreign keys. It also supports functions and stored procedures, and provides several expandability options.
I spoke with Momjian to discuss an earlier release and the potential for its Global Development Group evolving into a business or commercial entity for PostgreSQL. He recognizes Postgres’s real power and contribution as an influential software community is in remaining open source. That stature results in encouraging anyone to contribute patches, make commercial products based on it, and offer consulting for training on using it.
That preference for nonbusiness activity makes Postgres a serious alternative to the likes of Oracle and Microsoft. It is unlikely that the Postgres community will ever be waylaid by conflicts between decisions based on sales targets and those based on product dependability.
SQLite is a lightweight RDBMS. It does not require its own process, its own clustering, or its own user management the way other RDBMS do.
Let’s clear up three potentially confusing factors that separate SQLite from other databases in this category.
First, SQL is a query language that drives relational databases. Building on that, SQLite is an embeddable relational database management system.
Second, unlike other relational databases, SQLite does not support stored procedures.
Third, SQLite is file-based, unlike other databases which are server-based.
What makes SQLite lightweight while retaining its relational power is its use of a compact in-process library. For example, even with all its features enabled, the library size can be less than 500 kibibyte (KiB). If optional features are omitted, the size of the SQLite library can drop below 300KiB.
This library implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain. That makes the database free to use for any purpose, whether commercial or private.
You also can configure SQLite to run in a minimal stack space (think 4KiB) and very little heap, as in 100KiB. This makes SQLite a popular database engine choice on memory-constrained gear. While more memory is better than less, SQLite does not suffer degraded performance in low-memory environments.
Another key factor for selecting SQLite is the full-time attention to maintaining the database despite its free, nonmoney-making community structure. An international team of developers works on SQLite’s code base full time.
Database longevity counts big time here. The developers keep expanding SQLite’s capabilities and enhancing its reliability and performance. But they also keep maintaining backwards compatibility with the published interface spec, SQL syntax, and database file format.
Free support for SQLite is available from public support forums. But you have two other options that are not free.
One is purchasing an annual maintenance subscription for $1,500 annually that entitles you to private, expert advice from the developers via email. Or two, you can obtain more direct and time-sensitive technical support via high-priority email and phone directly from the SQLite’s developers. You can also add guaranteed response time for an additional cost. These enhanced support packages range from $8,000 to $35,000 per year.
MariaDB is a fully functional, open-source transactional database solution for modern application development and enterprise use cases. It offers a comprehensive package of technology and services, including feature-rich releases of MariaDB Server and MariaDB MaxScale. The combination of features does a good job of closing the gap between open-source functionality and proprietary offerings.
The original developers of MySQL created MariaDB as a fork of the MySQL database after Oracle acquired MySQL in 2008. This is a very significant development path if you are concerned about compatibility and functionality. It also gives you the confidence to use MariaDB as a drop-in, fully backward-compatible replacement for MySQL.
In fact, MariaDB has gone down a more progressive development path. It offers more DB engines than MySQL itself. For example, these standard engines are included: MyISAM, BLACKHOLE, CSV, MEMORY, ARCHIVE, and MERGE storage engines.
Plus, MariaDB includes 11 additional DB engines. These include: ColumnStore, MyRocks, Aria, FederatedX, SphinxSE, TokuDB, CONNECT, SEQUENCE, Spider, and Cassandra.
MariaDB also has a hefty catalog of optimizer enhancements compared to MySQL. For my money, MariaDB has far more features and enhancements than the leading contenders.
MariaDB’s backward compatible is one of its strongest selling points as a binary drop-in replacement for MySQL. For instance, data and table definition files (.frm) are binary compatible. All filenames, binaries, paths, ports, and sockets are the same. All client APIs and protocols are identical.
Two more salient features are 1. all MySQL connectors work as-is with MariaDB, and 2. the MySQL-client package works with the MariaDB server.
Typically, open-source relational databases share many common features and tools. In looking for your ideal RDBMS solution, you might find that a decision comes down to a scant difference or two that make one RDBMS better meet your needs.
Think of the choice as evaluating between a commercial “brand name” software option or a generic, “new and improved” platform. No doubt, cost can especially be a determining factor.
An open-source relational database might give you a business edge. A free database can be better than proprietary options. You will no doubt spend less in support costs adopting PostgreSQL, SQLite or MariaDB than if you buy a commercial product.
Get familiar with all three. Apply due diligence and requisite deliberation, then pick your favorite RDBMS flavor.
Please feel free to share below any comments, questions or insights about your experience with relational database management systems. And if you found this blog useful, consider sharing it through social media.
About the blogger: Jack M. Germain is a veteran IT journalist whose outstanding IT work can be found regularly in ECT New Network’s LinuxInsider, and other outlets like TechNewsDirectory. Jack’s reporting has spanned four decades and his breadth of It experience is unmatched. And while his views and reports are solely his and don’t necessarily reflect those of Linode, we are grateful for his contributions. He can be followed on Google+.