How Clickhouse changed the way I design platforms

The junior in me wanted to put every user data in a transactional database, that included the core platform data (users, first class objects ...), the transactions (purchases, ...) and the events (user has read a blog post, reacted to a topic...), and by the time my career started the designated database was MSSQL. In the same career progression came the senior in me who wanted to overcomplicate things, use micro-services for every need that did not require that aggravated expansion, similarly for over simplification, over complication comes with its tool picking misfits, and I found myself using a mix of Cassandra, PostgreSQL, MongoDB for a distributed events storage. That was before I learned about Clickhouse.

What I have just described is the choice between a row based and a column based database when it comes to storing analytics, and column databases are a far better pick, and the most relevant to today's architectural needs is Clickhouse.

ClickHouse is an open-source column-oriented DBMS (columnar database management system) for online analytical processing (OLAP) that allows users to generate analytical reports using SQL queries in real-time.

From complex system design to an afterthought: delaying data design complexity bottleneck

For all its bells and whistles, Clickhouse primarily offers a different approach to storing data that actually fits high efficiency analytics, for both storage and fast retrieval, at extremely large scale (we are talking petabytes of data). That's the money shot: store the events your platform produces and do not worry about failing performance at humongous scale. But it does not stop there, there is a shift in the way a backend engineer designs his data stores that radically changes opposite to the way he would with a row based database: instead of building more abstract fields and joint tables, he simply focuses on storing a pure event and forget about it. The usage will dictate the way he structures the data he will later retrieve. The abstraction has just moved from the rigid design phase to the moment we retrieve and present the data. And it tremendously simplifies the development of complex platforms.

This is only new for developers like me who never used databases like Google BigQuery, but due to its open source nature and its taunted superior performance, Clickhouse makes this capability available to the regular backend developer.

Hybrid storage for an efficient yet performant analytics storage

Clickhouse allows you to store your data either on a PVC or a distant S3. This flexible setting comes handy for different scenarios:

A frugal startup or scale up that wants to store a slew of analytics yet wants to keep their bills on control

Apps that want to have most recent data retrieved quickly, but can also retrieve archived or old events with an acceptable delay, all without changing their code

Tooling

Local development

In localhost I use a Docker image alongside tabix to view and edit the Clickhouse instance; it is open source and I strongly suggest it.

clickhouse:
    image: bitnami/clickhouse:latest
    ports:
      - 9034:9000
      - 8123:8123
    environment:
      ALLOW_EMPTY_PASSWORD: "yes"
      CLICKHOUSE_ADMIN_USER: "<%= CLICKHOUSE_ADMIN_USER %>"
      CLICKHOUSE_ADMIN_PASSWORD: "<%= CLICKHOUSE_ADMIN_PASSWORD %>"
      # CLICKHOUSE_HTTP_PORT: "<%= CLICKHOUSE_HTTP_PORT %>"
      # CLICKHOUSE_TCP_PORT: "<%= CLICKHOUSE_TCP_PORT %>"
    volumes:
      - ./clickhouse:/bitnami/clickhouse
  tabix:
    image: spoonest/clickhouse-tabix-web-client
    ports:
      - 8391:80
    depends_on:
      - clickhouse

Production - Kubernetes

In a gitops environment these are minimal values values you can use to configure your Clickhouse instance.

clickhouse:
  enabled: true
  shards: 1
  replicaCount: 1

  # Longhorn persistence for hot data
  persistence:
    enabled: true
    size: 3Gi

  extraEnvVars:
    - name: CLICKHOUSE_S3_ACCESS_KEY_ID
      valueFrom:
        secretKeyRef:
          name: clickhouse-s3-credentials
          key: access-key-id
    - name: CLICKHOUSE_S3_SECRET_ACCESS_KEY
      valueFrom:
        secretKeyRef:
          name: clickhouse-s3-credentials
          key: secret-access-key

Of course you'd have this secret configured in the same namespace:

apiVersion: v1
kind: Secret
metadata:
  name: clickhouse-s3-credentials
  namespace: default
type: Opaque
stringData:
  access-key-id: <MY_ACCESS_KEY_ID>
  secret-access-key: <MY_SECRET_ACCESS_KEY>

Then in your Chart.yaml

apiVersion: v2
name: default
description: A Helm chart for Kubernetes

dependencies:  
  - name: clickhouse
    alias: clickhouse
    version: 9.x
    repository: https://charts.bitnami.com/bitnami
    condition: clickhouse.enabled
type: application
version: 0.1.0
appVersion: 1.0

Then in your backend deployment you can use the clickhouse service:

- name: CLICKHOUSE_ADMIN_USER
  value: "{{ .Values.clickhouse.auth.username }}"
- name: CLICKHOUSE_ADMIN_PASSWORD
  value: "{{ .Values.clickhouse.auth.password }}"
- name: CLICKHOUSE_HOST
  value: "http://{{ .Release.Name }}-clickhouse:8123"

Voilà, install dependencies and you'll have a running Clickhouse in production.

Bonus: clickhouse as a recommendation system

When you think about it Clickhouse contains all the information needed to build a highly personalised recommendation system. It knows what the user clicked on, what he likes, dislikes, follows, what it follows subscribed to (graph) but it needs a little help in the form of an LLM that will add a bit of intelligence in the algorithm. I will explore this later on a dedicated series. Stay tuned.