Towards online relational schema transformations

Abstract

Current relational database systems are ill-equipped for changing the structure of data while the database is in use. This is a real problem for systems for which we expect 24/7 availability, such as telecommunication, payment, and control systems. As a result, developers tend to avoid making changes because of the downtime consequences. The urgency to solve this problem is evident by a multitude of tools developed in industry, such as pt-online-schema-change(1) and oak-online-alter- table(2). Also, MySQL recently added limited support for online schema changes(3).\ud \ud Contributions: We want to draw the attention of the database community to the problem of online schema changes. We have defined requirements for online schema change mechanisms, and we have experimentally investigated existing solutions. Our results show that current solutions are unsatisfactory for complex schema changes. We propose lazy schema changes as a solution.\ud \ud Experimental Setup: To assess the performance and behaviour of existing mechanisms for on- line schema changes, we have developed an experiment based on the standard TPC-C benchmark. For each of the relational schema transformation classes that we have identified, we chose a rep- resentative transformation for the TPC-C schema. We perform the schema change online while the TPC-C benchmark is running, and measure the impact on the TPC-C transaction through- put. We have performed our experiment on PostgreSQL, which does not support online schema changes, MySQL, which supports basic online schema changes, and using pt-online-schema-change on MySQL, as a representative for tools that use triggers to allow online schema changes.\ud \ud Results: We found that existing solutions are inadequate except for the simplest of schema changes. Some single-relation transformations can be performed transactionally and online. How- ever, existing solutions do not allow schema transformations to be composed using transactions. As a result, in complex transformations, intermediate states can be exposed to database programs, which are non-trivial to handle correctly. A secondary problem is that these solutions are much slower than offline transformations, which may not be acceptable for certain applications.\ud \ud Proposal: We propose a more fundamental solution based on lazy schema transformations. The main idea is that schema changes can be described as a view on the existing schema, which can be materialized lazily to perform the schema transformation. The data in the new schema is immedi- ately accessible by computing parts of the view on demand. For a large number of cases we expect that this approach allows schema transformations without any downtime, and with minimal impact on running transactions, while the ACID properties are maintained. Moreover, lazy transforma- tions can naturally be composed as transactions, allowing complex online schema transformations. We are developing an implementation of these ideas based on a persistent functional language