principlesqlMinor
MySql Vs. Postgresql for a blog application
Viewed 0 times
postgresqlapplicationmysqlforblog
Problem
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?
In which use cases will mysql be good and for what use cases will postgresql be good?
Solution
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:
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:
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.
As for where PostgreSQL does better than MySQL, here is my take. There are two big areas:
- Advanced data modelling and
- 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.
Context
StackExchange Database Administrators Q#20838, answer score: 2
Revisions (0)
No revisions yet.