Hash Index

ELI5 — The Vibe Check

A hash index uses a hash function to map values directly to locations, making equality lookups insanely fast. But it ONLY works for exact matches — no ranges, no sorting, no LIKE queries. It's a specialist: one trick, but does it faster than anything else.

Real Talk

A hash index uses a hash function to map key values to bucket locations, providing O(1) average lookup time for equality queries. In PostgreSQL, hash indexes support only the = operator (no range scans, sorting, or multicolumn indexes). They were made crash-safe in PostgreSQL 10. Generally, B-Tree indexes are preferred unless the workload is purely equality-based.

Show Me The Code

-- Create a hash index (PostgreSQL 10+)
CREATE INDEX idx_users_email_hash ON users USING hash (email);

-- Only useful for exact equality
SELECT * FROM users WHERE email = 'alice@example.com'; -- uses index
SELECT * FROM users WHERE email LIKE 'alice%'; -- does NOT use index

When You'll Hear This

"Hash index is only worth it if you ONLY do equality lookups on that column." / "Stick with B-Tree unless you've proven hash gives measurable improvement."