Rename Tables and Columns in Postgres with Zero Downtime

How do rename a table or a column in Postgres? Isn't it as easy as running ALTER "MyTable" RENAME ...?

The problem is that your database clients, typically your web server, need some time to update. Running a renaming query will break your service in the time before it’s updated.

Instead of momentarily taking service down with an "Under maintenance" sign, we can use a slightly more sophisticated approach and do the renames with zero downtime.

Rename a table ← this one is easy, requiring 2 PRs

Rename a column ← this one is harder, requiring 4 PRs

I’m basing the process around PRs, meaning a piece of code updates and/or migrations that you push into your live systems.

Part 1: Renaming a Table

Old table name

New table name

First PR

This migration renames the table, but creates a view with the old name that points to this table. The view can be used just like a table, with DELETE, UPDATE continuing to work.

migration.sql

BEGIN;

ALTER TABLE "Dao" RENAME TO "Community";

CREATE VIEW "Dao" AS SELECT * FROM "Community";

COMMIT;

prisma.schema

- model Dao {
+ model Community {
 ...
}

Second PR

After all clients have been updated, run this code to drop the old view:

migration.sql

DROP VIEW "Dao";

Part 2: Renaming a Column

Renaming columns is harder than renaming tables. There is no way to create an alias or a view for a single column. So in order to have zero downtime, we need to follow the expand and contract pattern.

Roughly, it works like this:

Create the new field alongside the old one. Write to both fields

Make sure old field == new field everywhere

Drop the old field

In practice, we do this safely with 4 PRs.

Old column name

New column name

First PR — add the new field, and start writing to both

Add a new field, but keep it optional for now

prisma.schema

model MyTable {
  daoId Int
+ communityId Int?
}

Start writing to both fields everywhere

app.ts

await prisma.myTable.create({
	data: {
		daoId: id,
+       communityId: id,
	}
})

Manual SQL queries — Copy data and check that it’s 100% on par

After all clients are updated, and have started writing to both fields, copy all the data from the old column to the new column with a manual SQL query:

SQL

UPDATE "MyTable" SET "communityId" = "daoId";

PS: If your tables are huge, you might want to do this in batches.

Let your client code run for a while, then verify that the new column is still 100% on par with the old column. This tells us that we successfully made our client code write to both fields:

SQL

-- should return 0
SELECT COUNT(*) 
FROM "MyTable" 
WHERE "daoId" IS DISTINCT FROM "communityId";

Second PR — Add constraints and start reading from the new field

The current state of our database should now be:

All clients are writing to both the old and the new field
The new field is 100% on par with the old field

This means we can add our foreign key constrains, unique NOT NULL constraints and indexes.

We'll make new column fully mimic the old column.

prisma.schema

model MyTable {
  daoId Int
- communityId Int? 
+ communityId Int

- OtherTable OtherTable @relation(fields: [daoId], references: [id])
+ OtherTable_old OtherTable @relation("Relation_1", fields: [daoId], references: [id])
+ OtherTable OtherTable @relation("Relation_2", fields: [communityId], references: [id])

  @@unique([userId, daoId])
+ @@unique([userId, communityId])
}

Start reading from the new field

app.ts

await prisma.myTable.find({
	where: {
-       daoId: id,
+       communityId: id,
	}
})

Third PR — Stop writing to the old field

Set the old field to Nullable, remove constraints/indexes and ignore it from your schema (prepare for removal)

prisma.schema

model MyTable {
- daoId Int 
+ daoId Int? @ignore   

# remove relations
- OtherTable_old OtherTable @relation("Relation_1", fields: [daoId], references: [id])

# remove indexes
- @@unique([userId, daoId])
}

Stop writing to the old field

app.ts

await prisma.myTable.create({
	data: {
-       daoId: id,
       communityId: id,
	}
})

Fourth PR — Remove the old field

prisma.schema

model MyTable {
- daoId Int? 
  ...
}

Conclusion

Renaming tables and columns in Postgres is not a straigthforward process. In a lot of cases, it can make sense to leave it as it is. But if you can afford to do the renames, it might be something that pays off down the road.

Thanks to brandur for writing this great post that I took a lot of inspiration from!

Rename Tables and Columns in Postgres with Zero Downtime

Part 1: Renaming a Table

First PR

Second PR

Part 2: Renaming a Column

First PR — add the new field, and start writing to both

Manual SQL queries — Copy data and check that it’s 100% on par

Second PR — Add constraints and start reading from the new field

Third PR — Stop writing to the old field

Fourth PR — Remove the old field

Conclusion

Other posts

Tools