SHOW PARTITIONS statement to view details about existing 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.
In testing, scripting, and other programmatic environments, we recommend querying the
crdb_internal.partitions internal table for partition information instead of using the SHOW PARTITIONS statement. For more information, see Querying partitions programmatically.Synopsis
Required privileges
No privileges are required to list partitions.Parameters
| Parameter | Description |
|---|---|
database\_name | The name of the database for which to show partitions. |
table\_name | The name of the table for which to show partitions. |
table\_index\_name | The name of the index for which to show partitions. |
Response
The following fields are returned for each partition:| Field | Description |
|---|---|
database\_name | The name of the database that contains the partition. |
table\_name | The name of the table that contains the partition. |
partition\_name | The name of the partition. |
parent\_partition | The name of the parent partition, if the partition is a subpartition. |
column\_names | The names of the columns in the partition definition expression. |
index\_name | The name of the index for the partition. |
partition\_value | The value that defines the partition. |
zone\_constraints | The zone constraints, if replication zones are configured for the partition. |
Examples
Setup
The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App. To follow along, runcockroach demo with the --geo-partitioned-replicas flag. This command opens an interactive SQL shell to a temporary, 9-node in-memory cluster with the movr database.
$ cockroach demo --geo-partitioned-replicas
movr database in this example is pre-partitioned. For information about partitioning tables, see Define Table Partitions or ALTER TABLE... PARTITION BY.
Show table partitions
> SHOW PARTITIONS FROM TABLE users;
database_name | table_name | partition_name | parent_partition | column_names | index_name | partition_value | zone_config | full_zone_config
+---------------+------------+----------------+------------------+--------------+------------------+-------------------------------------------------+----------------------------------------+-----------------------------------------+
movr | users | us_west | NULL | city | users@users_pkey | ('seattle'), ('san francisco'), ('los angeles') | constraints = '[+region=us-west1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=us-west1]',
| | | | | | | | lease_preferences = '[]'
movr | users | us_east | NULL | city | users@users_pkey | ('new york'), ('boston'), ('washington dc') | constraints = '[+region=us-east1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=us-east1]',
| | | | | | | | lease_preferences = '[]'
movr | users | europe_west | NULL | city | users@users_pkey | ('amsterdam'), ('paris'), ('rome') | constraints = '[+region=europe-west1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=europe-west1]',
| | | | | | | | lease_preferences = '[]'
(3 rows)
SHOW CREATE TABLE to view partitions on a 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@users_pkey CONFIGURE ZONE USING
| constraints = '[+region=europe-west1]';
| ALTER PARTITION us_east OF INDEX movr.public.users@users_pkey CONFIGURE ZONE USING
| constraints = '[+region=us-east1]';
| ALTER PARTITION us_west OF INDEX movr.public.users@users_pkey CONFIGURE ZONE USING
| constraints = '[+region=us-west1]'
(1 row)
SHOW CREATE TABLE output includes a warning.
> ALTER PARTITION us_west OF TABLE users CONFIGURE ZONE DISCARD;
ALTER PARTITION us_east OF TABLE users CONFIGURE ZONE DISCARD;
ALTER PARTITION europe_west OF TABLE users CONFIGURE ZONE DISCARD;
> 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'))
| )
| -- Warning: Partitioned table with no zone configurations.
(1 row)
Show partitions by index
> SHOW PARTITIONS FROM INDEX vehicles@vehicles_auto_index_fk_city_ref_users;
database_name | table_name | partition_name | parent_partition | column_names | index_name | partition_value | zone_config | full_zone_config
+---------------+------------+----------------+------------------+--------------+------------------------------------------------+-------------------------------------------------+----------------------------------------+-----------------------------------------+
movr | vehicles | us_west | NULL | city | vehicles@vehicles_auto_index_fk_city_ref_users | ('seattle'), ('san francisco'), ('los angeles') | constraints = '[+region=us-west1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=us-west1]',
| | | | | | | | lease_preferences = '[]'
movr | vehicles | us_east | NULL | city | vehicles@vehicles_auto_index_fk_city_ref_users | ('new york'), ('boston'), ('washington dc') | constraints = '[+region=us-east1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=us-east1]',
| | | | | | | | lease_preferences = '[]'
movr | vehicles | europe_west | NULL | city | vehicles@vehicles_auto_index_fk_city_ref_users | ('amsterdam'), ('paris'), ('rome') | constraints = '[+region=europe-west1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=europe-west1]',
| | | | | | | | lease_preferences = '[]'
(3 rows)
Show partitions by database
> SHOW PARTITIONS FROM DATABASE movr;
database_name | table_name | partition_name | parent_partition | column_names | index_name | partition_value | zone_config | full_zone_config
+---------------+------------+----------------+------------------+--------------+------------------------+-------------------------------------------------+----------------------------------------+-----------------------------------------+
movr | users | us_west | NULL | city | users@users_pkey | ('seattle'), ('san francisco'), ('los angeles') | NULL | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[]',
| | | | | | | | lease_preferences = '[]'
movr | users | us_east | NULL | city | users@users_pkey | ('new york'), ('boston'), ('washington dc') | NULL | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[]',
| | | | | | | | lease_preferences = '[]'
movr | users | europe_west | NULL | city | users@users_pkey | ('amsterdam'), ('paris'), ('rome') | NULL | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[]',
| | | | | | | | lease_preferences = '[]'
movr | vehicles | us_west | NULL | city | vehicles@vehicles_pkey | ('seattle'), ('san francisco'), ('los angeles') | constraints = '[+region=us-west1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=us-west1]',
| | | | | | | | lease_preferences = '[]'
movr | vehicles | us_east | NULL | city | vehicles@vehicles_pkey | ('new york'), ('boston'), ('washington dc') | constraints = '[+region=us-east1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=us-east1]',
| | | | | | | | lease_preferences = '[]'
movr | vehicles | europe_west | NULL | city | vehicles@vehicles_pkey | ('amsterdam'), ('paris'), ('rome') | constraints = '[+region=europe-west1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=europe-west1]',
| | | | | | | | lease_preferences = '[]'
...
(24 rows)
Querying partitions programmatically
Thecrdb_internal.partitions internal table contains information about the partitions in your database. In testing, scripting, and other programmatic environments, we recommend querying this table for partition information instead of using the SHOW PARTITIONS statement. For example, to get all us_west partitions of in your database, you can run the following query:
> SELECT * FROM crdb_internal.partitions WHERE name='us_west';
table_id | index_id | parent_name | name | columns | column_names | list_value | range_value | zone_id | subzone_id
+----------+----------+-------------+---------+---------+--------------+-------------------------------------------------+-------------+---------+------------+
53 | 1 | NULL | us_west | 1 | city | ('seattle'), ('san francisco'), ('los angeles') | NULL | 0 | 0
54 | 1 | NULL | us_west | 1 | city | ('seattle'), ('san francisco'), ('los angeles') | NULL | 54 | 1
54 | 2 | NULL | us_west | 1 | city | ('seattle'), ('san francisco'), ('los angeles') | NULL | 54 | 2
55 | 1 | NULL | us_west | 1 | city | ('seattle'), ('san francisco'), ('los angeles') | NULL | 55 | 1
55 | 2 | NULL | us_west | 1 | city | ('seattle'), ('san francisco'), ('los angeles') | NULL | 55 | 2
55 | 3 | NULL | us_west | 1 | vehicle_city | ('seattle'), ('san francisco'), ('los angeles') | NULL | 55 | 3
56 | 1 | NULL | us_west | 1 | city | ('seattle'), ('san francisco'), ('los angeles') | NULL | 56 | 1
58 | 1 | NULL | us_west | 1 | city | ('seattle'), ('san francisco'), ('los angeles') | NULL | 58 | 1
(8 rows)
crdb_internal.tables, include information that could be useful in conjunction with crdb_internal.partitions.
For example, if you want the output for your partitions to include the name of the table and database, you can perform a join of the two tables:
> SELECT
partitions.name AS partition_name, column_names, list_value, tables.name AS table_name, database_name
FROM crdb_internal.partitions JOIN crdb_internal.tables ON partitions.table_id=tables.table_id
WHERE tables.name='users';
partition_name | column_names | list_value | table_name | database_name
+----------------+--------------+-------------------------------------------------+------------+---------------+
us_west | city | ('seattle'), ('san francisco'), ('los angeles') | users | movr
us_east | city | ('new york'), ('boston'), ('washington dc') | users | movr
europe_west | city | ('amsterdam'), ('paris'), ('rome') | users | movr
(3 rows)