The SPLIT AT statement forces a key-value layer range split at the specified row in a table or index.

Synopsis

ALTER TABLE table_name SPLIT AT select_stmt
ALTER INDEX table_name @ index_name SPLIT AT select_stmt

Required privileges

The user must have the INSERT privilege on the table or index.

Parameters

Parameter Description
table_name
table_name @ index_name
The name of the table or index that should be split.
select_stmt A selection query that produces one or more rows at which to split the table or index.

Why manually split a range?

The key-value layer of CockroachDB is broken into sections of contiguous key-space known as ranges. By default, CockroachDB attempts to keep ranges below a size of 64MiB. To do this, the system will automatically split a range if it grows larger than this limit. For most use cases, this automatic range splitting is sufficient, and you should never need to worry about when or where the system decides to split ranges.

However, there are reasons why you may want to perform manual splits on the ranges that store tables or indexes:

  • When a table only consists of a single range, all writes and reads to the table will be served by that range's leaseholder. If a table only holds a small amount of data but is serving a large amount of traffic, load distribution can become unbalanced. Splitting the table's ranges manually can allow the load on the table to be more evenly distributed across multiple nodes. For tables consisting of more than a few ranges, load will naturally be distributed across multiple nodes and this will not be a concern.

  • When a table is created, it will only consist of a single range. If you know that a new table will immediately receive significant write traffic, you may want to preemptively split the table based on the expected distribution of writes before applying the load. This can help avoid reduced workload performance that results when automatic splits are unable to keep up with write traffic.

Note that when a table is truncated, it is essentially re-created in a single new empty range, and the old ranges that used to constitute the table are garbage collected. Any pre-splitting you have performed on the old version of the table will not carry over to the new version. The new table will need to be pre-split again.

Examples

Split a table

copy
icon/buttons/copy
> SHOW EXPERIMENTAL_RANGES FROM TABLE kv;
+-----------+---------+----------+----------+--------------+
| start_key | end_key | range_id | replicas | lease_holder |
+-----------+---------+----------+----------+--------------+
| NULL      | NULL    |       72 | {1}      |            1 |
+-----------+---------+----------+----------+--------------+
(1 row)
copy
icon/buttons/copy
> ALTER TABLE kv SPLIT AT VALUES (10), (20), (30);
+------------+----------------+
|    key     |     pretty     |
+------------+----------------+
| \u0209\x92 | /Table/64/1/10 |
| \u0209\x9c | /Table/64/1/20 |
| \u0209\xa6 | /Table/64/1/30 |
+------------+----------------+
(3 rows)
copy
icon/buttons/copy
> SHOW EXPERIMENTAL_RANGES FROM TABLE kv;
+-----------+---------+----------+----------+--------------+
| start_key | end_key | range_id | replicas | lease_holder |
+-----------+---------+----------+----------+--------------+
| NULL      | /10     |       72 | {1}      |            1 |
| /10       | /20     |       73 | {1}      |            1 |
| /20       | /30     |       74 | {1}      |            1 |
| /30       | NULL    |       75 | {1}      |            1 |
+-----------+---------+----------+----------+--------------+
(4 rows)

Split a table with a composite primary key

You may want to split a table with a composite primary key (e.g., when working with partitions).

Given the table

copy
icon/buttons/copy
CREATE TABLE t (k1 INT, k2 INT, v INT, w INT, PRIMARY KEY (k1, k2));

we can split it at its primary key like so:

copy
icon/buttons/copy
ALTER TABLE t SPLIT AT VALUES (5,1), (5,2), (5,3);
+------------+-----------------+
|    key     |     pretty      |
+------------+-----------------+
| \xbc898d89 | /Table/52/1/5/1 |
| \xbc898d8a | /Table/52/1/5/2 |
| \xbc898d8b | /Table/52/1/5/3 |
+------------+-----------------+
(3 rows)

To see more information about the range splits, run:

copy
icon/buttons/copy
SHOW EXPERIMENTAL_RANGES FROM TABLE t;
+-----------+---------+----------+----------+--------------+
| start_key | end_key | range_id | replicas | lease_holder |
+-----------+---------+----------+----------+--------------+
| NULL      | /5/1    |      151 | {2,3,5}  |            5 |
| /5/1      | /5/2    |      152 | {2,3,5}  |            5 |
| /5/2      | /5/3    |      153 | {2,3,5}  |            5 |
| /5/3      | NULL    |      154 | {2,3,5}  |            5 |
+-----------+---------+----------+----------+--------------+
(4 rows)

Alternatively, you could split at a prefix of the primary key columns. For example, to add a split before all keys that start with 3, run:

copy
icon/buttons/copy
> ALTER TABLE t SPLIT AT VALUES (3);
+----------+---------------+
|   key    |    pretty     |
+----------+---------------+
| \xcd898b | /Table/69/1/3 |
+----------+---------------+
(1 row)

Conceptually, this means that the second range will include keys that start with 3 through :

copy
icon/buttons/copy
SHOW EXPERIMENTAL_RANGES FROM TABLE t;
+-----------+---------+----------+----------+--------------+
| start_key | end_key | range_id | replicas | lease_holder |
+-----------+---------+----------+----------+--------------+
| NULL      | /3      |      155 | {2,3,5}  |            5 |
| /3        | NULL    |      165 | {2,3,5}  |            5 |
+-----------+---------+----------+----------+--------------+
(2 rows)

Split an index

copy
icon/buttons/copy
> CREATE INDEX secondary ON kv (v);
copy
icon/buttons/copy
> SHOW EXPERIMENTAL_RANGES FROM INDEX kv@secondary;
+-----------+---------+----------+----------+--------------+
| start_key | end_key | range_id | replicas | lease_holder |
+-----------+---------+----------+----------+--------------+
| NULL      | NULL    |       75 | {1}      |            1 |
+-----------+---------+----------+----------+--------------+
(1 row)
copy
icon/buttons/copy
> ALTER INDEX kv@secondary SPLIT AT (SELECT v FROM kv LIMIT 3);
+---------------------+-----------------+
|         key         |     pretty      |
+---------------------+-----------------+
| \u020b\x12a\x00\x01 | /Table/64/3/"a" |
| \u020b\x12b\x00\x01 | /Table/64/3/"b" |
| \u020b\x12c\x00\x01 | /Table/64/3/"c" |
+---------------------+-----------------+
(3 rows)
copy
icon/buttons/copy
> SHOW EXPERIMENTAL_RANGES FROM INDEX kv@secondary;
+-----------+---------+----------+----------+--------------+
| start_key | end_key | range_id | replicas | lease_holder |
+-----------+---------+----------+----------+--------------+
| NULL      | /"a"    |       75 | {1}      |            1 |
| /"a"      | /"b"    |       76 | {1}      |            1 |
| /"b"      | /"c"    |       77 | {1}      |            1 |
| /"c"      | NULL    |       78 | {1}      |            1 |
+-----------+---------+----------+----------+--------------+
(4 rows)

See also



Yes No