> ## Documentation Index > Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Index Sequential Keys with Hash-sharded Indexes export const InternalLink = ({version, path = "", children, ...props}) => { let detectedVersion = version || "stable"; if (typeof window !== 'undefined' && !version) { const match = window.location.pathname.match(/\/docs\/([^/]+)/); if (match) { detectedVersion = match[1]; } } const normalizedPath = path.startsWith("/") ? path.slice(1) : path; return {children} ; }; If you are working with a table that must be indexed on sequential keys, you should use **hash-sharded indexes**. Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range hotspots and improving write performance on sequentially-keyed indexes at a small cost to read performance. Hash-sharded indexes are an implementation of hash partitioning, not hash indexing. ## How hash-sharded indexes work ### Overview CockroachDB automatically splits ranges of data in the key-value store based on the size of the range and on the load streaming to the range. To split a range based on load, the system looks for a point in the range that evenly divides incoming traffic. If the range is indexed on a column of data that is sequential in nature (e.g., an ordered sequence or a series of increasing, non-repeating `TIMESTAMP`s), then all incoming writes to the range will be the last (or first) item in the index and appended to the end of the range. As a result, the system cannot find a point in the range that evenly divides the traffic, and the range cannot benefit from load-based splitting, creating a hotspot on the single range. Hash-sharded indexes solve this problem by distributing sequential data across multiple nodes within your cluster, eliminating hotspots. The trade-off to this, however, is a small performance impact on reading sequential data or ranges of data, as it's not guaranteed that sequentially close values will be on the same node. Hash-sharded indexes contain a virtual computed column, known as a shard column. CockroachDB uses this shard column, as opposed to the sequential column in the index, to control the distribution of values across the index. The shard column is hidden by default but can be seen with `SHOW COLUMNS`. In v21.2 and earlier, hash-sharded indexes create a physical `STORED` computed column instead of a virtual computed column. If you are using a hash-sharded index that was created in v21.2 or earlier, the `STORED` column still exists in your database. When dropping a hash-sharded index that has created a physical shard column, you must include the `CASCADE` clause to drop the shard column. Doing so will require a rewrite of the table. For details about the mechanics and performance improvements of hash-sharded indexes in CockroachDB, see our [Hash Sharded Indexes Unlock Linear Scaling for Sequential Workloads](https://www.cockroachlabs.com/blog/hash-sharded-indexes-unlock-linear-scaling-for-sequential-workloads) blog post. Hash-sharded indexes created in v22.1 and later will not backfill, as the shard column isn't stored. Hash-sharded indexes created prior to v22.1 will backfill if `schema_change_policy` is set to `backfill`, as they use a stored column. If you don't want CockroachDB to backfill hash-sharded indexes you created prior to v22.1, drop them and recreate them. ### Shard count When creating a hash-sharded index, CockroachDB creates a specified number of shards (buckets) within the cluster based on the value of the `sql.defaults.default_hash_sharded_index_bucket_count` cluster setting. You can also specify a different `bucket_count` by passing in an optional storage parameter. See the example below. For most use cases, no changes to the cluster setting are needed, and hash-sharded indexes can be created with `USING HASH` instead of `USING HASH WITH (bucket_count = n)`. Changing the cluster setting or storage parameter to a number greater than the number of nodes within that cluster will produce diminishing returns and is not recommended. A larger number of buckets allows for greater load-balancing and thus greater write throughput. More buckets disadvantages operations that need to scan over the data to fulfill their query; such queries will now need to scan over each bucket and combine the results. We recommend doing thorough performance testing of your workload with different `bucket_count`s if the default `bucket_count` does not satisfy your use case. Use `ALTER ROLE ALL SET {sessionvar} = {val} ` instead of the `sql.defaults.*` cluster settings. This allows you to set a default value for all users for any session variable that applies during login, making the `sql.defaults.*` cluster settings redundant. ### Hash-sharded indexes on partitioned tables You can create hash-sharded indexes with implicit partitioning under the following scenarios: * The table is partitioned implicitly with `REGIONAL BY ROW`, and the `crdb_region` column is not part of the columns in the hash-sharded index. * The table is partitioned implicitly with `PARTITION ALL BY`, and the partition columns are not part of the columns in the hash-sharded index. Note that `PARTITION ALL BY` is in preview. However, if an index of a table, whether it be a primary key or secondary index, is explicitly partitioned with `PARTITION BY`, then that index cannot be hash-sharded. Partitioning columns cannot be placed explicitly as key columns of a hash-sharded index as well, including `REGIONAL BY ROW` table's `crdb_region` column. ## Create a hash-sharded index The general process of creating a hash-sharded index is to add the `USING HASH` clause to one of the following statements: * `CREATE INDEX` * `CREATE TABLE` * `ALTER PRIMARY KEY` When this clause is used, CockroachDB creates a computed shard column and then stores each index shard in the underlying key-value store with one of the computed column's hash as its prefix. ## Examples ### Create a table with a hash-sharded primary key Let's create the `products` table and add a hash-sharded primary key on the `ts` column: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE products ( ts DECIMAL PRIMARY KEY USING HASH, product_id INT8 ); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW INDEX FROM products; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit -------------+---------------+------------+--------------+---------------------------+-----------+---------+----------- products | products_pkey | false | 1 | crdb_internal_ts_shard_16 | ASC | false | true products | products_pkey | false | 2 | ts | ASC | false | false products | products_pkey | false | 3 | product_id | N/A | true | false (3 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM products; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden ----------------------------+-----------+-------------+----------------+---------------------------------------------------+-----------------+------------ crdb_internal_ts_shard_16 | INT8 | false | NULL | mod(fnv32(crdb_internal.datums_to_bytes(ts)), 16) | {products_pkey} | true ts | DECIMAL | false | NULL | | {products_pkey} | false product_id | INT8 | true | NULL | | {products_pkey} | false (3 rows) ``` ### Create a table with a hash-sharded secondary index Let's now create the `events` table and add a secondary index on the `ts` column in a single statement: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE events ( product_id INT8, owner UUID, serial_number VARCHAR, event_id UUID, ts TIMESTAMP, data JSONB, PRIMARY KEY (product_id, owner, serial_number, ts, event_id), INDEX (ts) USING HASH ); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW INDEX FROM events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit -------------+---------------+------------+--------------+---------------------------+-----------+---------+----------- events | events_pkey | false | 1 | product_id | ASC | false | false events | events_pkey | false | 2 | owner | ASC | false | false events | events_pkey | false | 3 | serial_number | ASC | false | false events | events_pkey | false | 4 | ts | ASC | false | false events | events_pkey | false | 5 | event_id | ASC | false | false events | events_pkey | false | 6 | data | N/A | true | false events | events_ts_idx | true | 1 | crdb_internal_ts_shard_16 | ASC | false | true events | events_ts_idx | true | 2 | ts | ASC | false | false events | events_ts_idx | true | 3 | product_id | ASC | false | true events | events_ts_idx | true | 4 | owner | ASC | false | true events | events_ts_idx | true | 5 | serial_number | ASC | false | true events | events_ts_idx | true | 6 | event_id | ASC | false | true (12 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden ----------------------------+-----------+-------------+----------------+---------------------------------------------------+-----------------------------+------------ product_id | INT8 | false | NULL | | {events_pkey,events_ts_idx} | false owner | UUID | false | NULL | | {events_pkey,events_ts_idx} | false serial_number | VARCHAR | false | NULL | | {events_pkey,events_ts_idx} | false event_id | UUID | false | NULL | | {events_pkey,events_ts_idx} | false ts | TIMESTAMP | false | NULL | | {events_pkey,events_ts_idx} | false data | JSONB | true | NULL | | {events_pkey} | false crdb_internal_ts_shard_16 | INT8 | false | NULL | mod(fnv32(crdb_internal.datums_to_bytes(ts)), 16) | {events_ts_idx} | true (7 rows) ``` ### Create a hash-sharded secondary index on an existing table Let's assume the `events` table already exists: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE events ( product_id INT8, owner UUID, serial_number VARCHAR, event_id UUID, ts TIMESTAMP, data JSONB, PRIMARY KEY (product_id, owner, serial_number, ts, event_id) ); ``` You can create a hash-sharded index on an existing table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE INDEX ON events(ts) USING HASH; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW INDEX FROM events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit -------------+---------------+------------+--------------+---------------------------+-----------+---------+----------- events | events_pkey | false | 1 | product_id | ASC | false | false events | events_pkey | false | 2 | owner | ASC | false | false events | events_pkey | false | 3 | serial_number | ASC | false | false events | events_pkey | false | 4 | ts | ASC | false | false events | events_pkey | false | 5 | event_id | ASC | false | false events | events_pkey | false | 6 | data | N/A | true | false events | events_ts_idx | true | 1 | crdb_internal_ts_shard_16 | ASC | false | true events | events_ts_idx | true | 2 | ts | ASC | false | false events | events_ts_idx | true | 3 | product_id | ASC | false | true events | events_ts_idx | true | 4 | owner | ASC | false | true events | events_ts_idx | true | 5 | serial_number | ASC | false | true events | events_ts_idx | true | 6 | event_id | ASC | false | true (12 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden ----------------------------+-----------+-------------+----------------+---------------------------------------------------+-----------------------------+------------ product_id | INT8 | false | NULL | | {events_pkey,events_ts_idx} | false owner | UUID | false | NULL | | {events_pkey,events_ts_idx} | false serial_number | VARCHAR | false | NULL | | {events_pkey,events_ts_idx} | false event_id | UUID | false | NULL | | {events_pkey,events_ts_idx} | false ts | TIMESTAMP | false | NULL | | {events_pkey,events_ts_idx} | false data | JSONB | true | NULL | | {events_pkey} | false crdb_internal_ts_shard_16 | INT8 | false | NULL | mod(fnv32(crdb_internal.datums_to_bytes(ts)), 16) | {events_ts_idx} | true (7 rows) ``` ### Alter an existing primary key to use hash sharding Let's assume the `events` table already exists: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE events ( product_id INT8, owner UUID, serial_number VARCHAR, event_id UUID, ts TIMESTAMP, data JSONB, PRIMARY KEY (product_id, owner, serial_number, ts, event_id), INDEX (ts) USING HASH ); ``` You can change an existing primary key to use hash sharding by adding the `USING HASH` clause at the end of the key definition: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE events ALTER PRIMARY KEY USING COLUMNS (product_id, owner, serial_number, ts, event_id) USING HASH; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW INDEX FROM events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit -------------+---------------+------------+--------------+-------------------------------------------------------------------+-----------+---------+----------- events | events_pkey | false | 1 | crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | ASC | false | true events | events_pkey | false | 2 | product_id | ASC | false | false events | events_pkey | false | 3 | owner | ASC | false | false events | events_pkey | false | 4 | serial_number | ASC | false | false events | events_pkey | false | 5 | ts | ASC | false | false events | events_pkey | false | 6 | event_id | ASC | false | false events | events_pkey | false | 7 | data | N/A | true | false events | events_ts_idx | true | 1 | crdb_internal_ts_shard_16 | ASC | false | true events | events_ts_idx | true | 2 | ts | ASC | false | false events | events_ts_idx | true | 3 | crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | ASC | false | true events | events_ts_idx | true | 4 | product_id | ASC | false | true events | events_ts_idx | true | 5 | owner | ASC | false | true events | events_ts_idx | true | 6 | serial_number | ASC | false | true events | events_ts_idx | true | 7 | event_id | ASC | false | true (14 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------------------------------------------------------------+-----------+-------------+----------------+-----------------------------------------------------------------------------------------------+-----------------------------+------------ product_id | INT8 | false | NULL | | {events_pkey,events_ts_idx} | false owner | UUID | false | NULL | | {events_pkey,events_ts_idx} | false serial_number | VARCHAR | false | NULL | | {events_pkey,events_ts_idx} | false event_id | UUID | false | NULL | | {events_pkey,events_ts_idx} | false ts | TIMESTAMP | false | NULL | | {events_pkey,events_ts_idx} | false data | JSONB | true | NULL | | {events_pkey} | false crdb_internal_ts_shard_16 | INT8 | false | NULL | mod(fnv32(crdb_internal.datums_to_bytes(ts)), 16) | {events_ts_idx} | true crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | INT8 | false | NULL | mod(fnv32(crdb_internal.datums_to_bytes(event_id, owner, product_id, serial_number, ts)), 16) | {events_pkey,events_ts_idx} | true (8 rows) ``` ### Show hash-sharded index in `SHOW CREATE TABLE` Following the above [example](#create-a-hash-sharded-secondary-index-on-an-existing-table), you can show the hash-sharded index definition along with the table creation statement using `SHOW CREATE TABLE`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE TABLE events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | create_statement -------------+--------------------------------------------------------------------------------------------------------------------------------- events | CREATE TABLE public.events ( | product_id INT8 NOT NULL, | owner UUID NOT NULL, | serial_number VARCHAR NOT NULL, | event_id UUID NOT NULL, | ts TIMESTAMP NOT NULL, | data JSONB NULL, | crdb_internal_ts_shard_16 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(ts)), 16:::INT8)) VIRTUAL, | CONSTRAINT events_pkey PRIMARY KEY (product_id ASC, owner ASC, serial_number ASC, ts ASC, event_id ASC), | INDEX events_ts_idx (ts ASC) USING HASH WITH (bucket_count=16) | ) (1 row) ``` ### Create a hash-sharded secondary index with a different `bucket_count` You can specify a different `bucket_count` via a storage parameter on a hash-sharded index to optimize either write performance or sequential read performance on a table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE events ( product_id INT8, owner UUID, serial_number VARCHAR, event_id UUID, ts TIMESTAMP, data JSONB, PRIMARY KEY (product_id, owner, serial_number, ts, event_id), INDEX (ts) USING HASH WITH (bucket_count = 20) ); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW INDEX FROM events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit -------------+---------------+------------+--------------+---------------------------+-----------+---------+----------- events | events_pkey | false | 1 | product_id | ASC | false | false events | events_pkey | false | 2 | owner | ASC | false | false events | events_pkey | false | 3 | serial_number | ASC | false | false events | events_pkey | false | 4 | ts | ASC | false | false events | events_pkey | false | 5 | event_id | ASC | false | false events | events_pkey | false | 6 | data | N/A | true | false events | events_ts_idx | true | 1 | crdb_internal_ts_shard_20 | ASC | false | true events | events_ts_idx | true | 2 | ts | ASC | false | false events | events_ts_idx | true | 3 | product_id | ASC | false | true events | events_ts_idx | true | 4 | owner | ASC | false | true events | events_ts_idx | true | 5 | serial_number | ASC | false | true events | events_ts_idx | true | 6 | event_id | ASC | false | true (12 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM events; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden ----------------------------+-----------+-------------+----------------+---------------------------------------------------+-----------------------------+------------ product_id | INT8 | false | NULL | | {events_pkey,events_ts_idx} | false owner | UUID | false | NULL | | {events_pkey,events_ts_idx} | false serial_number | VARCHAR | false | NULL | | {events_pkey,events_ts_idx} | false event_id | UUID | false | NULL | | {events_pkey,events_ts_idx} | false ts | TIMESTAMP | false | NULL | | {events_pkey,events_ts_idx} | false data | JSONB | true | NULL | | {events_pkey} | false crdb_internal_ts_shard_20 | INT8 | false | NULL | mod(fnv32(crdb_internal.datums_to_bytes(ts)), 20) | {events_ts_idx} | true (7 rows) ``` ## See also * Indexes * `CREATE INDEX` * `UUID`