• Open Source Databases Comparison
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 12 Next »

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

  • FREE or Open Source
  • Multiple platform support
  • Easy to install and use
  • Can be bundle in code so little or no extra installation is required
  • Can be scalable, i.e. load balancing, size limit, etc.
  • Reliable, i.e. backup, replication, etc.
  • Efficient, i.e. indexing, fast search, etc.

 

PostgreSQL 8.2

MySQL 5.0

Apache Derby 10.3

HSQLDB 1.8

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

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

License

BSD License - Free

Depends - GPL for open source or Commercial for commercial

Apache License v2 - Free

BSD License - Free

Maximum Database Size

Unlimited

Unlimited

Unlimited

Unlimited (depends on memory)

Maximum Number of Tables

Unlimited

Unlimited

Unlimited

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)

Maximum Columns per Table

250 - 1600 depending on column types

1000 columns

1012 columns

Unlimited (depends on memory)

Maximum Indexes per Table

Unlimited

64 indexes

32767 indexes

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

Transactional

ACID-compliant

InnoDB type tables only - ACID-compliant

Yes

?

Bind Variables

Yes

Yes

Yes

?

Stored Procedures

Yes - SQL, PL/Tcl, PL/pgSQL

Yes

Yes

Yes

Trigger

Yes - SQL, PL/Tcl, PL/pgSQL

Yes

Yes

Yes

Row Level Locking

Yes

Yes

Yes

?

Timeout

Yes

No

Yes - deadlock detection and timeout

Yes

Error Code

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

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

N/A

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

Replication

Slony1 replication solution, not core

Master-Slave replication and Chained replication

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

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

Ease of Embedding in Java-based Application

Needs a lot of customizations

Can use MySQL Embedded

Almost no customization requires

Almost no customization requires

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) Derby
2) MySQL
3) HSQLDB
4) PostgreSQL
If Java is the programming language of choice for PRP, then Derby should be the most suitable choice from my opinion since it has already been included in JDK 6 as Java DB and it qualifies most of the selection criteria.

  • No labels