# Views

> Keep a SQL query over a namespace as a view. Its answer is kept as partials per segment, so asking it again only computes what changed, and it is always exact. Automatic views do the same for queries you repeat.

A view is a SQL query over one namespace, kept. Name it like a table in any SQL query (`SELECT * FROM daily_signups`, or join it), from the API or from an agent over MCP. It's built for the queries you ask again and again: a dashboard's tiles, a daily report, an agent checking the same totals every few minutes.

Views are **always exact**. There's no refresh to schedule and no staleness to reason about: every read answers as the namespace is now. (A `SUM` or `AVG` of floats adds the segments' sums in another order than one pass would, so its last digits can differ from the same query run without parts.)

## How it works

A namespace is stored as immutable segments: each write adds one, and compaction merges old ones into bigger ones. A view's query is split in two:

- **a part** that runs on one segment at a time: the same `WHERE`, grouped by the query's `GROUP BY` expressions, with each aggregate as something that merges (a count, a sum, a minimum, a maximum; an average as a sum and a count);
- **a merge** that combines the segments' parts (counts are summed, minimums of minimums taken, ...) and then applies `HAVING`, `ORDER BY` and `LIMIT`.

A segment's part depends only on its own rows and which of them are still visible. So it's kept, beside the segment, and reused until a newer write replaces or deletes one of that segment's rows. A write adds a small segment: the next read computes the part of that one segment and reuses the rest. A compaction makes a new segment: its part is computed once, ahead of time, in the background.

Every SQL answer says how it went, in `partials`:

```json
"partials": {"view": "daily_signups", "kind": "named", "computed": 1, "reused": 37, "remote": 0}
```

`computed` is the segments whose parts this query computed, `reused` those it reused, and `remote` those other servers of the cluster computed for it.

## Create a view

`POST /v1/namespaces/{name}/views` with a name and the query, over `docs` (write scope):

```sh title="curl"
curl -s $B/namespaces/events/views -H "Authorization: Bearer $KEY" \
  -d '{"name": "daily_signups", "sql": "SELECT DATE_TRUNC('\''day'\'', ts) AS day, plan, COUNT(*) AS signups, SUM(amount) AS revenue FROM docs WHERE kind = '\''signup'\'' GROUP BY 1, 2"}'
```

The answer has its columns and their types. Creating a view runs it once (metered as one query), so its parts are ready for the first read.

Then read it, alone or joined:

```sh title="curl"
curl -s $B/sql -H "Authorization: Bearer $KEY" \
  -d '{"sql": "SELECT day, SUM(signups) AS signups FROM daily_signups WHERE day >= 1790000000 GROUP BY day ORDER BY day"}'
```

```python title="Python"
from skimmerdb import SkimmerDB

db = SkimmerDB()
r = db.namespace("events").sql("SELECT * FROM daily_signups ORDER BY day, plan")
print(r.rows)
```

```ts title="TypeScript"
import { SkimmerDB } from "skimmerdb";

const db = new SkimmerDB();
const r = await db.namespace("events").sql("SELECT * FROM daily_signups ORDER BY day, plan");
console.log(r.rows);
```

`GET /v1/namespaces/{name}/views` lists a namespace's views, `GET .../views/{view}` shows one and `DELETE .../views/{view}` deletes it. Over MCP the tools are `create_view`, `list_views` and `delete_view`, and `sql_query` reads views by name.

A view's name is the tenant's: it can't be the name of one of your namespaces (or `docs`), and a key limited to some namespaces sees only the views of those. A view goes with its namespace.

## What a view's query can be

One table, `docs`, with aggregates that merge from parts:

| Works | |
|---|---|
| `COUNT(*)`, `COUNT(x)`, `SUM`, `MIN`, `MAX`, `AVG` | |
| `COUNT(DISTINCT x)`, `SUM(DISTINCT x)`, `AVG(DISTINCT x)` | each part is grouped by `x` too: keep `x`'s values few per group |
| `COUNT_IF(cond)`, `f(...) FILTER (WHERE cond)` | |
| Any `WHERE`, `contains(text, ...)` and `grep(text, ...)` included | |
| `GROUP BY` any expressions: columns, `DATE_TRUNC('day', ts)`, `CASE`, `n % 10`, ... | |
| `HAVING`, `ORDER BY`, `LIMIT` | applied after the merge |
| Expressions over aggregates: `SUM(a) / COUNT(*)` | |

Anything else (`MEDIAN`, `STRING_AGG`, window functions, joins, `SELECT DISTINCT`, rows without aggregates, `LIMIT` without `ORDER BY`, `CURRENT_DATE`) is refused with `422 view_not_decomposable` and the reason. Those still run as ordinary queries.

## Automatic views

You don't have to create views to get this. A query over one namespace whose aggregates merge from parts always runs as parts and a merge, and the parts are kept in memory while they're used. When the same **shape** of query comes back often (three times in a day) on a namespace big enough to matter (a million rows, or half a second a query), it becomes an automatic view: its parts are also kept beside the segments and computed ahead after writes and compactions. `partials.kind` is then `auto`.

The shape ignores the literals of simple filters: `WHERE plan = 'pro'` and `WHERE plan = 'team'` are the same shape. The automatic view groups by those filters' columns too, so every literal is answered from the same parts. When that would make the parts too big (a filter on a column with many values, such as a timestamp range), it keeps the exact query's parts instead.

Automatic views are capped (4 a namespace, 32 a tenant, on each server) and one unused for a day goes.

## Across servers

With several servers, a query's parts run on the server that owns the namespace, and a big namespace's segments are split over the servers, each computing and keeping its share. A join's scans of a big namespace are split the same way, with their filters. Any server that doesn't answer in time is left out: its share is computed where the query arrived. The answer is always the same as one server's, and it's one query for metering, however many servers worked on it.

## Cost

The kept parts are small: they hold one row a group, not a row a document. A part is stored beside its segment only for segments of 64k rows or more, at most 1 MB and at most 1/64 of the segment's size each, and at most 16 a segment. Reading a view, or a repeated query, reads those parts instead of the segments' columns. See [Limits and plans](/reference/limits) for the number of views a plan has (20 on free).