This topic describes the best practices for table operations.
Primary key design
Tablestore dynamically distributes table data to partitions based on the partition key. Each partition is hosted on one server node. The partition key is used as the minimum partition unit. Data with the same partition key value cannot be split further.
Applications must balance data distribution and access distribution across partitions. Otherwise, data with the same partition key value may become a hotspot, increasing the workloads on the server hosting the partition.
Tablestore sorts the rows in a table by primary key. A well-designed primary key can better balance data distribution across partitions and lets you fully utilize Tablestore's high scalability.
When you select a partition key, take note of the following items:
We recommend that data with the same partition key value in a table do not exceed 10 GB in size.
NoteThis recommendation is provided to prevent an access hotspot and not because of the limit on storage usage.
Data with different partition key values in the same table is logically independent.
Do not concentrate the access load on a small range of consecutive partition key values.
Partition key design
Spliced partition key
In Tablestore, we recommend that data with the same partition key value in a table do not exceed 10 GB in size. If data with the same partition key value in a table exceeds 10 GB in size, you can splice multiple original primary key columns into a partition key when you design the table.
This prevents the size of data with the same partition key value from reaching the upper limit. When you splice the partition key, take note of the following items:
When you choose the primary key columns that you want to splice, make sure that the original rows have different partition key values after splicing.
When you splice primary key columns of the Integer type, convert the integers into strings whose length is fixed. If an integer has fewer digits than the fixed length, you can pad it with leading zeros to ensure that the order of the table data remains unchanged.
When you select a connector, consider its effect on the lexicographical order of the new partition key. We recommend that you select a connector with an ASCII code lower than that of all other available characters.
Add hash prefixes to the partition key
Do not use a primary key column with sequentially increasing values as the partition key. If you need to use a primary key column with sequentially increasing values as the partition key, we recommend that you add hash prefixes to the partition key to ensure random data distribution across partitions, balancing the access load.
However, you can no longer call the GetRange operation to read logically contiguous data whose primary key value is within a specific range.
Data operations
Write data in parallel
A Tablestore table is split into multiple partitions distributed across multiple Tablestore servers.
When you sequentially write data sorted by primary key to Tablestore in a batch, the write load is concentrated on a specific partition, leaving other partitions in the idle state. In this case, you cannot fully utilize the reserved read and write throughput, which slows data import. You can use one of the following methods to accelerate data import:
Change the original data order, and then import the data. Make sure that the written data is evenly distributed across each partition.
Use multiple worker threads to import data in parallel. Split a large data set into multiple smaller ones and then randomly import them through the worker threads.
Distinguish between hot and cold data
In most cases, data is time-sensitive. Recently generated data may be accessed more often than previously generated data. Previously generated data gradually becomes cold data but still occupies storage space.
A large amount of cold data stored in a table results in unbalanced access load distribution across partitions. In this case, the reserved read and write throughput is underutilized.
To resolve this issue, use different tables to separate cold and hot data, and configure different reserved read and write throughput for each table.
Scenario-based case study
This section describes how to design a table based on a specific scenario.
Scenario
A table stores the consumption records of university students. Each consumption record is associated with a student by using their student ID card. The primary key columns include the CardID, SellerID, DeviceID, and OrderNumber columns. The CardID column contains the student card ID, the SellerID column contains the seller ID, the DeviceID column contains the point-of-sale device ID, and the OrderNumber column contains the order number. The following rules apply to the table:
Each student card corresponds to one CardID, and each seller corresponds to one SellerID.
Each point-of-sale device corresponds to a globally unique DeviceID.
Each consumption record generated by a point-of-sale device has an OrderNumber. An OrderNumber generated by a device is unique only to the device. For example, different point-of-sale devices may generate two separate consumption records using the same OrderNumber.
OrderNumbers generated by the same point-of-sale device are sorted by timestamp. New consumption records have larger sequential OrderNumbers than the previous ones.
Every consumption record is written into the table in real time.
Design process
Specify a suitable primary key column as the partition key.
To efficiently use Tablestore, you must consider the partition key setting when designing a table's primary key. The following table analyzes various partition key settings. Based on the following table, we recommend that you use the CardID or DeviceID column instead of the SellerID or OrderNumber column as the partition key of a table. You can design other primary key columns based on your business requirements.
Partitioning method
Analysis
Conclusion
Use the CardID column as the table's partition key
In most cases, each card has a similar number of consumption records each day. As a result, the access load on each partition is balanced.
If you use the CardID column as the partition key of the table, you can better utilize the reserved read and write throughput.
We recommend that you use the CardID column as the table's partition key.
Use the SellerID column as the table's partition key
The number of sellers in a university is relatively small, and many consumption records may be concentrated on specific sellers. In this case, a smaller number of sellers become hotspots. This causes uneven access load distribution across partitions.
We recommend that you do not use the SellerID column as the table's partition key.
Use the DeviceID column as the table's partition key
The number of consumption records generated by each point-of-sale device per day can be estimated even if the number of consumption records for each seller per day varies. The estimate is calculated based on the order processing speed of a cashier, which determines the number of consumption records that can be generated by a point-of-sale device per day. Therefore, the DeviceID column can be used as the table's partition key to guarantee balanced access load distribution across partitions.
We recommend that you use the DeviceID column as the table's partition key.
Use the OrderNumber column as the table's partition key
The OrderNumber column stores sequentially increasing order numbers. Order numbers generated within the same period of time fall within a small range and are clustered in specific partitions. In this case, the reserved read and write throughput is underutilized.
If you need to use the OrderNumber column as the partition key, we recommend that you calculate the hash value of the order number and add it to the order number as a prefix to ensure equal access load distribution across partitions.
We recommend that you do not use the OrderNumber column as the table's partition key.
If data with the same partition key value in a table may exceed 10 GB in size, you can splice multiple primary key columns into a partition key when you design the table.
For example, the primary key columns of the table containing the consumption records of students are the DeviceID, SellerID, CardID, and OrderNumber columns, among which the DeviceID column is specified as the table's partition key. The rows in which the value of the DeviceID column may exceed 10 GB in size. In this case, splice the DeviceID, SellerID, and CardID columns into the partition key.
The following table describes the schema of the original table.
DeviceID
SellerID
CardID
OrderNumber
attrs
16
'a100'
66661
200001
...
54
'a100'
6777
200003
...
54
'a1001'
6777
200004
...
167
'a101'
283408
200002
...
The following table describes the schema of the new table in which the DeviceID, SellerID, and CardID columns are spliced into the partition key.
CombineDeviceIDSellerIDCardID
OrderNumber
attrs
'16:a100:66661'
200001
...
'167:a101:283408'
200002
...
'54:a1001:6777'
200004
...
'54:a100:6777'
200003
...
In the original table, the two rows in which the value of the DeviceID column is 54 contain two consumption records with the same partition key value. In the new table, the two consumption records have different partition key values. This reduces the total amount of data with the same partition key value by splicing multiple primary key columns to form a partition key.
In the table containing the consumption records of students, two rows in which the value of the DeviceID column is the same have the same value in the SellerID column. If you splice only the DeviceID and SellerID columns into the partition key, the data with the same partition key value is still large in size. In this case, you can splice the DeviceID, SellerID, and CardID columns into the partition key.
As a result, a new issue arises. For example, the DeviceID column is a primary key column of the Integer type. In the original table, the row in which the value of the DeviceID column is 54 appears before the row in which the value of the DeviceID column is 167. If you splice the DeviceID, SellerID, and CardID columns into the partition key, the row in which the value of the DeviceID column is 54 appears after the row in which the value of the DeviceID column is 167. In this case, the new table does not meet the requirements when the application tries to read the rows in which the value of the DeviceID column is greater than or equal to 15 and less than 100.
To resolve this issue, convert the integers in the DeviceID column into strings whose length is fixed. If an integer has fewer digits than the fixed length, you can pad it with leading zeros based on the fixed length. The fixed length varies based on the maximum number of digits of the integers in the DeviceID column. For example, if the values in the DeviceID column range from 0 to 999999, pad the integers in the column with leading zeros until the number of digits of the integers reaches 6. Then, splice the DeviceID, SellerID, and CardID columns into the partition key. The following table describes the schema of the new table after splicing.
CombineDeviceIDSellerIDCardID
OrderNumber
attrs
'000016:a100:66661'
200001
...
'000054:a1001:6777'
200004
...
'000054:a100:6777'
200003
...
'000167:a101:283408'
200002
...
Another issue arises. For example, the row in which the value of the DeviceID column is 54 and the value of the SellerID column is 'a1001' appears after the row in which the value of the DeviceID column is 54 and the value of the SellerID column is 'a100'. This discrepancy is caused by the
:connector, which influences the lexicographical order. As a result, '000054:a1001' is lexicographically less than '000054:a100:', but 'a1001' is lexicographically greater than 'a100'.We recommend that you select a connector with an ASCII code lower than that of all other available characters. In the table, each value in the SellerID column consists of letters and digits. Based on the analysis,
,has an ASCII code lower than all available characters in the values of the SellerID column. Therefore, you can use,as the connector.The following table describes the schema of the new table after you use
,as the connector.CombineDeviceiDSellerIDCardID
OrderNumber
attrs
'000016,a100,66661'
200001
...
'000054,a100,6777'
200003
...
'000054,a1001,6777'
200004
...
'000167,a101,283408'
200002
...
The rows in the preceding table are sorted in the same order as those in the original table.
If you use a primary key column with sequentially increasing values as the partition key, add hash prefixes to the partition key.
The OrderNumber column stores sequentially increasing values. When new consumption records are written, they always fall within the latest order number range, whereas consumption records with older order numbers remain unchanged. This creates an unbalanced write workload because writes concentrate on only the most recent partition. Consequently, the reserved read and write throughput is underutilized. Therefore, do not use the OrderNumber column as the partition key when you design the table.
If you need to use the OrderNumber column as the table's partition key, add hash prefixes to the partition key to ensure that the rows in which the value of the OrderNumber column is the same are randomly distributed in the table. This balances the access load across partitions.
The following table describes the schema of the table when you use the OrderNumber column as the partition key.
OrderNumber
DeviceID
SellerID
CardID
attrs
200001
16
'a100'
66661
...
200002
167
'a101'
283408
...
200003
54
'a100'
6777
...
200004
54
'a1001'
6777
...
200005
66
'b304'
178994
...
Use a hashing algorithm, such as the MD5 algorithm, to calculate and add hash prefixes to the values in the OrderNumber column to form the partition key. Hash prefixes calculated by using the MD5 algorithm may be excessively long. You can take the first few characters to ensure that the rows in which the value of the OrderNumber column is the same are randomly distributed in the table.
In this example, the first four characters in hash prefixes are used. The following table describes the schema of the table when hash prefixes are added to the values in the OrderNumber column to form the partition key.
HashOrderNumber
DeviceID
SellerID
CardID
attrs
'2e38200004'
54
'a1001'
6777
...
'a5a9200003'
54
'a100'
6777
...
'c335200005'
66
'b304'
178994
...
'db6e200002
167
'a101'
283408
...
'ddba200001'
16
'a100'
66661
...
When you subsequently access the consumption records, use the same algorithm to calculate hash prefixes for the values in the OrderNumber column to obtain the partition key of each consumption record. However, you can no longer call the GetRange operation to read logically contiguous data whose primary key value is within a specific range.
Separately store cold and hot data based on data timeliness.
Applications need to process and collect statistics on the consumption records or query the latest consumption records in time. The latest consumption records in the table are more likely to be accessed. Previous consumption records are rarely queried and eventually becomes cold data. The student ID cards of students who graduated no longer generate consumption records.
The values in the CardID column increase based on the application date of the card IDs. If you use the CardID column as the partition key, reserved read and write throughput is allocated to the rows in which the value of the CardID column is no longer accessed because the students have graduated. This causes resource waste. In this case, you can divide the consumption records into tables by month and use a new table for each new calendar month. You can perform the following operations based on data timeliness:
Consumption record table of the current month: New consumption records are continuously written to the table, and consumption records are queried. You can allocate a large reserved read and write throughput for the table.
Consumption record tables in the previous months: No or few new data is written to the tables, whereas many query requests are initiated to query the consumption records. You can allocate a smaller reserved write throughput and a larger reserved read throughput for the tables.
Consumption record tables generated for more than one year: The tables are rarely accessed. You can specify a smaller reserved read and write throughput for the tables.
Consumption record tables that are no longer maintained: The tables are no longer accessed. You can export the data to Object Storage Service (OSS) for archiving or delete the data.