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. SQLite supports the following data types: NULL, INTEGER, REAL, TEXT, and BLOB.
Note: For more information about SQLite data types, see the official documentation.
The advantage of SQLite is that the entire file database is completely composed of a file on the disk, which makes it highly 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.
It is very suitable for development and 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.
The disadvantage of SQLite is that it does not support user systems when users manage advanced databases. For example, you can manage 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.
The lack of space for getting extra performance through optimization is still due to design reasons, and 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 embedded applications be used for all applications requiring portability and no extension, such as single-user local applications, mobile applications, or games.
Replacing disk access in many cases, applications that require direct read/write of disk files can switch to SQLite, benefiting from the additional features provided by SQLite and the simplicity of using structured query language (SQL.
Testing does not require additional processes to test the business logic for most applications (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.
Application write operations requiring high write volumes 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
MySQL is easy to install. Third-party tools, including visual tools, make it easy for users to get started.
Feature-rich MySQL supports most of the functions that RDS should have-either directly or indirectly.
Security supports many security features, which are 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.
Quickly discard certain standards so that MySQL can work very efficiently and simply, so the speed is faster.
The disadvantages of MySQL are known to restrict MySQL from having 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 certain functions (such as references, transactions, and audits) makes it less reliable than other relational databases.
Although MySQL is still an open-source product, there have been many complaints about its development progress since it was acquired. 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.
The high-security MySQL Security Mechanism provides reliable protection for data access and use in a simple way.
Despite some restrictions on websites and Web applications, 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.
Custom 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 I use MySQLSQL compliance? Because MySQL does not intend to implement the complete SQL standard, it does not fully comply with 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.
Lack of features MySQL lacks some features, such as full text search.
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 SQL-compliant PostgreSQL is an open-source, free, and powerful relational database.
A strong community is supported by A loyal and experienced community. Users can enjoy free services around the clock through the knowledge base and Q & A website.
Powerful third-party support in addition to very advanced features, PostgreSQL also has many excellent, open-source third-party tools that can assist in system design, management, and use.
Scalable PostgreSQL functions can be expanded through stored procedures.
Object-oriented PostgreSQL is not only a relational database, but also an object-oriented database-supporting nesting and other functions.
Performance disadvantages of PostgreSQL: for simple and heavy read operations, using PostgreSQL may be a little tricky, and the performance is worse than similar products like MySQL.
Although there are a large number of deployments in popularity, the popularity of the database is not high.
As mentioned above, it is difficult to find a host or service provider that provides a hosted PostgreSQL instance.
When to use PostgreSQL data integrity when reliability and data integrity are absolutely required, PostgreSQL is a better choice.
If a complicated custom program requires 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. Compared with other open-source and free databases, PostgreSQL provides the most comprehensive functionality and has the greatest 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.
Replication is easier for people who lack experience in database and system management to use MySQL for replication, unless you are willing to spend time, effort, and resources.
Reference: http://www.infoq.com/cn/news/2014/04/sqlite-mysql-postgresql
