# Analytics with SQL

> Every namespace is also a column store. Group, count and aggregate your documents with SQL, combined with exact text and regex matches.

Every namespace is also a column store: the metadata of the documents you search is stored as
typed, compressed columns that SQL reads directly. There's no warehouse to load, no pipeline to
keep in sync, and nothing running while you don't query. The text index works inside `WHERE`
too: find the log lines with a regex and group them, in one query.

`POST /v1/namespaces/{name}/sql` with `{"sql": "SELECT ..."}` (read scope), or the MCP tool
`sql_query`.

## How fast

ClickBench's 43 queries over its 10M-row web-analytics table, imported through the API, warm,
on one 8-vCPU server:

| | Hot geometric mean |
|---|---|
| **SkimmerDB, through HTTP** (auth, limits, metering and JSON included) | **9.3 ms** |
| `clickhouse-local`, same box, official MergeTree schema | 28.5 ms |

SkimmerDB was faster on all 43 queries. Importing those 10M rows as CSV took 23 seconds.

That is one benchmark, warm, with 1.5 GB of RAM cache; with 128 MB it was 14.8 ms and 34 to 37
of 43 faster. The column engine underneath, run on its own at 100M rows on 16 vCPUs, was
2.9-3.2x faster than ClickHouse and 2.1-2.6x faster than DuckDB by geometric mean (hot, 8 and
16 threads), in 25-31% less memory than ClickHouse. Broad SQL is where they're still ahead:
see [below](#where-it-stands-against-clickhouse-and-duckdb). More on the
[benchmarks page](https://skimmerdb.com/benchmarks).

## An example: what your agents did

Write one document per tool call. Give it a unique, meaningful id, the searchable part in
`text`, flat typed metadata, and the time as integer seconds (see
[Data model](/guides/data-model)):

```sh title="curl"
B=https://api.skimmerdb.com/v1
curl -s $B/namespaces/events/docs -H "Authorization: Bearer $KEY" -d '{"docs": [
  {"id": "agent-1/run-42/step-3", "text": "agent called search: error E504 upstream timeout",
   "meta": {"ts": 1760572937, "agent": "agent-1", "tool": "search", "ok": false, "latency_ms": 912.4, "tokens": 1840}},
  {"id": "agent-1/run-42/step-4", "text": "agent called sql_query",
   "meta": {"ts": 1760572951, "agent": "agent-1", "tool": "sql_query", "ok": true, "latency_ms": 38.0, "tokens": 220}}
]}'
```

```python title="Python"
events = db.namespace("events")
events.write([
    {"id": "agent-1/run-42/step-3", "text": "agent called search: error E504 upstream timeout",
     "meta": {"ts": 1760572937, "agent": "agent-1", "tool": "search", "ok": False, "latency_ms": 912.4, "tokens": 1840}},
    {"id": "agent-1/run-42/step-4", "text": "agent called sql_query",
     "meta": {"ts": 1760572951, "agent": "agent-1", "tool": "sql_query", "ok": True, "latency_ms": 38.0, "tokens": 220}},
])
```

```ts title="TypeScript"
const events = db.namespace("events");
await events.write([
  { id: "agent-1/run-42/step-3", text: "agent called search: error E504 upstream timeout",
    meta: { ts: 1760572937, agent: "agent-1", tool: "search", ok: false, latency_ms: 912.4, tokens: 1840 } },
  { id: "agent-1/run-42/step-4", text: "agent called sql_query",
    meta: { ts: 1760572951, agent: "agent-1", tool: "sql_query", ok: true, latency_ms: 38.0, tokens: 220 } },
]);
```

Then ask:

```sql
-- calls, failures and latency per tool
SELECT tool, COUNT(*) AS calls, SUM(CASE WHEN ok = 0 THEN 1 ELSE 0 END) AS errors,
       AVG(latency_ms) AS avg_ms, MAX(latency_ms) AS max_ms
FROM docs GROUP BY tool ORDER BY calls DESC

-- an hourly series for a dashboard
SELECT DATE_TRUNC('hour', ts) AS hour, COUNT(*) AS calls, SUM(tokens) AS tokens
FROM docs WHERE ts >= 1760572800 GROUP BY hour ORDER BY hour

-- the text index and SQL together: who hits 5xx errors
SELECT agent, COUNT(*) AS n FROM docs WHERE grep(text, 'E5[0-9]{2}') GROUP BY agent ORDER BY n DESC

-- one run, by its id
SELECT id, tool, ok, latency_ms FROM docs WHERE id LIKE 'agent-1/run-42/%' ORDER BY id

-- distinct agents active in a window
SELECT COUNT(DISTINCT agent) FROM docs WHERE ts >= 1760572800 AND ts < 1760659200
```

Sending one:

```sh title="curl"
curl -s $B/namespaces/events/sql -H "Authorization: Bearer $KEY" -d @- <<'EOF'
{"sql": "SELECT tool, COUNT(*) AS calls FROM docs GROUP BY tool ORDER BY calls DESC"}
EOF
```

```python title="Python"
res = events.sql("SELECT tool, COUNT(*) AS calls FROM docs GROUP BY tool ORDER BY calls DESC")
print(res.columns, res.rows)
```

```ts title="TypeScript"
const res = await events.sql("SELECT tool, COUNT(*) AS calls FROM docs GROUP BY tool ORDER BY calls DESC");
console.log(res.columns, res.rows);
```

The answer:

```json title="Answer"
{"namespace": "events", "columns": ["tool", "calls"], "types": ["text", "int"],
 "rows": [["sql_query", 136], ["search", 134]], "chunks": 1, "scanned": 1,
 "partial": false, "truncated": false, "took_ms": 0.9, "temperature": "warm", "store_gets": 0, "store_bytes": 0}
```

`DATE_TRUNC` gives a `time` value (`"2025-10-16 01:00:00"`); booleans like `ok` are `1` / `0`.

## Columns and types

The columns are `id` (the document id) and every top-level `meta` key. Types come from the
values: integers and booleans are `int`, other numbers `float`, anything else `text` (objects
and arrays as their JSON). A key whose values change type is read at the widest type seen
(`int` < `float` < `text`). Missing and JSON `null` values are SQL `NULL`. Run `SHOW COLUMNS`
to see every column, its type and how many segments hold it.

`text` itself isn't a column: reach it with `contains(text, '...')` and `grep(text, '...')`.

## Patterns

- **Time series.** Times as integer seconds (or an imported `time` column),
  `DATE_TRUNC('minute' | 'hour' | 'day', ts)`, and a `WHERE ts >= ...` window. Blocks outside
  the window are skipped by their min/max without being read.
- **Rates.** Count the parts in one query (`COUNT(*)`, `SUM(CASE WHEN ok = 0 THEN 1 ELSE 0
  END)`) and divide in your code: SQL arithmetic is `+` and `-` for now.
- **Top-N.** `GROUP BY ... ORDER BY n DESC LIMIT 10`.
- **Needle in the logs.** `contains(text, 'payment failed')` (literal, ignoring case) and
  `grep(text, 'ERR-[0-9]+')` (RE2) anywhere in `WHERE`, up to 8 a query, combined with any
  other condition. Blocks with no match are skipped before a column is read.
- **The current state, not the history.** A replaced or deleted document never counts, so a
  namespace of `ticket-<n>` documents answers "how many are open now". For the history, write
  each change as its own event (`ticket-<n>/<version>`).

## From files

[Import](/guides/import) CSV, NDJSON or Parquet straight into columns. A CSV with a header row
gets typed columns, and the columns you name in `text=` stay searchable:

```text title="orders.csv"
order_id,placed_at,country,amount,note
A-1,2026-10-01 09:15:00,US,120.5,gift wrap please
A-2,2026-10-01 10:40:00,DE,80,
A-3,2026-10-02 08:05:00,US,42.25,refund requested
```

Import it:

```sh title="curl"
curl -s "$B/namespaces/orders/import?format=csv&id=order_id&text=note" \
  -H "Authorization: Bearer $KEY" --data-binary @orders.csv
```

`placed_at` becomes a `time` column you can compare with `'2026-10-01'` and bucket by day:

```sql
SELECT DATE_TRUNC('day', placed_at) AS d, country, SUM(amount) AS revenue, COUNT(*) AS orders
FROM docs WHERE placed_at >= '2026-10-01' GROUP BY d, country ORDER BY d, revenue DESC

SELECT COUNT(*) FROM docs WHERE contains(text, 'refund')
```

## Where it stands against ClickHouse and DuckDB

The engine is fast on what it runs; the SQL is a subset, and growing.

| | Today |
|---|---|
| `SELECT ... FROM docs` with `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`, `LIMIT` / `OFFSET` | Yes |
| `COUNT`, `COUNT(DISTINCT)`, `SUM`, `AVG`, `MIN`, `MAX`, `CASE WHEN`, `COALESCE`, `LIKE`, `IN`, `IS NULL`, `DATE_TRUNC`, `EXTRACT(MINUTE ...)`, `REGEXP_REPLACE`, `LENGTH` | Yes, with SQL `NULL` semantics |
| Full-text and regex predicates in `WHERE` (`contains`, `grep`) | Yes, through the text index |
| Search, vectors and SQL on one copy, on S3, nothing running while idle | Yes |
| Joins, subqueries, `UNION`, CTEs, window functions | Not yet: one namespace, one table a query |
| `*`, `/`, `%`, `BETWEEN`, `NULLIF`, `STDDEV`, `MEDIAN`, `SELECT DISTINCT` | Not yet (use `GROUP BY` for distinct values) |
| Answer size | Up to 10,000 rows (`truncated: true` past it); `LENGTH` counts bytes |

Dashboards, product and agent analytics, log and trace exploration, and "count and group what
my search found" fit today, on data already in SkimmerDB. A star schema with joins, or a BI
tool generating arbitrary SQL, is still a warehouse's job.

## Limits and cost

A SQL query is metered and limited as one query. Each plan sets a time limit (10 seconds on
free, 30 on pro) and how many SQL queries a tenant runs at once (2 and 8; `429` past it). A
query's groups, distinct values and rows in flight, and what it reads from object storage, are
capped by a share of the server's memory: past a cap it stops with a `422` (or `413` for
reads, refused before reading anything), never taking the server down.

The full dialect, operators, `NULL` rules and errors: [SQL reference](/reference/sql).