On Fri, Jun 25, 2010 at 4:22 AM, John Gage <jsmgage@xxxxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
