Shulou(Shulou.com)05/31 Report--

Most people do not understand the knowledge points of this article "what are the differences between postgresql and mysql", so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "what are the differences between postgresql and mysql" article.

Differences: 1, MySQL starts the instance by executing commands, while PG starts by executing processes; 2, PG supports materialized views, while MySQL does not support materialized views; 3, MySQL does not support extensibility, while PG is highly scalable; 4. PG stored procedures have better functional support than MySQL and have the ability to locally cache execution plans.

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

MySQL

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

Initially, MySQL was designed to be a fast Web server backend, using a fast index sequence access method (ISAM) and not supporting ACID. After early and rapid development, MySQL began to support more storage engines and implemented ACID through the InnoDB engine. MySQL also supports other storage engines, providing temporary table functionality (using the MEMORY storage engine), high-speed database reading through the MyISAM engine, as well as other core storage engines and third-party engines.

MySQL has a wealth of documentation, with many good quality free reference manuals, books and online documentation, as well as training and support from Oracle and third-party vendors.

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

Now, there are more databases to choose from based on the original MySQL code, as several core MySQL developers have released MySQL branches. Michael "Monty" Widenius, one of the original founders of MySQL, seems to regret selling MySQL to Sun, so he developed his own MySQL branch, MariaDB, which is free and based on the GPL license. Drizzle, a branch created by the well-known MySQL developer Brian Aker, has done a lot of rewriting, especially for multi-CPU, cloud, web applications and high concurrency.

PostgreSQL

PostgreSQL (PG) boasts itself as the most advanced open source database in the world. Some fans of PostgreSQL say it is comparable to Oracle and is not so expensive and arrogant customer service. It has a long history and was first 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 1000 contributors worldwide. It provides a single, fully functional version, unlike MySQL, which offers multiple different community, business, and enterprise editions. PostgreSQL is based on a free BSD/MIT license that allows organizations to use, copy, modify, and redistribute code by providing a copyright notice.

Reliability is the highest priority of PostgreSQL. It is known for its rock-solid quality and good engineering, supporting high-transaction, mission-critical applications. PostgreSQL is not always well documented, providing a large number of free online manuals and archived reference manuals for older versions. PostgreSQL's community support is great, as well as business support from independent vendors.

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

Among the many management features, point-in-time recovery (PITR) is a great feature, a flexible and highly available feature that provides capabilities such as creating hot backups and snapshots and restores against failed restores. But that's not what PostgreSQL is all about. The project also provides several ways to manage PostgreSQL for high availability, load balancing, replication, and so on, so you can use features that suit your specific needs.

The difference between postgresql and mysql

The property MySQLPostgreSQL instance starts 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 that form a cluster. A cluster is an area on disk that is initialized at installation time and consists of a directory in which all data is stored. Create the first database using initdb. Multiple instances can be started on a single machine. A database database is a named collection of objects, entities that are separate from other databases in the instance. All databases in an 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. The data buffer sets 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 to up to 80% of the machine's physical memory. Shared_buffers cache. 64 buffers are allocated by default. The default block size is 8K. You can update the buffer cache by setting the shared_buffers parameter in the postgresql.conf file. When the database connection client connects to the database using CONNECT or USE statements, you can specify the database name, as well as the user id and password. Use roles to manage users and user groups in the database. When the client connects to the database using the connect statement, you can specify the database name, as well as the user id and password. 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 wealth of authentication methods: trust authentication, password authentication, Kerberos authentication, Ident-based authentication, LDAP authentication, PAM authentication encryption 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 through a SSL connection. Columns can be encrypted / decrypted using functions in the pgcrypto library. Network encryption can be achieved through a SSL connection. The audit can grep the querylog. You can use PL/pgSQL triggers on the table for auditing. Backup, recovery, and logging InnoDB uses pre-write (write-ahead) logging. Full online and offline backups as well as crash and transaction recovery are supported. Third-party software is required to support hot backup. Maintain pre-write logs in a subdirectory of the data directory. Support for online and offline full backups as well as crash, point-in-time, and transaction recovery. Hot backup can be supported. Constraints support primary key, foreign key, unique and non-null constraints. Check constraints are resolved, but not enforced. Primary key, foreign key, unique, non-null, and check constraints are supported. Stored procedures and user-defined functions support 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 are no separate stored procedures, they are all implemented through functions. User-defined functions can be written in PL/pgSQL (proprietary process language), PL/Tcl, PL/Perl, PL/Python, SQL, and C. Triggers support pre-row triggers, post-row triggers and statement triggers, and trigger statements are written in procedural language compound statements. Supports pre-row trigger, post-row trigger and statement trigger, and the trigger process is written in C. System profile my.confPostgresql.conf database configuration my.confPostgresql.conf client connection file my.confpg_hba.confXML supports limited XML support. Limited XML support. Data access and management server OPTIMIZE TABLE-reclaims unused space and removes fragmentation of data files

Myisamchk-analyze-updates the statistics used by the query optimizer (MyISAM storage engine)

Mysql-Command Line tool

MySQL Administrator-- client GUI tool Vacuum-- reclaims unused space

Analyze-updates the statistics used by the query optimizer

Psql-Command Line tool

PgAdmin-client GUI tool concurrency control supports table-level and row-level locks. The InnoDB storage engine supports READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, and SERIALIZABLE. Use the SET TRANSACTION ISOLATION LEVEL statement to set the isolation level at the transaction level. Table-level and row-level locks are supported. The supported ANSI isolation levels are Read Committed (the default-- you can see a snapshot of the database when the query starts) and Serialization (similar to Repeatable Read-- you can only see the results committed before the transaction starts). Use the SET TRANSACTION statement to set the isolation level at the transaction level. Use SET SESSION to set up at the session level.

VSPostgreSQLMySQL open source PostgreSQL is a free open source system that is subject to the PostgreSQL license (free open source license). MySQL belongs to Oracle products and provides several paid versions for users to use. Management PostgreSQL is a product developed by users all over the world. MySQL is the GNU General Public license and the product performance under the terms of various proprietary agreements PostgreSQL is suitable for large-scale systems that require high reading and writing speed. MySQL is mainly used in Web applications, which only need a database for data transactions. Follow ACIDPostgreSQL follow ACID principles from beginning to end and ensure that requirements are met MySQL meets ACID requirements only when using InnoDB and NDB cluster storage engines. SQL compatibility "from the documentation, PostgreSQL is compatible with most SQL. PostgreSQL supports most of the features of SQL:2011. Of the 179 mandatory features required for core consistency, PostgreSQL is compatible with at least 160. In addition, there is a range of supported optional features. "" From the documentation, MySQL is compatible with partial SQL in some versions. One of our main goals for 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, which can greatly improve the availability of MySQL servers in most of our user groups. " The supporting platform PostgreSQL can run on Linux, Windows (Win2000 SP4 and above), FreeBSD,OpenBSD,NetBSD, Mac OS X, AIX, IRIX, Solaris and Tu64. It also supports HP-UX OS developed by technology giant Hewlett-Packard and open source Unix OS. MySQL can be run on Oracle Solaris,Microsoft Windows, Linux Mac OS X. MySQL extends its support for open source FreeBSD OS programming languages PostgreSQL is written in C, it supports multiple programming languages, the most prominent of which are Cash Cleavage codes, Delphi, JavaScript, Java, Python, R, Tcl, Go, Lisp, Erlang and .net. PostgreSQL is written in C and C++, it supports Cash Cure codes, Erlang,PHP,Lisp, and Go, Perl,Java, Delphi, R, and Node.js. Materialized view PostgreSQL supports materialized view MySQL does not support materialized view data backup PostgreSQL supports active and standby replication, and can also implement third-party extension to handle other types of replication MySQL supports active and standby replication, in which each node is the master node, and has the right to update data extensibility PostgreSQL is highly extensible, you can add and own data types, operators, index types and functional languages. MySQL does not support extensibility. The access method PostgreSQL supports all standards. MySQL supports all standards. Community support PostgreSQL has an active community support that helps improve existing functionality, and its creative submitters do their best to ensure that the database maintains the latest features and maximum security and becomes the most advanced database. MySQL also has a large community of followers, and these community contributors, especially after being acquired by Oracle, focus on occasional new features and maintain existing ones. Security PostgreSQL provides native SSL support for connections to encrypt client / server communication. PSQL also has row-level security. MySQL is highly secure and contains multiple security features.

Advantages of PostgreSQL over MySQL

The standard implementation of SQL is more perfect than that of MySQL, and the functional implementation is more rigorous.

The function support of stored procedure is better than that of MySQL, and it has the ability of local cache to execute plan.

It has complete support for table join, complete function of optimizer, many index types, and strong complex query ability.

The main table of PG is stored by heap table, and MySQL uses index to organize the table, which can support a larger amount of data than MySQL.

The active and standby replication of PG belongs to physical replication. Compared with MySQL binlog-based logical replication, the consistency of data is more reliable, the replication performance is higher, and the impact on host performance is less.

The storage engine plug-in mechanism of MySQL has the problem that the lock mechanism is complex and affects concurrency, but PG does not exist.

Advantages of MySQL over PG:

Innodb's MVCC mechanism based on rollback segment is superior to the XID-based MVCC mechanism in which PG new and old data are stored together. When the new and old data are stored together, the VACUUM needs to be triggered regularly, which will bring redundant IO and database object locking overhead, resulting in a decline in the overall concurrency ability of the database. And VACUUM cleaning is not timely, it may also lead to data inflation.

MySQL uses indexes to organize tables, which is very suitable for query and deletion operations based on primary key matching, but there are constraints on table structure design.

The optimizer of MySQL is simple, and the implementation of system tables, operators and data types is very concise, which is very suitable for simple query operations.

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

The plug-in mechanism of MySQL storage engine makes its application scenarios more extensive. For example, except that innodb is suitable for transaction processing scenarios, myisam is suitable for static data query scenarios.

The above is about the content of this article on "what are the differences between postgresql and mysql". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.