Popular Open-Source RDBMSs
SQLite, MySQL, and PostgreSQL are currently the most widely implemented open-source relational database management systems (RDBMSs). Here, we will explore how each specialises in various types of tasks.
Table of Contents
- Server-Based Databases vs. Serverless Databases
- Data Types
- SQLite
- MySQL
- PostgreSQL
- How to choose among SQLite, MySQL, and PostgreSQL?
Server-Based Databases vs. Serverless Databases
| Server-Based | Serverless |
|---|---|
| - database engines implemented as a server process - allow programs to communicate with the host server - an interprocess communication that relays requests - e.g. mySQL and PostgreSQL | - interact (access, read, and write) with the database disk file directly - e.g. SQLite |
Data Types
A comprehensive list of data types used in varous relational databases can be found here. Here we provide a brief summary of the data types supported by SQLite, MySQL and PostgreSQL, respectively:
| SQLite | MySQL | PostgreSQL |
|---|---|---|
- null - integer: signed integers - real - blob: Binary Large Objects | - several numerical types - several date and time types - several string types | - in addition to numeric, string, and date and time - also supports geometric shapes, network addresses, bit strings, text searches, JSON entries |
SQLite
- a serverless database
Pros
- Lightweight
- SQLite can take up very little disk space and operate without external dependencies.
- Easy to use
- SQLite is not a server process. There is no need to go through steps associated with server processes, e.g. starting/stopping/restarting the process and managing configuration files.
- Portable
- An entire SQLite database is stored in a single file, whereas other DBMSs typically store data as a large batch of separate files. Hence, SQLite projects can be easily shared.
Cons
- Limited concurrency
-
- Concurrency – the ability of two transactions to use the same data at the same time
-
- Multiple processes can access and query an SQLite database, but only one process can make changes to the database at any given time.
- No user management
- SQLite allows users to read and write directly to disk, which is poor for applications that require multiple users with special access permissions.
- Security
- Being serverless may not be able to shield the database from bugs in the client application. Server database can control data access with more precision.
MySQL
- the most popular relational database since 2012
- powers many of world’s largest websites and applications like Twitter, Facebook, Netflix, and Spotify
- exhaustive documentation and large community of developers available
- peed and reliability at the expense of full adherence to standard SQL
Pros
- Popularity & ease of use
- There are experienced data administrators in the job market and abundant support online.
- Security
- MySQL supports user management, as opposed to SQLite.
- Replication
- MySQL supports setting up a database backup solution and horizontally scaling a database.
Cons
- Functional limitations
- MySQL does not fully support standard SQL.
- Licensing and proprietary features
- MySQL is dual-licensed with (1) free, open-source edition licenced under GPLv2 and (2) commercial editions released under proprietary licenses. Some features and plugins are only available for the proprietary editions.
- Slowed development
- Oracle’s acquisition of Sun resulted in slowed development process and led to lack of agile responses to problems.
PostgreSQL
Pros
- SQL compliance
- PostgreSQL closely adheres to SQL standards.
- Open-source & community-driven
- PostgreSQL is fully open-source with abundant online resources available.
- Extensible
- It is possible to extend PostgreSQL programmatically and on the fly.
Cons
- Memory performance
- Each new process is allocated about 10MB of memory, hence, PostgreSQL is not ideal for simple read-heavy operations.
- Not as popular as MySQL
- Fewer third-party tools can help manage a PostgreSQL database. There are not as many experienced database administrators available (as is the case for MySQL).
How to choose among SQLite, MySQL, and PostgreSQL?
Here is a list of questions that you may ask yourself/your team/your clients before deciding on the RDBMS for a specific project.
1. Are you working with a big amount of data?
- Yes → maybe not SQLite (poor when used to work with data exceeding 1TB)
2. What is your database setup?
- A simple setup that allows one to read/write to the disk directly → consider SQLite
- A distributed database setup → consider MySQL
3. How important is it to maintain data integrity in this project?
- Very important → consider PostgreSQL
4. What are the types of operations needed?
- Simple operations and quick tests → consider SQLite
- Concurrent read-write → consider PostgreSQL (better) or MySQL
5. Do you need SQL compliance?
- Yes → consider PostgreSQL
6. Is network access required?
- Yes → maybe not SQLite
7. Is this DBMS integrated with other tools?
- Yes → consider PostgreSQL
8. Is this DBMS used to power websites or web applications?
- Yes → consider MySQL
9. Do you need to replicate this DBMS?
- Yes → consider MySQL