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 #

• https://www-footcow-com.translate.goog/index.php/post/2020/05/17/D-o%C3%B9-provienne-les-index-INVALID?_x_tr_sl=fr&_x_tr_tl=en&_x_tr_hl=en&_x_tr_pto=wapp