Schema changes and the Postgres lock queue

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: those that make incompatible schema changes and those 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. Postgres provides the lock_timeout setting to control how long statements should wait to acquire locks before giving up. Setting an appropriate lock_timeout on DDL statements can prevent other queries from queuing behind them for too long. Tools like pgroll offer features like backoff and retry strategies to automatically handle lock acquisition failures, reducing the risk of blocking reads and writes during schema changes. It's crucial to consider these factors when making schema changes in Postgres to avoid unintended downtime and ensure smooth migrations.