Low latency API endpoints from analytical SQL

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^® 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.

The endpoint latency budget

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

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

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:

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:

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.

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:

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.

Caching: where it helps and where it lies

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^® 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.