There are two main strategies depending on how bad the fragmentation is:

---

1. REORGANIZE the Index (Light Defragmentation)

• What it does:
  • Defragment leaf pages online, without locking the table.
  • Moves rows between pages to compact them.
  • Does NOT rebuild the whole index — just tidies up the scattered data.
• Good for:
  • Low to moderate fragmentation (e.g., 5%–30%).
• Fast, non-blocking (online operation).
• Works on-the-fly — no heavy downtime.

✅ SQL Server Example:

ALTER INDEX IndexName ON TableName REORGANIZE;

✅ PostgreSQL (AUTO-VACUUM / CLUSTER):

• PostgreSQL doesn’t have a direct “reorganize,” but VACUUM reduces dead tuples.
• Or manually re-cluster:

CLUSTER TableName USING IndexName;

2. REBUILD the Index (Heavy Defragmentation)

• What it does:
  • Drops and recreates the index completely.
  • Rebuilds the entire B-tree from scratch — no fragmentation left.
• Good for:
  • High fragmentation (e.g., 30%–100%).
• Slow, blocks writes (unless using ONLINE options).
• Can cause downtime (depending on DB and options).
• Reclaims space — the index is fresh and tight like new.

---

✅ SQL Server Example:

ALTER INDEX IndexName ON TableName REBUILD;

(Optionally with ONLINE = ON to minimize downtime.)

✅ PostgreSQL Example:

REINDEX INDEX IndexName;

✅ MySQL Example (InnoDB):

ALTER TABLE TableName ENGINE = InnoDB;

(This rebuilds the table and its indexes.)

---

3. Manual Techniques

• Drop and recreate the index manually:

DROP INDEX IndexName ON TableName;
CREATE INDEX IndexName ON TableName (column1);

Partition the table:

• Break a huge table into smaller partitions based on a key (e.g., date ranges).
• Each partition has fewer rows = less fragmentation.

Fill Factor Tuning (SQL Server, PostgreSQL):

• Adjust fill factor — the percentage of page space to leave empty on inserts.
• E.g., FILLFACTOR = 80 means 80% full pages, 20% free.
• Reduces page splits (but increases storage).

CREATE INDEX IndexName ON TableName (column1) WITH (FILLFACTOR = 80);

Which to Choose? (Quick Decision Table)

Fragmentation %	Recommended Action
0% – 5%	Do nothing — healthy!
5% – 30%	REORGANIZE the index
> 30%	REBUILD the index

---

Bonus: How to Check Fragmentation

✅ SQL Server Example:

SELECT
    avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('TableName'), NULL, NULL, 'LIMITED');

✅ PostgreSQL: Use pgstattuple extension:

SELECT * FROM pgstattuple('TableName');

✅ MySQL:

SHOW TABLE STATUS LIKE 'TableName';

(See Data_free and Index_length for signs of fragmentation.)

---

Summary

Fix Method	Good for	Impact
REORGANIZE	Light fragmentation (5–30%)	Fast, online, no blocking
REBUILD	Heavy fragmentation (>30%)	Full rebuild, blocks writes (or online rebuild)
Drop and recreate	Full reset	Useful if rebuild is problematic
Partitioning	Big tables	Prevent fragmentation growth
Fill factor tuning	Prevent future fragmentation	Reduces page splits on insert

---

⚡ Bottom Line

Light fragmentation — REORGANIZE.

Heavy fragmentation — REBUILD.

Plan for growth — use fill factor or partitioning.