PostgreSQL Vs MySQL: Comparative Review

By Partho, Gaea News Network
Monday, August 31, 2009

postgresql-vs-mysqlMost of the businesses these days use database server. Although commercial databases like Oracle, Microsoft’s SQL Server, and IBM’s DB2 server are widely used, but the open source databases are fast gaining popularity, especial with the small and mid size businesses.
For decade now open source community has improved upon its quality of software to be more enterprise worthy. This has led to a gradual move from the proprietary, commercial software to open-source software in recent years. A large number of businesses around the world use Linux, the Perl programing language and open source database servers - PostgreSQL and MySQL.

Although the two open source RDBMS seem to converge somewhere, there exist a fundamental asymmetry between the database servers. We would delve into the major differences between PostgreSQL and MySQL that sets them apart.

Our original article had some discrepancies that we have updated. We dealt with comparative review between the open source databases - PostgreSQL and MySQL were not entirely based on the updated version.We would like to place a rejoinder updating the issue for all our readers.

Both the database servers are testing PostgreSQL 8.4 and MySQL 6.0 at this time to offer incremental improvements. There are no fundamental difference in the new version that would defy the general guideline or invalidate them.

Architecture

PostgreSQL is a unified database server with a single storage engine. MySQL has two layers, an upper SQL layer and a set of storage engines.

Speed

MySQL: The recently released MySQL 5.1 incorporates new features that speeds up the processes. The new service enables developers to automatically schedule common SQL- based tasks that are executed on the database server, reduces time-spent. Additionally, there’s a new MySQL Query Analyzer tool for the premium MySQL Enterprise subscribers in the new version that can dramatically enhance the speed and uptime of MySQL database application.

PostgreSQL: PostgreSQL 8.1 and 8.2 are both supported versions with more or less decent performance. The Postgres 8.3 claims to be moderately faster, around  30% so on some workloads. PostgreSQL 8.3 has added a number of new features to speed up specific operation.

Faster Recovery time: The amount of I/O from the Write Ahead Log at recovery time is reduced to half

Circular buffer in tuplestore: It speeds up small merge joins without spilling to disk

Lazy XID Assignment: This enables PostgreSQL to avoid assigning transaction IDs for some read-only queries. This results in faster throughput on read-mostly or read-only databases.

Function Costing: It informs the query planner of estimated function execution costs and rows returned, which enables better query plans.

Server-side features

MySQL: Each MySQL program takes many different options. All these programs are supported by help option that assists the user to get a description of the program’s different options. For more you can Look for server-side help.

In MySQL 5.1 Connector/J 3.0 has upgraded to 3.1. It includes a new feature Server-side Prepared Statements. Connector/J 3.1 has been designed to automatically detect and use server-side prepared statements when they are available.

PostgreSQl: These two functions read and write files in the server’s file system. It has server-side functions, which are callable from SQL that correspond to each of the client-side functions. Most of the client-side functions are simply interfaces to the equivalent server-side functions. The server-side lo_import and lo_export functions behave considerably differently from their client-side analogs.

Stability

With increasing commoditisation of databases, the issue of stability is not the core concern. Moreover it’s no more an issue of stable software rather relying on hardware. Moreover, stability also rests on the needs and features that the user is looking for.

MySQL: It would be more stable where replication is used since this feature has long been implemented in the database.

PostgreSQL: It has been equipped with advanced set of features, such as stored procedures and subqueries. These have been implemented to offer a more stability.

Advanced Indexing

When comparing the indexing functions of both the open source database servers we picked one of the most prominent difference

Partial Indexes

MySQL: It offers no support for partial indexes

PostgreSQL: It supports partial indexes. The partial index is an index built over a subset of a table. The index include the entries only for those table rows that satisfy the predicate. Partial indexes are specialized feature that can be used in several occasions. It is mostly used to avoid indexing common values. A query searching for a common value will not use the index anyway. There is no point in keeping those rows in the index at all. This reduces the size of the index that will speed up queries that do use the index. In the process it would speed up many table update operations.

An Extended overview

Our views on MySQL might seem biased, but the bottomline is MySQL still rules the roost. It widely used in various open-source web development packages. Especially, the MyISAM engine is the most popular engine used in web development.

However, MySQL’s claim of being world’s most popular open source database seems more of a sham than reality as there’s a fair dominance of other DBMS’s such as SQLite.

MySQL’s popularity can be given to the common perception that it is easier to use than most of the other databases, especially PostgreSQL. Although, MySQL’s monopoly has drastically reduced over the years, however this has hardly their reputation of being a comparatively easier to use RDBMS.

If you are one of those using recently released MySQL 5.1 GA, there’s much to scrutinize about the bugs that Michael Widenius, founder and original developer of MySQL discusses in infosecurity.us.

——————————————————————————————————————————-

——————————————————————————————————————————

This was our original article

SQL standard compliance

MySQL: MySQL foundation is based on SQL92. It runs on almost any platform. In MySQL users can construct queries that can be join tables from different databases. It supports both left and right outer joins using ANSI and ODBC syntax. With the MySQL 4.1 release, it can also handle the sub-queries now.

PostgreSQL: Understands a good subset of SQL92/99 plus some objects-oriented feature to these subsets.

Platforms

MySQL: It offers binary distribution for most of the supported platforms. MySQL works better on Windows  than PostgreSQL.

MySQL runs as a native Windows application (a service on NT/Win2000/WinXP) where as PostgreSQL runs under cygwin emulation

PostgreSQL: It doesn’t have binary distribution for all the support platforms. One of significant drawback with PostgreSQL is that it doesn’t run smoothly on NT as a service by default. It requires firedaemon to start working on it.

Although the PgAccess GUI is available on windows, but there’s psql support for only a few features. It doesn’t support platforms like Windows9x/Me, NextStep, Ultrix.

Stability

MySQL: Even for the high traffic sites MySQL works smoothly. It might have some problems handling hundreds of connections per second, but this is resolvable.

Unlike PostgreSQL, it rarely suffers from random disconnects or core dumps.
Since MySQL has a much larger user base than PostgreSQL, the code is more tested and presumably more stable then PostgreSQL. The code is has been widely used in production environment.

PostgreSQL: The PostgreSQL 6.x series and those released before it suffered from stability issue.  Random disconnects, core dumps and memory leaks were common with these series. However, the PostgreSQL 7.x series showed significant improvement in terms of stability.

Speed

MySQL: It is quite efficient with both simple and complex SELECT queries, but this might require you to change the database type from MyISAM to InnoDB for UPDATE intense application.

PostgreSQL: It is quite slow on low-end but has scopes for improvement. Actually Postgres forks on every incoming connection. The forking process and backend setup is somewhat sluggish. However, PostgreSQL can be speed up by coding things as stored procedures.

Special server-side features

MySQL: It has simple mechanism for server-side libraries with C function. MySQL 5.0.2 offered rudimentary support for triggers.

Store procedure in MySQL can store external development in Perl. MySQL offers more powerful admin tools  in the distribution including hot backup, file corruption recovery tool and some others facilities.

Command-line tools include the database and tablet structures using describe and show commands.

PostgreSQL: The 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. This will allow two people or applications having tables with the same name. Further, it also has a public schema for shared tables.

Security

MySQL: The access control is finely tuned. The user can GRANT and REVOKE whatever rights he wants after providing user name, table name and client host name.

PostgresSQL: It’s security features are similar to MySQL but less fine-tuned. However, Postgres can limit logins based on different criteria - network segment, ident string, etc.

Locking and currency support:

MySQL: It can do table locking for ISAM/MyISAM and HEAP tables, page level locking for BDB tables. The InnoDB in MySQL ensures full row level locking support

PostgreSQL: It features a MultiVersion Concurrency Control(MVCC) that is comparable or superior to best commercial databases. PostgreSQL does row-level locking and can lock rows for writing in one session and keep them unaffected in another session. Postgres can keep track of all the transactions and can manage the records.

After Table

MySQL: It offers extensive options for ALTER TABLE that you can ADD column, DROP it, RENAME or CHANGE its type on the fly. It’s an excellent feature for busy servers if the user doesn’t want to lock the entire database to change definition, reload it or dump it.

PostgreSQL: Postgres supports ALTER TABLE but not as extensively as MySQL. It can only ADD COLUMN, RENAME COLUMN and RENAME TABLE.

Large Objects

MySQL: Text and binary LOBs are just fields in the table. It’s easy to INSERT, UPDATE, SELECT and DELETE it. There are limitations to indexing and applying functions to these fields.

PostgreSQL: In this Large objects are created through lo_create function - OID - in regular table. Later on you can manipulate the LOB using the OID and other functions. In Postgres - pg_dump cannot dump LOBs as the large object support is broken. So the users have to develop their own backup mechanism.

Language Support

MySQL: It can be used perform certain tasks based on local setting.

PostgreSQL: The locals do some job based on the local setting and can change locale settings per client.

Licensing

MySQL

MySQL is produced by MySQL AB that has two license for its database product

GNU General Public License (GPL): This license is for 100% GPL projects. To comply with the norms, the project needs to be distributed along with the source code.

Commercial License:
This is for commercial applications when the project doesn’t involve distributing the source code.

PostgreSQL

It offers a much more simplified licensing scheme. Released under Berkley License, it cam be used as long as the copy of Berkley License is included with it. This implies that it can be released as a commercial product that uses PostgreSQL or is a derivative of PostgreSQL without including source code.

Conclusion

No doubt MySQL is the epitome of open source database servers and sets an ambitious schedule for designing its high price-performance databases up to enterprise standards. It scores high in runtime performance, availability of third party applications and tools that can attract most frontline developers. MySQL is ahead of PostgreSQL in most respects except certain features like transaction support. However, the advent of version PostgreSQL 8.x shows that it’s going though a process of continuous improvement. In terms of support for external packages (such as client-end programming systems) it’s now competing with both the commercial offerings and the other open source products. MySQL is working on adding transaction support and including features like subselects, Postgres is making progress in the performance and stability.

For those of you deciding to go for an open source database server the comparative review would serve as a guide. Lastly, we must to thank www-css.fnal.gov for their efforts to highlight the highs and lows in the two open source database servers.

Discussion

Lilian
April 6, 2010: 3:54 am

Wroten by an MySQL fanboy. Lol, so much mistakes…


peter
March 24, 2010: 4:21 pm

basically content-free


Mark
March 24, 2010: 3:36 am

Is this piece of crap still online? Why would someone make a fool of himself by so many mistakes? Both articles are death wrong, both on MySQL and PostgreSQL. Sounds like the author has never read any manual nor worked with any of these databases.

Advice: Ignore this “article”, it’s just a collection of bullshit.


R-GAY
March 16, 2010: 1:34 pm

HAHAHA THIS IS A SUCH MASTERPIECE OF SCIENTIFIC WORK ;-) THIS ONE BELOW IS EXCELLENT GENIUSSSSS. MUAHAHAHAHA

“With increasing commoditisation of databases, the issue of stability is not the core concern. Moreover it’s no more an issue of stable software rather relying on hardware. Moreover, stability also rests on the needs and features that the user is looking for.”


Hen
October 2, 2009: 6:58 am

“It has been equipped with advanced set of features, such as stored procedures and subqueries. These have been implemented to offer a more stability.”

Stored procedures and subqueries to offer more stability ? What are you talking about ?


Thom Brown
September 23, 2009: 7:34 am

What a terrible misinformed article! It just sounds like MySQL propaganda to make it sound superior to PostgreSQL. Any PostgreSQL user who reads this will just be gob-smacked at the number of glaring errors and omissions. Any area where it does kind of say PostgreSQL is better it says it in a vague hand-waving kind of way as if it’s not really anything worth noting.


Huberta
September 2, 2009: 3:53 am

Do you have a name ?


John
September 2, 2009: 12:03 am

If you don’t know how PostgreSQL works, how to use the manual or just don’t like it, just say so. You don’t have to write an article about it and say MySQL is better. The new aticle is just a copy-paste from other sources, it’s not your work. Or did you really discover the ins and outs of PostgreSQL in just a day? If so, that would make PostgreSQL the most easy to use database in the world.

You don’t know what you’re talking about, sad but true.


Mark Kirkwood
September 1, 2009: 7:26 pm

Oh dear - even the Mysql information is misleading at best, painting an overly rosy picture of everything getting better. Whereas consider:

http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html

Other studies show massive scaling issues with 5.1, and while it looks like 5.4 is taming these, this article seems blissfully unaware of the whole business.


David F. Skoll
September 1, 2009: 7:13 pm

What a lame, lame, lame article. What do stored procedures have to do with “stability”? If I had written this article, I’d be hanging my head in shame.

September 1, 2009: 2:22 pm

Thanks for updating the blog article and accepting our comments. However, IMHO it is still a “from-MySQL perspective” article.
Thanks


Dany
September 1, 2009: 1:28 pm

Funny :) Nobody gonna to believe that MySQL is such a good database, or you are not really demanding or you don’t know what are the requirement for a database in the real life.


Peter
September 1, 2009: 10:26 am

> specially, the MyISAM engine is the most
> popular engine used in web development

That might be true, but database that does not support transactions nor a referential or check constraints should not be used for any mission critical application.

MySQL sacrifices data integrity for speed!

And the GPL license makes MySQL’s use very limited especially when developing projects for customers.

The only area where I see MySQL ahead of Postgres is replication (but then Monty himself agreed that replication is broken in 5.1…)


John
September 1, 2009: 10:02 am

Who wrote this piece of crap? This article is just a joke! Who’s using pgSQL version 6? Running Windows NT or Windows 2000? And cygwin, why would anybody use it for PostgreSQL?

Do yourself a favor and delete this article. You’re making a fool of yourself.


Ewald G.
September 1, 2009: 9:05 am

It’s a shame that such an article ist allowed to be posted as comparison.


Jeff Davis
September 1, 2009: 1:44 am

This article is full of misinformation, misleading information, and ridiculously out-of-date information. I politely suggest that the author retract the entire article.

If the author (or readers) would like better information, please see documentation at http://www.postgresql.org or visit the #postgresql IRC channel or join a mailing list.

I have no idea why releases of PostgreSQL from a 1999 are being discussed ten years later in 2009.


Gene
August 31, 2009: 11:54 pm

This is the sadest excuse for a comparison I’ve ever read.


Andrew Dunstan
August 31, 2009: 10:49 pm

This piece demonstrates appalling ignorance and carelessness. PostgreSQL has run native on Windows for *FIVE YEARS*. The laziness and lack of proper research in this article is astounding. You owe your readers and the PostgreSQL community an apology and retraction.

August 31, 2009: 10:34 pm

Are you from another century?
Did you ever enter the postgresql site?
Were you typing randomly?


Frank
August 31, 2009: 10:22 pm

>> The PostgreSQL 6.x series and those released before it suffered from stability issue.

That was over a decade ago… What’s the point? Did you even look into the documentation about PostgreSQL? Did you even test it? What about these tests:
http://tweakers.net/reviews/674/7/database-test-8-way-opteron-solaris-vs-punt-linux.html

PostgreSQL running circles around MySQL and the testapplication was build and optimized for MySQL. PostgreSQL version 6 was slow, true, but that was a decade ago. It’s 2009, wake up.


arnold
August 31, 2009: 10:21 pm

Pathetic… Wroten by MySQL fanboy.


Richard Broersma Jr.
August 31, 2009: 9:13 pm

The information presented in this blog is at least ten years old.


Joe
August 31, 2009: 7:46 pm

This is a bizarre review. Postgresql 6.x? Really? To consider anything except the 8 series (out for 5+ YEARS) is a completely biased comparison.

In fact, almost every single comparison is flawed. These are outright false statements about PostgreSQL.

August 31, 2009: 6:44 pm

It is good joke.


Hans
August 31, 2009: 6:18 pm

This is complete and utter nonsense. You should have at least have a quick look into the manual before spreading simply wrong information about Postgres.

Postgres 6.x and 7.x have long been discontinued. If you are comparing those versions, then you should compare them to MySQL 3.x!

The Windows port since 8.2 is extremely stable and on the same stability level as the Linux versions.

Postgres supports a lot more ALTER TABLE parameters than ADD COLUMN, RENAME COLUMN and RENAME TABLE.
It would pay to read the manual before posting wrong information:
http://www.postgresql.org/docs/current/static/sql-altertable.html

Large objects can be dumped with pg_dump, that information might have been true for 6.x and 7.x but is definitely not true for the 8.x line.

And you should have compared Postgres’ bytea to MySQL’s LONGBLOB type not the Large Objects type.

And MySQL’s LONGTEXT type compares to Postgres’ text datatype.

Oh and MySQL’s BLOB datatype can only hold 65K. Pretty large for a LargeOBject…


Brad
August 31, 2009: 6:08 pm

Why would you bother with discussion of the 6.x and 7.x branches, and ignore 8.4, 8.3, 8.2, 8.1, and 8.0 (or some combination of them).

Comparing MySQL to PG 6.x is like comparing the initial versions of MySQL from the mid 90’s to current Postgres Releases. Comparing 7.x is like comparing to MySQL 3.23


alvherre
August 31, 2009: 5:06 pm

Postgres 6.x? That must be a joke. Are you comparing to MySQL 3.0? Many of the comments on Postgres seem based on very a outdated version.


Gabriele Bartolini
August 31, 2009: 4:47 pm

Excuse me. Why do you still consider PostgreSQL 6.x and PostgreSQL 7.x in your article and only at the end you name PostgreSQL 8.x? I remind you that PostgreSQL 8.0 has been out for almost 5 years.
I am very concerned about your “Stability” review, purely based on those obsolete versions (it is like if I did a review of MySQL purely based on 3.23 version - which I personally loved!!!).

YOUR VIEW POINT
NAME : (REQUIRED)
MAIL : (REQUIRED)
will not be displayed
WEBSITE : (OPTIONAL)
YOUR
COMMENT :