A comparative analysis of PostgreSQL database and MySQL database

1.Posgresql is a process mode, multi-process, single-threaded, and similar to Oracle. While MySQL uses threading mode, single-process, multi-threaded, for this, you can view the Task Manager when running the database, and so is SQL Server.

As we all know, multi-process, process mode, the use of CPU time slice rotation, can take advantage of more full CPU, so on the multi-core on the light of the single machine, he clearly in support of high concurrency performance. While threading mode, it is obvious that a thread will consume less resources than open a process, and the environment between the different threads and access to the common storage area is obviously faster than the different processes, so it is faster to process and less expensive for resources, but performance is poor when concurrency is high.

2. Storage Engine. The posgresql has only one storage engine and has a good overall performance. MySQL supports different tables using different storage engines. such as Myisam,innodb (Oracle All), memory. With many different storage engines configured in MySQL, users have the flexibility to choose the storage engine for servers, databases, and tables for maximum performance and maximum flexibility. This is also the main factor that MySQL differs from other large databases and why MySQL is so popular. Take a look at the features of 3 important storage engines in MySQL:

Each storage engine has its own advantages and cannot be generalized to say who has better performance and is not suitable.

(1) MyISAM storage Engine: Primarily for managing non-object tables, it provides high-speed storage and retrieval, as well as full-text search capabilities. The storage engine inserts data quickly, but the use of space and memory is less efficient. If the table is primarily suitable for inserting new records and reading the record, then choosing the MyISAM storage engine can achieve high efficiency of processing, do not support the commit and rollback of things, the whole table will be locked when query, but fast

(2) InnoDB storage Engine: It is primarily used for transactional applications, supports foreign keys, and also supports burst repair capability and concurrency control. If the integrity of things requires a high level of concurrency notification, then choosing the InnoDB storage engine has the advantage. You can also select the storage engine if you need frequent updates, deletions, because the storage engine can implement the commit and rollback of things. (So things are more demanding in real time)

(3) Memory storage Engine: Memories Storage Engine provides an "in-the-box" table in which all the data of the storage engine is stored in memory and the data is processed quickly but with low security.

3. For MySQL, its current use is more common, so its business support and should be better than posgresql, which if business applications can not be ignored.

Historical comparison

The PostgreSQL community is the oldest, largest and fastest-growing community in its class of databases, PostgreSQL originated in the Ingress project of the University of California, Berkeley, in 1985, after years of popularity in academia, the PostgreSQL project opened up and the community began to flourish, 　　Up to now, there are over 1000 contributors and 30,000 members. 　　The key advantage of the greater community is that the interaction between users and developers is more frequent, and users can even directly participate in the design of new features, which is also a model for other open source communities to emulate. MySQL community originally originated from the business world, MySQL was born in 1994 in Sweden, as a high-speed database behind the site, after a few years of MySQL AB control, MySQL also chose open source, with the support of commercial companies so that MySQL quickly became one of the world's widely used database, That's why Sun has spent $1 billion on the acquisition of MySQL AB for 2008 years, because the popularity of MySQL has become a major factor in the European Union's investigation into Oracle's takeover of Sun.

PostgreSQL Features and Features

With decades of development, PostgreSQL is known as the most advanced open source database on the market, and as a fully functional open source relational database management system, PostgreSQL offers many features to support high-transaction, mission-critical applications. 　　PostgreSQL's core competencies are the security of the data it keeps, and by using enterprise authentication mechanisms such as LDAP or Kerberos to control the connection access to the database, a single pass verifies that all communications to the database can take the SSL connection, providing a high level of security protection. When data is added or modified, PostgreSQL enforces a number of user-defined constraints, ensuring that data quality complies with business rules, from simple scope checks to complex foreign key checks, and once the data is stored on disk, backups can be made, and more importantly, disaster recovery is critical, 　　PostgreSQL has a simple online backup tool that works with the point-in-time recovery (PITR) mechanism, providing a great flexibility for administrators to perform quick restores. The core architecture of PostgreSQL allows other community groups to create more advanced functionality for PostgreSQL in the form of add-ons, a good example of PostgreSQL's geospatial support, which comes from a module called PostGIS. 　　It is a simple extension of PostgreSQL, making it the most powerful open source or commercial database for storing spatial data. PostgreSQL Another extension capability is that it has several different types of stored procedure languages that allow developers to write server-side code in the language they are familiar with, for example, a trigger that needs to perform complex text processing can be written in Perl to take advantage of its powerful regular expression capabilities.

MySQL Features and features

MySQL was hailed as the most popular open source database, and from the very beginning, MySQL was designed as a data storage engine for the site's fast sequential indexing method (ISAM), a feature of which was the large number of small queries that spawned the query cache. 　　MySQL cluster, and other technologies to improve MySQL performance, MySQL cluster allows the database to be deployed across multiple physical servers for load balancing. PostgreSQL is not the only open source database that allows you to add database functionality through external extensions, one of MySQL's biggest advantages is its pluggable storage engine, which defaults to the MyISAM storage engine, which provides better performance for environments with frequent read operations. 　　The InnoDB storage engine is more suitable for write-intensive transactional environments. In addition, there are a number of third-party storage engines, such as BrightHouse and DB2, which allow administrators to adjust MySQL instances based on the requirements of each table, for example, a table with frequent read operations, such as a country code table, using the MyISAM storage engine, for transactional tables, such as a sales order table, 　　The InnoDB storage engine is used. PostgreSQL and MySQL are widely used in a variety of applications, for example, PostgreSQL has always been known for its strong support for transactional-intensive enterprise applications, but many Web sites also use it to support Web applications, whereas MySQL has always been the preferred database for Web applications , but it has also been used extensively in the transactional enterprise application system.

Community as King

The biggest difference between PostgreSQL and MySQL may not be in technology, but in the community, where open source projects are essentially involved and contributed by community developers, the project can remain vibrant, and the open source community has two different types. 　　First of all, there is an independent community-based open source database project, PostgreSQL is the oldest, largest independent open source database community, the benefits of this community is truly independent of vendors, not under the control of manufacturers. The second open source community is controlled by the vendor, which often has both community free and commercial paid editions, MySQL is an example, MySQL project was originally funded and controlled by MySQL AB, all MySQL core developers and architects are hired by MySQL AB, After being acquired by Sun, the MySQL community was controlled by Sun and now controlled by Oracle.

Sharing licenses

Licensing, or how the source code allows for modification and sharing, which may be a determining factor in the choice of open source databases, the PostgreSQL license is modelled on the BSD licensing model, which allows code to be modified and released in the form of open source, depending on whether the modified person voluntarily 　　This Open License is ideal for software vendors who want to use PostgreSQL as part of their solution, because PostgreSQL licenses do not force derivative solutions to open source, and software vendors can choose to open their source code or not. MySQL is shared with the GNU GPL and controlled by Oracle, and the GNU GPL is more open, it encourages free sharing of code, it prevents the use of the GNU GPL-shared code, and is forced to use the source author GNU GPL license to share defects.

Summary

Although PostgreSQL and MySQL are both open source databases, the same points may be limited to the same point, there are few overlapping places, they have their own distinctive features, relative to the expensive proprietary database products, they are high-quality open source database, especially in today's economic downturn in the environment, Choosing a good open source database is a good way to save money for your business.

A comparative analysis of PostgreSQL database and MySQL database