Relational Database Schema Evolution: An Industrial Case Study

Abstract

Modern relational database management systems provide advanced features allowing, for example, to include behaviour directly inside the database (stored procedures). These features raise new difficulties when a database needs to evolve (e.g. adding a new table). To get a better understanding of these difficulties, we recorded and studied the actions of a database architect during a complex evolution of the database at the core of a software system. From our analysis, problems faced by the database architect are extracted, generalized and explored through the prism of software engineering. Six problems are identified: (1) difficulty in analysing and visualising dependencies between database's entities, (2) difficulty in evaluating the impact of a modification on the database, (3) replicating the evolution of the database schema on other instances of the database, (4) difficulty in testing database's functionalities, (5) lack of synchronization between the IDE's internal model of the database and the database actual state and (6) absence of an integrated tool enabling the architect to search for dependencies between entities, generate a patch or access up to date PostgreSQL documentation. We suggest that techniques developed by the software engineering community could be adapted to help in the development and evolution of relational databases.

Recommendations for Evolving Relational Databases

“…Others were never fully propagated. We argue that, according to what we observed in previous research [4] and the research made in this article, propagating structural change to behavior entities of the database is a hard task as well.…”

“…Before each migration, he prepares a road map containing, in natural language, the list of operators initially planned for the migration. We observed that these road maps are not complete or accurate [4]. Following a long manual process, the architect writes a SQL patch (i.e., a text file containing queries) to migrate from one version of the database to the next one.…”

“…We had the opportunity to record the architect's screen during this migration [4]. We observed that the architect used a trial-and-error process to find dependencies between entities of the database.…”

Assessing the Occurrence of Blocking Operations in Database Schema Evolution: A Case Study

“…We analyzed the extracted migration files using the sqlmigrate tool, provided by the Django framework itself. 3 This tool processes a given migration file and produces the exact DDL commands intended to be executed in the database schema.…”

“…Despite their potential negative impact on important quality attributes, blocking operations have not yet been empirically investigated in the context of software evolution. So far, empirical studies have investigated the evolution of the database schema alone [2,3,12] or the joint evolution of the database and application source code [7,8]. But none of these works have investigated the occurrence of blocking operations during database schema evolution.…”