Invalid index

PostgreSQLInvalidIndex #

Meaning #

Alert is triggered when an index is in invalid state for a while.

Impact #

  • PostgreSQL does not use the index for query execution, which could degrade query performances.

Diagnosis #

  • If an error occurred during index creation:

    1. If the index is UNIQUE and created with CONCURRENTLY option, data unicity was likely violated during index creation

      More

      When a UNIQUE index is created with CONCURRENTLY option, PostgreSQL proceeds in 2 steps:

      1. Process all data to create the index
      2. Process modified data during index creation

      If UNIQUE constraint is violated between step 1 and step 2, PostgreSQL will mark the index as invalid.

      Example:

      CREATE UNIQUE INDEX CONCURRENTLY ON <replace_with_table> (<replace_with_column>);
      
    2. Check if the query to create the index did not complete

      Look at PostgreSQL logs to have the index creation error message.

      If the query was killed or canceled (e.g. due to statement timeout), it will be reported in PostgreSQL logs.

  • If the error occurred on an existing index:

    1. Check if there are rows with more than 8191 bytes

      Why?PostgreSQL limits index column size to 8191 bytes.

      If you try to index larger data, the index will be marked as invalid since it cannot contain all data.

      How?

      Find rows with a column larger than 8191 bytes:

      SELECT <replace_with_column>
      FROM <replace_with_table>
      WHERE octet_length(<replace_with_column>) - 4 > 8191;
      

Mitigation #

PostgreSQL doesn’t use the invalid index.

  1. Delete the index

  2. Recreate index

Additional resources #

Production experiences
  • The index is still in creation. This alert is triggered after 1 hour. If the index is still being created, it is expected its state is invalid.

  • An index has been corrupted because the query that created it was killed due to statement_timeout.