Storing Telemetry From AI Calls

Telemetry from language model calls has an awkward shape: high volume, high cardinality, and questions that span months. Design the table backwards from the queries and it stays small and fast. Design it forwards from “log everything” and you get a table nobody can query and a metrics bill larger than your inference bill.

The six queries you will actually run

Every schema decision below follows from this list. It is worth writing your own version before you create the table, because the list is shorter than people expect and it rules a great deal out.

1. What did we spend last month, broken down by tenant, by feature, and by model?
2. What is p50 and p95 time to first token for model X this week, and how does it compare with last week?
3. What is the error rate by provider over the last hour, by error class?
4. Which requests took over thirty seconds yesterday, and why?
5. Which feature’s cost per request grew after the deploy on Tuesday?
6. Show me everything about request req_01H…, because a customer is asking.

Five of the six are aggregations over a time range grouped by a low-cardinality dimension. One is a point lookup by id. Nothing needs a join to a high-cardinality dimension, and nothing needs the prompt text — which is the observation that keeps this table small.

The table

CREATE TABLE llm_requests (
  id             text        NOT NULL,          -- your request id, e.g. ULID
  created_at     timestamptz NOT NULL,

  -- Low-cardinality dimensions. These are what you GROUP BY.
  tenant_id      uuid        NOT NULL,
  feature        text        NOT NULL,          -- 'chat', 'summarise', 'rag-answer'
  model          text        NOT NULL,
  provider       text        NOT NULL,
  environment    text        NOT NULL,
  status         text        NOT NULL,          -- 'ok' | 'error' | 'timeout'
  error_class    text,                          -- NULL when ok; bounded set

  -- Measures. Integers where possible: money in micro-units, not floats.
  ttft_ms        int,
  total_ms       int         NOT NULL,
  input_tokens   int         NOT NULL DEFAULT 0,
  output_tokens  int         NOT NULL DEFAULT 0,
  cached_tokens  int         NOT NULL DEFAULT 0,
  cost_micros    bigint      NOT NULL DEFAULT 0,   -- millionths of a currency unit
  retries        smallint    NOT NULL DEFAULT 0,

  -- High-cardinality identifiers: stored, never grouped by.
  user_id        uuid,
  conversation_id uuid,

  PRIMARY KEY (created_at, id)
) PARTITION BY RANGE (created_at);

CREATE INDEX llm_requests_brin ON llm_requests USING brin (created_at);
CREATE INDEX llm_requests_tenant_time ON llm_requests (tenant_id, created_at DESC);
CREATE INDEX llm_requests_id ON llm_requests (id);

Three choices in there earn their place.

• Money as an integer. cost_micros as a bigint in millionths. Floating-point money accumulates error over a million rows and produces monthly totals that do not reconcile, which is a conversation with finance you do not want to have twice.
• A BRIN index on the timestamp. On append-only time-ordered data a BRIN index stores a min and max per block range, so it is kilobytes where a B-tree would be gigabytes, and it answers range scans nearly as well. This is the single best-value index in Postgres and it exists for exactly this table shape.
• No prompt text. Not a column here. Prompts and completions are large, are sometimes personal data, and have a different retention rule from metrics — see PII in your logs. They belong in a separate table with its own lifecycle, joined by id when you need query six.

Cardinality: the rule and the failure

If you also export these as metrics — Prometheus, StatsD, anything with labels — there is one rule and it is absolute: a label’s value set must be small and bounded. The reason is that a time-series database creates one series per unique combination of label values, and each series carries fixed overhead whether or not it is ever queried.

series = product of the cardinalities of every label

Safe:
  model (12) × feature (8) × status (3) × environment (2)
    = 576 series

Add tenant, 5,000 customers:
  576 × 5,000 = 2,880,000 series

Add user_id, 200,000 users:
  2,880,000 × 200,000 = 576,000,000,000 series

Add request_id: one series per request, each holding one point,
retained for as long as your retention window. This is the
configuration that takes down the metrics cluster, and it is
one line of code.

The rule that follows: high-cardinality identifiers go in database rows, low-cardinality dimensions go in metric labels, and the two systems answer different questions. The row store answers “what happened to this request”; the metrics store answers “what is happening right now, in aggregate”. Asking either one to do the other’s job is the mistake.

error_class is on the boundary and deserves care. Store a normalised class — rate_limit, context_length, upstream_5xx, timeout — not the provider’s message string. Raw messages frequently embed a request id or a token count, which makes them unbounded, and an unbounded label is the failure above arriving by a side door.

Retention tiers and partitions

Three tiers, because the questions have different time horizons and different resolution needs.

Partition the raw table by day and dropping a day is a catalogue operation rather than a delete of millions of rows:

CREATE TABLE llm_requests_2026_08_04 PARTITION OF llm_requests
  FOR VALUES FROM ('2026-08-04') TO ('2026-08-05');

-- Expiry, at O(1) instead of O(rows):
DROP TABLE llm_requests_2026_07_05;

-- Compare with the alternative, which on a busy table can run for
-- hours, generate WAL proportional to the rows, and leave bloat
-- that autovacuum then has to work through:
--   DELETE FROM llm_requests WHERE created_at < now() - interval '30 days';

The rollup itself is one insert per hour, and it should be idempotent so a failed run can simply be repeated:

CREATE TABLE llm_requests_hourly (
  bucket        timestamptz NOT NULL,
  tenant_id     uuid NOT NULL,
  feature       text NOT NULL,
  model         text NOT NULL,
  status        text NOT NULL,
  requests      bigint NOT NULL,
  input_tokens  bigint NOT NULL,
  output_tokens bigint NOT NULL,
  cost_micros   bigint NOT NULL,
  total_ms_sum  bigint NOT NULL,          -- for a mean
  ttft_hist     int[]  NOT NULL,          -- see the next section
  PRIMARY KEY (bucket, tenant_id, feature, model, status)
);

INSERT INTO llm_requests_hourly AS h
SELECT date_trunc('hour', created_at), tenant_id, feature, model, status,
       count(*), sum(input_tokens), sum(output_tokens),
       sum(cost_micros), sum(total_ms),
       histogram(ttft_ms)                 -- your bucketing function
FROM llm_requests
WHERE created_at >= $1 AND created_at < $1 + interval '1 hour'
GROUP BY 1,2,3,4,5
ON CONFLICT (bucket, tenant_id, feature, model, status)
DO UPDATE SET requests = EXCLUDED.requests,
              cost_micros = EXCLUDED.cost_micros;

Why you cannot average a p95

This is the modelling mistake that survives longest, because the resulting numbers look reasonable. Percentiles are not additive. The mean of twenty-four hourly p95 values is not the daily p95, and it is not an approximation of it either — it can be off in either direction by an arbitrary amount.

Two hours, ten requests each, latencies in ms.

Hour A:  100 ×9,  5000 ×1     -> p95(A) ≈ 5000
Hour B:  100 ×10               -> p95(B) = 100

Mean of the hourly p95s = (5000 + 100) / 2 = 2550 ms

True p95 over all 20 requests: sorted, the 19th value is 100
and the 20th is 5000, so p95 ≈ 100–5000 depending on the
interpolation rule — and with 39 more quiet hours in the day
the true daily p95 is 100 ms.

The "average p95" of 2550 ms is not close to either answer.

There are two correct responses and one wrong one. The wrong one is storing the percentile and averaging it anyway.

• Store a histogram per bucket. Fixed exponential boundaries — 10, 25, 50, 100, 250, 500, 1000, 2500, 5000, 10000, 30000 ms — with a count per bucket. Histograms add: sum the arrays element-wise across hours and read the percentile off the total. That is the array column in the rollup table above, and it is why it is an array rather than a number.
• Or compute from raw, within the raw retention window. percentile_cont(0.95) WITHIN GROUP (ORDER BY total_ms) over the raw table is exact and cheap enough for a month. Past thirty days you no longer have the raw rows, which is precisely when the histogram earns its place.

-- Exact, from raw:
SELECT model,
       percentile_cont(0.50) WITHIN GROUP (ORDER BY ttft_ms) AS p50_ttft,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY ttft_ms) AS p95_ttft,
       count(*) AS n
FROM llm_requests
WHERE created_at >= now() - interval '7 days' AND status = 'ok'
GROUP BY model
ORDER BY p95_ttft DESC;

Note the status = 'ok' filter. Failed requests frequently return fast, so including them drags the percentiles down and hides exactly the degradation you are watching for. Why the percentile matters more than the mean for streaming workloads is in measuring p50, p95 and p99 for LLM calls.

Cost per request, stored not computed

Compute the cost at write time from the price in force at that moment, and store it. Do not store tokens alone and multiply by a current price table at query time — when a price changes, every historical figure silently changes with it, and your month-on-month comparison becomes meaningless in a way nobody notices for a quarter.

-- The price table is versioned by validity window, and the cost is
-- resolved once, at write time, against the window that was in force.
CREATE TABLE model_prices (
  model            text NOT NULL,
  valid_from       timestamptz NOT NULL,
  valid_to         timestamptz,
  input_micros_per_1k  bigint NOT NULL,
  output_micros_per_1k bigint NOT NULL,
  PRIMARY KEY (model, valid_from)
);

-- cost_micros written on the request row:
--   input_tokens  / 1000 × input_micros_per_1k
-- + output_tokens / 1000 × output_micros_per_1k

Then the monthly bill by tenant is one grouped sum over the daily rollup, it reconciles with the invoice, and it stays correct when prices change. Cost attribution that survives a refactor is the design work upstream of this: the feature column is only useful if something guarantees it is set consistently.

A final decision that becomes expensive to reverse: whether to sample. At high volume, storing every request row is the largest single contributor to this table’s size, and the temptation is to keep one in ten. Do not sample uniformly. Sample the successful, fast, unremarkable requests and keep everything that is an error, a timeout, a retry or above a latency threshold — because the rows you want during an incident are precisely the rare ones, and a uniform sample throws away nine tenths of them.

keep the row if:
     status <> 'ok'
  OR retries > 0
  OR total_ms > p99_threshold
  OR hash(request_id) % 10 = 0        -- 10% of the boring ones

Then the count is no longer the request count, so store the
sampling weight and multiply:

  estimated_requests = sum(weight)
  where weight = 1 for a kept-by-rule row, 10 for a sampled one.

Cost and token totals must NOT be sampled at all — they need to
reconcile with an invoice. Sample the rows, aggregate the money
before sampling.

That last constraint is the one that decides the design: money is summed into the rollup at write time from every request, and the sampling applies only to the raw rows kept for investigation. Mixing the two produces a monthly total that is approximately right, which is a worse property for a billing figure than being obviously absent.

Where the table itself should live is the remaining question, and the answer for most teams is the database they already run. Postgres with daily partitions and a BRIN index handles tens of millions of rows a month comfortably, and keeping telemetry next to the application data means a cost query can join to your customers table without an export. A column store becomes the right answer when the raw table passes roughly a billion rows or when analytical scans start competing with transactional traffic for the same buffers — and the signal for that is your rollup job taking longer than the interval it rolls up, which is a thing to alert on rather than to notice.