How to set up columnar storage in classes

Columnar storage is one of the newer offers provided by InterSystems IRIS. Unlike traditional row-based storage, it optimizes query processing by storing data in columns rather than rows, enabling faster access and retrieval of relevant information.

A couple of articles have been written on when it should be used to give a system the biggest boost, how to create tables like that using SQL

CREATE TABLE table (column1 type1, column2 type2, column3 type3) WITH STORAGETYPE = COLUMNAR  -- ex 1
CREATE TABLE table (column1 type1, column2 type2, column3 type3 WITH STORAGETYPE = COLUMNAR)  -- ex 2

and even the performance tests.

As we all know, InterSystems IRIS is a multi-model DBMS and it gives seamless access to the same data using relational and object access. So the former is covered in other articles, but what about the latter?

It is good to know that the columnar storage layout can also be defined in classes, and there are several ways to do it.

1. If you wish to define the storage for all properties as columnar (example 1 at the top) just add a following parameter to your class:

Parameter STORAGEDEFAULT = "columnar" 

And if we take an example above, we'll get the following class:

Class Post.Address Extends %Persistent [Final]
{
Parameter STORAGEDEFAULT = "columnar";
Parameter USEEXTENTSET=1;
Property City As %String(MAXLEN = 12);
Property ZIP As %String(MAXLEN = 9);
Property Country As %String(MAXLEN = 12);
}

Parameter STORAGEDEFAULT = "columnar" tells the system that all the data has to be stored in the columnar form (meaning each column will have its own global).

Parameter USEEXTENTSET = 1 tells the system to generate more efficient, hashed globals with shorter names.

You can declare any table as columnar. However, tables that use columnar as the default storage layout must specify either the Final class keyword or the NoExtent class keyword, with any immediate subclasses defined explicitly as Final. Otherwise you will get an error during compilation.

2. You can also define only some properties as stored by columns (example 2 at the top). To do this you need to specify the parameter STORAGEDEFAULT = "columnar" for a property.

Class Post.Address Extends %Persistent
{
Parameter STORAGEDEFAULT = "row";
Parameter USEEXTENTSET=1;
Property City As %String(MAXLEN = 12);
Property ZIP As %String(MAXLEN = 9);
Property Country As %String(MAXLEN = 12, STORAGEDEFAULT = "columnar");
}

This way Cities and ZIPs will be stored as usual in a global ^Post.AddressD and Countries will be stored in separate global as a column.

In the case of this example, this would be a much better approach, because if we have a database of different cities in different countries, the number of countries is limited while the number of cities not so much. Besides, there aren't many cases where you would need to do analytics queries on cities unlike on coutries.

I hope this helps you understand how to work with columnar storage in classes. There are quite a few limitations which you need to keep in mind so please read more on the subject here.