How We Scaled Firewall Logs with ClickHouse at MalCare

I worked on this while I was at BlogVault, on the MalCare side of the product, roughly between 2022 and 2024. I am no longer there. This post is just me sharing what we built and learned during that period. It is based on my own experience and memory, not an official BlogVault or MalCare write-up.

At the time, MalCare's Web Application Firewall ran across more than 500,000 websites. It produced hundreds of billions of firewall request log rows, and we ingested over a billion new rows every day.

This post covers how we moved that data to ClickHouse, how we ran it on modest hardware, and what we learned along the way.

The Problem

Every request that hits a MalCare-protected site gets logged: source IP, site ID, timestamp, request path, action taken, and more.

At our scale this added up to:

• hundreds of billions of rows
• terabytes of raw data
• over a billion new rows per day

We did not only need to store these logs. A post-processing pipeline scanned recent logs to find attack patterns and flag bad actors. So the database had to handle:

1. high write throughput
2. fast reads for queries like "give me logs after timestamp X"

Why We Left MongoDB

These logs used to live in MongoDB.

That stopped being a good fit because:

• most of our queries scanned large amounts of historical data
• writes were mostly append-only
• we did not need document-level transactions for this workload

We looked at column-oriented analytics databases instead. ClickHouse fit the use case well. Others running similar workloads on it also helped give us confidence: Cloudflare for high-volume HTTP analytics,[^1] and Uber for a large log analytics platform.[^2]

What We Learned from ClickHouse's Design

Looking at ClickHouse's architecture was useful on its own, especially when we compared it with MongoDB and MySQL-style engines.

A few ideas stood out:

1. Compress, then write. On insert, ClickHouse sorts a batch, splits it into columns, compresses those blocks, and then writes them to disk. Writing less data is often faster when disk and network are the bottleneck.
2. Append parts, merge later. MergeTree does not need an auto-increment ID on the write path. Each insert becomes a new local part. Background merges combine small parts into larger ones later. That is very different from updating a global B-tree-style index on every write.
3. Prefer simple, typed columns. Fixed-width integers pack well for compression and CPU work. Free-form strings need extra offset metadata and are harder to work with at this scale.

Migrating from MongoDB

We did not switch everything to ClickHouse in one shot. We migrated in stages so MongoDB stayed as a safety net.

1. Dual-write. We started writing every new firewall log row to both MongoDB and ClickHouse. Reads still came from MongoDB. If ClickHouse had write or schema issues, production logging and post-processing were unaffected.
2. Move reads gradually. Once dual-writes looked healthy (matching volumes, good query latency, no surprise disk or CPU pressure), we started moving reads to ClickHouse. Lower-risk paths moved first. Higher-volume paths, including bad-actor detection, moved later.
3. Finish the cutover. After all reads and writes were on ClickHouse, we waited a few weeks. Then we removed the MongoDB write path and the dual-write code.

Dual-running cost us extra disk and write work for a while. For a system that cannot drop a day of firewall logs, that trade-off was worth it.

Schema and Indexing

We used the MergeTree table engine for firewall logs.

Two choices mattered most for query speed:

• Order by (site_id, timestamp). Almost every read was for one website first. Keeping the data sorted that way on disk let ClickHouse skip large chunks of data instead of scanning the whole table.
• Skipping index on timestamp. The post-processing pipeline often asked for "all logs after time T." This index made that path cheaper.

We also partitioned the table by date.

That made retention simple:

1. a background job found old partitions
2. it dropped those partitions
3. we did not run row-level DELETEs over billions of rows

Partition drops in ClickHouse are cheap metadata operations, so cleanup stayed simple and safe to review.

The Hidden Disk Hog: system.query_log

One surprise was unexplained disk growth.

ClickHouse enables system.query_log by default. It records metadata for every query. At our query volume, that table grew to terabytes. It became larger than the firewall logs table itself.

We did not need query audit logs for the product. Once we found the cause, we disabled system.query_log and got the disk back.

Hardware

Even at hundreds of billions of rows and over a billion inserts a day, the workload ran well on a single server:

• CPU stayed under 5%
• network had spare capacity
• disk I/O had spare capacity

Columnar compression and fast query execution are a big part of why that was possible.

We also designed, but did not ship:

• hot-cold tiering: last 7 days on SSD, older data on cheaper disks
• replication for durability

Priorities moved elsewhere before we deployed either. The low load on one server suggested we still had room to grow vertically first.

Rails and MySQL Integration

The hardest part was not ClickHouse itself. It was connecting it to our Ruby on Rails 5 app, which already used MySQL.

At the time, the ClickHouse Ruby gems we tried were incomplete or buggy. None handled binary string encoding correctly, which led to bad queries. We evaluated two or three gems, picked the best one, and monkey-patched binary string handling.

On top of that, we built a ClickHouse-specific Active Record-style base class:

• every ClickHouse-backed model inherited from it
• it kept ClickHouse queries out of the MySQL connection pool and transaction flow
• it added ClickHouse-only operations that ActiveRecord does not have, especially partition create and partition drop for retention

Read-Only Access and Query Control

We created a read-only ClickHouse user for ad hoc analysis and debugging. That let people explore firewall logs without risking accidental writes.

This paid off once. A query under the read-only account started hurting database performance. We found the running query through ClickHouse's introspection views, killed it, and service recovered without a restart.

Backup and Archival

For long-term durability, a secondary server ran a background job that:

1. queried recent logs
2. compressed them into archive files
3. pushed those files to S3

That kept backup work off the primary ClickHouse server and gave us an archive outside the live database.

Closing Notes

Two things stuck with us:

1. ClickHouse gave us a lot of performance headroom on small hardware. We were doing billion-row daily ingest with single-digit CPU usage.
2. Application tooling still needed real work. Outside languages like Python, Go, and Java, we had to invest in glue code to fit ClickHouse into our Rails and MySQL stack.

The official ClickHouse documentation[^3] and Altinity's docs and knowledge base[^4] helped a lot while we designed the schema, operated the cluster, and debugged production issues.

---

References

1. HTTP Analytics for 6M requests per second using ClickHouse - Cloudflare Engineering

2. Fast and Reliable Schema-Agnostic Log Analytics Platform - Uber Engineering

3. ClickHouse Documentation - Official ClickHouse docs

4. Altinity Documentation / Altinity Knowledge Base - Altinity ClickHouse docs and operational guides