When a familiar database is the right analytics choice

At Cloudflare, PostgreSQL and ClickHouse serve as our standard databases for transactional and analytical workloads, respectively. PostgreSQL powers much of the configuration data behind our Dashboard; ClickHouse, which we adopted around 2017, handles enormous ingestion rates with millisecond-level queries. But when we set out to build the analytics and reporting for our Zero Trust product suite, we chose TimescaleDB — a PostgreSQL extension — over ClickHouse. The reasoning came down to matching the tool to the actual problem, not the most impressive technology.

Designing for what you actually need

After a decade in software development, I've learned to favor systems that are simple and boring. Whenever I see a system diagram with more than three boxes, I ask: Why are all these components here? Do we really need all of this?

It's easy to design for scenarios that might never happen — imagined scale, complex failures, edge cases. But systems often don't grow the way we expect, or don't have to. Product limits, rate limits, and clear customer expectations can defer the need for large-scale architecture. Launching with just two or three essential components gives you something to ship, test, and learn from quickly. You can add complexity later, but only once you know you need it.

Whether you call it YAGNI, Keep it simple, or minimalism in engineering, the principle holds: we're rarely good at predicting the future, and every additional component carries a cost. Each box in a system diagram is something that can break itself or other boxes, spiral into outages, and disrupt on-call engineers' weekends. Each also requires documentation, tests, observability, and service level objectives — and sometimes a new programming language for the team supporting it.

Building Digital Experience Monitoring on a small footprint

Two years ago, I was tasked with building Digital Experience Monitoring (DEX), which provides visibility into device, network, and application performance across Zero Trust environments. Our initial goal was an MVP focused on fleet status monitoring and synthetic tests. Fleet status and synthetic tests are structured logs generated by the WARP client, uploaded to an API, stored in a database, and visualized in the Cloudflare Dashboard.

DEX began as a “tiger team” — a small group of experienced engineers validating a new product quickly. We worked with these constraints:

  • Team of three full-stack engineers.
  • Daily collaboration with 2-3 other teams.
  • Can launch in beta, engineering can drive product limits.
  • Emphasis on shipping fast.

To balance usefulness with simplicity, we made deliberate early design decisions:

  • Fleet status logs uploaded at fixed 2-minute intervals.
  • Synthetic tests preconfigured by target (HTTP or traceroute) and frequency.
  • Usage caps: up to 10 synthetic tests per device, no more than once every 5 minutes.
  • Data retention of 7 days.

These guardrails let us ship months earlier and gather early customer feedback without prematurely investing in scalability.

We needed a configuration plane — a Dashboard interface for managing synthetic tests, backed by an API and database. Our setup stayed tight:

  • HTTP API for managing test configurations.
  • PostgreSQL for storing configurations.
  • React UI embedded in the Cloudflare Dashboard.

Just three components — simple and focused. Each carried real complexity behind the scenes: PostgreSQL ran as a high-availability cluster with one primary, one synchronous replica for failover, and several asynchronous replicas across two geographies. The API ran on horizontally scaled Kubernetes pods across two geographies. The React app was served globally via Cloudflare's network. Our platform teams abstracted that complexity away, letting us reason about just three parts — though it shows each box can carry substantial hidden cost.

Next came the analytics plane — an ingestion pipeline for WARP client logs, storage, and Dashboard visualization. I was personally eager to explore ClickHouse, having seen its performance elsewhere. But the internal documentation on getting started quickly reset my expectations:

Writing data to Clickhouse

Your service must generate logs in a clear format, using Cap'n Proto or Protocol Buffers. Logs should be written to a socket for logfwdr to transport to PDX, then to a Kafka topic. Use a Concept:Inserter to read from Kafka, batching data to achieve a write rate of less than one batch per second.

That's a lot — including ClickHouse and the WARP client, we'd be adding five boxes to the diagram. The architecture exists for good reason: ClickHouse's default MergeTree engine is optimized for high-throughput batch inserts, writing each insert as a separate partition and running background merges. Writes are fast, but not when they arrive in many tiny batches — exactly our situation with millions of devices uploading one log event every 2 minutes. Too many small writes trigger write amplification, resource contention, and throttling.

ClickHouse is a sports car, and to get value from it you need a race track. We needed a daily driver for short trips. At launch we didn't need millions of inserts per second — something easy to set up, reliable, familiar, and good enough to reach the market. A colleague suggested PostgreSQL, noting it "can be cranked up" to handle our expected load. We took the leap.

PostgreSQL for time-series workloads

Structurally, configuration data and analytical logs aren't very different. Logs are structured payloads — often JSON — that can be transformed into a columnar format and stored in a relational database. A device state log might look like:

{
  “timestamp”: “2025-06-16T22:50:12.226Z”,
  “accountId”: “025779fde8cd4ab8a3e5138f870584a7”,
  “deviceId”: “07dfde77-3f8a-4431-89f7-acfcf4ead4fc”,
  “colo”: “SJC”,
  “status”: “connected”,
  “mode”: “warp+doh”,
  “clientVersion”: “2024.3.409.0”,
  “clientPlatform”: “windows”,
}

We created a simple PostgreSQL table for these logs:

CREATE TABLE device_state (
	"timestamp" TIMESTAMP WITH TIME ZONE NOT NULL,
	account_id TEXT NOT NULL,
	device_id TEXT NOT NULL,
	colo TEXT,
	status TEXT,
	mode TEXT,
	client_version TEXT,
	client_platform TEXT
);

The table intentionally has no primary key, since time-series data is rarely queried by unique ID — queries target time ranges and filter by attributes like account ID or device ID. For deduplication on client retries, we relied on indexes instead:

CREATE UNIQUE INDEX device_state_device_account_time ON device_state USING btree (device_id, account_id, “timestamp”);
CREATE INDEX device_state_account_time ON device_state USING btree (account_id, “timestamp”);

The unique index guarantees each (device, account, timestamp) tuple is a single, unique log. The second index serves typical time-window queries at the account level. Since account_id and timestamp always appear in queries, they're always part of the index. Data was inserted from our API with an UPSERT:

INSERT INTO device_state (…) VALUES (…) ON CONFLICT DO NOTHING;

Column order matters in multicolumn indexes

PostgreSQL's B-tree indexes support multiple columns, but column order significantly affects query performance. From the PostgreSQL documentation:

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned.

Time-series queries typically have inequality constraints on the time column and equality constraints on everything else. A typical chart-building query looks like:

SELECT 
  DATE_TRUNC(‘hour’, timestamp) as hour, 
  account_id,
  device_id,
  status,
  COUNT(*) as total
FROM device_state 
WHERE 
  account_id = ‘a’ AND
  device_id = ‘b’ AND
  timestamp BETWEEN ‘2025-07-01’ AND ‘2025-07-02’
GROUP BY hour, account_id, device_id, status;

Our WHERE clause has equality constraints on account_id and device_id, plus inequality constraints on timestamp. Had we indexed columns in (timestamp, account_id, device_id) order, only the timestamp portion would reduce the scanned index range — account_id and device_id would require full scans with filtering after the fact.

B-tree search complexity is O(log n), so reducing the scanned index portion helps as tables grow. Even for equality-constrained columns, ordering by cardinality pays off — we've seen up to 100% improvement in SELECT performance simply by reordering account_id and device_id in our multicolumn index.

Our rules for column order in time-range query indexes:

  • The timestamp column is always last.
  • Other columns lead, ordered by cardinality starting with the highest.

Launch results and scaling path

By avoiding premature optimization, our minimal architecture took us from zero to a working DEX MVP in under four months. Early metrics were solid:

  • ~200 inserts/sec at launch.
  • Query latencies in the hundreds of milliseconds for most customers.

Post-launch, we focused on feedback and system monitoring. As adoption grew, we scaled to 1,000 inserts/sec and our tables reached billions of rows. That's when performance degradation appeared — particularly for large customers querying 7+ day time ranges across tens of thousands of devices.

Downsampling for dashboard-speed queries

With billions of device logs, DEX quickly hit the point where scanning raw rows for every dashboard visualization became impractical. The first optimization we put into production was precomputed aggregates, commonly called downsampling. If you know the query shapes ahead of time—grouping by status, mode, or data center location—you can compute and store those summaries once instead of repeatedly scanning full details.

BLOG-2846 Image 3

Our Fleet Status dashboard renders typical groupings: connected devices by colo, and device status and connection mode over time. These queries usually aggregate across a 1-hour window or the entire stored range. Our largest customers field 30,000+ devices, each emitting logs every 2 minutes—millions of rows per customer per day. Yet the grouping columns like status and mode only hold 4–6 distinct values. Aggregating ahead of time collapses millions of rows into a few hundred per interval, letting the dashboard query much smaller tables.

BLOG-2846 Image 4

The payoff was dramatic: up to 1000x faster queries, with charts that previously took seconds rendering instantly for 7-day views across tens of thousands of devices.

Implementing this in vanilla PostgreSQL required manual work. Materialized views don't refresh automatically or incrementally, so we ran a cron job executing custom aggregation queries across six pre-aggregate tables. Our database platform team had a lightweight framework for data retention that we reused, but any schema change demanded cross-team coordination and continuous effort tuning aggregation performance. The results justified the investment in fast, reliable queries for most customer-facing use cases.

Why we skipped native partitioning

Pre-aggregates only stretch so far. As new DEX features added columns, we needed more pre-aggregated tables, and some queries combined filters requiring raw data access that wasn't fast enough.

PostgreSQL table partitioning splits one large table into child tables, each covering a slice of data—say, a day of logs. Queries with timestamp filters then scan only relevant partitions, which can deliver strong speedups. Range partitioning on timestamps was attractive for supporting up to a year of retention without a single massive table.

CREATE TABLE device_state (
    …
) PARTITION BY RANGE (timestamp);

CREATE TABLE device_state_20250601 PARTITION OF device_state
    FOR VALUES FROM ('2025-06-01') TO ('2025-06-02');
CREATE TABLE device_state_20250601 PARTITION OF device_state
    FOR VALUES FROM ('2025-06-02') TO ('2025-06-03');
CREATE TABLE device_state_20250601 PARTITION OF device_state
    FOR VALUES FROM ('2025-06-03') TO ('2025-06-04');

But PostgreSQL won't manage partitions for you; each child table must be manually created, requiring a full automation system. More importantly, partitioning doesn't address our real problem: speeding up frequent dashboard queries over recent raw data. For the 7-day window our dashboards needed, partitioning offered no clear win, so we never adopted it.

TimescaleDB as a re-evaluation

With PostgreSQL's limits clear, we started evaluating alternatives and found TimescaleDB. Three capabilities stood out as potential cures for raw query performance: columnstore and sparse indexes, both common in OLAP engines like ClickHouse. Several other features reinforced the fit:

  • It's Postgres: TimescaleDB runs as an extension and coexists with regular tables, so transactional workloads stay put while analytical data moves to hypertables.
  • Automatic partition management: Hypertables are partitioned by default and managed automatically, removing the manual burden of native partitioning.
  • Automatic downsampling: Continuous aggregates replace our cron-based aggregation pipeline with built-in, ongoing maintenance.
  • Realtime aggregation: Realtime aggregation solves the stale-data problem of async aggregates by merging recent raw rows into rollups during queries.
  • Compression: Reduces table size by over 90% while improving query performance.
  • Columnstore analytics: The hybrid row/columnar engine, Hypercore, performs fast scans and aggregations while remaining fully mutable for UPSERT backfills.
  • Analytical functions: A library of tools for percentile approximation, unique-count approximation, time-weighted averages, and more.

Most compelling for operations was automation of aggregation and retention. That removed a scheduled job from our architecture entirely.

Evaluating against real production traffic

We deployed TimescaleDB on our canary PostgreSQL cluster and dual-wrote from the same production backend for an apples-to-apples comparison. Installation was trivial: loading the library and running one command.

CREATE EXTENSION IF NOT EXISTS timescaledb;

From there we created raw tables, converted them to hypertables, enabled columnstore features, defined continuous aggregates, and configured automatic policies for compression and retention. The device_state logs setup condensed looks like this:

– Create device_state table.
CREATE TABLE device_state (
	…
);

– Convert it to a hypertable.
SELECT create_hypertable ('device_state', by_range ('timestamp', INTERVAL '1 hour'));

– Add columnstore settings
ALTER TABLE device_state SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = ‘account_id’
);

– Schedule recurring compression jobs
CALL add_columnstore_policy(‘device_state’, after => INTERVAL '2 hours', schedule_interval => INTERVAL '1 hour');

– Schedule recurring data retention jobs
SELECT add_retention_policy(‘device_state’, INTERVAL '7 days');

– Create device_state_by_status_1h continuous aggregate
CREATE MATERIALIZED VIEW device_state_by_status_1h
WITH (timescaledb.continuous) AS
SELECT
  time_bucket (INTERVAL '1 hour', TIMESTAMP) AS time_bucket,
  Account_id,
  Status,
  COUNT(*) as total
FROM device_state
GROUP BY 1,2,3
WITH no data;

– Enable realtime aggregates
ALTER MATERIALIZED VIEW ‘device_state_by_status_1h’
SET (timescaledb.materialized_only=FALSE);

– Schedule recurring continuous aggregate jobs to refresh past 10 hours every 10 minutes
SELECT add_continuous_aggregate_policy (
  ‘device_state_by_status_1h’,
  start_offset=>INTERVAL '10 hours',
  end_offset=>INTERVAL '1 minute',
  schedule_interval=>INTERVAL '10 minutes',
  buckets_per_batch => 1
);

After two weeks of backfill, we benchmarked real production dashboard queries across:

  • 3 time windows: past 1 hour, 24 hours, and 7 days
  • 3 columnstore modes: uncompressed, compressed, and compressed with segmenting
  • Datasets of 500 million to 1 billion rows
BLOG-2846 Image 5

We saw 5x to 35x performance gains, varying by query type and time range:

  • For 1–24 hour windows, even uncompressed hypertables performed well.
  • For the 7-day window, compression and columnstore settings—especially segmentby—made the crucial difference.
  • Sparse indexes outperformed PostgreSQL btree indexes once the latter broke down at scale.

Compression also delivered ratios up to 33x, letting us retain 33x more data for the same storage budget.

SELECT 
    pg_size_pretty(before_compression_total_bytes) as before,
    pg_size_pretty(after_compression_total_bytes) as after,
    ROUND(before_compression_total_bytes / after_compression_total_bytes::numeric, 2) as compression_ratio
FROM hypertable_compression_stats('device_state');

before: 1616 GB
after: 49 GB
compression_ratio: 32.83

Where the speed comes from

Columnstore performance rests on two mechanisms: compression and sparse minmax indexes.

Querying compressed data seems counterintuitive—decompression costs CPU cycles. But in analytical workloads I/O dominates, and the massive reduction in disk reads more than offsets decompression cost. TimescaleDB transforms a hypertable into columnar format, grouping values from each column into chunks around 1,000 rows, storing them in arrays, then compressing those arrays into binary form.

The PostgreSQL implementation cleverly leverages TOAST pages. After compressing tuples of 1,000 values, they land in external TOAST pages. The columnstore table itself becomes a table of pointers, with actual data fetched lazily column-by-column.

Sparse indexes take the opposite approach from traditional indexes: instead of indexing every row, they store every Nth value, growing far smaller and scanning faster at large scales. TimescaleDB's minmax variant adds two metadata columns per compressed tuple holding the min and max of the 1,000 values. The planner checks those metadata values to skip compressed tuples without decompressing—if the sought range can't contain a tuple's data, the tuple is skipped.

We discovered during evaluation that these sparse indexes need explicit configuration. TimescaleDB sets timescaledb.orderby to a default that isn't always optimal. We listed every filter column in orderby:

– Add columnstore settings
ALTER TABLE device_state SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = ‘account_id’,
    timescaledb.orderby = ‘timestamp,device_id,colo,mode,status,client_version,client_platform
);

TimescaleDB beyond DEX: the Analytics & Reporting team

After DEX proved the model, other Cloudflare groups began evaluating TimescaleDB for its combination of simplicity and throughput. The Zero Trust Analytics & Reporting (ART) team is a prominent example. ART produces analytics and long-term reports — spanning months to years — for products like Access, Gateway, CASB, and DLP.

The underlying data was scattered across several ClickHouse and PostgreSQL clusters. ART wanted to replicate those into a single home built to unify related but separately stored data points, modeled around customer analytical queries. TimescaleDB became the aggregation tier sitting above raw logs kept elsewhere.

The implementation relies on crawler jobs driven by cron. These crawlers periodically poll the various clusters for hourly aggregates across all customers, then ingest those into TimescaleDB. From there, continuous aggregates further roll the data into daily and monthly summaries for reporting.

Access and Gateway datasets are enormous — often ingesting millions of rows per second. Crawler queries group by every relevant field, including high-cardinality columns like IP addresses, to support arbitrary report filters. That keeps the downsampling ratio low; in some cases the team is inserting roughly 100,000 aggregated rows per second. TimescaleDB handles the load, but only after several deliberate configuration changes:

  • Bulk INSERT statements were replaced with COPY, which materially improved ingestion throughput with large batches.
  • Synchronous replication was disabled — the crawlers are idempotent and can reprocess missing data, so temporary loss is acceptable.
  • fsync was turned off. Durability was not a concern for this workload, and skipping disk syncs boosted ingest performance.
  • Most indexes on hypertables were dropped; only one on (account_id, timestamp) remains. Combined with aggressive compression and sparse indexes, this raised insert rates. Query performance did not suffer meaningfully because only a tiny portion of the table stays uncompressed and uses traditional btree indexes.

You can see the result in Cloudflare Zero Trust Analytics.

The trade-off worth making

Focusing on core value rather than premature optimization accelerated Cloudflare's time to market and led to an architecture the team did not initially plan. In DEX's early days, that refocus surfaced TimescaleDB as exactly the right tool.

Not every team needs a hyper-specialized system that demands premium components and constant maintenance. For many Cloudflare teams, TimescaleDB offers a strong middle path: analytical data stored alongside configuration data in PostgreSQL, with much of the performance of a dedicated OLAP engine — without the operational overhead of a separate specialty database.

TimescaleDB-powered analytics, reporting, and digital experience monitoring are available on the Zero Trust platform. Contact your account team or sign up directly to try it.