We have been using Mysql for last serveral years. Bur from some time now we are facing scalability issues with Mysql as our tables are growing fast and no. of queries on these tables are increasing day by day. While looking the possible solutions many suggested to use PostgreSql. So first thing that came to my mind was in what ways Pgsql is better than Mysql. I searched the web, asked this question to my network and here is some of the findings...
Pgsql is a much more feature complete SQL engine.
PostgreSQL does not have an unsigned integer data type, but it has a much richer data type support (including BOOLEAN, IP addresses, UUIDs, and such), user-defined data types mechanism, built-in and contributed data types.
Both PostgreSQL and MySQL support Not-Null, Unique, Primary Key and Foreign Key constraints. MySQL doesn't support the Check constraint while PostgreSQL has supported it for a long time. PostgreSQL's base data types are much more consistent about enforcing data integrity, even in the absence of constraints and foreign keys. Thus, "NOT NULL" really means that NULLs are forbidden, you can't have "February 29th" in other than leap years, "blank" isn't automagically transmogrified into a "zero", and such.
PostgreSQL can compress and decompress its data on the fly with a fast compression scheme to fit more data in an allotted disk space. MySQL's high performance storage engines do not support on the fly compression as of 5.1. MySQL 6.0 will support on the fly compression with its Falcon storage engine
MySQL's MyISAM engine performs faster than PostgreSQL on simple queries and when concurrency is low. MyISAM's speed comes at the cost of not supporting transactions, foreign keys, and not offering guaranteed data durability.
MySQL's count(*) is really fast. PostgreSQL count(*) is very slow because instead of counting rows using an index scan, it goes through the entire table sequentially.
MySQL supports INSERT IGNORE and REPLACE statements. PostgreSQL supports neither of these statements and suggests using stored procedures to get around the lack of these statements.
PostgreSQL's speed advantage over MySQL can be seen drastically in a large multi-core/processor environment. PostgreSQL scales much better, both in terms of using up scale hardware, and dealing with concurrency. MySQL, on the other hand, focuses on scale out technologies and the use of off the shelf commodity hardware.
PostgreSQL is fully ACID-compliant, while MySQL's InnoDB storage engine provides engine-level ACID-compliance.
PostgreSQL supports Partial and Bitmap Indices. MySQL has no bitmap indices (but achieves similar functionality using its "index_merge" feature) and partial indices (MySQL supports partial indexing using the InnoDB engine, but not with the MyISAM engine).
A PostgreSQL trigger can execute any user defined function from any of its procedural languages, not just PL/pgsql. PostgreSQL also supports "rules" which allow operating on the query syntax tree, and can do some operations more simply that are traditionally done by triggers.
MySQL has built-in replication, PostgreSQL is modular by design, and replication is not in the core. There are several packages that allow replication in PostgreSQL.
PostgreSQL makes a much better impression, from administration perspective. Backup and replication features are more advanced, many features that MySQL is going to have (?) like point in time recovery , multiple replication slaves , better support for foreign keys, cursors and stored procedures are all available in PostgreSQL already.
MySQL is an open-source product. Postgres is an open-source project. MySQL community is more active and enthusiastic than PostgreSQL's and the MySQL documentation (books, blogs etc) are way more and up to date.
