CockroachDB query work and hot ranges

SQL work and KV work

The history query returns a customer’s ten newest orders since a lower time bound. It needs the status as well as the ordering columns:

SELECT order_id, placed_at, status
FROM orders
WHERE customer_id = 1001
  AND placed_at >= timestamptz '2026-10-01 00:00+00'
ORDER BY placed_at DESC, order_id DESC
LIMIT 10;

Customer 1001 belongs to the generated history used in Query execution and performance. It owns 70% of the 120,000 generated orders. Mina, customer 1, supplies a smaller example of the same lookup mechanism with the shop’s three orders.

A secondary index on (customer_id, placed_at DESC, order_id DESC) puts one customer’s history together, newest first. Equality fixes the customer prefix; the date bounds the interval inside it. The order ID breaks ties in time. Keep both columns in ORDER BY: an access path makes the requested order cheaper but does not replace the SQL contract.

The secondary entry contains an order’s primary key. If it lacks status, the engine uses that key to fetch the primary-index row. Carrying status in the secondary entry can remove that extra fetch without changing the requested answer.

The scan and primary fetch are two kinds of KV work: requests to the key-value layer below SQL. SQL work includes decoding and processing the supplied rows, applying expressions and producing the result. Both consume resources, and a small SQL result does not bound either kind of work.

Nor does it determine a request count. One key interval can cross several ranges. Several point fetches can be batched, and a request can need more than one lower-level operation. Distinguish result rows, decoded rows, key-value pairs, key spans, range owners and RPCs—a remote procedure call between components. Measure the quantities the native plan actually reports.

Covering indexes and index joins

The customer-history key already supplies the order ID and placement time. Status is the only missing value. A covering index stores everything this query needs:

CREATE INDEX orders_history
ON orders (customer_id, placed_at DESC, order_id DESC);

CREATE INDEX orders_history_cover
ON orders (customer_id, placed_at DESC, order_id DESC)
STORING (status);

These are alternatives for a comparison, not a recommendation to keep both. STORING adds a value without making it part of the search key or its ordering. It can remove the primary fetch while leaving the searched interval unchanged. It also adds another maintained copy of status: inserts and relevant updates must write that index data.

CockroachDB calls the secondary-to-primary operation an index join. Both sides belong to the same table. A lookup join uses values from one input to search an index for matching rows, commonly in another table—for example, finding an order’s lines by order ID. Both can generate KV work, but covering this history query removes the former. It does not remove all joins elsewhere in the application.

A paid-only history query adds AND status = 'paid'. Storing status lets the scan test it without fetching primary rows; it does not put all paid entries together. The scan may pass pending entries before it finds ten paid orders. A key beginning (customer_id, status, placed_at DESC, order_id DESC) separates statuses, but an unqualified history now needs histories from several status groups. A partial index with WHERE status = 'paid' serves only queries whose conditions establish that restriction. Choose for the actual mix, including status transitions.

Reading EXPLAIN ANALYZE

EXPLAIN shows a proposed route without running the statement. EXPLAIN ANALYZE executes it and records what happened, including a write’s effects. For the read above:

EXPLAIN ANALYZE (VERBOSE)
SELECT order_id, placed_at, status
FROM orders
WHERE customer_id = 1001
  AND placed_at >= timestamptz '2026-10-01 00:00+00'
ORDER BY placed_at DESC, order_id DESC
LIMIT 10;

Start at the scan. Check the named index and spans: did the customer and time condition bound the search, or does it say FULL SCAN? Follow its rows upwards. An index join indicates primary data was still needed; a sort indicates the chosen route did not already supply the required order.

The disposable v26.2.8 run used a projected orders table with the three core orders and the exact 120,000 generated order IDs, customers, times and statuses. All four comparison indexes were defined before loading rows. For a controlled access-path comparison, the two executions named orders@orders_history and orders@orders_history_cover explicitly.

These excerpts keep the operator tree and selected fields. Statistics-age details are left out; other omitted lines are marked …. Read from the most indented scan upwards.

• project
│ …
└── • index join (streamer)
    │ …
    │ KV rows decoded: 10
    │ actual row count: 10
    │ estimated row count: 10
    │ table: orders@orders_pkey
    │ …
    └── • scan
          …
          KV rows decoded: 10
          actual row count: 10
          estimated row count: 10
          table: orders@orders_history
          spans: /1001-/1001/2026-09-30T23:59:59.999999999Z
          limit: 10

The lower scan reads ten history entries in the customer/date span. Its limit: 10 stops further consumption. The index join above fetches ten primary rows to obtain status; its table field names the primary index. With stored status, that extra operator disappears:

• project
│ …
└── • scan
      …
      KV rows decoded: 10
      actual row count: 10
      estimated row count: 10
      table: orders@orders_history_cover
      spans: /1001-/1001/2026-09-30T23:59:59.999999999Z
      limit: 10

The span’s final timestamp is just before 1 October because this index stores time in descending order. The two scans bound the same interval; covering changes where status comes from.

Both scans reported the same customer/date span and a scan limit of ten. Neither needed a sort. The uncovered route read 450 B from the secondary index and 307 B from primary rows. Covering widened the secondary read to 537 B but removed the primary read altogether. Ten primary fetches were served by one reported KV call in this run, not ten calls.

The estimates at the consumed scans and index join were ten, matching their actual output. That does not estimate the customer’s whole history: the limit deliberately stopped consumption. The unhinted optimiser plan also selected the covering index. The eight combinations of common/rare customer, January/October lower bound and paid/all statuses returned equal ordered results through all four indexes. These checks establish the mechanism and result equivalence; they are not a latency or concurrent-write benchmark. The complete native observations retain the plans, schema and checked results.

CPU time is time spent doing processor work. KV elapsed time can include waiting. Cumulative work across concurrent components need not fit inside the statement’s wall-clock duration, and parent/child measurements can overlap. Do not add all the displayed times to reconstruct request latency. The native field reference defines each basis.

A large estimate error matters when it changes the chosen work. For example, expecting a few eligible orders can make repeated line lookups look cheaper than a broader scan and join; thousands of actual orders change that balance. Check statistics after a large data change, then compare the resulting route and work. A refreshed estimate is useful only if it describes the relevant distribution well enough. Accurate estimates can still describe an expensive request.

Where the delay occurs

Read the wait subtype before naming a blocker. The native output distinguishes lock-table waiting from waiting to acquire latches, which coordinate individual low-level requests. The hashed-index profiles in this run reported latch waits; the P7 experiment below reported a transaction lock wait. A contention summary alone does not identify a blocking transaction. Admission wait instead points to work queued for resources. Find the evidence that explains the delay before choosing another index.

The application also waits before and after server execution: borrowing a connection, sending the statement, receiving rows and processing them. Compare the same request boundary before and after a change. Diagnosing a database incident separates those delays; Connections, overload and capacity develops the shared resource budget.

Distributed execution and fan-out

A plan marked distribution: local performs SQL processing at the gateway. Its KV requests can still reach remote nodes. A distributed SQL plan instead places processors on several nodes and connects them with streams. Those are separate boundaries. Read DistSQL network usage alongside kv nodes and KV gRPC calls; a local SQL plan does not establish that all its data was local.

Fan-out means one operation reaches several participants. A scan whose spans cross many owners can fan out even before a SQL exchange appears. More participants can supply data concurrently; they also add communication and required responses. Count where requests go and where rows or bytes cross, rather than inferring network cost from the word “distributed”.

The paid-order report from Query execution and performance gives a larger example. It filters October’s paid orders, joins their lines and sums agreed line values by customer. On the three shop orders it returns Tariq 40.00 and Mina 39.50; pending order 103 contributes nothing. Adding nodes does not change that calculation.

Consider one possible distribution of its work. Processor A receives a 4.50 line for Mina and a 40.00 line for Tariq. Processor B receives another 35.00 line for Mina. Each can total its own lines by customer, but neither has Mina’s whole answer. A final grouping must combine her partial totals: 4.50 + 35.00 = 39.50. Tariq’s single partial remains 40.00.

This tiny distribution sends three partial totals for three contributing lines, so local aggregation saves no rows there. With many lines for each customer on a processor, one partial can replace many line rows before they move. The final grouping still needs every contributing partial. Wide values and many distinct customers can reduce that saving.

Use EXPLAIN ANALYZE (DISTSQL) to obtain the native physical plan and its measurements. Follow scans, lookup processors and stream endpoints. Check where the date/status filter reduces orders, whether line lookups repeat, and whether local aggregation reduces rows before an exchange. This establishes which work the actual plan performs, rather than assuming the illustrative distribution above.

Then inspect imbalance. A hash exchange by customer sends equal customer keys together. In the generated model, customer 1001 owns most orders; a processor responsible for that key may have much more work. Local partial aggregation can reduce what reaches it, but the final total still requires all contributing parts. A busy required participant can hold up the answer while other nodes are quiet.

The single-node observations in this article cannot demonstrate these placements or regional latency. They establish native access paths and counters. CockroachDB table locality and region survival develops policies for placing data and leases; changing the key or query is a different choice.

Finding the hot range

A busy node identifies a place to investigate, not the key that caused it. Match the affected request interval to node CPU and storage demand, statement activity and the DB Console’s Hot Ranges view. Record the view’s sampling or averaging window. A short statement sample and a long range average need not describe the same burst.

For a hot range, follow its range ID to its key bounds, database, table and index. Then connect the bounds to the schema and the statements reading or changing those keys. A high read rate, a high write rate and time spent waiting on a lock suggest different causes. High activity alone does not establish a harmful bottleneck.

SELECT range_id, start_key, end_key,
       lease_holder, replicas
FROM [SHOW RANGES FROM INDEX orders@orders_history_cover
      WITH DETAILS]
ORDER BY start_key;

SHOW RANGES supplies the ownership snapshot. It does not measure range demand or prove lock contention. An index can share a covering range with other key intervals, and ownership changes over time. Combine the snapshot with demand evidence and resource pressure from the same period; retain the range bounds as well as its ID.

In this run the covering index’s listing returned range 83, with node 1 as leaseholder and its only replica. Its bounds covered a much larger interval than this one index. That is useful ownership evidence, but neither a per-index load measurement nor evidence of a hotspot. The optional crdb_internal.hot_ranges probe was unavailable in v26.2.8; no native range-demand rate is claimed here.

For range activity without a clear statement cause, return to Operating and securing a CockroachDB cluster. Background jobs and wider cluster health can consume the same resources. Do not turn an unexplained node graph into a primary-key migration.

Sequential keys and hash-sharded indexes

Increasing IDs or timestamps put successive inserts near one index edge. After a split, later keys continue into the newest interval. Moving older ranges spreads stored data while the new demand follows the edge.

A random primary key can disperse primary writes, but an added (placed_at) secondary index still receives sequential time keys. Inspect the whole index set. The customer-leading history key creates separate customer prefixes; skew can keep one of them dominant.

Consider three new orders for the same customer, each with a different time and order ID. A customer-leading index keeps them in one ordered interval. Hashing their complete keys can put them into different bucket prefixes. Hashing only the customer cannot: all three have the same customer input.

A hash-sharded index prefixes its ordered key with a computed bucket. CockroachDB v26.2 can hash every key column or a specified leading prefix. These two declarations make that choice explicit:

CREATE INDEX orders_history_hash
ON orders (customer_id, placed_at DESC, order_id DESC)
USING HASH STORING (status)
WITH (bucket_count = 4);

CREATE INDEX orders_history_prefix
ON orders (customer_id, placed_at DESC, order_id DESC)
USING HASH STORING (status)
WITH (bucket_count = 4,
      shard_columns = (customer_id));

With full-key hashing, a customer/time interval can occupy every bucket. The ordered answer needs bucket searches and combination. Prefix hashing makes the customer determine a bucket, preserving that customer’s ordered interval there. It also preserves the customer’s concentrated writes. The hash-sharding reference describes that trade and the bucket setting.

The native full-key plan scanned each of four bucket/customer intervals with a local limit of ten, decoded 40 rows and combined the ordered streams before the final limit returned ten. It reported four KV calls and about 2.1 KiB. Prefix hashing selected bucket 3 for customer 1001: one bounded scan decoded ten rows, reported one call and read 547 B. Both returned the same ten orders. No extra sort appeared in either plan.

Those counts show the additional searches; they do not isolate their latency cost. The hashed profiles also reported many more MVCC iterator steps beneath the decoded rows and nonzero latch waits. Their single profiled durations cannot establish a production speed comparison. One node supplied every bucket, so this run demonstrates neither write dispersion across nodes nor increased throughput.

The full-key design can spread the three orders’ writes while requiring their history to be recovered across buckets. Customer-only hashing keeps that history adjacent, but keeps the same customer’s concentrated writes too. A bucket is a key prefix, not a promise of a dedicated node. Useful boundaries, placement and available capacity still determine where the work runs.

More buckets add opportunities to separate keys and can add searches to reads. They cannot make one stock decision independent of itself. Accept the design only when the write benefit survives the history queries and the other indexes’ costs.

Contention on one row

Return independently to P7 with stock 1, before the purchase replay. Two transactions want the same stock authority. This problem remains even if every order ID is dispersed across the cluster.

In the controlled native experiment, transaction A locks P7 without changing stock and stays open. Transaction B attempts a guarded decrement from 1 to 0. It must wait for A to release the lock before its update can complete. This isolates one holder and one updater from the purchase’s multi-row work.

The native snapshot found two transaction IDs on the same /Table/108/1/"P7"/0 key. A’s exclusive lock had granted = true; B’s waiting entry had granted = false. Both were marked contended. After A committed its lock-only transaction, B’s update completed and P7 had stock 0. P3 and P9 remained at 12 and 4.

B’s profile reported 317 ms of execution time. Of that, 313 ms was in KV, 313 ms was due to contention and 313 ms was in the lock table. KV CPU was 522 µs, and the profile reported zero admission wait. The deliberate hold caused this wait; these are not normal shop latencies. The three 313 ms fields describe overlapping aspects of the same delay, not three waits to add together.

Connect the waiter to the blocker and the contended key. CockroachDB’s lock views and contention events can identify transactions, statements and key intervals; inspect them while waiting occurs or retain the corresponding event. A statement’s own wait tells only part of the story: a blocker may be idle inside an open transaction while the waiter consumes the request budget.

Keep validation and remote calls that do not require the protected value outside the locked interval. Use a direct guarded update where it expresses the decision, and keep all changes that must commit together inside the transaction. Moving a stock decrement out of an atomic purchase would change correctness to obtain a shorter measurement.

Hashing the product ID sends both P7 attempts to the same bucket. Adding nodes or splitting between neighbouring products can help independent product work, but it cannot create two independent authorities for “one mug left”. Dividing inventory into separately allocated stock pools is a different business protocol with its own capacity and reconciliation rules, not an index trick.

Waiting and Serializable retries are related operational signals, but a wait count is not a retry count. The contention guide distinguishes lock-wait duration from Serializable conflicts. CockroachDB transactions and retries follows the native retry protocol; CockroachDB isolation and follower reads develops historical-read choices. An old stock value is not authority for a new purchase.

Testing the repair against the workload

Write the prediction before changing the schema. Covering should remove the history query’s primary fetch. Full-key sharding should make independent writes separable and make customer-history reads search across buckets. A shorter P7 transaction should reduce how long another attempt waits. These are different predictions and need different evidence.

First compare results: IDs, statuses, multiplicity and the complete requested ordering. Test common customer 1001 and rare customer 1002, a broad history and an October bound, and paid-only as well as all statuses. An index hint can isolate access-path mechanics; remove it when testing the optimiser’s normal choice.

Then collect plans and ordinary request measurements. Compare latency distributions, throughput, SQL/KV CPU, bytes, requests and waiting under the representative request mix and competing writes. Alternate variants, allow for cache state and repeat enough to see variation. A faster profiled execution does not establish a production latency improvement.

Record the engine version, topology, data distribution, statistics, parameters, settings and concurrency. An idle single-node experiment can show that primary fetches disappeared. It cannot establish that the new index distributes load across a cluster or meets a regional latency target. A production acceptance test must exercise the part of the prediction that needs those conditions.

Creating or replacing an index also needs a rollout that preserves application compatibility and budgets for backfill work. CockroachDB schema jobs under load follows that procedure. The index earns its place when it removes demonstrated work without moving an unacceptable cost into the rest of the workload.