Using Cloudflare D1 for analytics: what works and what does not

D1 is Cloudflare's SQLite database. For analytics it is a good fit in a way that only becomes apparent once you stop trying to use it like a data warehouse.

The constraints that shape everything

Write budget. The free tier allows 100,000 rows written per day. Paid raises it, but the budget is per-row, so the design question is always "how few rows can express this".

Read budget. 5,000,000 rows read per day free. Generous, and easy to exhaust with one badly indexed query run on every dashboard load.

Database size. 10 GB per database.

Latency. D1 has a primary location. A read from the other side of the world is a real network round trip, which is the single most important thing to know about it: the visitor's request path must never touch D1.

The schema that does not work

One row per pageview, aggregate at read time:

CREATE TABLE pageviews (
  id TEXT PRIMARY KEY,
  site_id TEXT,
  ts INTEGER,
  path TEXT,
  country TEXT,
  browser TEXT
);

Clean, obvious, and wrong here. A hundred thousand pageviews is a hundred thousand writes — one day of free tier for one day of a modest site. And every dashboard query scans a table that grows forever.

The schema that does

Two tiers with different jobs.

Pre-grouped counters for everything the default dashboard shows:

CREATE TABLE stat_rollup (
  site_id TEXT NOT NULL,
  day     TEXT NOT NULL,   -- YYYY-MM-DD in the site's timezone
  dim     TEXT NOT NULL,   -- 'path' | 'country' | 'browser' | ...
  value   TEXT NOT NULL,
  views   INTEGER NOT NULL DEFAULT 0,
  visitors INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (site_id, day, dim, value)
);

This table grows with distinct values per day, not with traffic. A site with 200 pages and visitors from 40 countries writes a few hundred rows a day whether it had a thousand pageviews or a million.

Every default panel becomes one indexed range scan. Fast, cheap, constant regardless of volume.

Row-level facts for the questions counters cannot answer:

CREATE TABLE pageview_facts (
  site_id TEXT NOT NULL,
  ts      INTEGER NOT NULL,
  path    TEXT,
  country TEXT,
  browser TEXT,
  device  TEXT
);
CREATE INDEX facts_site_ts ON pageview_facts (site_id, ts);

Expensive, bounded by a retention window, pruned nightly, and switchable off per site. This is what answers "mobile visitors from Germany who arrived on a campaign" — a cross of two dimensions that pre-grouped rows structurally cannot express.

The discipline is to reserve the expensive tier for the case that genuinely needs it, rather than defaulting to it because it is more flexible.

The monthly table

The twelve-month view is the query that will hurt you. Scanning 365 days times every distinct value is a lot of rows for one chart.

Keep a third table with the same counters keyed by month, written in the same flush from the same deltas. Same source, so it cannot drift, and no compaction job to fall behind.

This only works as a plain sum because of a specific property of the privacy model: if "visitors" means daily uniques — because the identifying salt rotates nightly — then a month genuinely is the sum of its days. A metric with real cross-day uniqueness could not be rolled up this way, and a system that did it anyway would be quietly wrong. Why identity is deliberately daily.

Batching is not optional

Writing to D1 per pageview fails on both budget and latency. Accumulate deltas somewhere that can hold state — a Durable Object is the natural place — and flush periodically.

A flush every fifteen seconds turns a thousand pageviews into a handful of upserts. That is the difference between a hundred thousand writes and a few hundred.

Use INSERT ... ON CONFLICT DO UPDATE so a flush is idempotent and a retry cannot double-count.

Keeping reads cheap

Index for the range scan you actually run. (site_id, day, dim) covers almost every dashboard query.

Never SELECT * on the facts table.

Prune on a schedule. A daily cron deleting facts past the retention window. Without it the free tier fills with rows nothing reads.

Cache the expensive ones at the edge, keyed on something that changes when the data does.

Where D1 is the wrong tool

Sub-second freshness on aggregates. Batching means a delay. If you need genuinely live numbers, that belongs in memory in a Durable Object, not in D1 — which is also why a real-time panel should cost no database reads at all.

Ad-hoc analytical queries over years of raw events. That is a warehouse. D1 is an operational database that happens to be very good at serving pre-computed answers.

Anything in the visitor's request path. Worth repeating because it is the mistake with the worst consequences: the collector should resolve at the edge and hand off asynchronously. A slow D1 read must never be something a stranger's browser is waiting for.