Concurrent indexes in Postgres

A concurrent index in PostgreSQL refers to an index that is created or rebuilt in a way that allows normal operations (INSERT, UPDATE, DELETE, SELECT) to continue on the table during the process. This feature is especially useful in production environments where downtime or blocking operations need to be minimized.

 Demo -

 create table demo_table ( id int, name varchar(100));

insert into demo_table

select id.*, md5(id::varchar) from generate_series(1,200000) id;

 create index idx_id on demo_table(id);

delete demo_table where id='100000';

 postgres=#

create index idx_id on demo_table(id);

delete from demo_table where id='100000';

Concurrent index demo

drop index idx_id;

create  index concurrently idx_id on demo_table(id);

  

Note :if the creation fails it exists and needs to be dropped:

 

\d demo_table

explain select * from demo_table where id= 100000;

select relpages from pg_class where relname = 'idx_id';

Note - When a create index operations fails in concurrent mode make sure that you drop the index immediately.

One more thing to keep in mind: When you create an index concurrently and there is another session already modifying the data the create index command waits until that other operation completes: