Towards Online Relational Schema Transformations

L. Wevers, M Hofstra, M Tammens, Marieke Huisman, Maurice van Keulen

Research output: Chapter in Book/Report/Conference proceedingConference contributionAcademic

1 Citation (Scopus)
53 Downloads (Pure)


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-change1 and oak-online-alter- table2. Also, MySQL recently added limited support for online schema changes3. 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. 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. 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. 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.
Original languageUndefined
Title of host publicationDutch-Belgian Database Day 2014
Place of PublicationAntwerp, Belgium
PublisherDutch-Belgian Database Day
Number of pages1
ISBN (Print)not assigned
Publication statusPublished - 17 Oct 2014
EventDutch-Belgian Database Day 2014 - Antwerp, Belgium
Duration: 17 Oct 201417 Oct 2014

Publication series

PublisherDutch-Belgian Database Day


OtherDutch-Belgian Database Day 2014
Other17 October 2014


  • EWI-25769
  • IR-94673
  • METIS-309917

Cite this