When Postgres blocks: tips for dealing with locks | Hacker News Reader

When Postgres blocks: tips for dealing with locks

citusdata.com

97 pointssaisrirampur10 comments

Half of the problems in this article are migration related.

I am extremely grateful that some people have created awesome libraries like strong migrations https://github.com/ankane/strong_migrations.

Even if you are not using rails, you can bookmark its readme, it is an awesome cheat-sheet on how to do common database migrations.

You might also be interested in Reshape, a tool I’ve been working on to automate zero-downtime deployments with Postgres: https://github.com/fabianlindfors/reshape

In terms of automating migrations in postgres, I really want declarative migrations and only the safe subset too. The overhead of having a schema defined over a bunch of migrations seems non ideal.

The closest tool I've found here is: https://atlasgo.io/

Sqitch probably does what you want?

Awesome will be adding this gem to my current rails backend.

Statement about default value for new column is not actual anymore starting from Postgres 11

https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-...

That is right.. default value for new column is now pretty fast.. all the other 6 tips are current and very relevant.. this blog has been my goto blog for debugging locks in postgres, as it captures a very crisp summary on this topic!

Just checked. Article was written Feb 2018, and pg11 was released in October 2018.

This was one of the huge features I was excited about when it dropped!

I'd really like a "LIMIT x" option for updates when initializing new columns.

Updating all rows can lock reading forever, so I'd like to do:

    loop
      UPDATE items SET last_update = now() WHERE last_update IS NULL LIMIT 10000
      commit
    until no rows modified

On postges < 10, adding a new not null column is a pain. A hacky way to get around this is:

- add the column which allows null values

- backfill in batches

- use triggers to auto-populate for new inserts

- add a check constraint

- and now the hack: set the column to not null using pg_attribute.attnotnull

Reply on news.ycombinator.com