Re: Article on MySQL vs. Postgres

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