What is the difference between postgresql and mysql

Difference: 1. MySQL starts the instance by executing the command, while PG starts by executing the process; 2. PG supports materialized views, but MySQL does not support materialized views; 3. MySQL does not support scalability, while PG does Highly scalable; 4. The function support of PG stored procedures is better than that of MySQL, and it has the ability to cache execution plans locally.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL

MySQL is relatively young, first appearing in 1994. It claims to be the most popular open source database. MySQL is the M in LAMP (a software package for web development, including Linux, Apache, and Perl/PHP/Python). Most applications built on the LAMP stack use MySQL, including well-known applications such as WordPress, Drupal, Zend, and phpBB.

From the beginning, MySQL was designed to be a fast web server backend, using the fast Index Sequential Access Method (ISAM) and not supporting ACID. After rapid early development, MySQL began to support more storage engines and implemented ACID through the InnoDB engine. MySQL also supports other storage engines, provides temporary table functions (using the MEMORY storage engine), and implements high-speed read databases through the MyISAM engine. In addition, there are other core storage engines and third-party engines.

MySQL has very rich documentation, including many high-quality free reference manuals, books and online documents, as well as training and support from Oracle and third-party vendors.

MySQL has experienced changes in ownership and some dramatic events in recent years. It was originally developed by MySQL AB and then sold to Sun for $1 billion in 2008. Sun was acquired by Oracle in 2010. Oracle supports multiple versions of MySQL: Standard, Enterprise, Classic, Cluster, Embedded and Community. Some of them are free to download, while others are paid. Its core code is based on the GPL license, and commercial licenses are available for developers and manufacturers who do not want to use the GPL license.

Now, there are many more databases to choose from based on the original MySQL code, because several core MySQL developers have released MySQL forks. One of the original MySQL creators, Michael "Monty" Widenius, seemed to regret selling MySQL to Sun, so he developed his own MySQL fork, MariaDB, which is free and licensed under the GPL. Drizzle, a branch created by the well-known MySQL developer Brian Aker, has been extensively rewritten, especially optimized for multi-CPU, cloud, network applications and high concurrency.

PostgreSQL

PostgreSQL (PG for short) bills itself as the world’s most advanced open source database. Some fans of PostgreSQL say it's comparable to Oracle, but without the hefty price tag and arrogant customer service. It has a long history, originally developed at the University of California, Berkeley, in 1985 as a successor to the Ingres database.

PostgreSQL is a completely community-driven open source project maintained by more than 1,000 contributors around the world. It provides a single fully functional version, unlike MySQL which provides multiple different community editions, commercial editions and enterprise editions. PostgreSQL is licensed under the liberal BSD/MIT license, and organizations can use, copy, modify, and redistribute the code as long as they provide a copyright notice.

Reliability is PostgreSQL's highest priority. It is known for its rock-solid quality and well-engineered support for high-transaction, mission-critical applications. PostgreSQL's documentation is very good, with a large number of free online manuals and archived reference manuals for older versions. PostgreSQL's community support is excellent, as is commercial support from independent vendors.

Data consistency and integrity are also high-priority features of PostgreSQL. PostgreSQL fully supports ACID features, provides strong security guarantees for database access, and makes full use of enterprise security tools, such as Kerberos and OpenSSL. You can define your own checks to ensure data quality based on your own business rules.

Among the many management features, point-in-time recovery (PITR) is a great feature. It is a flexible and high-availability feature that provides functions such as creating hot backups and snapshots and recovery for failed recovery. ability. But this is not all of PostgreSQL. The project also provides several methods to manage PostgreSQL to achieve high availability, load balancing, replication, etc., so that you can use the functions that suit your specific needs.

The difference between postgresql and mysql

Concurrency controlSupports table-level and row-level locks. The InnoDB storage engine supports READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, and SERIALIZABLE. Set the isolation level at the transaction level using the SET TRANSACTION ISOLATION LEVEL statement. Supports table-level and row-level locks. The supported ANSI isolation levels are Read Committed (default - you can see a snapshot of the database when the query is started) and Serialization (similar to Repeatable Read - you can only see the results committed before the transaction was started). Use the SET TRANSACTION statement to set the isolation level at the transaction level. Set at the session level using SET SESSION.

Features	MySQL	PostgreSQL
Instance	Start the instance by executing the MySQL command (mysqld). An instance can manage one or more databases. A server can run multiple mysqld instances. An instance manager can monitor individual instances of mysqld.	Start the instance by executing the Postmaster process (pg_ctl). An instance can manage one or more databases, which form a cluster. A cluster is an area on disk that is initialized during installation and consists of a directory where all data is stored. Use initdb to create the first database. Multiple instances can be started on one machine.
Database	A database is a named collection of objects that is a separate entity from other databases in the instance. All databases in a MySQL instance share the same system catalog.	A database is a named collection of objects, and each database is an entity separate from other databases. Each database has its own system catalog, but all databases share pg_databases.
Data buffer	Set the data buffer through the innodb_buffer_pool_size configuration parameter. This parameter is the number of bytes in the memory buffer that InnoDB uses to cache table data and indexes. On a dedicated database server, this parameter can be set up to 80% of the machine's physical memory.	Shared_buffers Cache. By default 64 buffers are allocated. The default block size is 8K. The buffer cache can be updated by setting the shared_buffers parameter in the postgresql.conf file.
Database connection	The client uses the CONNECT or USE statement to connect to the database. At this time, the database name must be specified, and User id and password can be specified. Use roles to manage users and user groups in the database.	The client uses the connect statement to connect to the database. At this time, the database name must be specified, and the user ID and password can also be specified. Use roles to manage users and user groups in the database.
Authentication	MySQL manages authentication at the database level. Basically only password authentication is supported.	PostgreSQL supports a variety of authentication methods: trust authentication, password authentication, Kerberos authentication, Ident-based authentication, LDAP authentication, PAM authentication
Encryption	#You can specify a password at the table level to encrypt the data. You can also use the AES_ENCRYPT and AES_DECRYPT functions to encrypt and decrypt column data. Network encryption can be achieved via SSL connections.	Columns can be encrypted/decrypted using functions in the pgcrypto library. Network encryption can be achieved via SSL connections.
Audit	You can grep the querylog.	You can use PL/pgSQL triggers on tables for auditing.
Backup, recovery and logging	InnoDB uses write-ahead logging. Supports online and offline full backup as well as crash and transaction recovery. Third-party software is required to support hot backup.	Maintain the write-ahead log in a subdirectory of the data directory. Supports online and offline full backups as well as crash, point-in-time and transaction recovery. Can support hot backup.
Constraints	Supports primary key, foreign key, unique and non-null constraints. Check constraints are resolved but not enforced.	Supports primary key, foreign key, unique, non-null and check constraints.
Stored procedures and user-defined functions	Supports CREATE PROCEDURE and CREATE FUNCTION statements. Stored procedures can be written in SQL and C. User-defined functions can be written in SQL, C, and C.	There is no separate stored procedure, it is all implemented through functions. User-defined functions can be written in PL/pgSQL (a specialized procedural language), PL/Tcl, PL/Perl, PL/Python, SQL, and C.
Trigger	Supports pre-row triggers, post-row triggers and statement triggers, trigger statements Written in procedural language compound statements.	Supports pre-row triggers, post-row triggers and statement triggers. The trigger process is written in C.
System configuration file	my.conf	Postgresql. conf
Database configuration	my.conf	Postgresql. conf
Client connection file	my.conf	## pg_hba.conf
XML support	Limited XML support.	Limited XML support.
Data Access and Management Server	##OPTIMIZE TABLE —— Reclaim unused space And eliminate fragmentation of data filesmyisamchk -analyze —— Update statistics used by the query optimizer (MyISAM storage engine)mysql —— Command line toolMySQL Administrator —— Client GUI tool	Vacuum ——Reclaim unused spaceAnalyze ——Update statistics used by the query optimizerpsql ——Command line toolpgAdmin ——Client GUI tool

VS	PostgreSQL	MySQL
Open Source	PostgreSQL is a free and open source system, which is subject to the PostgreSQL License (Free Open Source License).	MySQL is a product of Oracle and provides several paid versions for users to use
Management	PostgreSQL is a product developed by users around the world	MySQL is a product under the GNU General Public License and various proprietary agreement terms
Performance	PostgreSQL is suitable for large-scale applications that require high reading and writing speeds	MySQL is used in the system mainly for web applications, which only require a database for data transactions.
Follow ACID	PostgreSQL follows ACID principles from beginning to end and ensures that requirements are met	MySQL is only used when using InnoDB and NDB cluster storage engines Only in compliance with ACID requirements.
SQL Compatibility	"From the documentation, PostgreSQL is compatible with most SQL. PostgreSQL supports most features of SQL:2011. Required for core consistency Of the 179 mandatory functions, PostgreSQL is compatible with at least 160. In addition, there are a series of supported optional functions."	"From the documentation, MySQL is compatible with some SQL in some versions. We are One of the main goals of this product is to continue to strive to meet the requirements of the SQL standard without sacrificing speed or reliability. We can add SQL extensions or support for non-SQL features if this can greatly improve the MySQL server in most of our Availability among the user base."
Supported Platforms	PostgreSQL can run on Linux, Windows (Win2000 SP4 and above), FreeBSD, OpenBSD, NetBSD, Mac OS X , AIX, IRIX, Solaris and Tu64. Also supports HP-UX OS developed by technology giant Hewlett-Packard, as well as open source Unix OS.	MySQL can run on Oracle Solaris, Microsoft Windows, Linux Mac OS X. MySQL extends support for the open source FreeBSD OS
Programming language support	PostgreSQL is written in C, and it supports a variety of programming languages, the most prominent C/ C, Delphi, JavaScript, Java, Python, R, Tcl, Go, Lisp, Erlang and .Net.	PostgreSQL is written in C and C, and it supports C/C, Erlang, PHP, Lisp, And Go, Perl, Java, Delphi, R, and Node.js.
Materialized View	PostgreSQL supports materialized view	MySQL does not support materialized view View
Data backup	PostgreSQL supports primary and secondary replication, and can also handle other types of replication by implementing third-party extensions	MySQL supports primary and secondary replication Standby replication, where each node is the master and has the authority to update data
Scalability	PostgreSQL is highly scalable, you can add and have Data types, operators, index types and functional languages.	MySQL does not support scalability.
Access methods	PostgreSQL supports all standards.	MySQL supports all standards.
Community Support	PostgreSQL has an active community support that helps improve existing features and its creative committers go to great lengths to ensure that the database remains up to date functionality and maximum security, making it the most advanced database available.	MySQL also has a large follower community. These community contributors, especially after being acquired by Oracle, mainly focus on some occasional new features and maintain existing features.
Security	PostgreSQL provides native SSL support for connections to encrypt client/server communications. PSQL also has row-level security.	MySQL is highly secure and includes multiple security features.

Advantages of PostgreSQL over MySQL

• The standard implementation of SQL is more complete than MySQL, and the function implementation is more rigorous;

• The function support of stored procedures is better than that of MySQL, and it has the ability to cache execution plans locally;

• The support for table connections is relatively complete, and the functions of the optimizer are relatively complete. It supports many index types and has strong complex query capabilities;

• PG's main table is stored in a heap table, while MySQL uses indexes to organize tables, which can support a larger amount of data than MySQL.

• PG's primary and secondary replication is physical replication. Compared with MySQL's binlog-based logical replication, data consistency is more reliable, replication performance is higher, and the impact on host performance is smaller. .

• MySQL's storage engine plug-in mechanism has the problem of complex locking mechanisms affecting concurrency, but PG does not exist.

MySQL’s advantages over PG:

• innodb’s MVCC mechanism based on the rollback segment, compared to PG The XID-based MVCC mechanism, which stores old and new data together, is dominant. New and old data are stored together, and VACUUM needs to be triggered regularly, which will bring redundant IO and database object locking overhead, causing the overall concurrency capability of the database to decrease. Moreover, if VACUUM is not cleaned up in time, it may also cause data expansion;

• MySQL uses indexes to organize tables. This storage method is very suitable for queries and delete operations based on primary key matching, but it does not affect the table structure. There are constraints in the design;

• MySQL's optimizer is relatively simple, and the implementation of system tables, operators, and data types is very streamlined, which is very suitable for simple query operations;

• The implementation of MySQL partition table is better than PG's partition implementation based on inheritance tables, which is mainly reflected in the large difference in processing performance when the number of partitions reaches thousands or tens of thousands.

• MySQL's storage engine plug-in mechanism makes its application scenarios more extensive. For example, in addition to innodb being suitable for transaction processing scenarios, myisam is suitable for static data query scenarios.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the difference between postgresql and mysql. For more information, please follow other related articles on the PHP Chinese website!