Re: Need Some Recent Information on the Differences between Postgres and MySql

On Fri, Jun 25, 2010 at 4:22 AM, John Gage <jsmgage@numericable.fr> wrote:
> There are features, are there not, that Postgres has that MySQL does not
> have?

My favorite pgsql feature is partial and functional indexes.  For
instance, let's say you have a work queue, and in it you have a boole
called processed.  it is 99.999% true, and you need an index for quick
lookup on the ones that are false.

create index yada on table blah (id) where processed is false.

Now any query that does select id from table blah where processed is
false can use that index for quick lookups.

Now supposed you want to lookup a table based on the first two
characters of some field.

create index yada on table blah (substring(textfield,1,2));

Next up: PostgreSQL stores its system catalogs in transaction safe
table types, like everything else it stores.  MySQL stores its table
defs in myisam, even if the whole of the db you create is innodb and
innodb is the default.  System crash in the middle of DDL?  Might lose
a table or two.

Next up: MySQL has optimizations made without proper testing.  For
example, see this bug:

http://bugs.mysql.com/bug.php?id=28591

This "optimization" made MySQL ignore the DESC keywork in innodb
tables.  It was pushed into live, production ready MySQL code
midstream in version 5.0.28 on 2007-08-02.  MySQL GA (i.e. production
ready) release had been made two years previously in March of 2005.

Fix was pushed out on 2007-09-24, nearly 60 days later, to version
5.0.48.  Problem solved right?  Well, not really, according to
http://bugs.mysql.com/bug.php?id=31001 it wasn't quite fixed.  The
actual fix gets pushed out on 2008-09-12.

This shows several things about the MySQL release philosophy, at least
at the time.  1: Introducing performance enhancments without thorough
testing in a production release is A-OK.  2: The fix may or may not
actually work when it does get applied. and 3: It can take about a
year to get that fix in place.

Things may be a LOT better by now.  I'd certainly hope so.  But I have
no real confidence or evidence of such an internal change.

Now compare that to pgsql bugs introduced that may clobber your data
in some way.  It's easy to do, look for a production release, followed
by another production release within a day or two.  It's happened
about two times I can remember off the top of my head with pgsql.  The
new version comes out, a horrific bug is found, fix is made, new
version release, old one pulled.  For 8.1 it looks like there was a
two day window where overly restrictive type length in functions and
constraints snuck out.  Two. Days.  There was another release that was
never actually released, might have had a show stopper in it too.

Biggest difference between MySQL and PostgreSQL? The developers.