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?