Index Sequential Keys with Hash-sharded Indexes

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 based on and on . 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., or a series of increasing, non-repeating ), 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 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 , 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 .

In v21.2 and earlier, hash-sharded indexes create a physical STORED 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 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 , 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 . 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.

Use instead of the sql.defaults.* . This allows you to set a default value for all users for any 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 , 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:

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