H2 database: clustered indexes support

Question

I use H2 database for environmental data which contains lots of time series. Time series are simply measurement values of sensors which are recorded in database periodically (say once per hour).

The data stored in the table:

CREATE TABLE hydr
(dt timestamp
,value double
,sensorid int)

I would like to make range queries against the table, for example:

select * from hydr
where dt between '2010-01-01' and '2010-10-01'

In order to improve performance I would like to build clustered index over dt column, but the thing is, that I haven't found if H2 supports clustered indexes. Does anybody know if clustered indexes are supported in H2?

Answer 1

Short answer: the primary key of your table needs to be of type BIGINT:

CREATE TABLE hydr(dt bigint primary key, value double, sensorid int);

In this case, the table is organized using the "dt" column. This is called "clustered index". The data type TIMESTAMP is not supported, mainly because it also contains nanoseconds. What you could do is store the unix timestamp (milliseconds since 1970) as a BIGINT.

Long answer: The documentation of how data is stored internally in H2 is poor. I will add the following section to the "performance" documentation of the H2 database. I hope this will clear up things (if not please tell me):

How Data is Stored Internally

For persistent databases, if a table is created with a single column primary key of type BIGINT, INT, SMALLINT, TINYINT, then the data of the table is organized in this way. This is sometimes also called a "clustered index" or "index organized table".

H2 internally stores table data and indexes in the form of b-trees. Each b-tree stores entries as a list of unique keys (one or more columns) and data (zero or more columns). The table data is always organized in the form of a "data b-tree" with a single column key of type long. If a single column primary key of type BIGINT, INT, SMALLINT, TINYINT is specified when creating the table, then this column is used as the key of the data b-tree. If no primary key has been specified, if the primary key column is of another data type, or if the primary key contains more than one column, then a hidden auto-increment column of type BIGINT is added to the table, which is used as the key for the data b-tree. All other columns of the table are stored within the data area of this data b-tree (except for large BLOB, CLOB columns, which are stored externally).

For each additional index, one new "index b-tree" is created. The key of this b-tree consists of the indexed columns, plus the key of the data b-tree. If a primary key is created after data has been inserted, or if the primary key contains multiple column, or if the primary key is not of the data types listed above, then the primary key is stored in a new index b-tree.