[clue-tech] MySQL vs. Postgres: Was additional study groups ...

[Collins Richey]

I've lost track of the original mail in this series, but someone (Dave
Anselmi ?) raised the issue (paraphrased) "Why study Postgres when
MySQL is more popular and (seemingly) better supported on most Linux
distros?"

I presented this question and others to a senior Linux businessman
with a lot of experience in databases, and bcak came this feedback.
FWIW, the ">" comments below came from me; the uncommented portions
were the response to my questions.


---


> ... there is a lot more list traffic,general interest, etc. about MySQL than Postgres, even
> though those who really understand database technology (certainly not I) consider
> MySQL to be more of a toy. Postgres had all of the features that MySQL is now touting when
> MySQL was still wearing a training bra.

A lot more folks use M$ than Linux on the desktop.  What does that
tell you?  Marketing hype counts.  And MySQL has been a default
install on most distros where as Postgres hasn't.

Doesn't help that big blog and search sites use MySQL.  But who cares
if you get data corruption on a blog containing a bunch of idiots
non-sensical comments?  Or if you're running a farm of 1000 MySL
servers that monitor and rebuild each other if one gets
corrupted/outdated data?

[ note from Collins: the same responder has always maintained that
data corruption is more likely with MySQL than Postgres, especially
where multiple tables are in use. ]

>
> Given what little I understand about both products, I've never been
> able to figure out why MySQL became so insanely propular. I just read
> an article on LinuxToday where a company called EnterpriseDB has
> developed an interface (commercial not FOSS) that allows Postgres to
> run pretty much unadulterated Oracle code, and they're certainly not
> doing that for MySQL..

Because Postgres supports most of the same feature as Oracle,
Informix, Sybase, etc. MySQL is pure speed (only).  Most of these SQL
neophites also only use one table vice many.  But when you use many
tables (as I have, one app has over 50 tables), in MySQL you'd need 3
queries (a union of 2 natural joins) to perform a single left outer
join query.  No matter how fast MySQL is, unless the Postgres database
is huge, without indexes, and never analyzed (to give hints to the
optimizer) it will be twice as fast as MySQL when an outer join
workaround is required.

[ note from Collins: left outer join is one of the critical features
cited by data base professionals who prefer Postgres to MySQL. ]

Ask if MySQL requires a vacuum.  Vacuum eliminates deleted rows from
the db.  These rows are deleted when an updated row replaces it.
MySQL used to just rewrite the row (aagh, what a _terrible_ idea!
Imagine an OS crash in the middle of a row rewrite).  _All_ real SQL
dbs use row substitution to switch changes in.  And transactions are
vital, especially when updating hundreds of thousands of rows or even
when updating just a few rows if multiple users might be updating the
same rows at the same time (which brings up row vs table locking
issues).

Granted, it's been a long time since I followed MySQL development, but
I dropped it years ago for the same reasons I dropped M$.
Improvements be damned, I don't have time to learn hundreds of new
programs a year.  So I'll be sticking to PostgreSQL and Linux, thanx.


---

Enjoy!

--
Collins Richey
      Debugging is twice as hard as writing the code ... If you write
      the code as cleverly as possible, you are, by definition, not
      smart enough to debug it.
             -Brian Kernighan
_______________________________________________
CLUE-tech mailing list
CLUE-tech at cluedenver.org
http://cluedenver.org/mailman/listinfo/clue-tech