Storage

Data storage options can be tuned for each column similar to how indexing is defined.

Column store

Beside of storing the row data as-is (and indexing each value by default), each value term is stored into a Column Store by default. The usage of a Column Store is greatly improving global aggregations and groupings and enables ordering possibility as the data for one column is packed at one place. Using the Column Store limits the values of TEXT columns to a maximal length of 32766 bytes.

Turning off the Column Store in conjunction of turning off indexing will remove the length limitation.

Example:

cr> CREATE TABLE t1 (
...   id INTEGER,
...   url TEXT INDEX OFF STORAGE WITH (columnstore = false)
... );
CREATE OK, 1 row affected  (... sec)

Doing so will enable support for inserting strings longer than 32766 bytes into the url column, but the performance for global aggregations, groupings and sorting using this url column will decrease.

Note

INDEX OFF and therefore columnstore = false cannot be used with partition columns, as those are not stored as normal columns of a table.

Supported data types

Controlling if values are stored into a Column Store is only supported on following data types:

• TEXT

• Numeric data

• TIMESTAMP

• WITH TIME ZONE

For all other Primitive types and Geometric points it is enabled by default and cannot be disabled. Container types and Geometric shapes do not support storing values into a Column Store at all.