SQLitevsMySQLvsPostgreSQL: relational database comparison

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 worked in DigitalOcean

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 worked in DigitalOcean

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 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

MySQL Security Mechanism

A simple method provides reliable protection for data access and usage.

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

Data Types supported by PostgreSQL include: bigint, bigserial, bit [(n)], bit varying [(n)], boolean, box, bytea, character varying [(n)], character [(n)], cidr, circle, date, double precision, inet, integer, interval [fields] [(p)], line, lseg, macaddr, money, numeric [(p, s)], path, point, polygon, real, smallint, serial, text, time, timestamp, tsquery, tsvector, txid_snapshot, uuid, xml

Advantages of PostgreSQL

Open-source and compliant with SQL standards

PostgreSQL is an open-source, free, and powerful relational database.

Powerful community

Backed by A loyal and experienced community, users can enjoy free services around the clock through the knowledge base and Q & A website.

Strong third-party support

In addition to advanced features, PostgreSQL also has many excellent open-source third-party tools to assist in system design, management, and use.

Scalable

You can use stored procedures to extend the functionality of PostgreSQL.

Object-oriented

PostgreSQL is not only a relational database, but also an object-oriented database-supporting nesting and other functions.

Disadvantages of PostgreSQL

Performance:

For simple and heavy read operations, PostgreSQL may be a little tricky, and its performance is worse than similar products like MySQL.

Popularity

Despite a large number of deployments, the database's popularity is not high.

Managed

As mentioned above, it is difficult to find a host or service provider that provides a hosted PostgreSQL instance.

When should PostgreSQL be used

Data Integrity

PostgreSQL is a better choice when reliability and data integrity are absolutely required.

Complex custom programs

If you need a database to execute a custom program, the Scalable PostgreSQL is a better choice.

Integration:

If you may need to migrate the entire database to another suitable solution (such as Oracle) in the future, PostgreSQL may have better compatibility and easier switchover.

Complex Design

Compared with other open-source and free databases, PostgreSQL has the most comprehensive functionality and potential for complex database design. You do not need to discard other valuable assets.

When should PostgreSQL be used

Speed

If you only need quick read operations, PostgreSQL is not suitable.

Simple

Unless you need absolute data integrity, ACID compliance, or complex design, PostgreSQL is a little redundant for simple scenarios.

Copy

It is easier for people who lack experience in database and system management to use MySQL for replication, unless you are willing to spend time, energy, and resources.

Reference: http://www.infoq.com/cn/news/2014/04/sqlite-mysql-postgresql