Configuring replication across shards of scalable database tables for improved query performance
Replication of a client-managed table may be configured across shards of system-managed tables in a database system for improved query performance. A client-managed table may be identified to replicate as a complete copy of the table respectively collocated with two or more shards of one or more other system-managed tables. The complete copy may be stored in respective storage volumes of the two or more shards of the one or more other system-managed tables. Metadata for performing access requests at a database system may be updated to identify the client-managed table as collocated with the two or more shards of the one or more other system-managed tables.
1 . A system, comprising:
at least one processor; and
a memory, storing program instructions that when executed by the at least one processor, cause the at least one processor to implement a database service, the database service configured to
identify a table out of a plurality of tables to replicate as a complete copy of the table respectively collocated with two or more shards of one or more other tables of the plurality of tables assigned to different, respective database nodes of the database service for performing access requests to different ones of the two or more shards, wherein a first one of the two or more shards stores different table data than a second one of the two or more shards;
store complete copies of the table in respective storage volumes for the two or more shards of the one or more other tables;
enable replication for the complete copy to replicate received changes to the table to the complete copies stored in the respective storage volumes for the two or more shards of the one or more other tables;
update metadata for performing access requests at the database system to identify the table as collocated with the two or more shards of the one or more other tables when planning performance of access requests with regard to the different, respective database nodes to access the different ones of the two or more shards; and
based, at least in part, on the updated metadata, direct an access request for both at least one shard of the two or more shards of the one or more other tables and the table to a query engine assigned to the respective storage volume that stores both the at least one shard and the complete copy of the table.
2 . The system of claim 1 , wherein to identify the table out of the plurality of tables to replicate as the complete copy of the table respectively collocated with the two or more shards of the one or more other tables of the plurality of tables, the database service is configured to identify the table according to a request to replicate the table across shards of the one or more other tables.
3 . The system of claim 1 , wherein to identify the table out of the plurality of tables to replicate as the complete copy of the table respectively collocated with the two or more shards of the one or more other tables of the plurality of tables, the database service is configured to evaluate a history of access requests received at the database system to identify the table.
4 . The system of claim 1 , wherein the access request is directed to a database node that implements the query engine by a router of the database service.
5 . A method, comprising:
identifying, by a database system, a client-managed table out of a plurality of tables in a database to replicate as a complete copy of the table respectively collocated with two or more shards of one or more other system-managed tables of the plurality of tables assigned to different, respective database nodes of the database system for performing access requests to different ones of the two or more shards, wherein a first one of the two or more shards stores different table data than a second one of the two or more shards;
storing, by the database system, the complete copy of the client-managed table in respective storage volumes for the two or more shards of the one or more other tables;
updating, by the database system, metadata for performing access requests at the database system to identify the client-managed table as collocated with the two or more shards of the one or more other system-managed tables at the different, respective database nodes to access the different ones of the two or more shards; and
based, at least in part, on the updated metadata, directing an access request for both at least one shard of the two or more shards of the one or more other system-managed tables and the client-managed table to a query engine assigned to the respective storage volume that stores both the at least one shard and the complete copy of the client-managed table.
6 . The method of claim 5 , wherein identifying the client-managed table out of the plurality of tables in the database to replicate as the complete copy of the client-managed table respectively collocated with the two or more shards of the one or more other system-managed tables of the plurality of tables comprises identifying the client-managed table according to a request to replicate the client-managed table across shards of the one or more other system-managed tables.
7 . The method of claim 5 , wherein identifying the client-managed table out of the plurality of tables in the database to replicate as the complete copy of the client-managed table respectively collocated with the two or more shards of the one or more other system-managed tables of the plurality of tables comprises evaluating a history of access requests received at the database system to identify the client-managed table.
8 . The method of claim 5 , wherein identifying the client-managed table out of the plurality of tables to replicate as the complete copy of the client-managed table respectively collocated with the two or more shards of the one or more other system-managed tables of the plurality of tables comprises selecting the two or more shards out of a plurality of shards of the one or more other system-managed tables.
9 . The method of claim 5 , wherein the access request is directed to a database node that implements the query engine by a router of the database system.
10 . The method of claim 5 , further comprising updating, by the database system, the complete copy of the client-managed table in the respective storage volumes for the two or more shards of the one or more other system-managed tables to include one or more updates to the client-managed table.
11 . The method of claim 6 , wherein updating the complete copy of the client-managed table in the respective storage volumes for the two or more shards of the one or more other system-managed tables to include the one or more updates to the client-managed table comprises:
performing, by a database node, assigned to the client-managed table in the metadata, the one or more updates to the client-managed table, wherein respective requests for the one or more updates are sent to the database node by a router of the database system; and
sending, by the database node, requests to update the client-managed table to other database nodes assigned to the two or more shards of the one or more other system managed tables according to the one or more updates.
12 . The method of claim 5 , wherein one of the two or more shards is a new shard added for the one or more other system-managed tables and wherein the complete copy of the client-managed table is automatically stored in the respective storage volume for the new shard after the new shard is added.
13 . The method of claim 5 , wherein a further one or more shards of the one or more other system-managed tables does not have a respectively stored complete copy of the client-managed table collocated with the further one or more shards.
14 . One or more non-transitory, computer-readable storage media, storing program instructions that when executed on or across one or more computing devices cause the one or more computing devices to implement:
identifying, by a database system, a client-managed table out of a plurality of tables in a database to replicate as a complete copy of the client-managed table respectively collocated with two or more shards of one or more other system-managed tables of the plurality of tables assigned to different, respective database nodes of the database system for performing access requests to different ones of the two or more shards, wherein a first one of the two or more shards stores different table data than a second one of the two or more shards;
storing, by the database system, the complete copy of the client-managed table in respective storage volumes for the two or more shards of the one or more other system-managed tables;
updating, by the database system, metadata for performing access requests at the database system to identify the client-managed table as collocated with the two or more shards of the one or more other system-managed tables at the different, respective database nodes to access the different ones of the two or more shards; and
based, at least in part, on the updated metadata, directing an access request for both at least one shard of the two or more shards of the system managed table and the client-managed table to a query engine assigned to the respective storage volume that stores both the at least one shard and the complete copy of the client-managed table.
15 . The one or more non-transitory, computer-readable storage media of claim 14 , wherein, in identifying the client-managed table out of the plurality of tables to replicate as the complete copy of the client-managed table respectively collocated with the two or more shards of the one or more other system-managed tables of the plurality of tables, the program instructions cause the one or more computing devices to implement identifying the client-managed table according to a request to replicate the client-managed table across shards of the one or more other system-managed tables.
16 . The one or more non-transitory, computer-readable storage media of claim 14 , wherein, in identifying the client-managed table out of the plurality of tables to replicate as the complete copy of the client-managed table respectively collocated with the two or more shards of the one or more other system-managed tables of the plurality of tables, the program instructions cause the one or more computing devices to implement evaluating a history of access requests received at the database system to identify the client-managed table.
17 . The one or more non-transitory, computer-readable storage media of claim 14 , wherein the access request is directed to a database node that implements the query engine by a router of the database system.
18 . The one or more non-transitory, computer-readable storage media of claim 14 , storing further program instructions that when executed on or across the one or more computing devices, cause the one or more computing devices to further implement updating, by the database system, the complete copy of the client-managed table in the respective storage volumes for the two or more shards of the one or more other system-managed tables to include one or more updates to the client-managed table.
19 . The one or more non-transitory, computer-readable storage media of claim 14 , wherein, in identifying the client-managed table out of the plurality of tables to replicate as the complete copy of the client-managed table respectively collocated with the two or more shards of the one or more other system-managed tables of the plurality of tables, the program instructions cause the one or more computing devices to implement selecting the two or more shards out of a plurality of shards of the one or more other system-managed tables.
20 . The one or more non-transitory, computer-readable storage media of claim 14 , wherein a further one or more shards of the one or more other system-managed tables does not have a respectively stored complete copy of the client-managed table collocated with the further one or more shards.