[stellation-res] Some database questions, with major performance impact

["Mark C. Chu-Carroll" <mcc@xxxxxxxxxxxxxx>]

After thinking a bit about the database correspondance over the
last few days, I realized that there's a bit of implementation detail and
databases that's worth talking about, and that has a major impact on
our current performance.

We're currently using PostgreSQL as our primary development platform.
We decided on Postgres early on because it's a solid open-source
database, with reasonable performance and solid long-transaction support.

Unfortunately, PostgreSQL's JDBC support is abominable. It's bug-ridden,
unstable, and very poorly supported. It's a second-class stepchild
of the postgres codebase.  One particular feature of this that has
a dreadful impact on our performance is that JDBC batch mode
doesn't work, at all. As a result, at the moment, none of our code uses 
batch mode. Performance wise, this is a disaster. When we go to
insert 100 lines into the database, that becomes 100 separate JDBC
round-trips instead of just one.  Right now, this is the dominant 
performance factor in the database part of anything but the most trivial 
checkin.

We've had other problems with postgres recently, most prominently
one where they dropped support for true LOBs in JDBC.

While I'm neck-deep in database related things, I'm leaning towards
taking some time to migrate to a different open-source database. I'd
like to know what people prefer. Our requirements are:

(1)  full transactionality, with long-transaction support,
(2) reasonable BLOB support, 
(3) support for VARCHARS at least 2000 bytes long  (preferably more),
(4) solid JDBC support, including batch mode,
(5) Scalability to very large databases.

With a quick web scan, the things that I've found that look like
that support this:

(1) Inprise/Firebird (or whatever they're calling it these days). This is the
  system that Borland open-sourced.  I've heard very little about this,
  so I'd like to hear ay experiences that anyone has had with it. It
  looks to have really good cross-platform support, and they seem
 very serious about solid JDBC support, but it's currently only
  beta.

(2) SAPDB. I've heard some nice things about this one. The
  scuttlebut on this one is that getting it installed isn't a picnic, 
  and it's got a non-zero ongoing administrative load,
  but that it runs *really* well once you've got it going.  JDBC
  support looks to be very solid. 

(3) MySQL/InnoDB. The performance absolutely screams. The
  SQL support for long transactions is a bit wierd, but nothing we
  can't work around. Supposedly easy to install; but I've heard some
  negative comments about trouble keeping it running smoothly.

Any comments, experiences, preferences, or alternative proposals?

	-Mark

-- 
Mark Craig Chu-Carroll,  IBM T.J. Watson Research Center  
*** The Stellation project: Advanced SCM for Collaboration
***		http://www.eclipse.org/stellation
*** Work Email: mcc@xxxxxxxxxxxxxx  ------- Personal Email: markcc@xxxxxxxxxxx