Zero Downtime Database Migrations: A Practical Guide for PostgreSQL
The article provides a practical guide for performing zero-downtime database schema migrations in PostgreSQL. It outlines safe techniques for common operations like adding columns, creating indexes, renaming and changing column types. The methods focus on avoiding locks that block reads or writes, using tools like golang-migrate and Flyway.
- ▪Adding a column with a default value is safe in PostgreSQL 11+ due to catalog-based default storage.
- ▪CREATE INDEX CONCURRENTLY allows index creation without blocking reads or writes.
- ▪Renaming columns safely involves using views and a phased expand-contract approach.
- ▪Changing column types should be done using dual writes via triggers to prevent table rewrites.
- ▪Migration tools like golang-migrate and Flyway support zero-downtime operations with proper configuration.
Opening excerpt (first ~120 words) tap to expand
try { if(localStorage) { let currentUser = localStorage.getItem('current_user'); if (currentUser) { currentUser = JSON.parse(currentUser); if (currentUser.id === 3790305) { document.getElementById('article-show-container').classList.add('current-user-is-article-author'); } } } } catch (e) { console.error(e); } SoftwareDevs mvpfactory.io Posted on May 1 • Originally published at mvpfactory.io Zero Downtime Database Migrations: A Practical Guide for PostgreSQL #webdev #programming --- title: "Zero Downtime Database Migrations: A Practical Guide for PostgreSQL" published: true description: "Step-by-step guide to performing schema migrations in PostgreSQL without downtime — covering ALTER TABLE tricks, CREATE INDEX CONCURRENTLY, safe renames, and migration tooling." tags: postgresql,…
Excerpt limited to ~120 words for fair-use compliance. The full article is at DEV.to (Top).
Discussion
0 commentsMore from DEV.to (Top)
