A slow query is not always just a “bad query.”
Sometimes the query plan looks reasonable, the index is being used, and the application is only reading a small number of rows. Yet over time, read latency keeps creeping up on one node.
In YugabyteDB, that pattern can happen when a range scan walks through a growing number of obsolete index entries before it finds the live rows it needs.
The immediate symptom may look like a compaction issue.
The root cause may be index design.
The Situation
Imagine a background job that runs every minute and looks for a small batch of rows that are ready to be retried.
The table might look conceptually like this:
CREATE TABLE app_queue (
item_id uuid PRIMARY KEY,
state text NOT NULL,
retry_count int NOT NULL,
retry_after timestamp,
category_code text,
source_type text,
completed_at timestamp,
created_at timestamp NOT NULL DEFAULT now()
);
A multi-column index exists to support retry processing:
CREATE INDEX app_queue_retry_idx
ON app_queue (
state ASC,
retry_count ASC,
retry_after DESC,
category_code ASC
)
INCLUDE (source_type, completed_at);
The job does something like this:
UPDATE app_queue
SET state = $1,
retry_count = retry_count + 1
WHERE item_id IN (
SELECT item_id
FROM app_queue
WHERE state = $2
AND retry_after <= $3
AND retry_after > $4
AND retry_after IS NOT NULL
AND retry_count <= $5
AND category_code <> $6
AND source_type <> $7
AND completed_at > $8
LIMIT $9
)
RETURNING item_id;
The query is using the index. The batch size is small. The job only runs once per minute.
So why would latency keep increasing?
The Hidden Problem: Range Scans Over Obsolete Index Entries
A common issue with indexes that start with a low-cardinality, frequently updated column is that many rows may cluster together in the same part of the index.
For example:
state = 'retryable'
If many rows share the same state, then that portion of the index may live on one tablet, or only a small number of tablets. The read workload for that state can become concentrated on the node that owns the leader for that tablet.
That explains why one node may show increasing read latency while other nodes look fine.
But there is another layer.
If state, retry_count, retry_after, or other indexed values change frequently, YugabyteDB’s DocDB storage layer may accumulate obsolete versions of index entries. These obsolete entries are eventually removed by compaction, but until that happens, range scans may need to step over them.
Point lookups are usually less affected because the latest version of a key can be found efficiently.
Range scans are different.
A range scan over an index may have to walk through many obsolete entries before finding the small number of live rows that match the query.
Key Insight
The index may be used, but that does not automatically mean the index is efficient for the workload. If the scan starts in the wrong place, YugabyteDB may need to walk through many obsolete or filtered entries before it finds the rows the query actually needs.
Why Compaction May Appear to Be the Problem
YugabyteDB performs compactions to clean up obsolete records and improve read efficiency.
Compactions are often triggered by storage-level conditions, such as SSTable file patterns and size relationships. There are also read-triggered compactions when YugabyteDB detects that reads are stepping over many obsolete keys.
However, compaction is not always the first thing to tune.
If the query is repeatedly scanning a hot, low-selectivity range of an index, more aggressive compaction may help temporarily, but the workload may still be inefficient.
A manual compaction may make latency drop immediately:
yb-admin compact_table ysql.<database_name> <index_name> <timeout_seconds>
That can confirm that obsolete entries were contributing to the read latency.
But it does not necessarily mean the best long-term fix is to make compaction more aggressive.
The Better Question: Is the Index Ordered for the Query?
The original index pattern looked conceptually like this:
CREATE INDEX app_queue_retry_idx
ON app_queue (
state ASC,
retry_count ASC,
retry_after DESC,
category_code ASC
);
The query filters by:
state = ?
retry_count <= ?
retry_after <= ?
retry_after > ?
category_code <> ?
source_type <> ?
completed_at > ?
The important design question is:
- Which predicate is most selective after
state?
Is this more selective?
retry_count <= ?
Or is this more selective?
retry_after <= ?
AND retry_after > ?
If the time window is more selective, then placing retry_after earlier in the index may allow the scan to start closer to the rows the query actually needs.
For example:
CREATE INDEX app_queue_retry_idx_v2
ON app_queue (
state ASC,
retry_after DESC,
retry_count ASC,
category_code ASC
);
That small change can make a big difference because the range scan has a better starting point.
Even Better: Use a Targeted Partial Index
If this job only cares about a specific subset of rows, a partial index may be more effective than trying to build one broad index for many access patterns.
Example:
CREATE INDEX app_queue_retry_ready_idx
ON app_queue (
retry_after DESC,
completed_at DESC,
source_type ASC
)
WHERE retry_count <= 20
AND state = 'retryable'
AND category_code <> 'ignored';
Now the index is smaller, more targeted, and aligned with the retry job.
This avoids forcing the query to scan through unrelated states, retry counts, categories, or historical rows.
Tip
Avoid using one “catch-all” index for every query. A dedicated index for a high-frequency background job is often better than a wider index that only partially matches several access patterns.
What to Look For
When troubleshooting this type of issue, start with the SELECT portion of the query.
Run:
EXPLAIN (ANALYZE, DIST, DEBUG)
SELECT item_id
FROM app_queue
WHERE state = 'retryable'
AND retry_after <= now()
AND retry_after > now() - interval '1 day'
AND retry_after IS NOT NULL
AND retry_count <= 20
AND category_code <> 'ignored'
AND source_type <> 'external'
AND completed_at > now() - interval '7 days'
LIMIT 100;
You want to understand:
Useful Metrics and Settings
If reads are stepping over many obsolete keys, metrics such as obsolete key counts can help confirm the issue.
You may also see discussion around these tserver flags:
--auto_compact_percent_obsolete
--auto_compact_min_obsolete_keys_found
Lowering these values can make read-triggered compactions more aggressive.
But be careful.
Important
Compaction tuning can be a useful mitigation, but it should not be the first answer to every growing read-latency problem. If the index scan starts too broadly, the better fix may be a more selective index order or a targeted partial index.
Practical Fixes
Here is the troubleshooting order I would use:
- 1. Isolate the SELECT portion of the query.
- 2. Run
EXPLAIN (ANALYZE, DIST, DEBUG). - 3. Check whether the index scan is walking through many obsolete or filtered entries.
- 4. Identify the most selective predicates.
- 5. Reorder the index so the scan starts closer to the desired rows.
- 6. Consider a partial index for high-frequency background jobs.
- 7. Narrow time windows where possible.
- 8. Only then consider making compaction more aggressive.
Final Takeaway
Indexes in YugabyteDB are not just about whether a query can use an index.
They are about whether the query can start scanning in the right place.
A low-cardinality leading column, frequent updates, broad time windows, and range scans can combine to create steadily increasing read latency, especially when obsolete index entries accumulate faster than compaction removes them.
Manual compaction may temporarily reset the symptom.
Better index design can remove the pressure.
Final Takeaway
When index read latency creeps up over time, do not stop at “maybe compaction is behind.” Ask whether the index order matches the most selective part of the workload. In many cases, the best fix is not more compaction. It is a better starting point for the scan.
Have Fun!
