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

Answer from Erwin Brandstetter on Stack Overflow
🌐
PostgreSQL
postgresql.org › docs › current › sql-createindex.html
PostgreSQL: Documentation: 18: CREATE INDEX
May 14, 2026 - 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 ...
🌐
Stack Overflow
stackoverflow.com › questions › 74865499 › create-index-concurrently-is-executed-but-the-concurrently-option-lost-after-cre
postgresql - CREATE INDEX CONCURRENTLY is executed but the CONCURRENTLY option lost after creation? - Stack Overflow
Top answer
1 of 1
32

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

sql - Why is my "CREATE INDEX CONCURRENTLY .." command blocked by a "SELECT FROM" query? (Postgres 9.6)
sql - Postgresql concurrent index
postgresql - How do I create indexes concurrently with flyway in postgres?
postgresql - create index concurrently on partitioned table
PostgreSQL - When do indices get build and when to use CONCURRENTLY?
More results from stackoverflow.com
Discussions
What is the difference between CREATE INDEX and CREATE INDEX CONCURRENTLY in PostgreSQL? - Database Administrators Stack Exchange
A concurrently created index can have some bloat, but that difference will even out after a while. ... Find the answer to your question by asking. Ask question ... See similar questions with these tags. ... Partnerships can keep open source... 8 What is the difference between xact_start and query_start in postgresql... More on dba.stackexchange.com
🌐 dba.stackexchange.com
November 13, 2023
how to create big indexes
https://www.visuality.pl/posts/indexing-partitioned-table---postgres-stories Like this article (and the docs) say, I'd recommend creating an "invalid" index on the table as a whole using ONLY, then create the indexes concurrently on the partitions and attach one at a time. A bit slower, but much safer. You can use this query to generate the create index and alter index statements for the partitions SELECT nmsp_parent.nspname AS parent_schema, parent.relname AS parent, nmsp_child.nspname AS child_schema, child.relname AS child, 'CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_'|| child.relname || 'on_column_name ON ' || child.relname || '(column_name);' as "idx_create", 'ALTER INDEX idx_table_on_column_name ATTACH PARTITION idx' || child.relname || '_on_column_name;' as "idx_alter" FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace WHERE parent.relname='tablename'; More on reddit.com
🌐 r/PostgreSQL
5
8
June 8, 2024
Don't rely on IF NOT EXISTS for concurrent index creation in PostgreSQL
In my experience on large tables that are “busy”, sometimes indexes need to be added manually first from a utility session, perhaps inside tmux/screen that's detached from while they are created. This could take hours for large tables. Then once done, and the index is valid, an Active Record ... More on news.ycombinator.com
🌐 news.ycombinator.com
54
90
August 14, 2024
Support `CREATE INDEX CONCURRENTLY` (PostgreSQL)
This is being discussed as a comment in #9154 (comment), but I wanted to surface it further because I've personally encountered a couple of incidents where not using this has led to database st... More on github.com
🌐 github.com
18
July 22, 2022
Videos
53:35
🌐 YouTube
Deep Dive Into PostgreSQL Indexes - YouTube
July 30, 2021
11:16
🌐 YouTube
Why create Index blocks writes - YouTube
October 29, 2023
33:36
🌐 YouTube
CREATE INDEX CONCURRENTLY implementation details - Andrey Borodin ...
November 28, 2023
155
02:21
🌐 YouTube
Postgres How to create index concurrently using liquibase - YouTube
April 11, 2024
1
04:17
🌐 YouTube
5mins of Postgres E20: Bug in Postgres 14 with REINDEX / CREATE ...
June 2, 2022
View all
View all
🌐
Bytebase
bytebase.com › blog › guides › how to use postgres create index concurrently
How to Use Postgres CREATE INDEX CONCURRENTLY | Bytebase
August 27, 2025 - PostgreSQL's CREATE INDEX CONCURRENTLY allows index building without blocking writes by using a SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) instead of a SHARE lock:
🌐
Stack Exchange
dba.stackexchange.com › questions › 333044 › what-is-the-difference-between-create-index-and-create-index-concurrently-in-pos
What is the difference between CREATE INDEX and CREATE INDEX CONCURRENTLY in PostgreSQL? - Database Administrators Stack Exchange
Top answer
1 of 2
3

The difference is in "how it works". It is about "How long?" and "Can we block the indexed table changes for the indexing time?". The results are the same.

Any CREATE INDEX should scan its table, sort values from your target field, and store the result.

The regular one LOCKs the table. It means no INSERT, UPDATE, or DELETE query can not be processed until the index creation is finished. On Production, depending on the data amount, it could take a very long time. The change queries can timeout, and a service could be partially inoperable. SELECT queries work normally with this lock. But from the total DB processing time, such a request will be optimal and fast. It takes only 1 full table scan and sorting.

The CONCURRENT one doesn't make a LOCK. So any updates can flow into the table. The change queries will flow as expected. But indexing will take much more time. This operation will take at least 2 full table scans and sorting. If during the operation there were changes then - update the new index data and scan again. This step can be iterated multiple times. So its execution is not optimal and takes much more time and resources, than the base one.

Hope, this will help.

2 of 2
2

The difference is only in the way the indexes are built, but once that is done, the results is the same. A concurrently created index can have some bloat, but that difference will even out after a while.

🌐
PostgreSQL
postgresql.org › docs › current › progress-reporting.html
PostgreSQL: Documentation: 18: 27.4. Progress Reporting
May 14, 2026 - Whenever CREATE INDEX or REINDEX is running, the pg_stat_progress_create_index view will contain one row for each backend that is currently creating indexes.
🌐
Shayon Mukherjee
shayon.dev › post › 2024 › 225 › stop-relying-on-if-not-exists-for-concurrent-index-creation-in-postgresql
Stop Relying on IF NOT EXISTS for Concurrent Index Creation in PostgreSQL
August 12, 2024 - However, this approach can lead ... CREATE INDEX CONCURRENTLY, PostgreSQL first creates an entry for the index in the system catalogs (specifically in pg_index) and marks it as invalid....
Find elsewhere
🌐
DBI Services
dbi-services.com › accueil › create index concurrently in postgresql
Create index CONCURRENTLY in PostgreSQL
November 22, 2017 - One more thing to keep in mind: ... a; INSERT 0 1000000 -- second sessions tries to build the index postgres=# create unique index concurrently i1 on t1(a);...
🌐
Thoughtbot
thoughtbot.com › blog › how-to-create-postgres-indexes-concurrently-in
How to Create Postgres Indexes Concurrently in ActiveRecord Migrations
January 10, 2025 - On a large table, indexing can take hours. However, Postgres has a CONCURRENTLY option for CREATE INDEX that creates the index without preventing concurrent INSERTs, UPDATEs, or DELETEs on the table.
🌐
Reddit
reddit.com › r/postgresql › how to create big indexes
r/PostgreSQL on Reddit: how to create big indexes
June 8, 2024 -

Hello experts,

We have a few tables having size ~5TB and are partitioned on a timestamp column. They have ~90 partitions in them and are storing 90 days of data. We want to create a couple of indexes on those tables. They are getting the incoming transactions(mainly inserts) 24/7 , which are mostly happening on the current day/live partition. Its RDS postgres version 15.4. So in this situation

Should we go with below i.e one time create index command on the table..

CREATE INDEX CONCURRENTLY idx1 ON tab(column_name);
Or
create index on individual partitions from different sessions, say for example create indexes on 30 partitions each from three different sessions so as to finish all the 90 partitions faster?
CREATE INDEX CONCURRENTLY idx1 ON tab_part1(column_name);
CREATE INDEX CONCURRENTLY idx1 ON tab_part2(column_name);..........

Basically I have three questions:
1)If we can do this index creation activity online without impacting the incoming transactions or do we have to take down time for this activity?
2)If we can't do it online then , what is the fastest method to do this index creation activity ?
3)Should we change the DB parameters in a certain way to make the process faster? We have currently set below parameters

max_parallel_workers-16
max_parallel_maintenance_workers-2
maintenance_work_mem- 4GB

Top answer
1 of 2
7
https://www.visuality.pl/posts/indexing-partitioned-table---postgres-stories Like this article (and the docs) say, I'd recommend creating an "invalid" index on the table as a whole using ONLY, then create the indexes concurrently on the partitions and attach one at a time. A bit slower, but much safer. You can use this query to generate the create index and alter index statements for the partitions SELECT nmsp_parent.nspname AS parent_schema, parent.relname AS parent, nmsp_child.nspname AS child_schema, child.relname AS child, 'CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_'|| child.relname || 'on_column_name ON ' || child.relname || '(column_name);' as "idx_create", 'ALTER INDEX idx_table_on_column_name ATTACH PARTITION idx' || child.relname || '_on_column_name;' as "idx_alter" FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace WHERE parent.relname='tablename';
2 of 2
1
We have a similar setup and stop the inserts when we have to create a new index. However that's not too often and the system can catch up afterwards. We have 1 partition a week that's about 2TB per partition We also create the partitions weeks in advance with all the indexes needed so they are ready to go when they become active and start getting inserts. Another big help is if you have a faster disk, you can create a new table space on that disk and have just the most used indexes on that faster disk. Then you can have weekly routines that move the indexes to different tablespaces so the active ones are always on the fast disk. Also, on your inserts, if you are using the copy command, you can get a huge performance increase by preprocessing the csv files so each one only has data for one partition, and then on the copy, have it go directly into correct partition table instead of the main/parent table.
🌐
Alexstoica
alexstoica.com › blog › create-index-concurrently-locks
CREATE INDEX CONCURRENTLY and what locks it requires | Alex’s nuggets of information
April 23, 2025 - The primary benefit is minimizing downtime. By allowing writes during the lengthy index build process, CREATE INDEX CONCURRENTLY is essential for adding indexes to busy tables in production environments without disrupting application availability or performance.
🌐
Hacker News
news.ycombinator.com › item
Don't rely on IF NOT EXISTS for concurrent index creation in PostgreSQL | Hacker News
August 14, 2024 - In my experience on large tables that are “busy”, sometimes indexes need to be added manually first from a utility session, perhaps inside tmux/screen that's detached from while they are created. This could take hours for large tables. Then once done, and the index is valid, an Active Record ...
🌐
CYBERTEC PostgreSQL
cybertec-postgresql.com › home › postgresql: parallel create index for better performance
PostgreSQL: Parallel CREATE INDEX for better performance
October 8, 2024 - The default value here tells PostgreSQL that if the table is sufficiently large, it can launch two workers to help with index creation. To compare a “traditional” way to create the index with the new settings, I have set max_parallel_maintenance_workers to 0.
🌐
Medium
medium.com › dovetail-engineering › how-to-safely-create-unique-indexes-in-postgresql-e35980e6beb5
How to safely create unique indexes in PostgreSQL | by Dovetail Engineering | Dovetail Engineering | Medium
May 23, 2023 - If you can’t prevent duplicates from being created, you’ve already attempted to create an index, or your initial migration failed due to deadlocks, don’t despair and read on. If all else fails, you can force the index to re-calculate by manually running the following query: REINDEX INDEX [CONCURRENTLY] reference_count_unique_index; You can run the query off-peak and re-index the table synchronously, however, that still takes a write lock and carries a risk of user impact. Luckily, PostgreSQL supports re-indexing concurrently - it’s still quite slow, but sometimes it’s the only option.
🌐
OneUptime
oneuptime.com › home › blog › how to create indexes concurrently in postgresql
How to Create Indexes Concurrently in PostgreSQL
January 21, 2026 - Standard Index: 1. Lock table with ... long-running index builds from being killed SET statement_timeout = 0; CREATE INDEX CONCURRENTLY idx_users_email ON users(email);...
🌐
PostgreSQL
postgresql.org › message-id › CAMAof6-=D9jZyBLpbPq7FZJjtuHUtErxyOgAkBkKnVXDj=BDhw@mail.gmail.com
PostgreSQL: CREATE INDEX CONCURRENTLY on partitioned table
October 4, 2024 - "Concurrent builds for indexes on partitioned tables are currently not supported. However, you may concurrently build the index on each partition individually and then finally create the partitioned index non-concurrently in order to reduce ...
🌐
SourceForge
postgres-xc.sourceforge.net › docs › 1_0 › sql-createindex.html
CREATE INDEX
This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, Postgres-XC 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.
🌐
EnterpriseDB
enterprisedb.com › blog › explaining-create-index-concurrently
Explaining CREATE INDEX CONCURRENTLY | EDB
This technical blog explains how CREATE INDEX CONCURRENTLY (CIC) works and how it manages to avoid locking the table from updates. A unique distinguishing factor of CIC is that it can build a new index on the table, without blocking it from updates/inserts/deletes. But even before that, let’s understand how Heap-Only-Tuple (HOT) works. It was a landmark feature added in PostgreSQL 8.3 to reduce table bloat and improve performance significantly.
🌐
GitHub
github.com › prisma › prisma › issues › 14456
Support `CREATE INDEX CONCURRENTLY` (PostgreSQL) · Issue #14456 · prisma/prisma
July 22, 2022 - Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve Migration name: 20220722170033_schema_sync_2022_07 Database error code: 25001 Database error: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E25001), message: "CREATE INDEX CONCURRENTLY cannot run inside a transaction block", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("xact.c"), line: Some(3409), routine: Some("PreventInTransactionBlock") } This is kind of odd to me because I was under the impression that Prisma migrations didn't occur in transactions in Postgres as well.
Author   prisma
🌐
LeanIX Engineering
engineering.leanix.net › blog › postgres-index-creation
PostgreSQL Index Creation - How it works | LeanIX Engineering
April 12, 2024 - For a production system, this means that your application is unable to persist any data until the index build is finished. CREATE INDEX CONCURRENTLY in contrast, builds the index in a concurrent way, so write operations are still allowed, but ...
🌐
Agiliq
agiliq.com › home › blog
Creating concurrent indexes without down time in Django + PostgreSQL
November 3, 2025 - We can create indexes on PostgreSQL concurrently using keyword CONCURRENTLY.
Related queries
flyway create index concurrently
postgres index naming convention
postgres index concurrently
create index concurrently vs create index
create index concurrently cannot run inside a transaction block
liquibase create index concurrently
postgresql create index concurrently on partitioned table
postgres create index concurrently partitioned table