I am trying to create indexes on multiple (1000) partitioned tables. As I'm using Postgres 10.2, I would have to do this for each of the partition separately, having to execute 1000 queries for the same.
I have figured how to do it, and it does work on environments where the table size(s) and number of transactions are very less. Below is the query to be executed for one of the table (which is to be repeated for all the tables ( user_2, user_3, etc.)
CREATE INDEX IF NOT EXISTS user_1_idx_location_id
ON users.user_1 ( user_id, ( user_data->>'locationId') );
where user_data is a jsonb column
This query does not work for large tables, with high number of transactions - when I run it for all the tables at once. Error thrown:
ERROR: SQL State : 40P01
Error Code : 0
Message : ERROR: deadlock detected
Detail: Process 77999 waits for ShareLock on relation 1999264 of database 16311; blocked by process 77902.
Process 77902 waits for RowExclusiveLock on relation 1999077 of database 16311; blocked by process 77999
I am able to run it in small batches (of 25 each) - still encountering the issue at times, but running successfully when I retry it once or twice. Smaller the batch, lesser the chances of a deadlock.
I would think this happens because all the user tables ( user_1, user_2, etc) are linked to the parent table: user. I don't want to lock the entire table for the index creation (since in theory only one table is being modified at a time). Why does this happen and is there any way around this, to ensure that the index is created without the deadlocks ?
This worked:
CREATE INDEX CONCURRENTLY IF NOT EXISTS user_1_idx_location_id
ON users.user_1 ( user_id, ( user_data->>'locationId') );