SQLite vs MySQL vs PostgreSQL – The Search For The “Best” Relational Database Management System

Relational data structures that organize data in tables of rows and columns form the basis of how most online services we use function. Although there are other data models, relational database management systems remain the global standard for storing and managing data.

So in this article, we’re going to dive deep and compare the three most widely-known and adopted relational database management systems (RDBMSs) – MySQL, SQLite, and PostgreSQL – as well as the situations in which each makes the most sense...

Some Background About Database Management Systems

Before we begin, here's some important context on databases and database management systems you should know...

The Different Models of DBMSs

Database management systems use different models that determine the way users store and interact with databases: 

• Hierarchical database model
• Relational model
• Network model
• Object-oriented database model
• Entity-relationship model
• Document model
• Entity-attribute-value model
• Star schema
• Object-relational model  — a mixture of the relational and the object-oriented model

The relational database management systems, which we are analyzing here, use the relational data model for these tasks. This model organizes data in tables made up of columns and rows — or relations, as they are referred to in relational database management system terminology. 

These relations (tables) consist of tuples (table rows), and each of those tuples has a set of attributes (table columns) that they relate to in the relation.

On Standard SQL

In this article, we’ll use the term ‘standard SQL’ to refer to this generic SQL query language. 

When it comes to SQL standards, there are a number of institutions that are in charge of regulating them. To clarify, when we use the term ‘standard SQL’ or ‘the SQL standard’, we will be talking about the to-date version of the standard for SQL issued by the following institutions:

• IEC  — The International Electrotechnical Commission;
• ISO  — The International Organization for Standardization;
• ANSI  —The American National Standards Institute.

Additionally, bear in mind that the full structured query language standard is quite complex and needs to have all 179 features to qualify for the full core SQL:2011 compliance. For this reason, the majority of relational database management systems don’t comply with the whole standard. However, the degree to which a particular RDBM complies with the full core can vary, as some relational database management systems comply with the SQL:2011 more than others.

How Does It Work?

Each column in the table receives a designated data type. This determines (and limits) the type of entries that could be entered in that particular column. 

The data types implemented by the different relational database management systems can vary, and they are not always interchangeable by rule. Some commonly used data types are:

• Integers
• Booleans
• Dates
• Strings

There are two kinds of numeric data types: signed and unsigned. 

Signed numeric data types indicate the data can stand for both positive and negative numbers. Unsigned types, on the other hand, indicate data that represents positive numbers only. 

Let’s take MySQL’s ‘smallint’ data type as an example. Its storage capacity is 2 bytes or 16 bits of data  — when it comes to possible values, this translates into 65536 possible values. This means that the signed value range of this data type ranges from -32768 to 32767, and the unsigned value range, on the other hand, can go from 0 to a maximum value of 65535.

What Are SQL Constraints?

In some cases, database administrators can institute a constraint on the number of values that can be inserted in a table. These constraints can apply to either a single column (which is more frequent) or to the whole table. 

Some of the constraints that are widely used in structured query language are the following:

• UNIQUE: Ensures that all values in a column are unique.
• NOT NULL: A constraint that disallows the use of NULL entries within the relevant column.
• PRIMARY KEY: This constraint is a shortcut for the UNIQUE and NOT NULL constraints. It ensures that no entry in a particular column can be identical to another and that that same column can’t have a NULL value.
• FOREIGN KEY: A constraint used to link two tables together via a field (or collection of fields) in the ‘child’ table that points to the PRIMARY KEY in the ‘parent’ table.
• CHECK: Restricts the range of values that users can insert in a relevant column. If you wish to, let’s say, make your software available only to people that live in the state of Colorado, you can apply the CHECK constraint to a ZIP code column and set it up to permit entries from 80001 to 81658 only.
• DEFAULT: A constraint which serves to assign a default value to a column. The SQLite RDBMS, for example, will automatically insert this default value, except in cases where the user inserts a different value.
• INDEX: This is a constraint that helps the user extract data from a table faster, in a way that resembles an index in a textbook, as the name indicates. The query simply has to look into entries from the indexed column in order to reach the results it’s searching for, which saves you the time you’d spend looking into each separate entry from the table.

The above is a short and generalized glance at relational database management systems. From here on we will continue by analyzing each of the three open-source RMDBSs that we are set to compare within this article. So, without further ado, let’s begin with the first one.

SQLite

The first relational database management system we’ll look into is SQLite, which is an entirely open-source RDBMS. 

The majority of the relational database engines use servers, that is, they relay requests to a host server. However, the SQLite website qualifies SQLite as a ‘serverless’ and self-contained RDBMS, also known as an embedded database. SQLite’s engine works from within the application software that accesses the data.

This means that a process that needs to access the database can do so directly, without needing intermediation from a server to read from or write on the disk file. 

This simplifies things when it comes to the setup of SQLite. Moreover, any program that uses SQLite doesn’t need any additional configuration apart from access to the disk file. 

SQLite is famous for being very reliable, portable, and for performing solidly even in low-memory environments. It has a library that is often under 600 kilobytes.

SQLite is a file-based RDBMS  — it stores data in one file, which means it’s simple to copy, store, and share it. Its transactions comply with ACID in a stable way, even during situations of a system crash or electrical failure.

As an open-source program, you can use this RDBMS free of charge and without having to acquire a special license.

If you’d like to get additional extensions that help with compression and encryption, these come with a one-off charge. Some of them include:

• The SQLite Encryption Extension (SEE) for $2000
• The Compressed And Encrypted Read-Only Database (CEROD) for $2000
• The ZIPVFS Read/Write Compressed Database Extension for $4000

Additionally, there are also several packages for commercial support that require a yearly subscription:

• The Annual Maintenance Subscription for $1500 a year
• The Technical Support package for $8000-50000 a year
• The SQLite Consortium Membership for $85000 a year

SQLite’s Supported Data Types

Let’s look at the data types that SQLite allows, and the storage classes they belong to:

• null — Used for NULL values.
• integer — Used for signed integers. Depending on the magnitude of the value, these integers are stored in 1, 2, 3, 4, 6, or 8 bytes.
• real — Used for real numbers or floating-point values, stored as 8-byte floating-point numbers.
• text — Used for text strings. The following database encoding can be used to store them: — UTF-8, UTF-16BE, or UTF-16LE.
• blob — Used for data blobs. Each blob is stored in the same way it was put in.

You can use either ‘data type’ or ‘storage class’ when it comes to SQLite, as both of these terms refer to the same thing. You can visit SQLite’s datatypes section of its website if you want to know more about its data types and type affinity.

Advantages of