Which is better, PostgreSQL or MySQL?

When you're choosing a database, you're making a long-term decision, because changing your mind later is difficult and expensive.

Now a days, There are so many applications using MySQL and PostgreSQL. Both are the most popular open-source RDMS (Relation Database Management System) programs on the market. MySQL is perceived to be much quicker but offers fewer features. PostgreSQL is believed to have a deeper feature set. 

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.

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

Lets look at difference between PostgreSQL and MySQL at implementation level.

Some Differences Between PostgreSQL and MySQL

In general, PostgreSQL makes a strong effort to conform to existing database standards, where MySQL has a mixed background on this. If you're coming from a background using MySQL or Microsoft Access, some of the changes can seem strange (such as not using double quotes to quote string values).

• MySQL uses nonstandard '#' to begin a comment line; PostgreSQL doesn't. Instead, use '--' (double dash), as this is the ANSI standard, and both databases understand it.

• MySQL uses ' or " to quote values (i.e. WHERE name = "John"). This is not the ANSI standard for databases. PostgreSQL uses only single quotes for this (i.e. WHERE name = 'John'). Double quotes are used to quote system identifiers; field names, table names, etc. (i.e. WHERE "last name" = 'Smith').

• MySQL uses ` (accent mark or backtick) to quote system identifiers, which is decidedly non-standard.

• PostgreSQL is case-sensitive for string comparisons. The field "Smith" is not the same as the field "smith". This is a big change for many users from MySQL and other small database systems, like Microsoft Access. In PostgreSQL, you can either:

• Use the correct case in your query. (i.e. WHERE lname='Smith')

• Use a conversion function, like lower() to search. (i.e. WHERE lower(lname)='smith')

• Use a case-insensitive operator, like ILIKE or ~*

• Database, table, field and columns names in PostgreSQL are case-independent, unless you created them with double-quotes around their name, in which case they are case-sensitive. In MySQL, table names can be case-sensitive or not, depending on which operating system you are using.

• PostgreSQL and MySQL seem to differ most in handling of dates, and the names of functions that handle dates.

• MySQL uses C-language operators for logic (i.e. 'foo' || 'bar' means 'foo' OR 'bar', 'foo' && 'bar' means 'foo' and 'bar'). This might be marginally helpful for C programmers, but violates database standards and rules in a significant way. PostgreSQL, following the standard, uses || for string concatenation ('foo' || 'bar' = 'foobar').

• There are other differences between the two, such as the names of functions for finding the current user. MySQL has a tool, Crash-Me, which can useful for digging this out. (Ostensibly, Crash-Me is a comparison tool for databases; however, it tends to seriously downplay MySQL's deficiencies, and isn't very objective in what it lists: the entire idea of having procedural languages (a very important feature for many users!) is relegated to a single line on the bottom fifth of the document, while the fact that MySQL allows you to use || for logical-or (definitely non-standard), is listed way before this, as a feature. Be careful about its interpretations.)

In Conclusion

Despite their different histories, engines, and tools, no clear differentiator distinguishes either PostgreSQL or MySQL for all uses. Many organizations favor PostgreSQL because it is so reliable and so good at protecting data, and because, as a community project, it is immune to vendor follies. MySQL is more flexible and has more options for being tailored for different workloads. Most times an organization's proficiency with a particular piece of software is more important than differences in feature sets, so if your organization is already using one of these, that is a good reason to stick with it. If you held my dogs hostage and forced me to choose a database for a new project, I would pick PostgreSQL for all tasks, including Web site backends, because of its rock-solid reliability and data integrity. And I would keep Drizzle running on a test machine, to stay acquainted with it, until it is ready for prime time, and then roll it out for cloud and application servers.