Rename Tables and Columns
in a Postgres DB

Contents

• Preface
• How It Works
• Step-by-Step Guide
• Conclusion

Preface

^▲

Have you ever faced the challenge of renaming database tables or columns without halting your application? If you're building systems that require zero downtime and seamless transitions (blue/green deployment), especially in cloud environments, read on. In this post, we'll explore a strategic approach to renaming database tables and columns using PostgreSQL's powerful features.

In a world of agile software development with ever changing requirements and daily production deployments, keeping your data structure flexible and maintainable is crucial. While renaming tables and columns might seem simple, it is not as straight forward when working in a zero downtime context. Renaming operations can lead to significant application downtime, service disruption or data loss! Some of you might say "We have some maintenance windows for (planned) downtime in our SLAs". That might be true, but you'd be well-advised to spare that contingent.

How It Works

^▲

This blog posts presents an approach for renaming tables and columns leveraging PostgreSQL's Updatable Views, allowing for a smooth transition by abstracting direct table access during the renaming process. The updatable view is an intermediary between the app and the original table, keeping everything running smoothly while renaming the table and/or columns. After the renaming is done we let the app point to the new table name and the view can be removed.

With this strategy, we aim to ensure that your application (and its users!) remain unaffected while you carry out these critical modifications. We utilize Flyway as database migrations tool to systematically implement the changes, but that shouldn't play a role for the renaming approach itself.

Step-by-Step Guide

^▲

Before diving into the steps, it's important to note:

• A deployment is required after each step to fulfill blue/green deployment requirements. No old service versions must be deployed/running anymore when the next step is merged/deployed.
• It is not possible to create indices or constraints on a simple view.
• This approach also works for primary key and foreign key columns but there might be special cases to consider.
• Check the official documentation for postgres' Updatable Views to understand what's possible (and what might not).

We'll assume you're using JPA (Hibernate in this example, but the instructions should be equivalent for EclipseLink). This approach has been tested with Java 21 and Spring Boot 3.

You can find an example implementation of the steps on GitHub.

Step 1: Create a View

First, create a DB view {legacy_name_table}_view through a Flyway script. The view should fetch all data from your {legacy_name_table}. This additional layer abstracts direct access to the table itself. Deploy this change and only continue with the next step, once the view was created.

Step 2: Update Entity Annotations

Next, update all JPA @Entity/@Table annotations to point from {legacy_name_table} to {legacy_name_table}_view. This step ensures that your application's data access logic remains intact, even as you are about to rename the underlying table in the next step. Deploy this change and only continue with the next step, once no old application instance is running anymore that still points to the table and not to the view.

Step 3: Rename the Table and/or Columns

Using a Flyway script, rename your table from {legacy_name_table} to {new_name_table}. If needed you can also (or alternatively) rename columns. Deploy this change and only continue with the next step, once the renaming operation was successful.

Step 4: Update Annotations Again

Now, redirect all JPA @Entity/@Table annotations to point from {legacy_name_table}_view to {new_name_table}. By this point, your application should be fully referencing the newly named table directly. Deploy this change and only continue with the next step, once no old application instance is running anymore that could point to the view.

Step 5: Remove the View

Finally, drop the {legacy_name_table}_view via a Flyway script. With this step, you have fully transitioned your application and database to the new table name.

Conclusion

^▲

Renaming database tables and columns in a cloud environment is possible without downtime. By utilizing PostgreSQL's Updatable Views and adhering to a disciplined, step-by-step approach, you can achieve a seamless transition that maintains application functionality throughout the process. As always, test thoroughly and plan your deployments carefully to ensure a smooth database evolution.
If you found this blog post helpful, feel free to leave me a ★ Star on the GitHub repository.

Happy renaming!

★
/ \
/ * \
/ o o \
/ * @ * \
/ o o o \
/ * o * @ \
/ o @ o * \
/ @ * o * o \
|||
|||

Remark: This blog post was written with AI assistance.

Credits: There is a blog post by brandur who already described this approach in 2022. In my article, I focused on writing down the instructions to a zero downtime renaming step by step, pointing out when deployments need to happen. In the original post, the possibility of also renaming columns was not mentioned.

To the top ▲