Low latency database from keys to rollups

A low latency database promise shows up on every vendor homepage. Sub-millisecond. Single-digit ms p99. Real-time. The number is meaningless until you attach query shape, concurrency, consistency requirements, and what sits between the client and storage.

A keyed read on three ScyllaDB replicas is not the same problem as a dashboard aggregating thirty days of events for ten thousand concurrent users. Both get called "low latency." Only one belongs on the product homepage API path. A warehouse query that finishes in eight seconds is low latency for finance close and unacceptable for an in-app usage chart.

This post builds a latency budget by access pattern, names the engine classes that can hit each tier, and explains where managed columnar SQL with published endpoints closes the gap between fast storage and fast product APIs.

Decompose latency before you pick an engine

End-to-end latency is a sum of segments. Optimizing SQL while ignoring connection pools or cross-region routing wastes quarters.

Low latency API endpoints from analytical SQL maps the HTTP and caching layer when ClickHouse® backs product APIs. This post focuses on database tier choices and rollup design before that wrapper exists.

Measure each segment under production concurrency, not a single curl from a laptop in the same AZ as the database.

Tier 1: Sub-10 ms keyed operational paths

Redis, DynamoDB, ScyllaDB, Cassandra, Memcached optimize partition-local reads and writes.

Query shapes that stay fast

• Single-key GET or narrow partition range scan
• Counter increments with idempotent writers and bounded cardinality
• Session blobs under TTL with read-modify-write on one key
• Feature rows pre-materialized for ML serving at inference time
• Conditional writes (UpdateItem with version checks) for optimistic locking

Query shapes that fail on this tier

• Ad hoc GROUP BY across partitions
• Full table scans for BI exports
• Multi-partition transactions without saga or two-phase design
• Unbounded fan-out reads (every user's entire history without pagination caps)

High-performance NoSQL covers wide-column compaction and consistency tuning when ops latency dominates the budget. ShareChat NoSQL modernization cites public ScyllaDB materials describing sub-millisecond P99 on tuned counter clusters when partition keys match access paths and compaction keeps read amplification bounded.

Target: 1–10 ms p99 on hot keys at declared RPS with production partition skew. Uniform load tests lie. Celebrity keys invalidate spreadsheet comparisons.

DynamoDB-specific latency traps

On-demand capacity scales, but per-partition throughput still caps hot keys. Adaptive capacity helps; it does not erase physics. AWS partition key design guidance documents the per-partition throughput ceiling that hot keys hit in production. The DynamoDB Connector mirrors tables into Tinybird for analytics without adding synchronous read load on the hot path.

Redis-specific latency traps

Sub-ms reads assume data fits RAM and persistence mode matches recovery expectations. AOF fsync policy, RDB snapshots, and cluster failover all add tail latency spikes invisible in average benchmarks.

Tier 2: Low millisecond OLTP SQL

Postgres, MySQL, CockroachDB deliver low millisecond point queries on indexed rows at moderate scale.

Indexed SELECT by primary key, short transactions on narrow rows, and connection-pooled ORM access stay fast until:

• Analytics queries share CPU with checkout on the primary
• Connection pools exhaust under burst (default pool sizes are often wrong)
• Replica lag makes "real-time dashboard on replica" a stale read
• Lock contention on hot rows (inventory, wallet balance) serializes writers
• Autovacuum or index bloat on large tables stalls unrelated queries

Split early: OLTP SQL for transactions, columnar store for analytics. How to handle analytics workloads in Postgres explains the tipping point when pg_stat_activity fills with long aggregations while product p99 climbs.

Target: 2–20 ms p99 for indexed point reads at moderate concurrency. Not billion-row scans.

Tier 3: 10–100 ms analytical SQL at product concurrency

Columnar OLAP engines (ClickHouse, Druid, Pinot, StarRocks) target aggregations over large datasets, not single-row PRIMARY KEY lookups.

Low latency here means:

• Rollups and materialized views match dashboard grain (minute, hour, day)
• Sort key and PREWHERE prune most bytes before aggregation
• Result sets stay small: top-N, bounded time windows, precomputed tiles
• Replicas or workload classes isolate heavy ingest merges from interactive queries
• Endpoint layer returns kilobytes, not megabytes, of JSON

Raw fact scans on every filter change produce 800 ms APIs even when the database query reports 40 ms. ClickHouse fast queries covers scan discipline. ClickHouse for time series metrics, rollups, and SLOs tiers 1m → 1h → 1d so tiles never read a month of raw samples.

ClickHouse real-time analytics frames freshness expectations: ingest lag plus query time plus API overhead must fit the product SLO, not only the SQL stopwatch.

Target: 10–100 ms p99 for rollup-backed endpoints at hundreds to thousands of concurrent dashboard users. Sub-10 ms is achievable on hot aggregates; sub-ms is the wrong goal for billion-row scans.

Tier 4: Warehouse and batch SQL (seconds, not milliseconds)

Snowflake, BigQuery, Redshift, Databricks SQL optimize cross-domain historical SQL at scale. Seconds per query is excellent for finance and BI. Calling that tier a low latency database for in-app analytics misaligns expectations.

Use warehouses when latency budgets allow minutes or when analysts are the primary consumers. Use columnar OLAP when product surfaces need sub-second tiles. Redshift vs ClickHouse contrasts the boundary when the same metrics appear in both BI tools and customer-facing dashboards.

Choosing a low latency database by question

NoSQL vs SQL frames the operational side. Compare top databases places warehouse and OLAP tiers on the same map so procurement does not collapse them into one RFP line.

Hardware, topology, and placement

Low latency database marketing skips infrastructure that often dominates tails:

• NVMe local SSD vs network-attached storage on cloud OLAP nodes
• Same-AZ placement for app, database, and Kafka consumers when possible
• Connection pooling (PgBouncer, JDBC pools, HTTP keep-alive to columnar HTTP interface)
• Read-your-writes requirements vs eventual replica reads for dashboards
• CPU steal and noisy neighbors on small shared instances

A misconfigured pool or cross-region default adds more latency than switching engine brands. ClickHouse real-time monitoring systems treat infra metrics as first-class signals when database p99 regresses without query changes.

Caching layers: where they help and where they lie

Caches do not fix wrong engine choice. They buy time while you move scans off OLTP primaries or add rollup tables aligned to UI filters.

Observability signals that explain tail latency

Low latency work requires metrics tied to query class, not only CPU graphs:

• p50/p95/p99 per endpoint route, not global cluster average
• Ingest lag from event time to queryable row
• Merge and compaction queue depth on columnar stores
• Pool wait time and active connections on OLTP
• Partition throttle events on DynamoDB
• Cross-AZ bytes and RTT when regions multiply

ClickHouse Prometheus integration and OpenTelemetry integration show how product and infra telemetry can land in the same analytical tier you use for SLO dashboards, so latency investigations do not require three different query languages.

When the bottleneck is the API, not the database

Teams often hit p99 targets in SQL Lab but fail in production because:

• ORMs open one connection per serverless invocation
• Responses include full history instead of paginated windows
• N+1 queries multiply round trips
• JSON serialization runs on unbounded arrays
• Auth middleware calls a remote service synchronously on every request

Sort keys aligned to API filters, PREWHERE, and bounded response payloads protect database tiers from query storms when filters change on every click—the HTTP-side patterns covered in low latency API endpoints.

Shipping low latency analytics rollups with Tinybird

When a low latency database requirement applies to product analytics and monitoring APIs—not cart lookups or counter serving—the gap is usually rollups plus endpoints, not raw query milliseconds on a naked ClickHouse cluster. Tier 3 columnar SQL still needs ingest fabric, auth, and HTTP routes before dashboards hit p99 targets.

Tinybird runs managed ClickHouse with those pieces built in: SQL Pipes as endpoints, branch-based deploys, and per-route observability. Redis, DynamoDB, and ScyllaDB stay on Tier 1 paths; the columnar tier ships without a custom Go or Node service around JDBC or hand-rolled tenant auth.

Rollups aligned to UI grain, not raw event scans

Dashboard latency dies when every tile scans raw events. Define rollup tables at ingest so endpoints read SummingMergeTree or AggregatingMergeTree targets:

CREATE TABLE api_requests (
    ts DateTime64(3),
    route LowCardinality(String),
    status_code UInt16,
    duration_ms Float32,
    tenant_id UUID
)
ENGINE = MergeTree
ORDER BY (tenant_id, route, ts);

CREATE MATERIALIZED VIEW route_latency_1m
ENGINE = AggregatingMergeTree
ORDER BY (tenant_id, route, minute)
AS SELECT
    tenant_id,
    route,
    toStartOfMinute(ts) AS minute,
    quantileState(0.99)(duration_ms) AS p99_state,
    countState() AS requests_state
FROM api_requests
GROUP BY tenant_id, route, minute;

Endpoints query route_latency_1m with quantileMerge(p99_state), not thirty days of raw rows. That pattern mirrors how ClickHouse SaaS analytics models multi-tenant product metrics where each filter maps to a bounded scan.

Published endpoints with bounded parameters

SQL Pipes turn rollups into HTTPS routes with typed template parameters and JWT-scoped fixed params:

NODE route_p99_24h
SQL >
    %
    SELECT
        route,
        quantileMerge(0.99)(p99_state) AS p99_ms,
        countMerge(requests_state) AS requests
    FROM route_latency_1m
    WHERE tenant_id = {{ UUID(tenant_id) }}
      AND minute >= now() - INTERVAL 24 HOUR
    GROUP BY route
    ORDER BY p99_ms DESC
    LIMIT {{ Int32(limit, 50) }}
    %

TYPE endpoint

fixed_params pin tenant_id server-side so clients cannot widen scope. That matters for low latency database designs where security checks must not add another synchronous hop to a separate auth database on every chart interaction.

Compare time to first production endpoint against building the same rollup query behind a custom API. Latency budgets include engineering calendar time, not only query milliseconds.

Ingest without blocking the hot path

Product events should not synchronously double-write to analytics storage on the request critical path. Async ingest paths decouple serving from history:

ClickHouse event tracking covers envelope design so ingest stays fast: narrow columns, LowCardinality enums, timestamps with timezone discipline.

Branches for latency-safe schema changes

Adding a column to a rollup or changing quantile granularity can stall merges or invalidate caches. Branches provide isolated workspaces with production-shaped volume to test new Pipes before prod deploy. Pair with CI dry-run deploys so analytics schema changes get the same review bar as application migrations.

When latency regressions appear after deploy, roll back the Pipe or datasource change without touching operational NoSQL clusters serving counters.

Service Data Sources as latency observability

Service Data Sources expose ingest lag per datasource and p95/p99 per published endpoint inside the platform. When dashboard p99 spikes:

• If ingest lag grows, the problem is upstream (Kafka consumer, Events API rate, bad deploy)
• If ingest is healthy but endpoint p99 grows, rollups or SQL templates need tuning
• If only one endpoint regresses, compare scan bytes and LIMIT clauses before scaling cluster SKUs

That visibility matters because low latency database incidents often get misdiagnosed as "we need more ScyllaDB nodes" when analytics staleness or rollup gaps caused the user-visible slowness.

How the latency stack splits across tiers

Resend measured 62 ms p90 query latency at 100 TB/month without relying on cache, per Tinybird's Resend customer story. Canva reports 54 ms p99 query latency at 3.6 PB processed per month, per Tinybird's managed ClickHouse product page. Those figures cover the full managed stack (ingest, ClickHouse, endpoints), not raw SQL on a single node in one region.

Latency anti-patterns that survive every rearchitecture

Measuring cold cache only. First query after deploy is not your SLO. Warm rollups and realistic concurrency.

One cluster for ETL and homepage. Batch merges and large inserts stall interactive p99. Isolate workloads or use dedicated replicas.

Returning ten thousand rows to paint one chart. Fix API shape and LIMIT clauses, not only SQL hints.

Chasing sub-ms on analytics. Wrong metric drives wrong engine choice and expensive over-provisioning.

Ignoring region. EU users hitting US-East storage add 80–120 ms forever unless you partition data and routes deliberately.

Synchronous analytics writes on checkout. Never block Tier 1 paths on Tier 3 ingest acknowledgment.

Using warehouse latency targets for product. Eight-second Snowflake queries do not become 50 ms because the frontend adds a spinner.

What low latency database means in practice

Pick the engine whose native operation matches the query shape. Keyed ops on NoSQL or Redis. Transactions on OLTP SQL. Aggregates on columnar OLAP with rollups and an API tier that respects payload size and tenant scope.

Low latency is a system property. The database is one line in the budget. The rollup model, ingest decoupling, endpoint design, and observability determine whether that line stays flat as traffic grows.

Frequently Asked Questions (FAQs)

What counts as low latency?

Context-dependent: sub-10 ms for keyed ops, sub-100 ms for analytical endpoints at product concurrency, seconds acceptable for warehouse reports.

Is Redis a low latency database?

For session and cache paths, yes. Not for durable analytics history alone without another tier.

Can Postgres be a low latency database?

For indexed OLTP queries at moderate scale, yes. For billion-row analytics at high concurrency, usually no without a split.

Does ClickHouse replace Redis?

No. ClickHouse serves analytical SQL and rollups. Redis serves hot keyed state with different durability semantics.

How does Tinybird reduce analytics latency?

Rollups at ingest, columnar scans on bounded windows, published HTTP endpoints, managed ops, and per-endpoint latency metrics without custom API glue.

Should I optimize SQL or infrastructure first?

Measure the budget segments. Often rollups, connection pooling, and async ingest beat engine swaps.