# PARTITION BY

`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.

Note:

Partitioning is an enterprise-only feature. If you are looking for the `PARTITION BY` used in SQL window functions, see Window Functions.

Tip:

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.

New in v20.1: 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`.

## Querying 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.

### Filtering 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.

### Filtering 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.

### Filtering on an 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

- `CREATE TABLE`
- `ALTER TABLE`
- `ALTER INDEX`
- Define Table Partitions
- `SHOW JOBS`
- `SHOW PARTITIONS`