Concurrent PostgreSQL Index Build Handoff for Offshore Maintenance

Start with the query and table, not the index name

Rehearse against representative synthetic volume and a realistic mix of readers and writers. Record server version, table and existing-index definitions, relation sizes, data distribution, transaction age, replication context, maintenance settings, and the exact CREATE INDEX CONCURRENTLY statement. Observe pg_stat_progress_create_index, pg_locks, pg_stat_activity, wait events, application latency, and catalog validity through every phase. Seed a duplicate or expression failure where relevant, cancel during a declared phase, and inspect what remains. A failed concurrent build may leave INVALID catalog state; dropping or retrying it is an owner decision, not automatic cleanup. After success, compare EXPLAIN plans for representative parameter values and confirm the index supports the intended predicate and ordering. Database owners set resource budgets and production timing; the developer supplies measurements, cancellation evidence, cleanup options, and a re-test trigger.

Record the statements the proposed index should help, including their parameter shapes, predicates, ordering, and row limits. Save the current plans and observed work before creating anything. The proposal should explain why existing indexes do not serve those statements. This prevents a handoff from treating successful DDL as the outcome when the planner has no reason to use the new structure.

Build a rehearsal that resembles maintenance

Use synthetic or approved non-production data with comparable volume and distribution. Record PostgreSQL version, table size, index size, write rate, long-running transactions, replica context, maintenance settings, and free storage. Start representative reads and writes before issuing the exact CREATE INDEX CONCURRENTLY statement. A quiet empty table proves syntax, not operational safety.

Keep the SQL immutable in the evidence. Changes to an expression, predicate, collation, operator class, sort direction, included column, or uniqueness requirement can alter both build behavior and planner eligibility. If the review produces a revised definition, treat it as a new fixture and preserve why it changed.

Buyer decision record

Scroll sideways to read every column on a small screen.

Observe every phase and its blockers

Sample pg_stat_progress_create_index, pg_stat_activity, pg_locks, wait events, application latency, replica delay, and storage during the build. Correlate the samples by time. Concurrent creation avoids the ordinary write lock of a standard build, but it still waits for transactions and performs significant work. Report the waits actually observed instead of calling the operation nonblocking.

Introduce one deliberately old transaction in rehearsal. Show which phase waits, what the application experiences, and how the operator identifies the session. The runbook must not assume that terminating a session is authorized. It should state the evidence, owner, and decision needed before any cancellation on a shared system.

Cancel and inspect what remains

Use application-shaped statements with parameters and record generic versus custom plans when that distinction matters. An index that exists and is valid may still be unused because statistics, predicate implication, collation, operator class, ordering, or selectivity differs from the proposal. Confirm write amplification and replica apply behavior during a sustained sample, not only build completion. If the index is partial, seed rows just inside and outside its predicate. If it is unique, stage the duplicate case and document business cleanup ownership. Schedule ANALYZE only when justified and separately approved. The final recommendation names the queries helped, those unchanged, new maintenance cost, and the condition that would make removal appropriate.

Cancel during a declared phase and inspect the catalog afterward. Record validity, readiness, size, locks, and write overhead. If an INVALID index remains, demonstrate the reviewed DROP INDEX CONCURRENTLY cleanup on the synthetic fixture. Do not hide this case by deleting the test database immediately after cancellation. It is the evidence that tells an operator what a failed production attempt would leave behind.

Exercise uniqueness and predicates honestly

For a unique index, seed a duplicate that represents the business conflict and capture the resulting state. Assign cleanup of the underlying data to the data owner; the developer should not silently choose a winning row. For a partial index, seed rows on both sides of its predicate and use queries that do and do not imply that predicate. The planner cannot use an index simply because a reviewer knows two business descriptions are related.

Expression indexes need the same precision. Record the function definition, collation, and application expression. A harmless-looking rewrite in query code can stop matching the indexed expression. Add that neighboring query to the regression corpus rather than promising that all similar searches benefit.

Check plan adoption and ongoing cost

After a successful build, run EXPLAIN for the preserved statement corpus with representative parameter values. Note generic and custom plans when prepared statements make that distinction relevant. Compare rows, buffers, sort work, and elapsed time carefully; a short isolated run does not establish production latency. State which statements improved, which did not change, and which regressed.

Measure writes during a sustained sample and inspect replica behavior. Every additional index adds maintenance work even when reads improve. Include the index in bloat, vacuum, backup, and schema ownership procedures. Define a later review trigger based on query use and write cost rather than leaving the index indefinitely because its build once succeeded.

Schedule only from complete evidence

Ready means the valid index builds within the approved resource envelope, intended plans can use it, cancellation leaves a known recoverable state, and cleanup is rehearsed. Invalid catalog entries, unexplained blocking, replica risk, or plan regressions prevent scheduling the production change.

The database owner receives the exact DDL, baseline and post-build plans, progress timeline, workload measurements, cancellation result, invalid-index cleanup, uniqueness or predicate fixtures, replica observations, storage requirement, and rollback conditions. The owner chooses the production window and any session intervention. The developer may prepare and rehearse the change, but cannot translate a clean rehearsal into permission to run maintenance on production.

Questions about assessing Philippine developers