SQLitevsMySQLvsPostgreSQL: database comparison

Since the introduction of the relational database model in 1970, the relational database began to emerge. After more than 40 years of evolution, today's relational databases are widely used in a wide range of features. Facing so many relational databases

Since the introduction of the relational database model in 1970, the relational database began to emerge. After more than 40 years of evolution, today's relational databases are widely used in a wide range of features. Facing so many relational databases

Since the introduction of the relational database model by edugatd in 1970, relational databases began to emerge. After more than 40 years of evolution, today's relational databases have a wide variety of features and are widely used. In the face of so many relational databases, how should we weigh and find a database system suitable for our own application scenarios? O.S. tezer recently published a blog post on DigitalOcean, comparing SQLite, MySQL, and PostgreSQL among the three popular relational database management systems (RDBMS ), hope to help you.

O.S. Tezer compares SQLite, MySQL, and PostgreSQL with the data types, strengths, weaknesses, when to use, and when to use the database.

SQLite

SQLite is a lightweight database that complies with ACID and can be embedded into applications that use it. As a self-contained, file-based database, SQLite provides an excellent tool set to process all types of data, compared with a process-based relational database hosted on a server, it has fewer constraints and is easier to use.

When an application uses SQLite, SQLite does not communicate with the application as an independent process through a communication protocol (such as socket), but as part of the application, applications can directly access data files by calling the SQLite interface. Thanks to the underlying technology of the class library, it makes SQLite very fast, efficient, and powerful.

Data Types supported by SQLite

SQLite supports the following data types: NULL, INTEGER, REAL, TEXT, and BLOB.

Note: For more information about SQLite data types, see the official documentation.

Advantages of SQLite

File-based

The entire database is completely composed of a file on the disk, which makes it very portable.

Standardization

Although it looks like a simplified database implementation, SQLite does support SQL. It skips some features (right outer join and for each statement), but also adds some additional features.

Suitable for development and even testing

In the development phase of most applications, most developers may need a solution that supports concurrent scaling. SQLite contains a wide range of functions. It provides more features than development needs and is very simple to use-only one file and a C-Link Library are required.

Disadvantages of SQLite

No user management

Advanced databases support user systems, such as managing connections to access databases and tables. Given the purpose and nature of SQLite (there is no high-level design for multi-client concurrency), it does not include these features.

Lack of space for extra performance through Optimization

It is still because of design reasons that a large amount of extra performance cannot be achieved through optimizing SQLite. This class library is very easy to adjust and use. It is not complicated, so it cannot be made faster technically because it is already very fast.

When should SQLite be used?

Embedded applications

All applications that require portability and no extension, such as single-user local applications, mobile applications, or games.

Replace disk access

In many cases, applications that need to directly read and write disk files can switch to SQLite, benefiting from the additional features provided by SQLite and the simplicity of using structured query language (SQL.

Test

For most applications, there is no need to use additional processes to test the business logic (for example, the main objective of an Application: Functionality ).

When SQLite should not be used

Multi-user applications

If multiple clients need to access and use the same database, it is best to use a fully functional relational database (such as MySQL) instead of SQLite.

Applications requiring high write volumes

Write operations are a limitation of SQLite. The DBMS only allows one write operation at a time, thus limiting its throughput.

MySQL

MySQL is the most popular large-scale database server. It is an open-source product with rich functions. Many websites and online applications use this database. The entry to MySQL is relatively simple. developers can obtain a large amount of information related to the database from the Internet.

Note: Given the popularity of this product, using this database can benefit us from a large number of third-party applications, tools, and integrated class libraries.

Although MySQL does not try to implement the complete SQL standard, it still provides a lot of functions for users. As an independent database server, applications need to communicate with the MySQL daemon to access the database-different from SQLite.

Data Types supported by MySQL

MySQL supports TINYINT, SMALLINT, MEDIUMINT, INT or INTEGER, BIGINT, FLOAT, DOUBLE, double precision, REAL, DECIMAL, NUMERIC, DATE, DATETIME, TIMESTAMP, TIME, YEAR, CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM, SET.

Advantages of MySQL

Easy to use

It is easy to install. Third-party tools, including visual tools, make it easy for users to get started.

Rich Functions

MySQL supports most of the functions that RDS should have-either directly or indirectly.

Security

It supports many security features, some of which are very advanced and built-in to MySQL.

Scalable and powerful

MySQL can process a large amount of data and can be used in a large scale as needed.

Fast

Dropping some standards allows MySQL to work very efficiently and simply, and thus the speed is faster.

Disadvantages of MySQL

Known limits

MySQL has no intention to do everything from the very beginning, so it has some functional limitations and cannot meet the requirements of some advanced applications.

Reliability Problems

MySQL's implementation of some features (such as reference, transaction, and audit) makes it less reliable than other relational databases.

Development stagnation

Although MySQL is still an open-source product, since its acquisition, many people complain about its development progress. Note that some MySQL-based and fully integrated databases add other values to standard MySQL databases, such as MariaDB.

When should I use MySQL?

Distributed operations

If SQLite cannot meet your needs, introduce MySQL into the development stack, just like any other independent database server, it brings you a lot of operational freedom and some advanced functions.

High Security

The security mechanism of MySQL provides reliable protection for data access and use in a simple way.

Websites and Web applications

Despite some constraints, most websites and Web applications can simply run on MySQL. Related flexible and scalable tools are very easy to use and manage-they have proven to be useful for long-term operation.

Customized solutions

MySQL has rich configuration items and running modes. If you need a highly customized solution, MySQL can easily follow and execute your rules.

When should MySQL be used?

SQL compliance

Because MySQL does not intend to implement the complete SQL standard, it does not fully comply with the SQL. If you may need to integrate with such a relational database, it may not be easy to switch from MySQL.

Concurrency

Although MySQL and some other storage engines can perform read operations very well, concurrent read/write may be faulty.

Function missing

MySQL lacks some functions, such as full text search.

PostgreSQL

PostgreSQL is an advanced and open-source object relational database management system. Its main goal is to comply with standards and scalability. PostgreSQL, or Postgres, tries to combine the ANSI/iso SQL standard and its correction.

Compared with other relational databases, PostgreSQL supports highly-needed and complete object-oriented and relational database functions, such as fully supporting reliability transactions.

Thanks to its powerful underlying technology, PostgreSQL can handle many tasks very efficiently. Thanks to multi-version concurrency control (MVCC), it can implement concurrency without a read lock and ensure ACID.

PostgreSQL is highly programmable and therefore highly scalable. It supports custom programs called stored procedures. You can create such functions to simplify repeated, complex, and frequently needed database operations.

Although the database is very powerful, it is not as popular as MySQL. Even so, there are still many excellent third-party tools and libraries that make it easier for us to use.

Data Types supported by PostgreSQL