MySQL vs Postgres

We have two good matured open source databases. One is MYSQL and other is POSTGRES.

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.

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.

                                         POSTGRESQL                        MYSQL
ANSI SQL compliance     Closer to ANSI SQL         Follows some of the ANSI
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

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)

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

I think now you can make your decision easier. If you are having complex system and need good features of database obviously POSTGRESQL is good choice and if your system is simple and needs fast response time first choice is MYSQL.