search
HomeDatabaseMysql TutorialWhat 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.

What is the difference between postgresql and mysql

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!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
mysql怎么替换换行符mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

MySQL复制技术之异步复制和半同步复制MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

MySQL和PostgreSQL:在Web开发中的最佳实践MySQL和PostgreSQL:在Web开发中的最佳实践Jul 14, 2023 pm 02:34 PM

MySQL和PostgreSQL:在Web开发中的最佳实践引言:在现代的Web开发领域中,数据库是必不可少的组成部分。在选择数据库时,常见的选择是MySQL和PostgreSQL。本文将介绍在Web开发中使用MySQL和PostgreSQL的最佳实践,并提供一些代码示例。一、适用场景MySQL适用于大多数Web应用程序,特别是那些需要高性能、可扩展性和易于使

MySQL和PostgreSQL:性能对比与优化技巧MySQL和PostgreSQL:性能对比与优化技巧Jul 13, 2023 pm 03:33 PM

MySQL和PostgreSQL:性能对比与优化技巧在开发web应用程序时,数据库是不可或缺的组成部分。而在选择数据库管理系统时,MySQL和PostgreSQL是两个常见的选择。他们都是开源的关系型数据库管理系统(RDBMS),但在性能和优化方面有一些不同之处。本文将比较MySQL和PostgreSQL的性能,并提供一些优化技巧。性能对比在比较两个数据库管

带你把MySQL索引吃透了带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。

如何在PHP编程中使用PostgreSQL数据库?如何在PHP编程中使用PostgreSQL数据库?Jun 12, 2023 am 09:27 AM

随着数据库技术的发展,数据库管理系统也呈现出多种多样的选择,开发人员可以根据自己的需求和喜好选择最适合自己的数据库。而PostgreSQL作为一种先进的开源关系型数据库系统,越来越受到开发人员的关注和使用。那么,在PHP编程中如何使用PostgreSQL数据库呢?一、安装和配置PostgreSQL数据库在使用PostgreSQL之前,需要先安装和配置它。首先

MySQL和PostgreSQL:数据安全与备份策略MySQL和PostgreSQL:数据安全与备份策略Jul 13, 2023 pm 03:31 PM

MySQL和PostgreSQL:数据安全与备份策略引言:在现代社会中,数据成为了企业和个人生活中不可或缺的一部分。对于数据库管理系统来说,数据安全与备份策略是至关重要的,既能保护数据免受丢失或损坏,也能确保恢复数据的可靠性和完整性。本文将重点讨论MySQL和PostgreSQL两种主流关系型数据库系统的数据安全性和备份策略。一、数据安全性方面:(一)用户权

在Go语言中使用PostgreSQL:完整指南在Go语言中使用PostgreSQL:完整指南Jun 18, 2023 am 09:28 AM

Go语言是一种快速、高效的编程语言,适合构建Web服务和后端应用程序。而PostgreSQL是一个开源的关系型数据库管理系统,承诺提供更高的可靠性、可扩展性和数据安全性。在本文中,我们将深入探讨如何在Go语言中使用PostgreSQL,并提供一些实用的代码示例和技巧。安装和设置PostgreSQL首先,我们需要安装和设置PostgreSQL。可以在官方网

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!