From: Martin Gregorie on
On Sun, 22 Nov 2009 15:10:32 -0800, Roedy Green wrote:

> On Sat, 21 Nov 2009 18:59:04 +0000 (UTC), Martin Gregorie
> <martin(a)address-in-sig.invalid> wrote, quoted or indirectly quoted
> someone who said :
>
>>How do you rate H2 against Derby and HSQL?
>
> For a comparison of PosGreSQL and MySQL see
> http://mindprod.com/jgloss/postgresql.html
>
> The information is a few years old.
>
> I understand that Derby is fast, but ram resident only. It is for small
> databases only.
>
I'm interested finding out if Derby, H2 or HSQL could be used as a
lighter weight, portable alternative to PostgreSQL, i.e. minimal changes
to SQL queries and schema, full ACID capability, capable of dealing with
fairly large tables on disk.

From what you say it looks like Derby wouldn't fit the bill.


--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |
From: Lew on
Martin Gregorie wrote, quoted or indirectly quoted someone who said :
>> How do you rate H2 against Derby and HSQL?

Roedy Green wrote:
> For a comparison of PosGreSQL [sic] and MySQL see
> http://mindprod.com/jgloss/postgresql.html
>
> The information is a few years old.

And mentions nothing about Derby or HSQL.

<http://troels.arvin.dk/db/rdbms/>
has a fairly comprehensive comparison of various RDBMSes, but also fails to
mention Derby and HSQL. It has a rather more comprehensive comparison between
PG and MySQL than the mindprod site.

I dispute mindprod's claims that MySQL is "easier to set up" than PG, and
while there may be more books and tutorials on MySQL than PostgreSQL, there is
plenty of information available about the latter so one will not feel the lack.

> I understand that Derby is fast, but ram [sic] resident only. It is for
> small databases only.

Derby is not RAM resident only.
<http://db.apache.org/derby/papers/pageformats.html>
"Derby stores table and index data in Containers, which currently map to files
in the seg0 directory of the database. In the current Derby implementation
there is a 1 to 1 mapping of containers to files."

<http://db.apache.org/derby/docs/10.5/devguide/>
"A Derby database is stored in files that live in a directory of the same name
as the database. "

As for the size of the database, that is shown in the Developer's Guide at the
above link also:
> rows in each table No limit.
> size of table No limit.
> Some operating systems impose a limit on the size of a single file.
> size of row No limit.
> Rows can span pages. Rows cannot span tables so some operating systems
> impose a limit on the size of a single file, which results in limiting the
> size of a table and size of a row in that table.

How well Derby performs at larger sizes is a whole 'nother question, natch.

The documentation has a lot of advice on how to tune Derby performance.

--
Lew
From: Lew on
Roedy Green wrote:
>> I understand that Derby is fast, but ram resident only. It is for small
>> databases only.

Martin Gregorie wrote:
> I'm interested finding out if Derby, H2 or HSQL could be used as a
> lighter weight, portable alternative to PostgreSQL, i.e. minimal changes
> to SQL queries and schema, full ACID capability, capable of dealing with
> fairly large tables on disk.
>
> From what you say it looks like Derby wouldn't fit the bill.

What he says is wrong.

Derby is highly SQL compliant, not RAM resident, and ACID compliant.
<http://db.apache.org/derby/docs/10.5/devguide/cdevconcepts15366.html>

Table size is limited only by the OS's file-size limit.

Check the facts.

--
Lew
From: Arne Vajhøj on
Roedy Green wrote:
> On Sat, 21 Nov 2009 12:10:06 -0500, Arne Vajh�j <arne(a)vajhoej.dk>
> wrote, quoted or indirectly quoted someone who said :
>> SUN did not provide you MySQL. SUN just acquired the company
>> not long ago. And have lost most of the original people since then.
>
> You can thank Oracle for continuing to provide Java free in the same
> sense you can thank Sun for continuing to provide MySQL free.

They are about equivalent in that.

Neither have much of a choice.

MySQL is GPL. Java specs are JCP and Java RI is GPL and
GPL with classpath exception.

Arne
From: Arne Vajhøj on
Roedy Green wrote:
> On Sat, 21 Nov 2009 18:59:04 +0000 (UTC), Martin Gregorie
> <martin(a)address-in-sig.invalid> wrote, quoted or indirectly quoted
> someone who said :
>> How do you rate H2 against Derby and HSQL?
>
> For a comparison of PosGreSQL and MySQL see
> http://mindprod.com/jgloss/postgresql.html
>
> The information is a few years old.

#PostGres has many data types.

Most databases has.

#You can write triggers in Oracle stored procedure language PL/SQL or in
other languages such as Java.

http://www.postgresql.org/docs/8.2/interactive/triggers.html

does not list Java but several other languages.

And even though PL/pgSQL is very similar to Oracle PL/SQL, then
I assume that Oracle prefer it not to be called Oracle PL/SQL.

> I understand that Derby is fast, but ram resident only.

Not true.

> It is for
> small databases only.

Probably.

Arne