2

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?

  • 3
    Indexing 20k rows should only take seconds. My guess is that your create index is waiting for a lock: wiki.postgresql.org/wiki/Lock_Monitoring – a_horse_with_no_name Nov 10 '15 at 9:27
  • 2
    An AccessShareLock is a regular lock that is acquired when reading from a table. That should not block a create index concurrently. Is your create index actually waiting for another session? – a_horse_with_no_name Nov 10 '15 at 9:45
  • 2
    IIRC CREATE INDEX CONCURRENTLY still 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:18
  • 1
    @CraigRinger 'PostgreSQL supports building indexes without locking out writes. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially use the index to terminate.' postgresql.org/docs/9.4/static/… – dezso Nov 10 '15 at 11:14
  • 1
    @dezso Confirmed with a quick test and a code check, CREATE INDEX CONCURRENTLY is not blocked by ACCESS SHARE lock. It takes a SHARE UPDATE EXCLUSIVE lock, which conflicts with its self and stronger locks. A stuck VACUUM could cause issues, but not much else that doesn't also block updates. See postgresql.org/docs/current/static/explicit-locking.html – Craig Ringer Nov 10 '15 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.