PostgreSQL,MySQL,商业数据库,谁更好

数据库服务器在当今的商业应用中无疑是非常重要的设备。通用的商业数据库,如Oracel,微软的SQL Server,IBM的DB2,它们提供许多用户信赖的企业级特性，包括高级数据库存储，数据库管理工具,信息复制，数据备份等.

During the past ten years, the open source community has improved the quality of its software, making it more enterprise worthy. As a result, enterprises have shown an interest in migrating from proprietary, commercial software to open-source software in recent years. For example, businesses around the world commonly use Linux, the Perl programming language, the Apache Web server, and the two leading open-source database engines, PostgreSQL and MySQL.

This article compares PostgreSQL and MySQL, both to each other as well as with their commercial counterparts. Rather than examining the MySQL MAX product based on SAP‘s database engine, it looks at the more widely deployed "original" MySQL.

Questions about MySQL and PostgreSQL often relate to speed. Even though current Postgres releases have gotten much faster, earlier versions were known to be slow. But speed isn‘t everything when it comes to choosing a good database engine. This comparison is based on features rather than speed. If all you need is raw speed, you can get it in other ways.

How It All Began

History of PostgreSQL
The PostgreSQL relational database system (RDBMS) came from the POSTGRES project at the University of California at Berkley. Professor Michael Stonebraker started the project in 1986 to replace the aging Ingres RDBMS, and DARPA, the National Science Foundation, the Army Research Office, and ESL, Inc. sponsored it. While known as the POSTGRES project, the database assumed various roles in different organizations, including an asteroid tracking database, a financial data analysis system, and an educational tool.

POSTGRES originally used a language called PostQUEL for accessing database information. In 1994, Andrew Yu and Jolly Chen added the POSTGRES SQL interpreter, originally known as Postgres95. Postgres95 was then re-licensed under the Berkley software license and shortly thereafter was renamed PostgreSQL.

Brief History of MySQL
Prior to creating MySQL, the people that wrote it used mSQL to connect to their own low-level data structure. They discovered that mSQL lacked the features and speed they wanted and decided to write their own front end instead. Thus began the life of MySQL as the product.

The Ins and Outs of Licensing
Both MySQL and PostgreSQL have different licenses, and understanding how they work is important when incorporating these products into enterprise projects. Different licenses fulfill different needs, and they have different requirements.

MySQL AB, the company that owns and produces MySQL, has two licenses available for its database product:

1. GNU General Public License (GPL) for GPL projects. If your project is 100 percent GPL in its distribution, you can use this license. To fully comply, you must distribute your application, along with the source code. You also can use this license if you don‘t intend to ever distribute your project internally or externally.
2. Commercial License for commercial applications. An example of the use for this license is when you don‘t want to distribute the source code for your application. This includes database drivers as well. You can‘t use the MySQL database drivers with a commercial application unless it‘s either distributed under the GPL license or you have a Commercial License.

PostgreSQL has a much simpler licensing scheme. It is released under the Berkley License, which allows for any use as long as a copy of the Berkley License is included with it. This means that you can release a commercial product that uses PostgreSQL or is a derivative of PostgreSQL without including source code.

The Features You‘ve Come to Expect
The database comparison boils down to the features that each database engine provides (see Table 1). Database administrators that have worked with commercial database engines such as Oracle, DB2, or MS-SQL have come to rely on a fairly broad feature set. This section compares these commercial databases with the open-source databases.

Data Storage
MySQL has several different data storage mechanisms available. It originally used ISAM/MyISAM, which was then replaced by the more advanced InnoDB. Other storage mechanisms are available, but this discussion focuses primarily on using InnoDB tables because it typically has the most advanced database feature set and is the default table type in MySQL version 4.x. When choosing a MySQL storage mechanism, make sure you read up on all of the features you plan on implementing. While researching this article, I found that some features exist in certain storage mechanisms, but not in others. Most notably, InnoDB and BDB are the only table types that are transaction-safe. PostgreSQL, on the other hand, uses only one data storage mechanism, the aptly named Postgres storage system.

Data Integrity
One of the critical features of any database engine is data integrity. ACID (Atomic, Consistent, Isolated, Durable) compliance is a qualification that assures data integrity. ACID essentially means that when a transaction is performed within a database, either the whole transaction is successful and the information is written to the database, or nothing is written. Both PostgreSQL and MySQL support ACID-compliant transaction functionality.

Both databases also support partial rollbacks of transactions, and they know how to deal with deadlocks. MySQL uses traditional row-level locking. PostgreSQL uses something called Multi Version Concurrency Control (MVCC) by default. MVCC is a little different from row-level locking in that transactions on the database are performed on a snapshot of the data and then serialized. New versions of PostgreSQL support standard row-level locking as an option, but MVCC is the preferred method.

The Advanced Features
PostgreSQL has many of the database features that Oracle, DB2, or MS-SQL has, including triggers, views, inheritance, sequences, stored procedures, cursors, and user-defined data types. MySQL‘s development version, version 5.0, supports views, stored procedures, and cursors. MySQL‘s future version, version 5.1, will support triggers. MySQL does, however, support the advanced feature of data partitioning within a database. PostgreSQL does not.

Stored Procedures and Triggers
While PostgreSQL has had support for stored procedures and triggers for quite some time now, MySQL has support for these only in development versions 5.0 and beyond. PostgreSQL‘s query language, PL/pgSQL, is very similar to Oracle‘s PL/SQL. In addition, PostgreSQL‘s procedures and triggers can be written in other languages as well, such as PL/TCL, PL/perl, and PL/python. These additional languages come in two basic flavors, safe and unsafe. Safe allows only for use of things in the programming language that don‘t affect the host system negatively, such as direct access to the file system.

Indexes
Oracle is known for the amount of tweaking it allows for databases, especially when it comes to indexing. Overall, experienced Oracle users will probably find the indexing strategies employed by these open-source databases quite primitive. Both PostgreSQL and MySQL support single column, multi-column, unique, and primary key indexes. MySQL supports full text indexes out of the box, and PostgreSQL can support full text indexes with some changes to the database that are included with the source.

Data Types
Databases hold data, and the types of data that a database can hold are called data types. Both PostgreSQL and MySQL support most standard data types. In the past few years, large object support has become increasingly popular, and both databases support this as well. PostgreSQL supports user-defined data types, while MySQL does not. MySQL and PostgreSQL also both support the storing of geographic features, known as GIS (Geographic Information System). PostgreSQL additionally has network-aware data types that recognize Ipv4 and Ipv6 data types.

Replication
Another major feature of enterprise-level databases is support for replication. Both MySQL and PostgreSQL have support for single-master, multi-slave replication scenarios. This base level of replication is included with the distributions of the software, and the source code is open. PostgreSQL offers additional support for multi-master, multi-slave replication from a third-party vendor, as well as additional replication methods.

Platform Support
While both Oracle and DB2 run on multiple platforms, Microsoft‘s SQL Server is limited to Windows. Both MySQL and PostgreSQL support many different platforms, including Windows, Linux, FreeBSD, and MacOSX. MySQL uses a threaded model for server processes, wherein all of the users connect to a single database daemon for access. PostgreSQL uses a non-threaded model where every new connection to the database gets a new database process.

Database Interface Methods
PostgreSQL and MySQL both support ODBC and JDBC for network connectivity, as well as native database access methods. These native methods provide access via the network in both plain text methods and, for a higher level of security, SSL-encrypted methods.

Another important part of database interface methods is authentication for the database. MySQL uses a simple method to store all of its authentication information inside a table. When users attempt to access a database, MySQL compares their credentials against this database, verifying from which machines the users can connect and to what resources they have access.

PostgreSQL can use a similar method, but it also has some others. For example, it can use a hosts file for database access to define which remote users can connect to which database. It can also use the local authentication systems for database access (e.g., your Unix password would also be your PostgreSQL password).

A number of programming methods also provide ways to access these databases. Both PostgreSQL and MySQL support access via C/C++, Java, Perl, Python, and PHP. PostgresSQL also has internal programming languages for writing stored procedures and triggers, among them are pl/pgsql, pl/tcl, and pl/perl.

Backups
When it comes to backups, open-source databases may not completely fulfill your needs. Both databases come with scripts to facilitate a simple text dump of your database data and its schema. Both database solutions also provide methods for doing a hot-database backup, or backing up your database without shutting it down. Many commercial backup tools, such as Vertias NetBackup or Tivoli TSM, have agents that provide online backups of commercial databases. A quick Web search returned only a few vendors that create agents for PostgreSQL and MySQL. The overall coverage appears limited.

Backups also include simple database recovery from soft failures, such as database crashes or unexpected power failures. PostgreSQL uses a system called Write Ahead Logging to provide database consistency checking. MySQL has database consistency checking only under InnoDB table types.

GUI Tools
Many people use GUI tools to manage their databases. Many such tools—both open source and commercial—are available for MySQL and PostgreSQL. These tools can be either applications that run natively on your operating system or Web-based tools. Many of these tools are closely modeled after tools available to commercial databases.

Data Migration
Both MySQL and PostgreSQL have database migration utilities to help migrate data from commercial databases. These utilities are available from third parties as either open-source or commercial tools. PostgreSQL also comes with tools to help migrate data from Oracle and MySQL. Obviously, the more complex your schema, the more difficult the conversion will be, and some of these tools may not completely migrate everything perfectly.

 

Training and Support
The issue of support has mitigated acceptance for open-source software in the enterprise. Many do not realize that support is available for many open-source products—beyond Web sites and mailing lists. MySQL AB provides support for MySQL, and several companies, including Command Prompt, Inc. and PostgreSQL, Inc., provide support for PostgreSQL. These offerings include support levels that rival commercial databases, many providing 365x24 support.

Training is also available on a wide variety of topics for both PostgreSQL and MySQL. MySQL AB provides training in cities around the world, with topics ranging from administration to writing Web-based applications using MySQL. PostgreSQL training is also available from dbExperts and Big Nerd Ranch.

Who Else Uses Them?
A number of large companies use both open-source databases in various ways. Both database engines have somewhat large database installations in use. I use the word somewhat because data storage is a relative term. Oracle and DB2 can scale to terabytes of data storage fairly easily. MySQL and PostgreSQL are known to run well into the hundreds of gigabytes, but few companies use the databases above that range.

Cox Communications uses MySQL to manage information related to its cable modem business. NASA uses MySQL to store information about public contracts. Slashdot, a widely read online publication, uses MySQL to store all of the information related to its site. The Associated Press uses MySQL to serve various types of information, including access to the U.S. Census and Olympic results.

You probably use PostgreSQL indirectly on a fairly regular basis. Afilias, which manages the .ORG registration, uses PostgreSQL to store all of .ORG registry information. The American Chemical Society uses PostgreSQL to store documents that exist only within that database. BASF uses PostgreSQL in a shopping platform for its agriculture products. The World, a media company, has built much of its infrastructure around the use of PostgreSQL.

If It Ain‘t Broke, Don‘t Extend it
An axiom of open-source software is that developers write it to "scratch an itch." This is a good practice when the itch is something like a failing disk, which compels the developer to improve the tools that work on the disk. However, in regards to databases, the itch flares up only when data surpasses certain limits, such as size or complexity. PostgreSQL and MySQL boast widespread use for relatively small databases (under 100GB, for example). Once the data grows larger than 100GB or so, the number of users drops off drastically. At that point, working through large-database-related issues becomes more of a problem.

The itch axiom also applies when working in some of the more "buzzword-complaint" areas. Some of the more advanced features in the open-source databases (such as replication) are nowhere near what you‘d find on commercial alternatives. Quite simply, most users don‘t need replication at the levels that Oracle, DB2, or MS-SQL offer; therefore, PostgreSQL and MySQL developers don‘t get the itch to improve it.

The great thing about open source software, though, is that it‘s pretty easy to try out and has lots of freely available online documentation to help you learn the products. While these databases may not be optimal for every project, they work very well for others. If you‘re curious—and this article hasn‘t answered your particular usage questions, take MySQL or PostgreSQL out for a spin and see if they meet your needs.

 

I’m often asked, “Do you prefer PostgreSQL or MySQL?” My answer is always the same: “It’s a matter of preference.” You could ask many developers the same question, and their responses will all be different. Here is a comparison of MySQL and PostgreSQL databases, offered not for the sake of voicing my opinion, but to help you make your own decision.

Both systems have much to offer in terms of stability, flexibility, and performance. MySQL has features that PostgreSQL lacks, and vice versa. However, my primary focus is to help you determine which of the two databases to use in your own development.

Before getting into the side-by-side comparison, I need to make it clear I’m referring to default installations. MySQL has many different table types that support transactions and foreign keys and are compliant with ACID. However, some of the configurations of these table types are quite complex. Not many Web developers or programmers use the additional table types found in MySQL. With that said, let’s compare these two products.

Feature list
Table A is a side-by-side comparison of some of the more frequently used features of MySQL and PostgreSQL.

Table A is not an exhaustive list of features, data types, or performance issues relating to these two database systems—it just gives a view of what each has to offer. From the table we see that PostgreSQL offers overall features for traditional database applications, while MySQL focuses on faster performance for Web-based applications. Open source development will bring more features to subsequent releases of both databases.
Table A

POSTGRESQL MYSQL ANSI SQL compliance Closer to ANSI SQL standard Follows some of the ANSI SQL standards Performance Slower Faster Sub-selects Yes No Transactions Yes Yes, however InnoDB table type must be used Database replication Yes Yes Foreign key support Yes No Views Yes No Stored procedures Yes No Triggers Yes No Unions Yes No Full joins Yes No Constraints Yes No Windows support Yes Yes Vacuum (cleanup) Yes No ODBC Yes Yes JDBC Yes Yes Different table types No Yes

MySQL and PostgreSQL comparison

When to use MySQL
Why would you use MySQL over PostgreSQL? First, we need to consider the needs of the applications in terms of database requirements. If I want to create a Web application and performance is an issue, MySQL will be my choice because it’s fast and designed to work well with Web-based servers. However, if I want to create another application that demands transactions and foreign key references, PostgreSQL is the choice.

As an open source developer, I work with both databases on a regular basis, and I typically use all the features of a given database in the design and development process. It wouldn’t suffice for me to use PostgreSQL for a database-driven Web site when my application requires performance.

Even though MySQL is not fully compliant with the ANSI SQL standard, I should mention that, while PostgreSQL is closer to the ANSI SQL standard, MySQL is closer to the ODBC standard.

Let me suggest some reasons for using MySQL over PostgreSQL:

• MySQL is relatively faster than PostgreSQL.
• Database design will be simpler.
• You can create a basic Web-driven Web site.
• MySQL’s replication has been thoroughly tested.
• There’s no need for cleanups in MySQL (Vacuum).

 

When to use PostgreSQL
Not many Web developers use PostgreSQL because they feel that the additional features degrade performance. The article "Many Web developers prefer MySQL" offers a glimpse into the mentality of Web developers and their reasons for using MySQL. However, PostgreSQL offers many advantages over MySQL.

For example, some of the features I use are foreign key references, triggers, and views. They allow me to hide the complexity of the database from the application, thus avoiding the creation of complicated SQL commands. I know many developers who prefer the rich functionality of PostgreSQL’s SQL commands. One of the most notable differences between MySQL and PostgreSQL is the fact that you can’t do nested subqueries of subselects in MySQL. PostgreSQL follows many of the SQL ANSI standards, thus allowing the creation of complex SQL commands.

Let me suggest some reasons for using PostgreSQL over MySQL:

• Complex database design
• Moving away from Oracle, Sybase, or MSSQL
• Complex rule sets (i.e., business rules)
• Use of procedural languages on the server
• Transactions
• Use of stored procedures
• Use of geographical data
• R-Trees (i.e., used on indexes)

When to use both
You’ll have to choose which database is “perfect” for your application or Web site. And you may want to use both. I recently worked for the Quality Assurance Department for Enterasys Networks. One of my responsibilities was to create a database-driven intranet site as a repository for all the QA testing done at our facility. From the outset, I was convinced that PostgreSQL was the only database to use. I was wrong. I ended up using both databases for the intranet site. I used MySQL to handle the entire Web front-end and PostgreSQL to keep track of all the testing metrics that we stored. I found that MySQL and PostgreSQL helped in their own ways. My conclusion is that one isn‘t necessarily better than the other, but that each one has its place in the world of open source databases.

Don‘t hope one tool fits all your jobs.

To choose between the two databases, you need to understand whether you need the transaction
support of Postgres or the large-text-area support in MySQL.

It‘s interesting that the two databases appear to be converging to meet in the middle somewhere.
While MySQL is working on adding transaction support and slowly adding features like subselects,
Postgres is making progress in the performance and stability.

Having foreign keys, views, subselects, and transactions can all be very attractive in PostgreSql -
if you need them and you will make any use of them. If you don‘t need them or won‘t use them, then
you‘re probably better off with MySQL and its superior performance.


PostgreSQL versus MySQL: an unbiased comparison

	PostgreSQL	MySQL
SQL standard COMPLIANCE	Postgres understands a good subset of SQL92/99 plus some object-oriented features to these subsets. Postgres is capable of handling complex routines and rules as declarative SQL queries, subqueries, views, multi-user support, transactions, query optimization, inheritance, and arrays. Does not support selecting data across different databases.	MySQL uses SQL92 as its foundation. Runs on countless platforms. Mysql can construct queries that can join tables from different databases. Supports both left and right outer joins using both ANSI and ODBC syntax. As of MySQL 4.1 from that release on, MySQL will handle subqueries. Views supported as of release 5.
PLATFORMS	Lacks binary distribution for all the supported plataforms. One of the problems is that PostgreSQL doesn‘t run properly on NT as a service by default, you need something like firedaemon to start it. The PgAccess GUI is available on windows as well, but it lacks a few features that psql supports. Non-supported platforms: Windows9x/Me, NextStep, Ultrix.	There are binary distribution for most of the supported plataforms. MySQL works better on Windows than PostgreSQL does. MySQL runs as a native Windows application (a service on NT/Win2000/WinXP), while PostgreSQL is run under the cygwin emulation.
SPEED	Postgres slower on low-end but has some options for improving. Postgres forks on every incoming connection - and the forking process and backend setup is a bit slow, but one can speed up PostgreSQL by coding things as stored procedures.	MySQL is very fast on both simple and complex SELECTs, but might require changing the database type from MyISAM to InnoDB for UPDATE intense applications. MySQL handles connections very fast, thus making it suitable to use MySQL for Web - if you have hundreds of CGIs connecting/disconnecting all the time you‘d like to avoid long startup procedures.
STABILITY	PostgreSQL 6.x series and earlier were much worse in this aspect. Random disconnects, core dumps and memory leaks are usual. PostgreSQL 7.x series was a big improvement. Expect PostgreSQL 8.x to continue this trend.	MySQL does very good job even on the busiest sites; it certainly has some problems handling hundreds of connections per second, but these problems are resolvable. Random disconnects and core dumps are exceptionally rare. MysSQL has a much larger user base than PostgreSQL, therefore the code is more tested and has historically been more stable than PostgreSQL and more used in production environments.
DATA INTEGRITY	Postgres does very good job supporting referential integrity, has transactions and rollbacks, foreign keys ON DELETE CASCADE and ON UPDATE CASCADE.	Mysql has some basic provisions for referential integrity and transactions/rollbacks. CHECK clause is allowed for compatibility only and has no effect on database operation. InnoDB tables have FOREIGN KEYs for relational or multi-table delete, and support transaction processing. In MySAM tables FOREIGN KEY is for compatibility only and has no effect on database operation.
SPECIAL server-side FEATURES	Postgres has rules, triggers, server-side functions that can be written in C, pgsql, python, perl and tcl languages. INSTEAD OF rules can be used for updating data through views. PostgreSQL has schemas that allow users to create objects in separate namespaces, so two people or applications can have tables with the same name. There is also a public schema for shared tables. Table/index creation can be restricted by removing permissions on the public schema.	MySQL has simple (and probably inconvenient) mechanism for server-side shared libraries with C functions. Rudimentary support for triggers was included beginning with MySQL 5.0.2. An external development implemented in perl can be used as stored procedures in Mysql. MySql has more powerful admin tools included in the distribution (mysqladmin allows you to watch processes and queries in-progress), including hot backup, a file corruption recovery tool and a of couple others. Command-line tools - you can see database and table structures using describe and show commands. Postgres‘ commands are less obvious ( \d to show a list of tables for instance).
SECURITY	Postgres has similar features, but a little less fine-grained. For example, if user can connect to a database, user can CREATE TABLE, thus running Denial-of-Service. On the other hand Postgres can limit logins based on different criteria - network segment, ident string, etc.	MySQL has exceptionally good fine-grained access control. You can GRANT and REVOKE whatever rights you want, based on user name, table name and client host name.
LOCKING and CONCURRENCY SUPPORT	PostgreSQL has a mechanism called MVCC (MultiVersion Concurrency Control), comparable or superior to best commercial databases. It can do row-level locking, can lock rows for writing in one session but give these rows unaffected in another session. MVCC is considered better than row-level locking because a reader is never blocked by writer. Instead, Postgres keeps track of all transactions and is able to manage the records without waiting to become available.	MySQL can do table locking for ISAM/MyISAM and HEAP tables, page level locking for BDB tables. InnoDB has full row level locking support.
LARGE OBJECTS	In Postgres, Large Objects are very special beasties. You need to create them using lo_create function and store the result of the function - OID - in a regular table. Later you can manipulate the LOB using the OID and other functions - lo_read/lo_write, etc. Large object support is broken in Postgres - pg_dump cannot dump LOBs; you need to develop your own backup mechanism. Tthe team is working on implementing large rows; this will replace current LOB support.	In MySQL, text and binary LOBs are just fields in the table. Nothing special - just INSERT, UPDATE, SELECT and DELETE it the way you like. There are some limitations on indexing and applying functions to these fields.
ALTER TABLE	Postgres supports ALTER TABLE to some extent. You can ADD COLUMN, RENAME COLUMN and RENAME TABLE.	MySQL has all options in ALTER TABLE - you can ADD column, DROP it, RENAME or CHANGE its type on the fly - very good feature for busy servers, when you don‘t want to lock the entire database to dump it, change definition and reload it back.
NATIONAL LANGUAGE SUPPORT	Postgres compiled with --enable-locale does some jobs based on its locale settings, and can change locale settings per client (not per database), which is a bit more flexible. Compiled --with-mb (Multibyte support) Postgres can translate on-the-fly between many predefined character sets.	MySQL does some tasks based on its locale settings, but not many.
	Date/time formats parsing/generating need more work for both sides. Neither system can handle multicharset databases.

Oracle 10g vs PostgreSQL 8 vs MySQL 5

This is my comparison of installing and getting started with Oracle 10g, PostgreSQL 8 and MySQL 5. This is what I consider the comparison of state of the art for three categories of DB: Commercial vs. Academe vs. Internet Model. This is a comparison from the view of a new user wanting to install a database to learn. I‘m trying to keep the viewpoint of a home or small business user.

Full Disclosure: I am strongly biased towards Oracle and fully expected no real competition.

License: I will not get into a debate over open source and closed source. Oracle is a commercial database and requires licensing to use. MySQL and PostgreSQL are both open source projects. Oracle provides a free developers license for you to "test out" and create prototypes.

Configuration: I am installing on an older machine with 256 Megs of Ram and a 40 Gig hard drive. The CPU is a 633 Mhz Celeron. The OS is Windows 2000 Professional. I want to install at the low end of the spectrum to gauge the ability for home user-type installations.

OS: I chose to perform this comparison under Windows, as I believe that that is still the most common platform that new people to the world of databases will use. By that, I mean people installing at home. For new corporate users, the odds are good that they will not have a choice of either OS or database so a comparison is pointless.

Versions:

PostgreSQL 8.0 - PostgreSQL 8.0 is a very new product. I chose 8.0 as it is the latest version and it natively supports Windows. This is the first version that actually does support Windows without an emulator or third-party recompilation.

MySQL - MySQL 5.0 is also a very new product. As a matter of a fact, the version I used, v5.0.4, is actually a beta version. I argued with myself about whether I should use the stable v4 or the beta v5. I chose v5 because v4 does not compete with PostgreSQL or Oracle. V4 does not have stored procedures, triggers, views, etc. Without those features, I would not consider it as a contender. The upside to that is that v5 does support those features.

Oracle 10g - This is the latest version of Oracle and was released last year. In my opinion, Oracle 10g is the gold standard of databases. Nevertheless, I also recognize that it is very expensive and requires quite a bit of knowledge to support in a production environment.