Your PostgreSQL concurrent index build has been waiting, and the warehouse app still needs to record shipments. If the migration runner reports a timeout, you have to decide whether to give the build more time, cancel it, or approve another attempt. First find out what the database is doing and whether the application can afford to let it continue.
Suppose the slow screen lists recent shipments for a warehouse. The migration proposes a non-unique B-tree index named shipments_warehouse_shipped_idx on app.shipments (warehouse_id, shipped_at DESC). The table is non-partitioned. Use a separate procedure for a partitioned table or an index that enforces uniqueness. Check the documentation for your server's major version before using this procedure. If the application still runs on PostgreSQL 14, use the PostgreSQL 14 upgrade and rollback guide to plan its move to a supported major version. The examples follow the PostgreSQL 18 release series.
Set the limits before running CREATE INDEX CONCURRENTLY
CREATE INDEX CONCURRENTLY allows ordinary writes to continue during the build. It scans the table twice and can slow the application by adding CPU and I/O load. PostgreSQL allows one concurrent index build at a time on the same table. Check that the migration runner can run this statement outside a transaction block. PostgreSQL CREATE INDEX documentation
Record the latest time you are willing to let the build run and who can call it off. Use your existing service limits for shipment write latency, failed writes and lock queues. Include a minimum free disk threshold and a replication lag limit if the app relies on replicas. Leave time in the window to verify the outcome and decide what to do if cleanup is needed. The same ownership and cutoff belong in a cutover plan with rollback.
Read the phase and decide whether to wait
Run this from a separate monitoring session connected to the same database on the server running the migration. Take each sample in a fresh transaction. The query joins index progress to session activity for the process the build is currently waiting on, when reported. The backend running the build needs track_activities enabled to report progress. Use an authorized monitoring role with visibility into the relevant sessions, such as one with pg_read_all_stats privileges. PostgreSQL index progress view, progress reporting implementation, monitoring documentation
SELECT p.pid AS build_pid, p.command, p.index_relid, p.phase,
p.blocks_done, p.blocks_total, p.lockers_done, p.lockers_total,
p.current_locker_pid, a.application_name, a.state,
a.xact_start AS locker_transaction_started
FROM pg_stat_progress_create_index AS p
LEFT JOIN pg_stat_activity AS a ON a.pid = p.current_locker_pid
WHERE p.datname = current_database()
AND p.relid = 'app.shipments'::regclass;
Identify the migration by its backend PID and command. Use successive samples of blocks_done and blocks_total only during a build scan that reports block progress or during the index and table scans in validation. During writer and old-snapshot waits, use lockers_done, lockers_total and current_locker_pid when available. If a locker is reported, check how long its transaction has been open. An older snapshot can hold up completion after validation finishes scanning the table. Ask the session owner when their transaction is likely to finish. PostgreSQL index build phases
Wait while the application stays within its limits and there is a credible path to finishing before the cutoff. If the build is waiting on a warehouse import and its owner expects it to commit shortly, ask when they can confirm and set the next check.
Stop when an agreed limit is breached or the cutoff arrives. If a blocker has no owner or credible end time, escalate while there is still time to decide. Before the operator requests cancellation, confirm the backend still belongs to the migration and is running that build. PostgreSQL's pg_cancel_backend targets the session's current query. To signal a non-superuser migration backend, the operator can use a superuser or a role with the privileges of the migration's role or pg_signal_backend. Canceling a superuser's migration backend requires a superuser. A successful signal still leaves you to verify how the statement ended. Let the blocking session's owner decide how to end their transaction. PostgreSQL server signaling documentation
Check what remains before approving a retry
An empty progress result is a cue to check the migration session and logs. Confirm the database command ended, especially if the runner lost its connection or reported a timeout. Keep other migration jobs on this table paused while you check and act. Treat the following catalog result as evidence for a retry decision only after ruling out an active attempt. A concurrent build can create an invalid entry before it finishes. PostgreSQL CREATE INDEX documentation
SELECT target.named_relation, i.indrelid::regclass AS indexed_table,
pg_get_indexdef(i.indexrelid) AS index_definition,
i.indisvalid, i.indisready, i.indislive, i.indisunique,
i.indisreplident,
EXISTS (SELECT FROM pg_constraint AS c
WHERE c.conindid = i.indexrelid) AS supports_constraint
FROM (VALUES (to_regclass('app.shipments_warehouse_shipped_idx')))
AS target(named_relation)
LEFT JOIN pg_index AS i ON i.indexrelid = target.named_relation;
Use the returned table and definition to identify the exact object. indisvalid says whether the index is valid for queries. indisready says whether inserts and updates maintain it, so an invalid index with indisready = true can still add write overhead. indislive = false marks an index being dropped. The query also checks whether the index is replica identity or supports a constraint. PostgreSQL index catalog, constraint catalog, relation lookup and index definitions
- Name is free (
named_relationis null). Review the server error and resolve the cause. Approve another attempt only when there is time and capacity to run it. - Valid, live index matches the migration. Keep it and move on to the query check. Reconcile the migration record if the runner reported a failure.
- Invalid index matches the migration. Once the operator confirms the index is live, standalone and left by the failed build, they can approve removal. Have the index owner run
DROP INDEX CONCURRENTLYwith the schema-qualified name. This command can itself wait for conflicting transactions. It must run outside a transaction block. Verify the name is free after the drop. Address the original failure before approving a retry.IF NOT EXISTScan skip a same-name relation without checking its definition or validity. PostgreSQL DROP INDEX, CREATE INDEX - Unexpected definition, name collision, constraint, replica identity or drop in progress. Pause and have the database owner resolve the state before approving any change. For example, a populated
named_relationwith noindexed_tablemeans the name is taken by another kind of relation.
Verify the shipment query after the build
Capture the plan for the actual application query before the migration. After the build, repeat it from a fresh session with the same query shape and representative parameters. This example uses the fictional warehouse 42 and a page size of 50. Plain EXPLAIN shows the planned execution. EXPLAIN ANALYZE runs the query, so use it only where the operator has approved the added load. PostgreSQL EXPLAIN documentation
EXPLAIN
SELECT shipment_id, shipped_at
FROM app.shipments
WHERE warehouse_id = 42
ORDER BY shipped_at DESC
LIMIT 50;
Look for the intended index in the plan and compare observed application latency under comparable traffic. PostgreSQL can choose another plan if it estimates that plan will cost less. If the new index goes unused or the screen stays slow, check representative parameter values and row estimates before approving more schema changes. PostgreSQL guide to reading query plans
Add the cutoff, the decision owner and the before-and-after query check to the migration ticket. If the build breached a service limit, use the same ticket to capture the timeline and assign a follow-up with a short incident review for internal systems.
Sources
- PostgreSQL Global Development Group, PostgreSQL 18 release series (2025). Release 18
- PostgreSQL 18 documentation (2025). CREATE INDEX, Building Indexes Concurrently, DROP INDEX
- PostgreSQL 18 documentation (2025). CREATE INDEX Progress Reporting, Monitoring Statistics, Server Signaling Functions
- PostgreSQL 18 source code (2025 release series). Command progress reporting implementation
- PostgreSQL 18 documentation (2025). pg_index, pg_constraint, System Information Functions
- PostgreSQL 18 documentation (2025). EXPLAIN, Using EXPLAIN
