Rebuild / reorganize an index

Oracle

ALTER INDEX customer_city_idx REBUILD ONLINE;

ONLINE rebuilds without locking the table (Enterprise Edition). To move it to another tablespace: ... REBUILD TABLESPACE new_ts.

PostgreSQL

REINDEX INDEX CONCURRENTLY customer_city_idx;
-- Whole table: REINDEX TABLE CONCURRENTLY customer;

CONCURRENTLY (PG 12+) rebuilds without blocking writes to the table; slower but safe.

MySQL

-- Rebuild InnoDB indexes + table:
ALTER TABLE customer ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
-- Alternative: OPTIMIZE TABLE customer;

LOCK=NONE allows concurrent writes; INPLACE may not be possible for every ALTER.

SQL Server

-- Rebuild for high fragmentation:
ALTER INDEX ALL ON dbo.customer REBUILD WITH (ONLINE = ON);
-- Lighter option for low fragmentation:
-- ALTER INDEX ALL ON dbo.customer REORGANIZE;

Common threshold: 5-30% fragmentation REORGANIZE, >30% REBUILD. ONLINE is an Enterprise feature.

MongoDB

db.customer.reIndex()

Rebuilds all indexes in the foreground and locks the collection. In 6.0+ it runs only on a standalone (not allowed on replica set members); on a replica set, rebuild a single index with dropIndex + createIndex, node by node (rolling).

Redis

# Not applicable (core). If RediSearch is used:
# FT.ALTER / rebuilding is done per the module documentation.

The key-value model has no traditional index maintenance.