Online asynchronous schema change for row-oriented and column-oriented databases

Description

[0001] Online Asynchronous Schema Change for Row-Oriented and Column-Oriented Databases

[0002] First, since all data must be as available as possible, we do not restrict access to data undergoing reorganization. Second, because the schema change must have minimal impact on user transactions, we allow transactions to span an arbitrary number of schema changes and finally, we apply schema changes asynchronously on individual database servers or shards implying that multiple versions of the schema may be in use simultaneously. Here we consider only changes to the logical schema, such as the addition or removal of columns, but not changes to the physical schema like adding or removing secondary indexes. Further we ensure that there is no more than two schema versions in use at any given time, and that those schema versions have their corresponding properties (depending upon old or new desired schema version), our protocol enables distributed schema changes in a way that does not require global membership, implicit or explicit synchronization between nodes, or the need to retain old schema versions once a schema change is complete. Here in this invention, for the table undergoing a logical schema change we assign a dedicated persistent keyvalue store like RocksDB, etc. to store new insert / update / delete data for user transactions, wherein the key is the primary key for the table and value is the list of objects wherein an object consists of the operation performed (Update / Insert / Delete) along with the change vector for that row. The above said key-value store is actively used while the database transitions from Schema 1 or old Schema to Schema 2 or new Schema. For row-oriented databases for the transition to new Schema or Schema 2 we use background Map-Reduce jobs which partitions the table and assigns partitions to map tasks. The map tasks scan all rows in their corresponding assigned table partitions at a snapshot timestamp corresponding to the start of the canonical schema change, and updates each row to conform to the new schema. The Reduce task then performs aggregation and summary operation on the results of the map tasks. For column-oriented databases the transition to new Schema or Schema 2 is decoupled since adding a new column or deleting an existing column would involve allocating a new set of pages for the newly added column or deleting an existing set of pages for the deleted column. Also note for both row-oriented and column-oriented databases if there is a primary index on the table (or data layout is in the form of a primary B+ tree index like in open source MySQL), it is updated too as part of transition to new Schema or Schema 2. Finally when we are ready to switch to new Schema or Schema 2 we atomically change the canonical schema (from old Schema to new Schema) by acquiring a lock on the metadata table of the database server and replay or synchronise the local updates of the persistent key-value store to the new schema table. Also remember the Multi- Version Concurrency Control (MVCC) component of the database refers to the persistent key-value store for getting access to the latest row data of the table. When there are multiple database shards, the above is achieved or triggered with the help of a distributed transaction but the actual schema change is online and asynchronous.

Claims

Following are the claims for this invention1. First, since all data must be as available as possible, we do not restrict access to data undergoing reorganization. Second, because the schema change must have minimal impact on user transactions, we allow transactions to span an arbitrary number of schema changes and finally, we apply schema changes asynchronously on individual database servers or shards implying that multiple versions of the schema may be in use simultaneously. Here we consider only changes to the logical schema, such as the addition or removal of columns, but not changes to the physical schema like adding or removing secondary indexes. Further we ensure that there is no more than two schema versions in use at any given time, and that those schema versions have their corresponding properties (depending upon old or new desired schema version), our protocol enables distributed schema changes in a way that does not require global membership, implicit or explicit synchronization between nodes, or the need to retain old schema versions once a schema change is complete. Here in this invention, for the table undergoing a logical schema change we assign a dedicated persistent key-value store like RocksDB, etc. to store new insert / update / delete data for user transactions, wherein the key is the primary key for the table and value is the list of objects wherein an object consists of the operation performed (Update / Insert / Delete) along with the change vector for that row.

2. The above said key- value store is actively used while the database transitions from Schema 1 or old Schema to Schema 2 or new Schema.

3. For row-oriented databases for the transition to new Schema or Schema 2 we use background Map-Reduce jobs which partitions the table and assigns partitions to map tasks. The map tasks scan all rows in their corresponding assigned table partitions at a snapshot timestamp corresponding to the start of the canonical schema change, and updates each row to conform to the new schema. The Reduce task then performs aggregation and summary operation on the results of the map tasks.

4. For column-oriented databases the transition to new Schema or Schema 2 is decoupled since adding a new column or deleting an existing column would involve allocating a new set of pages for the newly added column or deleting an existing set of pages for the deleted column.

5. Also note for both row-oriented and column-oriented databases if there is a primary index on the table (or data layout is in the form of a primary B+ tree index like in open source MySQL), it is updated too as part of transition to new Schema or Schema 2.

6. Finally when we are ready to switch to new Schema or Schema 2 we atomically change the canonical schema (from old Schema to new Schema) by acquiring a lock on the metadata table of the database server and replay or synchronise the local updates of the persistent key-value store to the new schema table.

7. Also remember the Multi- Version Concurrency Control (MVCC) component of the database refers to the persistent key-value store for getting access to the latest row data of the table.

8. When there are multiple database shards, the above is achieved or triggered with the help of a distributed transaction but the actual schema change is online and asynchronous.