MariaDB vs. PostgreSQL Performance
A Database Comparison
When a company hires you as a software developer, database administrator, or solution architect, often, you are given an established environment and technology stack to work with.
My story isn’t different from yours, but it has a twist. So, first, a little history.
I was hired as a technical lead/architect in 2018, and I was given ASP.Net Core 2.1 for UI and API layers and Microsoft SQL Server 2016 as the database engine to build an Excise Tax Software website for a mid-Atlantic state. After seven months, phase 1 was completed with successful deployment to production. In 2019, while working with the existing stack, I was also tasked to compare, plan, and change the SQL engine with a less expensive alternative. Our team started phase 2 and an additional contract to deploy a similar system for a sunbelt state.
As you can imagine, that meant more work with the same team and less budget. Ultimately, we had to adapt and change our focus to develop this website into a product that can be configured or customized for other states faster, easier, and cheaper. We decided to modify, or upgrade different layers of this software, allowing the system to be developed quicker while providing the same features and offering a more modern user experience with less cost for the next client.
Each layer had to be changed. Some drastically, others just a tad, and the twist I mentioned earlier, is about those modifications. This article is about changing our Database layer, and did I mention using the same team? Don’t you hope sometimes, someone else could step in, solve problems, and hand them over to you and make your life easy?
Changing Database
Initially, we used Microsoft SQL Server’s powerful engine to store relational, structured data and JSON NoSQL data to capture our users, accounts, tax returns, payment records, and many more. We leverage SQL Server’s native JSON features heavily in our data models. There are thousands of articles about MS SQL Server and its pros and cons versus other databases. In our case, we needed to present a comparison to management to make an informed decision about using another database engine, as the ultimate goal was to cut costs.
To fit our software within the smaller budget, we had to replace SQL Server with a free or much cheaper engine. We compared two free, open-source products, MariaDB and PostgreSQL.
To make the change, we had to account for the following:
- database features
- cost of the service/engine
- performance
- learning curve
- cloud support
Technical Summary
Our technical analysis showed that for ‘our product’ and ‘our use case,’ MariaDB is a better option than PostgreSQL. We compared these two engines on the same Windows 2016 server, with 16GB RAM and the same database model. The comparisons were based on these criteria:
• Database read/write performance
• Learning curve
Using ‘Tax Returns’ and ‘Search’ data as key datasets, MariaDB was much faster than PostgreSQL for Tax Return calculations and Search operations.
Performing CRUD operations were slower in PostgreSQL than in MariaDB. That meant historical data migrations, especially e-File and Audit, would be impacted.
Development with MariaDB was closer to ANSI SQL standard and MS SQL Server than PostgreSQL for our team, and it was easier to adapt our MS SQL models to MariaDB. PostgreSQL had a steeper learning curve for our developers.
The market share of Database management system products indicates that the leaders are SQL Server and MySQL at 21.9% and 21.1%.
Please note: MySQL is the original database from which MariaDB was created. MariaDB is promoted as “An enhanced, drop-in replacement for MySQL.” These databases are the same, the only difference being the support model. For more information, you can click on the following:
Tax Calculations
Testing tax Return calculations, ranging from 70 transactions to +100,000 transactions, MariaDB consistently performed faster than PostgreSQL. We use old-fashioned tabular and JSON NoSQL functions when performing tax calculations.
Search Operations
Searching across 6.3 million transactions using the same relational model and indexes, MariaDB performed significantly faster than PostgreSQL in our specific use cases. Also, after adding 2 million records, MariaDB produced similar results, while PostgreSQL degraded slightly.
While both databases have native JSON support, MariaDB’s features and performance were better than PostgreSQL.
CRUD Operations
Testing insert, update, and delete operations, showed that MariaDB and PostgreSQL are comparable in similar datasets.
Customers
Market share
The Verdict
To fit within the smaller budget, we moved away from SQL Server Standard in 2019, which roughly cost $7.5K per 4-core instance. This figure is doubled when you account for disaster recovery (DR), which is standard for production environments. We compared open-source databases MariaDB and PostgreSQL. They both support JSON natively, a feature we leverage heavily in our software.
We used 6.3 million sample Tax transactions in MariaDB and PostgreSQL, and the performance of the ‘load,’ ‘search,’ and ‘tax calculations’ were compared. In each test scenario, MariaDB outperformed PostgreSQL. When we Increased the number of transactions to 8.3 million, our conclusion didn’t change. MariaDB and PostgreSQL both produced a similar outcome.
MariaDB was used by another development team for a similar product, and rather than implement newer instances with PostgreSQL; we decided to adopt one database as the standard. In my opinion, it is prudent to use one database technology for similar products. This will facilitate database administration, sharing resources, performance tuning, transfer of knowledge, and other database tasks.
It is important to note that SQL Server was superior in performance compared to MariaDB and PostgreSQL.
Footnote
This comparison is limited to our specific needs and unique database model. More than 20+ years of experience successfully delivering software is behind this comparison. The entire process was reviewed by two other leads/architects. In the end, I am happy to announce so far, so good, and no regrets :).
