Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am designing a blog application. Which of these fits better? In which use cases will mysql be good and for what use cases will postgresql be good?

share|improve this question
9  
Both will be fine for such an application. Although I can't think of anything that MySQL does better than Postgres. But I can think of a lot of things that Postgres does much better than MySQL –  a_horse_with_no_name Jul 13 '12 at 11:54
2  
SQLite will be good enough. –  Frank Heikens Jul 13 '12 at 13:14
    
Can someone tell me some stuff which postgres is really good at but mysql isn't? –  Aram Bhusal Jul 13 '12 at 14:37
1  
@a_horse_with_no_name - You can't think of "anything"? MySQL does many things better than postgresql, particularly around flexible replication topologies, operational usability, and easier upgrades. What postgres is better at is nearly everything in the SQL-land. This is all unlikely to be relevant for a blog application, however. –  Aaron Brown Jul 13 '12 at 15:07
    
show 8 more comments

2 Answers 2

MySQL's home territory is in CMS. It will be good enough. I like PostgreSQL as much as the next guy but a blog will work on either, and MyISAM tables might actually be really nice there.

As for where PostgreSQL does better than MySQL, here is my take. There are two big areas:

  1. Advanced data modelling and
  2. Multi-application databases

The first means that PostgreSQL is far more mature at doing complex data modelling than MySQL is. It has a very mature extensible type system, a wide range of procedural languages, and a great deal of flexibility in how these languages can be plugged into existing queries.

If that wasn't enough, the fact is that you can essentially build your data model in PostgreSQL based not only on what information you are storing but what information is commonly derived from what you are storing. This makes things like not-first-normal-form actually sane to use where they are needed. Add collections and multiple inheritance in table structure and you have a very sophisticated data modelling platform. For examples, see my recent blog entries.

On to the other side. MySQL developers tend to see the application as the bottom tier of the application stack, while PostgreSQL developers tend to see the application as the center of the data environment. Besides the content management system market, MySQL's other major market is in applications where data is not expected to be exposed to more than one writing application at a time. This leads to a significant difference in handling data validation, etc.

In PostgreSQL validation is always equally strict. If the app expects special error treatment it had better call functions or casts to handle this explicitly. For example, if I want to store up to a maximum of 144 characters in a text field, I can: insert into mytable values (?::varchar(144)); and absent such, PostgreSQL will throw an error if it isn't crystal clear what the application wants, and whether it will fit into the data schema as defined.

MySQL however places the application in charge of defining the data validation rules. The sql_mode setting (which includes such things as whether '0000-00-00' is a valid date or whether '2008-02-30' can be stored in a date field) is something any application can set without restriction, and the DBA has no way of restricting this. So while PostgreSQL allows the relational and object-relational interface to serve as a public API, it is essentially intended largely to be a private API for applications in MySQL. This is a huge difference and not readily understood by many people trying to make the choice. This leads to major differences in application design.

MySQL is a data storage and reporting solution for your application. PostgreSQL is a data centralization, modelling, and reporting solution for your organization. The two are remarkably different.

share|improve this answer
    
The best comparison for a laydba I've ever read. –  alfish Feb 3 at 0:44
add comment

Are you writing the blog application just for fun for yourself, or are you planning to make it available for everyone to download (think WordPress) or sell it as a product?

If it's going to be something that anyone can download and install, then it's a no-brainer:
It must work with MySQL if you want it to succeed...because practically every web hosting provider out there offers MySQL databases, but not PostgreSQL (at least I never saw a provider with PostgreSQL).

No matter if MySQL or PostgreSQL is better from a pure technical point of view - if you want people to use your app, it must work with what's available at their web hosters.

share|improve this answer
    
Overwhelming majority of shared hosting providers that offer cPanel also offer Postgresql. So this availability issue is almost non-existance. –  alfish Feb 3 at 0:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.