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 hot spots 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 TIMESTAMPs), 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 hot spot 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 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_counts if the default bucket_count does not satisfy your use case.
New in v22.2: 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 thecrdb_regioncolumn 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 thatPARTITION ALL BYis 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:
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:
> CREATE TABLE products (
ts DECIMAL PRIMARY KEY USING HASH,
product_id INT8
);
> SHOW INDEX FROM products;
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)
> SHOW COLUMNS FROM products;
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:
> 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
);
> SHOW INDEX FROM events;
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)
> SHOW COLUMNS FROM events;
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:
> 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:
> CREATE INDEX ON events(ts) USING HASH;
> SHOW INDEX FROM events;
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)
> SHOW COLUMNS FROM events;
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:
> 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:
> ALTER TABLE events ALTER PRIMARY KEY USING COLUMNS (product_id, owner, serial_number, ts, event_id) USING HASH;
> SHOW INDEX FROM events;
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)
> SHOW COLUMNS FROM events;
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, you can show the hash-sharded index definition along with the table creation statement using SHOW CREATE TABLE:
> SHOW CREATE TABLE events;
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:
> 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)
);
> SHOW INDEX FROM events;
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)
> SHOW COLUMNS FROM events;
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
Was this helpful?
