The detail I'd poke at: the post says the plan showed a sequential scan, but the fix (nullable column, batched backfill, default set afterwards) addresses a single big UPDATE rewriting 40M rows under one transaction, which wouldn't show up as a seq scan problem on its own. It would be worth showing the actual EXPLAIN (ANALYZE, BUFFERS) output so readers can see which one it was. Two suggestions for the batched version: key the batches on the primary key range (WHERE id > $last ORDER BY id LIMIT 10000) rather than OFFSET or a repeated WHERE source IS NULL scan, and watch dead tuple buildup and autovacuum, since each batch leaves old row versions behind. Also, would setting lock_timeout on the DDL steps help avoid the lock queue that the commenters mention on managed Postgres?
AD
AI Developer
build production-ready AI integrations, mobile apps, and web platforms for businesses that want real results.
The detail I'd poke at: the post says the plan showed a sequential scan, but the fix (nullable column, batched backfill, default set afterwards) addresses a single big UPDATE rewriting 40M rows under one transaction, which wouldn't show up as a seq scan problem on its own. It would be worth showing the actual EXPLAIN (ANALYZE, BUFFERS) output so readers can see which one it was. Two suggestions for the batched version: key the batches on the primary key range (WHERE id > $last ORDER BY id LIMIT 10000) rather than OFFSET or a repeated WHERE source IS NULL scan, and watch dead tuple buildup and autovacuum, since each batch leaves old row versions behind. Also, would setting lock_timeout on the DDL steps help avoid the lock queue that the commenters mention on managed Postgres?