In a recent exploration of its engineering choices, Cloudflare’s team behind the Zero Trust product suite has articulated its rationale for selecting TimescaleDB over ClickHouse to enhance its internal analytics and reporting capabilities. The decision stems from a desire to achieve a “phenomenal balance” between the ease of managing analytical data alongside configuration data and the performance characteristics of a dedicated OLAP system.
Digital Experience Monitoring (DEX)
Cloudflare has placed significant emphasis on minimalism in engineering, leading to the development of Digital Experience Monitoring (DEX), an observability platform that offers insights into device, network, and application performance across its Zero Trust environments. The architecture of DEX includes a configuration plane for managing synthetic tests and an analytics plane that serves as an ingestion pipeline. This pipeline collects structured logs from WARP clients, efficiently stores them, and presents them through a user-friendly dashboard.
Despite having utilized ClickHouse since 2017, the team, led by Robert Cepa, recently opted for a different approach for this project. He noted the limitations of ClickHouse’s default table engine, MergeTree, which is optimized for high-throughput batch inserts but struggles with numerous small writes. This challenge arose as millions of individual devices sent log events every two minutes, leading to issues such as write amplification and resource contention.
The default and most commonly used table engine in ClickHouse, MergeTree, is optimized for high-throughput batch inserts. It writes each insert as a separate partition, then runs background merges to keep data manageable. This makes writes very fast, but not when they arrive in lots of tiny batches, which was exactly our case with millions of individual devices uploading one log event every 2 minutes. Too many small writes can trigger write amplification, resource contention, and throttling.
To expedite the initial release and deliver a functional DEX MVP within four months, the team relied on PostgreSQL for both configuration data and analytical logs. This setup successfully managed 200 inserts per second at launch, with query latencies in the hundreds of milliseconds for most users. However, as usage increased, Cepa observed:
As adoption grew, we scaled to 1,000 inserts/sec, and our tables grew to billions of rows. That’s when we started to see performance degradation, particularly for large customers querying 7+ day time ranges across tens of thousands of devices.
Faced with the challenge of processing billions of device logs, the team investigated precomputing aggregates, or downsampling, to enhance performance. By storing summaries in advance rather than repeatedly querying raw data, they achieved a remarkable 1000x improvement in query performance, allowing charts that once took several seconds to render to be displayed instantaneously, even for extensive time ranges.
Source: Cloudflare blog
Recognizing that PostgreSQL does not automatically refresh materialized views or manage table partitions, the team transitioned to TimescaleDB. This open-source time-series database, built as an extension to PostgreSQL, optimizes storage and querying for time-stamped data while preserving full SQL compatibility and ACID properties. TimescaleDB’s automation of aggregation and data retention through automatic partition management and downsampling enabled Cloudflare to streamline its internal infrastructure.
Not every team needs a hyper-specialized race car that requires 100 octane fuel, carbon ceramic brakes, and ultra-performance race tires: while each one of these elements boosts performance, there’s a real cost towards having those items in the form of maintenance and uniqueness. For many teams at Cloudflare, TimescaleDB strikes a phenomenal balance between the simplicity of storing your analytical data under the same roof as your configuration data, while also gaining much of the impressive performance of a specialized OLAP system.
In benchmarking a TimescaleDB compressed hypertable against a traditional PostgreSQL table, Cepa noted performance enhancements ranging from 5x to 35x, contingent on the query type and time range, attributed to the advantages of compression and sparse indexes. The community response to the decision to forgo ClickHouse has been mixed, with some questioning the rationale. On platforms like Hacker News, users have expressed skepticism regarding the choice, while others, like Ajay Kulkarni, cofounder of TigerData, have defended the decision:
PostgreSQL with TimescaleDB did the job. Why overcomplicate things?
Jamie Lord, a solution architect at CDS UK, echoed this sentiment, highlighting the benefits for teams already entrenched in the PostgreSQL ecosystem:
For teams already invested in the PostgreSQL ecosystem, this represents a compelling evolution rather than revolution. You retain all existing tooling, knowledge, and operational procedures whilst gaining analytical capabilities that rival purpose-built OLAP systems.
Source: Cloudflare blog
Following the successful implementation of the DEX project, TimescaleDB has been adopted as the aggregation layer for other Cloudflare initiatives, including Zero Trust Analytics & Reporting, facilitating the generation of analytics and long-term reports for systems processing millions of rows per second.
