I attempted to create a partial unique index for a table like example 11-3 here:
CREATE TABLE tests (
subject text,
target text,
success boolean,
...
);
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;
However, (to mimic creating the index in a production environment) I additionally used the CONCURRENTLY keyword, so:
CREATE UNIQUE INDEX CONCURRENTLY tests_success_constraint ON tests (subject, target)
WHERE success;
If course, I understand, according to this, that creating an index concurrently takes "significantly longer to complete", however, in my test environment on a table with ~20K rows and no other users of that table, this command has now run for an hour and is still going at it and I feel that this is a bit too much (especially when looking forward to applying the command in production where there is traffic on that table).
Of course, the table contains some more columns than the example and a UNIQUE constraint against (the equivalents of) (subject, target) but it does not look like anything that should hinder the creation of a new index that much.
I see that there has been a fellow sufferer but as that case is with a huge table compared to mine, I tend to think that his problem is not applicable to me.
The only thing that I think is relevant is that the success column in my case currently consists of mostly NULLs, only a few rows that contain TRUE there. Would that be the thing that makes that much of a difference?
Or any other hidden caveats that I may have stumbled on?
create indexis waiting for a lock: https://wiki.postgresql.org/wiki/Lock_Monitoring – Nov 10 '15 at 09:27AccessShareLockis a regular lock that is acquired when reading from a table. That should not block acreate index concurrently. Is yourcreate indexactually waiting for another session? – Nov 10 '15 at 09:45CREATE INDEX CONCURRENTLYstill needs to get a moderately strong lock, just briefly. So a long running lock could still prevent it from getting started. – Craig Ringer Nov 10 '15 at 10:18CREATE INDEX CONCURRENTLYis not blocked byACCESS SHARElock. It takes aSHARE UPDATE EXCLUSIVElock, which conflicts with its self and stronger locks. A stuckVACUUMcould cause issues, but not much else that doesn't also block updates. See http://www.postgresql.org/docs/current/static/explicit-locking.html – Craig Ringer Nov 10 '15 at 11:39pg_locks. Also look atpg_stat_activityfor the command. Is it markedwaiting? What's your exact PostgreSQL version? – Craig Ringer Nov 10 '15 at 11:43AccessShareLock, too. – András Váczi Nov 10 '15 at 11:48