Comparison between PostgreSQL and MySQL

Comparison between PostgreSQL and MySQL

Features	MySQL	PostgreSQL
Instance	Run the MySQL Command (mysqld) to start the instance. One instance can manage one or more databases. One server can run multiple mysqld instances. An instance manager can monitor various instances of mysqld.	Start the instance by executing the Postmaster process (pg_ctl. One instance can manage one or more databases, which form a cluster. A cluster is an area on a disk. This area 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 set of named objects that are separated from other databases in the instance. All databases in a MySQL instance share the same system catalog.	A database is a set of named objects. Each database is an entity separated from other databases. Each database has its own system catalog, but all databases share pg_databases.
Data Buffer Zone	PassInnodb_buffer_pool_sizeConfigure parameters to set the data buffer. This parameter is the number of bytes in the memory buffer. InnoDB uses this buffer to cache table data and indexes. On a dedicated database server, this parameter can be set to 80% of the physical memory volume of the machine.	Shared_buffersCache. By default, 64 buffers are allocated. The default block size is 8 K. You can set the shared_buffers parameter in the postgresql. conf file to update the buffer cache.
Database Connection	The client uses the CONNECT or USE statement to CONNECT to the database. You must specify the Database Name and user id and password. Use roles to manage users and user groups in the database.	The client uses the connect statement to connect to the database. You must specify the Database Name and user id and password. Use roles to manage users and user groups in the database.
Authentication	MySQL manages authentication at the database level. Only password authentication is supported.	PostgreSQL supports a wide range of authentication methods: trust authentication, password authentication, Kerberos authentication, Ident-based authentication, LDAP authentication, and PAM Authentication.
Encryption	You can specify a password at the table level to encrypt data. You can also use the AES_ENCRYPT and AES_DECRYPT functions to encrypt and decrypt column data. Network Encryption can be achieved through SSL connections.	You can use functions in the pgcrypto library to encrypt/decrypt columns. Network Encryption can be achieved through SSL connections.
Audit	You can execute grep on querylog.	You can use the PL/pgSQL trigger to audit a table.
Query description	Run the EXPLAIN command to view the EXPLAIN Plan.	Run the EXPLAIN command to view the EXPLAIN Plan.
Backup, recovery, and logs	InnoDB uses the write-ahead log to record data. Supports both online and offline full backup and crash and transaction recovery. Third-party software is required to support hot backup.	Maintain the pre-write log in a sub-directory of the data directory. Supports full online and offline backup, crash, time point, and transaction recovery. Supports hot backup.
JDBC driver	You can download the JDBC driver from references.	You can download the JDBC driver from references.
Table type	Depends on the storage engine. For example, the NDB storage engine supports partitioned tables and the memory engine supports memory tables.	Supports partition tables of temporary tables, regular tables, and range and list types. Hash partition tables are not supported. Since table partitions in PostgreSQL are completed through table inheritance and rule systems, more complex partitioning methods can be implemented.
Index type	Depends on the storage engine. MyISAM: BTREE, InnoDB: BTREE.	Supports B-tree, hash, R-tree, and Gist indexes.
Constraints	The primary key, foreign key, unique, and non-null constraints are supported. Resolve the check constraints, but do not enforce them forcibly.	Supports primary keys, foreign keys, 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 ++.	No separate stored procedures are implemented through functions. User-defined functions can be written in PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, SQL, and C.
Trigger	Supports pre-row triggers, post-row triggers, and statement triggers. Trigger statements are written in a procedural language.	Supports front-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_mirror.conf
XML support	Limited XML support.	Limited XML support.
Data access and management server	OPTIMIZE TABLE-- Reclaim unused space and eliminate data file fragments Myisamchk-analyze-- Update the statistics used by the query optimizer (MyISAM storage engine) Mysql-- Command line tool MySQL Administrator-- Client GUI tool	Vacuum-- Recycle unused space Analyze-- Updates statistics used by the query optimizer. Psql-- Command line tool PgAdmin-- Client GUI tool
Concurrency Control	Table-level and row-level locks are supported. 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 (default: You can see the snapshot of the database when the query is started) and Serialization (similar to Repeatable Read-you can only see the results submitted before the transaction starts ). Use the set transaction statement to SET the isolation level at the TRANSACTION level. SET sessions at the SESSION level.

Disadvantages of MySQL over PostgreSQL:

MySQL	PostgreSQL
The most important engine InnoDB has long been controlled by Oracle. Currently, the entire MySQL database is controlled by Oracle.	The BSD Protocol is not monopolized by large companies.
Weak processing of complex queries and immature Query Optimizer	A powerful query optimizer that supports complex query processing.
There is only one table connection type: nested loop join (nested-loop), which does not support sorting-merge join (sort-merge join) and hash join ).	All supported
Insufficient performance optimization tools and metrics	Some performance views are provided to conveniently view the select, delete, update, and insert statistics on a table and indexes, as well as the cache hit rate. There is an open-source pgstatspack tool on the Internet.
InnoDB tables and indexes are stored in the same way. That is to say, all tables are indexed and organized tables. This generally requires that the primary key should not be too long and the primary key at the time of insertion should increase sequentially, otherwise it will have a great impact on performance.	This problem does not exist.
Most queries can only use a single index on a table. In some cases, multiple indexes are used for queries, but the query optimizer usually underestimate the cost, they are often slower than table scans.	This problem does not exist.
It takes a long time to add columns to a table, which is basically to recreate the table and index.	Add columns to the table. Only table definitions are added to the data dictionary and the table is not rebuilt.
Stored Procedures and triggers have limited functions. Language functions that can be used to write stored procedures, triggers, scheduled events, and stored functions are weak.	In addition to pl/pgsql stored procedures, pl/perl, pl/python, and pl/Tcl stored procedures are also supported. It also supports writing stored procedures in C language.
Sequence is not supported.	Supported
Function indexes are not supported. You can only create an index based on a specific column. Materialized views are not supported.	Function indexes and partial data indexes are supported. You can use the rule system to implement the materialized view function.
The execution plan is not shared globally. It is shared only within the connection.	Execution Plan sharing
A small part of the SQL syntax supported by MySQL (ansi SQL standard. Recursive queries, common table expressions (with statements in Oracle), and window functions (analysis functions) are not supported ).	All supported

User-Defined type or domain is not supported)	Yes.
For time, date, interval, and other time types, there is no storage type of the second or lower level	It can be accurate to less than seconds.
The authentication function is fully built-in. It does not support operating system authentication, PAM Authentication, LDAP, and other similar external authentication functions.	Supports OS authentication, Kerberos authentication, Ident authentication, LDAP authentication, and PAM Authentication.
Database link is not supported. There is a storage engine called Federated that can pass query statements to a table on a remote server as a transit server. However, it is very functional and has many vulnerabilities.	Dblink and dbi-link can be connected to oracle and mysql.
Mysql Cluster may be significantly different from what you think. There are few open-source cluster software. Replication is asynchronous and has many limitations. for example, it is a single-thread (single-threaded), so the recovery speed of a server Load balancer with higher processing capability is hard to keep up with the Master with a slower processing capability.	Supports a wide range of open-source cluster software.
Explain: the execution plan result is simple.	Explain returns a wealth of information.
Operations similar to alter table or create table are non-transactional. They commit uncommitted transactions and cannot be rolled back or perform disaster recovery.	DDL also has transactions.

Major advantages of PostgreSQL:
1. postgreSQL is completely free, and it is a BSD Protocol. If you change PostgreSQL and then sell it for money, no one cares about you. This is very important, this indicates that the PostgreSQL database will not be controlled by other companies. Oracle databases are commercial databases and are not open. While MySQL databases are open-source, but now SUN has been acquired by oracle and is now basically controlled by oracle. Before SUN was acquired, the most important InnoDB Engine in MySQL is also controlled by oracle, and many important data in MySQL is stored in the InnoDB engine. Our company is like this. Therefore, if the market scope of MySQL conflicts with the market scope of oracle databases, oracle will sacrifice MySQL, which is undoubtedly true.
2. there are many open-source software that works with PostgreSQl, including many distributed cluster software, such as pgpool, pgcluster, slony, and plploxy. It is easy to implement solutions such as read/write splitting, load balancing, and horizontal data splitting, this is difficult in MySQL.
3. The source code of PostgreSQL is clearly written. It is easier to write than MySQL, and the source code of MySQL is suspected to be confused. Therefore, many companies use PostgreSQL for secondary development.
4. PostgreSQL is better than MySQL in many aspects, such as complex SQL Execution, stored procedures, triggers, and indexes. At the same time, PostgreSQL is multi-process, while MySQL is thread. Although the concurrency is not high, MySQL processing speed is fast, but when the concurrency is high, for a single machine with multiple cores, the overall processing performance of MySQL is not as good as that of PostgreSQL because MySQL threads cannot fully utilize CPU capabilities.
Currently, I only think about this. I want to add it later. You are welcome to make a brick.


Differences between PostgreSQL and multiple versions of oracle or InnoDB

The major difference between PostgreSQL and multiple versions of oracle or InnoDB is whether the latest and historical versions are stored separately. PostgreSQL does not distinguish between the latest and historical versions, but oracle and InnoDB, while innodb only separates data, the index itself is not separated.
PostgreSQL has the following advantages:
1. PostgreSQL does not have rollback segments, while oracle and innodb both have rollback segments. For oracle and Innodb, the rollback segment is very important. If the rollback segment is damaged, it may cause data loss or even serious problems that the database cannot be started. Because PostgreSQL does not have a rollback segment, the old data is recorded in the original file, so when the database recovers after a crash exception occurs, it will not perform as complex recovery as oracle and Innodb databases, because redo and undo are required for synchronization between oracle and Innodb. Therefore, when a crash exception occurs in a PostgreSQL database, the chances of a database not getting started are lower than those of oracle and mysql.
2. Since the old data is directly recorded in the data file, rather than in the rollback segment, it does not often report ora-01555 errors as oracle does.
3. rollback can be completed quickly, because rollback does not delete data, but oracle and Innodb are complicated during rollback, and the modifications made by this firm must be cleared during transaction rollback, the inserted record must be deleted, and the updated record must be updated (see the row_undo function). At the same time, the rollback process will generate a large number of redo logs again.
4. WAL logs are simpler than oracle and Innodb. For oracle, you must not only record changes in data files, but also record changes in rollback segments.
The major disadvantage of PostgreSQL multi-version is:
1. The latest and historical versions are not separated for storage. As a result, more scans are required to clear old versions at a high cost. However, normal databases have peak periods. If we reasonably arrange VACUUM, this is not a big problem, and VACUUM is further enhanced in PostgreSQL9.0.
2. Because the index does not have version information, Coverage index scan cannot be implemented. That is, the query only scans the index and returns the required attributes directly from the index. You also need to access the table. Oracle and Innodb can;


Comparison between process mode and thread mode
PostgreSQL and oracle adopt the process mode, while MySQL adopts the thread mode.
The process mode has high CPU utilization.
Shared Memory is required for data sharing in the process mode, while data in the thread mode is shared in the process space. Different threads only need to control synchronization between threads.
The thread mode consumes less resources.
Therefore, MySQL supports more connections than oracle.
For PostgreSQL, this problem also exists if the connection pool software is not used. However, PostgreSQL has excellent connection pool software, such as pgbouncer and pgpool, so many connections can be supported through the connection pool.

Comparison between heap table and index organization table

Oracle supports heap tables and index organization tables.
PostgreSQL only supports heap tables and does not support index organization tables.
Innodb only supports index organization tables
Advantages of indexed organization tables:
The data in the table is organized by index, and the data is ordered. If the data is accessed by primary key, the access data is faster. In a heap table, when accessing data by primary key, you must first locate the physical location of the data by primary key index.
Disadvantages of indexing an Organizational table:
When other indexes are added to an index organization table, the Data Location of other index records is no longer a physical location, but a primary key value. Therefore, for an index organization table, the primary key value cannot be too large, otherwise, the occupied space is relatively large.
For an index organization table, if data is inserted in the middle each time, the index may be split, which greatly reduces the insertion performance. Therefore, for innodb, we generally recommend that the primary key be a meaningless sequence, so that each insertion occurs at the end to avoid this problem.
Because the index organization table is based on an index tree, it generally accesses data blocks according to the relationship between data blocks, rather than accessing data by physical blocks, therefore, full table scanning is much slower than heap tables, which may not be obvious in OLTP, but may be a problem in data warehouse applications.




PostgreSQL9.0 features:
Hot Standby function in PostgreSQL
That is, standby can also provide read-only services when synchronizing application logs, which is useful for read/write splitting. This function is only available in oracle11g.

Functions of PostgreSQL Asynchronous Commit:
This feature is only available in oracle at oracle11g R2. In many application scenarios, a small amount of data is allowed to be lost when the server goes down. This function is particularly suitable for such scenarios. Set synchronous_commit to false in PostgreSQL9.0 to enable this function. It should be noted that, although it is set to asynchronous submission, when the host goes down, PostgreSQL will only lose a small amount of data, asynchronous submission will not cause data corruption and the database will not be able to start. I have never heard of this function in MySQL.

Features of Indexing in PostgreSQL:

PostgreSQL can have partial indexes, that is, only some data in the table can be indexed. create index can contain the where condition. At the same time, indexes in PostgreSQL can be scanned in reverse order, so you do not need to create a special descending index in PostgreSQL.