why is it that mysql has so much more of a presence than postgre does in web development?
the more i read, i see postgre ranking in many areas with oracle, and i routinely hear that mysql pails in comparison on nearly every front outside of select X from Y

just curious as to what all of your thoughts are on why this is the case (or if it isnt?)

Speaking only from my limited experience, MySQL:

1. Is virtually universal, especially among hosting services.
2. Is Good Enough, especially when database abstraction like Active Record dumbs it down.
3. Has a really excellent tool, PhpMyAdmin.
4. Is the one I know really well.

It would be very easy to add PostgreSQL to my Kubuntu, but unless I see a really good reason to, well ... I have a lot of other things to do.

linuxbz - 16 February 2007 01:28 PM

1. Is virtually universal, especially among hosting services.

this is kind of what i mean though, why is that the case?

as far as i have read, mysql 4 and 5 both hardly compare with postgre, so it just seems strange to me that mysql dominates the market

dont get me wrong though, i am a long time user of mysql as well

Hey mrapples -

I’m using both, but I also do my own hosting for most projects (or get to recommend the server/software).

If you do all the research there’s no doubt that Postgres is a more robust server with more bells and whistles. The latter is definitely a major contributor to MySQL success. MySQL development was driven with website development in mind. Easy to setup and use and for many years the tag-team partner of PHP. Thus it’s easier to support for hosting companies and (esp. thanks to phpMyAdmin) more accessible to end users in a hosted web environment. And a linuxbz mentioned - it’s good enough for the largest scope of web apps.

I adopted Pg a at version 8.1 and built 3 sites on top of it. I really like the referential integrity stuff. I use pgAdmin III (Java app) to admin it and for some time there this was really cool since I develop on OS X. More recently MySQL has made good admin/gui tools available (native) to run on OS X. They really feel great and adminstering MySQL Servers has now become fun and feels more serious.

When I look at the sites I build based on PG today I find that I overcomplicated things from a db perspective. Simply overkill for many a site.

The one thing I still like it for, are a few clients of mine that have me write apps against their Oracle dbs. I can almost exactly duplicate their db-schemas and procs in Postgres and develop offline. Other than that, I’ve picked up new interest in MySQL. I just allows me to get things done faster and keeps sites more portable.

HTH,
Juergen

If I recall my early days of PHP development (PHP3), MySQL boasted pure speed and stamina because it was lean and without the constraint, foreign key, transaction and stored procedures.

PostgreSQL on the other hand was this powerful database with all the bella and whistles.

For my project, which was a fairly substantial project, the thought of finding a database administrator familiar with PostgreSQL was daunting. It was much easier for the web application to forgo the database administration part and use a bare-bones product that didn’t need administration.

I know now, of course, that minimal administration would be required, but all us developers had come from an Oracle background where we had dedicated database administrators. So for me those two reasons (speed and administration) defined my initial choices.

I think it’s the name. The first time you see it, it’s not immediately obvious how to pronounce it properly and it’s easy to forget. j/k

Easy to setup and use and for many years the tag-team partner of PHP.

I think this has more to do with it than anything. During the time when every host was installing PHP4, they were also installing MySQL. We know all to well the ubiquity of PHP4 in web hosts…

I think it is even simpler than that - people want it simple, because they themselves are inherently complex. PHP and MySQL are exactly that - and they get the job done well.

Iksander - 17 February 2007 04:27 PM

...because they themselves are inherently complex…

That’s deep, man! Deep, but poetic.

It’s actually quite easy to explain why…

MySQL works better out of the box (pg needs tuning), is faster on small databases and easier to use.
PostgreSQL is playing in the enterprise league (with db’s like Oracle) tuned towards immensly complicated (and/or big) data/transactions.

[my personal experience, not necessarily true]

MySQL lacks some of the more “interresting” features (views, stored procedures, full joins, constrains…), making it easier to use - thus more appealing to the general masses.

As an example it is possible to make a nice “embedded” redcloth (rubyish for textile) parser from within Postgres using Pl/Ruby

(blatantly copied from robbyonrails.com, since i know jack about ruby ;P)

Deep, but poetic.

Totally dude - my code is like poetry on the soft buttery skin (of young college girls). :-D

Gunnar Lundström - 18 February 2007 03:42 PM

It’s actually quite easy to explain why…

MySQL works better out of the box (pg needs tuning), is faster on small databases and easier to use.
PostgreSQL is playing in the enterprise league (with db’s like Oracle) tuned towards immensly complicated (and/or big) data/transactions.

[my personal experience, not necessarily true]

MySQL lacks some of the more “interresting” features (views, stored procedures, full joins, constrains…), making it easier to use - thus more appealing to the general masses.

As an example it is possible to make a nice “embedded” redcloth (rubyish for textile) parser from within Postgres using Pl/Ruby

CREATE FUNCTION redcloth(text) RETURNS text AS '
  require ''rubygems''
  require ''redcloth''

  content = args[0]
  rc = RedCloth.new(content)
  return rc.to_html

' LANGUAGE 'plruby';

(blatantly copied from robbyonrails.com, since i know jack about ruby ;P)

Actually, MySQL can be tuned although most users do not realize it. Most installations include several versions on the ini file that can be used to tune MySQL for different database storage scenarios.

The more recent versions of MySQL do support the InnoDB and BDB database engines and also allow a combination of database-engine specific tables to be used. For example, it’s also possible to intermix MyISAM tables with InnoDB tables in the same database (have not tried this with BDB tables). With InnoDB and BDB, its possible to support transactions and referential integrity. Support for referential integrity simplifies coding requirements considerably because it virtually eliminates the possibility of orphaned data. Stored procedures, views, triggers, and other features are also supported in the newer versions. With newer versions of MySQL, there are many features that are available if you ever need to use them, even though you might not. It’s a good feeling to know that those features are available even if you never need to use them.

esra - 18 February 2007 11:08 PM

Actually, MySQL can be tuned although most users do not realize it. Most installations include several versions on the ini file that can be used to tune MySQL for different database storage scenarios.

Everything can be tuned… but PG almost requires it to work 100%, MySQL gives like 90% out of the box - only hardcore users need to tune…

esra - 18 February 2007 11:08 PM

The more recent versions of MySQL do support the InnoDB and BDB database engines and also allow a combination of database-engine specific tables to be used. For example, it’s also possible to intermix MyISAM tables with InnoDB tables in the same database (have not tried this with BDB tables). With InnoDB and BDB, its possible to support transactions and referential integrity. Support for referential integrity simplifies coding requirements considerably because it virtually eliminates the possibility of orphaned data. Stored procedures, views, triggers, and other features are also supported in the newer versions. With newer versions of MySQL, there are many features that are available if you ever need to use them, even though you might not. It’s a good feeling to know that those features are available even if you never need to use them.

Yeah, it’s like just before the big bang… all software is getting more and more homogenic - all software becomes more and more alike [in the way they spit out data, presentation and some backend stuff still differs]...

This is my first post, but I thought I’d weigh in.

I’m the CTO for a fairly large startup, we use Code Igniter for all of our admin tools.  Our main site is pretty much bare boned PHP, although I suspect a rewrite in CI coming down the pipe (I inherited the site from a large agency that built it, although I did the initial tech spec when the VC firm was funding it).  By large, I mean multi-million dollar investment with even more money being spent on marketing, etc.

Anyways, re: postgres, our site is built using PostgreSQL as our DB backend for a variety of reasons:

- Referential integrity
- Stored procedures/Triggers (which can be written in almost any language you can think of, including PHP)
- Performance
- Feature parity with commerical DB solutions

I’m not going to hate on MySQL, it’s great for certain purposes, but my personal experience is that PostgreSQL is a more mature solution with a very definite performance edge on MySQL.  Also, using pmemcached, there is a unique caching solution which provides for some great optimization at the DB layer versus the PHP layer (although we use memcached on that side too).  Pmemcached is a version of memcached for Postgres, and it basically allows us to do database caching from the database so that the latest data is always cached regardless of what client is updating/changing the database.  For example, our admin tools make changes to the database, and those changes are instantly in the cache for the front end to pull.  Nearly 80% of our database queries are actually pulled from memcached instead of from the database, and as most people know, the database is the biggest scale problem you have for large scale sites.

Of course this is all theoritical as our site isn’t live yet, but we’ve done enough load testing to be fairly confident with our setup and obviously have contingency plans ready to roll in the case of breakout success. 

I wouldn’t discount postgres, specifically if you have control over your hosting environment.  I think it to be one of the better open source RDBMS out there, but might be a little too heavy for some common uses, where MySQL is probably a better fit.

If you have any specific questions about Postgres, let me know.

jawngee - 19 February 2007 03:20 PM

[...] I wouldn’t discount postgres, specifically if you have control over your hosting environment.  I think it to be one of the better open source RDBMS out there, but might be a little too heavy for some common uses, where MySQL is probably a better fit. [...]

I think this summons it up quite good…
Since most of us are hobbyists with shared hosting and small needs (a small blog, guestbook and perhaps a forum)

mrapples - 16 February 2007 01:14 PM

why is it that mysql has so much more of a presence than postgre does in web development?
the more i read, i see postgre ranking in many areas with oracle, and i routinely hear that mysql pails in comparison on nearly every front outside of select X from Y

Gunnar Lundström - 18 February 2007 03:42 PM

MySQL lacks some of the more “interresting” features (views, stored procedures, full joins, constrains…), making it easier to use - thus more appealing to the general masses.

You need to start playing catch up, because MySQL has supported these for quite some time now. Be sure to read the manual.

Negligence - 20 February 2007 11:59 AM

You need to start playing catch up, because MySQL has supported these for quite some time now. Be sure to read the manual.

Yes, I do indeed! This shows how advanced my database usage/knowledge is

Out of curiosity, and totally offtopic. How many is there that use the “interresting” features, that is stuff other than basic insert/select/update/delete on a somewhat-daily basis?