InfluxDB cardinality: keep user and device IDs out of tags

I designed 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.

The first schema decision in that system turned out to matter most. For each identifier it came down to one question: does this value go in a tag or in a field?

I put every identifier in a field, including the listing, user and device IDs, and made none of them tags. The rest of this post is about why, what that cost, and what made up for it.

Here is an event as most people would first write it, for someone viewing a listing on some device:

1listing_event,listing_id=48213,user_id=u_9f2c,device_id=d_71ab,event=view count=1i 1717430400000

It reads well. Everything you might filter by is in the tag set, and the only field is the thing you're counting. Most examples you'll find have this shape, with a host or a region in the tags.

The trouble is that host and region take their values from small, fixed sets. Listing IDs, user IDs and device IDs keep growing.

InfluxDB indexes tags and not fields. The cost depends on what that index holds, which is series.

A series is a measurement plus one exact combination of tag values. Every distinct combination the database has ever seen is a separate series with its own entry in the index. Fields play no part in this. You can write a million distinct field values into one series and it stays one series.

The number of series is therefore bounded by the product of the number of distinct values in each tag:

1series(listing_event) <= |listing_id| x |user_id| x |device_id| x |event|

In practice you won't reach the full product, because not every user touches every listing, but you don't have to. With a user and a device in the tag set, every new visitor creates new series, and an advertising platform exists to bring in new visitors.

That number is the series cardinality, and it only goes up. A tag with a small, fixed set of values adds a known number of series. An identifier adds new ones as traffic grows, so with identifiers in the tags the index grows with your traffic instead of with your schema.

A growing index uses more memory, slows down writes, and makes queries that used to be instant touch far more of it than they should. It holds up at launch and fails once your traffic is good, which is the worst possible time. That is the most common way these installations die.

Identifiers go in fields. Tags are kept for values that come from a small, known set and that you filter by:

1import { InfluxDB, Point } from '@influxdata/influxdb-client'2 3const writeApi = new InfluxDB({ url: INFLUX_URL, token: INFLUX_TOKEN })4  .getWriteApi(INFLUX_ORG, INFLUX_BUCKET, 'ms')5 6export function recordEvent(e) {7  const point = new Point('listing_event')8    // tags: a closed set, known before the first write9    .tag('event', e.type)10    // fields: anything that grows with traffic11    .stringField('listing_id', String(e.listingId))12    .stringField('user_id', e.userId)13    .stringField('device_id', e.deviceId)14    .stringField('session_id', e.sessionId)15    .stringField('page_url', e.pageUrl)16    .intField('count', 1)17    .timestamp(e.at)18 19  writeApi.writePoint(point)20}

The same event in line protocol:

1listing_event,event=view listing_id="48213",user_id="u_9f2c",device_id="d_71ab",count=1i 1717430400000

Each candidate tag gets the same short test. Can I write down every value it will ever have? If I can, it may be a tag. If the list grows every time someone signs up, lists a business or opens the site on a new phone, it's a field.

The event type passes. We decide that set in code, and traffic doesn't change it. A listing ID fails, because every new listing is a new value, and new listings are what the business wants more of.

With listing IDs in fields, you can't group by listing efficiently inside the time-series store. In InfluxQL, GROUP BY accepts tags and time but not fields. Other query paths can group by any column, but only after reading the rows, so they scan the whole time window instead of going straight to a series. A per-listing report over a year is the worst case for that kind of work.

So the obvious objection is right. The question the business asks most often, "how is my listing doing?", is the one this schema makes expensive to answer from the time-series store. That was acceptable, because the time-series store was never meant to answer it.

The design works because of a second decision made alongside the first. Raw events stay in the time-series store for seven days. Once a day, a summary per listing is written to the relational database, and the dashboard and the invoices query that summary. The raw data goes to object storage as an archive.

The time-series store has one job: take the writes and serve recent time windows. It never has to answer a question grouped by listing over a long period, and that is the only kind of question that putting identifiers in fields makes slow.

The rollup goes into a plain MySQL table:

1CREATE TABLE listing_daily (2  listing_id  BIGINT UNSIGNED NOT NULL,3  day         DATE            NOT NULL,4  event       VARCHAR(64)     NOT NULL,5  total       INT UNSIGNED    NOT NULL,6  uniques     INT UNSIGNED    NOT NULL,7  PRIMARY KEY (listing_id, day, event)8);

The daily job reads one day of raw events, where a scan is cheap because the window is small, and writes one row per listing, day and event. One way to write it:

1async function rollUp(day) {2  const flux = `3    from(bucket: "${INFLUX_BUCKET}")4      |> range(start: ${day.start}, stop: ${day.stop})5      |> filter(fn: (r) => r._measurement == "listing_event")6      |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")7      |> keep(columns: ["_time", "event", "listing_id", "session_id", "page_url"])8  `9 10  const buckets = new Map()11  for await (const { values, tableMeta } of queryApi.iterateRows(flux)) {12    const row = tableMeta.toObject(values)13    const key = `${row.listing_id}|${row.event}`14    const b = buckets.get(key) ?? { total: 0, seen: new Set() }15    b.total += 116    b.seen.add(`${row.session_id}|${row.page_url}`)17    buckets.set(key, b)18  }19 20  const rows = [...buckets].map(([key, b]) => {21    const [listingId, event] = key.split('|')22    return [listingId, day.date, event, b.total, b.seen.size]23  })24 25  await mysql.query(26    `INSERT INTO listing_daily (listing_id, day, event, total, uniques)27     VALUES ?28     ON DUPLICATE KEY UPDATE total = VALUES(total), uniques = VALUES(uniques)`,29    [rows],30  )31}

The job keeps totals and uniques apart. Uniques are keyed by session and page URL, so someone who searches in two cities counts twice, because they were looking for two different things. Totals are the larger number and look better in a report, while uniques tell an advertiser how many different people saw their listings. Keeping both in the table means neither number has to be hidden.

The upsert makes the job safe to rerun. If a day is rolled up twice, the second run overwrites the first instead of doubling it.

In the raw store, the session and the page URL are fields too. The unique count needs them, and both are values that grow with traffic.

The annual report never touches a raw row. It reads 365 summarized rows per listing:

1SELECT d.listing_id, d.event, SUM(d.total) AS total2FROM listing_daily d3JOIN listings l ON l.id = d.listing_id4WHERE l.account_id = ?5  AND d.day >= CURDATE() - INTERVAL 1 YEAR6GROUP BY d.listing_id, d.event;

I measured the worst case. For accounts with over 900 listings, a year of daily rollups is roughly 330,000 rows aggregated in a single query, and it returns in under one second. "Under a second" means nothing on an account with three listings, so the largest accounts are the ones worth measuring.

The same split also mattered for the hosting bill, which is covered in its own post. For the index, the result was that it stayed small, because nothing in it grew with traffic.

A time-series store is good at "what happened recently, sliced a few fixed ways" and bad at "what happened to this one entity over a long time". The second is a relational question, and it has a relational answer: a summary table with a primary key.

Once the long-range questions go to the relational database, the tag decision is easy. Tags cover the fixed slices. Identifiers belong to the long-range questions, so they don't need an index in a store that doesn't answer those questions.

When I see identifiers used as tags, the reason is rarely that someone didn't know better. More often the time-series store is the only database in the design, so it has to answer every question, including the one it handles worst, and the index grows with the traffic to pay for that.

I'd revisit the seven-day window. It was chosen early and never checked. It was almost certainly right, but I never looked at whether anyone needed to reprocess a window longer than a week, or what thirty days would have cost. The rollup design depends on that number, and I picked it and moved on.

I'd review the event types on a schedule. The analytics tracked around 180 event types over four years, with no regular review of which ones anyone still looked at. Some certainly stopped being used and kept being written. The tag test above keeps the index small, but it doesn't stop a closed set from growing, and nobody was removing event types from this one.

I'd also write the tag test down before the first write. A schema is cheap to change on day one and expensive once the series exist. The decision fits in one question, and it's worth putting that question where the next person adding a tag will see it.