Shulou(Shulou.com)06/01 Report--

Omni-directional comparison of PostgreSQL and MySQL

I. the original text

Https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison

II. Summary

This paper makes a detailed comparison between MySQL and PostgreSQL, which is convenient to choose.

1. Why use PostgreSQL

2. Why use MySQL

3. Ease of use

4. Grammar

5. Data type

6. Replication and clustering

7. View

8. Trigger

9. Stored procedure

10. Query

11. Zoning

12. Scalability of the table

13. NoSQL capability

14. Security

15. Analytical function

16. GUI tool

17. Performance

18 、 Adoption

19. The best environment

3. PG vs MySQL: which one do you choose?

Both PostgreSQL and MySQL are the most popular open source databases. MySQL is considered to be the most popular database in the world, while PostgreSQL is considered to be the most advanced database in the world. MySQL is not fully compliant with the SQL standard, and many features on PG are not supported. This is why PG is popular with a large number of developers, and PG is becoming more and more popular.

A few years ago, Oracle acquired MySQL, resulting in two versions of MySQL: the commercial version and the community version. For the latter, because Oracle controls the development of MySQL, it has been criticized by the majority of users.

PostgreSQL is the most popular database in the world: it supports a large number of enterprise-class features and functions. PG is developed by the postgresql global community, which is made up of a group of excellent developers who have worked for decades to ensure that PG has rich functionality and competes with other open source, commercial databases. Communities also receive great contributions from companies around the world.

1. Why use PG

As an open source and feature-rich database, PG can compete with Oracle. Developers will also use PG as a NoSQL database. It is very easy to use PG for cloud and local deployment, and it can also be used on various platforms such as docker containers.

PG fully supports ACID and is very friendly to developers and DBA. It is the best choice for highly concurrent transactions and complex applications across any domain, and can meet a variety of WEB-and mobile-based application services. PG is also a very good data warehouse for running complex report queries on big data.

2. Why use MySQL

MySQL is available in both community and business editions. The business version is managed by Oracle. As a relational database, it is easy to deploy and use. However, it is not suitable for applications with high SQL standards. The integration ability of MySQL is also limited, so it is difficult to become part of a heterogeneous database environment.

MySQL is suitable for simple web applications or applications that require simple schema and SQL to perform database operations. MySQL is not a good choice for complex applications that deal with large amounts of data.

3. Ease of use

PG can handle both structured and unstructured data and has all the features of a relational database. MySQL's limitations in terms of SQL and features may pose challenges for it to build efficient RDBMS applications.

4. Grammar

The SQL syntax of most databases is similar. However, MySQL does not support all SQL. For the supported SQL and other databases are similar. For example, queries, PG and MySQL are both:

SELECT * FROM employees

5. Data type

Both MySQL and PG support many data types, from traditional data types (integer, date, timestamp) to complex types (json, xml, text). However, it is different in complex real-time data query.

PG not only supports traditional data types: numeric, strings, date, decimal, etc., but also supports unstructured data types: json, xml, hstore, etc., as well as network data types, bit strings, as well as ARRAYS, geographic data types.

MySQL does not support geographic data types.

Since 9. 2, PG supports the json data type. Compared with MySQL, PG's support for json is more advanced. He has some operators and functions specified by json, and yes, searching json text is very efficient. Starting with, json data can be stored in binary format, and full-text indexing (GIN indexing) on this column is supported for quick search in json documents.

Since 5. 7, MySQL supports the json data type, which is later than PG. You can also index on the json column. However, the support for json-related functions is limited. Full-text indexing on json columns is not supported. Because of MySQL's limitations on SQL support, MySQL is not a good choice for storing and processing json data.

6. Replication and clustering

Both MySQL and PG have replication and clustering capabilities to ensure that data operations are distributed horizontally.

MySQL supports the replication mechanism of master-standby and one-master and multi-standby, and ensures that all data is transferred to the standby through SQLs, that is, binlog. This is why replication can only be asynchronous and semi-synchronous.

Advantages: the standby machine can write. This means that once master crashes, slave can take over immediately to ensure that the application works properly. DBAs needs to make sure that slave becomes the master and that the new binlog is copied to the original owner. Replication becomes slow when there are many long SQL.

MySQL also supports NDB clusters, that is, multi-master replication mechanisms. This type of replication is good for transactions that require horizontal scaling.

PG replication is different from MySQL, it is based on WAL files, making replication more reliable, faster, and more manageable. He also supports master-standby and one-master and multi-slave modes, including cascading replication. The replication of PG is called stream replication or physical replication, which can be asynchronous or synchronous.

By default, replication is asynchronous and Slave satisfies read requests. If you are required to read the same data on the standby as on the host, you need to set up synchronous replication. But the disadvantage is that once the transaction on the standby machine is not committed, the host will hang.

Table-level replication can be archived using third-party tools such as Slony, Bucardo, Londiste, RubyRep, and so on. These tools are based on trigger replication. PG also supports logical replication. Logical replication was initially supported through the pglogical extension, and from 10 onwards the kernel supports logical replication.

7. View

MySQL supports views, and the number of tables used through SQL under the view is limited to 61. Views do not store physical data and do not support materialized views. Views created by simple SQL statements can be updated, while views created by complex SQL cannot be updated.

PG is similar to MySQL. Views created by simple SQL are updatable and complex. However, complex views can be updated through RULES. PG supports materialized views and REFRESHED.

8. Trigger

MySQL supports triggers for AFTER and BEFORE events on INSERT, UPDATE, and DELETE. Triggers execute dynamic SQL statements and stored procedures differently.

The trigger of PG is more advanced. Triggers that support AFTER, BEFORE, INSTEAD OF events. If a complex SQL is executed when the trigger wakes up, it can be done through a function. Triggers in PG can execute functions dynamically:

CREATE TRIGGER audit

AFTER INSERT OR UPDATE OR DELETE ON employee

FOR EACH ROW EXECUTE FUNCTION employee_audit_func ()

9. Stored procedure

Both MySQL and PG support stored procedures, but MySQL only supports standard SQL syntax, while PG supports very advanced stored procedures. PG completes the stored procedure as a function with a RETURN VOID clause. PG supports many languages: Ruby, Perl, Python, TCL, PL/pgSQL, SQL, and JavaScript. MySQL doesn't have that many.

10. Query

Limitations to consider when using MySQL:

The return values of some UPDATE SQL of  do not conform to the SQL standard.

Mysql > select * from test

+-+ +

| | c | C1 |

+-+ +

| | 10 | 100 | |

+-+ +

1 row in set (0.01 sec)

Mysql > update test set c=c+1, c1roomc

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from test

+-+ +

| | c | C1 |

+-+ +

| | 11 | 11 |

+-+ +

1 row in set (0.00 sec)

Expected standard form:

Mysql > select * from test

+-+ +

| | c | C1 |

+-+ +

| | 11 | 10 | |

+-+ +

UPDATE or DELETE statements that cannot be executed by :

Mysql > delete from test where c in (select T1. C from test T1, test T2 where t1.c=t2.c)

ERROR 1093 (HY000):

LIMIT clause cannot be used in  subquery

Mysql > select * from test where c in (select c from test2 where c create table emp (id int not null, fname varchar (30), lname varchar (30), store_id int not null) partition by range (store_id) (partition p0 values less than (6) tablespace tbs, partition p1 values less than (20) tablespace tbs1, partition p2 values less than (40) tablespace tbs2)

ERROR 1478 (HY000): InnoDB: A partitioned table is not allowed in a shared tablespace.

Mysql >

PG supports table partition inheritance and declaration of table partitions. Declaring table partitioning was introduced in 10, similar to MySQL, while table partition inheritance is done through the use of triggers and rules. Partition types support RANGE, LIST, HASH. Restrictions:

, like MySQL, declares that table partitions can only be on primary and unique keys

 inherits table partitions, and child tables cannot inherit primary and unique keys.

 INSERT and UPDATE do not automatically send a constant letter to the word list.

12. Scalability of the table

As the table segment becomes larger and larger, it can cause performance problems, and queries on this table will take up more resources and take more time. MySQL and PG need to consider different factors.

MySQL supports B+tree indexes and partitions, which can improve performance for large tables. However, DBA cannot be better tuned because it does not support bitmap, partial, and functional indexes. And partitioned tables cannot be placed on different tablespaces, which makes IO not more balanced.

PG's expression index, partial index, bitmap index, and full-text index can all improve the performance of large tables. The table partition and index of PG can be placed on different disks, which can improve the scalability of the table. To achieve horizontal table-level extension, you can use citusdb, Greenplum, Netezza, and so on. Open source PG does not support horizontal table partitioning, PostgresXC supports it, but its performance is not good.

13. Stora

Data storage is a key capability of the database. Both PG and MySQL provide a variety of options for storing data.

PG has a general storage feature: tablespaces can hold physical objects such as tables, indexes, materialized views, and so on. Through tablespaces, objects can be grouped and stored in different physical locations, which can improve IO capabilities. Previous versions of PG12 do not support pluggable storage, and 12 only support pluggable architecture.

MySQL, like PG, will have tablespace features in the future. He supports pluggable storage engines. This is an advantage of MySQL.

14. Supported data models

The NoSQL capabilities of relational databases can help deal with unstructured data, such as json, xml, text, etc.

The NoSQL capability of MySQL is limited. The introduction of the json data type takes a long time to become more mature.

PG has rich json capabilities and is a good choice for developers who need NoSQL capabilities in the next 3 years. The Json and jsonb data types make PG faster and more efficient for json operations. You can also build B-tree indexes and GIN indexes on json data columns. XML and HSTORE data types can handle data in XML format as well as other complex text formats. With the support of spatial data types, PG is a complete multi-model database.

15. Security

Database security plays an important role in databases that can be accessed without authentication. Security includes object level and connection level.

MySQL pays access to databases, objects, and connections through ROLES and PRIVILEGES. Each user needs to be granted connection permissions.

GRANT ALL PRIVILEGES ON testdb. TO 'testuser@'192.168.1.1' IDENTIFIED BY' newpassword'

GRANT ALL PRIVILEGES ON testdb. TO 'testuser@'192.168.1.*' IDENTIFIED BY' newpassword'

You need to specify a password for each authorization, otherwise the user will not be able to connect.

MySQL also supports SSL connections. Can be integrated with external authentication systems LDAP and PAM. It's part of its enterprise version.

PG uses the GRANT command to provide access through ROLES and PRIVILEGES. Connection authentication is relatively simple, which is set through the pg_hba.conf authentication file:

Host database user address auth-method [md5 or trust or reject]

The open source version of PG also supports SSL connectivity and can be integrated with external authentication systems.

The parsing function aggregates a set of row data. There are two types of analytic functions: window functions and aggregate functions. The aggregate function performs the aggregation and returns an aggregate value of the record collection (sum,avg,min,max, etc.); the parsing function returns the aggregate value of each record. Both MySQL and PG support multiple aggregate functions. MySQL8.0 only supports windowing functions, and PG has supported it for a long time.

Window functions supported by PG:

Function name description

CUME_DIST Return the relative rank of the current row.

DENSE_RANK Rank the current row within its partition without gaps.

FIRST_VALUE Return a value evaluated against the first row within its partition.

LAG Return a value evaluated at the row that is at a specified physical offset row before the current row within the partition.

LAST_VALUE Return a value evaluated against the last row within its partition.

LEAD Return a value evaluated at the row that is offset rows after the current row within the partition.

NTILE Divide rows in a partition as equally as possible and assign each row an integer starting from 1 to the argument value.

NTH_VALUE Return a value evaluated against the nth row in an ordered partition.

PERCENT_RANK Return the relative rank of the current row (rank-1) / (total rows-1)

RANK Rank the current row within its partition with gaps.

ROW_NUMBER Number the current row within its partition starting from 1.

MySQL supports all PG window functions, except for the following restrictions:

 window functions cannot appear in UPDATE and DELETE

 window function does not support DISTINCT

 window function does not support NESTED

16. Graphical interface tools

MySQL has Oracle's SQL Developer, MySQL workbench, dbeaver, omnidb, etc., and monitoring tools include nagios, cacti, zabbix and so on. PG can also use Oracle's SQL Developer, pgAdmin, omnidb, dbeaver. Monitoring tools are Nagios, Zabbix, and Cacti.

17. Performance

MySQL database performance tuning options are limited and are not supported by many index types. Writing an efficient SQL statement is challenging. MySQL is also not a good choice for large-scale data. Tablespaces only support innodb and cannot accommodate table partitions.

PG is well suited for any type of load: OLTP,OLAP, data warehouse, etc. Because there are many index types supported, the performance can be better improved. PG also has the option to collect database memory, and partitioned tables can be placed in different table spaces to balance IO.

18 、 Adoption

PG is the most advanced open source database in the world. EnterpriseDB and 2ndQuadrant can ensure that PG is used by more users around the world.

MySQL represents the best choice for RDBMS and ORDBMS applications. Because since Oracle acquired MySQL dependence, the adoption rate of MySQL has declined significantly, and the progress of development in the open source field has also been impacted, which has attracted criticism from MySQL users.

19. The best environment

MySQL is popular in LAMP stack, and PG is popular in LAPP stack. The LAPP stack represents Linux, Apache, Postgres, and Php/Python, and is becoming more and more popular. The LAMP stack represents Linux Apache MySQL/MongoDB and Php/Python.

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.