The following bug has been logged on the website:
Bug reference: 16166
Logged by: Wayne Walker
Email address: wwalker@solid-constructs.com
PostgreSQL version: 9.6.0
Operating system: Documentation
Description:
https://www.postgresql.org/docs/9.6/sql-createindex.html
Has this Caution:
" Caution
Hash index operations are not presently WAL-logged, so hash indexes might
need to be rebuilt with REINDEX after a database crash if there were
unwritten changes. Also, changes to hash indexes are not replicated over
streaming or file-based replication after the initial base backup, so they
give wrong answers to queries that subsequently use them. Hash indexes are
also not properly restored during point-in-time recovery. For these reasons,
hash index use is presently discouraged."
This leaves out the fact that if one creates a HASH index in a cluster that
is replicated, the replica will Not be Usable. The fact that the index
exists will be replicated, but no data will be replicated. Therefore,
queries will attempt to use the index and will fail with a message like
this:
'ERROR: could not read block 0 in file
"pg_tblspc/3291733/PG_9.6_201608131/17993/32595182": read only 0 of 8192
bytes'
This happened when we were in the middle of a failover to our primary
replica because we had just had a hardware failure on our master server.
This is not the time to find out.
We had read the caution and discussed it. For our use case it was
acceptable (we thought), because the hash index wouldn't exist on the
replica, and we would just get slow queries while we were creating the new
hash index on the replica become primary.
Instead we were trying to find out why queries ( INSERTs :-( ) were
failing.
If the caution had mentioned the "partially, but unusably, replication of
the index,we would have recovered much faster and without the extra dowtime.