How to query billion+ rows on postgres without overhead

This article explores Cloudflare's decision to utilize TimescaleDB, a PostgreSQL extension, instead of ClickHouse for powering the analytics and reporting features of its Zero Trust product suite. TimescaleDB was chosen due to its simplicity, seamless integration with existing PostgreSQL infrastructure, and native support for time-series data.

The Digital Experience Monitoring (DEX) team at Cloudflare faced the challenge of handling structured log data from WARP clients. By opting for TimescaleDB, the team was able to efficiently manage this data, leading to rapid development and deployment with a small engineering team. TimescaleDB's compatibility with PostgreSQL allowed for straightforward scaling and maintenance, aligning with Cloudflare's preference for streamlined and reliable systems.

Cloudflare has been using PostgreSQL as their standard database for both transactional and analytical workloads since the beginning. It is known for its speed, versatility, and reliability, having been a foundational part of their infrastructure for over three decades. In contrast, ClickHouse was incorporated more recently, allowing them to ingest tens of millions of rows per second with millisecond-level query performance.

However, ClickHouse comes with trade-offs, and the team felt TimescaleDB provided a more suitable solution for their needs.

Robert Cepa, a Cloudflare community member and original author of the article, shares his experience and reasoning behind choosing TimescaleDB over ClickHouse for the analytics and reporting capabilities. After a decade of software development, he has come to appreciate minimalistic and straightforward systems. He emphasizes the importance of avoiding unnecessary complexity and focusing on launchable MVPs with essential components.

By setting customer expectations and implementing guardrails like product limits and rate limits, Cloudflare can expand their architecture later when necessary, without over-engineering from the start.

During the development of DEX, a product focused on fleet status monitoring and synthetic tests, the team made deliberate design decisions to balance usefulness and simplicity. They opted for three core components - a configuration plane in the Dashboard, an API, and a database - providing a minimal yet functional solution early on.

Though each component comes with its own complexities, such as PostgreSQL deployed as a high-availability cluster, a horizontally scaled API on Kubernetes, and a globally served React app, the simplicity of the design allowed the team to focus on the essential parts without getting bogged down by unnecessary complexity.