PostgreSQL and MariaDB are open source databases, which boast of vast community support and development, as well as enterprise versions. While PostgreSQL has withstood time and continued development as a robust, object-relational database system, MariaDB has emerged as an innovative RDMS dedicated to remaining highly compatible with MySQL and freely available. Over the last few weeks, new versions of both PostgreSQL (version 10) and MariaDB (version 10.3.2) were released, which makes this an opportune time to compare the two and discuss new features and changes.
See also MariaDB vs MySQL.
What Has Changed?
PostgreSQL: Several major changes mark this release of PostgreSQL 10 (press release; release notes), many of which are centered on a “divide and conquer” strategy for faster access, management, and analysis of data. This effort to distribute data across multiple nodes for greater efficiency and increased performance includes: logical replication, declarative table partitioning, and improved query parallelism. Changes have also been made to increase security with stronger password authentication via the new SCRAM-SHAM-256 authentication method. Finally, the new release dramatically reduces downtime from the application perspective with the Quorum Commit for Synchronous Replication feature.
MariaDB: Changes noted in MariaDB 10.2.3 (release notes; changelog; overview of changes) from previous versions are geared towards differentiating MariaDB further from MySQL while still maintaining the drop-in replacement capability that underscores the continued dedication of MariaDB to maintaining compatibility with MySQL. One major change in this release is the use of InnoDB as default rather than the previously used XtraDB storage engine. However, a majority of the changes center on syntax and general features, variables, replication and binary log changes, and optimizations, as well as the introduction of the GeoJSON / JSON data type. Also, see What’s New in MariaDB 10.
Who Uses These Databases?
PostgreSQL: PostgreSQL is used by large corporations and organizations across several industries, including BioPharm, e-Commerce, Education, Finance, Gaming, Government, Healthcare, Manufacturing, Media, Retail, Technology, and Telecom, as well as several open source projects.
MariaDB: MariaDB is being used by many large corporations, Linux distributions, and more. Some organizations that use MariaDB include Alibaba, Facebook, Google, Tencent, HP, Virgin Mobile, WordPress.com, and Wikipedia..
What About Database Structure and Indexes?
PostgreSQL: PostgreSQL supports most of the major features and core requirements of ISO/IE 9075:2011. A complete list of supported features and unsupported features can be found in the PostgreSQL 10.0 documentation.
PostgreSQL is also fully ACID compliant, which includes full support for foreign keys, joins, views, triggers, and stored procedures, ensuring the four properties of ACID (Automaticity, Consistency, Isolation, and Durability) are upheld.
MariaDB: Because MariaDB was originally a fork of MySQL and remains committed to being a drop-in replacement for MySQL, the database structure and indexes of MariaDB are similar to MySQL. As such, each release of MariaDB is accompanied by detailed notes of differences and compatibility with the most recent version of MySQL.
Additionally, the new version of MariaDB promises increased SQL completeness and schema compatibility. For SQL completeness, common table expressions and window functions were added. Existing limitations were also removed through the added support of subqueries within views and the ability to have multiple temporary tables within a single query. For improved schema compatibility, check constraints and default values for BLOB/TEXT columns were added, and DECIMAL precision was increased to 38 places. Support was also added for multiple triggers of the same type per table.
Where (and How) Are These Databases Deployed?
PostgreSQL: PostgreSQL is written in C and supports a variety of programming languages, including: .Net, C, C++, Delphi, Java, Perl, PHP, Python, and Tcl.
PostgreSQL is available in source code and several binary formats. Binary packages are available for the following operating systems: BSD (i.e., FreeBSD and OpenBSD), Linux (i.e., RedHat, including CentOS, Fedora, Scientific, Oracle variants; Debian; Ubuntu; SuSE and OpenSuSE; and others), macOS, Solaris, and Microsoft Windows.
To download PostgreSQL, go to the PostgreSQL downloads page and select the desired binary package, source code, or third-party distribution. Third-party distributions include PostgreSQL Live CD, 2UDA, BigSQL, TurnKey PostgreSQL, and BigSQL.
MariaDB is available in several source and binary formats. Binary packages are available for the following operating systems: Linux, Solaris, and Microsoft Windows; however, many distributions include MariaDB in their package repositories, which include extensive Linux, BSD, and macOS distributions.
To download MariaDB, go to the MariaDB downloads page.
For CentOS, Fedora, RedHat, Debian, Ubuntu, OpenSuSE, Arch Linux, Mint, or Mageia, go to the download repository for your operating system.
What Types of Replication / Clustering Are Available?
PostgreSQL: PostgreSQL offers a master-slave replication. As such, streaming replication, cascading replication, and synchronous replication are possible. Bidirectional replication is possible, as Hans-Jurgen Schonig notes, in recent versions with the BDR package, but this newer version of PostgreSQL promises much more in terms of replication and flexibility with nearly zero downtime via logical replication and the use of quorum commit for synchronous replication.
With logical replication, modifications can be sent on a per-database or per-table level to different PostgreSQL databases, which allows you to fine-tune how data is replicated to database clusters.
Quorum commit for synchronous replication provides greater flexibility in synchronous replication by allowing you to specify how soon each commit will proceed once any number of standbys reply irrespective of their ordering, which allows for a continuous update and deploy of the database.
MariaDB: MariaDB supports asynchronous master-slave and multi-source replication, as well as multi-master replication. As such, semisynchronous replication, parallel replication, and multi-master clustering via the MariaDB Galera Cluster are possible. Additionally, new to this version, delayed replication is supported. Delayed replication allows you to specify an amount of time (in seconds) by which the replication slave will lag behind the master. The intent of this is to ensure the slave reflects the state of the master from some time in the past.
Who’s Currently Behind the Databases?
PostgreSQL: PostgreSQL Global Development Group (PGDG) is one of the main entities behind PostgreSQL and consists of individuals and companies that have contributed to the PostgreSQL project. While there are PostgreSQL foundations, their primary purposes are fundraising and coordination of projects; they do not own the code. The PostgreSQL Core Team is a group of dedicated, senior contributors and community members who are tasked with setting release dates, handling confidential matters for the project, acting as spokespeople for the PGDG, and arbitrating decisions within the community that are not settled by consensus. However, it should be noted that the entities behind PostgreSQL mostly handle administrative, funding, and coordination roles, but even these are directed by the vast PostgreSQL community of developers and users—anyone can join and contribute directly to the PostgreSQL project. See also Contributor Profiles.
MariaDB: MariaDB Foundation is the main entity behind MariaDB, as the purpose of the foundation is to ensure MariaDB is actively developed in the community. MariaDB Foundation is the owner of the main MariaDB server project and mariadb.org, maintains and oversees the MariaDB community, and ensures the official MariaDB development tree is always open to the MariaDB developer community. MariaDB Corporation is the other major entity behind MariaDB, providing expert support and consulting for MySQL and MariaDB. See also People Behind MariaDB.
Who Provides Support?
PostgreSQL: The extensive and active PostgreSQL community provides support through documentation, users groups, mailing lists, and other additional resources, including an IRC channel for users to easily ask questions from active and knowledgeable PostgreSQL community members and several international sites to ensure you find resources and community engagement opportunities in your language and/or country. Commercial resources and professional services are also available and listed by region to ensure you find the right support solutions for your needs.
MariaDB: MariaDB offers support engineers who are experienced DBAs and software developers, and who are also technical experts of Maria DB and MySQL. Support is also offered through the MariaDB Knowledge Base, where you can access documentation, tutorials and training, and other resources. Additional services and training are also available. For enterprise subscriptions, MariaDB Corporation also offers extensive 24x7x365 support.
Who Supplies Ongoing Development?
PostgreSQL: PostgreSQL champions community development and involvement. The Developers page notes, “We don’t hire programmers, we reach across the Internet, drawing the best developers in the world to PostgreSQL.” As such, development depends on volunteers, which includes a core team and community-based set of major contributors, as well as individual contributors within the PostgreSQL community. Also see the PostgreSQL Development Wiki.
MariaDB: The motto “Born in the community. Raised in the enterprise.” underscores the focus of MariaDB on both community and enterprise. As such, MariaDB strongly relies on its community for innovation and development. The active community consists of non-developers and developers, some of whom are within enterprises that actively support MariaDB innovation. As a result, contributors to MariaDB technologies include enterprise partners: for example, MyRocks, an SSD optimized storage engine for workloads included in MariaDB 10.0, was developed by Facebook.
Who Maintains the Documentation?
PostgreSQL: Documentation is maintained by the PostgreSQL Core Team, however, any member of the community can edit or create documents by creating a PostgreSQL community account.
MariaDB: Documentation is maintained primarily by the MariaDB Foundation and MariaDB Corporation on the MariaDB Documentation page. However, documentation is created and improved, as well as translated, by the active community of contributors.
Is There an Active Community?
PostgreSQL: There is a vast, active, and dedicated PostgreSQL community of contributors, developers, and users. On the PostgreSQL Community page, there are multiple ways to discover general or specific mailing lists, job postings, and learning opportunities. The Developers page provides means to learn more or become active developers on the PostgreSQL project. Additional community resources, where you can find other ways of connecting and contributing include the PostgreSQL Wiki and Planet PostgreSQL.
MariaDB: MariaDB is dedicated to and dependent upon an active community, as such the MariaDB developer, non-developer, and contributor community is extensive and active. On the MariaDB Foundation Get Involved page, you can find ways to engage with community members through mailing lists, social media, and events and conferences, as well as help document, debug, and develop MariaDB. The Community page on the MariaDB Corporation website provides resources and methods for contributing and participating in the community and community projects..
What About Database Connectors?
Connecting Panoply to PostgreSQL and/or MariaDB:
Whether you choose to use PostgreSQL or MariaDB to run your business, Panoply, smart data warehouse, connects seamlessly to both, providing a single data management solution in the cloud without a single line of code.
Which Database Is Right for Your Business?
PostgreSQL: PostgreSQL is a powerful and time-tested relational database system that has remained an important free and open source option for individuals, small business, and enterprises. With an extensive history, PostgreSQL boasts of more than 15 years of active development and proven architecture with industry-recognized reliability, data integrity, and correctness. Advantages include low-cost deployment and maintenance, reliability and stability, as well as the ability to remain extremely responsive even in high volume environments.
MariaDB: MariaDB is a popular open source database created by the founders of MySQL. With an extensible architecture meant to allow for innovation and customization and the fierce dedication to remaining open source and MySQL compatible, MariaDB lives the motto: “Born of the community. Raised in the enterprise.”
Panoply and Your Database as Data Source
Regardless of which database is right for you, Panoply with its cloud based data warehouse architecture provides a seamless end-to-end platform for your data management and analytics needs. As a smart data warehouse in the cloud, Panoply provides Infrastructure-as-a-Service (IaaS), eliminating the cost and burden of data architecture, maintenance, and optimization, and allowing your team to focus on turning data into insights in as little time as possible.