Indexing Anti-Patterns and Maintenance

Potential overlap:
INDEX (user_id)
INDEX (user_id, created_at)

The second index may already cover many uses of the first.

# Do not keep several indexes that serve the same leading prefix needlessly.

-- Often weak by itself
INDEX (is_deleted)

-- Often better
INDEX (is_deleted, created_at)
-- or a partial index where supported

# A boolean by itself is often weak unless paired with something else or made partial.

Every INSERT, UPDATE, and DELETE may need to update every relevant index.

# Unused indexes still hurt writes and consume disk.

REINDEX INDEX idx_orders_user_id;

# Rebuild a problematic or bloated index.

ANALYZE TABLE users;

# Refresh optimizer statistics after big data shifts.

VACUUM;

# Compact the file after heavy changes when appropriate.