Dev's PostgreSQL experiment probes possibility of zero-downtime schema migration

It has potential, says one expert

A Swedish developer has published code that promises to avoid application downtime during PostgreSQL schema migrations.

Using "views" in the popular open-source database to encapsulate tables and create a versioned interface, Fabian Lindfors, a final year MSc student in computer science at Lund University, has produced a tool that he hopes can automate zero-downtime migrations.

Available on GitHub, Reshape is a command-line tool which works with files that define changes to the database schema.

"The big difference is that Reshape makes non-destructive changes, ensuring that clients that still expect the old schema continue to work. This means that the database can be accessed in parallel using the old and the new schema, and application and database changes can be deployed gradually without any downtime," Lindfors explained in his blog.

Speaking to The Register, he said that smaller, non-critical applications tend to be taken offline when database schema are changed and rewritten to accommodate the new data structure.

"For more critical applications or larger companies, you might not be able to accept downtime," he said.

"When you do large-scale migrations, it usually becomes a very manual process. You will make sure that every change you do to the database schema doesn't break the application. If you do some major changes, you need to do multiple changes manually over time, which takes a lot of developer effort and it can risk human error. It's a huge burden on us developers."

The Reshape project is available as open-source code under the MIT Licence.

Lindfors is hoping developers will be able to feedback on the proof of concept.

"I didn't think anybody had done this before. I'm still not entirely sure it will be used by people, so I created this proof of concept," he said. "It's experimental – and probably full of bugs – but I wanted to release it, put it out there to find out if people would find it useful. Hopefully, I can find some people who want to try it out and experiment with it, so we can bring it to a state where it can actually be used in a production setting."

A zero-downtime upgrade feature is an interesting topic for database users as it could help provide high availability for systems that need to periodically upgrade, said Umair Shahid, head of PostgreSQL at Percona, an open-source consultancy and contributor.

"This tool – Reshape – aims to fill that remit for PostgreSQL, which makes it interesting as PostgreSQL is so popular today," he told us after looking at the project.

"Most commits came in over the last month, so this would be a project I would keep an eye on to see how it develops over time. As it appears to be a single author project and a very new piece of software that is still experimental, it's not something to push into production right away. However, I think it has a lot of potential."

Lindfors said the project was not likely to become a proposal for PostgreSQL but remain as a standalone tool for developers using the database.

"They are the ones who actually decide what the schema will be. They're the ones who want to change it. I want to put that power in their hands so they don't have to worry about the administrative parts of schema migration.

"I'm hoping to find some application developers who use PostgreSQL and get some feedback on what features they want to see and what migrations they have to run." ®