skip to content

You need to add an index to a 4 TB partitioned table with 200 partitions on a live production system, without taking a long lock on the whole table. How would you build it?

level: seniorimportance: should knowfreq 33%

answer

  1. CONCURRENTLY / ONLINE, one partition at a time
  2. CREATE INDEX ON ONLY parent = invalid placeholder
  3. ALTER INDEX ... ATTACH PARTITION
  4. parent flips valid when all attached
  5. failed concurrent build ⇒ INVALID index, drop and retry

basics

~20 s

Build it partition by partition. Create the index concurrently/online on each partition, create the parent index as an empty invalid placeholder, then attach each partition index to it; the parent becomes valid once all are attached. Work is resumable, bounded per partition, and can skip cold partitions.

solid answer

~50 s

Declaring the index on the parent in one statement locks the whole table for the entire build — unacceptable at 4 TB. The standard technique is a per-partition build: 1. `CREATE INDEX CONCURRENTLY` (PostgreSQL) / `ONLINE` (Oracle, SQL Server) on **one partition at a time**, in batches, watching for long-running transactions that stall concurrent builds. 2. Create the parent index with `ON ONLY` — it is created invalid and holds no data, but a brief lock. 3. `ALTER INDEX parent ATTACH PARTITION child_idx` for each partition. The parent flips to valid automatically once every partition has attached an equivalent index. Things I'd plan for: each concurrent build is two table passes and generates WAL/redo, so throttle and stagger; a failed concurrent build leaves an INVALID index that must be dropped and retried; disk headroom for 4 TB of new index; and the option to index only hot partitions if older ones are never queried on that column. Once the parent index exists, all future partitions inherit it automatically.

code

sql · 8 lines
sql
CREATE INDEX events_cust_idx ON ONLY events (customer_id);

CREATE INDEX CONCURRENTLY events_2026_01_cust_idx
  ON events_2026_01 (customer_id);
ALTER INDEX events_cust_idx
  ATTACH PARTITION events_2026_01_cust_idx;

-- repeat per partition; parent becomes valid when all are attached

go deeper

for a junior

Know that the online/concurrent index option exists and that per-partition work is possible because each partition has its own index.

for a middle

Describe the three-step pattern — placeholder parent index, per-partition concurrent builds, attach — and why it avoids a long global lock.

for a senior

Own the runbook: throttling, replica lag as the stop signal, blocking old transactions, invalid-index cleanup, disk headroom, and resumability.

for a principal

Position it as a change-safety property of the schema: partitioning makes large structural changes incremental and reversible, and you should choose partition granularity partly so that any single unit of maintenance fits inside one operational window.

## Why the naive statement is unacceptable A single `CREATE INDEX ON events (customer_id);` against a partitioned parent in PostgreSQL takes a lock that blocks writes on the whole table and holds it while all 200 partitions are indexed — potentially many hours on 4 TB. It is also all-or-nothing: a failure at partition 190 rolls back everything. ## The per-partition pattern The point of local indexes is that each partition's index is an independent object. So build them independently and *then* tell the parent about them. **Step 1 — build on each partition, online.** PostgreSQL's `CREATE INDEX CONCURRENTLY` avoids blocking writers by doing two passes over the table plus waiting for older transactions; Oracle uses `CREATE INDEX ... LOCAL ONLINE`; SQL Server uses `WITH (ONLINE = ON)` (edition-dependent). Do partitions one or a few at a time so that at any moment only a small fraction of the table is under build pressure. **Step 2 — declare the parent index without building.** In PostgreSQL, `CREATE INDEX ON ONLY events (customer_id);` creates an invalid parent index instantly, holding no data and taking only a brief lock. **Step 3 — attach.** `ALTER INDEX events_customer_idx ATTACH PARTITION events_2026_01_customer_idx;` is a catalog operation. When every partition has attached a matching index, PostgreSQL marks the parent valid and the planner starts using it as a partitioned index. From then on, any partition created or attached inherits the index automatically. ## Operational details that separate a real answer from a textbook one - **Concurrent builds are not free.** Each is roughly two full passes over the partition plus index write amplification; expect elevated I/O, WAL/redo volume, replication lag on standbys, and pressure on backup windows. Throttle: N partitions per window, and monitor replica lag as the stop signal. - **Long-running transactions stall concurrent builds.** PostgreSQL's `CREATE INDEX CONCURRENTLY` waits for transactions older than its snapshot to finish before it can complete; a stuck analytics query or an idle-in-transaction session will pin it indefinitely. Check for them first and set a statement/idle timeout for the build window. - **Failure leaves debris.** A cancelled or failed concurrent build leaves an `INVALID` index that still costs write maintenance but is never used for reads. Detect them (`pg_index.indisvalid = false`), drop them concurrently, and retry. Build this check into the runbook, not into hindsight. - **Disk headroom.** Index on a 4 TB table can be hundreds of GB. Confirm free space, and remember that concurrent builds temporarily hold both old and new structures during rebuild scenarios. - **Resumability is the big win.** Because each partition is a separate statement, the job survives being interrupted; you record which partitions are done and pick up where you left off. A single monolithic build has none of that. - **Selective indexing.** With local indexes you can legitimately decide not to index cold partitions at all — e.g. index only the last 6 months if the query always filters on a recent date range. The catch is that the parent index stays invalid until all partitions attach, so either accept per-partition indexes without a parent declaration (query the partitions directly, or accept that the planner still uses each child index when scanning that child), or index everything. Know your engine's exact behaviour here before promising it. - **New partitions.** Once a valid parent index exists, `CREATE TABLE ... PARTITION OF` builds the index as part of partition creation. For a large pre-loaded partition, build indexes on the standalone table *before* `ATTACH` so attach stays metadata-only. ## Oracle / SQL Server variants Oracle can create the index `UNUSABLE` first (instant, no data) and then `ALTER INDEX ... REBUILD PARTITION p ONLINE PARALLEL n` partition by partition — the same shape: cheap declaration, incremental population. SQL Server supports partition-level online index rebuilds; creating an aligned index can be done with `ONLINE = ON` and `RESUMABLE = ON`, which gives explicit pause/resume. ## The summary sentence "Never build a multi-terabyte index as one statement. Build per partition, online, throttled and resumable; declare the parent index as an empty placeholder; attach the finished per-partition indexes and let the parent flip to valid."

  • A `CREATE INDEX CONCURRENTLY` has been running for hours with no progress. What do you check?
    Old transactions. The concurrent build must wait for transactions older than its snapshot to finish before each phase completes, so a long analytics query, an idle-in-transaction session, or a stuck prepared transaction will pin it indefinitely. I would look at the activity view for long-running or idle-in-transaction backends, and on a primary with hot-standby feedback also check replica queries. Fixing it means terminating the blocker or scheduling the build in a window with a statement timeout in place.
  • Why does building indexes on a staging table before ATTACH matter?
    Attach must ensure the incoming partition has an index matching every parent index; if one is missing the engine builds it while holding a lock on the partitioned table, which is exactly the stall you were trying to avoid. Building them on the standalone table first — where nothing else is reading it — turns attach into a metadata-only operation of milliseconds.

saying these in an interview costs you the question

  • Proposing a single CREATE INDEX on the parent and calling it online.
  • Not knowing that a failed concurrent build leaves an unusable index that still slows writes.
  • Ignoring WAL/redo volume and the replication lag a bulk index build causes.
  • Assuming CREATE INDEX CONCURRENTLY never blocks — it waits on old transactions and can stall forever.
  • Forgetting to pre-build indexes on a table before attaching it as a partition.

context

resolving links…