Indexing Strategy and Design Questions

Direct answer: In any horizontally-partitioned database, a secondary index is a second, independently-keyed data structure: its rows are ordered by the indexed column, so they almost always land on different partitions than the base row they point back to. Every write to an indexed column therefore has to touch at least two partitions instead of one, and if you want that index to return transactionally correct results, the database has to pay for cross-node coordination on every single write, not just occasionally. CockroachDB and Spanner both choose to pay this cost synchronously (the index write is part of the same distributed transaction as the base-row write), which is exactly what buys you a secondary index whose reads you can trust; systems that instead update indexes asynchronously trade that trust for lower write latency and more availability under partition.

The general cost, before any vendor specifics

This is true of secondary indexes in ANY distributed database, not just CockroachDB or Spanner: maintaining one is expensive because it is a second write path that has to stay correct relative to the first. That cost shows up as three concrete things:

• Write amplification. One logical write to the base row becomes N physical writes: one to the base table, one per secondary index that covers a changed column.
• Cross-node coordination. If the index entry and the base row live in different partitions (the normal case), keeping them consistent needs a distributed transaction, a locking/intent protocol, or an eventual-consistency compromise.
• Read amplification when the index isn't covering. A non-covering index gives you a matching key, not the full row, so a query often pays for an index lookup AND a follow-up fetch of the base row by primary key.

When you'd avoid a secondary index on a distributed table entirely

Given that cost, the honest "don't" cases are: (a) a column that's written far more often than it's queried by, where the write amplification dwarfs the read benefit, particularly under high write throughput per node; (b) a query pattern that can be served by denormalizing the needed columns directly onto the base row or into a separate purpose-built table instead (an async projection, a change-feed into a search system like Elasticsearch/OpenSearch, or a materialized summary table), decoupling the write-hot path from the index-maintenance cost entirely; and (c) a column whose selectivity is so low (e.g., a boolean status flag) that an index barely narrows the scan, so a full or partial scan is cheap enough not to be worth the write cost. In all three cases the fix is the same shape: stop trying to make the base table serve every access pattern, and let a second, purpose-built and separately-scaled structure absorb the query instead of a tightly-coupled secondary index.

CockroachDB and Spanner specifics

Physical layout. In both systems a secondary index is not a pointer structure bolted onto the table; it is itself a distinct, independently-partitioned keyspace, sharded and distributed across nodes exactly like a table is. An index row's key is built from the indexed column(s) (plus, if not unique, the primary key to disambiguate), and its value holds either nothing (a bare index, requiring a lookup back to the primary key for other columns) or the extra columns you name in a STORING clause (CockroachDB) / STORING(GoogleSQL)-INCLUDE(PostgreSQL-dialect) clause (Spanner). A STORING clause turns the index into a covering index for queries that only need the stored columns: the read is satisfied from the index alone, with no extra round trip to fetch the base row, which is the main lever you have for cutting the read-amplification cost above.

Transaction implications. Both systems fold index writes into the SAME distributed transaction as the base-row write. CockroachDB calls its partition unit a "range" and Spanner calls its "split"; both are just the vendor-specific name for the same idea this answer has been calling a partition, the physical chunk of sorted data that gets replicated and moved as one piece. In CockroachDB, each table and each index lives in one or more Raft-replicated ranges (Raft is a consensus protocol: a way for multiple replicas holding the same data to agree on the order of writes and elect a leader, even when some replicas are slow or unreachable), and a multi-range write goes through the transaction coordinator, which writes provisional "intents" across every affected range and only resolves them atomically at commit; the underlying per-range consensus is Raft. In Spanner, commits across the ranges (splits) touched by a transaction use two-phase commit (2PC: the coordinator first asks every participant "can you commit this?" and only tells them to actually commit once every participant has said yes, so a transaction spanning multiple partitions either commits everywhere or nowhere) coordinated with TrueTime, Google's globally-synchronized clock service, which lets Spanner assign a commit timestamp that every replica can agree on without extra communication just to establish ordering. Either way, correctness costs coordination: you cannot get a transactionally-consistent secondary index in a distributed table for free.

Coordination across nodes and write amplification. Because base row and index row are usually on different ranges/splits (unless deliberately colocated), a single-row UPDATE on an indexed column becomes at minimum two range writes, more if multiple indexes cover the changed column, each requiring intent-write-then-resolve (CockroachDB) or inclusion in the same 2PC commit (Spanner). Spanner has a specific mitigation for this: INTERLEAVE IN on the index lets you colocate the index's rows with an ancestor table's rows in the same split, so that when the index is interleaved in an ancestor of the base table, the index write becomes a LOCAL operation instead of a cross-split one, avoiding the two-phase commit overhead entirely for that write.

Multi-region write latency, and the eventual-consistency trade-off. When the ranges or splits touched by a transaction (base row plus every indexed column's index entry) span regions, the commit has to wait on cross-region round trips and quorum acknowledgment from replicas in those regions, regardless of how fast any single node is. Spanner's TrueTime bounds the UNCERTAINTY in that commit's timestamp, it does not remove the network latency of reaching a remote replica. CockroachDB can place replicas and leaseholders (the one replica of a range currently responsible for serving that range's reads and writes) to keep hot ranges local to where they're written most, but a write that must update an index range in another region still pays that region's round-trip time. This is the real lever for the "multi-region low-latency reads" trade-off: both systems let you trade strict, transactionally-fresh secondary-index reads for latency by reading with bounded/stale staleness (Spanner's stale reads, CockroachDB's AS OF SYSTEM TIME / follower reads) from a nearby replica instead of the current leaseholder. That gets you a fast local read of a secondary index at the cost of it possibly being a few hundred milliseconds to a few seconds out of date, an explicit eventual-consistency trade you opt into per query rather than a property of the index itself.

Trade-offs and pitfalls

• Minimize the number of secondary indexes on any table with a high write rate; each one is a proportional tax on every write to a covered column, not a one-time cost.
• Prefer covering (STORING) indexes for read-heavy queries specifically to avoid the extra primary-key fetch, but remember every stored column also has to be kept in sync on every write, so storing everything defeats the purpose.
• Use locality (Spanner interleaving, CockroachDB replica/leaseholder placement) to keep an index's writes local to where its base rows are written, before reaching for staleness as the fix.
• For extremely high write throughput where even synchronous, well-placed index maintenance is too expensive, the honest answer is often "don't index it in the primary OLTP store": stream changes out (CDC, a change feed) to an external, independently-scaled search or analytics system instead, and accept that its results are eventually consistent by design rather than accidentally so.

Direct answer

Build the index with CREATE INDEX CONCURRENTLY, which avoids the lock that a plain CREATE INDEX takes against writes, verify it actually finished valid (a concurrent build can fail partway through and leave an unusable index behind, silently, with no error surfaced anywhere else), replay real production query samples against it with EXPLAIN to confirm the plans you expect before relying on it, and keep a rollback plan (DROP INDEX) ready the whole time. I ran both the successful and the failure path against a live Postgres 16 instance, not just the happy path, because the failure path is the part most rollout plans skip.

The rollout plan

flowchart TD
    A[CREATE INDEX CONCURRENTLY] --> B{Build succeeded?}
    B -- No, error mid-scan --> C[Invalid index left behind]
    C --> D[DROP INDEX CONCURRENTLY]
    D --> A
    B -- Yes --> E[Check pg_index.indisvalid = true]
    E --> F[Replay top production queries via EXPLAIN]
    F --> G{Plans match expectations, no regressions?}
    G -- No --> H[Investigate; index may need a different shape]
    G -- Yes --> I[Roll out to read traffic; monitor p95 and error rate]
    I --> J[Old index, if replacing one, dropped last, one at a time]

1. Build without blocking writes. A plain CREATE INDEX takes a lock that blocks inserts, updates, and deletes on the table for the whole build (reads still proceed). CREATE INDEX CONCURRENTLY builds the index without that lock, at the cost of requiring two full table scans instead of one, and waiting for concurrent transactions to finish between them, so it takes longer and does more total work.
2. Verify the build actually completed valid, don't assume it. If a concurrent build hits a problem mid-scan (most commonly a deadlock, or a uniqueness violation for a unique index), the command fails but leaves an INVALID index behind: it still exists, still costs write overhead to maintain, but is ignored for query planning. Nothing else in the system flags this; you have to check for it.
3. Confirm correctness with real query replay, not just an existence check: pull representative queries via pg_stat_statements or a query log and run EXPLAIN against them to confirm the plan actually changes the way you expect, before treating the index as "ready."
4. Apply new traffic and watch, then finish the cutover. If this index is replacing an existing one, keep the old one in place through an observation window and drop it last, separately, not in the same change as the new build.

Worked example

CREATE INDEX CONCURRENTLY idx_orders_plain_status ON orders_plain (status);
SELECT indexrelid::regclass, indisvalid, indisready FROM pg_index
WHERE indexrelid = 'idx_orders_plain_status'::regclass;

Real result: idx_orders_plain_status | t | t. That's the success path.

For the failure path, I forced a real one rather than describing it hypothetically: building a unique index concurrently against a column that actually has duplicate values.

CREATE UNIQUE INDEX CONCURRENTLY idx_customers_country_unique ON customers (country);

Real result: ERROR: could not create unique index "idx_customers_country_unique", DETAIL: Key (country)=(GB) is duplicated. Checking the catalog immediately after:

SELECT indexrelid::regclass, indisvalid, indisready FROM pg_index
WHERE indexrelid::regclass::text = 'idx_customers_country_unique';
-- idx_customers_country_unique | f | f

The failed index is still there, indisvalid = false, consuming write overhead on every future insert to customers while contributing nothing to any query, until it's explicitly cleaned up:

DROP INDEX CONCURRENTLY idx_customers_country_unique;

That's the real mechanism behind "verify the new index is correct before relying on it": success and failure both leave something behind, and only checking pg_index.indisvalid distinguishes them.

Choosing the index type itself, on a table that serves both live traffic and analytics

"Zero-downtime rollout" also covers picking the right structure, not just building it safely. A plain B-tree is the right default for selective lookups and range queries. On a large, naturally time-ordered table that also gets scanned for occasional analytics queries (a table both serving live OLTP, online transaction processing, lookups and occasional large reporting scans), a BRIN index (Block Range Index, a much smaller structure that stores the minimum and maximum value per block range instead of one entry per row) is worth considering instead of, or alongside, a B-tree for the analytics-facing column: it's a fraction of a B-tree's size and cheap to maintain, at the cost of only working well when the column is well-correlated with physical row order, which a naturally time-ordered insert pattern usually gives you for free. Whichever type you pick, a table under this kind of mixed workload also needs autovacuum (Postgres's background process that reclaims space left behind by updated or deleted rows, so the table doesn't grow forever) tuned more aggressively than the default (lower autovacuum_vacuum_scale_factor, so it triggers on a smaller fraction of changed rows), since a table that's both written to continuously and read by long analytics scans accumulates bloat (dead, not-yet-reclaimed row versions taking up space in the table's physical pages) that a default, infrequent autovacuum schedule won't keep up with, and bloat degrades exactly the kind of block-range correlation a BRIN index depends on.

Trade-offs and pitfalls

CREATE INDEX CONCURRENTLY is not actually free of risk just because it doesn't block; it takes meaningfully longer (two full scans plus waiting on concurrent transactions), so a failure partway through is more likely on a large, busy table than on a quiet one, and every rollout plan needs the indisvalid check as a standard step, not an afterthought for when something looks wrong. If the new index is on a computed or functional expression involving text sorting, there's a subtler risk worth naming: text sort order depends on the operating system's collation library (glibc or ICU), and Postgres tracks a collation version specifically because an OS library upgrade can silently make an already-built index inconsistent with the actual current sort order; it only warns about this mismatch (collation "..." has version mismatch) rather than preventing it up front. A replica or an ETL (extract-transform-load) environment provisioned on a different OS image than production can compute "the same" expression's sort position differently for exactly this reason, so validating a new expression index includes confirming collation versions agree everywhere that depends on the same ordering, not only on the primary where you ran the build.

Direct answer

VACUUM reclaims space made reusable by dead row versions in place, without rewriting the table or its indexes, so it's low-lock but does not shrink the file on disk; REINDEX (and VACUUM FULL for the table itself) fully rewrites the structure into a new, compacted file, which does shrink it back down, at the cost of a much heavier lock. That maps directly onto the REORGANIZE-versus-REBUILD choice: schedule VACUUM (or let autovacuum handle it) continuously, right after the nightly load, and reserve a full REINDEX/VACUUM FULL for an occasional, deliberately scheduled maintenance window, not every night, because it locks the table for its duration.

What each operation actually does

• VACUUM scans the table, marks the space used by dead row versions (rows superseded by an UPDATE or removed by a DELETE, and no longer visible to any transaction) as reusable by future writes, and removes the now-unreferenced entries from every index on the table. It does this with a lock level that still allows normal reads and writes to proceed. It generally does not return space to the operating system; the table file stays the same size, just with more free space inside it available for future rows.
• REINDEX rebuilds an index from scratch into a new physical structure, discarding the old one; this both removes any accumulated bloat in that index and produces a compact result, but it requires more work and (for a plain REINDEX, as opposed to REINDEX CONCURRENTLY) locks out writes to the table for its duration.
• VACUUM FULL does the table-level equivalent: rewrites the entire table into a new file, reclaiming all dead space and shrinking the file on disk, but it takes an ACCESS EXCLUSIVE lock, blocking all reads and writes for the duration, the heaviest-weight option here.

Worked example

A transactions table (3,000,000 rows) with several indexes, one of them a composite (status, amount) index. Before any updates: table 162 MB, idx_full_status_amount 97 MB.

UPDATE transactions SET amount = amount + 0.01 WHERE id % 5 = 0;  -- 600,000 rows

After that update: table grew to 193 MB, n_dead_tup = 600000, and the index grew to 192 MB, nearly doubling, because amount is part of that index's key, so every updated row needs a fresh index entry (this disqualifies Postgres's Heap-Only-Tuple fast path, which only applies when no indexed column changes).

VACUUM (VERBOSE) transactions;

After VACUUM: n_dead_tup back to 0 (real log line: tuples: 600000 removed, 3000000 remain), but table size is still 193 MB and the index is still 192 MB. VACUUM reclaimed the space for reuse; it did not shrink the files. This is exactly the REORGANIZE shape: low-impact, but the bloat doesn't visibly go away in pg_relation_size until something rewrites the structure.

REINDEX INDEX idx_full_status_amount;
VACUUM FULL transactions;

After that: table back down to 161 MB, index back down to 97 MB, both essentially their pre-bloat size. That's the REBUILD shape: full reclaim, at the cost of an exclusive lock while it runs.

When to schedule each, for a 200-million-row nightly-loaded reporting table

At a much larger scale, say a 2 TB version of this same reporting table, a single VACUUM FULL/REINDEX pass against the whole table stops being practical: the maintenance window needed to lock the entire table for that long rarely exists on a table people query throughout the day. If the table is partitioned (by load date, most naturally for a nightly-loaded reporting table), the practical version of REBUILD is a rolling one: rebuild one partition at a time, oldest or least-queried first, each on its own short-lived lock, rather than the whole table under one long one. That also turns "when do we schedule the disruptive option" into a conversation with report consumers ahead of time (which partition, what night, how long), not a surprise outage; naming the affected partition and window explicitly is what makes that stakeholder conversation concrete instead of a vague "there might be a slow night sometime."

Trade-offs and pitfalls

Running a full rebuild every night "just to be safe" defeats the purpose of having a reporting table available for reports; the whole point of VACUUM's low-lock design is that it can run continuously without taking the table offline, so lean on that for routine maintenance and treat REINDEX/VACUUM FULL as an exception, not a nightly habit. The bigger operational trap is the opposite: assuming VACUUM running successfully means the table is "clean," when pg_relation_size staying flat or growing despite regular VACUUM runs is itself a signal that write volume is outpacing vacuum's reclaim rate, which calls for tuning autovacuum aggressiveness (or fillfactor, to leave room for in-place updates) rather than reaching straight for a disruptive full rebuild.

Direct answer

An index that degrades a frequently-updated table almost always shows up as elevated write latency, not read latency, so the diagnostic checklist starts on the WRITE path: confirm the timing of the degradation lines up with when the index was added (deployment logs, pg_stat_user_indexes.indexrelid and its relation's creation time), then measure whether INSERT/UPDATE latency on the affected table actually rose, whether WAL (write-ahead log) volume rose in step, and whether the new index introduced lock contention (long-held locks, or frequent page-split-induced locks) that wasn't there before. Only after confirming the write path is genuinely worse do you decide whether to drop the index outright, rebuild it with a different fillfactor to reduce page-split frequency, or make it partial (indexing only the subset of rows that actually need it) to cut its write cost without losing all its read-side value.

Structured elaboration

Diagnostic checklist, roughly in the order to work through it:

1. Correlate timing. Does the metrics regression start at (or shortly after) the index's creation timestamp? A regression that predates the index, or that tracks a separate deploy or traffic change, isn't this index's fault, and chasing it here wastes the incident.
2. Confirm write latency actually moved. Check pg_stat_statements for the table's INSERT/UPDATE statements: did mean_exec_time or total_exec_time rise after the index went in, comparing the same time-of-day/day-of-week window to control for normal traffic variation?
3. Check WAL volume. Every extra index means extra WAL records per write. A rise in pg_stat_wal bytes generated, or in replication lag on any replicas, that lines up with the deploy is strong corroborating evidence, especially useful when application-level latency metrics are noisy.
4. Check index bloat and maintenance load. A newly-added index on a hot, frequently-updated column can accumulate dead tuples and page-split-driven bloat fast; pgstatindex() (from the pgstattuple extension) reporting rising leaf_fragmentation and falling avg_leaf_density over the incident window supports "this index is thrashing," and rising autovacuum activity on the table (visible in pg_stat_user_tables.last_autovacuum frequency, or autovacuum logs) is a related symptom, since a freshly bloating index makes every subsequent vacuum pass more expensive too.
5. Check for lock contention specifically. pg_locks joined against pg_stat_activity during a live incident, or historical wait-event sampling if you have it, to see whether writers are queuing behind index-page locks that didn't exist before.
6. Quantify before deciding. Once you have a real before/after latency delta attributable to the index, weigh it against the read-side benefit it was added for: if the write regression clearly outweighs the read win, or if the read win never actually materialized in production the way it did in testing, that's the case for rolling back.

Worked example

A concrete, measured illustration of how quickly write cost compounds as indexes are added to the same table (Postgres 16, bulk-loading 500,000 rows into an otherwise identical table, isolating the marginal cost of secondary indexes from base insert cost):

0 secondary indexes (only the primary key):  522.1 ms  (baseline)
+1 secondary index:                          899.6 ms  (+377.5 ms, ~0.755 microseconds/row)
+3 secondary indexes:                       1852.2 ms  (+1330.1 ms total, ~0.887 microseconds/row/index average)

rows = 500_000
t0, t1, t3 = 522.129, 899.582, 1852.243     # median ms, measured

per_row_us_1idx = (t1 - t0) / rows * 1000
per_row_us_3idx_total = (t3 - t0) / rows * 1000
print(per_row_us_1idx, per_row_us_3idx_total / 3)
# 0.755, 0.887 -- close agreement, real evidence the cost scales close to linearly per index

The near-linear scaling (about 0.76 to 0.89 microseconds/row per extra index, consistently, whether one or three are added) is itself a useful diagnostic fact: in a genuine regression, if measured write-cost growth is roughly this small and linear, the newly-added index's raw CPU/IO maintenance cost is probably NOT the dominant cause of a serious latency regression on its own, pointing the investigation toward bloat, lock contention, or WAL/replication effects instead, which often dominate the raw per-row insertion cost by a wide margin once the table has been live and churning under the new index for days, not just during a clean bulk-load benchmark.

Trade-offs & pitfalls

Deciding to roll back versus adjust: dropping the index outright is the fastest fix and always available as a last resort, but it forfeits whatever read-side benefit motivated adding it in the first place; rebuilding at a lower fillfactor (trading some read density for fewer page splits under the table's actual write pattern) or converting to a PARTIAL index (indexing only, say, WHERE status = 'active' rows if most writes touch rows the query never filters for anyway) both preserve most of the read benefit while cutting the specific write cost that regressed. Decide which by re-running the same before/after measurement discipline against the adjusted index before committing to it in production, rather than guessing.

A pitfall worth naming explicitly: don't conflate "the index makes writes slower" (true of literally every index, by a small, usually-acceptable margin) with "the index CAUSED this regression" (a specific, provable claim that needs the timing correlation and the measured delta above it). Blaming a newly-added index for a coincidental regression, without confirming the causal chain, risks removing a genuinely valuable index over an unrelated problem, and leaves the real cause (which might be, say, a concurrent autovacuum storm, a connection pool exhaustion event, or an unrelated deploy) unaddressed.

Direct answer

Quantify fragmentation directly rather than guessing from symptoms: Postgres exposes real, physical, per-index numbers through the pgstattuple extension's pgstatindex() function, specifically avg_leaf_density (how full the leaf pages actually are versus their target) and leaf_fragmentation (the percentage of leaf pages that are physically out of their logical B-tree order, evidence of page splits scattering new pages away from their neighbors). SQL Server exposes the equivalent through sys.dm_db_index_physical_stats, whose avg_fragmentation_in_percent and avg_page_space_used_in_percent columns feed the maintenance decision. For years the commonly repeated rule of thumb was a fixed band (below roughly 5 to 10 percent: do nothing; 10 to 30 percent: reorganize, an online, incremental defragmentation that doesn't rebuild the whole structure; above 30 percent: rebuild, which fully recreates the index, recovering the most space and fragmentation but costing more I/O and, unless run online/concurrently, blocking writes for the duration). Microsoft's current documentation explicitly retracts fixed thresholds as the deciding criterion: it states that index maintenance decisions "shouldn't be based on fixed fragmentation or page density thresholds alone," and names a specific reason customers get misled: a rebuild also does a full statistics refresh as a side effect (equivalent to UPDATE STATISTICS ... WITH FULLSCAN), and the performance win people attribute to reduced fragmentation is often really that statistics refresh, recoverable at far lower cost with a plain UPDATE STATISTICS call instead of a full rebuild. The current guidance is to measure the actual before/after query performance (Microsoft's own suggested method is a Query Store A/B test) rather than trigger maintenance off a fragmentation percentage alone, try replacing a rebuild with a statistics update first to see if that alone recovers the performance, and reorganize or rebuild only the specific indexes behind a real, measured regression. In Postgres there's no ALTER-based reorganize, so the equivalent choice is REINDEX CONCURRENTLY (an online rebuild that doesn't take an exclusive lock) versus doing nothing, since a plain VACUUM does NOT defragment an index at all, only a rebuild does; unlike SQL Server's rebuild, Postgres's REINDEX does not fold in a statistics refresh as a side effect (ANALYZE is a fully separate step there), so the statistics-refresh caveat above is specifically about why SQL Server's fixed-threshold rule is outdated, not a reason to skip ANALYZE after a Postgres REINDEX.

Structured elaboration

Why VACUUM alone is not the fix. VACUUM reclaims space occupied by dead tuples (rows deleted or superseded by an update) so it can be reused by future inserts, but it does not compact pages together or restore physical/logical order once splits have scattered pages. It is necessary maintenance, but it is a different operation from defragmentation.

Reorganize versus rebuild, as a spectrum. A reorganize (SQL Server's ALTER INDEX ... REORGANIZE, or, loosely, Postgres's community tool pg_repack) works incrementally and stays fully online, at the cost of leaving some fragmentation behind and taking longer for a heavily fragmented index. A rebuild (SQL Server's ALTER INDEX ... REBUILD, Postgres's REINDEX) throws the whole structure away and recreates it from scratch at the target fillfactor, fully defragmented, at the cost of a much bigger burst of I/O and, for a plain REINDEX, an exclusive lock that blocks writes (and in older Postgres versions, reads too) against that index for the duration; REINDEX CONCURRENTLY (Postgres 12+) avoids the exclusive lock by building a new index alongside the old one and swapping it in, at the cost of needing roughly double the disk space during the operation and taking noticeably longer than a locking rebuild.

Worked example

Continuing from a heavily churned index (default fillfactor 90, 40 percent of its 150,000 rows randomly re-keyed): measured leaf_fragmentation of 49.94 percent and avg_leaf_density of 61.74 percent, up from a clean baseline of 0 percent fragmentation and 90.05 percent density.

First, confirming that VACUUM alone does not fix it:

VACUUM churn_hi_ff;
SELECT avg_leaf_density, leaf_fragmentation, leaf_pages FROM pgstatindex('idx_churn_hi_ff_k');
-- 44.32 | 49.94 | 819       <- fragmentation UNCHANGED, density actually got slightly worse

Fragmentation stayed exactly at 49.94 percent and density dropped further, to 44.32 percent, confirming VACUUM reclaims dead-tuple space but does not touch page layout.

Then REINDEX CONCURRENTLY, the online rebuild:

REINDEX INDEX CONCURRENTLY idx_churn_hi_ff_k;
SELECT avg_leaf_density, leaf_fragmentation, leaf_pages FROM pgstatindex('idx_churn_hi_ff_k');
-- 90.00 | 0.00 | 380

SELECT pg_size_pretty(pg_relation_size('idx_churn_hi_ff_k'));
-- before: 6592 kB   ->   after: 3072 kB

A full recovery in one operation: fragmentation back to 0.00 percent, density back to the target 90 percent, leaf page count DOWN from 819 to 380 (better than the original 410, since the rebuild also discarded the dead-tuple slack VACUUM had reclaimed but not compacted), and the physical index size roughly halved, from 6.6 MB to 3.1 MB. REINDEX CONCURRENTLY completed in under 200 ms on this dataset without taking the exclusive lock a plain REINDEX would have needed.

Trade-offs & pitfalls

Scheduling maintenance with minimal throughput impact means several concrete choices, all real, all worth naming explicitly to an interviewer:

• Use the online/concurrent form (REINDEX CONCURRENTLY in Postgres, ALTER INDEX ... REBUILD ONLINE in SQL Server Enterprise, pg_repack as a widely used third-party tool that achieves a similar online rebuild) so writers aren't blocked for the operation's duration.
• Run it during the lowest-traffic window you have, since even an online rebuild adds real CPU/IO load and briefly doubles the index's disk footprint while old and new copies coexist.
• For a very large index where even an online rebuild is a long-running, resource-heavy operation, some engines and tools support an incremental or chunked rebuild, so the operation can be paused, monitored, or spread across a longer window rather than committing to one long uninterrupted run; know your specific tool's support for this before promising it.
• Communicate the maintenance window to stakeholders ahead of time, even for an "online" operation, since online rebuilds still consume meaningful I/O and CPU headroom that other workloads on the same instance will feel, and set an explicit rollback plan (keep the old index available, or have a tested path to cancel a REINDEX CONCURRENTLY cleanly, since a cancelled concurrent reindex can leave an INVALID index behind that needs to be dropped) before starting, particularly for an index serving a latency-sensitive path during peak hours.
• Monitor, but don't auto-trigger on a fixed band: track leaf_fragmentation (or SQL Server's avg_fragmentation_in_percent) on your hottest, most write-churned indexes on a schedule, but treat a rising percentage as a signal to go measure, not an automatic rebuild trigger. Current Microsoft guidance is explicit that maintenance decisions shouldn't be based on fixed fragmentation or page density thresholds alone: correlate the trend against actual measured query performance (Query Store before/after) and reorganize or rebuild only the specific indexes behind a real, observed regression, rather than firing an automatic rebuild across the board every time an index crosses the old 10 or 30 percent bands, since indiscriminate maintenance burns real CPU, memory and I/O for a benefit that's frequently minuscule on a workload that hasn't actually slowed down.

Fillfactor and fragmentation are the same underlying mechanism viewed from two angles: a properly-tuned fillfactor for a table's actual write pattern reduces how OFTEN this maintenance is needed in the first place, but it never eliminates the need entirely for a table under sustained random-order churn; monitoring and a scheduled rebuild policy are still required.