How We Scaled Firewall Logs with ClickHouse at MalCare

Short summary

MalCare scaled firewall log analytics for 500K+ websites (1B+ rows/day) by migrating from MongoDB to ClickHouse. Key decisions included ordering by (site_id, timestamp) for query speed, partitioning by date for cheap retention via partition drops, and a staged dual-write migration that kept MongoDB as a safety net. The author shares concrete architecture lessons on compression, append-merge design, and column typing at scale.

• •Migrated 1B+ daily firewall log rows from MongoDB to ClickHouse using staged dual-write cutover
• •MergeTree with (site_id, timestamp) ordering and date partitioning enabled fast reads and cheap retention
• •Practical lessons on compression-first writes, append-merge architecture, and fixed-width column preferences

Generated with AI, which can make mistakes.