Postgresql – Adding an index without a lag (PostgreSQL)

I solved the mystery on pg irc channel - I had a long running query (idle in transaction) and they told me that a new index doesn't get used until all transactions that started before its creation don't finish.

Killed the transaction, and now the indexes are back to normal.

The table itself is only locked in ROW EXCLUSIVE mode, which shouldn't prevent any normal operations on the table, only things like DROP, ALTER, and CREATE INDEX.

Each individual row that is being deleted will be locked for the duration. This should only block other processes if those other processes are trying to update the rows (or delete them themselves). Normally with bulk deletions you are deleting rows that no one else cares about, which means no one else will be trying to update them.

If you really need to unlock all the deleted rows and the table periodically, then you need to do each loop of the deletions in a separate transaction. There is no easy way to do this in a DO block because the entire block runs in a single transaction. So you should put your loop outside of PostgreSQL, like in bash, perl, python, etc.