# FRAMEWORK COMPARISON ANALYST™ v1.0 ### CyberGuard: Core Data Storage & Ingestion Layer *** ## Reasoning: Criteria Derivation **From {DECISION_CONTEXT}**, the weights are driven by three hard constraints: (1) a 2-dev team with no dedicated Ops, (2) real-time ingestion peaking at 10,000 events/sec, and (3) sub-second dashboard latency. Generic criteria like "ecosystem maturity" or "community size" are downweighted — they are irrelevant when the team has no bandwidth to exploit them. **Surfaced trade-off the buyer hasn't named:** Kafka adds a *separate ingestion layer* — the team is actually comparing 1-component stacks (Firestore, TimescaleDB) against a 2-component stack (Kafka + ClickHouse). Operational surface doubles. This asymmetry must factor into every maintenance score. *** ## Criteria Engine | LABEL | DEFINITION (in CyberGuard context) | WEIGHT | RATIONALE | |---|---|---|---| | **Ingestion Throughput** | Sustained write capacity at 2K–10K events/sec without data loss or backpressure | HIGH | Hard requirement — failure here is a product-breaking defect | | **Real-Time Query Latency** | Sub-second response on active threat dashboards querying recent event windows | HIGH | Directly tied to product's core security value proposition | | **Analytical Query Power** | Ability to run aggregations, window functions, correlation queries across log history | HIGH | Anomaly detection and SOC workflows depend on this heavily | | **Operational Overhead** | Ease of deployment, monitoring, schema management, and incident response for 2 devs | HIGH | Team is the binding constraint; complexity here kills velocity | | **Cost Predictability** | Ability to forecast and cap infrastructure costs at scale | MEDIUM | Security platforms have unpredictable traffic spikes; runaway costs are a real risk | | **Data Model Flexibility** | Ability to handle heterogeneous log schemas from varied security sources | MEDIUM | Security logs are notoriously schema-inconsistent across sources | **Downweighted (generic but LOW here):** - *Vendor ecosystem/integrations* — LOW. A 2-person team won't build a rich integration layer; raw data performance matters more. - *Global replication* — LOW. CyberGuard is not stated as globally distributed; single-region is the baseline. *** ## Scoring Map **Scoring key:** 5 = excellent fit | 4 = good fit | 3 = adequate | 2 = weak fit | 1 = poor fit ``` ┌──────────────────────────────────┬──────────────────┬───────────────────────────┬───────────────────────────────┐ │ CRITERION {WEIGHT} │ Firebase │ PostgreSQL + │ Apache Kafka + │ │ │ Firestore │ TimescaleDB │ ClickHouse │ ├──────────────────────────────────┼──────────────────┼───────────────────────────┼───────────────────────────────┤ │ Ingestion Throughput {H} │ 2/5 │ 3/5 │ 5/5 │ │ Real-Time Query Latency {H} │ 3/5 │ 4/5 │ 4/5 │ │ Analytical Query Power {H} │ 1/5 │ 4/5 │ 5/5 │ │ Operational Overhead {H} │ 5/5 │ 4/5 │ 2/5 │ │ Cost Predictability {M} │ 2/5 │ 5/5 │ 3/5 │ │ Data Model Flexibility {M} │ 4/5 │ 3/5 │ 4/5 │ ├──────────────────────────────────┼──────────────────┼───────────────────────────┼───────────────────────────────┤ │ WEIGHTED SCORE │ 22 │ 28 │ 28* │ │ CONTEXT FIT │ LOW │ HIGH │ MEDIUM │ └──────────────────────────────────┴──────────────────┴───────────────────────────┴───────────────────────────────┘ *Kafka+ClickHouse raw score matches TimescaleDB but carries an operational penalty (see notes) ``` **Score Notes (non-obvious cells):** - **Firestore — Ingestion Throughput (2/5):** While the 10K writes/sec database cap was removed in 2022, the *1 write/sec per document* limit is fatal for high-cardinality security logs where the same IP or host generates burst sequences. Firestore's document model requires careful key sharding to avoid hot-spotting — a non-trivial engineering burden. [stackoverflow](https://stackoverflow.com/questions/59640130/exceeding-the-10k-writes-per-second-per-database-limit-in-firebase-cloud-firest) - **Firestore — Analytical Query Power (1/5):** Firestore has no native aggregation engine for time-windowed queries, GROUP BY analytics, or correlation across event fields. Security use cases (e.g., "show me all failed auth events for IPs seen in the last 5 min") require pulling data to the client or Cloud Functions — this is architecturally inadequate for a SOC dashboard. [cometchat](https://www.cometchat.com/blog/firebase-limitations) - **Firestore — Cost Predictability (2/5):** At 10,000 events/sec, Firestore's per-read/per-write billing model becomes extremely expensive and non-linear. Spikes during active incidents — precisely when a security platform is most active — directly translate to cost spikes. [supertokens](https://supertokens.com/blog/firebase-pricing) - **TimescaleDB — Ingestion Throughput (3/5):** Handles continuous time-series writes well with hypertables and chunk compression, but is a row-store under the hood. Sustained 10K events/sec is achievable but requires proper partitioning and connection pooling tuning (PgBouncer). Not out-of-the-box. [yandex](https://yandex.cloud/en/blog/posts/2025/03/clickhouse-vs-postgresql) - **TimescaleDB — Real-Time Query Latency (4/5):** Continuous aggregates and columnar compression in TimescaleDB 2.x achieve near-sub-second latency on recent data windows. Benchmarks show TimescaleDB is 1.9× faster than ClickHouse on real-time analytics benchmarks (RTABench), though slower on bulk historical scans. [tigerdata](https://www.tigerdata.com/blog/benchmarking-databases-for-real-time-analytics-applications) - **TimescaleDB — Operational Overhead (4/5):** Extends standard PostgreSQL — the team can use `psql`, standard backup tools (`pg_dump`, WAL archiving), and familiar SQL. One process to manage. Schema migrations are standard Postgres DDL. [youtube](https://www.youtube.com/watch?v=EMpintnHsso) - **Kafka + ClickHouse — Ingestion Throughput (5/5):** Kafka is purpose-built for exactly this use case — sustained high-throughput, ordered, durable log ingestion at 10K+ events/sec is well within its comfort zone. ClickHouse natively ingests from Kafka topics. [axual](https://axual.com/blog/starting-small-with-kafka-why-its-the-right-choice-for-your-enterprise) - **Kafka + ClickHouse — Analytical Query Power (5/5):** ClickHouse returns aggregation queries 10–100× faster than PostgreSQL on OLAP workloads. For security event correlation across billions of rows, this is the best-in-class option. [blog.octabyte](https://blog.octabyte.io/topics/open-source-databases/clickhouse-vs-postgresql-analytics/) - **Kafka + ClickHouse — Operational Overhead (2/5):** This is a **two-system stack**. The team must operate Kafka brokers (or a managed service), manage Kafka topics/partitions, configure ClickHouse MaterializedViews for Kafka ingestion, handle schema registry, monitor consumer lag, and manage two independent failure domains. For 2 developers, this is not a side concern — it is a primary burden. [metacto](https://www.metacto.com/blogs/the-true-cost-of-apache-kafka-a-comprehensive-guide-to-usage-integration-and-maintenance) - **Kafka + ClickHouse — Cost Predictability (3/5):** Managed Kafka (Confluent Cloud, Upstash, MSK) adds significant per-message cost at scale. Self-hosted reduces cost but increases ops burden, compounding the overhead penalty. [metacto](https://www.metacto.com/blogs/the-true-cost-of-apache-kafka-a-comprehensive-guide-to-usage-integration-and-maintenance) *** ## Verdict Gate ``` ── GATE CHECK: RECOMMENDATION READINESS ────────────── GATE STATUS: CONDITIONAL REASON: TimescaleDB and Kafka+ClickHouse score identically on raw points, but the operational penalty for a 2-dev team is a context-specific discriminator that resolves the tie in favor of TimescaleDB — conditional on managed Kafka being ruled cost-prohibitive or on the team not hiring DevOps. ───────────────────────────────────────────────────── ``` *** ## Verdict **RECOMMENDATION: PostgreSQL + TimescaleDB** **CONFIDENCE:** HIGH — the 2-developer operational constraint is the decisive context variable, and TimescaleDB uniquely satisfies all four hard requirements without compounding system surface. **PRIMARY REASON:** TimescaleDB is the only option that simultaneously meets the ingestion, latency, and analytical requirements *without introducing a second operational system*. At 10K events/sec, it is viable with standard tuning; sub-second queries on recent time windows are well-supported via continuous aggregates; and the team operates a single Postgres-compatible instance they can reason about fully. [youtube](https://www.youtube.com/watch?v=EMpintnHsso) **Supporting Reasons:** - **Cost is fixed and predictable.** Self-hosted on a single VM (or managed via Timescale Cloud) costs a flat infrastructure rate — no per-event billing spikes during active incident response, unlike Firestore. [supertokens](https://supertokens.com/blog/firebase-pricing) - **Standard SQL analytical queries work.** Anomaly detection windows, IP-based correlation, alert aggregations — all expressible in standard SQL with TimescaleDB's time_bucket() and continuous aggregates. No custom query DSL to learn. - **Operational familiarity reduces MTTR.** When a security platform's data layer goes down, 2 developers need to diagnose and recover fast. A Postgres process they know beats a Kafka+ZooKeeper+ClickHouse triage tree. *** **TRADE-OFFS YOU ARE ACCEPTING:** → **You give up best-in-class bulk historical analytics.** ClickHouse is 10–100× faster on billion-row aggregation scans. If CyberGuard's roadmap includes long-term forensic analysis across months of log history at scale, TimescaleDB will eventually feel slow for those queries. [blog.octabyte](https://blog.octabyte.io/topics/open-source-databases/clickhouse-vs-postgresql-analytics/) → **You give up guaranteed durability of the ingestion buffer.** Kafka provides a persistent, replayable log buffer — if the ingest pipeline falls behind during a traffic spike, events are queued. TimescaleDB's direct-write path has no native replay buffer; a spike beyond capacity risks write pressure on Postgres. → **The painful condition:** If you onboard enterprise customers generating 50K+ events/sec sustained or add a third developer with Kafka/ClickHouse experience, TimescaleDB will become a ceiling. The pain point arrives when queries on 90-day windows start taking 3–5 seconds. *** **CONDITIONS THAT WOULD CHANGE THIS RECOMMENDATION:** → **If team grows to 3+ developers with dedicated infra experience** → revisit Kafka + ClickHouse. The operational cost drops significantly with a dedicated person, and the analytics ceiling disappears. → **If managed Kafka (Upstash/Confluent) is budgeted at <$300/month** → the ops burden of Kafka largely disappears. At that point, Kafka + ClickHouse becomes viable and may be preferable for the analytics power. → **If forensic historical queries (>30 days, >500M rows) become a core product feature** → TimescaleDB compression helps but ClickHouse wins decisively. A hybrid architecture (TimescaleDB for hot/recent + ClickHouse for cold/historical) becomes the correct answer. → **Eliminate Firestore entirely** for this use case. Its per-document write limits, lack of analytical query support, and per-operation billing make it architecturally unsuitable for a security event pipeline at any scale beyond prototyping. [docs.cloud.google](https://docs.cloud.google.com/firestore/quotas) *** ## Framing for System Design Spec Sheet > *"After weighted evaluation against CyberGuard's binding constraints — 2-developer team, 10K events/sec ingestion, and sub-second dashboard latency — **PostgreSQL + TimescaleDB** is selected as the core data layer. The decision optimizes operational manageability over raw analytical throughput. The architecture shall be re-evaluated if sustained ingestion exceeds 20K events/sec, historical query windows exceed 90 days routinely, or the engineering team expands with dedicated infrastructure capacity. At that threshold, migration to Kafka + ClickHouse is the documented upgrade path."* *** > **Context Limit Note:** This comparison is calibrated to the CyberGuard context as described. The recommendation would change materially if team size, budget, or the scope of forensic analytics features shifts. For benchmarked claims about TimescaleDB vs. ClickHouse query performance, cross-reference with the RTABench results and the Yandex ClickHouse benchmark series. [yandex](https://yandex.cloud/en/blog/posts/2025/03/clickhouse-vs-postgresql)