PostgreSQL is similar to Oracle's multi-process framework, so it can support high-concurrency application scenarios. This is similar to Oracle databases, so it is easier to switch Oracle DBA to PostgreSQL databases, after all, PostgreSQL databases are similar to Oracle databases.
At the same time, the source code of the PostgreSQL database is easier to understand than the source code of the MySQL database. If the team has a strong knowledge of the C language, it can be developed on the PostgreSQL database, for example, the implementation of a system similar to greenplum can also be in line with the current distributed trend.
To illustrate the functions of postgresql, I will briefly compare the differences between the PostgreSQL database and the MySQL database:
We will first use the "50 things that must be known before migrating from Oracle to MySQL" Translated by jametong to see how to transfer Oracle to MySQL:
50 things to know before migrating Oracle to MySQL
By Baron Schwartz, translated by jametong
1. subquery optimization is poor.
2. Weak processing of complex queries
3. the query optimizer is not mature enough.
4. Insufficient performance optimization tools and measurement information
5. Relatively weak audit functions
6. immature security features can be said to be rough. there is no concept of user groups and roles, and there is no permission revocation function (only permissions can be granted ). when a user logs on from a different host/network with the same user name/password, it may be treated as a completely different user. there is no built-in encryption function similar to Oracle.
7. the authentication function is fully built-in. LDAP, Active Directory, and other similar external authentication functions are not supported.
8. The MySQL cluster may be significantly different from what you think.
9. Limited Functions of stored procedures and triggers.
10. Weak vertical scalability.
11. MPP (large-scale parallel processing) is not supported ).
12. supports SMP (symmetric multi-processor), but if each processor has more than 4 or 8 cores (cores), MySQL has poor scalability.
13. For time, date, interval, and other time types, there is no storage type of the second level or lower.
14. The language functions used to write stored procedures, triggers, scheduled events, and stored functions are weak.
15. There is no roll-back-based recovery function, and only roll-forward recovery is available.
16. snapshot 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 transit server. However, it has many features and vulnerabilities.
18. Data Integrity check is very weak, and even basic integrity constraints are often not executable.
19. There are very few optimizer prompts for optimizing the query statement execution plan.
20. There is only one table connection type: nested loop join (Nested-loop). Sorting-merge join (sort-merge join) and hash join are not supported ).
21. 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.
22. Bitmap indexes are not supported. Each storage engine supports different types of indexes. Most storage engines Support B-tree indexes.
23. Fewer management tools and immature functions.
24. there are no mature ide tools and debugging programs that are satisfactory. you may have to write a stored procedure in a text editor and debug it by inserting records into the table (debug log table.
25. Each table can use a different storage engine.
26. Each storage engine may have significant differences in behavior, features, and functions.
27. Most storage engines do not support foreign keys.
28. The default storage engine (MyISAM) does not support transactions and is vulnerable to corruption.
29. The most advanced and popular storage engine InnoDB is owned by Oracle.
30. Some execution plans only support specific storage engines. Specific types of Count queries can be executed quickly in such storage engines, and may be slow in other storage engines.
31. The execution plan is not shared globally. It is shared only within the connection.
32. The full-text search function is limited and only applicable to non-transactional storage engines. Ditto is used for Geographic Information Systems/spatial types and queries.
33. No resource control. A completely unauthorized user can consume all the memory of the server and cause it to crash without any effort, or can exhaust all CPU resources.
34. There are no software packages integrated with business intelligence and OLAP ** datasets.
35. No tool similar to Grid Control (http://solutions.mysql.com/go.php? Id = 1296 & t = s)
36. There is no function similar to RAC. If you ask "How to use MySQL to construct RAC", you can only say that you have asked an error.
37. User-defined types or domains are not supported ).
38. The maximum number of connections supported by each query is 61.
39. 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 ). it supports some SQL syntax extensions similar to merge or similar features, but it is very simple compared with Oracle.
40. Function columns are not supported (for computed or expression-based columns, oracle11g supports computed columns, and early versions support virtual columns (rownum, rowid )).
41. function indexes are not supported. You can only create an index based on a specific column.
42. materialized views are not supported.
43. statistics vary greatly between different storage engines, and all statistics supported by storage engines only support simple cardinality) and the number of records in a certain range (rows-in-a-range ). in other words, the statistical information of data distribution is limited. there are not many mechanisms for updating statistics.
44. No built-in load balancing and Failover mechanisms.
45. 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.
46. The cluster is not as perfect as I think. Maybe I have mentioned this, but it is worth repeating.
47. The information_schema function is limited and the access speed is slow (it is easy to crash on busy systems ).
48. Online alter table operations are not supported.
49. sequence.
50. operations similar to alter table or create table are non-transactional. they commit uncommitted transactions and cannot roll back or perform disaster recovery. schame is stored in the file system, which is irrelevant to the storage engine used by schame.
PostgreSQL databases can solve the following problems:
1. subquery optimization is not performing well
2. Weak processing of complex queries
3. the query optimizer is not mature enough.
PostgreSQL fully supports SQL-92 standards, SQL support is also comprehensive, can support complex SQL queries.
4. Insufficient performance optimization tools and measurement information
PostgreSQL provides an execution plan and detailed cost values, allowing you to easily view the SQL Execution efficiency.
9. Limited Functions of stored procedures and triggers.
PostgreSQL provides comprehensive support for stored procedures and triggers.
11. MPP (large-scale parallel processing) is not supported)
PostgreSQL is similar to the Oracle database architecture and a multi-process architecture. Unlike MySQL, It is a multi-thread architecture, so it supports MPP.
18. Data Integrity check is very weak, and even basic integrity constraints are often not executable.
PostgreSQL provides a complete data integrity check mechanism that supports foreign keys.
20. There is only one table connection type: nested loop join (Nested-loop). Sorting-merge join (sort-merge join) and hash join are not supported ).
PostgreSQL supports the connection types of these tables.
21. 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 in PostgreSQL data. Assume that the two fields of table t, col1, have two indexes, idx_1 and idx_2. Then select * from t where col1 =: A and col2 =: B; when querying, the PostgreSQL database may convert this query to select * from t where col1 =: A intersect select * from t where col2 =: B, so that both indexes can be used.
25. Each table can use a different storage engine.
26. Each storage engine may have significant differences in behavior, features, and functions.
27. Most storage engines do not support foreign keys.
28. The default storage engine (MyISAM) does not support transactions and is vulnerable to corruption.
29. The most advanced and popular storage engine InnoDB is owned by Oracle.
30. Some execution plans only support specific storage engines. Specific types of Count queries can be executed quickly in such storage engines, and may be slow in other storage engines.
PostgreSQL has only one storage engine, so the above situation does not exist. PostgreSQL supports complete transactions.
32. The full-text search function is limited and only applicable to non-transactional storage engines. Ditto is used for Geographic Information Systems/spatial types and queries.
PostgreSQL supports full-text search and 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 are not supported ).
PostgreSQL supports a wide range of types and custom types.
39. 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 ). it supports some SQL syntax extensions similar to merge or similar features, but it is very simple compared with Oracle.
These PostgreSQL databases are supported, such as window functions.
41. function indexes are not supported. You can only create an index based on a specific column.
PostgreSQL supports function Indexing
49. sequence.
PostgreSQL supports sequence
50. operations similar to alter table or create table are non-transactional. They commit uncommitted transactions and cannot roll back or perform disaster recovery. schame is saved in
This is not related to the storage engine it uses.
PostgreSQL does not have this problem.
