Introduction
First of all, a distinction should be made between the terms: “database”, “database management system” (so-calledDBMS “Database Management System”) and the “SQL language” itself.
“Relational database” is a digital database based on the relational data model and is a set of related data that replaces some aspect of the real world.
“Relational Database Management System” (RDBMS) is software used to maintain relational databases. It provides an interface between the data and the software application ie. accepts a request for data from an application and instructs the operating system to provide certain data.
SQL (Structured Query Language) is a language used to manage relational databases. This language is designed to allow the user to store, retrieve, manage or manipulate data within a database management system
database (DBMS).
SQLlite
SQLite is a “file-based” system that does not require any installation or setup and is extremely small in size around 700KB. This means that the application does not run within a separate server process that needs to be started, stopped, or configured. This serverless architecture allows the database to be cross-platform compatible.
The complete SQL database is contained in a single disk file and all reads and writes take place directly on this disk file. Since the SQLite database requires no administration, it works well on devices that need to operate without human support. SQLite is suitable for use in mobile phones, set-top boxes, televisions, game consoles… This is a simple system that supports only five data types: BLOB, NULL, INTEGER, TEXT, REAL (while more advanced dbms support almost all possible types). Its very simplicity allows it to be very fast, unlike other more advanced dbms.
One of the main disadvantages of the SQLite system is the lack of multi-user capabilities that can be found in RDBMS systems such as MySQL and PostgreSQL. Because it works with only one user, SQLite does not provide an authentication system when accessing the database file. Another major drawback of SQLite is its handling of write operations that are serialized. This can be a major bottleneck for applications that require concurrent operations.
MySQL
Unlike SQLite, MySQL uses a server/client architecture consisting of a multi-threaded SQL server. This multi-threaded nature of MySQL allows for higher performance as core threads can easily use more CPUs. This system has security features that enable user authentication, a user account management system, and encrypted connections using SSL. Unlike SQLlite, MySQL supports working with multiple users. This system is available on all major platforms: Windows, Linux and Mac OS X.
The disadvantage of MySQL is that a large number of INSERTs can have a negative impact on performance (PostgreSQL excels at this). It also doesn’t work well with long SELECTs and in the case of MySQL is best used with smaller SELECTs.
PostgreSQL
Like MySQL, PostgreSQL uses a client/server database model. PostgreSQL has the ability to handle concurrent client sessions by creating (“forking”) a new process for each connection. Like MySQL, PostgreSQL also has several advanced features related to security and replication. Because of its superior parallel processing capabilities, PostgreSQL excels (unlike MySQL) when running long SELECTs. When compared to MySQL in terms of energy consumption, it is significantly worse and consumes a lot of energy. Another major drawback can be observed during frequent UPDATEs, where due to the unsupported clustered indexes, PostgreSQL can have a negative impact on performance compared to MySQL databases.
Microsoft SQL Server
This is Microsoft’s relational database management system that also uses a client/server database model. The advantages of this system are that it is relatively easy to maintain, easy to use and understand, and very easy to access and administer. It can be used for small and large projects. SQL Server was developed by Microsoft originally only for the Windows operating system, although Microsoft has announced a decision to make the RDBMS available on Linux as well as Mac OS (via Docker).
One of the disadvantages of this system is that newer versions need advanced technologies to run. So, if your hardware consists mostly of older hardware, you may need to invest in newer machines to use Microsoft SQL Server.