Max rows per table - why 31-bit, compared to "no limit" on other databases?

[Mark A. Ziesemer]

I was just comparing H2 against some other similar (embedded) databases, including Derby, and came across the interesting statistic of maximum "rows per table":

- H2: 2,147,483,648 (2^31, http://www.h2database.com/html/advanced.html#limits_limitations)
- Derby: "No limit" (http://db.apache.org/derby/docs/10.7/devguide/cdevdvlp40724.html)

As I don't have a current and actual need for more than 2^32 rows per table, this is more of an academic question - though I can see desirable and realistic scenarios where more rows would be required.  Why the limit difference between Derby and H2?  If a hard limit is necessary, why 2^31 (Java int, using only +'s) and not 2^63 or 2^64 (Java long)?

Even PostgreSQL (which H2 provides some compatibility with) has "No limit" (http://www.postgresql.org/about/).

Granted, if I actually need to exceed this limit, I'm free to change to another database.  I'm just looking for any insights or documentation to explain the decisions behind the limit (likely for performance reasons?).

Thanks!

[Thomas Mueller]

Hi,

The 2^31 is no longer correct. It was correct in version 1.0 and 1.1,
(before the "page store"), but now it's 2^63. I will update the
documentation.

Regards,
Thomas

[Mark A. Ziesemer]

Good to hear, and thanks for the information.

I do see that the documentation was just corrected, to 2^64. Are all
64 bits really allowed - including negatives when treated as signed?
Or should it have been 2^63 as you responded above?

Thanks again!

[Thomas Mueller]

Hi,

> I do see that the documentation was just corrected, to 2^64.  Are all
> 64 bits really allowed - including negatives when treated as signed?
> Or should it have been 2^63 as you responded above?

It's actually 2^64 (negative values are also allowed for the primary key).

Regards,
Thomas