In a distributed database, data in multiple tables can be distributed across servers. In this case, cross-server communication is required when you perform complex operations such as JOIN queries or cross-table transactions. Table groups can help avoid cross-server operations to improve database performance. This topic describes the features and operating principles of table groups.
As a distributed database, OceanBase Database supports partitioning to store data of a table into multiple partitions, which meets the requirements for scalability and multi-point writing. The column used for data partitioning in a table is called a partition key. You can hash a row of data based on its partition key value to locate the partition to which the data belongs. Data can be partitioned by using multiple methods. Hash partitioning is used as an example herein.
You can aggregate tables partitioned by using the same method into a table group. For example, hash partitioned tables have the same number of partitions, and the same hash algorithm is used to calculate partitions. Partitions with the same IDs of tables in a table group are called a partition group, as shown in the following figure.
When you create partitions or during load balancing, OceanBase Database deploys partitions in one partition group to one server. This can avoid cross-server operations in cross-table data access, provided that the accessed data belongs to partitions in the same partition group. How can OceanBase Database ensure that the accessed data belongs to the same partition group? OceanBase Database does not interfere with your operations, but ensures that most cross-table data involved in some operations belongs to the same partition group based on business characteristics.
Use a student table and a class table as examples. Both tables contain the class id column. The class id column can serve as the partition key. Data of the same class in the two tables is aggregated into one partition. This way, when you initiate a JOIN operation on the class id column, the operation is performed only on the server on which the partition is located, instead of searching for data corresponding to a specific class id in all partitions across servers. In a distributed write transaction, if you want to add a piece of student information, you must add a data entry to the student table and update the total number of students in the class table. The two data records belong to different tables and therefore belong to different partitions. Therefore, you must initiate a distributed write transaction for consistent updates. The two partitions are located on the same OBServer. Therefore, OceanBase Database can optimize distributed transactions on the same OBServer, which makes them more efficient than cross-server distributed transactions.
Only tables partitioned by using the same method can be aggregated into a table group. Therefore, tables in a table group do not support partitioning that violates the partitioning rules. However, you can partition a table group to change the partitions of all tables in the group.
In the current design of table groups, one partition corresponds to one log stream. The log stream synchronizes data changes from the leader to followers based on the Paxos algorithm. If a transaction involves multiple partitions, multiple log streams must be written. In this case, a distributed transaction is required to ensure consistent operations. Standalone distributed transactions are optimized compared with cross-server transactions. However, OceanBase Database supports binding of table groups for further optimization. Partitions in a partition group are deployed on one server, and partition changes are also written to one log stream. This way, a cross-table write transaction for a partition group can be atomically committed in one log stream. This converts a distributed transaction into a standalone transaction and improves the transaction efficiency. Because one partition group corresponds to one log stream, it is difficult to delete tables from table groups. OceanBase Database does not allow deletion of tables from bound table groups. You can use table groups based on the characteristics of your business.
To sum up, in typical business scenarios, table groups in OceanBase Database optimize distributed queries and transactions, but also impose some limitations. For example, you cannot change the partitions of a specific table in a table group. On this basis, OceanBase Database supports binding of table groups, which improves the performance in typical distributed transaction scenarios but also imposes some limitations. For example, you cannot delete tables from bound table groups.