[Bendug] Knock/KNock

[Tim Howe]

forgot to CC the list...

On Thu, 29 Dec 2005 23:47:52 -0800 (PST)
"Gary E. Miller" <gem at rellim.com> wrote:

> I have heard that Postgres has come a long way, it was pretty piss poor
> compared to MySQL about 5 years ago when last I compared them.  There is
> still a lot more free code out there for MySQL than Postgres, and of
> course MySQL is just bullet proof.  Got any good reason that Postgres
> is now ahead of MySQL?  The main one I heard of was that it was pretty
> lite weight, but SQLite now wins that corner.

	If anybody needs to catch up, it's MySQL.  Finally, with InnoDB, they have a storage engine that is more on par with PostgreSQL, but it looks like it is being purchased out from under them.  With v5.1, MySQL finally has features that enterprise DB users need, but I still haven't heard of anybody trying to move from Oracle or MS-SQL to MySQL (except for web storage).
	MySQL does do one thing very well, maybe better than anyone: Very fast flat data inserts and selects.  If you have a flat data model and need to insert and fetch that data really fast, MySQL is an excellent choice.  I use it for RTG, which needs to use big, flat tables to store integers and get that data out nice and fast.  For this reason (well, not really, but it is a good reason) people use it for all kinds of website data storage.  This almost always requires an uninteresting data model and fast, simple queries.  Fast and simple is what MySQL is very good at, but as you said, SQLite may be better.  SQLite can't run on a different server than the web server, though, so it's kinda hokey for real installs.
	PostgreSQL can do everything that MySQL can do.  For very large flat data, it may not be as fast.  The jury is still out since benchmarks seem to conflict, but who cares.  People aren't using PostgreSQL as an SQLite or MySQL replacement (well, I am because I know it much better now and when I don't know an answer, it's easy to figure out).  People are using PostgreSQL because they are database people.  They have used Oracle or MSSQL or DB2 and know what is needed to build complex, enterprise databases.  PostgreSQL even comes with tools for migrating your Oracle or MSSQL database over to it.  These systems require capabilities beyond running some simple backend for another application.
	Try a somewhat complex join on MySQL and watch its speed go down the toilet.  Try some long-running concurrent transactions.
	This stuff seems to be pretty well known to people who actually work with large databases.  There were no fewer than 6 people at OneEighty with several years of large database experience.  When we thought we were going to ditch Oracle, one guy mentioned moving to MySQL...as a joke.  We all chuckled for a minute before a quick little consensus for PostgreSQL developed.
	One little project of mine is an IP address management tool.  It doesn't require a lot of horsepower, but I'm using PostgreSQL instead of MySQL for the simple fact that Pg has network aware data types.  Even if it didn't, Pg allows custom data types, so I could have made my own.

	Crap, this did get long...  And it isn't even a comparison, just a rant!  Anyway, YMMV, but having used Pg, MySQL just won't do for me.  I'll still use it where it is the default backend for something I need, and I have no doubt that it will perform great, but when I need to build a real database, it won't even be considered.

	But I'm glad we have choice.  WHat ever happened to SAP-DB?  I heard it was good, but never got a chance to look at it....   I just looked it up and it is apparently now called MaxDB and is affiliated with MySQL.  Weird.  Maybe that's where the the v5 features came from.

	So, to close, you are right: PostgreSQL is lightweight (by default, you can beef it up for large installs), but it provides serious database functionality at that weight on par with the big boys.

--TimH

P.S. Here's a recent article that talks about Pg and Ruby!  It always makes me smile. http://www.oreillynet.com/pub/wlg/6286