Hash indexes

A PostgreSQL hash index stores a 32-bit hash code of each value, not the value, in bucket pages. A lookup hashes the search value and goes straight to its bucket instead of walking down a tree. Because the order of values is gone it answers only equality: no ranges, no sorting, no prefix matches. It covers a single column, can’t enforce uniqueness, and every match is rechecked against the table. Storing 4 bytes instead of the key pays off for long values: on 1 million 94-character URLs in PostgreSQL 18, the hash index took 32 MB and a B-tree 119 MB.

Until PostgreSQL 10 (October 2017) hash indexes weren’t written to the write-ahead log, so they weren’t crash-safe or replicated and PostgreSQL warned when you created one. Buckets still split in the foreground while inserts wait, and many duplicates of one value pile up in overflow pages, so they suit unique or nearly unique keys.

MySQL uses hashing in two places. MEMORY tables use hash indexes by default. InnoDB can build an adaptive hash index in memory, keyed on a prefix of the index key, for B-tree pages it sees searched often; MySQL 8.4 turned it off by default (innodb_adaptive_hash_index).