Skip site navigation (1) Skip section navigation (2)

Re: Article on MySQL vs. Postgres

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Tim Perdue <tperdue(at)valinux(dot)com>
Cc: Benjamin Adida <ben(at)mit(dot)edu>, pgsql-hackers(at)hub(dot)org
Subject: Re: Article on MySQL vs. Postgres
Date: 2000-07-05 00:51:30
Message-ID: 39628692.4DD35078@mascari.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tim Perdue wrote:
> 
> 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.
> 

I've seen problems with block read errors in large Oracle
databases which fail their alleged CRC check -- intermittent core
dumps which required a dump/restore of 25 years of insurance
claims data (40 gig - it was a lot at the time). After being down
for days and restoring on a new box, the same errors occured. 

> 
> 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.

This can be true for Internet sites, of course. But with
corporate Intranet sites that dish-out and process ERP data, the
queries can become quite complex while concurrency is limited to
< 1000 users.

> 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.

Umm...

DELETE FROM foo WHERE EXISTS 
(SELECT f.key FROM foo f WHERE f.key = foo.key AND f.oid >
foo.oid);

I believe there's even a purely SQL (non-oid) method of doing
this as well.

> 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.

This is the nature of the application. In the same example above,
how can I "charge" a cost center for the purchase of products in
an in-house distribution center and "deduct" the resulting
quantity from the distribution center's on-hand inventory sanely
without transactions?

Mike Mascari

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-07-05 00:59:05
Subject: Re: [COMMITTERS] pgsql/doc/TODO.detail (alpha default distinct flock fsync function limit null pg_shadow primary)
Previous:From: Chris BitmeadDate: 2000-07-05 00:33:14
Subject: Re: Re: [HACKERS] Revised Copyright: is this more palatable?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group