CREATE INDEX CONCURRENTLY is executed but the CONCURRENTLY option lost after creation?-CodePudding

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?

CodePudding user response：

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: 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.)