Why Large Postgres UPDATE Queries Stall Deployments and How to Fix Them

Running a bulk UPDATE on a large production Postgres table can silently lock the database, stall deployments, and provide no progress feedback until the query finishes or is forcibly terminated. The problem is common during migrations that appear instant on small datasets but become unmanageable at scale, sometimes affecting hundreds of millions of rows. Common chunking strategies — such as splitting by business columns, using OFFSET/LIMIT pagination, or computing buckets with window functions like NTILE — each carry hidden costs, including full table scans before a single row is processed. OFFSET-based approaches are particularly deceptive, as they degrade in performance at large offsets and can cause rows to be skipped or double-processed if the table changes mid-migration. The recommended approach is to break the work into small, independently safe chunks using primary key ranges, minimizing lock duration and allowing failures to be recovered without restarting the entire operation.