Why use table partitioning
Table partitioning helps you reduce latency and cost:- Geo-partitioning allows you to keep user data close to the user, which reduces the distance that the data needs to travel, thereby reducing latency. To geo-partition a table, define location-based partitions while creating a table, create location-specific , and apply the zone configurations to the corresponding partitions.
- Archival-partitioning allows you to store infrequently-accessed data on slower and cheaper storage, thereby reducing costs. To archival-partition a table, define frequency-based partitions while creating a table, create frequency-specific with appropriate storage devices constraints, and apply the zone configurations to the corresponding partitions.
How it works
Table partitioning involves a combination of CockroachDB features:Node attributes
To store partitions in specific locations (e.g., geo-partitioning), or on machines with specific attributes (e.g., archival-partitioning), the nodes of your cluster must be with the relevant flags:- Use the
--localityflag to assign key-value pairs that describe the location of a node, for example,--locality=region=east,az=us-east-1. - Use the
--attrsflag to specify node capability, which might include specialized hardware or number of cores, for example,--attrs=ram:64gb. - Use the
attrsfield of the--storeflag to specify disk type or capability, for example,--store=path=/mnt/ssd01,attrs=ssd.
Table creation
You can define partitions and subpartitions over one or more columns of a table. During , you declare which values belong to each partition in one of two ways:- List partitioning: Enumerate all possible values for each partition. List partitioning is a good choice when the number of possible values is small. List partitioning is well-suited for geo-partitioning.
- Range partitioning: Specify a contiguous range of values for each partition by specifying lower and upper bounds. Range partitioning is a good choice when the number of possible values is too large to explicitly list out. Range partitioning is well-suited for archival-partitioning.
Partition by list
lets you map one or more tuples to a partition. To partition a table by list, use the syntax while creating the table. While defining a list partition, you can also set theDEFAULT partition that acts as a catch-all if none of the rows match the requirements for the defined partitions.
See Partition by List example below for more details.
Partition by range
lets you map ranges of tuples to a partition. To define a table partition by range, use the syntax while creating the table. While defining a range partition, you can use CockroachDB-definedMINVALUE and MAXVALUE parameters to define the lower and upper bounds of the ranges respectively.
The lower bound of a range partition is inclusive, while the upper bound is exclusive. For range partitions,
NULL is considered less than any other data, which is consistent with our key encoding ordering and ORDER BY behavior.< (now() - '1d') on 2017-01-30, it would be contain all values less than 2017-01-29. It would not update the next day, it would continue to contain values less than 2017-01-29.
See Partition by Range example below for more details.
Partition using primary key
The primary key required for partitioning is different from the conventional primary key. To define the primary key for partitioning, prefix the unique identifier(s) in the primary key with all columns you want to partition and subpartition the table on, in the order in which you want to nest your subpartitions. For instance, consider the database of a global online learning portal that has a table for students of all the courses across the world. If you want to geo-partition the table based on the countries of the students, then the primary key needs to be defined as:Primary key considerations
The order in which the columns are defined in the primary key is important. The partitions and subpartitions need to follow that order. In the example of the online learning portal, if you define the primary key as(country, expected_graduation_date, id), the primary partition is by country, and then subpartition is by expected_graduation_date. You cannot skip country and partition by expected_graduation_date.
Partition using a secondary index
The primary key discussed in the preceding section has two drawbacks:- It does not enforce that the identifier column is globally unique.
- It does not provide fast lookups on the identifier.
NOTICE message to the client stating that creating a non-partitioned index on a partitioned table may not perform well.
Partition using foreign key reference
If a partitioned table contains a to a non-partitioned table, the secondary index created automatically for the foreign key reference will not be partitioned. This can impact performance when querying against the partitioned table, as the data may exist in a distant node. To minimize potential latency issues, configure the non-partitioned table to be a .Replication zones
On their own, partitions are inert and simply apply a label to the rows of the table that satisfy the criteria of the defined partitions. Applying functionality to a partition requires creating and applying to the corresponding partitions. CockroachDB uses the most granular zone config available. Zone configs that target a partition are considered more granular than those that target a table or index, which in turn are considered more granular than those that target a database. For more information about how zone config inheritance works, see .Query partitions
Similar to , partitions can improve query performance by limiting the numbers of rows that a query must scan. In the case of , 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 , the 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 database are geo-partitioned by region, and you want to query theusers table for information about a specific user.
Here is the CREATE TABLE statement for the users table:
id column:
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:
Filter on a non-indexed column
Suppose that you want to query theusers table for information about a specific user, but you only know the user’s name.
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 ) 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:new york partition value.
Examples
Define table partitions by list
Consider a global online learning portal, RoachLearn, that has a database containing a table of students across the world. Suppose we have three availability zone: one in the United States, one in Germany, and another in Australia. To reduce latency, we want to keep the students’ data closer to their locations:- We want to keep the data of the students located in the United States and Canada in the United States availability zone.
- We want to keep the data of students located in Germany and Switzerland in the German availability zone.
- We want to keep the data of students located in Australia and New Zealand in the Australian availability zone.
Step 1. Identify the partitioning method
We want to geo-partition the table to keep the students’ data closer to their locations. We can achieve this by partitioning oncountry and using the PARTITION BY LIST syntax.
Step 2. Start each node with its availability zone location specified in the --locality flag
-
In separate terminal windows, start three nodes in the US availability zone:
-
In a new terminal window, initialize the cluster:
-
In new terminal windows, start three more nodes in the German availability zone:
-
In separate terminal windows, start three nodes in the Australian availability zone:
Step 3. Request and set a trial Enterprise license
See .Step 4. Create the roachlearn database and students table
-
Open the CockroachDB SQL shell:
-
Create the database and set it as current:
-
Create the table with the appropriate partitions:
Alternatively, you can create and partition the table as separate steps:
Step 5. Create and apply corresponding replication zones
To create replication zone and apply them to corresponding partitions, use the statement:-
Create a replication zone for the
north_americapartition and constrain its data to the US availability zone: -
Create a replication zone for the
europepartition and constrain its data to the German availability zone: -
Create a replication zone for the
australiapartition and constrain its data to the Australian availability zone: -
After creating these replication zones, you can view them using the statement:
Step 6. Check replica distribution
| Node IDs | Zone |
|---|---|
| 1-3 | north\_america |
| 4-6 | europe |
| 7-9 | australia |
US and CA-based students are located on nodes 1-3 in north_america, the replicas for DE and CH-based students are located on nodes 4-6 in europe, and the replicas for AU and NZ-based students are located on nodes 7-9 in australia.
Show partitions and zone constraints
To retrieve table partitions, you can use the statement:Define table partitions by range
Suppose we want to store the data of current students on fast and expensive storage devices (e.g., SSD) and store the data of the graduated students on slower, cheaper storage devices (e.g., HDD).Step 1. Identify the partitioning method
We want to archival-partition the table to keep newer data on faster devices and older data on slower devices. We can achieve this by partitioning the table by date and using thePARTITION BY RANGE syntax.
Step 2. Set the Enterprise license
To set the Enterprise license, see .Step 3. Start each node with the appropriate storage device specified in the --store flag
-
Start the first node:
-
Start the second node:
-
Initialize the cluster:
Step 4. Create a table with the appropriate partitions
Step 5. Create and apply corresponding zone configurations
To create zone configurations and apply them to corresponding partitions, use the statement:Step 6. Check replica distribution
Define subpartitions on a table
A list partition can itself be partitioned, forming a subpartition. There is no limit on the number of levels of subpartitioning; that is, list partitions can be infinitely nested.Range partitions cannot be subpartitioned.
- Keep the students’ data close to their location.
- Store the current students’ data on faster storage devices.
- Store the graduated students’ data on slower, cheaper storage devices (example: HDD).
Step 1. Identify the Partitioning method
We want to geo-partition as well as archival-partition the table. We can achieve this by partitioning the table first by location and then by date.Step 2. Start each node with the appropriate storage device specified in the --store flag
Start a node in the US availability zone:
Step 3. Set the Enterprise license
To set the Enterprise license, see .Step 4. Create a table with the appropriate partitions
graduated and current are sub-partitions of distinct partitions, they still need to be uniquely named. Hence the names graduated_au, graduated_us, and current_au and current_us.
Step 5. Create and apply corresponding zone configurations
To create zone configurations and apply them to corresponding partitions, use the statement:Step 6. Verify table partitions
Repartition a table
Consider the partitioned table of students of RoachLearn. Suppose the table has been partitioned on range to store the current students on fast and expensive storage devices (example: SSD) and store the data of the graduated students on slower, cheaper storage devices(example: HDD). Now suppose we want to change the date after which the students will be considered current to2018-08-15. We can achieve this by using the subcommand of the command.
Unpartition a table
You can remove the partitions on a table by using the syntax:Show the replication zone for a partition
To view the replication zone for a partition, use the statement.Locality–resilience tradeoff
There is a tradeoff between making reads/writes fast and surviving failures. Consider a partition with three replicas ofroachlearn.students for Australian students.
- If only one replica is pinned to an Australian availability zone, then reads may be fast (via leases follow the workload) but writes will be slow.
- If two replicas are pinned to an Australian availability zone, then reads and writes will be fast (as long as the cross-ocean link has enough bandwidth that the third replica doesn’t fall behind). If those two replicas are in the same availability zone, then the loss of one availability zone can lead to data unavailability, so some deployments may want two separate Australian availability zones.
- If all three replicas are in Australian availability zones, then three Australian availability zones are needed to be resilient to an availability zone loss.
How CockroachDB’s partitioning differs from other databases
Other databases use partitioning for three additional use cases: secondary indexes, sharding, and bulk loading/deleting. CockroachDB addresses these use-cases not by using partitioning, but in the following ways:- Changes to secondary indexes: CockroachDB solves these changes through online schema changes. Online schema changes are a superior feature to partitioning because they require zero-downtime and eliminate the potential for consistency problems.
- Sharding: CockroachDB automatically shards data as a part of its distributed database architecture.
- Bulk Loading & Deleting: CockroachDB does not have a feature that supports this use case as of now.
- Logical structure of partitions: CockroachDB uses the partitioning concept to allow users to logically subdivide a single physical table to enable independent configuration of those partitions using . Other databases sometimes implement partitioning as the logical union of physically separate tables. This difference means that CockroachDB is able to permit inexpensive repartitioning in contrast to other databases. Consequently, CockroachDB is unable to provide the same bulk data deletion operations over table partitions that other databases achieve by physically dropping the underlying table represented by the partition.
Known limitations
- When defining a , either during table creation or table alteration, it is not possible to use placeholders in the
PARTITION BYclause. - CockroachDB does not currently support dropping a single partition from a table. In order to remove partitions, you can repartition the table.