Rishabh AgarwalRelational databases first appeared in the 1970s—that was more than half a century ago! They have stood what one would call the “test of time” and remained the go-to persistence solution for software applications. Other database technologies like NoSQL have challenged this dominance of RDBMS, but their sheer versatility has kept them at the top.
In a landscape filled with open-source and commercial relational databases, this article focuses on the four most prominent open-source databases - PostgreSQL, MySQL, MariaDB, and SQLite. These DBMS are the most preferred databases per the SO’s 2023 survey.
When developers face many database options, it can feel like drowning in a sea of choices. But the real danger lies in the aftermath of a poor database selection. It's not just a ticking time bomb; it's a full-blown explosion in the advanced stages of application development, turning a switch to a more suitable database technology into a living nightmare.
This article aims to empower developers with enough knowledge to make informed decisions about various popular relational databases.
Core Feature Comparison: PostgreSQL Vs. MariaDB Vs. MySQL Vs. SQLite
The core features of a database often emerge as the pivotal factor in your choice of database technology. Whether it's the architecture or the feature set implemented by a database, these properties can significantly sway your decision. Given the importance of these core features, we begin our discussion with an extensive comparison of these across the four databases.
1. Database Architecture
While all of the databases we are comparing are relational databases, there are some significant architectural differences among them. Let's explore these differences.
-
PostgreSQL: PostgreSQL's unique architecture as an Object-Relational DBMS (ORDBMS) is a game-changer. It not only caters to the needs of a relational database but also extends its support to classes, objects, and inheritance. This powerful capability of PostgreSQL effectively bridges the gap between relational databases and object-oriented databases, opening up a world of possibilities for a wide range of applications.
Example: This object-oriented perspective of PostgreSQL is evident in several places. Every table in PostgreSQL has a corresponding data type created, and each row of a table can be seen as an instance of that data type.
CREATE TYPE car_type AS (id integer, maker text, model text);
CREATE TABLE car OF car_type;
You can also create tables inherited from other tables.
CREATE TABLE ev_car (battery_capacity integer) INHERITS (car);
Additionally, PostgreSQL is fully ACID-compliant in all of its configurations. This is a desired guarantee for many enterprise-grade applications! Moreover, it is the closest database to complete conformance with the SQL standards.
PostgreSQL's implementation of MVCC (Multi-Version Concurrency Control) is a testament to its efficiency. Unlike other databases that rely on locks or undo logs, PostgreSQL maintains versioned copies of DB objects. This approach is not just efficient, it's highly efficient, particularly when dealing with a large number of concurrent users and long running transactions. This reassures you of its performance in handling your database's needs.
-
MySQL: This is a pure relational database with an architecture optimized for read-heavy workloads. It uses a single process for multiple users, resulting in better read performance overall compared to other databases!
MySQL offers a flexible architecture, supporting around 15 DB engines in addition to its default InnoDB engine. MySQL allows you to specify database engines at the table level using the TYPE parameter.
Example: Using ISAM engine for a table in MySQL.
CREATE TABLE car (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
maker TINYTEXT,
model TINYTEXT
) TYPE=ISAM
-
MariaDB: This is a fork of MySQL and thus shares many architectural features with MySQL. The project was initiated by MySQL's developers after its acquisition by Oracle, but since then, both databases have been developed independently.
MariaDB's architecture offers improved scalability and performance. It has a larger thread pool for better concurrency, only offered in MySQL's enterprise version. It also provides better performance in terms of the number of queries served per second.
Columnar storages have proven to outperform their row-based counterparts for workloads heavy on analytics. MariaDB sets itself apart by offering a dedicated DB engine for columnar storage, known as ColumnStore. This unique feature positions MariaDB as an exceptional choice for OLAP workloads, further enhancing its appeal for specific use cases.
Example: Using ColumnStore in MariaDB
CREATE TABLE `experiment` (
`time` datetime NOT NULL,
`scientist` varchar(1024) NOT NULL,
`content` text NOT NULL
) ENGINE=Columnstore
-
SQLite: Unlike the other three databases, SQLite is a serverless database! A single-file database, it is lightweight, simple, and quickly embeddable. This unique architecture makes it a great choice for embedded applications.
SQLite, a client-side database, operates on a relational data storage model. However, it does come with some limitations when compared to the other three databases. Its unique architecture, while offering benefits, also means that SQLite has limited transaction and concurrency support.
2. Data Types & Unique Functionality
All four databases encompass a comprehensive set of basic datatypes, including text, number, boolean, and date, for regular use cases. However, differences start to appear when dealing with advanced data types. Let us compare the support offered by various databases for advanced data types.
- JSON - With the increased adaption of JSON in web applications, databases are often required to persist JSON data directly. Both MySQL and PostgreSQL provide support for JSON datatypes. This support is absent for MariaDB and SQLite, and the best we can do is serialise JSON as a string.
- Arrays - PostgreSQL is the only database among the four that supports storing arrays in database tables. Arrays can be persisted as JSON in MySQL, but there is no support for storing arrays natively. Again, MariaDB and SQLite do not provide support for persisting arrays natively.
- Geospatial Data - Barring SQLite, all the other databases support Geospatial Data. PostgreSQL's PostGIS extension supports over 600 spatial operators. MySQL, with its Spatial extension and MariaDB support over 160 operators.
Each database offers unique features that may be suitable for specific use cases. Here is a list of popular features provided by each database.
| Database | Features |
|---|---|
| PostgreSQL | Materialized Views, Pub/Sub Notifications |
| MySQL/MariaDB | Invisible Columns, Temporary Tablespaces |
| SQLite | Virtual Table |
3. Performance & Scalability
The performance of a database depends on several factors, such as data volume, mix of operations, number of concurrent users, and hardware capability. Therefore, a statement like “Database system X is more performant than database system Y” is incomplete without mentioning all of these variables!
MySQL/MariaDB have generally been found to provide better performance for a read-heavy workload. On the other hand, when the workload consists of both read and write operations, PostgreSQL outperforms other databases. For very simple queries on small to medium-sized databases, SQLite will be the most efficient since there is no overhead of maintaining a server and heavy concurrency machinery.
NOTE: It is very important to characterize your workloads and compare database performance on each workload instead of using a general performance comparison!
Talking about scalability, all databases, except SQLite, offer replication and horizontal scalability. Between MariaDB and MySQL, MariaDB is known to be better scalable.
Development Experience
Besides the core features, general development experience is another significant factor when deciding on a database. This section will explore key factors influencing your experience working with each database option.
1. Ease of Use & Learning Curve
Installation and Setup Complexity
Regarding setup and installation, SQLite wins the race by miles. With its server-less architecture and zero-configuration setup, it is the simplest to spin up and use. Everything needed to use SQLite comes bundled with the application!
Both MariaDB and MySQL offer a relatively straightforward configuration and usage experience. This ease of installation is a key factor in their popularity for quick Proof of Concept (PoC) and ideation, as it allows developers to focus on their projects rather than the setup process.
PostgreSQL has the most complex configuration of all four. Installing it can be challenging, especially for beginners. While efforts are being made to simplify its installation, it still lacks MariaDB and MySQL in terms of ease of installation.
Learning Curve
Without the complexities of large database systems, SQLite has the gentlest learning curve. With some basic knowledge of SQL and data persistence, anyone can start using and working with SQLite.
Both MySQL and MariaDB, with their shared architecture, offer a smooth learning curve for beginners. The knowledge you gain from one database can be easily applied to the other, making the learning process even more efficient.
With its extensive set of features, PostgreSQL has the steepest learning curve. Before they can start using it, users should be familiar with the ORDBMS architecture and several database configurations.
2. Community & Support
All four databases are open-source and highly popular, so they provide good community support. However, MySQL also provides an enterprise version, and some feature requests might be denied in the community version in favor of the enterprise version.
3. Third-Party Tools and Integrations
All four databases enjoy a rich repository of third-party tools and integrations. These tools simplify day-to-day database management and usage.
PostgreSQL
PostgreSQL has a large collection of extensions that add a number of features to it. Some of the popular ones are PostGIS, LTree, and HStore. All four databases are open-source and highly popular, so they provide good community support. However, MySQL also provides an enterprise version, and some feature requests might be denied in the community version in favor of the enterprise version.
- Management Tools - psql and pgAdmin.
- Backup & Restore - pg_dump and pg_restore.
- Monitoring - pg_stat_activity and pg_stat_statement
MySQL and MariaDB
MySQL and MariaDB being interoperable shares most of the tools in common.
- Management Tools - mysql and phpMyAdmin
- Backup & Restore - mysqldump and mysqlimport
SQLite
SQLite too offer tools like sqlite3 to manage database from command line.
Database Integration with Strapi
Strapi allows you to use either of these four databases for your application development purposes. Once you decide on the most suitable database technology for your application, you can configure Strapi to persist your application data inside that database. Let us understand the configurations required in Strapi for each database. Check out the documentation for database configurations in Strapi.
SQLite Integration with Strapi
SQLite is the default (quickstart) and the recommended database to quickly create an app locally. You can use the quickstart flag to automatically configure the SQLite database.
yarn create strapi-app my-project --quickstart
This should automatically open up the admin page on localhost:1337/admin. Once logged in, you can create collections and SQLite database will be used to persist all data.
By default the database file (data.db) for SQLite will be placed inside .tmp folder at the root of your Strapi project. This can be configured in the database.js file inside the config directory.
module.exports = ({ env }) => ({
connection: {
client: 'sqlite',
connection: {
filename: path.join(__dirname, '..', env('DATABASE_FILENAME', '.tmp/data.db')),
},
useNullAsDefault: true,
},
});
You can either set DATABASE_FILENAME environment variable or explicitly provide database file path in database.js. As discussed, SQLite is the easiest way to get you started when working on applications.
PostgreSQL Integration with Strapi
We can use the custom installation method for creating Strapi projects that uses PostgreSQL. For this to work, you must already have a running PostgreSQL instance on your machine.
Here is how this can be done. Specify the correct database name, host, port, username, and password for your Postgres database and Strapi will do the rest for you!
npx create-strapi-app strapi-with-postgres
? Choose your installation type Custom (manual settings)
? Choose your preferred language JavaScript
? Choose your default database client postgres
? Database name: postgres
? Host: 127.0.0.1
? Port: 5432
? Username: postgres
? Password: ********
? Enable SSL connection: No
Creating a project with custom database options.
Creating a new Strapi application at /home/strapi-with-postgres.
Creating files.
Dependencies installed successfully.
Initialized a git repository.
Your application was created at /home/strapi-with-postgres.
Available commands in your project:
yarn develop
Start Strapi in watch mode. (Changes in Strapi project files will trigger a server restart)
yarn start
Start Strapi without watch mode.
yarn build
Build Strapi admin panel.
yarn strapi
Display all available commands.
You can start by doing:
cd /home/strapi-with-postgres
yarn develop
As suggested in the output of the npx command, we can start the server and our Strapi application will now use the Postgres database. You might see errors like below if the PostgreSQL database is not running or is not configured properly.
┌───────────────────────────────────────────────────────────────────────────┐
│ │
│ Error: connect ECONNREFUSED 127.0.0.1:5432 │
│ at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1195:16) │
│ │
└───────────────────────────────────────────────────────────────────────────┘
Again, you can find all the database configuration inside the config/database.js file.
postgres: {
connection: {
connectionString: env('DATABASE_URL'),
host: env('DATABASE_HOST', 'localhost'),
port: env.int('DATABASE_PORT', 5432),
database: env('DATABASE_NAME', 'strapi'),
user: env('DATABASE_USERNAME', 'strapi'),
password: env('DATABASE_PASSWORD', 'strapi'),
ssl: env.bool('DATABASE_SSL', false) && {
key: env('DATABASE_SSL_KEY', undefined),
cert: env('DATABASE_SSL_CERT', undefined),
ca: env('DATABASE_SSL_CA', undefined),
capath: env('DATABASE_SSL_CAPATH', undefined),
cipher: env('DATABASE_SSL_CIPHER', undefined),
rejectUnauthorized: env.bool(
'DATABASE_SSL_REJECT_UNAUTHORIZED',
true
),
},
schema: env('DATABASE_SCHEMA', 'public'),
},
pool: { min: env.int('DATABASE_POOL_MIN', 2), max: env.int('DATABASE_POOL_MAX', 10) },
}
For a comprehensive tutorial on setting up Strapi with Postgres, refer to this article.
MySQL/MariaDB Integration with Strapi
The configuration process for MySQL/MariaDB is similar to that of PostgreSQL. We can use custom installation method for starting a MySQL/MariaDB based Strapi application.
npx create-strapi-app strapi-with-mysql
? Choose your installation type Custom (manual settings)
? Choose your preferred language JavaScript
? Choose your default database client mysql
? Database name: strapi-with-mysql
? Host: 127.0.0.1
? Port: 3306
? Username: mysql
? Password: *****
? Enable SSL connection: No
Creating a project with custom database options.
Creating a new Strapi application at /home/strapi-with-mysql.
Creating files.
Dependencies installed successfully.
Initialized a git repository.
Your application was created at /home/strapi-with-mysql.
Available commands in your project:
yarn develop
Start Strapi in watch mode. (Changes in Strapi project files will trigger a server restart)
yarn start
Start Strapi without watch mode.
yarn build
Build Strapi admin panel.
yarn strapi
Display all available commands.
You can start by doing:
cd /home/strapi-with-mysql
yarn develop
This will add the following configuration inside the database.js file. It lists out the client, host, password, database, and user to be used for connecting.
mysql: {
connection: {
connectionString: env('DATABASE_URL'),
host: env('DATABASE_HOST', 'localhost'),
port: env.int('DATABASE_PORT', 3306),
database: env('DATABASE_NAME', 'strapi'),
user: env('DATABASE_USERNAME', 'strapi'),
password: env('DATABASE_PASSWORD', 'strapi'),
ssl: env.bool('DATABASE_SSL', false) && {
key: env('DATABASE_SSL_KEY', undefined),
cert: env('DATABASE_SSL_CERT', undefined),
ca: env('DATABASE_SSL_CA', undefined),
capath: env('DATABASE_SSL_CAPATH', undefined),
cipher: env('DATABASE_SSL_CIPHER', undefined),
rejectUnauthorized: env.bool(
'DATABASE_SSL_REJECT_UNAUTHORIZED',
true
),
},
},
pool: { min: env.int('DATABASE_POOL_MIN', 2), max: env.int('DATABASE_POOL_MAX', 10) },
}
For a more comprehensive tutorial on using MySQL/MariaDB with Strapi, refer to this article!
Use Cases & Recommendations
Equipped with the knowledge of core features and development experience for each of the database, it is time for us to identify what scenarios makes one database preferrable over the other.
SQLite: This is most suited for embedded applications. It can also be a viable option if you are working with low volumes of data without much concern for data concurrency.
MySQL: MySQL is a good choice for quick prototyping. Additionally, if your workload consists of bulk reads and a comparatively smaller number of writes, MySQL is a better candidate.
MariaDB: The fact that MariaDB and MySQL share the same architecture implies that both databases are good candidates for many everyday use cases. But if you are looking for a scalable database that offers high query speed, then MariaDB is the way to go.
PostgreSQL: PostgreSQL becomes the best option when building large and complex enterprise-grade applications. Its rich feature set and SQL standard conformance offer everything you need from a relational database. Strapi allows you to use either of these four databases for your application development purposes. Once you decide on the most suitable database technology for your application, you can configure Strapi to persist your application data inside that database. Let us understand the configurations required in Strapi for each database.
Comparison Matrix: PostgreSQL Vs. MariaDB Vs. MySQL Vs. SQLite
The image below shows some summarized differences between the top relational databases discussed so far.
Conclusion
Each database has its own strengths and weaknesses. While one might be easy to install and use, the other might offer more features. When dealing with large volumes of data requiring complex queries, PostgreSQL is the way to go. For embedded applications or low volume-low concurrency use cases, SQLite offers the best solution. MariaDB/MySQL is a better option for medium data volumes and read-heavy workloads.
Choosing the correct database for your particular use case is challenging. Finding one that best aligns with your needs takes a lot of research and experimentation. A thorough understanding of core database features facilitates making informed decisions, as explored in this comparison of PostgreSQL, MySQL, MariaDB, and SQLite. While this article covered the four most popular relational databases, a similar exercise can be done for any database.
