0

I have trouble understanding two limitations for postgres when it comes to create indexes and dropping them in concurrently mode:

the first about CREATE INDEX CONCURRENTLY:

Regular index builds permit other regular index builds on the same table to occur simultaneously, but only one concurrent index build can occur on a table at a time.

actually I tried that, but I could apply two commands with CREATE INDEX CONCURRENTLY and both indexes were valid. Does that just mean that Postgres allows running the command twice but

the second about DROP INDEX CONCURRENTLY:

There are several caveats to be aware of when using this option. Only one index name can be specified, and the CASCADE option is not supported. (Thus, an index that supports a UNIQUE or PRIMARY KEY constraint cannot be dropped this way.)

currently I did not read the docs and removed an index with DROP INDEX CONCURRENTLY that was UNIQUE. However it did work and the index was removed, did I miss something here?

Improve this question
2
  • 2
    Unique index <> unique constraint.
    – mustaccio
    Commented Aug 30 at 16:14
  • 1
    Your first question seems to have been truncated abruptly at 'twice but'.
    – jjanes
    Commented Aug 30 at 17:23

1 Answer 1

Reset to default
1

1. CREATE INDEX CONCURRENTLY

... but I could apply two commands with CREATE INDEX CONCURRENTLY

You can create any number of indexes with CREATE INDEX CONCURRENTLY. They just cannot be created concurrently. Only one index can be built concurrently at a time. You probably built them sequentially, i.e. one at a time. CONCURRENTLY is not a property of the index, but an instruction how to build it.

Seems related to this misunderstanding:

2. DROP INDEX CONCURRENTLY

I [...] removed an index with DROP INDEX CONCURRENTLY that was UNIQUE.

UNIQUE constraints (ALTER TABLE tbl ADD CONSTRAINT ...) are part of the SQL standard and implemented in Postgres using a UNIQUE index, which you can also create on its own (CREATE UNIQUE INDEX ...). The functionality of the former is a superset of the later.

You can drop a unique index CONCURRENTLY, but not if it's just an implementation detail of a constraint. That would need the CASCADE functionality which is not supported - as per your quote.

Related:

Improve this answer
4
  • Wow the 2. nails it, we actually do not use UNIQUE constraints and instead always use unique indexes, especially since we often need to use partial unique indexes. I still have a question about: CREATE INDEX CONCURRENTLY so you said I built them "sequentially", does that mean that If I run CREATE INDEX CONCURRENTLY that it blocks until the index is created? thats why it worked? I always tought that they run inside the background and the indexes I created should take at least some minutes to be done.
    – Christian Schmitt
    Commented Sep 3 at 7:20
  • @ChristianSchmitt Unless the table is big, index creation is quite fast. Milliseconds for small to medium tables. CREATE INDEX CONCURRENTLY does not block concurrent writes. This requires some smart bookkeeping in the background, so only one index per table can be built at a time this way. It "blocks" concurrent CREATE INDEX CONCURRENTLY on the same table.
    – Erwin Brandstetter
    Commented Sep 3 at 13:20
  • well the table is big ~10 mio entries, the index creation takes seconds, at least 5 on my dev machine with a snapshot from the live database. and it's write heavy, we create indexes and the table with a fillfactor. (we also prune the table every 180 days since only the newest entries are important for us) if we do not use CREATE INDEX CONCURRENTLY the ` CREATE INDEX` takes longer?! and we encounter around 30seconds downtime
    – Christian Schmitt
    Commented Sep 3 at 17:26
  • 1
    CREATE INDEX without CONCURRENTLY is typically (substantially) faster. But it blocks concurrent writes. And it may have to wait before it can place its exclusive lock on the table. Production life is not as clear cut as lab experiments.
    – Erwin Brandstetter
    Commented Sep 3 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.