Ever had a situation like this:
Select from database ID where name = RICHARD;
Returns and ID of 55 for example.
Then go and do a query like this:
Select * from some_other_table where ID = 55;
Returns, “Sorry does not exist, time to die…..”
Well apparently indexes when corrupt – which is NOT SUPPOSED TO HAPPEN – can cause PostgreSQL to go all stupid and not do a table lookup for real. This happened to me. So I found this:
Turns out that a reindex and a full vacuum can do wonders – even though a full vacuum is not needed with autovacuum and indexes can’t get corrupted…..or so they say.
I have now added a system wide reindex maintenance plan for PostgreSQL every night. I know that MS-SQL server has an option for this with their maintenance jobs inside enterprise manager. Maybe someone should make an enterprise manager for PostgreSQL too?