PARTITION BY is a subcommand of ALTER TABLE and ALTER INDEX that is used to partition, re-partition, or un-partition a table or secondary index. After defining partitions, CONFIGURE ZONE is used to control the replication and placement of partitions.
Most users should not need to use partitioning directly. Instead, they should use CockroachDB's built-in multi-region capabilities, which automatically handle geo-partitioning and other low-level details.
Partitioning is an Enterprise-only feature. If you are looking for the PARTITION BY used in SQL window functions, see Window Functions.
This command can be combined with other ALTER TABLE commands in a single statement. For a list of commands that can be combined, see ALTER TABLE. For a demonstration, see Add and rename columns atomically.
Primary key requirements
The primary key required for partitioning is different from the conventional primary key: The unique identifier in the primary key must be prefixed with all columns you want to partition and subpartition the table on, in the order in which you want to nest your subpartitions.
If the primary key in your existing table does not meet the requirements, you can change the primary key with an ALTER TABLE ... PRIMARY KEY statement.
Synopsis
alter_table_partition_by_stmt ::=
alter_index_partition_by_stmt ::=
Parameters
| Parameter | Description |
|---|---|
table_name |
The name of the table you want to define partitions for. |
index_name |
The name of the index you want to define partitions for. |
name_list |
List of columns you want to define partitions on (in the order they are defined in the primary key). |
list_partitions |
Name of list partition followed by the list of values to be included in the partition. |
range_partitions |
Name of range partition followed by the range of values to be included in the partition. |
Required privileges
The user must have the CREATE privilege on the table.
Viewing schema changes
This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.
Query partitions
Similar to indexes, partitions can improve query performance by limiting the numbers of rows that a query must scan. In the case of geo-partitioned data, partitioning can limit a query scan to data in a specific region.
Filter on an indexed column
If you filter the query of a partitioned table on a column in the index directly following the partition prefix, the cost-based optimizer creates a query plan that scans each partition in parallel, rather than performing a costly sequential scan of the entire table.
For example, suppose that the tables in the movr database are geo-partitioned by region, and you want to query the users table for information about a specific user.
Here is the CREATE TABLE statement for the users table:
> SHOW CREATE TABLE users;
table_name | create_statement
+------------+-------------------------------------------------------------------------------------+
users | CREATE TABLE users (
| id UUID NOT NULL,
| city VARCHAR NOT NULL,
| name VARCHAR NULL,
| address VARCHAR NULL,
| credit_card VARCHAR NULL,
| CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
| FAMILY "primary" (id, city, name, address, credit_card)
| ) PARTITION BY LIST (city) (
| PARTITION us_west VALUES IN (('seattle'), ('san francisco'), ('los angeles')),
| PARTITION us_east VALUES IN (('new york'), ('boston'), ('washington dc')),
| PARTITION europe_west VALUES IN (('amsterdam'), ('paris'), ('rome'))
| );
| ALTER PARTITION europe_west OF INDEX movr.public.users@primary CONFIGURE ZONE USING
| constraints = '[+region=europe-west1]';
| ALTER PARTITION us_east OF INDEX movr.public.users@primary CONFIGURE ZONE USING
| constraints = '[+region=us-east1]';
| ALTER PARTITION us_west OF INDEX movr.public.users@primary CONFIGURE ZONE USING
| constraints = '[+region=us-west1]'
(1 row)
If you know the user's id, you can filter on the id column:
> SELECT * FROM users WHERE id='00000000-0000-4000-8000-000000000000';
id | city | name | address | credit_card
+--------------------------------------+----------+---------------+----------------------+-------------+
00000000-0000-4000-8000-000000000000 | new york | Robert Murphy | 99176 Anderson Mills | 8885705228
(1 row)
An EXPLAIN statement shows more detail about the cost-based optimizer's plan:
> EXPLAIN SELECT * FROM users WHERE id='00000000-0000-4000-8000-000000000000';
tree | field | description
+------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| distributed | true
| vectorized | false
scan | |
| table | users@primary
| spans | -/"amsterdam" /"amsterdam"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"amsterdam"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"amsterdam\x00"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"boston" /"boston"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"boston"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"boston\x00"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"los angeles" /"los angeles"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"los angeles"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"los angeles\x00"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"new york" /"new york"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"new york"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"new york\x00"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"paris" /"paris"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"paris"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"paris\x00"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"rome" /"rome"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"rome"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"rome\x00"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"san francisco" /"san francisco"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"san francisco"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"san francisco\x00"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"seattle" /"seattle"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"seattle"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"seattle\x00"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"washington dc" /"washington dc"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"washington dc"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"washington dc\x00"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-
| filter | id = '00000000-0000-4000-8000-000000000000'
(6 rows)
Because the id column is in the primary index, directly after the partition prefix (city), the optimal query is constrained by the partitioned values. This means the query scans each partition in parallel for the unique id value.
If you know the set of all possible partitioned values, adding a check constraint to the table's create statement can also improve performance. For example:
> ALTER TABLE users ADD CONSTRAINT check_city CHECK (city IN ('amsterdam','boston','los angeles','new york','paris','rome','san francisco','seattle','washington dc'));
> EXPLAIN SELECT * FROM users WHERE id='00000000-0000-4000-8000-000000000000';
tree | field | description
+------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| distributed | false
| vectorized | false
scan | |
| table | users@primary
| spans | /"amsterdam"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"amsterdam"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"boston"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"boston"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"los angeles"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"los angeles"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"new york"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"new york"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"paris"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"paris"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"rome"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"rome"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"san francisco"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"san francisco"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"seattle"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"seattle"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/# /"washington dc"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"-/"washington dc"/"\x00\x00\x00\x00\x00\x00@\x00\x80\x00\x00\x00\x00\x00\x00\x00"/#
| parallel |
(6 rows)
To see the performance improvement over a query that performs a full table scan, compare these queries to a query with a filter on a column that is not in the index.
Filter on a non-indexed column
Suppose that you want to query the users table for information about a specific user, but you only know the user's name.
> SELECT * FROM users WHERE name='Robert Murphy';
id | city | name | address | credit_card
+--------------------------------------+----------+---------------+----------------------+-------------+
00000000-0000-4000-8000-000000000000 | new york | Robert Murphy | 99176 Anderson Mills | 8885705228
(1 row)
> EXPLAIN SELECT * FROM users WHERE name='Robert Murphy';
tree | field | description
+------+-------------+------------------------+
| distributed | true
| vectorized | false
scan | |
| table | users@primary
| spans | ALL
| filter | name = 'Robert Murphy'
(6 rows)
The query returns the same result, but because name is not an indexed column, the query performs a full table scan that spans across all partition values.
Filter on a partitioned column
If you know which partition contains the data that you are querying, using a filter (e.g., a WHERE clause) on the column that is used for the partition can further improve performance by limiting the scan to the specific partition(s) that contain the data that you are querying.
Now suppose that you know the user's name and location. You can query the table with a filter on the user's name and city:
> EXPLAIN SELECT * FROM users WHERE name='Robert Murphy' AND city='new york';
tree | field | description
+------+-------------+-----------------------------------+
| distributed | true
| vectorized | false
scan | |
| table | users@primary
| spans | /"new york"-/"new york"/PrefixEnd
| filter | name = 'Robert Murphy'
(6 rows)
The table returns the same results as before, but at a much lower cost, as the query scan now spans just the new york partition value.
Examples
Define a list partition on a table or secondary index
Suppose we have a table called students_by_list, and secondary index on the table called name_idx, in a global online learning portal, and the primary key of the table is defined as (country, id). We can define partitions on the table and index by list:
> ALTER TABLE students_by_list PARTITION BY LIST (country) (
PARTITION north_america VALUES IN ('CA','US'),
PARTITION australia VALUES IN ('AU','NZ'),
PARTITION DEFAULT VALUES IN (default)
);
> ALTER INDEX students_by_list@name_idx PARTITION BY LIST (country) (
PARTITION north_america VALUES IN ('CA','US'),
PARTITION australia VALUES IN ('AU','NZ'),
PARTITION DEFAULT VALUES IN (default)
);
Define a range partition on a table or secondary index
Suppose we have another table called students_by_range, also with a secondary index called name_idx, and the primary key of the table is defined as (expected_graduation_date, id). We can define partitions on the table and index by range:
> ALTER TABLE students_by_range PARTITION BY RANGE (expected_graduation_date) (
PARTITION graduated VALUES FROM (MINVALUE) TO ('2017-08-15'),
PARTITION current VALUES FROM ('2017-08-15') TO (MAXVALUE)
);
> ALTER INDEX students_by_range@name_idx PARTITION BY RANGE (expected_graduation_date) (
PARTITION graduated VALUES FROM (MINVALUE) TO ('2017-08-15'),
PARTITION current VALUES FROM ('2017-08-15') TO (MAXVALUE)
);
Define subpartitions on a table or secondary index
Suppose we have an yet another table named students, again with a secondary index called name_idx, and the primary key is defined as (country, expected_graduation_date, id). We can define partitions and subpartitions on the table and index:
> ALTER TABLE students PARTITION BY LIST (country) (
PARTITION australia VALUES IN ('AU','NZ') PARTITION BY RANGE (expected_graduation_date) (
PARTITION graduated_au VALUES FROM (MINVALUE) TO ('2017-08-15'),
PARTITION current_au VALUES FROM ('2017-08-15') TO (MAXVALUE)
),
PARTITION north_america VALUES IN ('US','CA') PARTITION BY RANGE (expected_graduation_date) (
PARTITION graduated_us VALUES FROM (MINVALUE) TO ('2017-08-15'),
PARTITION current_us VALUES FROM ('2017-08-15') TO (MAXVALUE)
)
);
> ALTER INDEX students@name_idx PARTITION BY LIST (country) (
PARTITION australia VALUES IN ('AU','NZ') PARTITION BY RANGE (expected_graduation_date) (
PARTITION graduated_au VALUES FROM (MINVALUE) TO ('2017-08-15'),
PARTITION current_au VALUES FROM ('2017-08-15') TO (MAXVALUE)
),
PARTITION north_america VALUES IN ('US','CA') PARTITION BY RANGE (expected_graduation_date) (
PARTITION graduated_us VALUES FROM (MINVALUE) TO ('2017-08-15'),
PARTITION current_us VALUES FROM ('2017-08-15') TO (MAXVALUE)
)
);
Repartition a table or secondary index
> ALTER TABLE students_by_range PARTITION BY RANGE (expected_graduation_date) (
PARTITION graduated VALUES FROM (MINVALUE) TO ('2018-08-15'),
PARTITION current VALUES FROM ('2018-08-15') TO (MAXVALUE)
);
> ALTER INDEX students_by_range@name_idx PARTITION BY RANGE (expected_graduation_date) (
PARTITION graduated VALUES FROM (MINVALUE) TO ('2018-08-15'),
PARTITION current VALUES FROM ('2018-08-15') TO (MAXVALUE)
);
Unpartition a table or secondary index
> ALTER TABLE students PARTITION BY NOTHING;
> ALTER INDEX students@name_idx PARTITION BY NOTHING;
See also
Was this helpful?
