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:
If the index is
UNIQUE
and created withCONCURRENTLY
option, data unicity was likely violated during index creationMore
When a
UNIQUE
index is created withCONCURRENTLY
option, PostgreSQL proceeds in 2 steps:- Process all data to create the index
- 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>);
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:
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.
Delete the index
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
.