Re: Article on MySQL vs. Postgres

On Tue, 4 Jul 2000, Tim Perdue wrote:

> Benjamin Adida wrote:
> > Jan said that each tool has its value, and that's true. I recommend you
> > define your evaluation context before you write this. Is this for running a
> > serious mission-critical web site? Is it for logging web site hits with
> > tolerance for data loss and a need for doing simple reporting?
>
> This is for what most people do with PHP and databases - run
> semi-critical medium-traffic sites. Anyone running a mission-critical
> site would have to look elsewhere for true robustness. I would not at
> this time recommend any serious, life-threatening app run On either
> database.

Someone want to give me an example of something that would be
life-threatening that would run on a database? I can think of loads of
mission critical stuff, but life threatening? As for mission critical,
mission critical is in the eye of the end-user ... all my clients run
PostgreSQL for their backend needs, and I can guarantee you that each and
every one of them considers it a mission critical element to their sites
... then again, I have 3+ years of personal experience with PostgreSQL to
back me up ..

> I took a real-world page from our site
> <http://sourceforge.net/forum/forum.php?forum_id=1> and made it portable
> to both databases. Of course, I could not import the "body" of the

did you take the time to optimize the queries to take advantage of
features that MySQL doesn't have, or just straight plug-n-play?

> > of PHP apps don't, from what I've seen)? How does the performance get
> > affected when a query in Postgres with subselects has to be split into 4
> > different queries in MySQL?
>
> I'd really love to see a case where a real-world page view requires 4x
> the queries on MySQL. If you are doing subselects like that on a website
> in real-time you've got serious design problems and postgres would
> fold-up and quit under the load anyway.

Odd, I'll have to let one of my clients know that their site has design
flaws ... wait, no, they had 3x the queries in MySQL as in PgSQL, so that
probably doesnt' apply ...

> > Do you run on a magic power grid that
> > never fails?
>
> Reality is that postgres is as likely - or more likely - to wind up with
> corrupted data than MySQL. I'm talking physical corruption where I have
> to destroy the database and recover from a dump.

Odd, in my 3+ years of PostgreSQL development, I've yet to have a
project/database corrupt such that I had to restore from backups *knock on
wood* INDEX corruption, yup ... 'DROP INDEX/CREATE INDEX' fixes that
though. Physical database corruption, nope ...

> Further, I have had situations where postgres actually had DUPLICATE
> ids in a primary key field, probably due to some abort or other nasty
> situation in the middle of a commit. How did I recover from That?
> Well, I had to run a count(*) next to each ID and select out the rows
> where there was more than one of each "unique" id, then reinsert those
> rows and drop and rebuild the indexes and reset the sequences.

Odd, were you using transactions here, or transactionless?

> > Do you never have code-related error conditions that require
> > rolling back a series of database edits?
>
> Personally, I check every query in my PHP code. On the rare occasion
> that it fales, I show an error and get out. Even with postgres, I have
> always checked success or failure of a query and shown an appropriate
> error. Never in two years of programming PHP/postgres have I ever used
> commit/rollback, and I have written some extremely complex web apps
> (sourceforge being a prime example). Geocrawler.com runs on postgres and
> again, I NEVER saw any need for any kind of rollback at all.

Wait ... how does checking every query help if QUERY2 fails after QUERY1
is sent, and you aren't using transactions?

> Only now, with some extremely complex stuff that we're doing on
> SourceForge would I like to see locks and rollbacks (hence my recent
> interest in benchmarking and comparing the two). Your average web
> programmer will almost never run into that in the short term.

Cool, at least I'm not considered average :) I *always* use transactions
in my scripts ... *shrug* then again, I'm heavily into 'the rules of
normalization', so tend to not crowd everything into one table.