The SPLIT AT statement forces a key-value layer range split at the specified row in a table or index.
Synopsis
Required Privileges
The user must have the INSERT privilege on the table or index.
Parameters
| Parameter | Description |
|---|---|
table_nametable_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
> SHOW EXPERIMENTAL_RANGES FROM TABLE kv;
+-----------+---------+----------+----------+--------------+
| Start Key | End Key | Range ID | Replicas | Lease Holder |
+-----------+---------+----------+----------+--------------+
| NULL | NULL | 72 | {1} | 1 |
+-----------+---------+----------+----------+--------------+
(1 row)
> 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)
> 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
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:
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:
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:
> 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 ∞:
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
> CREATE INDEX secondary ON kv (v);
> SHOW EXPERIMENTAL_RANGES FROM INDEX kv@secondary;
+-----------+---------+----------+----------+--------------+
| Start Key | End Key | Range ID | Replicas | Lease Holder |
+-----------+---------+----------+----------+--------------+
| NULL | NULL | 75 | {1} | 1 |
+-----------+---------+----------+----------+--------------+
(1 row)
> 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)
> 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
Was this helpful?
