PostgreSQL Schema Migrations & Data Modeling

We're excited to be back for our 2nd meet up of 2025, following on from our even in April.

This event will be covering PostgreSQL schema migrations and management, a topic I'm sure a number of application developers and PostgreSQL DBAs can resonate with.

We'll provide some free drinks and snacks for those attending, starting from 18:30, aiming to get our main talk underway shortly after starting.

Main Talk: Postgres Schema Migrations Using The Expand/Contract Pattern

Learn how to do Postgres schema changes without breaking dependent applications by leveraging the expand/contract pattern.

pgroll is a new open-source migration tool for Postgres that keeps multiple versions of a schema live during a migration, helping you roll out database schema changes without downtime. Postgres schema changes 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 changes 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 talk covers the ideas behind this approach, and the challenges to be overcome such as:

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.

Presented By: Andrew Farries of Xata

Andrew has over 10 years experience building developer tools, with a particular focus on database tools in cloud-native environments. He currently works at Xata, building a serverless data platform powered by Postgres.