When Postgres blocks: tips for dealing with locks(citusdata.com)
citusdata.com
When Postgres blocks: tips for dealing with locks
https://www.citusdata.com/blog/2018/02/22/seven-tips-for-dealing-with-postgres-locks/
4 comments
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/
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-...
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!
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:
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 modifiedOn 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
- 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
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.