Storing UUIDs in HSQLDB Database

Question

I want to save the UUIDs created using java.util.UUID in the HSQLDB database.

The obvious option is to simply store them as strings (in the code, they will probably just be treated as such), i.e. varchar (36).

What other options should I consider for this, given issues such as database size and query speed (none of which cause much concern because of the large amount of data, but I would like to consider them at least)

Answer 1

You have several options:

• Save it as VARCHAR (36), as you said. It will take 36 bytes (288 bits) of memory per UUID, not counting the overhead.
• Store each UUID in two BIGINT columns, one for the least significant bits and one for the most significant bits; use UUID # getLeastSignificantBits () and UUID # getMostSignificantBits () to capture each part and save it properly. It takes 128 bits of storage per UUID, not counting any overhead.
• Store each UUID as an OBJECT object; this saves it as a binary serialized version of the UUID class. I have no idea how much space this takes; I need to run a test to find out what the standard serialized form of Java UUID is.

The top and bottom sides of each approach are based on how you pass the UUIDs in your application - if you pass them as your string equivalents, then the flaw requires a double storage capacity VARCHAR (36) will probably outweigh without reinstalling them every time You are executing a query or updating a database. If you pass them as native UUIDs then the BIGINT method is likely to be pretty low. Upstairs.

Oh, and it's nice that you want to consider questions about speed and storage space, but as many of them, as I said, it’s also good that you acknowledge that this may not be critical, given the amount of data your application will stored and maintained. As always, micro-optimization for performance’s sake is important only if it doesn’t lead to unacceptable cost or performance. Otherwise, these two problems — the UUID memory space and the time required to service and query the database — are of rather low importance, given the cheap storage cost and the ability of database indexes to make your life much easier. :)

Answer 2

• I would recommend char(36) instead of varchar(36) . Not sure about hsqldb, but in many DBMSs char works a little faster.

• To search if the DBMS is smart, you can use an integer value to "get closer" to your UUID.

For example, add an int column to your table, as well as char (36). When you insert into the table, insert uuid.hashCode () in the int column. Then your searches may be like this

WHERE intCol = ? and uuid = ?

As I said, if hsqldb is as smart as mysql or sql server, it will narrow the search by intCol and then will compare no more than a few values ​​using uuid. We use this trick to search millions of records tables in rows, and it is essentially as fast as searching in integers.

Answer 3

Using BINARY (16) is another possibility. Less disk space than character types. Use CREATE TYPE UUID .. or CREATE DOMAIN UUID .. as suggested above.

Answer 4

HSQLDB has a built-in UUID type. Use it

 CREATE TABLE t ( id UUID PRIMARY KEY ); 

Answer 5

I think the easiest way would be to create your own domain, thus creating your own type of UUID (not quite a type, but almost).

You should also consider the answer to this question (especially if you plan to use it instead of the "regular" primary key)

INT, BIGINT or UUID / GUID in HSQLDB?

HSQLDB: creating and manipulating a domain