Testing Columnar Storage
Tags: docker, python, programming
Published: 2024-06-16 16:20:33

<p>As most of you probably already know, since approximately the end of 2022 InterSystems IRIS included the columnar storage functionality to its database, well, in today's article we are going to put it to the test in comparison to the usual row storage.</p>
<h2>Columnar Storage</h2>
<p>What is the main characteristic of this type of storage? Well, if we consult the <a href="http://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSOD_storage" target="_blank">official documentation</a> we will see this fantastic table that explains the main characteristics of both types of storage (by rows or by columns):</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lozc4xdxl0w0vx1o58tr.png)
<p>As you can see, columnar storage is designed primarily for analytical tasks in which queries are launched against specific fields in our table, while row storage is more optimal when a large number of insertion, update and deletion operations are required. as well as obtaining complete records.</p>
<p>If you continue reading the documentation you will see how simple it is to configure our table to be able to use columnar storage:</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">table</span> (<span class="hljs-keyword">column</span> <span class="hljs-keyword">type</span>, column2 type2, column3 type3) <span class="hljs-keyword">WITH</span> STORAGETYPE = COLUMNAR</code></pre>
<p>Using this command we would be defining all the columns of our table with columnar storage, but we could opt for a mixed model in which our table has row storage but certain columns make use of columnar storage.</p>
<p>This mixed scenario could be interesting in cases where aggregation operations such as sums, averages, etc. are common. For this case we could define which column is the one that will use said storage:</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">table</span> (<span class="hljs-keyword">column</span> <span class="hljs-keyword">type</span>, column2 type2, column3 type3 <span class="hljs-keyword">WITH</span> STORAGETYPE = COLUMNAR)</code></pre>
<p>In the previous example we defined a table with row storage and a column (column3) with columnar storage.</p>
<h2>Comparative</h2>
<p>To compare the time spent by column storage and row storage in different queries, we have created a small exercise using Jupyter Notebook that will insert a series of records that we will generate in two tables, the first with storage with rows ( Test.PurchaseOrderRow) and the second with columnar storage in two of its columns (Test.PurchaseOrderColumnar)</p>
<h4>Test.PurchaseOrderRow</h4>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> Test.PurchaseOrderRow (
    <span class="hljs-keyword">Reference</span> <span class="hljs-built_in">INTEGER</span>,
    Customer <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">225</span>),
    PaymentDate <span class="hljs-built_in">DATE</span>,
    Vat <span class="hljs-built_in">NUMERIC</span>(<span class="hljs-number">10</span>,<span class="hljs-number">2</span>),
    Amount <span class="hljs-built_in">NUMERIC</span>(<span class="hljs-number">10</span>,<span class="hljs-number">2</span>),
    <span class="hljs-keyword">Status</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">10</span>))</code></pre>
<h4>Test.PurchaseOrderColumnar</h4>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> Test.PurchaseOrderColumnar (
    <span class="hljs-keyword">Reference</span> <span class="hljs-built_in">INTEGER</span>,
    Customer <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">225</span>),
    PaymentDate <span class="hljs-built_in">DATE</span>,
    Vat <span class="hljs-built_in">NUMERIC</span>(<span class="hljs-number">10</span>,<span class="hljs-number">2</span>),
    Amount <span class="hljs-built_in">NUMERIC</span>(<span class="hljs-number">10</span>,<span class="hljs-number">2</span>) <span class="hljs-keyword">WITH</span> STORAGETYPE = COLUMNAR,
    <span class="hljs-keyword">Status</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">WITH</span> STORAGETYPE = COLUMNAR)</code></pre>
<p>If you download the Open Exchange project and deploy it in your local Docker, you can access the Jupyter Notebook instance and review the file <strong>PerformanceTests.ipynb</strong>, which will be responsible for generating the random data that we are going to store in different phases in our tables&nbsp;and finally it will show us a graph with the performance of the query operations.</p>
<p>Let's take a quick look at our project configuration:</p>
<h4>docker-compose.yml</h4>
<pre class="codeblock-container" idlang="4" lang="YAML" tabsize="4"><code class="language-yaml hljs"><span class="hljs-attr">version:</span> <span class="hljs-string">'3.7'</span>
<span class="hljs-attr">services:</span>
  <span class="hljs-comment"># iris</span>
<span class="hljs-attr">  iris:</span>
<span class="hljs-attr">    init:</span> <span class="hljs-literal">true</span>
<span class="hljs-attr">    container_name:</span> <span class="hljs-string">iris</span>
<span class="hljs-attr">    build:</span>
<span class="hljs-attr">      context:</span> <span class="hljs-string">.</span>
<span class="hljs-attr">      dockerfile:</span> <span class="hljs-string">iris/Dockerfile</span>
<span class="hljs-attr">    ports:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-number">52774</span><span class="hljs-string">:52773</span>
<span class="hljs-bullet">      -</span> <span class="hljs-number">51774</span><span class="hljs-string">:1972</span>
<span class="hljs-attr">    volumes:</span>
<span class="hljs-bullet">    -</span> <span class="hljs-string">./shared:/shared</span>
<span class="hljs-attr">    environment:</span>
<span class="hljs-bullet">    -</span> <span class="hljs-string">ISC_DATA_DIRECTORY=/shared/durable</span>
<span class="hljs-attr">    command:</span> <span class="hljs-bullet">--check-caps</span> <span class="hljs-literal">false</span> <span class="hljs-bullet">--ISCAgent</span> <span class="hljs-literal">false</span>
  <span class="hljs-comment"># jupyter notebook</span>
<span class="hljs-attr">  jupyter:</span>
<span class="hljs-attr">    build:</span>
<span class="hljs-attr">      context:</span> <span class="hljs-string">.</span>
<span class="hljs-attr">      dockerfile:</span> <span class="hljs-string">jupyter/Dockerfile</span>
<span class="hljs-attr">    container_name:</span> <span class="hljs-string">jupyter</span>
<span class="hljs-attr">    ports:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-string">"8888:8888"</span>
<span class="hljs-attr">    environment:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-string">JUPYTER_ENABLE_LAB=yes</span>
<span class="hljs-bullet">      -</span> <span class="hljs-string">JUPYTER_ALLOW_INSECURE_WRITES=true</span>
<span class="hljs-attr">    volumes:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-string">./jupyter:/home/jovyan</span>
<span class="hljs-bullet">      -</span> <span class="hljs-string">./data:/app/data</span>
<span class="hljs-attr">    command:</span> <span class="hljs-string">"start-notebook.sh --NotebookApp.token='' --NotebookApp.password=''"</span> </code></pre>
<p>We deploy the IRIS and Jupyter containers in our docker, initially configuring IRIS with the namespace "TEST" and the two tables required for the test.</p>
<p>To avoid boring you with code, you can consult the <strong>PerformanceTests.ipynb</strong> file from which we will connect to IRIS, generate the records to be inserted and store them in IRIS</p>
<h2>Test execution</h2>
<p>The results have been the following (in seconds):</p>
<h3>Inserts:</h3>
<p>The insertions made are of bulk type:</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> Test.PurchaseOrderColumnar (<span class="hljs-keyword">Reference</span>, Customer, PaymentDate, Vat, Amount, <span class="hljs-keyword">Status</span>) <span class="hljs-keyword">VALUES</span> (?, ?, ?, ?, ?, ?)</code></pre>
<p>And the time for each batch of inserts is as follows:</p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td>
<p style="text-align: center;">Total inserts</p>
</td>
<td style="text-align: center;">Row storage</td>
<td style="text-align: center;">Mixed storage</td>
</tr>
<tr>
<td style="text-align: center;">1000</td>
<td style="text-align: center;">
<p>0.031733</p></td>
<td style="text-align: center;">
<p>0.041677</p></td>
</tr>
<tr>
<td style="text-align: center;">5000</td>
<td style="text-align: center;">
<p>0.159338</p></td>
<td style="text-align: center;">
<p>0.185252</p></td>
</tr>
<tr>
<td style="text-align: center;">20000</td>
<td style="text-align: center;">
<p>0.565775</p></td>
<td style="text-align: center;">
<p>0.642662</p></td>
</tr>
<tr>
<td style="text-align: center;">50000</td>
<td style="text-align: center;">
<p>1.486459</p></td>
<td style="text-align: center;">
<p>1.747124</p></td>
</tr>
<tr>
<td style="text-align: center;">100000</td>
<td style="text-align: center;">
<p>2.735016</p></td>
<td style="text-align: center;">
<p>3.265492</p></td>
</tr>
<tr>
<td style="text-align: center;">200000</td>
<td style="text-align: center;">
<p>5.395032</p></td>
<td style="text-align: center;">
<p>6.382278</p></td>
</tr>
</tbody></table>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/arf5m31p462q58ke6m8t.png)
<h3>Selects:</h3>
<p>The Select launched includes an aggregation function and a condition, both on columns with columnar storage:</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">AVG</span>(Amount) <span class="hljs-keyword">FROM</span> Test.PurchaseOrderColumnar <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">Status</span> = <span class="hljs-string">'SENT'</span></code></pre>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td style="text-align: center;">
<p>Total rows</p></td>
<td style="text-align: center;">Row storage</td>
<td style="text-align: center;">Mixed storage</td>
</tr>
<tr>
<td style="text-align: center;">1000</td>
<td style="text-align: center;">
<p>0.002039</p></td>
<td style="text-align: center;">
<p>0.001178</p></td>
</tr>
<tr>
<td style="text-align: center;">5000</td>
<td style="text-align: center;">
<p>0.00328</p></td>
<td style="text-align: center;">
<p>0.000647</p></td>
</tr>
<tr>
<td style="text-align: center;">20000</td>
<td style="text-align: center;">
<p>0.005493</p></td>
<td style="text-align: center;">
<p>0.001555</p></td>
</tr>
<tr>
<td style="text-align: center;">50000</td>
<td style="text-align: center;">
<p>0.016616</p></td>
<td style="text-align: center;">
<p>0.000987</p></td>
</tr>
<tr>
<td style="text-align: center;">100000</td>
<td style="text-align: center;">
<p>0.036112</p></td>
<td style="text-align: center;">
<p>0.001605</p></td>
</tr>
<tr>
<td style="text-align: center;">200000</td>
<td style="text-align: center;">
<p>0.070909</p></td>
<td style="text-align: center;">
<p>0.002738</p></td>
</tr>
</tbody></table>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v9m18alggfh5e24br5j9.png)
<h2>Conclusions</h2>
<p>As you can see in the results obtained, the operation is exactly what is indicated in the documentation. Including columns with columnar storage has slightly penalized performance during insert (about 18% slower for our example) while queries on those same columns have dramatically improved response time (258 times faster).</p>
<p>It is undoubtedly something to take into account when planning the development of any application.</p>