SQL (structured query language) has paved the way for the development of many relational database management systems (RDBMS) since its standardization. Aside from their flexibility, RDBMSs continue to stay popular because they are among the easiest database systems to learn.
In this article, we’ll thoroughly compare & review three of the best RDBMSs—MySQL, SQLite, and PostgreSQL. Fortunately, we’ve compiled everything you need to know, including their benefits, disadvantages, and their best use cases.
Some Background About Database Management Systems
In the simplest sense, a database is a storage of data. To access the stored data, you’ll need a program called a database management system (DBMS). DBMSs come in various types, such as Hierarchical DBMS, Network DBMS, Object DBMS, and Relational DBMS. The latter of which has gained the most popularity.
DBMSs have varying approaches to managing data. In the context of an RDBMS, data is managed in tables—otherwise known as relations. Each datum can have one or more attributes, which can be retrieved and processed through queries. Generally, RDBMSs requires a special language called structured query language (SQL) for you to query and process data.
Most RDBMSs adhere to SQL standards, so if you are familiar with this language, there’s a high chance that you can easily learn and adjust to different database systems right away. Now, let’s take a look at the most popular RDBMSs and gauge their benefits.
SQLite
As we can infer from its name, SQLite is a lightweight RDBMS. SQLite is a popular choice for applications that require embedded databases instead of client-server architecture. If you’re looking for a fast and compact database system that doesn’t require many configurations, SQLite should be on the top of your list.
Unlike other RDBMSs, SQLite does not require transmission protocols such as TCP/IP when communicating with applications. It provides them instead with direct read and writes access to the actual database files. This means that there’s no server process between the application and the database, resulting in faster operations.
SQLite complies with ACID (atomicity, consistency, isolation, durability)—another factor as to why it sits on top of the list. The term ACID is a computer science concept intended to standardize how databases should guarantee data validity in spite of power outages, errors, and other technical problems.
SQLite’s Supported Data Types
There are several data types supported by SQLite. These data types are organized into the following classes:
| Data Type | Description |
| BLOB (Binary Large Object) | Any type of object |
| INTEGER | Whole numbers stored in 1, 2, 3, 4, 6, or 8 bytes |
| NULL | Null data type can store null or empty value |
| REAL | A floating-point value, stored as an 8-byte floating point |
| TEXT | Text string, stored using UTF-8, UTF-16BE, or UTF-16LE |
Advantages of SQLite
- Portable. An SQLite database is stored in a single file only. When copied, you can already utilize it out of the box without any configuration.
- Serverless. Since SQLite is serverless, you don’t need to install, configure and manage a separate server process just to make it work.
- Cross-platform. The developers make sure that SQLite remains platform-independent. It runs on 8-bit, 32- and 64-bit platforms that have a C compiler. Testing is regularly conducted on all operating systems and CPUs in the market, so you don’t have to worry about compatibility issues.
Disadvantages of SQLite
- Lack of Security. Although there are advantages of a serverless database, it’s still considered a double-edged sword. The main concern for this setup is its lack of security. The absence of security measures for user management can cause unauthorized access, data corruption, and other issues.
- The tendency of performance bottleneck. SQLite can handle simultaneous queries to access data, but it only allows one process to update data at a given time. Hence, there’s a chance that you’ll experience a decline in your application’s performance, especially when it extensively implements concurrent changes on the database.
What is SQLite Good For?
SQLite is a great choice for projects that do not require constant IT support. It’s the perfect database for portable applications like those installed in automobiles, appliances, robots, mobile games, and the “internet of things”.
What Shouldn’t SQLite Be Used For?
There may be countless applications of SQLite but it shouldn’t be your go-to database. You must not force it on complex applications that require multi-client architecture. Its lack of user management and other features present in other RDBMSs might complicate your entire development process.
Another instance where you would consider other database systems over SQLite is when your application needs to execute simultaneous updates of data. Cases like this commonly crash SQLite, which makes it a bad choice for this scenario.
MySQL
Currently, MySQL is one of the most highly-regarded database management systems. It provides most of the ideal features that a developer could ask for in an RDBMS. MySQL is known to be a fast and reliable database engine that can efficiently support high-volume applications.
MySQL works best in a client-server structure. It uses server processes to work in between a client and the database. Thus, allowing you to integrate better security approaches as you have complete control over who can access data.
MySQL’s popularity and high performance are evident as it powers the likes of Shopify, Twitter, Uber, and other popular platforms.
MySQL’s Supported Data Types
MySQL supports most of the commonly-used data types that are categorized by numeric, string, and also date and time.
Numeric Data Types
| Data Type | Description |
| BIT(size) | It can store bit values. You can specify a value from 1 to 64 in the size parameter. |
| BIGINT(size) | This type of data is a large signed or unsigned integer. Signed integer ranges from -9223372036854775808 to 9223372036854775807. For unsigned, the range is from 0 to 18446744073709551615. The maximum display width which is denoted by size is 255. |
| BOOL or BOOLEAN | The possible values for these data types are only true or false. |
| DEC(size, digits) or DECIMAL(size,digits) | A decimal data type is an exact fixed-point number. The size parameter holds the total number of digits, while digits specifies the actual number of digits after the decimal point. |
| DOUBLE(size, digits) or DOUBLE PRECISION(size, digits) | Double data type represents a normal-size floating-point number. Like decimal, you can specify the total number of digits and decimal numbers on the size and digits parameters respectively. |
| FLOAT(p) | Float signifies a floating-point number. The parameter p is used to specify whether the resulting data type will be of type float or double. |
String Data Types
| Data Type | Description |
| BINARY(size) | This data type stores binary byte strings. You can specify the column length using the size parameter. |
| BLOB(size) | Blob data type can hold a binary string up to 65,535 bytes. |
| CHAR(size) | A char data type is a fixed-length string that may contain numbers, letters, and special characters. |
| ENUM(a,b,c…) | An enumerated data type can have one value from a list of possible values. |
| LONGBLOB | A blob data type that can hold up to 4,294,967,295 bytes. |
| LONGTEXT | Like text data type, a longtext can hold a string but up to 4,294,967,295 characters |
| MEDIUMBLOB | Mediumblob stores up to 16,777,215 bytes. |
| MEDIUMTEXT | A text data type that can contain a maximum of 16,777,215 characters. |
| SET(a,b,c…) | A data type that can store a unique value |
| TEXT(size) | Text can hold a string of up to 65,535 bytes of data |
| TINYBLOB | Tinyblob stores binary large objects with a maximum length of 255 bytes. |
| TINYTEXT | A text data type that holds up to 255 characters only. |
| VARBINARY(size) | Stores binary byte strings. |
| VARCHAR(size) | Similar to char data type, it can store strings that range from 0 to 65535 characters. |
Date and Time Data Types
| Data Type | Description |
| DATE | A basic date value with YYYY-MM-DD as the format. |
| DATETIME(fsp) | As the name implies, it’s a data type that holds a date and time combination. Its supported format is YYYY-MM-DD hh:mm:ss. You can define a fractional seconds precision for data types that include time using the fps parameter. |
| TIMESTAMP(fsp) | Timestamps can be stored using this data type. The supported date range is from “1970-01-01 00:00:01” UTC to “2038-01-09 03:14:07” UTC. |
| TIME(fsp) | You can use the TIME data type for a basic time format—hh:mm:ss. |
| YEAR | A year formatted in four-digit. |
Advantages of MySQL
- Top-notch security. MySQL offers best-in-the-business security. Their user management feature is one of their best selling points, which lets you define user passwords as well as manage and remove user accounts. As the administrator, you are also given the facility to control the access privileges of each user.
- Large Community. Since MySQL is well-known, thousands of tutorials and references are available online. You’ll find yourself working comfortably with this DBMS as many experts are actively conducting or participating in online forums.
- High Performance. Speed is a vital factor that should be considered in any application. MySQL is well-optimized even for complex systems like a networked POS (Point Of Sale) that processes frequent transactions.
Disadvantages of MySQL
- Partially SQL standard-compliant. MySQL does not fully comply with SQL standards. Although this factor may be overlooked by some people, it might still have a great impact on large-scale applications. Think about a scenario wherein you need to migrate from MySQL to another database that completely adheres to standards. You’ll need to spend a significant amount of time just to update your queries.
- A decline in technical support. There have been many complaints by active users that the technical support behind MySQL has declined in recent years. According to some reviews, MySQL is no longer community-driven since it was acquired by Oracle Corporation.
What is MySQL Good For?
MySQL can be your ideal RDBMS if you want to develop a system of distributed databases. In contrast with “serverless” databases, MySQL is best utilized for client-server setup. Consider this database engine for extensive projects such as ERPs (Enterprise Resource Planning) and e-commerce systems.
What Shouldn’t MySQL Be Used For?
Even though MySQL is optimized for most high-volume applications, it shouldn’t be your preference for systems that consistently require concurrent operations. For this instance, you might want to consider a better choice like PostgreSQL.
PostgreSQL
PostgreSQL, or Postgres, is an object-oriented RDBMS. Hence, it is commonly termed as ORDBMS. It played a significant role in pioneering more advanced database concepts such as updatable views, transactional integrity, and multi-version concurrency control (MVCC). Additionally, Postgres also introduced new data types that cover a wide range of applications.
PostgreSQL may not be as famous as MySQL at the moment, but its trend continuously rises because of its laudable performance that has satisfied many developers. Its MVCC feature makes it a very efficient database system able to process and manage tasks simultaneously.
PostgreSQL’s Supported Data Types
In addition to numeric, string, and date-time data types, PostgreSQL supports advanced types such as geometric, network address, and JSON data. The following shows the complete data types PostgreSQL supports.
Numeric Data Types
| Data Type | Description |
| bigint | An 8-byte large-range integer. |
| bigserial | A large auto-incrementing integer of 8 bytes. |
| decimal or numeric | An exact user-specified point number. The left side of the decimal point can hold up to 131072 digits, while the right stores a maximum of 16383 digits. |
| double precision | An 8-byte floating-point data type that can hold up to 15 decimal digits. |
| integer | A 4-byte integer. |
| real | A 4-byte floating-point number. |
| serial | A 4-byte auto-incrementing integer. |
| smallint | A small-range 2-byte integer |
| smallserial | A 2-byte small auto-incrementing integer. |
Character Data Types
| Data Type | Description |
| character varying(n), varchar(n) | A fixed length character data type. |
| character(n), char(n) | A character with specified length. |
| text | A string data type with unlimited length. |
Date and Time Data Types
| Data Type | Description |
| date | A plain date data type which consists of day, month and year. |
| interval [ fields ] [ (p) ] | A time interval. |
| time [ (p) ] with time zone | A time data type which includes the time zone |
| time [ (p) ] [ without time zone ] | A plain time data type. |
| timestamp [ (p) ] with time zone | A data type which consists of date and time, and also the time zone. |
| timestamp [ (p) ] [ without time zone ] | A date and time data type. |
Geometric Data Types
| Data Type | Description |
| box | A rectangular box represented by pairs of points |
| circle | A circle with a representation of a center point and radius. |
| line | An infinite line represented by {A,B,C}. |
| lseg | A finite line segment sign. |
| path | A path on a surface represented by connected points. |
| point | A point on a surface. |
| polygon | A closed path on a plane represented by lists of points. |
Network Address Data Types
| Data Type | Description |
| cidr | IPv4 or IPv6 network specification |
| inet | IPv4 or IPv6 host address |
| macaddr | MAC address |
| macaddr8 | A MAC address in EUI-64 format |
Bit String Data Types
| Data Type | Description |
| bit | A bit string of fixed-length |
| bit varying | A bit string of variable length |
Text Search Data Types
| Data Type | Description |
| tsquery | A representation of a text query |
| tsvector | A text search document |
JSON Data Types
| Data Type | Description |
| json | Stores an exact copy of a JSON data |
| jsonb | A decomposed binary JSON data |
Other Data Types
| Data Type | Description |
| boolean | A data type that accepts either true or false state |
| bytea | A variable-length binary string |
| money | A currency amount with fixed fraction precision |
| pg_lsn | A data type used to store a log sequence number. |
| uuid | A data type of universally unique identifier |
| xml | A data type that stores XML data |
Advantages of PostgreSQL
- It’s catalog-driven. A special feature of PostgreSQL is its extensibility. While standard RDBMSs store basic information of databases as in tables and columns, PostgreSQL does more than that. It also saves all the data types, access methods, and functions of all the tables in catalogs.
- Fully open-source and has an extensive community. PostgreSQL’s performance initiated a wide community of active developers. Unlike MySQL, PostgreSQL maintains community-driven technical support. With its comprehensive official documentation and numerous references online, you can easily learn and take advantage of all its features in building large-scale applications.
- Compatible with most platforms. Another factor why you should consider PostgreSQL is its flexibility. You won’t need to spend a great deal of trouble in migrating your database into another operating system as it is compatible with most of them.
Disadvantages of PostgreSQL
- Limited tools to manage a PostgreSQL database. PostgreSQL is relatively new compared to MySQL, so there’s a limited number of third-party tools at the moment to help manage a PostgreSQL database.
- Costly memory allocation. According to Okta, for every client connection in Postgres, it forks a new process with a 10-MB memory allocation. This may not seem like much. But, when multiple connections are used, the server may crash, especially if it has a limited amount of memory.
What is PostgreSQL Good For?
In addition to the advantages stated above, PostgreSQL has features that are capable of performing data analytics. With these tools, PostgreSQL can be best utilized for the development of applications that implement data visualization like Business Intelligence (BI) systems.
What Shouldn’t PostgreSQL Be Used For?
Despite its wide scale of applications, you should not be enticed to use PostgreSQL all the time. For systems that don’t execute concurrent or heavy database processes, you might just opt for lighter RDBMS. It might be overkill to use PostgreSQL for portable applications.
Conclusion
The definition of “the best relational database management system” always depends on different situations. We’ve covered three popular RDBMSs in this article to enlighten you on under what circumstances you can favor one over the other two.
There is no perfect RDBMS, as you have noticed that each of them has its own pros and cons. Aside from popularity, choosing the right database for your applications demands a thorough understanding of the scale and the real purpose of your projects. The best RDBMS for you is the one that caters to all your needs at the lowest cost.
