US 12,093,248 B1
Online index creation for multi-version database without table locks
Cristian Diaconu, Kirkland, WA (US); Nikolaos Romanos Katsipoulakis, Redwood City, CA (US); Alexander Miller, San Mateo, CA (US); Dimitrios Tsirogiannis, Belmont, CA (US); William Waddington, Stateline, NV (US); and Zhaohui Zhang, Redwood City, CA (US)
Assigned to Snowflake Inc., Bozeman, MT (US)
Filed by Snowflake Inc., Bozeman, MT (US)
Filed on May 8, 2023, as Appl. No. 18/313,768.
Int. Cl. G06F 16/23 (2019.01); G06F 16/22 (2019.01)
CPC G06F 16/2379 (2019.01) [G06F 16/22 (2019.01); G06F 16/2365 (2019.01)] 18 Claims
OG exemplary drawing
 
1. A system comprising:
at least one hardware processor; and
a memory storing instructions that cause the at least one hardware processor to perform operations comprising:
receiving a statement to initiate an online index building process of an index of a hybrid table from a set of hybrid tables, the set of hybrid tables being stored in a distributed data store provided by a networked database system;
initiating the online index building process of the index in response to receiving the statement;
determining, during the online index building process, that the index can be generated;
performing, during the online index building process, a write operation to store an index record indicating an initial status of the online index building process, the index record being stored in a metadata database provided by the networked database system, the metadata database being different than the distributed data store;
performing, during the online index building process, a statement fencing process;
performing a back-filling process of the index, the back-filling process including, in the index, a new index record for each record from the hybrid table;
validating the index, the validating comprising:
retrieving, by a compute service manager, a first metadata version of the hybrid table from the metadata database;
sending, by the compute service manager, the first metadata version of the hybrid table to an execution node;
retrieving, by the execution node, a second metadata version of the hybrid table from the distributed data store;
comparing, by the execution node, the first metadata version with the second metadata version; and
determining, by the execution node, that the second metadata version is less than or equal to the first metadata version; and
in response to successfully validating the index, indicating in a particular entry of the index that validation of the index has succeeded.