Page 2 - MySQL vs PostgreSQL

I think I just will repeat what everyboy else seems to have already said...
MySQL is used mainly for it's simplicity and it's price. The learning curve is not as steep as it is with other databases. Coming from a background with databases like MSSQL and DB/2, I picked up MyQSL just because it's simplicity and ease of using together with PHP. But when it comes to more complex issues, MySQL just don't cut it. Using cursors, sp, transactions, views etc can *really* easy your life as well as the coding part on some occasions...

I haven't used Postgresql (yet), so I can't really say much about it. I've been meaning to install it and give it a whirl for some time now, but as with just about everything else, it's a matter of time...

So choose the database and OS depending on your needs, as it's not fair to either of them to be compared in a simple way...

//NoXcuz

You made some really importants points rycamor.
I've also met quite a few php programmers who don't care/know about anything beyond mysql although the features of other RDBMS are really essential in some situations.
The situation is similar with benchmarks IMHO.
It's like comparing apples and oranges because you are always coding the "lowest common denominator" which is often mysql.

However that shouldn't read like I dislike mysql - it has it's place and I use it too.
But the more I learn about postgres I like it's flexibility and extensibility more and more.

All the seniors are right. I have diffrent opinion. All the young budding Programmers Developers Look forward to PHP and later they find PHP is not the kind of stuff Perl is Perl is much Stronger than PHP but PErl can't do what C or C++ can. this is the thing with MySQL and PostgreSQL. I never Used PostgreSQL But will definitly try it now. The readily available support for PHP and MySQL makes it a first choice to use MySQL. I read RDBMS Thery. (EF Codd's rules) And found that MySQL is Really a Poor thing. More things can be done by SQL itself without any intervention from PHP. But PHP Fills the gap and MySQL still boasts of Complex application support. If MySQL can't do it itself Make PHP script to do the thing. Next the big thing is availaiblity of Readymade programs. All u need to do is to browse Frehsmeat/Sourceforge/HotScripts. Even PHP unofficially is bound with MySQL( ). As one of thesenior has pointed out there are too many functions in PHP for pgsql and One can even use OPracle with PHP. But again if it has been done with MySQL no body wants it to repeat and invest his/her own time.

I will surely look in to more comments

if you're up for complex database, you definetely need postgresql

Agree.

Now, PostGRESQL's performance can be very well compared to MySQL. and you get all the features of TRUE RDBMS.

Also there has been article on phpbuilder, where tim states that MySQL crashes around 20-50 SIMULTANEOUS users.. where PostGRESQL can really handle around 100 SIMULTANEIOUS users...i guess thats very crucial.

[hey someone will have to checkout those figures. ]



Agree. Majority doesnt mean that its BEST.

JD

I don`t think you can compare them at all, MySQL is full of features which would support one application whereas PostgreSQL has a host of features which would support another application.

It is the same old story that PHP is better than ASP, or CGI is better than PHP, at the end of the day you cannot compare them because they all have weakness's and stregths, they all have advantages and disadvantages.

It depends on your knowledge of them and what you are developing as to what is the best to use.

That is my view of comparisions....

On another note I would just like to comment on the fact that people are expressing that they prefer one over the other because you can cut down on the coding in PHP and put it into the database, I disagree with this. Yes it may be less code and it may work slightly faster when using the database as opposed to the scripting language. But at the end of the day you are always using the servers resources to reach a single goal which can be achieved regardless of the choice of whether or not you put the database to work harder than the coding or vice versa.


Just my tuppence worth.

Just a note on where to put the load, the database or the webserver. It probably won't matter much if you're using a box with both the database and the webserver installed, but things start to happen when you use separate boxes...

//NoXcuz

It's not just 'moving' it around since pre-compiled SQL (in Oracle, MS SQL, etc.) is far more efficient than ad-hoc (basically what the embedded SQL is in PHP, etc.) SQL.

You can, theoretically (provided web server/network lines/etc. are not saturated, etc.), serve more clients using stored procs than embedded SQL since it takes more work (sum total) to use Ad Hoc than precompiled SQL. It takes load off of BOTH machines (less client activity AND less DB work).

That, and if you have more than one box (e.g. clustered web servers talking to DBs) it cuts WAY down on network latency / overhead since you only have to send 'execute my_proc $param1' rather than many, many lines of SQL.

So it is a significant advantage to use precompiled SQL over ad hoc SQL.

I know it's popular to take an "egalitarian" view of the debate between PostgreSQL and MySQL, since they are different, and thus have different advantages and drawbacks. That much is true, but I believe the relative drawbacks of MySQL are much worse than the drawbacks (few) of PostgreSQL.

I I don't know if anyone read the FAQ at Tek-tips.com that I posted above, but I recommend it seriously, because I did some homework on the differences, having done two conversion of serious web-based apps from MySQL to PostgreSQL.

If you read the FAQ, you will see that I consider MySQl to have only 3 advantages, none of which involve core RDBMS functionality. PostgreSQL has many advantages.

Also, SilkySmooth is barking up the wrong tree. It's not about performance, but logical integrity. There is much discussion about where application constraints and complex data relationships should be handled. Many younger programmers like to put these constraints into the application code, but I believe that is almost always a mistake, because now your database itself will not maintain those constraints if other applications connect to it, not to mention that it is far more risky to depend on application code, rather than internal DB integrity. RDBMS's are not only optimized for data sorting and retrieval, but for tight data constraints, which you cannot get easily in application code.

In short, the more logic you can push to your database, the better. The only real trade-off is when you use proprietary features of one DBMS, which make it difficult to port your application to another DBMS. However, I would much rather port a database to another DBMS than port complex application constraints to another application environment, such as going from Java to PHP, or vice-versa.

The developers mantra should be "logic first, performance later". You can always optimize performance in many different ways, or just get better hardware. Logic is not so scaleable. If you don't build the right logical constraints and relationships from the beginning, your application's lifetime will be much shorter, and will involve much more restructuring than you ever wanted to do.

Well said, Ryan... and a lot better than I could, as well. Another aspect is encapsulation/portability. If all your SQL is wrapped up in stored procedures (and generally most RDBMS's may not have the same syntax but the same ability when you use their built-in languages) you won't have to change much in your application if you switch RDBMS's -- if you program 'right' you won't have to change anything.

Ye Massive Multiple Join Query of Death. LOL -- EXACTLY. Especially with the lack of subqueries and other advanced SQL like EXISTS.

Can postgresql do JOIN'ed updates?

e.g. something like this:


It is currently horribly awkward to update tables in MySQL with results from other tables -- you are forced to write a *script* to accomplish what can be done in a single line of SQL. UGH.

No, sadly PostgreSQL does not have full JOIN'ed updates, nor does it allow table aliases in updates. But, it does allow referencing columns from other tables in an UPDATE, and it allows for subselects in non-SELECT queries.

So, PostgreSQL can accomplish the same thing, but with a different syntax:


Not as elegant, to be sure, but at least we can "get there from here".

Also, using the stored procedure language PLg/PgSQL, you can do a lot more multiple-table manipulation.

If PGSQL community actually got off their preverbial asses and made a real binary copy of their database for the Win32 platform (like MySQL have), without the need for people (those who aren't super-pooper-dooper C/C++ programmers) to tear their hair out tryin to work out how to port and recompile it for Win32. Then maybe I'd be able to find out and comment how great PG actually is.

The sooner these geeks realise that *NIX/LINUX isn't the only O/S used on development workstations in the world and the fact that not every web developer is a C/C++ guru the better.

The Cygwin version of PG (postgresql-7.1.3-1) I tried was a load of crap, especially considering that I couldn't find any cygwin-version-specific info on how to get it up and running.

With the above in mind, I am going to say MySQL is better. Mainly because of these things:

• 100% compatible with Win32 with a host of easy to use tools like MySQL-Front and MyODBC
• Your database will function the same on *nix/linux systems as it does on your Win32 platform
• No need to install cygwin with up to 100MB of tools your never going to use

i guess i will agree with deepspring.

Though most of the deployment of web apps is on *nix machine, considerable apps are developed on windows. and if pg doesnt have good support on windows..

hm....


JD

So take a look at http://firebird.sourceforge.net/ which is feature rich and has an excellent multiplatform support!

<rant mode="on">

Deepspring,

Those sentiments of yours are one of the biggest turn-offs to open source developers ever. Not the fact that you prefer Windows, but what your attitude is about it. It's not their business to do what you want. They are doing what they want because hey, it's their time, and their effort. If you were a paying customer and they were misrepresenting their product, your attitude would be appropriate, but that is not the case.

No one has to develop anything for free. They do it because they want to, or because certain companies see an advantage to funding some of this development and releasing it for free. So, given these facts, if you want Windows support, then why not try to help the PostgreSQL effort in this regard? Even if you can't do any C programming, or donate any money, you could still send some nice letters to certain companies explaining the advantages of funding some development of PostgreSQL in Windows. (couldn't hurt, could it?)

As far as I'm concerned, I have no real reason to desire PostgreSQL in Windows. I see it as a crippled OS, as far as serious data management goes. Even if I develop from my Windows box, it is a very easy matter to connect to a cheap pentium 133 running in the corner with PostgreSQL. If you would move your "proverbial a**" enough to download and burn (or purchase) a single bootable CD of any standard Linux or BSD distribution, you would find that it is probably easier to get that OS up and running with built-in PostgreSQL supprt than it is to install Windows 2000 with SQL Server.



If you want it, its there for you. And guess what? You will get more online help doing that than you would ever get from Microsoft in a million phone calls.

If you want it easy, then stick with MySQL. The benefit you get from some things depends on the effort you are willing to put into them.

Now, that being said, I should mention that there is a considerable effort being put into porting the next version of PostgreSQL (7.3) to run natively in Windows, without Cygwin binaries. Don't know if they will achieve it right away, but they will sooner or later. I hope they don't see your post about getting "off their proverbial asses", though. That is not a very motivating attitude.

</rant>