Does H2 support column compression?

[Yong Zhao]

I am using H2 as in memory database and I have a table with 6 columns and millions of rows . There are two rows has very low cardinality. But I can not know all the values of these column while I create the table, so can not use ENUM type.

Want to check with you guys if H2 can do some dictionary encoding on these columns? Or event internally store the data in a columnar format ?

Thank You

Yong

[Evgenij Ryazanov]

Hello.

It sounds like your database is not normalized properly. I suggest you to read a some book about database design or at least some articles about database normalization.

For your specific use case you need to extract all possible values for each such column into own table that has a column with integer primary key and a column with a string value. In the main table these string columns should be replaced with integer columns with foreign key references to the new tables. This area is under responsibility of database engineers.

[Yong Zhao]

Thanks for you reply Evgenij.

My point is, this database is been used to do analysis, and the value are dynamically added . I only know the value's carnality wont be more than 300, 

but I do know the distinct values. I dump data into the db every day, the data is from customer. I am doing some aggregation on it.

It will be good the db can compress the column by default but seems that is not the case.

Thank You

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/368d26d8-8546-4b66-b5be-25c038de2fa6%40googlegroups.com.

[Evgenij Ryazanov]

You can enable the compression only for the whole database, but it can reduce overall performance.

[Noel Grandin]

No, we don't do columnar compression.