“The edge is the only place an architecture is actually tested.”
Writes, live reads, and a year of history — pick three
Two million writes a day, read live in a customer’s dashboard, with the annual report returning in under a second — for the accounts holding nine hundred listings.
We designed and built the analytics layer for an advertising marketplace. It took over two million writes a day, fed a dashboard that updated live, and answered a full year of history in under a second.
Those three properties normally exclude one another. A database fast at ingesting is slow at complex queries. A year of raw data at that rate is hundreds of millions of rows. The answer wasn’t a faster database — it was refusing to make one database do all three jobs.
The problem
The constraint
What we did
A time-series database for ingest, deliberately with a small index
Identifiers — listing, user, device — went into fields rather than tags. In a time-series store, tags are indexed and fields are not; putting a listing ID in a tag means every new listing creates a new series and the index eventually suffocates. That is the single most common way these installations die.
Cost: no grouping by listingDaily rollup into the relational database
Raw events live seven days, with a daily export to object storage as archive. A daily per-listing summary lands in the relational store, and that is what the dashboard and the invoices query. The annual report therefore never touches a raw row.
365 rows per listingMeasured at the worst case, not the average
Under one second holds for accounts with over 900 listings — a year of daily rollups for nine hundred listings is roughly 330,000 rows aggregated in a single query. “Under a second” means nothing if the account has three listings; the edge is the only place an architecture is actually tested.
330,000 rows in one queryTotals and uniques in separate buckets
Unique views keyed by session and by page URL, so someone searching in two cities isn’t counted as one view — they were looking for two different things. Totals are the bigger number and look better; uniques are what actually tells an advertiser how many different people saw them. Keeping both means there’s nothing to hide.
Nothing to hideBots filtered at three layers
Firewall, known addresses, user agent. On an advertising platform an inflated view count isn’t an accuracy problem, it’s a trust problem. An advertiser who sees five hundred views and no calls stops believing the number, and then stops believing the invoice.
Trust, not accuracy
Two million writes a day needs a database designed for writing. The cost is that it is slow at complex queries, so the read job must not be left to it. Identifiers go in fields rather than tags: a tag per listing means a series per listing, and the index eventually suffocates.
The result
- 2M+ writes per day
- Live reads in the advertiser’s dashboard
- Annual report under one second at 900+ listings
- Same data feeding the yearly report that goes out before renewal
Nielsen’s response-time thresholds, unchanged since 1993. Under a second is not an arbitrary target — it is the boundary below which a user does not lose their train of thought.
What we’d do differently
Seven days of raw retention was chosen early and never revisited. It was almost certainly right, but we never checked what we lost by it — whether anyone ever needed to reprocess a window longer than a week, and what it would have cost to keep thirty days. We picked a number and moved on.
- InfluxDB
- MySQL
- WebSockets
- Node.js
- AWS