PGDay UK2025

Database schema migrations often pose significant challenges to developers, particularly when striving for zero downtime migrations.

There are many things that can go wrong:

• Schema changes breaking client applications
• Unexpected table locking causing downtime
• Human mistakes causing data loss

This talk presents a new approach to schema migrations using an ‘expand/contract’ pattern where multiple versions of a database schema are maintained during the migration, allowing old and new versions of client applications to run side-by-side during an application rollout. Each version of the application sees the version of the database schema with which it is designed to work.

The talks covers the ideas behind this approach, and the challenges to be overcome such as:

• Limitations of existing tools and techniques for Postgres schema migrations.
• The use of Postgres views to present multiple versions of an underlying table.
• Backfilling data between old and new schema versions.
• Techniques to avoid unexpected table locking during migrations.
• Finally we introduce pgroll, an open-source tool that puts these ideas into practice and we show how it can be used to facilitate safe application rollouts across database schema changes.