MySQL is so widely used that I have to migrate some applications from MySQL to PostgreSQL, many open source software is mysql as a database standard, and MySQL as the basis of the abstract, but the use of the process, found that MySQL has a lot of problems, So all migrated to PostgreSQL and turned to a MySQL vs. PostgreSQL article:
PostgreSQL is a multi-process framework similar to Oracle, so it can support high concurrency scenarios, much like the Oracle database, so transfer Oracle DBA to the PostgreSQL database
is easier, after all, the PostgreSQL database is similar to the Oracle database.
At the same time, the source code of PostgreSQL database is easier to read than the source code of MySQL database, if the team's C language ability is better known, it can be developed on the PostgreSQL database, for example, to achieve similar greenplum system, This also can be connected with the current distributed trend.
To illustrate the functionality of PostgreSQL, let me briefly compare the differences between the PostgreSQL database and the MySQL database:
Let's take a look at some of the 50 things you need to know before migrating from Oracle to MySQL with Jametong translation:
Things to know before migrating Oracle to MySQL
by Baron schwartz,translated by Jametong
1. suboptimal performance of sub-queries.
2. Weak processing of complex queries
3. The query optimizer is not mature enough
4. Insufficient performance optimization tool and metric information
5. Relatively weak audit function
6. The safety function is immature, even can be said very rough. There is no concept of user groups and roles, there is no ability to reclaim permissions (permissions can only be granted). When a user logs on from a different host/network with the same username/password, may be treated as a completely different user. There is no built-in encryption functionality similar to Oracle.
7. The authentication feature is fully built-in. ldap,active directory and other similar external authentication features are not supported.
8. Mysql cluster may differ greatly from your imagination.
9. Stored procedures and triggers have limited functionality.
10. The vertical scalability is weak.
11. MPP (Massively parallel processing) is not supported.
12. SMP (symmetric multiprocessor) is supported, but MySQL has poor scalability if each processor exceeds 4 or 8 cores (CORE).
13. There are no storage types for time, date, interval, and other time types that are below the second level.
14. The language functions that can be used to write stored procedures, triggers, scheduled events, and stored functions are weak.
15. There is no recovery feature based on rollback (roll-back), only roll forward (roll-forward) recovery function.
16. Snapshot functionality is not supported.
17. 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.
18. Data integrity checks are very weak and often fail to execute even with basic integrity constraints.
19. There are very few optimizer tips for optimizing query statement execution plans.
20. 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.
21. 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.
22. Bitmap indexing (bitmap index) is not supported. Each storage engine supports different types of indexes. Most storage engines support B-tree indexes.
23. The management tools are small and the functions are not mature enough.
24. There is no mature and satisfactory IDE tool and debugger. You might have to write a stored procedure in a text editor and debug by inserting records into a table (debug Log table).
25. A different storage engine can be used for each table.
26. Each storage engine can vary greatly in behavior, features, and functionality.
27. Most storage engines do not support foreign keys.
28. The default storage engine (MyISAM) does not support transactions and is prone to corruption.
29. The most advanced and popular storage engine InnoDB is owned by Oracle.
30. Some execution plans only support specific storage engines. A specific type of count query, which executes very quickly in this storage engine, can be slow in another storage engine.
31. The execution plan is not shared globally, and is shared only within the connection.
32. Full-Text search functionality is limited and applies only to non-transactional storage engines. Ditto for geographic information systems/spatial types and queries.
33. No resource control. A fully unauthorized user can effortlessly run out of all of the server's memory and crash it, or can run out of all CPU resources.
34. There are no integrated business intelligence (Intelligence), OLAP * * data sets and other packages.
35. No tool similar to Grid control (Http://solutions.mysql.com/go.php?id=1296&t=s)
36. There is no feature similar to RAC. If you ask "how to use MySQL to construct RAC", you can only say that you have asked the wrong question.
37. User-defined types or domains (domain) are not supported.
38. The maximum number of connections supported per query is 61.
A small part of MySQL's supported SQL syntax (ANSI SQL standard). Recursive queries, common table expressions (with statements from Oracle), or window functions (parsing functions) are not supported. Support for some SQL syntax extensions similar to merge or similar features, However, the functionality is simple compared to Oracle.
40. Feature columns are not supported (columns based on calculations or expressions, oracle11g begins to support computed columns, and virtual columns (ROWNUM,ROWID) are supported in earlier versions).
41. Function indexes are not supported and can only be created on specific column-based indexes.
42. Materialized views are not supported.
43. The statistical information varies widely between different storage engines, and all the statistics supported by the storage engine support only the simple cardinality (cardinality) and the number of records (Rows-in-a-range) within a certain range. In other words, data distribution statistics are limited. There are not many mechanisms for updating statistics.
44. There is no built-in load balancing and failover mechanism.
45. 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.
Cluster is not as perfect as you might think. I may have mentioned that, but it is worth repeating.
47. The data Dictionary (INFORMATION_SCHEMA) feature is limited and access is slow (crashes easily on busy systems).
48. The ALTER TABLE operation is not supported online.
49. Sequence is not supported.
50. Operations such as ALTER TABLE or CREATE table are non-transactional. They commit uncommitted transactions and cannot roll back or do disaster recovery. Schame is stored on the file system, regardless of the storage engine it uses.
The PostgreSQL database can address the above problems:
1. suboptimal Performance for subqueries
2. Weak processing of complex queries
3. The query optimizer is not mature enough
PostgreSQL fully supports the SQL-92 standard, and support for SQL is comprehensive and can support complex SQL queries.
4. Insufficient performance optimization tool and metric information
PostgreSQL provides an execution plan and detailed cost values, which makes it easy to see the efficiency of SQL execution.
9. Stored procedures and triggers have limited functionality.
PostgreSQL provides complete support for stored procedures and triggers.
11. MPP not supported (massively parallel processing)
PostgreSQL, like the Oracle database architecture, is a multi-process architecture, and unlike MySQL, which is a multithreaded architecture, it can support MPP.
18. Data integrity checks are very weak and often fail to execute even with basic integrity constraints.
PostgreSQL provides a comprehensive data integrity check mechanism to support foreign keys.
20. 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.
PostgreSQL supports these table connection types
21. 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.
The PostgreSQL data does not have this problem, assuming that the two fields of table T col1 have two indexes on the col2, Idx_1 and Idx_2, then select * from T where col1=:a and col2=:b; The PostgreSQL database is likely to convert this query into a select * from t where col1=:a intersect select * from T where col2=:b so that two indexes can be used.
25. A different storage engine can be used for each table.
26. Each storage engine can vary greatly in behavior, features, and functionality.
27. Most storage engines do not support foreign keys.
28. The default storage engine (MyISAM) does not support transactions and is prone to corruption.
29. The most advanced and popular storage engine InnoDB is owned by Oracle.
30. Some execution plans only support specific storage engines. A specific type of count query, which executes very quickly in this storage engine, can be slow in another storage engine.
PostgreSQL has only one storage engine, so there is no situation above. PostgreSQL supports well-developed transactions.
32. Full-Text search functionality is limited and applies only to non-transactional storage engines. Ditto for geographic information systems/spatial types and queries.
The PostgreSQL database supports full-text search and supports more types of indexes such as B-tree,r-tree, Hash, GiST, gin,r-tree,gist,gin indexes can be used for spatial types and queries.
37. User-defined types or domains (domain) are not supported.
PostgreSQL supports a wide variety of types and also supports custom types.
A small part of MySQL's supported SQL syntax (ANSI SQL standard). Recursive queries, common table expressions (with statements from Oracle), or window functions (parsing functions) are not supported. Support for some SQL syntax extensions similar to merge or similar features, However, the functionality is simple compared to Oracle.
These PostgreSQL databases are supported, such as window functions.
41. Function indexes are not supported and can only be created on specific column-based indexes.
PostgreSQL Support Function Index
49. Sequence is not supported.
PostgreSQL Support Sequence
50. Operations such as ALTER TABLE or CREATE table are non-transactional. They commit uncommitted transactions and cannot roll back or do disaster recovery. Schame is saved in the text
System, this is independent of the storage engine it uses.
PostgreSQL does not exist for this problem.
PostgreSQL vs. MySQL (RPM)
