0

In my Postgres 14.3 database on AWS RDS, I want to create an index without blocking other database operations. So I want to use the CONCURRENTLY option and I executed the following statement successfully.

CREATE INDEX CONCURRENTLY idx_test
    ON public.ap_identifier USING btree (cluster_id);

But when checking the database with:

SELECT * FROM pg_indexes WHERE indexname = 'idx_test';

I only see: Index without CONCURRENTLY option

I am expecting the index is created with CONCURRENTLY option.

Is there any database switch to turn this feature on, or why does it seem to ignore CONCURRENTLY?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Micha
  • 3
  • 2
  • 4
    CONCURRENTLY is an option to the CREATE INDEX command, it is not an option of the index itself. Once the index is (successfully) built, it doesn't remember/care if it were built concurrently, nor under a full moon. – jjanes Dec 20 '22 at 16:32
  • There is no such thing as a concurrent index. The `CONCURRENTLY` option only states that the index is to be created concurrently. So, is not an index property but rather a creation option to avoid locks. – Jim Jones Dec 20 '22 at 16:35

1 Answers1

2

As has been commented, CONCURRENTLY is not a property of the index, but an instruction to create the index without blocking concurrent writes. The resulting index does not remember that option in any way. Read the chapter "Building Indexes Concurrently" in the manual.

Creating indexes on big tables can take a while. The system table pg_stat_progress_create_index can be queried for progress reporting. While that is going on, CONCURRENTLY is still reflected in the command column.

To your consolation: once created, all indexes are "concurrent" anyway, in the sense that they are maintained automatically without blocking concurrent reads or writes (except for UNIQUE indexes that prevent duplicates.)

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    What you might see is that the index is "invalid" while it is still being built, or if it fails. – Schwern Dec 20 '22 at 21:45