The following chart will illustrate the comparison between several open source databases. The comparison will cover: PostgresSQL 8.2, MySQL 5, Derby 10.3, and HSQLDB 1.8 in terms of their limitations and strength. The goal of this analysis is to provide enough background information in the process of decision making in choosing a suitable open source database for PRP and which will also be easy to install and probably able to be bundle together with the PRP without extra installation.

Selection Criteria

 

PostgreSQL 8.2

MySQL 5.0

Apache Derby 10.3

HSQLDB 1.8

H2

OS support

First windows supported version, only support linux and windows, special compilation requires for other platforms

Supports Windows/Linux/Unix/Mac OSX

Any OS that can run Java

Any OS that can run Java

Any OS that can run Java

Documentation

Full pre and post installation documentations

Extensive pre and post installation documentations

Extensive pre and post installation documentations

Full pre and post installation documentations

Extensive pre and post installation documentations

License

BSD License - Free

Depends - GPL for open source or Commercial for commercial

Apache License v2 - Free

BSD License - Free

Modified MPL - Free

Maximum Database Size

Unlimited

Unlimited

Unlimited

Unlimited (depends on memory)

Unlimited (depends on memory)

Maximum Number of Tables

Unlimited

Unlimited

Unlimited

Unlimited (depends on memory)

Unlimited (depends on memory)

Maximum Table Size

32 TB

64 TB

Unlimited

8 GB

?

Maximum Row Size

1.6 TB

8 kB

Unlimited

Unlimited (depends on memory)

?

Maximum Field Size

1 GB

255 B

2 GB

Unlimited (depends on memory)

?

Maximum Rows per Table

Unlimited

Unlimited (Limited by tablespace of 64TB)

Unlimited

Unlimited (depends on memory)

Unlimited (depends on memory)

Maximum Columns per Table

250 - 1600 depending on column types

1000 columns

1012 columns

Unlimited (depends on memory)

Unlimited (depends on memory)

Maximum Indexes per Table

Unlimited

64 indexes

32767 indexes

Unlimited (depends on memory)

Unlimited (depends on memory)

Indexes

32 fields - B-Tree, R-Tree, GiST, and Hash

16 fields - B-Tree

16 fields

?

?

Interfaces

ODBC, JDBC, C, etc.

ODBC, JDBC, C, etc.

JDBC

JDBC

JDBC

Transactional

ACID-compliant

InnoDB type tables only - ACID-compliant

Yes

?

Yes

Bind Variables

Yes

Yes

Yes

?

?

Stored Procedures

Yes - SQL, PL/Tcl, PL/pgSQL

Yes

Yes

Yes

Yes

Trigger

Yes - SQL, PL/Tcl, PL/pgSQL

Yes

Yes

Yes

Yes

Row Level Locking

Yes

Yes

Yes

?

Yes

Timeout

Yes

No

Yes - deadlock detection and timeout

Yes

Yes

Error Code

Yes

Yes

Yes

Yes

Yes

Date Time

Broad range of date/time formats

Broad range of date/time formats

Broad range of date/time formats

Broad range of date/time formats

Broad range of date/time formats

Scalability/SMP

Each connection uses one CPU, but spread the processes of different connections across available CPUs

SMP is supported by using OS threads. All connections are executed in one thread, queries are not using multiple CPUs

Yes, embedded engine does all work in client threads

Multithread listener, but execute queries one at a time

Load Balancing

Select queries are distributed among the master and slave servers in random manner. Other queries are executed on master and replicated to slaves

MySQL clusters - handle transactions in a round robin manner

N/A

N/A

N/A

Replication

Slony1 replication solution, not core

Master-Slave replication and Chained replication

Master-Slave replication with manual fail-over

N/A

N/A

Online Backup

Write-ahead log and PostgreSQL point-in-time recovery, also Hot Standby System

Online Backup with Mysqldump, InnoDB Hot Backup (commercial add-on), MySQL Clusters commited log

Yes - Online backup utility

Yes - backup manually data files and redo logs

Yes - built-in tool

Cluster

PGCluster

in-memory, shared-nothing, replicatied to 4 replicas. If 1 node fails, currenct transaction fails even all other nodes survive

N/A

N/A

Yes?

Ease of Embedding in Java-based Application

Needs a lot of customizations

Can use MySQL Embedded

Almost no customization requires

Almost no customization requires

Almost no customization requires, and run as server after embedded

Performance comparison
There is a published page about the performance comparison between PostgreSQL, MySQL, Derby, HSQLDB, and H2.
http://www.h2database.com/html/performance.html

History of Derby from Wikipedia
Apache Derby originated at Cloudscape Inc, an Oakland, California start-up founded in 1996 to develop Java database technology. The first release of the database engine, then called JBMS, was in 1997. Subsequently the product was renamed Cloudscape and releases were made about every six months.

In 1999 Informix Software, Inc., acquired Cloudscape, Inc. In 2001 IBM acquired the database assets of Informix Software, including Cloudscape. The database engine was re-branded to IBM Cloudscape and releases continued, mainly focusing on embedded use with IBM's Java products and middleware.

In August 2004 IBM contributed the code to the Apache Software Foundation as Derby, an incubator project sponsored by the Apache DB project. In July 2005 the Derby project graduated from the Apache incubator and is now being developed as a sub-project of the DB Top Level Project at Apache. Prior to Derby's graduation from incubation, Sun joined the Derby project with an intent to use Derby as a component in their own products, and with the release of Java 6 in December 2006, Sun started packaging Derby in the JDK branded as Java DB.

In March 2007 IBM announced that they would withdraw marketing and support for the Cloudscape product, but would continue to contribute to the Apache Derby project.

Conclusion
My own findings and choice of preferences:
1) H2
2) Derby
3) HSQLDB
4) MySQL
5) PostgreSQL
If Java is the programming language of choice for PRP, then H2 or Derby should be the most suitable choice from my opinion since they satisfied most of the selection criteria and also Derby is Java DB which is the standard for Java embedded database. However the only disadvantage of Derby is that under embedded mode it cannot run as server so may cause some problem later on, on the other hand H2 offers the exact functionality and can be run as server even when embedded in application.