At some moment index became bad and queries does not return any data.
For example, there are two tables - A (id, name) and B (id, name, a_id). B.a_id is foreign key to A. Both name columns in tables contains identical values for A.id = B.a_id. A.name column has unique constraint and additional index by it.
So, in some moment results for queries like [select id from A where name = 'petya'] became empty (row with 'petya' name exist in A).
But query [select a_id from B where name = 'petya'] returns A.id and [select * from A where id = <found id>] returns row.
This problem can be solved by index recreation only.
How can we avoid such situation?
What version??
The first one fail was on 9.1.? (table with at least 10 000 000 rows with 20% every day modifications)
Two day ago was another one fail on 9.2.3. (table with 120 000 rows with less than 0.5% every day modifications)
Perhaps the name you're not finding is spelled differently than what you're typing, due to collation?
If there's actually something wrong with the database; it looks a bit like your tables and your indexes get out of sync somehow, which normally wouldn't be possible. I'm mostly guessing, but perhaps one of the below has something to do with it:
Maybe you turned fsync off?
What type of index is that? A standard btree or one of the newer types?
Are those tables and indexes perhaps on some kind of virtual storage or on a file-system that might be rolling back file-system transactions? It this database perhaps a replicated node?
Oh, it is not about short experiments. :)
Both failed queries are part of 24/7 application. And one of the query results became empty for existing key at some moment. We've recreated index and same query returned to the normal work.
fsync is in off state, yes
Both failed indexes are btree type.
Database located on software md raid 1 based on two SSD disks array. Ext4 filesystem. Database is master node.
