Re: MySQL refugee interested in pgSQL

On Sun, 25 Apr 2004, Jesse Thompson wrote:

> Hello, how do you do?
>
> I am a MySQL guy. I am interested in pgSQL. I have learned roughly
> everything I know about databases from using MySQL, and all that that
> implies. I would like to learn about PG and about "real" relational
> database theory. Links to any type of "PGsql for MySQL vets" and/or "Real
> relational database theory for MySQL vets" documents would be appreciated.
>
> Due to the hype over the feud, what I fear here of course is that I will
> be told that "learning Real DBM requires a Ph.D in DBM", and/or that
> "Knowing DBM is an in-born trait, please exit the gene pool immediatly". I
> don't suspect that either is the case.
>
> I am also very torn about the feud. I have been using MySQL without
> incident for years doing fairly complicated things under load. I want to
> know more about the things that ACID pundits appear to hold so dear to
> their hearts and MySQL eschews. I really can't fathom most of them yet.
> But at the same time I am cut very deep by the animous shown towards MySQL
> and it's users on a personal level. It is as if, by entrusting data of any
> kind to a MySQL database, I am clubbing baby seals somehow.

As someone who uses both MySQL and PostgreSQL (although I greatly prefer
PostgreSQL for most tasks, MySQL does have its uses...) I find the feud
fuelled mostly by rabid users on both sides with less understanding than
the typically quiet, experienced users from both sides.

However, I see more ignorance of basic SQL and relational theory from the
MySQL users who feel they are being attacked by PostgreSQL users
personally.

While I have serious issues with using MySQL for things like accounting,
finance, human resources, data warehousing, or anything where the numbers
and math HAVE to be right, I have no qualms about using it for things like
content management and that type of application. In the past, it was
much faster than PostgreSQL at the kinds of simple things that content
management required. Nowadays, PostgreSQL is much faster.

If you've learned SQL on MySQL, you'll likely need to re-learn some things
that MySQL let you do that weren't really the best or proper way of doing
things. For a list of such things, go here:

http://sql-info.de/mysql/gotchas.html

For learning relational theory, I'd recommend "Data and Databases:
Concepts in Practice" By Joe Celko. It's a more modern oriented read than
many of the larger and older tomes put out in the 70s and 80s, and covers
everything you really need to know about relational databases in about 350
pages.

For learning PostgreSQL, I'd highly recommend reading as much of the docs
as you can. I personally set up an ht://dig search engine on my
workstation and used it to index the docs for postgresql, apache, php, and
a few other things so I can search them easily and quickly.

Read up on MVCC, why it's great, what to look out for when using a
database like PostgreSQL that uses an in-store MVCC with dead tuples
floating about / being created.

Read up on sequences, constraints, partial / functional indexes.

Use Btree indexes unless you're sure another type works better. Most
usually don't, except the new, semi-experimental GiST types.

Wander about gborg.postgresql.org lotsa good stuff there.

Philosophically, PostgreSQL is built to handle hundreds or thousands of
clients at a time. This design philosophy means it will beat few
competitors when running a single threaded benchmark. however, as you
increase the number of threads hitting postgresql and whatever other
database, you should notice PostgreSQL slowing linearly, or nearly so, as
the threads increase in number, while most other databases will slow down
much more quickly.

And we don't club baby seals around here, but I have seen a few elephants
roasting dolphin meat for lunch. :-)