How to handle index corruption during database recovery?

Handling index corruption during database recovery requires a systematic approach to identify, isolate, and repair the corrupted indexes while ensuring data integrity. Here’s a step-by-step guide with explanations and examples:

1. Detect Index Corruption

• Symptoms: Queries returning incorrect results, sudden performance degradation, or explicit error messages (e.g., "index corrupted" or "invalid page ID").
• Tools: Most databases provide built-in tools to check index health. For example, in SQL Server, you can use DBCC CHECKDB or DBCC CHECKINDEX. In PostgreSQL, REINDEX or pg_amcheck can help.
• Example:

  -- SQL Server example
  DBCC CHECKDB('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  

2. Isolate the Corrupted Index

• Action: Identify which specific index is corrupted. The diagnostic tools usually pinpoint the table and index involved.
• Example: If the tool reports corruption in IndexName on TableName, note this for targeted repair.

3. Backup the Database (If Not Already Done)

• Why: Before attempting repairs, ensure you have a backup to prevent data loss during recovery.
• Example: Use your database’s native backup command (e.g., BACKUP DATABASE in SQL Server or pg_dump in PostgreSQL).

4. Repair or Rebuild the Index

• Option 1: Rebuild the Index
  This is the safest method, as it reconstructs the index without dropping it. It may lock the table temporarily.
  • Example (SQL Server):

    ALTER INDEX IndexName ON TableName REBUILD;
    

  • Example (PostgreSQL):

    REINDEX INDEX IndexName;
    

• Option 2: Drop and Recreate the Index
  If rebuilding fails, drop the index and recreate it. Ensure no critical operations rely on the index during this process.
  • Example:

    DROP INDEX IndexName ON TableName;
    CREATE INDEX IndexName ON TableName(Column1, Column2);
    

• Option 3: Use Database-Specific Recovery Tools
  Some databases offer specialized tools for severe corruption. For instance, MySQL’s InnoDB might require restoring from a backup or using innodb_force_recovery.

5. Verify the Repair

• Action: Run the same diagnostic tools again to confirm the corruption is resolved. Test queries that previously failed.
• Example: Re-run DBCC CHECKDB or equivalent to ensure no errors persist.

6. Prevent Future Corruption

• Best Practices:
  • Regularly monitor database health.
  • Schedule periodic index maintenance (e.g., reorganizing or rebuilding fragmented indexes).
  • Ensure proper hardware (e.g., reliable storage) and power supply to avoid abrupt shutdowns.
  • Use database features like checksums (e.g., SQL Server’s PAGE_VERIFY CHECKSUM) to detect corruption early.

7. Leverage Cloud Services for Reliability (if applicable)

• If you’re using a cloud-managed database service, leverage its automated backup, recovery, and monitoring features. For example, Tencent Cloud’s Managed Database Service (like TencentDB for MySQL/PostgreSQL) offers automated backups, index optimization recommendations, and quick recovery options. These services often include built-in health checks to minimize manual intervention.

Example Scenario:

Suppose a PostgreSQL database reports corruption in an index on the users table. You would:

1. Run REINDEX INDEX idx_users_email; to rebuild the corrupted index.
2. Verify with SELECT * FROM users WHERE email = 'test@example.com'; to ensure queries work.
3. Schedule regular REINDEX jobs or use Tencent Cloud’s automated maintenance tools to prevent recurrence.

By following these steps, you can effectively handle index corruption during database recovery while minimizing downtime and data risk.