Build or rebuild a PostgreSQL index on a production table without blocking writes using CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY

Documented steps

1. Run CREATE INDEX CONCURRENTLY idx_name ON table_name (column); — normal INSERT/UPDATE/DELETE on the table continue while the index builds.
2. Run it as a standalone statement with autocommit: CREATE INDEX CONCURRENTLY cannot be executed inside a transaction block (so not inside BEGIN, and not via migration tools that wrap statements in a transaction).
3. Monitor progress: SELECT * FROM pg_stat_progress_create_index;
4. If the build fails or is cancelled, an INVALID index is left behind. Detect it with \d table_name in psql (the index is marked INVALID) or: SELECT c.relname FROM pg_class c JOIN pg_index i ON c.oid = i.indexrelid WHERE NOT i.indisvalid;
5. Recover per the docs' recommendation: DROP INDEX the invalid index and retry CREATE INDEX CONCURRENTLY; alternatively rebuild it in place with REINDEX INDEX CONCURRENTLY idx_name;
6. To rebuild an existing bloated index without blocking writes, use REINDEX INDEX CONCURRENTLY idx_name; (or REINDEX TABLE CONCURRENTLY table_name for all its indexes).
7. A larger maintenance_work_mem speeds up the build.
8. Official docs: https://www.postgresql.org/docs/current/sql-createindex.html and https://www.postgresql.org/docs/current/sql-reindex.html