Workload-Aware Storage Reduction for Multi-Tenant SIEM on ClickHouse

Abstract

—Security Information and Event Management (SIEM) platforms ingest terabytes of heterogeneous telemetry daily—Windows event logs, DNS queries, HTTP transactions, EDR alerts, and network metadata from Zeek—yet the majority of stored records are never queried for threat-hunting or incident-response workflows. This study presents a workload-aware storage reduction framework that tailors data retention to observed analytical demand within a multi-tenant ClickHouse deployment. The main contribution is a Workload Analyzer algorithm that extracts importance scores for columns from ClickHouse query logs using a frequency–recency–coverage weighting scheme, and a Storage-Coverage Cost Model that computes the optimal pruning threshold that minimizes a weighted sum of storage cost and coverage loss. Guided by these metrics, the framework applies six composable reduction operators: column pruning with materialized views, adaptive sampling, deduplication, per-column codec selection, skip-indexing, and time-to-live (TTL)-based retention tiering across hot/warm/cold storage. Multi-tenant isolation is enforced through role-based access control overlays aligned with the Thai Personal Data Protection Act (PDPA). Experimental evaluation on 1,000,000 Zipf-distributed Windows Security Events demonstrates 79% uncompressed and 70% compressed storage reduction with sub-second query latency, while the Workload Analyzer automatically identifies the optimal column subset that preserves 100% detection rule coverage at minimum storage cost.