5 ms·

When Postgres blocks: tips for dealing with locks

97 points by saisrirampur 5y ago

hyzyla 5y ago
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-fast-alter-table-add-column-with-a-non-null-default/ https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-...
- saisrirampur 5y ago
  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!
- philliphaydon 5y ago
  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!
it_citizen 5y ago
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 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.
- fabianlindfors 5y ago
  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 https://github.com/fabianlindfors/reshape
  - rockwotj 5y ago
    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/ https://atlasgo.io/
    - RedShift1 5y ago
      Sqitch probably does what you want?
- sprite 5y ago
  Awesome will be adding this gem to my current rails backend.
yeswecatan 5y ago
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
silvestrov 5y ago
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

97 points by saisrirampur 5y ago

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-fast-alter-table-add-column-with-a-non-null-default/ https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-...

saisrirampur 5y ago
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!
philliphaydon 5y ago
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!

it_citizen 5y ago

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 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.

fabianlindfors 5y ago
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 https://github.com/fabianlindfors/reshape
- rockwotj 5y ago
  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/ https://atlasgo.io/
  - RedShift1 5y ago
    Sqitch probably does what you want?
sprite 5y ago
Awesome will be adding this gem to my current rails backend.

yeswecatan 5y ago

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

silvestrov 5y ago

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