How we cut p99 from 11 ms to 4.2 ms

In March our public p99 for point and range reads sat at 11 ms. It was fine for dashboards and too slow for the use cases customers kept bringing us: fraud checks inside a payment request, feature lookups in a model call, stock levels on a checkout page. Those calls have a budget of about 20 ms end to end, and we were spending half of it.

v4.0 shipped in September with a p99 of 4.2 ms across all 38 public regions. Nothing about the storage format or the network changed. We changed how the read path finds rows, how it allocates memory, and when the planner decides to sort.

Where the time went

We started by tracing 2 million production reads from Scale workspaces in Reykjavík, Frankfurt and Virginia. The median read was already fast at 1.8 ms. The tail was the problem, and it had three causes that together explained 91% of reads slower than 8 ms.

• Range reads on views without a declared order merged every partition and sorted the result in memory.

• Allocation spikes during large merges pushed the shared allocator into lock contention.

• The planner re-sorted partitions that were already sorted, because it could not prove they were.

Tracing at the tail

Sampling at 1% hides tails. We switched to tail-based sampling: every read keeps its spans in a ring buffer, and only reads above the p95 for their view are written out. That gave us full traces for the slow reads at a cost of roughly 0.3% CPU.

Declared sort keys

A materialized view in Basalt is a set of partitions, each sorted by its write order. If you query by customer, every partition has to be scanned and merged. Declaring a sort key lets compaction keep partitions in that order, so a range read becomes a seek into each partition followed by a streaming merge.

-- v3.9: rows land in write order, reads merge and sort
CREATE MATERIALIZED VIEW orders_by_customer AS
SELECT customer_id, count(*) AS orders, sum(total) AS revenue
FROM orders
GROUP BY customer_id;
-- v4.0: declare the order once, reads become a seek
CREATE MATERIALIZED VIEW orders_by_customer
WITH (sort_key = 'customer_id', granule = '8 KiB') AS
SELECT customer_id, count(*) AS orders, sum(total) AS revenue
FROM orders
GROUP BY customer_id;

-- v3.9: rows land in write order, reads merge and sort
CREATE MATERIALIZED VIEW orders_by_customer AS
SELECT customer_id, count(*) AS orders, sum(total) AS revenue
FROM orders
GROUP BY customer_id;
-- v4.0: declare the order once, reads become a seek
CREATE MATERIALIZED VIEW orders_by_customer
WITH (sort_key = 'customer_id', granule = '8 KiB') AS
SELECT customer_id, count(*) AS orders, sum(total) AS revenue
FROM orders
GROUP BY customer_id;

-- v3.9: rows land in write order, reads merge and sort
CREATE MATERIALIZED VIEW orders_by_customer AS
SELECT customer_id, count(*) AS orders, sum(total) AS revenue
FROM orders
GROUP BY customer_id;
-- v4.0: declare the order once, reads become a seek
CREATE MATERIALIZED VIEW orders_by_customer
WITH (sort_key = 'customer_id', granule = '8 KiB') AS
SELECT customer_id, count(*) AS orders, sum(total) AS revenue
FROM orders
GROUP BY customer_id;

Compaction pays for this with about 6% more write amplification. On every workload we measured, the trade was worth it. Views created in the console now suggest a sort key from the first hundred queries they serve.

Sparse index granules

Each partition keeps one index entry per 8 KiB granule. The full index for a 40 GB view fits in 9 MB, stays resident, and turns most seeks into a single read from local NVMe.

A per-core allocator

Our merge operators allocated small buffers on every batch. Under load, threads queued on the global allocator lock. We moved to per-core arenas that are reset at the end of each query instead of freeing objects one by one.

The fastest allocation is the one you never free. Resetting an arena costs the same for ten objects or ten million.

This change alone took 2.1 ms off p99 and cut peak memory during large merges by 18%, because arenas no longer fragment.

Results

We rolled v4.0 out region by region over three weeks, starting with Reykjavík. The chart below shows read latency by percentile across all public regions for the 30 days before and after.

p50 moved from 1.8 ms to 1.2 ms. p99 moved from 11.0 ms to 4.2 ms. The gap between them shrank from 6x to 3.5x, which matters more to customers than either number: a predictable tail is something you can put inside a request budget.

What is next

The remaining tail is mostly cross-region reads during failover. We are testing follower reads with bounded staleness, so a query can choose to read from the nearest replica if it is no more than 50 ms behind. Expect a post on that, and on the clock work that makes it safe, before the end of the year.

If you run Basalt on Scale or Enterprise, v4.0 is already live in your workspace. Declared sort keys are opt-in per view and can be added without downtime.