PostgreSQL pgroll: Zero-Downtime, Reversible Migrations

on YouTube ↗38m

" summary

pgroll is a Go single-binary CLI that applies PostgreSQL (14+) schema changes by creating virtual schemas and shadow tables, using triggers and transaction tracking to serve old and new schemas simultaneously. It performs automatic backfill of data, exposes start/status/complete/rollback commands (rollback works until a final complete is run), and targets zero service impact via zero-downtime migrations. Limitations: migration inputs are JSON files (no batch file import yet) and builds from source require Go 1.23+.

AI-written summary · about

" chapters (11)

1. 0:00Introduction and speaker
2. 1:48Challenges of online schema changes
3. 6:01What is pgroll and its benefits
4. 9:34How pgroll works in the background
5. 11:21Expand contract workflow explained
6. 14:56Schema versions during migration
7. 18:50Installing and configuring pgroll
8. 21:14Using pgroll with JSON change files
9. 23:40Adding columns and constraints
10. 25:45Comparison with other tools
11. 28:12Q&A session begins

" related talks

• 43mPostgres schema migrations using the expand/contract patternAndrew Farries · PostgreSQL Conference Europe 2024
• 28mBringing PostgreSQL towards zero downtime migration with PythonMatthieu Rigal · EuroPython 2015
• 52mManaging PostgreSQL changes for the Agile DBARyan Booz · PGConf NYC 2021
• 30mPuny to Powerful PostgreSQL Rails AppsAndrew Atkinson · RailsConf 2022
• 37mDatabase Schema Migrations with Zero DowntimeMichiel Rook · PHP UK Conference 2018
• 01h 41mPostgreSQL Upgrades with Logical Replication & Minimal DowntimeIldefonso C · Postgres Conference Orlando 2025
• 48mMigrating to PostgreSQL: Strategies, Sovereignty, and SuccessRaphael Salguero · PostgreSQL Conference Europe 2025
• 50mAn ultimate guide to upgrading your PostgreSQL installationIlya Kosmodemiansky · PostgreSQL Conference Europe 2024