MySQL vs PostgreSQL

Which one would be better to use? What are thier strenghts and weaknesses? Which is faster? More Secure? Well, What do you think?

Hi

This is posted in the wrong forum! This is => PHP


It would be better if you put it in....

General Database Discussion


Other than that, I think you will find it very hard to compare MYsql to PostgreSQL, first one is a real 24/7 RDBMS and the other is not.


A great case study was done a few months back, in PC Magazine, and it showed some very interesting results. What makes it a good study is that it is not people who use the software doing the study but the people who design it! They had some 20 Microsoft tech's there, 10 or 15 Oracle tech's and 5 or 6 from MYsql, The Wizard was one!

Anyway if you want to read some good content I would advise you to read that article, and you will see why *PostgreSQL* is not even close to being compared to *MYsql*

In the study *MYsql* just plain out did MSSQL, in the read and query test, it was only 10 pages slower than Oracle, but over 100 pages faster than MSSQL.

For me I will stick with using Mysql & Oracle, because they have shown me that they perform wonderful under hostile conditions, that's something I can't say about PostgreSQL, for me anyways!


F!

I posted here beacause they both have built in PHP support

Hi

I was was being polite!

If you want to get in a word battle..........


Think about what you just said!




No they don't, it more like..........

PHP HAS BUILT IN SUPPORT FOR BOTH OF THEM


So does (Perl, through DBI, and JSP) and many others!


How come you didn't post it in those forums?


F!

i agree with fataqui, you should post this topic in the general database discussion forum, as you'll likely get biased opinions in either the mysql or postgresql forums.

OK, Fataqui, I'll bite:

Yes, I agree that this should have been posted in General Database Discussion. Moderators, please move this thread there if possible.

But, I don't see how anyone who spends any time building real applications could consider MySQL as even comparing to PostgreSQL, much less Oracle, or even the lowly MSSQL server. Yes, that noisy PC Magazine article was a bunch of hooey! Of course MySQL is blazing fast. That's because it hardly even brushes the surface of providing useful data constraints, much less attempting to be a true relational database. And I only grudginly say this because they are finally actually trying to add these things. On the MySQL website, they used to say such things as


So far, the only reasons I see you presenting MySQL as a great database are 1. speed, and 2. it's a "24/7" database. Huh? I suppose PostgreSQL only works 23 hours of the day, 6 days a week. Yes, there was a time when you would have to take PostgreSQL offline for a few seconds to run a VACUUM on the tables. That time has been gone for awhile. And honestly, even if not, I would gladly pay the cost of having that downtime, in exchange for having real referential integrity, views, triggers, rules, CHECK constraints, stored procedures, user-defined datatypes, etc... (it's going to take MySQL *awhile* to catch up to the things PostgreSQL has had for years)

See http://www.pgro.uk7.net/innodb1.htm for an interesting discussion. And here's my FAQ from another forum on how to really compare PostgreSQL to MySQL. No, MySQL is not evil, and it has it's good points. But it has its place, and that is not (yet) among the serious RDBMS systems.

And speaking of PHP's relationship to all this, just browse http://php.net/pgsql and you will see some amazing new functions being developed for PHP/PostgrSQL. The future looks good! I have developed real PHP business applications with both MySQL and PostgreSQL, and I am never going back to the former .

I agree with rycamor, mysql and postgres are two different tools. You're comparing apples and oranges. You have to determine your projects needs, and then decide which to use.

If all you are doing is searching/displaying then use mysql. If you need stored procedures, and other more advanced features then you need postgres or oracle. I didn't mention mssql, because I don't care for microsoft products being used on the web.

I disagree with rycamor, when he says mysql isn't a serious rdbms. It is, just different. You don't use a jackhammer to pound a nail, but you don't use a hammer to break up concrete. Both are still hammers, just different kinds.

hi,
i have both of them installed on my freebsd server,
but i'm developing my application under windows os.

I have no title and no experience to give some comparisons between the twos,
but sometimes ago I found a bit complicated the way to install postresql under windows.

I think that a more easy-way-to-install for windows os would be helpful.

PostgreSQL comes with the cygwin environment now.

As far as which one is better, I would have to agree with Rycamor. To me MySQL is still just a baby and lacks to much in what I need. I have used MySQL and it is all right, but I normally use DB2 and find it much more appealing and functional. MySQL still has some time before it makes its way into the ranks of the powerhouses that are currently out there.

Guys,

I am a big fan of DB2 but I have only used it on AS/400's so I don't know how it is under UNIX/LINUX (Win!??)?

I use mySql for stuff on UNIX servers but have never really put it under any real heavy load. The lack of foreign key support is a bit of a bind though.

I never really considered PostgreSQL because mySQL seemed to be the popular choice when I started to use PHP so I have just stuck with it. My ISP only supports mySQL, but I will definitely have a look at postgreSQL based on what you guys have said about it.

Regards, Ed.

MySQL became the most popular database for Apache/PHP/Linux for a couple reasons(2 good, one not so):

1. It is very easy to install and learn, and it is very portable. You can simply copy MySQL data files to another MySQL data directory, and start using the data. Because of it's simplicity it is fairly robust and efficient at storing and retrieving data.

2. MySQL has a very nice fine-grained user management system, which is logically abstacted from the operating system's own user management. This made it easy for system admins to install MySQL quickly and add users and databases quickly, without needing to worry about interactions with the system permissions. In fact, the user managment system is the one area where I prefer MySQL to PostgreSQL.

3. A whole generation of PHP programmers came out of nowhere, with very little experience in real programming, and very little theoretical foundation to their work. MySQL was so easy to install and start working with that many of them never looked further. I also suspect that many were intimidated by the prospect of dealing with all that serious Relational stuff, and MySQL comfortably steered clear of that, and even attempted to reassure PHP developers that these features were not needed.

Even now, while MySQL is starting to add these features for some table types, most hosting services still offer just the plain "vanilla" version of MySQL, with no data integrity constraints. Let's face it, the more MySQL implements these features, the more complicated it's going to become. I think we are going to see that many users want MySQL to stick to the old simplistic methods.

So, MySQL did well for some good reasons, but also for a certain amount of ignorance in its user base. In fact, I am convinced that there are quite a few MSSQL (and even some Oracle or DB2 users) who never really use the data constraint features available to them, or even understand why they are so important.

MySQL is just easy to use. It is plain and simple and for those of us who use it for personal use, it comes very easy. I started using MySQL when I started PHP. I do everything with the two and MySQL is just plain and simple. I am moving on to building programs that use MySQL a lot so I want something that I can visualize simply.

The others may have mroe features and what not but, to me, MySQL is just more simple to use and interface with.

Another reason, MySQL is widely used. If you don't feel like building your own program, you go to a place like HotScripts.com and look for a program. More than 90% of the time it is based out of MySQL. Some people are starting to make it work with several databases, but 90% beats the 40% that both are supported.

I understand the need for simplicity sometimes. But, I feel that sometimes this simplicity is deceptive. Yes, MySQL is simple, but often that forces you to code more complexity into your PHP applications. This is where the rubber meets the road, so to speak.

Believe me, I spent my time with MySQL and PHP, and my reasons were the same as yours. But, when I started truly examining what the relational data model is, I realized that I was doing all kinds of extra work in my applications that could be much better positioned in the database itself. If you haven't checked out the possibilities of views, triggers, foreign keys, stored procedures, etc... then you need to. These are not just "features". They are critical to maintaining a true data system.

On one system where I converted to PostgreSQL from MySQL, the system ran much better, and I was able to cut out more than 50% of my PHP code from some of the pages (especially anything involving reporting).

Yes, I am aware of the argument that 90% of the PHP scripts out there support MySQL. Maybe I am an elitist, but I never looked on the majority as something to be pursued. 90% of the scripts out there are pretty bad examples of programming, anyway.

I guess if you work in an environment where you have your own dedicated servers, you can choose the server platform and database setup that is the best for the job or with which you are most comfortable.

A lot of smaller companies and individuals use shared hosting with service providers, so have to go along with what is there. As rycamor said, it's [mySql's] simplicity makes it more popular with ISP's, so most 'new' web developers get the initial exposure to the user friendly mySql (or access), and as they may not have gained experience in legacy or mainframe systems, they never miss what they never had, ie. triggers, stored procedures, access paths, keyed logical files, foreign keys or whatever.

Ed.