Understanding the three major open source relational databases: SQLite, MySQL, and PostgreSQL

Databases can be divided into commercial databases and free databases, common commercial databases are Microsoft SQL Server, Oracle, Sybase, DB2, and Informix, and commonly used free databases are SQLite, MySQL, and PostgreSQL.Commercial databases are comprehensive in terms of performance and power, and they are also supported by a strong technical team. Free open source databases are no less stable and powerful, and are backed by an active open source community.

Some government and bank projects use more commercial databases, such as Microsoft SQL Server, Oracle, Sybase, DB2 and Informix. However, for most IT vendors, free and open source databases are used for cost reasons, and open source databases are more controllable.

In this article we are going to discuss about three free open source databases SQLite, MySQL and PostgreSQL. The lightweight database SQLite is mainly used in clients and embedded devices to store some lightweight data.MySQL and PostgreSQL are mainly used in server-side to store some large or massive data.

In addition, the domestic first-tier manufacturers have conducted in-depth research and layout in the field of database, and carried out in-depth optimization and innovation on the basis of open source databases such as MySQL and PostgreSQL, and developed new databases to meet the needs of their large-scale business systems for massive data storage as well as cloud databases applied in the field of cloud computing, such as Tencent’s TBase database, Ali’s Tencent’s TBase database, Ali’s OceanBase database, and Huawei’s GaussDB database. Moreover, these large manufacturers have developed a variety of databases for different business scenarios according to the needs of their own business systems.

There are several reasons why these big players have been able to play up their own innovative databases:

1) They have strong capital and talent reserves, and can invest a lot of manpower and resources to do in-depth research and layout;

2) They have their own large-scale business systems (for example, Tencent has WeChat, Ali has Taobao, and Huawei has a huge telecom operation support system, and cloud computing and big data technologies are deeply integrated into these product systems), and in order to meet the demand for efficient storage and querying of massive amounts of data in their own systems, they have to carry out continuous technological innovations on the basis of existing technologies;

(3) After the new products and technologies are applied to their own large-scale business systems, they can encounter such and such problems or bottlenecks, which can continuously promote the optimization and improvement of products and technologies.

In the past decade, many domestic database vendors have also been born, such as Henkel’s Henkel database (based on the open source PostgreSQL database), Damon’s Damon database, and NPC Kingbase database.

2、SQLite database

2.1 Introduction to SQLite

SQLite, a lightweight relational database, is a development library in C language. It is designed to be used in embedded systems, and it is used in many embedded products. It occupies very low resources, and may only need a few hundred K of memory. It supports Windows, Linux, Unix and other major operating systems, because it is developed in C, it can be combined with many programming languages, such as C++, C#, PHP, Java, Python, Ruby and so on:

1）C/C++
Since SQLite itself is written in C, the API it comes with is also a C interface. So C/C++ is the most straightforward to use. If you don’t like the process-oriented C API style, you can find another C++ wrapper library. If you want to reinvent the wheel, you can also wrap one yourself.
2）Java
If you want to use Java to access SQLite, you can go through SQLite’s JDBC driver, or through a specialized SQLite wrapper library. Personally, I recommend going the JDBC way, in case you want to change the database in the future, the code will not have to change drastically.
3）Python
pysqlite is Python’s first choice for manipulating SQLite. Since Python 2.5, it has been integrated into the Python standard library. It seems that the Python community is still quite fond of SQLite.
4）.Net
For those who prefer dotNet, it can be accessed through SQLite’s ADO.NET driver.
5）Ruby
Ruby can manipulate SQLite databases through SQLite-Ruby.

The first Alpha version of SQLite was created in May 2000 By 2023 it will be nearly 23 years old and SQLite has iterated to version 3.0. Unlike the common client-server database paradigm, the SQLite engine is not a standalone process that communicates with the database server, but rather is linked directly into the program as part of the program (either by adding .h and .c directly to the target project, or by encapsulating it as a dll for the target program to call).

The way to use SQLite database is very simple, just need to integrate it into the program, directly call the API interface provided by SQLite to complete the database and table creation, data addition, deletion, modification and other operations. The entire database (definitions, tables, indexes and data itself) is stored in one or more db files on the host host. For more information on how to call the API functions provided by the SQLite library, see the previously written article:

Description of the API interface to the lightweight database SQLitehttps://blog.csdn.net/chenlycly/article/details/123482588

2.2、SQLite Advantages and Disadvantages

SQLite is completely free and open source , is a lightweight data , running time occupies little memory resources , only need to be integrated into the program can be used . Support for multiple operating systems , support for C, C + + + , Java and Python multiple development languages .SQLite supports most SQL statements and database features .

SQLite has the following disadvantages:

1) Locking machine for concurrent access, SQLite has been less than ideal in terms of performance for concurrent (both multi-process and multi-threaded) reads and writes. The database may be exclusively occupied by write operations, resulting in blocking or errors for other read and write operations.
2) Incomplete SQL standards support, on its official website, it specifically lists which SQL92 standards are not supported. The one that I personally find offensive is the lack of support for foreign key constraints.
3) Sometimes when you need to access SQLite library files on other machines, you place the database files on a network share directory. This is when you have to be careful. When SQLite files are placed on NFS, things can go wrong (e.g. data corruption) in concurrent read and write situations. The reason for this is said to be due to a bug in some NFS file locking implementations.

2.3、SQLite application scenarios

SQLite is a lightweight database, mainly used to store some lightweight data, often used in embedded systems and mobile devices due to its small resource consumption.SQLite can not deal with massive data in large systems, its real-time and performance is not enough to meet the demand for efficient storage and querying of massive data.

SQLite can be used in client software to store some data, such as IM chat software can be used to store chat data, you can also use it to store some configuration information. Embedded devices have limited resources, and when you need to use a database, you should give priority to SQLite database.

Some data we can directly write the binary data in memory directly to the file in the form of a binary stream, but this binary data is very inconvenient to add, delete, change and check, to rewrite the updated binary data rewrite overwrite write to the file. Using SQLite to store the data will be very convenient when adding, deleting, and checking.

3. MySQL database

3.1 Introduction to MySQL

The first version of MySQL was released in 1995 by MySQL AB, a Swedish company founded by David Axmark, Allan Larsson, and Michael Widenius.The MySQL project was released as open source under the GNU General Public License (GPL) in 2000. By 2001, MySQL had more than 2 million active installations; by 2004, the software was being downloaded more than 30,000 times a day.

MySQL was acquired by Sun Microsystems in 2008 for $1 billion. When Oracle acquired Sun Microsystems in 2009, it also acquired ownership of MySQL. Oracle has a dual-licensing policy for the MySQL software, which is divided into a free community edition and a commercial edition. mySQL uses the SQL language, which is the most commonly used standardized language for accessing databases, and because it is free and open-source, it is currently the most widely used open-source relational database system and the first choice of many IT vendors. The SQL language used by MySQL is the most common standardized language used to access databases, and because it is free and open source, it is currently the most widely used open-source relational database system, and is the first choice of many IT vendors.

3.2. MySQL Advantages and Disadvantages

MySQL has the following main advantages:

1) It uses core threads that are fully multi-threaded, fast and support multi-processors.
(2) there are a variety of column types: 1, 2, 3, 4, and 8-byte length signed/unsigned integers, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, and ENUM type.
3) It implements SQL libraries through a highly optimized class library and is as fast as they can be, and usually shouldn’t have any memory allocation after query initialization. There are no memory holes.
4) Support ANSI SQL LEFT 0UTER JOIN and ODBC.
5) MySQL can work on different platforms. Support for C, C + +, Java, Perl, PHP, Python and TCL API.
6) Full support for SQL’s GROUP BY and ORDER BY clauses , support for aggregation functions (COUNT (), COUNT (DISTINCT), AVG (), STD (), SUM (), MAX () and MIN ()). You can mix tables from different databases in the same query.
7) All columns have default values. You can insert a subset of table columns with INSERT, and those columns that do not have explicitly given values are set to their default values.

Of course MySQL has some flaws:

1) MySQL’s biggest drawback is its security system, which is mostly complex rather than standard, plus it only changes until mysqladmin is called to reread user permissions.
2) One of the other major shortcomings of MySQL is the lack of a standard RI (Referential Integrity-RI) mechanism; the lack of Rl constraints (a kind of fixed range limit on a given field domain) can be compensated for by a large number of data types.
3) MySQL did not support stored procedures until version 5.0, and support for stored procedures is not very good.
4) MySQL does not support hot backups. The problem of MySQL hot backups is mainly related to the characteristics of the InnoDB storage engine. the InnoDB storage engine is the default storage engine of MySQL, which has the ACID feature and supports the row locking mechanism, which can provide good performance in highly concurrent scenarios. However, in the case of implementing hot standby, it exposes some flaws of the InnoDB storage engine.

3.3. MySQL Application Scenarios

MySQL provides reliable protection for data access (and usage) in a simple way. Most websites and web applications can simply work on MySQL by ignoring constraints. Alternatively, if you are working on a solution that requires a high degree of customization, you can use MySQL’s rich configuration settings and operating modes to be able to easily enforce your rules. If you are developing an application that needs to be accessed by multiple users, and all these users use the same database, you need to choose the client-server mode database MySQL.

4. PostgreSQL database

4.1 Introduction to PostgreSQL

PostgreSQL is a powerful open source database system that was born at the University of California, Berkeley, U.S.A. PostgreSQL was first released as open source software in 1996. After more than 15 years of active development and continuous improvement, PostgreSQL has gained an excellent reputation in the industry for reliability, stability, and data consistency.

PostgreSQL currently runs on all major operating systems, including Linux, Unix (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, and Tru64), and Windows.PostgreSQL is a fully transactional secure database, with complete support for foreign keys, unions, views, triggers, and stored procedures (and supports the development of stored procedures in multiple languages). It supports most of the SQL:2008 standard data types, including integer, numeric, boolean, byte, character, date, interval, and time, and it also supports storing binary large pairs of images, sound, and video.PostgreSQL has native programming interfaces to many high-level development languages, such as C/C++, Java, , Perl, Python, Ruby, Tcl, and ODBC, as well as other languages, and also includes a variety of documentation.

As an enterprise-class database, PostgreSQL boasts a wide range of advanced features like multi-version concurrency control (MVCC), point-in-time recovery (PITR), tablespaces, asynchronous replication, nested transactions, online hot standby, planning and optimization of complex queries, and pre-written logging for fault tolerance. It supports international character sets, multi-byte encoding and supports operations such as sorting, case handling and formatting using local languages. It is also fully scalable in terms of the amount of data it can manage and the amount of concurrent access time it allows for large numbers of users. There are already many PostgreSQL systems managing more than 4TB of data in real production environments. As a result of its performance, PostgreSQL has won numerous awards from end-users and the industry, including the Linux New Media award for Best Database and five Linux Journal Editor’s Choice awards for Best Database.

Most importantly, PostgreSQL’s source code is freely available, and it is licensed under a very liberal open source license, which allows users to use, modify, and distribute PostgreSQL’s source code in a variety of open source and closed source projects. Users can make any changes and improvements to the source code as they wish.As a result, PostgreSQL is not only a powerful enterprise-class database system, but also a database development platform that allows users to develop private, web and commercial software products.

4.2. PostgreSQL Advantages

PostgreSQL offers a rich set of core features that make it a widely used database solution. Below are a few important core features:

Advanced Query Functions: PostgreSQL supports complex SQL queries, including joins, subqueries, aggregate functions, window functions, and so on. It also supports extended functions such as full-text search, geospatial data processing and graphical data analysis.
Integrity Constraints: PostgreSQL allows defining various integrity constraints such as primary key, unique constraints, foreign key and check constraints to ensure data integrity and consistency.
Triggers and Stored Procedures: PostgreSQL supports triggers and stored procedures that allow customized business logic to be executed when inserting, updating or deleting data.
Concurrency Control: With Multi-Version Concurrency Control (MVCC) technology, PostgreSQL enables highly concurrent read and write operations, avoiding data locking and read/write conflicts.
Replication and High Availability: PostgreSQL supports data replication and streaming replication to create hot backups and enable high availability architectures.
Extensibility: By providing an extensibility mechanism, users can customize and use various plug-ins and extensions to enhance the functionality of PostgreSQL.

4.3. PostgreSQL Application Scenarios

If you need your database to perform some customized operations, the flexible and scalable PostgreSQL is the better choice. If you may want to migrate your entire database system to another database system (e.g. Oracle), PostgreSQL will be the most compatible and easy to work with for such a switch.PostgreSQL is completely open source and is not controlled by any commercial company, which makes it more trustworthy than MySQL, which is controlled by Oracle. For example, in a localized system, the more autonomous and controllable PostgreSQL will be preferred for security reasons.

5. Selection in the actual system

In terms of the actual systems we come into contact with, the need to handle large amounts of data information, the need for a high degree of security, as well as support for multi-user concurrency, you can choose MySQL or PostgreSQL. in embedded devices and mobile devices, the industry is using a lightweight database SQLite, the actual operation of the need to frequently read / write directly to the disk file. In localized systems, PostgreSQL, which is completely open source and free from commercial control, will be chosen in order to achieve complete autonomy.