Hello,
We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a Multi-zone failover configuration. We do not have access to the hardware or file system. Fsync is enabled.
We have a recurring problem that we can get out of with "reindex table mytable". Basically we can't find a particular record or two using the primary key and after reindexing we can. There does seem to be a pattern related to application business processes but we are at a loss as to how this could happen.
Assuming the RDS instance is solid, what are the things we could do using nothing but SQL that could cause a corruption to the primary key index? I've researched the issue a fair amount and most content talks about hardware, file system, and fsync. As mentioned above fsync (and other properties) we can control (and fsync is enabled) but we can't get to the file system or hardware. If such a thing cannot be done with transactions, statements, and queries alone, what should we be looking at for settings?
Thanks.