Comparison of "Mei Cai Net" PostgreSQL with MySQL

Source: Internet
Author: User
Tags connection pooling ldap

MySQL's disadvantage relative to PostgreSQL:

Mysql PostgreSQL
The most important engine InnoDB is very early in the control of Oracle Corporation. Currently, the entire MySQL database is controlled by Oracle. The BSD protocol was not monopolized by big companies.
Weak processing of complex queries, the query optimizer is not mature enough Very powerful query optimizer that supports very complex query processing.
There is only one type of table connection: nested loop join (nested-loop), sorting-merge Join (Sort-merge join) and Hash join (hash joins) are not supported. All support
Insufficient performance optimization tools and metrics

Provides a performance view that makes it easy to see SELECT, Delete, UPDATE, insert statistics that occur on a table and index, and also to see the cache hit rate. There is an open source Pgstatspack tool on the web.

InnoDB tables and indexes are stored in the same way. This means that the table is an indexed organization table. This generally requires that the primary key cannot be too long and that the primary key at the time of insertion is preferably ascending sequentially, otherwise there is a significant impact on performance.

There is no such problem.

Most queries use only a single index on the table, and in some cases, queries that use multiple indexes, but the query optimizer often underestimates its costs, which are often slower than table scans.

There's no such thing.

Table adds columns, basically rebuilding tables and indexes, which can take a long time.

Table adds columns, but adds table definitions to the data dictionary and does not rebuild the table

Stored procedures and triggers have limited functionality. Language functions that can be used to write stored procedures, triggers, scheduled events, and stored functions are weak

In addition to supporting pl/pgsql write stored procedures, Perl, Python, tcl type stored procedures are also supported: PL/PERL,PL/PYTHON,PL/TCL.

also supports writing stored procedures in C language.

Sequence is not supported.

Support

function indexes are not supported and can only be created on specific column-based indexes.

Materialized views are not supported.

Support function index, and also support partial data index, through the rule system can realize the function of materialized view.

The execution plan is not shared globally and is shared only within the connection.

Perform plan sharing

MySQL supports a small subset of SQL syntax (ANSI SQL Standard). Recursive queries, common table expressions (with statements from Oracle), or window functions (analytic functions) are not supported.



All support

User-defined types or domains (domain) are not supported

Support.

For time, date, interval, and other time types, there are no storage types below the second level

Can be accurate to the following seconds.

The authentication feature is fully built-in, does not support operating system authentication, Pam authentication, and does not support LDAP and other similar external authentication features.

Supports OS authentication, Kerberos authentication, Ident authentication, LDAP authentication, 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 relay, but it is very coarse and has many vulnerabilities.

There are dblink, and there is a dbi-link thing that can be connected to both Oracle and MySQL.

Mysql cluster may differ greatly from your imagination. Open source cluster software is less.

The copy (Replication) feature is asynchronous and has great limitations. For example, it is single-threaded (single-threaded), so a more capable slave recovery is difficult to keep up with the relatively slow master.

There are rich open source cluster software support.

Explain see the results of the execution plan are simple.

Explain returns rich information.

Operations such as ALTER TABLE or CREATE table are non-transactional. They commit uncommitted transactions and cannot roll back or do disaster recovery

DDL also has a business.






PostgreSQL Main Advantages:
1. PostgreSQL is completely free, and it's BSD, it's important that if you change PostgreSQL and then sell it and nobody cares, it means that the PostgreSQL database will not be controlled by other companies. The Oracle database does not have to be a commercial database and is not open. Although the MySQL database is open source, but now with the acquisition of Sun by Oracle Company, is basically controlled by Oracle, in fact, before sun was acquired, the most important InnoDB engine in MySQL is controlled by Oracle company, And in MySQL, a lot of important data are placed in the InnoDB engine, anyway, our company is like this. So if MySQL has a market range that conflicts with the Oracle database market, there is no doubt that Oracle will sacrifice MySQL.
2. Many open source software with PostgreSQL, there are many distributed cluster software, such as Pgpool, Pgcluster, Slony, Plploxy and so on, it is easy to do read and write separation, load balancing, data level split and other programs, and this is more difficult under MySQL.
3. PostgreSQL source code is very clear, easy to read more than MySQL strong, suspected MySQL source code has been confused. So many companies are basic PostgreSQL do two times to develop.
4. PostgreSQL is more powerful than MySQL in many ways, such as execution of complex SQL, stored procedures, triggers, indexes. At the same time PostgreSQL is multi-process, and MySQL is a thread, although concurrency is not high, MySQL processing speed, but when the concurrency is high, for now multicore single machine, MySQL overall processing performance than PostgreSQL, The reason is that MySQL threads are unable to take full advantage of the CPU capacity.
At present only think of these, later thought to add, welcome everyone to shoot bricks.


The difference between PostgreSQL and the multi-version implementation of Oracle or INNODB

The biggest difference between PostgreSQL and Oracle or INNODB's multi-version implementations is whether the latest and historical versions are separated from storage, PostgreSQL is not, and Oracle and InnoDB, and InnoDB only separates the data, and the index itself is not separate.
The main advantages of PostgreSQL are:
1. PostgreSQL does not have a rollback segment, and Oracle and INNODB have rollback segments, and both Oracle and INNODB have rollback segments. For Oracle and InnoDB, the rollback segment is very important, and the rollback segment is corrupted, causing data loss or even a serious problem that the database cannot start. Because PostgreSQL does not have a rollback segment, the old data is recorded in the original file, so when the database is abnormally crash, it will not recover as complex as Oracle and InnoDB database. Redo and undo are required for the synchronization of Oracle and InnoDB recovery. As a result, the PostgreSQL database is less likely to get out of the database after an abnormal crash than Oracle and MySQL.
2. Because the old data is recorded directly in the data file, not the rollback segment, ora-01555 errors are not reported as often as Oracle.
3. Rollback can be completed very quickly, because rollback does not delete data, while Oracle and InnoDB, rollback is very complex, when the transaction rollback must clean up the changes made by the firm, the inserted records to be deleted, updated records to be updated back (see Row_undo function), The process of rolling back also generates a large number of redo logs again.
4. The Wal logs are simpler than Oracle and InnoDB, and for Oracle you need to record not only the changes to the data files, but also the changes to the rollback segments.
The main disadvantage of the multiple versions of PostgreSQL is:
1, the latest version and the historical version does not separate storage, resulting in the cleanup of older versions need to do more scanning, the cost is relatively large, but the general database has a peak, if we properly arrange vacuum, this is not a big problem, and in the PostgreSQL9.0 vacuum is further strengthened.
2, because there is no version information in the index, the coverage index scan cannot be implemented, that is, the query scans the index only, returns the desired property directly from the index, and requires access to the table. and Oracle and InnoDB can;


Comparison of process mode and threading mode
PostgreSQL and Oracle are process modes, and MySQL is thread mode.
The process pattern is relatively high for multi-CPU utilization.
Process mode shared data requires shared memory, while thread-mode data itself is shared within the process space, and different thread accesses need only control the synchronization between the threads.
The thread pattern is less resource-intensive.
So MySQL can support a lot more connections than Oracle.
For PostgreSQL, there is also this problem if you do not use the connection pooling software, but PostgreSQL has excellent connection pooling software, such as Pgbouncer and Pgpool, so a connection pool can also support a lot of connections.

Comparison of heap table and Index organization table

Oracle Support heap table, also support index organization table
PostgreSQL supports only heap tables and does not support indexed organization tables
InnoDB only supports indexed organization tables
Advantages of index Organization tables:
The data in the table is organized by index, the data is orderly, if the data are accessed by the primary key, the access data is faster. In the case of heap tables, when data is accessed by primary key, the physical location of the data must first be found by the primary key index.
Disadvantages of index Organization tables:
When you add another index to the Index organization table, the data location of the other index records is no longer the physical location, but the primary key value, so the value of the primary key cannot be too large for the index organization table, otherwise it takes up more space.
For an indexed organization table, if you insert data in the middle each time, it can cause the index to split, and index splitting can greatly reduce the performance of the insert. So for the use of InnoDB, it is generally best to make the primary key a meaningless sequence, so that the insertion happens at the end every time to avoid this problem.
Because the Index organization table is an index tree, generally it accesses data blocks that must be accessed in relation to the data blocks, rather than by physical blocks, so it is much slower than the heap table for full table scans, which may not be noticeable in OLTP, but may be an issue in the application of the Data Warehouse.




Featured Features in PostgreSQL9.0:
the hot standby feature in PostgreSQL
That is, standby can also provide read-only services when applying log synchronization, which is useful for reading and writing separations. This function is a function of oracle11g.

features of PostgreSQL asynchronous commit (asynchronous commit):
This feature is also the function of Oracle to ORACLE11G R2. Because in many scenarios, this is especially true in scenarios where a small amount of data is allowed to be lost when the outage is in use. Setting Synchronous_commit to False in PostgreSQL9.0 opens this function. It is important to note that, although set up in order to commit asynchronously, PostgreSQL only loses a small amount of data when the host is down, asynchronous commit does not cause data corruption and the database does not get out of the situation. MySQL has not heard of this feature.

featured features of the index in PostgreSQL:
PostgreSQL can have a partial index, that is, only some of the data in the table is indexed, CREATE index can take the where condition. While the indexes in PostgreSQL can be scanned backwards, there is no need to create a special descending index in PostgreSQL.

Comparison of "Mei Cai Net" PostgreSQL with MySQL

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.