A while back I wrote about how primary keys and indexes are partitioned in YugabyteDB. I promised a follow-up on what to do when your key is monotonically increasing and every write lands on the same node. This is that follow-up, and it came out of a Slack thread where a colleague asked, reasonably enough, whether the trick was not just what HASH does out of the box.

Glossary

  • Tablet - the unit of sharding in YugabyteDB, a slice of the key space which lives on one node and is replicated to a few others
  • Hash sharding - rows are placed by hashing the hash key columns into a two-byte space running from 0x0000 to 0xFFFF, and each tablet owns a subrange of it
  • Range sharding - rows are placed in the sort order of the key columns, so a tablet owns a contiguous range of real key values rather than of hashes
  • Skip scan - the planner seeking repeatedly into an index to step over a leading column with few distinct values, rather than scanning every entry
  • yb_hash_code(...) - a YSQL function exposing the same hash DocDB uses for sharding, so a row’s bucket can be worked out by hand

Bucketing a hot index

Index a column which only ever increases, such as created_at or a UUID v7, and range sharding puts every new entry at the same end of the key space. One tablet then takes all of your writes whilst the rest of your cluster sits idle. Hash sharding that leading column fixes the distribution but destroys the ordering. A query for the most recent thousand events can no longer walk the index in order, so it has to read the whole thing and sort it instead.

The usual answer is to keep the range ordering and bolt a synthetic leading column on the front to spread the writes out, normally called bucketing.

CREATE INDEX idx_events_recent
    ON events ((yb_hash_code(id) % 27) ASC, created_at DESC);

That gives you 27 leading values instead of one, so 27 places for concurrent inserts to land, and created_at DESC still sorts within each of them. What goes into yb_hash_code(...) literally does not matter as long as it distributes well enough to keep your buckets even, so hashing the primary key is usually the way to go. The modulus itself is worth sizing against your node count rather than picking a round number, for reasons which show up at the end of this post.

Isn’t this what HASH does out of the box?

That is roughly what a colleague asked when the index went up for review. It is a fair question. The bucket column is already a hash, so getting the database to hash it a second time looks like pure ceremony.

CREATE INDEX idx_events_recent
    ON events ((yb_hash_code(id) % 27) HASH, created_at DESC);

Both statements give you 27 distinct leading values, the same rows, the same ordering within each bucket, and near enough the same bytes on disk. The difference is in what the storage layer and the planner are allowed to assume about those bytes, and it turns out to be a pretty expensive difference.

HASH caps the index at 27 tablets

A hash sharded tablet owns a subrange of the two-byte hash space, and when it grows too large the database splits it by picking a new boundary inside that subrange. That works fine when your hash key has millions of distinct values, because the occupied codes are spread thinly across all 65,536 of them and almost any boundary has data on both sides.

A bucket column has 27 distinct values, so it occupies at most 27 points in that space, and every row sharing a bucket hashes to exactly the same code. The mechanism is easier to see in the source than in the docs. When YugabyteDB picks a split key it takes the middle key of the tablet, which it truncates to kUpToHashCode for a hash partitioned tablet, and the result then has to sit strictly above the tablet’s lower bound. Once a tablet holds a single occupied code there is nothing left to satisfy that, so the split is refused with TABLET_SPLIT_KEY_RANGE_TOO_SMALL and the master quietly stops retrying that tablet for a few minutes at a time. The code comment even names the case, which is indexing a large tablet by a low cardinality column.

Range sharding takes the other branch. It truncates to kWholeDocKey, so its split points are real key values. The database can cut at (5, '2026-07-30 11:59') and move half of one bucket onto another node.

HASH bucket
((yb_hash_code(id) % 8) HASH, created_at DESC) · the bar is the 2 byte hash space, 0x0000 to 0xFFFF
ASC bucket
((yb_hash_code(id) % 8) ASC, created_at DESC) · the bar is the key space in sort order, bucket 0 through bucket 7
8 buckets rather than 27 so the bars stay readable, and a 2M row split threshold standing in for the size based one. Hatched tablets hold no rows, and the dashed lines on the lower bar mark the bucket boundaries.

Two details the picture glosses over. Bucket 5 is not tablet 5, because the bucket value gets hashed again on its way into the hash space, so the buckets land in a scattered order and several of them share a tablet until the splits pull them apart. 27 is also a ceiling on tablets holding rows rather than on tablets outright, since a split which isolates a code still mints an empty sibling. The count in your UI can be higher whilst the data still sits in 27 places.

HASH also loses skip scan

The other cost shows up on your reads, because a HASH column in an index is a required key. DocDB can only seek to a hash code it has been handed, so your query has to pin every hash key column with an equality or an IN list, or supply an explicit yb_hash_code() range. Without one of those the conditions on the later index columns are ignored and the whole index gets scanned, which is the equality half of the old ESR rule and the reason hash columns belong at the front of an index and in front of predicates which are equalities.

That is fine when the lookup names a bucket, but a query for the most recent thousand events has no idea which bucket they live in, so a hash leading column leaves it reading everything. A range leading column lets the planner seek into bucket 0, take what it needs, seek into bucket 1 and carry on, which is the skip scan behaviour YugabyteDB implements for range indexes and, as of August 2026, still does not implement for hash sharded ones.

What you pay either way

Bucketing is not free whichever variant you pick, because the bucket is derived from data your query does not filter on, so every read has to visit all 27 of them. The way to keep that cheap is to ask each bucket for its own top N and merge the small results, rather than sorting the full range.

WITH buckets AS (SELECT generate_series(0, 26) AS b)
SELECT e.*
FROM buckets
CROSS JOIN LATERAL (
    SELECT * FROM events
    WHERE yb_hash_code(id) % 27 = buckets.b
    ORDER BY created_at DESC
    LIMIT 100
) e
ORDER BY e.created_at DESC
LIMIT 100;

Recent versions can reach the same plan from a plain IN list with yb_enable_derived_saops, merging the sorted streams and stopping at the limit without a sort node at all. Either way your fan-out costs 27 seeks rather than one. It grows with the modulus whilst your write distribution grows with it too, so that number is a real trade rather than a free dial.

Takeaway

  • Bucket a monotonically increasing index key when the writes are hot, and size the modulus against your node count, since every read pays for every bucket
  • Declare the bucket ASC, never HASH, so the database can carry on splitting inside a bucket as the data grows
  • Remember that a HASH key needs an equality or an IN list, so putting one in front of a range scan removes the planner’s ability to skip
  • Reach for a LATERAL join per bucket, or yb_enable_derived_saops, rather than a sort over the whole range when you only want the top N

So no, it is not quite what HASH does out of the box, even though the bytes on disk say otherwise.