Cloudflare Chooses PostgreSQL for Analytics Over ClickHouse

Cloudflare Chooses PostgreSQL Extension Over Specialized OLAP for 100K Row/Second Analytics (Another real-time that PostgreSQL dominates every space) Introduction Cloudflare's engineering team, focused on the Zero Trust product suite, opted for TimescaleDB—a PostgreSQL extension—over ClickHouse to enable analytics and reporting in their internal platform. This choice highlights a "phenomenal balance" between storing analytical data alongside configuration data and achieving OLAP-like performance, emphasizing engineering minimalism. Building Digital Experience Monitoring (DEX) DEX is an internal observability platform offering visibility into device, network, and application performance in Cloudflare Zero Trust environments. It includes a configuration plane for managing synthetic tests and an analytics plane that ingests structured logs from WARP clients, stores them, and visualizes data in dashboards. Why Not ClickHouse? Cloudflare has used ClickHouse since 2017, but it wasn't suitable here. The MergeTree engine excels in high-throughput batch inserts but struggles with tiny batches—like millions of devices uploading one log every 2 minutes. This causes write amplification, resource contention, and throttling. Initial PostgreSQL Implementation For the MVP, delivered in four months, the team used PostgreSQL for both configuration and analytical data. It handled 200 inserts/second at launch with query latencies in hundreds of milliseconds for most customers. Scaling Challenges As adoption grew to 1,000 inserts/second and billions of rows, performance degraded, especially for large customers querying 7+ day ranges across tens of thousands of devices. Solutions: Precomputing Aggregates To address this, the team implemented downsampling by precomputing and storing summaries. This yielded a 1000x query performance boost, enabling instant rendering of charts for 7-day views on massive datasets. Adopting TimescaleDB PostgreSQL lacks automatic materialized view refreshes and partition management, so the team adopted TimescaleDB. This open-source time-series extension (Apache 2.0) optimizes time-stamped data with columnstore and sparse indexes, while preserving SQL compatibility and ACID properties. It automates aggregation, retention, and partitioning, simplifying infrastructure. Performance Benchmarks Benchmarking revealed 5- to 35-fold improvements in query speed over plain PostgreSQL, depending on the query type and time range, resulting from compression and indexes. Original Blog @ https://lnkd.in/gTRHs6wG