Overview
Synchronous tables (sync table) are one of the means for TDSQL Boundless to enhance performance through multiple replicas. Unlike ordinary tables, sync tables create a replica on every node in the cluster, and each replica can provide strong consistency read services. This enables the capabilities of multiple replicas to be effectively utilized, significantly increasing the read throughput of sync tables.
Applicable Scenarios
Synchronous tables require that modifications are strongly synchronized by the Leader to all replicas and only return to the client after all replicas are updated. In contrast, write requests for ordinary tables can return once a majority of member replicas are reached. Compared to ordinary tables, the write capability of synchronous tables is subject to certain limitations. Therefore, synchronous tables are suitable for read-intensive and write-light business scenarios.
Use Limits
Currently, synchronous tables provide services in the form of broadcast tables. Users must create broadcast synchronous tables to utilize synchronous tables.
If any Follower replica fails, it may cause write requests to stall for the duration of a lease.
The read request routing policy is local-first; if local access fails, the request is routed to the Leader.
Currently, changing the attributes of synchronous tables is not supported, meaning it is not supported to convert a synchronous table to a non-synchronous table, nor to convert a non-synchronous table to a synchronous table.
Synchronous tables cannot be partitioned tables.
Broadcast Sync Log Streams will not be destroyed once created, even if users delete all synchronous tables.
An excessive number of replicas will degrade the performance of write requests.
Syntax and Example
Syntax
CREATE TABLE table_name (
column_definitions
) sync_level = node(all) distribution = node(all);
Synchronous tables maintain the same read and write access syntax as ordinary tables. However, during read operations, the specific node accessed depends on the connection. Specifically, TDSQL Boundless prioritizes accessing the synchronous table replica on the local node where the connection resides. Only when the local node is unreadable (such as lease expiration) does it fall back to accessing the Leader node.
Example
Take creating a user table as an example.
CREATE TABLE `user_center`.`user_profiles` (
`user_uuid` varchar(36) NOT NULL,
`nick_name` varchar(50),
`register_time` datetime,
PRIMARY KEY (`user_uuid`)
) SYNC_LEVEL = NODE(ALL) DISTRIBUTION = NODE(ALL);
Frequently Asked Questions (FAQ)
Q: What Do "SYNC_LEVEL = NODE(ALL)" and "DISTRIBUTION = NODE(ALL)" Represent Respectively in the Table Creation Syntax?
A: SYNC_LEVEL = NODE(ALL) indicates the synchronization level, where modifications must be synchronized to all nodes; DISTRIBUTION = NODE(ALL) indicates the distribution rule, requiring a replica to be distributed on each node.
Q: Can We Specify Part Instead of ALL?
A: Currently, specifying replicas is not supported; only ALL is supported.
Q: Can a synchronous table be created without specifying DISTRIBUTION = NODE(ALL)?
A: Currently not supported; synchronous tables can only create one replica on all nodes.