SHOW PARTITIONS

On this page Carat arrow pointing down

Use the SHOW PARTITIONS statement to view details about existing partitions.

Tip:

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.

Note:

This is an enterprise-only feature. You can use free trial credits to try it out.

Tip:

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

SHOW PARTITIONS FROM TABLE table_name DATABASE database_name INDEX table_index_name table_name @ *

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, run cockroach 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.

icon/buttons/copy
$ cockroach demo --geo-partitioned-replicas

The 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

icon/buttons/copy
> 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)

You can also use SHOW CREATE TABLE to view partitions on a table:

icon/buttons/copy
> 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)

If a partitioned table has no zones configured, the SHOW CREATE TABLE output includes a warning.

icon/buttons/copy
> 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;
icon/buttons/copy
> 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

icon/buttons/copy
> 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

icon/buttons/copy
> 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

The crdb_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:

icon/buttons/copy
> 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)

Other internal tables, like 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:

icon/buttons/copy
> 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)

See also


Yes No
On this page

Yes No