Skip to main content

Fast Analytics with MariaDB ColumnStore

·1264 words·6 mins
Alejandro Duarte
Alejandro Duarte
Alejandro Duarte is a Software Engineer, published author, and award winner. He currently works for MariaDB plc as a Developer Relations Engineer. Starting his coding journey at 13 with BASIC on a rudimentary black screen, Alejandro quickly transitioned to C, C++, and Java during his academic years at the National University of Colombia. Relocating first to the UK and then to Finland, Alejandro deepened his involvement in the open-source community. He’s a recognized figure in Java circles, credited with articles and videos amassing millions of views, and presentations at international events.

Slow query times in large datasets are a common headache in database management. MariaDB ColumnStore offers a neat way out of this. It’s a columnar storage engine that significantly speeds up data analytics. Typically, you can improve query performance in relational databases by adding appropriate indexes. However, maintaining indexes is hard, especially with ad-hoc queries where you don’t really know where indexes are going to be needed. ColumnStore eases this pain. It’s as if you had an index on each column, but without the hassle of creating and updating them. The price to pay? Well, inserts are not as fast as with InnoDB, so this is not the best option for operational/transactional databases but rather for analytical ones. Bulk inserts are very fast though.

There’s plenty of online documentation about ColumnStore, so I won’t go through all the details on how it works or how to deploy it on production. Instead, in this article, I’ll show you how to try MariaDB ColumnStore on your computer using Docker.


You’ll need:

Setting up MariaDB ColumnStore

  1. Run a container with MariaDB + ColumnStore:
docker run -d -p 3307:3306 -e PM1=mcs1 --hostname=mcs1 --name mcs1 mariadb/columnstore

This command runs a new Docker container using the official ColumnStore image, with several specified options:

  • docker run: Starts a new Docker container.
  • -d: Runs the container in detached mode (in the background).
  • -p 3307:3306: Maps port 3307 on the host (your computer) to port 3306 inside the container. This makes the database accessible on port 3307 on the host machine.
  • -e PM1=mcs1: The PM1 environment variable PM1 specifies the primary database node (mcs1).
  • --hostname=mcs1: Sets the hostname of the container to mcs1.
  • --name mcs1: Names the container mcs1.
  • mariadb/columnstore: Specifies the Docker image to use, in this case, an image for MariaDB with the ColumnStore storage engine.
  1. Provision ColumnStore:
docker exec -it mcs1 provision mcs1

The command docker exec is used to interact with a running Docker container. This is what each option does:

  • docker exec: Executes a command in a running container.
  • -it: This option ensures the command is run in interactive mode with a terminal.
  • mcs1 (first occurrence): This is the name of the Docker container in which the command is to be executed.
  • provision mcs1 This is the specific command being executed inside the container. provision is a script included in the Docker image that initialize and configure the MariaDB ColumnStore environment within the container. The argument mcs1 is passed to the provision command to specify the host for the MariaDB server within the Docker container.
  1. Connect to the MariaDB server using the default credentials defined in the MariaDB ColumnStore Docker image:
mariadb -h -P 3307 -u admin -p'C0lumnStore!'
  1. Check that ColumnStore is available as a storage engine by running the following SQL sentence:

Setting up a demo database

  1. Create the operations database and its InnoDB tables:

CREATE TABLE operations.doctors(
    name VARCHAR(200) NOT NULL CHECK(TRIM(name) != '')

CREATE TABLE operations.appointments(
    name VARCHAR(200) NOT NULL CHECK(TRIM(name) != ''),
    phone_number VARCHAR(15) NOT NULL CHECK(phone_number RLIKE '[0-9]+'),
    email VARCHAR(254) NOT NULL CHECK(TRIM(email) != ''),
    reason ENUM('Consultation', 'Follow-up', 'Preventive', 'Chronic') NOT NULL,
    status ENUM ('Scheduled', 'Canceled', 'Completed', 'No Show'),
    CONSTRAINT fk_appointments_doctors FOREIGN KEY (doctor_id) REFERENCES doctors(id)
  1. Create the analytics database and its ColumnStore table:

CREATE TABLE analytics.appointments(
    name VARCHAR(200) NOT NULL,
    phone_number VARCHAR(15) NOT NULL,
    email VARCHAR(254) NOT NULL,
    reason VARCHAR(15) NOT NULL,
    status VARCHAR(10) NOT NULL,
) ENGINE=ColumnStore;

You can use the same database (or schema, they are synonyms in MariaDB) for both the InnoDB and ColumnStore tables if you prefer. Use a different name for the ColumnStore table if you opt for this alternative.

Inserting demo data

  1. Insert a few doctors:
INSERT INTO operations.doctors(name)
VALUES ("Maria"), ("John"), ("Jane");
  1. Create a new file with the name with the following content:
import random
import os
import subprocess
from datetime import datetime, timedelta

# Function to generate a random date within a given range
def random_date(start, end):
    return start + timedelta(days=random.randint(0, int((end - start).days)))

# Function to execute a given SQL command using MariaDB
def execute_sql(sql):
    # Write the SQL command to a temporary file
    with open("temp.sql", "w") as file:
    # Execute the SQL command using the MariaDB client["mariadb", "-h", "", "-P", "3307", "-u", "admin", "-pC0lumnStore!", "-e", "source temp.sql"])
    # Remove the temporary file

print("Generating and inserting data...")

# Total number of rows to be inserted
total_rows = 4000000
# Number of rows to insert in each batch
batch_size = 10000

# Possible values for the 'reason' column and their associated weights for random selection
reasons = ["Consultation", "Follow-up", "Preventive", "Chronic"]
reason_weights = [0.5, 0.15, 0.25, 0.1]

# Possible values for the 'status' column and their associated weights for random selection
statuses = ["Scheduled", "Canceled", "Completed", "No Show"]
status_weights = [0.1, 0.15, 0.7, 0.05]

# Possible values for the 'doctor_id' column and their associated weights for random selection
doctors = [1, 2, 3]
doctors_weights = [0.4, 0.35, 0.25]

# List of patient names
names = [f"Patient_{i}" for i in range(total_rows)]

# Insert data in batches
for batch_start in range(0, total_rows, batch_size):
    batch_values = []

    # Generate data for each row in the batch
    for i in range(batch_start, min(batch_start + batch_size, total_rows)):
        name = names[i]
        phone_number = f"{random.randint(100, 999)}-{random.randint(100, 999)}-{random.randint(1000, 9999)}"
        email = f"patient_{i}"
        time = random_date(datetime(2023, 1, 1), datetime(2024, 1, 1)).strftime("%Y-%m-%d %H:%M:%S")
        reason = random.choices(reasons, reason_weights)[0]
        status = random.choices(statuses, status_weights)[0]
        doctor_id = random.choices(doctors, doctors_weights)[0]

        # Append the generated row to the batch
        batch_values.append(f"('{name}', '{phone_number}', '{email}', '{time}', '{reason}', '{status}', {doctor_id})")

    # SQL command to insert the batch of data into the 'appointments' table
    sql = "USE operations;\nINSERT INTO appointments (name, phone_number, email, time, reason, status, doctor_id) VALUES " + ", ".join(batch_values) + ";"
    # Execute the SQL command
    # Print progress
    print(f"Inserted up to row {min(batch_start + batch_size, total_rows)}")

print("Data insertion complete.")
  1. Insert 4 million appointments by running the Python script:
  1. Populate the ColumnStore table by connecting to the database and running:
INSERT INTO analytics.appointments (
FROM operations.appointments;

Run cross-engine SQL queries

MariaDB ColumnStore is designed to run in a cluster of multiple servers. It is there where you see massive performance gains in analytical queries. However, we can also see this in action with the single-node setup of this article.

  1. Run the following query and pay attention to the time it needs to complete (make sure it queries the operations database):
SELECT, status, COUNT(*) AS count
FROM operations.appointments -- use the InnoDB table
JOIN doctors ON doctor_id =
WHERE status IN (
    'No Show'
GROUP BY, status
ORDER BY, status;

On my machine, it took around 3 seconds.

  1. Now modify the query to use the ColumnStore table instead (in the analytics database):
SELECT, status, COUNT(*) AS count
FROM analytics.appointments -- use the ColumnStore table
JOIN doctors ON doctor_id =
WHERE status IN (
    'No Show'
GROUP BY, status
ORDER BY, status;

It takes less than a second. Of course, you can speed up the first query by adding an index in this simplistic example, but imagine the situation in which you have hundreds of tables—it will become harder and harder to manage indexes. ColumnStore removes this complexity.


What is MariaDB?
·346 words·2 mins
SQL Databases
MariaDB is an open-source relational database management system that uses the Structured Query Language (or SQL) to manage and manipulate data.
My experience in Latin America presenting the evolution of MariaDB
·584 words·3 mins
Events Databases
Last week, I had the pleasure of giving a talk at the open source event organized by our partner Imagunet in Colombia.
My experience at Cloud Expo Europe - London 2023
·509 words·3 mins
Events Databases Developer Relations
I recently had the opportunity to attend the Cloud Expo Europe in London with our team at MariaDB, where we promoted SkySQL, our cloud database solution.