What is the expand/contract pattern and how does it apply to schema changes in Postgres? Let's say you want to convert a column from text to integer, something like: ALTER TABLE foo ALTER COLUMN col TYPE INTEGER USING col::integer; #postgresql - ThreadSky

About ThreadSky

tudor.golubenco.org • 94 days ago

What is the expand/contract pattern and how does it apply to schema changes in Postgres? Let's say you want to convert a column from text to integer, something like:

ALTER TABLE foo ALTER COLUMN col TYPE INTEGER USING col::integer;

#postgresql

Comments

tudor.golubenco.org•94 days ago

Doing the above works, but what if the foo table is large enough (say, a few million rows)? Postgres needs to modify all those rows, which will take some time (say, minutes).

tudor.golubenco.org•94 days ago

While the ALTER statement executes, it has the ACCESS EXCLUSIVE lock, which means no other query can read or write to that table.

That’s essentially downtime.

tudor.golubenco.org•94 days ago

So how would you do it safely? You break it in multiple steps:

- add a new integer column
- use a script to backfill the data to the new column, converting it
- switch the application to use the new column
- drop the original text column

This is an example of the expand/contract pattern.

tudor.golubenco.org•94 days ago

You first “expand” the table by adding a new column. You use them both for a period of time, backfill data, then you “contract” by dropping the old column.

pgroll automates this for you. For the "change type" migration type, it does pretty much exactly the above steps.

https://github.com/xataio/pgroll

tudor.golubenco.org•94 days ago

While expand/contract is a multi-step process, pgroll manages to do it as a single operation. How?

It uses Postgres view to show both the old version of the schema and the new version of the schema **at the same time**.

This means you can do a rolling upgrade of your app without worries.

Posting Rules

Be respectful to others
No spam or self-promotion
Stay on topic
Follow Bluesky's terms of service

Comments

Posting Rules

Reply