Renaming a table in production in PostgreSQL

Sometimes you don’t get it right in the first attempt.

When some time ago I announced that we need to rename one of the database tables in our recently launched service, some of my colleagues looked at me like I said something really stupid. Their argument was that is won’t work without causing a downtime and generally it’s not worth it. Their stance was that it’s better to live with a too generic table name than to attempt a risky operation. In my opinion it was worth it and I pushed the change through, without any downtime.

Their instincts were right, however. If you do it naively by simply calling ALTER TABLE RENAME, it will harm your production. Fortunately, with a little more ceremony you can do it right and here is how.

What’s the problem, really?

MostI’m tempted to say “all”, but that’s probably not true of deployment processes these days consist of few sequential steps to get a new version up and running. They vary from project to project, but usually contain these three in this order:

1. Make the new code ready (build a Docker image, compile, copy to server)
2. Run migrations
3. Restart application instances to use new code

The problem is that there is a non-zero gap between 2. and 3. are finished. In fact, it’s not that uncommon to take a minute or longer, as you have to let the servers finish serving existing requests etc. Now, when migrations are run and your renaming-the-table migration among them, the old table does not exist, but the old application instances still reference it. As a result they still try to query a non-existing table, resulting in an exception (most likely).

This is something we want to avert.

View to the rescue

So, if only we could use some kind of an alias to route requests to old table name to a new name…

Fortunately, we can. Since a couple of versions PostgreSQL supports usage of a regular dynamic views along its old materialized views. These views are, in essence, an alias for a query. Every time you select from a view, it is translated to an underlying query. What’s even better? SimpleSimple – meaning no aggregations, no window functions, no unions, no grouping, limits offsets… But even some joins count! views are updatable, which means you can insert to them or update them - and it will be perfectly reflected in an underlying table. Specifically, we can use them to just create aliases.

Armed with that knowledge, we may write our migration as something like thisI’m using ActiveRecord migration in the example, but it can be ported to annything. Actually, original was written in Ecto.Migration:

def up
  rename_table :old_name, :new_name
  execute "CREATE VIEW old_name AS SELECT * FROM new_name"
  # also rename sequences, indices etc. for consistency
  execute "ALTER SEQUENCE old_name_id_seq RENAME TO new_name_id_seq"
  execute "ALTER INDEX old_name_something_id_index RENAME TO new_name_something_id_index"
end

This will be fast. Renaming a table is fast. Creating a view is fast. Actually, our migrator reported 0.0s for this migration (which means less that 50 ms IIRC) for a table with few million records. Most likely it won’t cause any negative side-effects for your application. You just want to remember to delete the view next time the deployment is done.

Note that because of using execute the migration is irreversible. You definitely shoud write a corresponding down method in case things go wrong.

And that’s it. We renamed the table. Now we can move on with our lives, without having weird legacy names and have BI/Analytics team ask questions about it every now and then.

Updates

15.02.2022: As /u/fatkodima noted on Reddit, this approach might still cause some trouble, because by default ActiveRecord cannot read columns from views. He also is a creator of a gem online_migrations which helps with that issue in Rails.