---
title: "Discussion on \"Debugging a Slow Postgres Migration: What the Query Plan Told Us\" | Hashnode"
description: "Discussion on \"Debugging a Slow Postgres Migration: What the Query Plan Told Us\". We had a migration that should have taken four minutes. It took forty.\nThe first mistake was looking at CPU. The database was idle. The second mistake was looking at locks - plenty of them, but short."
url: "https://hashnode.com/posts/debugging-a-slow-postgres-migration-what-the-query-plan-told-us/6ae182ad5cf232452323837f"
---

[Feed](https://hashnode.com/feed) /

Discussion

# [Debugging a Slow Postgres Migration: What the Query Plan Told Us](https://reliability-notes.hashnode.dev/debugging-a-slow-postgres-migration-what-the-query-plan-told-us?utm_source=hashnode&utm_medium=feed)

We had a migration that should have taken four minutes. It took forty. The first mistake was looking at CPU. The database was idle. The second mistake was looking at locks - plenty of them, but short.

Oct 28 · 2 min read · reliability-notes.hashnode.dev  
Read the article

-   [#backend](https://hashnode.com/tag/backend)
-   [#databases](https://hashnode.com/tag/databases)
-   [#postgres](https://hashnode.com/tag/postgres)

## Written by

[Noah Bennett](https://hashnode.com/@hhnh4g7udvs)

@hhnh4g7udvs · Toronto, Canada

  
[View profile](https://hashnode.com/@hhnh4g7udvs)

Notes on backend systems, reliability and the trade-offs behind them.

Backend developer focused on distributed systems and observability. I write about the trade-offs behind real production incidents - what broke, why, and what we changed. Currently exploring how LLM agents fit into developer tooling.

## Responses 4

Markdown supported · ⌘↵ to post

[AI Developer](https://hashnode.com/@lycore)

[Oct 29](https://hashnode.com/posts/debugging-a-slow-postgres-migration-what-the-query-plan-told-us/6ae182ad5cf232452323837f/comment/6ae2d579e56a5f52e87b4883)

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?

[Omar Haddad](https://hashnode.com/@omarhaddad)

[Oct 28](https://hashnode.com/posts/debugging-a-slow-postgres-migration-what-the-query-plan-told-us/6ae182ad5cf232452323837f/comment/6ae1b726bace774ba7353601)

Running this on a managed Postgres is where the lock queue really starts to hurt.

[Nadia Ferreira](https://hashnode.com/@nadiaferreira)

[Oct 28](https://hashnode.com/posts/debugging-a-slow-postgres-migration-what-the-query-plan-told-us/6ae182ad5cf232452323837f/comment/6ae1b71230376003aaa81af5)

The batched backfill is the part I always forget when I only test on small tables.

[Priya Raman](https://hashnode.com/@priyaraman)

[Oct 28](https://hashnode.com/posts/debugging-a-slow-postgres-migration-what-the-query-plan-told-us/6ae182ad5cf232452323837f/comment/6ae185bea50a971c124c4a35)

The batching detail is the part most write-ups skip.
