WO2021207830A1

WO2021207830A1 - Method and systems for indexing databases based on states and state transitions

Info

Publication number: WO2021207830A1
Application number: PCT/CA2021/050486
Authority: WO
Inventors: Marc-Antoine ROSS; Alexandre BOURGET
Original assignee: Dfuse Platform Inc.
Priority date: 2020-04-14
Filing date: 2021-04-12
Publication date: 2021-10-21

Abstract

In at least one embodiment, the present disclosure provides methods and systems for indexing a database comprising the steps of identifying, with a computing device, a state change of a key value pair stored in a table stored in the database, the at least one table including at least one data entry row, the at least one data entry row including the at least one key/value pair, assigning, with the computing device, a unique identifier associated with the state change, storing, in a database in electronic communication with the computing device, the unique identifier in a corresponding map associated with the table, obtaining, with a computing device, a snapshot of the database, and storing, with the computing device, the snapshot in a database in electronic communication with the computing device.

Description

METHOD AND SYSTEMS FOR INDEXING DATABASES BASED ON STATES AND

STATE TRANSITIONS

Field

The present invention relates to database architectures for networked systems. More specifically, the present disclosure relates to methods and systems for efficiently searching the historical state of a database in a resource efficient manner.

Background

Networks of electronically connected and remotely located computing devices form the backbone of many modem industries. It will be readily appreciated that such distributed networks of computing devices generate large amounts of data that must be stored in a reliable and accessible way.

Data can be stored in a conceptual “table” format, where each entry in the table represents a particular value for a key, or primary key for the data that is stored in that particular location of the table and by extension, the database at a particular point in time. Given the sheer volume of data that can be stored in a typical database, it will readily be appreciated that it can be extremely computationally heavy to ascertain the value of each particular data entry, as the processor of a computing device will have to query, identify and obtain a value for data at each location of the table.

This task can be straightforward if a table has, for example, 40 individual rows, however this task scales up quickly when the table includes millions or even billions of individual entries in a grid format.

Presently, the problem of finding a value within the most recent database state is solved by creating a database “index”, which is a data structure that can be used to quickly locate data without having to search every row in a database table. A database index is a copy of selected database columnar entries (typically called a database “key”) that is maintained in an ordered or tree structure such that it can be efficiently searched in order to return a storage location from where the entire row can be retrieved. l In a related manner, maintaining a historical record of the state and content of a database is particularly resource intensive, as a copy of the database must be stored at regular intervals for future use. Moreover, once these copies are stored, they must be examined on an entry-by-entry basis in order to determine the value of each particular data entry in the database at the time in question.

Therefore, it would be useful to provide methods and systems for efficiently storing snapshots of a database at particular intervals, and identifying any state changes of the data entries in the database in order to quickly provide an indication of how the database has changed over time and to obtain the historical values of particular entries in the database at chosen points in time, even if those values were subsequently changed.

Accordingly, there is a need for methods and systems for efficiently cataloging and searching previous versions of a database and identifying any state changes of the data entries in the database in a resource efficient manner.

Brief Summary

It is contemplated that the present disclosure can provide methods and systems for efficiently cataloging and searching previous versions of a database and identifying any state changes of the data entries in the database in a resource efficient manner.

In at least one embodiment, the present disclosure provides methods and systems for indexing a database further comprising the steps of retrieving, from the central server with the computing device, the snapshot of the database, retrieving, from the central server with the computing device, the unique identifier associated with the database, retrieving, from the central server with the computing device, the state change associated with the unique identifier, and applying, with the computing device, the state change to the snapshot of the database.

Brief Description of the Figures

The present disclosure will be better understood in connection with the following figures, in which:

FIGURE 1 is an illustration of a suitable system for use in accordance with at least one embodiment of the present disclosure;

FIGURE 2 is an illustration of a suitable device for use in accordance with at least one embodiment of the present disclosure; and

FIGURE 3 is an illustration of a method in accordance with at least one embodiment of the present invention.

Detailed Description of the Embodiments

In the context of the present disclosure, it will be appreciated that a suitable “device”, “user device”, “server” and “central server” can include any computing device having appropriate user input means, processing means, electronic communication means (either wireless or wired), power supply means and electronic storage means that will readily appreciated by the appropriately skilled person.

Suitable examples of devices, user devices, servers or central servers in accordance with the present disclosure include, but are not limited to, suitable mobile devices, laptop computers, desktop terminals, smart, wearable and internet-of-things (IOT) devices and remotely located “cloud” servers. In the context of the present disclosure, a suitable “electronic communication network” can include any network of electronically connected computing devices including a local area network, wide area network, peer-to-peer network and internet or “cloud” computing infrastructure as required by the end user application of the present disclosure.

It will be appreciated that the present disclosure can be used in any suitable context or protocol, such as but not limited to, the Bitcoin™ , Ethereum™ , EOSIO or any other altcoin or cryptocurrency, distributed ledger or smart contract platform, based on graphs, DAGs, chains of blocks or similar technology, even so-called instant-finality chains (like Tendermint or HotStuff- based consensus chains), all of which will be readily appreciated by the skilled person.

In the context of the present disclosure, a suitable “database” and “electronic storage” can be any suitable means for storing electronic data in a writable, rewritable, retrievable and/or permanent manner, as will be readily understood by the skilled person.

In at least one embodiment, it is contemplated that a suitable database will be organized in a table format. In these embodiments, it is contemplated that each row in the table in the database can further be composed of a key and a value, and that each key is unique to that particular table.

It will be appreciated that as a database is updated, each particular value (and the corresponding key) that is stored in the table in the database could conceivably be updated from time to time.

In the context of the present disclosure, it will be appreciated that when the key/value is updated it constitutes a “state change”, which can alternately and analogously be referred to as a “change of state” or a “mutation”. Suitable state changes include, for example, but not limited to, when any row in a table is “mutated”, meaning that data is inserted, changed, or deleted in that particular row of the table under consideration.

In other words, in the context of the present disclosure a row and a key/value pair stored in that row experiences a change of state when it is updated. It is contemplated that state changes are written to suitable electronic storage, which in some embodiments can be append-only storage as will be readily appreciated by the skilled person. It is further contemplated that each state change that corresponds to an updated key/value pair can be assigned a unique identifier that can consist of, for example, information such as the identity of the table, the primary key and a location in time at which the state change occurred.

It is contemplated that this unique identifier information can be stored in fundamental rows that are pointed to by a suitable data structure.

For example, in at least one embodiment, it is contemplated that for each table, a corresponding map can be generated and stored that is an associative array of the keys for each row in each table stored in the database. In these embodiments, it is contemplated that the map can include the locations in time pointing to the specific time at which the corresponding value for that particular key experienced a state change.

In these embodiments, it is contemplated that each time there is a change of the state of a key/value pair (and by extension a row in a table stored in a database) the corresponding map of that particular table is updated to reflect the newly updated rows of the table.

For example, if a key is inserted into a row of the table under consideration, a pointer to that row can be added to the corresponding map. If, on the other hand, it is removed, this pointer can be removed from the corresponding map. If a key is changed, the pointer can be altered on the corresponding map to point to a new fundamental row in the table under consideration.

In some embodiments, it is contemplated that once a sufficient number of state changes have occurred to a table under consideration, a snapshot of the table at that point in time can be generated and stored.

For example, the determination of whether a sufficient number of state changes has occurred can depend on a number of factors, including the number of state changes that have occurred to the table under consideration, the size of the table under consideration, or the rate at which the changes have occurred to the table under consideration.

In the context of the present disclosure, a snapshot can be considered a list of pointers to fundamental rows where each row is one of the large number of mutations that ever existed during the lifetime of the database under consideration. It is contemplated that each pointer in the snapshot points or links to a separate section of the database, which in some embodiments is the index of the database. In at least one embodiment, it is further contemplated that each pointer is marked with the precise location in time of the last mutation of the particular table under consideration.

In at least one embodiment, it is contemplated that when retrieving the state of a key/value pair from a given table at a particular location in time, the closest previous snapshot of the table under consideration can be retrieved, and then mutations can be applied to that snapshot from that point forward, until the location in time reaches a point in time under current consideration, which may be the present time or a time between the present time and time of the closest previous snapshot of the table under consideration, as will be readily appreciated by the skilled person.

Next, it is contemplated that corresponding rows can be retrieved out of the large number of mutations that have occurred, given that the identity of the table, the keys that are entered into the rows of that table and the locations in time for each row are all available from the initially retrieved snapshot.

In this way, all mutations that have occurred subsequently to the initially retrieved snapshot can be applied to the table the location in time of interest is reached. Therefore, a view of a desired table at the location in time of interest can be provided, which can subsequently be searched using conventional means.

Turning to Figure 1, at least one embodiment of a system for use in connection with the present disclosure is illustrated. In this embodiment, a first user device 2, a storage device 4 and a second user device 8 are in electronic communication with one another by way of an electronic communication network.

In this embodiment first user device 2 and second user device 8 have visual display means and user interface means, as will be discussed herein. In this embodiment, storage device 4 is a remotely located server.

It is further contemplated that first user device 2, storage device 4 and second user device 8 are in electronic communication with each other through an electronic communication network 6 that is a wireless communication network operated through remote servers, also known as a cloud-based network, although other arrangements such as hard-wired local networks are also contemplated as discussed herein.

Turning to Figure 2, at least one embodiment of user device 2, 8 for use in connection with the present disclosure is illustrated. In this embodiment, user device 2, 8 includes a processor 3, a communication subsystem 5 and local data storage 7, data input means 9, and user detection means 11 all of which are electronically connected by way of a bus 13.

Turning to Figure 3, at least one embodiment of the present method is depicted, in this embodiment, the method starts 100 and a state change that occurs to a key value pair stored in a table that is stored in a database is identified 102. It is contemplated that the table includes at least one data entry row and the data entry row contains the key value pair, as will be readily understood by the skilled person.

Next, it is contemplated that a unique identifier associated with the identified state change is assigned 104. Next, the unique identifier is stored in a corresponding map 106 that is stored in a database in electronic communication with the computing device. In this embodiment, it is contemplated that the unique identifier is associated with the table, such that the table that experienced the state change can be readily identified when the unique identifier is retrieved.

Next, it is contemplated that a snapshot of the database is obtained 108. Finally, the snapshot can be stored in a suitable database 110 that is in electronic communication with the computing device.

In some embodiments, it is contemplated that the method can further include the step of retrieving the snapshot from the database 112. Next, is it contemplated that the unique identifier associated with the table of the database can be retrieved 114. Subsequently, the state change that is associated with the unique identifier can be retrieved 116, and the state change can be applied to the retrieved snapshot of the database 118.

In this way, it is contemplated that a database can be iterated through time by retrieving a snapshot of the database that is representative of the database at some time in the past, and the state changes that the database has subsequently experienced can be applied to the snapshot to represent the database at any time subsequent to the time when the snapshot was obtained. Moreover, it is contemplated that snapshots can be taken at regular intervals, such as, for example, as a predetermined number of state changes have occurred. In this way, the uesr can select the closest snapshot that represents the database at a time of interest, and this snapshot can be populated with all subsequent state changes that occurred after that snapshot was obtained.

The embodiments described herein are intended to be illustrative of the present compositions and methods and are not intended to limit the scope of the present disclosure. Various modifications and changes consistent with the description as a whole and which are readily apparent to the person of skill in the art are intended to be included. The appended claims should not be limited by the specific embodiments set forth in the examples but should be given the broadest interpretation consistent with the description as a whole.

Claims

WHAT IS CLAIMED IS:

1. A method for indexing a database comprising the steps of:

Identifying, with a computing device, a state change of a key value pair stored in a table stored in the database, the at least one table including at least one data entry row, the at least one data entry row including the at least one key/value pair;

Assigning, with the computing device, a unique identifier associated with the state change;

Storing, in a database in electronic communication with the computing device, the unique identifier in a corresponding map associated with the table;

Obtaining, with a computing device, a snapshot of the database; and

Storing, with the computing device, the snapshot in a database in electronic communication with the computing device.

2. The method of claim 1, further comprising the steps of:

Retrieving, from the central server with the computing device, the snapshot of the database; Retrieving, from the central server with the computing device, the unique identifier associated with the database;

Retrieving, from the central server with the computing device, the state change associated with the unique identifier; and

Applying, with the computing device, the state change to the snapshot of the database.

3. The method of claim 1 or claim 2, wherein the unique identifier further comprises at least one of: an identity of the table, the key and a location in time at which the state change occurred.

4. The method of any one of claims 1 to 3, further comprising the step of:

Determining, with the central server, that a state change of the at least one data entry row has occurred; and Updating, with the map to reflect that a state change of the at least one data entry row has occurred.

5. The method of claim 4 wherein the step of updating the map further comprises at least one of: replacing the key of the data entry row with a new key and deleting the key of the data entry row.

6. The method of any one of claims 1 to 5, further comprising the step of:

Tracking, with the central server, at least one of the number of state changes that have occurred and the rate at which the state changes have occurred; and

When at least one of the number of state changes that have occurred and the rate at which the state changes have occurred reaches a predetermined threshold;

Obtaining, with the computing device, a snapshot of the database.

7. The method of any one of claims 1 to 6 wherein the snapshot further comprises a pointer to the database index, the database index containing a location in time that corresponds to a state change of a data entry row in the database.

8. A method for indexing a database comprising the steps of:

Obtaining, with a computing device, a snapshot of the database; and

9. The method of claim 1, further comprising the steps of: