What is a hash index in PostgreSQL and when should you use it?

Q: Are hash indexes safe to use in PostgreSQL 18?

Yes. Since PostgreSQL 10 hash indexes are write ahead logged, which makes them crash safe and replicated to standbys (PostgreSQL 10 release notes). The "never use hash indexes" advice in older articles describes pre 10 behaviour. Safety is no longer the reason to prefer a B tree; capability is.

Q: Can a hash index enforce a unique constraint or back a primary key?

No. Only B tree indexes can be declared in PostgreSQL (CREATE INDEX), so a hash index cannot back a primary key, a unique constraint, or an arbiter. This is the single most common reason a hash index is not an option, regardless of how equality shaped the workload looks.

Q: Can a hash index be multicolumn or cover an `ORDER BY`?

Neither. Multiple key columns are supported only by B tree, GiST, GIN and BRIN (CREATE INDEX), and because a hash index stores hash codes rather than values it has no ordering the planner can use for sorting or merge joins. If you need either, the answer is a B tree. Part of the PostgreSQL index types: the complete guide guide. Last verified against PostgreSQL 18, 2026 09 17.

2026-10-04

A hash index stores a 32-bit hash of the column value, not the value itself, so it can answer = and nothing else. Since PostgreSQL 10 it is crash-safe and replicated, which the older "never use hash indexes" advice predates. Use one only where you have measured it smaller than the B-tree on your own data.

Key facts

| Fact | Value | |---|---| | What is stored | A 32-bit hash code derived from the indexed value, not the value (Hash Indexes) | | Operators supported | = only — no <, >, BETWEEN, LIKE 'x%', or ORDER BY support (Index Types) | | Multicolumn | Not supported; only B-tree, GiST, GIN and BRIN take multiple key columns (CREATE INDEX) | | Unique indexes | Not supported; only B-tree indexes can be declared UNIQUE (CREATE INDEX) | | WAL logging | Added in PostgreSQL 10; before that, hash indexes were not WAL-logged, not crash-safe and not replicated (PostgreSQL 10 release notes) | | Default access method | btree — CREATE INDEX uses it unless you write USING hash (CREATE INDEX) | | Fleet observation | Across 31 monitored instances, 58.1% have at least one unused index; across 50 instances, 44.0% carry index bloat findings |

Why this happens

A B-tree stores the indexed value itself, in sorted order, in every leaf entry. That is what lets it serve range predicates, prefix matches, ORDER BY, merge joins and index-only scans. A hash index instead stores a 32-bit hash code derived from the value, and uses it to pick a bucket (Hash Indexes).

Everything a hash index cannot do follows from that single design choice:

Equality only. Two values that sort next to each other hash to unrelated buckets, so there is no ordering to walk. The hash access method supports the = operator and no other (Index Types).
No sorting help. The planner cannot satisfy an ORDER BY from a hash index, because the index has no useful order.
No prefix matching. LIKE 'abc%' is a range scan in disguise. A hash of 'abcdef' tells you nothing about the hash of 'abc'.
No multicolumn indexes. Only B-tree, GiST, GIN and BRIN support multiple key columns (CREATE INDEX).
No unique constraints. Only B-tree indexes can be declared unique (CREATE INDEX). A hash index cannot back a UNIQUE constraint or a primary key, and it cannot be the arbiter of an ON CONFLICT clause. In practice this is the dealbreaker far more often than the query-shape limits are.

The history that still poisons the search results

Search for "postgres hash index" and most of the first page tells you never to use one. That advice was correct, and it is now eight major versions out of date.

Before PostgreSQL 10, hash index operations were not written to the write-ahead log. A hash index did not survive a crash and had to be rebuilt with REINDEX, and because WAL is the replication transport, hash indexes were not replicated to standbys at all (WAL introduction). The documentation of the day carried an explicit warning to that effect.

PostgreSQL 10 added write-ahead logging for hash indexes, making them crash-safe and replicated, alongside performance work on the access method — caching the metapage and reducing the locking around bucket splits (PostgreSQL 10 release notes). The WARNING: hash indexes are not WAL-logged you may remember from psql is gone from modern versions.

So the honest answer to "should I use a hash index?" is: rarely, but not because they are dangerous. It is that a B-tree does everything a hash index does plus everything it cannot, and the size advantage is smaller and more situational than people expect.

Where a hash index can genuinely win

The one real argument is index size on wide keys. A B-tree leaf entry stores the full key, so a 200-character URL, a long external identifier or a UUID-ish blob held as text is carried in full by every entry. The hash index stores a 32-bit hash plus the tuple pointer however wide the value is (Hash Indexes). On a large table with a wide key and pure equality lookups, that can be a materially smaller index — so more of it stays in cache.

Two caveats keep this from being a rule. Hash index buckets are pre-allocated, so on a small or narrow-keyed table the hash index is frequently the larger of the two. And the size win buys you nothing if anything in your workload ever needs a range, a sort, or uniqueness on that column.

How to detect it

Do not take the size claim on trust — including from this article. Build both indexes on your own data and compare. This script is safe to run on a copy of a real table:

-- 1. Build both indexes on the same column.
CREATE INDEX idx_probe_btree ON documents USING btree (external_ref);
CREATE INDEX idx_probe_hash  ON documents USING hash  (external_ref);

-- 2. Compare their on-disk size.
SELECT c.relname AS index_name,
       am.amname AS access_method,
       pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
       pg_relation_size(c.oid) AS size_bytes
FROM pg_class c
JOIN pg_am am ON am.oid = c.relam
WHERE c.relname IN ('idx_probe_btree', 'idx_probe_hash')
ORDER BY size_bytes DESC;

pg_relation_size reports the main fork of the relation in bytes (database object size functions). Expected output shape:

   index_name    | access_method | index_size | size_bytes
-----------------+---------------+------------+------------
 idx_probe_btree | btree         | 412 MB     |  432013312
 idx_probe_hash  | hash          | 176 MB     |  184549376

Read it strictly: if the hash index is not comfortably smaller on your key distribution and your row count, there is no case left to argue. Drop both and keep the B-tree.

To see which access methods are already in use on an instance, and whether an unusual one has crept in:

SELECT am.amname AS access_method,
       count(*) AS index_count,
       pg_size_pretty(sum(pg_relation_size(c.oid))) AS total_size
FROM pg_class c
JOIN pg_am am ON am.oid = c.relam
WHERE c.relkind = 'i'
GROUP BY am.amname
ORDER BY 2 DESC;

And to check what the hash access method will actually do with your column's type — the operator class and its support function:

SELECT op.opcname AS operator_class,
       t.typname  AS column_type,
       op.opcdefault AS is_default
FROM pg_opclass op
JOIN pg_am am ON am.oid = op.opcmethod
JOIN pg_type t ON t.oid = op.opcintype
WHERE am.amname = 'hash' AND t.typname IN ('text', 'uuid', 'int8')
ORDER BY t.typname;

For text the default hash operator class is text_ops, whose support function is hashtext; you do not name it explicitly unless you want a non-default class (operator classes). This matters because the traditional workaround — a B-tree over an expression like md5(external_ref) — is choosing a hash function by hand and then indexing its output as a sortable value (string functions):

CREATE INDEX idx_probe_md5 ON documents (md5(external_ref));
-- queries must then be written as:
SELECT * FROM documents WHERE md5(external_ref) = md5('some-long-external-reference');

That workaround predates crash-safe hash indexes and is often still fine: a fixed-width 32-character key, portable to every version, and it supports UNIQUE. Its cost is that every query must repeat the md5() call, which is easy to forget in an ORM.

How MyDBA shows this

Low-usage indexes: scan counts against size, the evidence for whether an equality-only index is earning its keep.

MyDBA's index advisor groups indexes into Unused Indexes ("indexes with no scans in analysis period") and Low Usage Indexes ("indexes with minimal activity"), each row showing the scan count against the index size, and the All Indexes table carries a Type column — the access method — so a hash index, or any other unusual one, is visible next to its scan count rather than buried in the catalog. This is the evidence that settles the argument: an equality-only index sitting on a column whose queries turn out to use ranges never gets scanned, and shows up here with zero scans and a real size. Across 31 monitored instances, 58.1% have at least one unused index and 54.8% are flagged for the space those unused indexes occupy, which is the better argument against adding a second index "just to try it" than any rule of thumb. If you would like that inventory for your own instance, the free PostgreSQL health check reports it.

How to fix it

If you are deciding whether a hash index belongs on a column, work in this order.

1. Confirm the workload is equality-only. Check every query touching the column for a range predicate, a LIKE prefix, an ORDER BY, or a join that might use a merge. One of these is enough to end the discussion. 2. Confirm you do not need uniqueness. If the column is or might become unique, stop here — a hash index cannot enforce it (CREATE INDEX). 3. Measure both. Run the build-both-and-compare script above on a copy of the real table, at production row counts. A test on 10,000 rows tells you about bucket pre-allocation, not about your production index. 4. Build it without locking the table if you go ahead, using CREATE INDEX CONCURRENTLY (CREATE INDEX):

``sql CREATE INDEX CONCURRENTLY idx_documents_external_ref_hash ON documents USING hash (external_ref); ``

5. Verify it is used after a representative period, with EXPLAIN (ANALYZE, BUFFERS) on the real query and a check of pg_stat_user_indexes.idx_scan. If it is not, drop it without blocking reads.

If you inherited a hash index from a pre-10 database and have upgraded since, rebuild it once with REINDEX — a hash index built on an old major version is not necessarily in the current on-disk format.

How to prevent it

The preventive habit is not "avoid hash indexes". It is to stop adding indexes on a guess.

Default to B-tree. It is what CREATE INDEX gives you without USING, and it is right for the overwhelming majority of columns. See what a B-tree index is and how it works for what you get in exchange.
Write the index from the query, not the column. How to create an index in PostgreSQL covers choosing column order, INCLUDE and partial predicates from the actual predicates in your workload.
Watch scan counts, not index counts. Every index costs write throughput and space on every insert. The fleet numbers above — 58.1% of instances carrying an unused index, 44.0% with index bloat findings — are the accumulated cost of indexes that were added on a hypothesis and never checked again.
Re-check after schema changes. An equality-only index survives exactly as long as nobody adds a BETWEEN to the query it was built for.

FAQ

Are hash indexes safe to use in PostgreSQL 18?

Yes. Since PostgreSQL 10 hash indexes are write-ahead logged, which makes them crash-safe and replicated to standbys (PostgreSQL 10 release notes). The "never use hash indexes" advice in older articles describes pre-10 behaviour. Safety is no longer the reason to prefer a B-tree; capability is.

Can a hash index enforce a unique constraint or back a primary key?

No. Only B-tree indexes can be declared UNIQUE in PostgreSQL (CREATE INDEX), so a hash index cannot back a primary key, a unique constraint, or an ON CONFLICT arbiter. This is the single most common reason a hash index is not an option, regardless of how equality-shaped the workload looks.

Is a hash index faster than a B-tree for equality lookups?

Not reliably, and speed is the wrong thing to test for. Both resolve an equality lookup in a small number of page reads. The measurable difference is size on wide keys, and a smaller index keeps more of itself in shared buffers. Build both and compare pg_relation_size on your own data rather than trusting a benchmark run on someone else's schema.

Should I use a B-tree on md5(column) instead?

It is the traditional workaround and often still fine: a fixed-width 32-character key, portable to any version, and it supports UNIQUE (string functions). The cost is that every query must wrap the column in md5() to match the expression index, which is easy to forget. A native hash index needs no query rewrite.

Can a hash index be multicolumn or cover an `ORDER BY`?

Neither. Multiple key columns are supported only by B-tree, GiST, GIN and BRIN (CREATE INDEX), and because a hash index stores hash codes rather than values it has no ordering the planner can use for sorting or merge joins. If you need either, the answer is a B-tree.

Part of the PostgreSQL index types: the complete guide guide. Last verified against PostgreSQL 18, 2026-09-17.