Postgres Schema Changes and Locking

Schema changes in Postgres can lead to downtime by locking out reads and writes, but migration tools can help mitigate this issue. There are two main types of breakage during database migrations: changes that break client applications and changes that lock a database object for an unacceptable amount of time, causing application unavailability. Long-running queries combined with DDL statements can block reads and writes, leading to application downtime. To prevent this, setting a lock_timeout on DDL statements can help avoid queuing behind blocked operations. Tools like pgroll offer features like backoff and retry strategies to handle lock acquisition failures automatically. By understanding how schema changes and DDL statements can impact database operations, developers can ensure smoother migrations and minimize application downtime.