When Postgres blocks: tips for dealing with locks (2018)

When you can have long-running SELECT queries on a table, don’t do this:

Instead, do this:

By setting , the DDL command will fail if it ends up waiting for a lock, and thus blocking queries for more than 2 seconds. When you need to run a command that acquires aggressive locks on a table, try to do it as late in the transaction as possible to allow queries to continue for as long as possible. Postgres makes it very easy to create a primary key using , but while the index for the primary key is being built, which can take a long time if the table is large, all queries will be blocked.

Source: www.citusdata.com