This topic describes how to create and manage database objects in OBKV-HBase.
Notice
Before you perform operations on database objects, ensure that you have created a user tenant.
Database
An OBKV cluster is essentially an OceanBase AI Database cluster for a user tenant. A database is a collection of database objects used for privilege management and namespace-based isolation.
Before you create a data table, you must first create a database under the user tenant. For more information about how to create and manage databases, see the following topics:
Table group
A table group is a logical concept. It represents a collection of tables. By default, data is randomly distributed across different tables and the tables have no direct relationship. By defining a table group, you can control the physical closeness among a group of tables. When implementing the HBase model, OBKV-HBase maps an HBase table to a table group in OceanBase AI Database. Assume that HBase has a table named htable1. You need to create a table group named htable1 in OceanBase AI Database.
Notice
All tables in the same table group must use the same partitioning rules.
Before you create a data table, you must create the corresponding table group.
CREATE TABLEGROUP htable1;
For more information about table groups, see the following topics:
- About table groups
- Create a table group
- View table group information
- Add tables to a table group
- Modify the SHARDING attribute of a table group
- Manage tables within a table group
- Delete a table group
- CREATE TABLEGROUP
Tables
OBKV-HBase maps an HBase column family to a relational table in OceanBase AI Database. Before you use HBase, you must first create a table group and a table in the database.
Table identification markers
For OBKV-HBase tables, table identification markers serve the following two purposes:
- Identify the table type. If a table is an OBKV-HBase table, it can be identified as supporting the Automatic partition splitting in OBKV-HBase.
- Enable the Delete expired data in OBKV-HBase.
Notice
We strongly recommend adding kv_attributes settings when creating a table, so that the system can accurately identify the OBKV table type.
The kv_attributes of the OBKV-HBase type currently supports only two parameters: TimeToLive and MaxVersions. You can set either or both of them in any order, or set neither of them.
Here is an example:
-- Set TimeToLive and MaxVersions. Both parameters support only positive integer values.
CREATE TABLE t1$cf1 (
K VARBINARY(1024),
Q VARBINARY(256),
T BIGINT,
V VARBINARY(1048576) NOT NULL,
PRIMARY KEY(K, Q, T))
-- The '{"HBase": {xxx}}' format cannot be changed. HBase, TimeToLive, and MaxVersions are case-insensitive.
kv_attributes ='{"HBase": {"TimeToLive": 3600, "MaxVersions": 3}}'
PARTITION BY KEY(K) PARTITIONS 97;
-- Set only MaxVersions.
CREATE TABLE t1$cf1 (
K VARBINARY(1024),
Q VARBINARY(256),
T BIGINT,
V VARBINARY(1048576) NOT NULL,
PRIMARY KEY(K, Q, T))
kv_attributes ='{"HBase": {"MaxVersions": 3}}'
PARTITION BY KEY(K) PARTITIONS 97;
-- kv_attributes is empty.
CREATE TABLE t1$cf1 (
K VARBINARY(1024),
Q VARBINARY(256),
T BIGINT,
V VARBINARY(1048576) NOT NULL,
PRIMARY KEY(K, Q, T))
kv_attributes ='{"HBase": {}}'
PARTITION BY KEY(K) PARTITIONS 97;
Regular tables
A regular table is the most basic data model of OBKV-HBase. Suppose that HBase has a table named htable1 with a column family named family1. In OceanBase AI Database, you need to create a table group named htable1 and a regular table named htable1$family1. A regular table is named in the format of TableGroupName$FamilyName and is bound to the htable1 table group.
Notice
The OBKV-HBase Put, Delete, Get, and Scan interfaces support multiple column families. For examples, see Data operation examples.
CREATE TABLEGROUP htable1;
CREATE TABLE htable1$family1 (
K varbinary(1024),
Q varbinary(256),
T bigint,
V varbinary(1048576) NOT NULL,
PRIMARY KEY(K, Q, T))
TABLEGROUP = htable1
PARTITION BY KEY(K) PARTITIONS 97;
CREATE TABLE htable1$family2 (
K varbinary(1024),
Q varbinary(256),
T bigint,
V varbinary(1048576) NOT NULL,
PRIMARY KEY(K, Q, T))
TABLEGROUP = htable1
PARTITION BY KEY(K) PARTITIONS 97;
Although OBKV-HBase is compatible with the schemaless feature of HBase, any model implemented based on OceanBase AI Database has a schema. The preceding table creation statement defines the physical storage model of OBKV-HBase.
The parameters are described as follows:
htable1specifies the name of the HBase table. You can use a custom name.family1andfamily2specify the names of the column families. You can use custom names.Note
The table name and column family names are joined by using a dollar sign ($) to form the table name in OceanBase AI Database.
- The K column stores the rowkey of the HBase table.
- The Q column stores the column qualifier.
- The T column stores the timestamp version, in milliseconds since 1970-01-01 UTC.
- The V column stores the value of the varbinary type, with a maximum length of 1 MB. If the length is insufficient, the longblob type can be used, but this results in a performance loss of about 10%.
- K, Q, and T together form a composite primary key that identifies a cell in the HBase model.
Notice
The column names are fixed to K, Q, T, and V, and cannot be modified.
In the relational table created by using the preceding statement, data of multiple columns in a row of an HBase table is stored in adjacent rows. Each row actually stores a cell of the HBase table, which is a <row, column family, column qualifier, timestamp, value> tuple.
Time-series tables
OBKV-HBase supports the storage of time-series tables, which apply to time-series scenarios. The details are as follows:
- A time-series table consists of the K, T, S, V, and G columns. Column names cannot be changed.
- It supports primary and secondary partitioning. The subpartitioning strategy is the same as that of a regular table.
Description and limitations of time-series tables
The description and limitations of time-series tables are as follows:
- Distributed: The distributed mode must be enabled for a time-series table, that is,
DISTRIBUTED = true. Otherwise, the-4007 OB_NOT_SUPPORTEDerror is returned. - KV_ATTRIBUTES: The
TimeToLiveandMaxVersionsparameters in KV_ATTRIBUTES cannot be set. - Cell-level TTL: Time-series tables do not support cell-level TTL. If a table is created with KV_ATTRIBUTES and a cell TTL column is detected, an error is returned immediately. If KV_ATTRIBUTES are not specified, using cell-level TTL in DML operations also results in an error. A cell TTL column added by using
ALTER TABLE ADD COLUMNdoes not take effect, and subsequent DML operations report that it is not supported.
The current interface support and limitations are as follows (see the overview table below first; the detailed list is authoritative):
The overview of time-series table interfaces is as follows:
Category |
Description |
|---|---|
| Supported read and write paths | put, put (List in batches), get, scan, getScanner, batch (batchPut only), and backCallBack (backCallBackPut only) |
| Unsupported read and write paths | delete (including List in batches), the exists series (including batch and all semantics), append, increment, incrementColumnValue, and mutateRow |
| Unsupported conditional writes | checkAndPut (1.0) / thenPut (2.0), checkAndDelete (1.0) / thenDelete (2.0), and checkAndMutate (1.0) / thenMutate (2.0) |
The commonly used capabilities of get and scan are compared as follows:
Capability |
get |
scan |
|---|---|---|
TimeRange |
Supported | Supported |
Other HBase Filter |
Not supported. The -4007 OB_NOT_SUPPORTED error is returned. |
Same as the get interface. |
| Multiple column families | Not supported | Not supported |
maxVersion |
Not supported | Not supported |
Rowkey TTL |
Not supported | Not supported |
setLimit |
— | Supported |
setCaching / setMaxResultSize |
— | Supported |
setBatch, setRowOffsetPerColumnFamily, setMaxResultsPerColumnFamily, setCacheBlocks, reverseScan |
— | Not supported |
Note
In the table, Other HBase Filter refers to any general filter other than those related to time ranges. TimeRange may be passed to the server through a channel related to filters, but this does not mean that any filter can be used.
The detailed limitations are as follows:
- Supported put interface:
- Setting the TTL is not supported. The
-4007 OB_NOT_SUPPORTEDerror is returned. - Multiple column families are not supported.
- The
put<list>interface is supported.
- Setting the TTL is not supported. The
- Supported get interface:
- Setting
TimeRangeis supported. Other HBaseFilterconditions (except those related to time ranges) are not supported. Otherwise, the-4007 OB_NOT_SUPPORTEDerror is returned. - Multiple column families are not supported.
- Setting
maxVersionis not supported. isCheckExistenceOnlyis not supported.isClosestRowBeforeis not supported.Rowkey TTLis not supported.- The
get<list>interface is not supported.
- Setting
- Supported scan interface:
- Setting
TimeRangeis supported. Other HBaseFilterconditions (except those related to time ranges) are not supported. Otherwise, the-4007 OB_NOT_SUPPORTEDerror is returned. - Setting
setLimit,setCaching, andsetMaxResultSizeis supported. - Multiple column families are not supported.
- Setting
maxVersionis not supported. setBatch,setRowOffsetPerColumnFamily,setMaxResultsPerColumnFamily,setCacheBlocks, andreverseScanare not supported.Rowkey TTLis not supported.
- Setting
- Supported batch interface:
- The
batchPutinterface is supported. - The
batchDeleteinterface is not supported.
- The
- Supported backCallBack interface:
- The
backCallBackPutinterface is supported. - The
backCallBackDeleteinterface is not supported.
- The
- Supported getScanner interface:
- Whether an individual parameter is supported is subject to the limitations of the scan interface.
- The
deleteanddelete<list>interfaces are not supported. - The
exists,exists<list>, andexists<list>Allinterfaces are not supported. - The
checkAndPut(1.0) andthenPut(2.0) interfaces are not supported. - The
checkAndDelete(1.0) andthenDelete(2.0) interfaces are not supported. - The
checkAndMutate(1.0) andthenMutate(2.0) interfaces are not supported. - The
appendinterface is not supported. - The
incrementinterface is not supported. - The
incrementColumnValueinterface is not supported. - The
mutateRowinterface is not supported.
Create a time-series table
CREATE TABLEGROUP htable1;
CREATE TABLE `htable1$family1` (
`K` varbinary(1024) NOT NULL,
`T` bigint(20) NOT NULL,
`S` bigint(20) NOT NULL,
`V` json NOT NULL,
`G` bigint(20) GENERATED ALWAYS AS (ABS(T)),
PRIMARY KEY (`K`, `T`, `S`)
) PARTITION BY RANGE COLUMNS(`G`) SUBPARTITION BY KEY(`K`) SUBPARTITIONS 97 (
PARTITION `p0` VALUES LESS THAN (1728921600000),
PARTITION `p1` VALUES LESS THAN (1729008000000),
PARTITION `p2` VALUES LESS THAN (1729094400000),
PARTITION `p3` VALUES LESS THAN MAXVALUE
);
CREATE TABLE `htable1$family2` (
`K` varbinary(1024) NOT NULL,
`T` bigint(20) NOT NULL,
`S` bigint(20) NOT NULL,
`V` json NOT NULL,
`G` bigint(20) GENERATED ALWAYS AS (ABS(T)),
PRIMARY KEY (`K`, `T`, `S`)
) PARTITION BY RANGE COLUMNS(`G`) SUBPARTITION BY KEY(`K`) SUBPARTITIONS 97 (
PARTITION `p0` VALUES LESS THAN (1728921600000),
PARTITION `p1` VALUES LESS THAN (1729008000000),
PARTITION `p2` VALUES LESS THAN (1729094400000),
PARTITION `p3` VALUES LESS THAN MAXVALUE
);
The parameters are described as follows:
- The K column stores the rowkey of the HBase table and can be used as the partitioning key.
- The T column stores the timestamp version, in milliseconds since 1970-01-01 UTC.
- The S column stores the sequence number of multiple different writes that correspond to the same K and T (timestamp). Typically, this sequence number is the timestamp of the write operation. You do not need to specify the S column when you write data. It is automatically generated by the system.
- The V column encodes multiple Q (qualifier) and V (value) pairs into JSON.
- The G column is a virtual generated column that stores the absolute value (ABS) of T (timestamp). It can be used as the partitioning key for the time dimension.
- K, T, and S form a composite primary key.
Notice
The column names must follow the K, T, S, V, and G order and cannot be modified.
LOB
HBase is a KV database in which user data is serialized into binary values. As a result, the amount of data in HBase can be very large. OceanBase AI Database supports large objects (LOBs) no larger than 512 MB.
As we all know, the DML performance of LOBs is low, so avoid using LOBs whenever possible. Normally, we recommend that you use the varbinary type for the V column, with a maximum length of 1 MB, when you define the schema. If operations on LOBs larger than 1 MB are unavoidable in your actual scenario, you can set the V column to the longblob type.
create table htable1$family1 (
K varbinary(1024),
Q varbinary(256),
T bigint,
V longblob NOT NULL,
primary key(K, Q, T))
TABLEGROUP = htable1
partition by key(K) partitions 97;
Indexes
After a table is created, you can create indexes on one or more columns of the table to accelerate statement execution. Well-designed indexes can reduce physical or logical I/O operations.
OceanBase AI Database supports global indexes, local indexes, unique indexes, and non-unique indexes.
Notice
At present, OBKV-HBase does not support secondary indexes.
For more information about indexes, see the following topics:
- Indexes
- Create an index
- Create an index on a partitioned table
- View indexes
- Monitor indexes
- Drop an index
Partitioning
The preceding table creation procedure does not involve partitioning. The created table is a non-partitioned table and applies only to scenarios with a very small amount of data. Generally, HBase business tables contain large amounts of data. Therefore, you need to create a partitioned table. OBKV-HBase supports subpartitioning and multiple partitioning strategies. Currently, RANGE partitioning and KEY partitioning are supported.
A primary partition can use only one data distribution method. For example, it can use only RANGE partitioning.
In subpartitioning, a table is first partitioned by using one data distribution method and then each partition is further divided into subpartitions by using a second data distribution method. The following subpartitioning strategies are supported:
- Perform primary RANGE partitioning based on the T column and then secondary KEY partitioning based on the K column.
- Perform primary KEY partitioning based on the K column and then secondary RANGE partitioning based on the T column.
Currently, only RANGE and RANGE COLUMNS partitioning of primary partitions supports automatic partition splitting. This partitioning method applies to range scan scenarios. It also supports automatic splitting based on data volume distribution, which regular RANGE partitioning does not support, ensuring even data distribution. For more information, see Automatic partition splitting in OBKV-HBase. Automatic splitting of subpartitions is not supported.
For more information about partitioning, see Partition types.
Notice
All tables in the same table group must use the same partitioning rules.
Primary partitioning strategies
If you choose to use primary partitioning, refer to the following sections to determine which partitioning method to use.
KEY partitioning
If your business does not use the Scan interface for range scans, or if you only need the Get interface, use KEY partitioning. The number of partitions must be odd, preferably a prime number. Recommended values are 97, 193, and 389. KEY partitioning features even data distribution and generally prevents data skew and hotspots. Here is an example:
CREATE TABLE htable1$family1 (
K varbinary(1024),
Q varbinary(256),
T bigint,
V varbinary(1048576) NOT NULL,
PRIMARY KEY(K, Q, T))
TABLEGROUP = htable1
PARTITION BY KEY(K) PARTITIONS 97;
Virtual columns combined with KEY partitioning
If you must use scans in your business but the scan range is always a fixed-length prefix of the rowkey (prefix scan), you can define a virtual column on K and then apply KEY partitioning to this virtual column. This way, each scan involves only one partition. Unless the prefix data is skewed, this partitioning method also helps avoid hotspots. Here is an example:
CREATE TABLE htable1$family1 (
K varbinary(1024),
Q varbinary(256),
T bigint,
V varbinary(1048576),
K_PREFIX varbinary(1024) GENERATED ALWAYS AS (substring(K, 1, 4)),
PRIMARY KEY(K, Q, T))
TABLEGROUP = htable1
PARTITION BY KEY(K_PREFIX) PARTITIONS 97;
The substring(K, 1, 4) function takes the first four bytes of K (the rowkey of the HBase table) as a substring. Replace 4 with the prefix length that suits the characteristics of your rowkey.
Note
Currently, virtual columns can be created only by using substring.
RANGE partitioning
If you need both prefix scans and Get operations, you must use RANGE partitioning. We recommend that you first estimate or sample the distribution of rowkeys and then determine the range boundaries. If the estimation is inaccurate, RANGE partitioning can easily cause data skew and hotspots. Therefore, avoid RANGE partitioning whenever possible. Here is an example:
CREATE TABLE htable1$family1 (
K varbinary(1024),
Q varbinary(256),
T bigint,
V varbinary(1048576),
PRIMARY KEY(K, Q, T)
)
TABLEGROUP = htable1
PARTITION BY RANGE columns (K)
(
PARTITION p0 VALUES LESS THAN ('a'),
PARTITION p1 VALUES LESS THAN ('w'),
PARTITION p2 VALUES LESS THAN MAXVALUE);
The value range of the rowkeys is divided into three segments by 'a' and 'w'.
Subpartitioning strategies
OBKV-HBase supports subpartitioning strategies based on the K and T columns.
Considerations
The current limitations on subpartitioning in OBKV-HBase are as follows:
- The expired data deletion (TTL) feature does not support MaxVersions or rowkey TTL.
RANGE + KEY
OBKV-HBase supports a strategy that performs primary RANGE partitioning based on the T column and secondary KEY partitioning based on the K column. This strategy first distributes data to primary partitions based on the time dimension and then further scatters the data by K. Here is an example:
CREATE TABLE `htable1$cf1` (
`K` varbinary(1024) NOT NULL,
`Q` varbinary(256) NOT NULL,
`T` bigint(20) NOT NULL,
`V` varbinary(1024) DEFAULT NULL,
`G` bigint(20) GENERATED ALWAYS AS (ABS(T)),
PRIMARY KEY (`K`, `Q`, `T`)
) PARTITION BY RANGE COLUMNS(`G`) SUBPARTITION BY KEY(`K`) SUBPARTITIONS 97 (
PARTITION `p0` VALUES LESS THAN (1728921600000),
PARTITION `p1` VALUES LESS THAN (1729008000000),
PARTITION `p2` VALUES LESS THAN (1729094400000),
PARTITION `p3` VALUES LESS THAN MAXVALUE
);
CREATE TABLE `htable1$cf2` (
`K` varbinary(1024) NOT NULL,
`Q` varbinary(256) NOT NULL,
`T` bigint(20) NOT NULL,
`V` varbinary(1024) DEFAULT NULL,
`G` bigint(20) GENERATED ALWAYS AS (ABS(T)),
K_PREFIX varbinary(1024) generated always as (substring(`K`, 1, 4)),
PRIMARY KEY (`K`, `Q`, `T`)
) PARTITION BY RANGE COLUMNS(`G`) SUBPARTITION BY KEY(`K_PREFIX`) SUBPARTITIONS 97 (
PARTITION `p0` VALUES LESS THAN (1728921600000),
PARTITION `p1` VALUES LESS THAN (1729008000000),
PARTITION `p2` VALUES LESS THAN (1729094400000),
PARTITION `p3` VALUES LESS THAN MAXVALUE
);
KEY + RANGE
OBKV-HBase supports a strategy that performs primary KEY partitioning based on the K column and secondary RANGE partitioning based on the T column. This strategy first distributes data to primary partitions by K and then layers the data by time. Here is an example:
CREATE TABLE `htable1$cf3` (
`K` varbinary(1024) NOT NULL,
`Q` varbinary(256) NOT NULL,
`T` bigint(20) NOT NULL,
`V` varbinary(1024) DEFAULT NULL,
`G` bigint(20) GENERATED ALWAYS AS (ABS(T)),
PRIMARY KEY (`K`, `Q`, `T`)
) PARTITION BY KEY(`K`) SUBPARTITION BY RANGE COLUMNS(`G`) SUBPARTITION TEMPLATE (
SUBPARTITION `p0` VALUES LESS THAN (1728921600000),
SUBPARTITION `p1` VALUES LESS THAN (1729008000000),
SUBPARTITION `p2` VALUES LESS THAN (1729094400000),
SUBPARTITION `p3` VALUES LESS THAN MAXVALUE
) PARTITIONS 97;
CREATE TABLE `htable1$cf4` (
`K` varbinary(1024) NOT NULL,
`Q` varbinary(256) NOT NULL,
`T` bigint(20) NOT NULL,
`V` varbinary(1024) DEFAULT NULL,
`G` bigint(20) GENERATED ALWAYS AS (ABS(T)),
K_PREFIX varbinary(1024) generated always as (substring(`K`, 1, 4)),
PRIMARY KEY (`K`, `Q`, `T`)
) PARTITION BY KEY(`K_PREFIX`) SUBPARTITION BY RANGE COLUMNS(`G`) SUBPARTITION TEMPLATE (
SUBPARTITION `p0` VALUES LESS THAN (1728921600000),
SUBPARTITION `p1` VALUES LESS THAN (1729008000000),
SUBPARTITION `p2` VALUES LESS THAN (1729094400000),
SUBPARTITION `p3` VALUES LESS THAN MAXVALUE
) PARTITIONS 97;
Partition management
For more information about partition operations, see the following topics:
- Create a partitioned table
- Modify partitioning rules
- Add a partition
- Drop a partition
- TRUNCATE
- Partition pruning
Primary key design
Design of the KEY partitioning key
In OBKV-HBase, a well-designed partitioning key can effectively improve query performance. As we all know, in a distributed system, extra performance costs are required to ensure the atomicity, consistency, isolation, and durability (ACID) of distributed transactions and the snapshot consistency of distributed reads. This is why many open-source NoSQL databases do not support cross-row transactions or provide globally consistent snapshot reads.
For performance purposes, OBKV-HBase does not support distributed transactions or globally consistent snapshot reads. However, it supports node-level transactions (whereas open-source HBase supports only single-row transactions) and node-level consistent snapshot reads. By designing the partitioning key, you can confine business requests to a single partition, which combines the performance and consistency of a standalone cluster with scalability.
The partitioning key is the prefix of the primary key. For example, in a user message system:
- Each user is identified by a unique 64-bit user ID,
userid. - Each message is identified by a 64-bit message ID,
msgid.
The primary key is userid | msgid, and the partitioning key is defined as follows:
CREATE TABLE htable1$family1 (
K varbinary(1024),
Q varbinary(256),
T bigint,
V varbinary(1048576),
K_PREFIX varbinary(1024) GENERATED ALWAYS AS (substring(K, 1, 64)),
PRIMARY KEY(K, Q, T))
TABLEGROUP = htable1
PARTITION BY KEY(K_PREFIX) PARTITIONS 97;
As shown in the preceding definition, the substring function extracts a fixed-length partitioning key from the part of the primary key in the K column. All scan, get, put, and batch operations of the same userid access the same partition. Within a single partition, OBKV-HBase guarantees cross-row transactions and consistent reads.
Considerations for the number of KEY partitions
Why you should pay attention to the number of partitions
- Adjusting the number of partitions is a DDL operation, which incurs certain overhead.
- An excessively small number is inappropriate. The balancing mechanism of a database ensures that the difference in partition quantities between two nodes does not exceed 1. Assume that the database has three nodes and seven partitions. The partition distribution on the three nodes is <3, 2, 2>, which means a partition imbalance degree of (3-2)/3 = 33%. If no data skew exists, the number of partitions is almost directly proportional to the stored data amount and the accessed data amount. Therefore, partition imbalance leads to unbalanced resource usage among the nodes.
- An excessively large number is also inappropriate. A partition is similar to a miniDB, which means that a database consists of many miniDBs. Each miniDB independently manages its own storage space and data access. A large number of miniDBs lead to insufficient sharing of the storage space. A scan may span multiple miniDBs, causing performance loss. Batch business operations cannot be directly pushed down to the storage for performance optimization.
Impact of the number of partitions on performance
- Generally, the partition size does not obviously affect performance.
- Partition-level tasks, such as major compactions, migration, and backup, are mainly affected. If partitions are too large, the execution time of a single task increases and more storage space is required.
How to determine the number of partitions
- If capacity is a concern, we recommend that the data volume in a single partition of a single replica do not exceed 100 GB. When you determine the number of partitions, we recommend that you select a prime number such as 23, 59, 97, 193, 389, or 997. For example, for three replicas that occupy 24 TB of storage space, 8 TB is occupied by each replica. It can be estimated that at least 80 partitions are required. Based on the preceding recommended values, select 97.
- Adjusting the number of partitions is a DDL operation with certain overhead. When you design the number of partitions, plan in advance to avoid frequent adjustments. Assume that the current storage space is 24 TB but is expected to scale out to 50 TB within a year. You can design the number of partitions based on 50 TB and preferably specify 193 partitions.
- The number of partitions cannot be excessively large. We recommend that the number not exceed 997.
How to determine data skew among partitions
KEY partitioning currently uses MurmurHash. It hashes data by the partitioning key and distributes the data to the corresponding partition. Data skew occurs only in extreme cases. For example, if 10 million of 100 million records have the same partitioning key, severe data skew occurs. If there are 100 million records and only a few thousand records under each partitioning key, data skew generally does not occur, because the hashed partitioning keys are quite random when the sample is large enough. From a global perspective, the data across partitions should be relatively balanced.
Common considerations for primary key design
Scattering the primary key
In a distributed system, scattering the primary key is essential. If you can evenly distribute requests to all servers in the cluster, you can make full use of the computing capabilities of the entire distributed system.
- If you use RANGE partitioning, we recommend that you do not use an auto-increment column or a time column as the prefix of the primary key. For example, if the primary key of a message system is designed as
timestamp | msgid, almost all write requests are sent to one server. - We recommend that you do not use a prefix with a large cardinality as the prefix of the primary key. For example, if the primary key of a message system is designed as
msgtype | msgid, almost all read and write requests are sent to one server becausemsgtypehas only a few enumeration values.
Primary key design
- We recommend that the primary key do not exceed 1 KB in length. An excessively long primary key causes redundant storage and degrades access performance. Typically, the primary key cannot be of a LOB type.
- If RANGE partitioning is used and the prefix of the primary key must be an auto-increment column or a time column, data access hotspots may occur. In this case, you can hash the auto-increment column and use the hashed value as the new prefix of the primary key. For example, you can change a non-optimal primary key
timestamp | msgidtohashvalueoftime | timestamp | msgid. In KEY partitioning, the partitioning key values are hashed and scattered to partitions, so you do not need to hash the prefix explicitly.
References
- For more information about database objects in OceanBase AI Database, see Database objects.
- For information about how to manage database objects in OceanBase AI Database, see Overview of database object management.