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.

Short Talk: Fun With UUIDs

UUIDs have a bad reputation, mostly based on randomly allocated UUIDs effect on indexes. But UUIDs also give us 16 bytes of space to play with, which can be to our advantage. We can use the space of UUIDs to structure and encode data into our identifiers. This can be really useful for multi-tenant applications, for sharding or paritioning. UUIDs can also help improve you web app security, by not leaking sequentially allocated ids.

We'll take a look at index performance concerns with randomly allocated UUIDs, sequential ids and sensibly structured UUIDs.

Seeing how we can go about extracting information from these UUIDs and how to build these UUIDs inside PostgreSQL, and how we can look at the performance of these functions.

Presented By: Chris Ellis of Nexteam

Chris is a computing and electronics geek, who loves working with Open Source software. He is a jack of all trades consultant usually spending his time engineering systems with PostgreSQL, Java and Linux. Now and then he gets time to design some electronics often involving ESP32s and Raspberry Pi CM4s.

On The Evening

We have a pre-paid bar tab open (first come first served), so come in grab a drink of your choice at the bar and join us in the upstairs room.

We'll have a short introduction to the meet up, before kicking off with Fun With UUIDs.

Following our first short talk, snacks will arrive, and we'll have a short break.

Before the main event of Postgres Schema Migrations Using The Expand/Contract Pattern.

We'll have time for a Q&A with Andrews and the chance for all to meet others and chat about PostgreSQL.

With Thanks

Xata has generously supported us by providing our headline talk.
Nexteam has generously supported the venue, food and drinks.

Many thanks to Xata and Nexteam, the event would not be possible with out them. If you're interested in speaking or supporting a future event, get in touch.