PostgreSQL Vs MySQL: Comparative Review

Most 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.