---
title: "Low latency API endpoints from analytical SQL"
excerpt: "Sub-100ms API endpoints need rollups, bounded scans, and auth at the edge. A latency budget for ClickHouse® queries exposed as HTTP."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-09-02 00:00:00"
publishedOn: "2026-09-02 00:00:00"
updatedOn: "2026-09-02 00:00:00"
status: "published"
---

Product teams ask for **low latency API endpoints** when dashboards live inside the app, filters change on every interaction, and "refresh every 30 seconds" is not good enough. The database might answer in 40 ms. The endpoint still returns 800 ms because the API layer serializes too much, scans raw events, or opens a new connection per request.

Low latency is a **system property**, not a query hint. This post maps the latency budget for analytical endpoints backed by ClickHouse{% sup %}®{% /sup %} and where Tinybird Pipes fit when you do not want to build the API tier yourself. For the full build walkthrough, see [build real-time APIs on ClickHouse](https://www.tinybird.co/blog/build-real-time-apis-clickhouse-tinybird).

## The endpoint latency budget

Break every request into measurable segments. If you cannot attribute p99 to a segment, you cannot fix it.

| Segment | Typical target | What blows it up |
| --- | --- | --- |
| Auth + routing | 1-5 ms | JWT validation hitting a remote DB every request |
| Query queue wait | 0-20 ms | Shared cluster with batch jobs |
| ClickHouse execution | 10-80 ms | Full table scan, bad sort key, `SELECT *` |
| Serialization | 5-30 ms | Returning 50k rows as JSON arrays |
| Network | 5-40 ms | Cross-region client to database |

User-facing SLOs often land at **p99 under 100 ms** for rollup endpoints and **under 300 ms** for exploratory drill-downs. Define both. Do not point product traffic at raw fact tables.

## Raw facts vs rollup endpoints

| Endpoint type | Source table | When it is OK |
| --- | --- | --- |
| Live counters | 1-minute AggregatingMergeTree rollup | Dashboard tiles, KPI badges |
| Filtered breakdown | Hourly or daily rollup by bounded dims | Category charts, top-N lists |
| Event search | Raw MergeTree with strict time bound | Operator tools, not homepage load |
| Ad hoc SQL | Whatever the analyst typed | Internal only |

Expose **rollups on the hot path**. Keep raw events for authenticated drill-down with mandatory `event_time` lower bounds and row limits.

Example rollup table for API serving:

```sql
CREATE TABLE metrics.requests_1m
(
    minute DateTime,
    tenant_id LowCardinality(String),
    route LowCardinality(String),
    count_state AggregateFunction(count),
    p95_state AggregateFunction(quantile(0.95), Float64)
)
ENGINE = AggregatingMergeTree
ORDER BY (tenant_id, route, minute);
```

Query with `-Merge` combinators:

```sql
SELECT
    route,
    countMerge(count_state) AS requests,
    quantileMerge(0.95)(p95_state) AS p95_ms
FROM metrics.requests_1m
WHERE tenant_id = {tenant:String}
  AND minute >= now() - INTERVAL 15 MINUTE
GROUP BY route
ORDER BY requests DESC
LIMIT 20;
```

For materialized view patterns, see [ClickHouse create materialized view example](https://www.tinybird.co/blog/clickhouse-create-materialized-view-example).

## Parameterized endpoints, not string-built SQL

Low latency and SQL injection resistance both require **bound parameters**. Dynamic SQL built with string concatenation from URL query params fails security review and prevents plan cache reuse.

Tinybird Pipes template parameters the same way:

```sql
NODE tenant_routes
SQL >
    SELECT
        route,
        countMerge(count_state) AS requests
    FROM requests_1m
    WHERE tenant_id = {{String(tenant_id, required=True)}}
      AND minute >= {{DateTime(since, required=True)}}
    GROUP BY route
    ORDER BY requests DESC
    LIMIT {{Int32(limit, 20)}}

TYPE endpoint
```

Use JWT **fixed_params** for tenant scoping values that must not be overridden from the URL. Product UIs pass the JWT; the endpoint enforces row scope server-side.

## Concurrency without melting the cluster

Analytical databases handle many concurrent **small** queries well. They suffer when hundreds of clients run identical heavy scans because nobody added a rollup.

Practices that hold up in production:

1. **Separate endpoints** for product (rollups) and internal (raw)
2. **Query timeouts** per token or role (`max_execution_time`)
3. **Result row caps** enforced in SQL (`LIMIT`) and HTTP layer
4. **Read replicas** or dedicated replica for API traffic when self-hosting
5. **Rate limits** at the API gateway before ClickHouse sees traffic

For ClickHouse cluster operations when API load drives replica sizing, see [ClickHouse cluster operations in production](https://www.tinybird.co/blog/clickhouse-cluster-operations).

## Caching: where it helps and where it lies

| Cache layer | Good for | Bad for |
| --- | --- | --- |
| CDN / edge | Static public aggregates | Per-tenant filtered data without cache keys |
| In-process memoization | Single-node dev | Multi-tenant correctness |
| ClickHouse rollups | Everything product-facing | N/A |
| Client polling 1s | Demos | Production cost and stale UI |

Rollups are the honest cache. Pre-aggregate the shapes product actually requests instead of caching raw query results for 5 seconds and hoping.

## Ingest freshness vs read latency

An endpoint cannot be fresher than ingest. Events API and Kafka connectors batch for healthy inserts. Budget **1-30 seconds** ingest lag into the product copy unless you operate sub-second flush tuning.

Monitor lag with Service Data Sources or `system.parts` metrics. Alert when p99 endpoint latency rises because parts are not merging, not because SQL changed.

## Tinybird for low latency endpoints

Tinybird ships the API layer on managed ClickHouse:

1. **Pipes** as HTTP endpoints with typed parameters and OpenAPI docs
2. **Events API** and **Kafka connector** for ingest without loader code
3. **Branches** to test endpoint SQL against production-shaped data
4. **JWT tokens** with fixed_params for tenant isolation
5. **Endpoint metrics** for p95 latency and error rate per published Pipe

Canva reports 3.6 PB processed per month and 54 ms p99 query latency on Tinybird's product page. Resend reports 100 TB per month and 62 ms p90 query latency without relying on cache. Tinybird is SOC 2 Type II certified.

## 5 mistakes that kill API latency

### 1. Pointing product traffic at raw events

Every dashboard filter scans billions of rows.

**Fix:** Rollup tables per endpoint shape. Raw tables for drill-down only.

### 2. Unbounded time ranges from client params

A user selects "all time" and melts the cluster.

**Fix:** Required `since` with server-side max window. Default to 24 hours.

### 3. Returning wide JSON blobs

Ten kilobytes of columns nobody renders.

**Fix:** Select only fields the UI reads. Aggregate server-side.

### 4. No connection pooling in custom API servers

New TCP+TLS to ClickHouse per request adds tens of milliseconds.

**Fix:** Pool in the API tier or use Tinybird's hosted endpoints.

### 5. One SLO for rollups and search

Search endpoints need higher p99. Rollups should be tighter.

**Fix:** Split routes and monitor separately.

## What low latency endpoints come down to

Low latency API endpoints are rollup-first, parameter-bound, and tenant-scoped. ClickHouse{% sup %}®{% /sup %} gives you fast aggregation when sort keys and schemas match the question. You still own auth, concurrency, and the HTTP contract unless you use a platform that publishes SQL as endpoints directly.

Measure p99 per segment, ship rollups before launch, and keep raw event search off the homepage critical path.

## Frequently Asked Questions (FAQs)

### What is a realistic p99 for user-facing analytics APIs?

Many teams target under 100 ms p99 on pre-aggregated endpoints and under 300 ms on bounded drill-downs.

### Do I need Redis if I use ClickHouse?

Often no for product KPIs if rollups answer the UI shapes. Redis helps session state and OLTP, not replacement for analytical pre-aggregation.

### Can ClickHouse serve thousands of concurrent API requests?

Yes on rollup queries with proper sort keys and replica capacity. Raw scans at high concurrency require careful isolation.

### How does Tinybird differ from a custom Node API on ClickHouse?

Tinybird publishes Pipes as authenticated HTTP endpoints with metrics and git-based deploys. You skip building and scaling the API wrapper.

### Should endpoints query materialized views or rollup tables?

Both work. AggregatingMergeTree rollups with `-Merge` combinators are flexible for multiple endpoint shapes from one table.

### Where do I start if latency is already bad?

Profile one slow endpoint. Check bytes read, rows returned, and connection setup time before tuning SQL text.

{% cta
  title="Ship sub-100ms endpoints without the API boilerplate"
  text="Tinybird turns SQL into authenticated HTTP endpoints on managed ClickHouse. Rollups, JWT scoping, and endpoint metrics included."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}