Skip to main content
  • Pricing
Tutorials

PostgreSQL hash indexes: what they store, what they can't do, and when to use one

By Oakgen TeamPublished 3 min read

Share
PostgreSQL hash indexes: what they store, what they can't do, and when to use one

A PostgreSQL hash index stores a 32-bit hash code of each indexed value instead of the value itself, so it can answer only equality (=) lookups. Since PostgreSQL 10 hash indexes are write-ahead logged, which makes them crash-safe and replicatable, and the documentation says they are best suited to equality scans on larger tables where keys are unique or nearly unique. For range queries, sorted output or unique constraints, the default B-tree is the right tool.

How a hash index is organised

Per the PostgreSQL manual, each hash index tuple holds just the 4-byte hash value, not the column value. The index has four kinds of pages: a meta page (page zero) with control information, primary bucket pages, overflow pages and bitmap pages that track freed overflow pages for reuse. A lookup computes the hash, finds the matching bucket and reads it directly, instead of descending a tree to a leaf page. The docs say this can reduce "logical I/O" on tables with millions of rows, more so when the index is larger than shared_buffers or RAM.

When a bucket page fills up, overflow pages are chained to that bucket. A scan has to read every overflow page in the bucket, which is why the manual warns that an unbalanced hash index can need more block accesses than a B-tree.

What a hash index can and cannot do

PropertyHash index, according to the manual
OperatorsOnly =; range conditions cannot use it
Column countSingle-column indexes only
UniquenessDoes not allow uniqueness checking
Data typesAny type, including types with no linear ordering
Stored dataOnly the 4-byte hash, so key length does not limit it
Scan typeLossy, so the table row confirms the match
SizeCan be much smaller than a B-tree on long values such as UUIDs or URLs
Scan methodsCan take part in bitmap index scans and backward scans

The same manual describes B-tree as the index for equality and range queries on sortable data, including <, <=, >=, >, BETWEEN, IN, IS NULL, sorted retrieval and left-anchored LIKE patterns. If a query needs any of that, a hash index will not be considered.

A short history that still matters

Before version 10 there was a warning message about using hash indexes. The PostgreSQL 10 release notes list two changes: "Add write-ahead logging support to hash indexes", which makes them crash-safe and replicatable and removes the old warning, and "Improve hash index performance". The same notes say hash indexes must be rebuilt after running pg_upgrade from any earlier major version, and that pg_upgrade creates a script to help. Advice that dismisses hash indexes as unsafe predates that release.

When a hash index makes sense

The manual's own description gives a short checklist:

  • Your hot queries are equality lookups, for example WHERE token = $1.
  • The table is large, and the workload is SELECT- and UPDATE-heavy.
  • The indexed values are unique, nearly unique, or have few rows per hash bucket.
  • The key is long enough that a 4-byte hash saves meaningful space.
  • The table is not growing quickly. Bucket splits happen in the foreground and "could increase execution time for user inserts".

A minimal example, with a table and column invented for illustration:

CREATE INDEX sessions_token_hash ON sessions USING HASH (token);

The CREATE INDEX reference confirms the syntax (USING method, with hash among the choices) and the Index Types chapter says the planner will consider a hash index whenever the indexed column appears in a comparison using the equal operator. Neither page promises a speedup, so compare plans and timings on your own data before keeping it.

If a few values are very common, the manual suggests excluding them with a partial index condition, using the WHERE predicate clause of CREATE INDEX. It adds that this may not suit every case.

Keeping an eye on it

The pgstattuple extension includes pgstathashindex(regclass), which reports bucket pages, overflow pages, bitmap pages, unused pages, live items, dead items and free percent. By default only roles with pg_stat_scan_tables (and superusers) can run these functions. Given how overflow pages work, a large number of overflow pages relative to bucket pages suggests crowded buckets and is worth investigating.

Two maintenance facts from the manual: VACUUM removes dead index tuples and tries to squeeze tuples onto fewer overflow pages, and there is no way to shrink a hash index or reduce its number of buckets other than rebuilding it with REINDEX.

Choosing quickly

Pick a B-tree unless you can answer yes to all of these: equality only, one column, no uniqueness requirement, large table, evenly spread values. In that narrow case a hash index is a supported, crash-safe option.

Take this further with Oakgen Agent

Explain index types, reason about a described schema and query pattern, run small Python calculations on index-size estimates, and write a test checklist or a markdown/docx summary.

Looking for a recap of one specific YouTube video? The YouTube Summarizer does that today.

Open this task in Oakgen Agent

The prompt is filled in for you. Nothing is sent until you press send.

Open in Oakgen Agent

Frequently asked questions

What is a hash index in PostgreSQL?

A hash index stores a 32-bit hash code of the indexed value rather than the value. According to the PostgreSQL manual, that means it can only handle simple equality comparisons. It is created with CREATE INDEX name ON table USING HASH (column).

Are PostgreSQL hash indexes safe to use?

Since PostgreSQL 10 they are. The version 10 release notes say write-ahead logging support was added to hash indexes, which makes them crash-safe and replicatable, and that the former warning about using them was removed. Hash indexes must be rebuilt after pg_upgrade from any version before 10.

Can a hash index enforce a unique constraint or cover several columns?

No. The manual states that hash indexes support only single-column indexes and do not allow uniqueness checking. Use a B-tree for unique constraints and multicolumn indexes.

When is a hash index better than a B-tree?

The manual says hash indexes are best optimized for SELECT- and UPDATE-heavy workloads that use equality scans on larger tables, and are most suitable for unique or nearly unique data. They may also be much smaller than B-trees for long items such as UUIDs or URLs. The documentation does not promise a speedup, so test on your own data.

How do I check the health of a hash index?

Install the pgstattuple extension and call pgstathashindex on the index. It returns the number of bucket, overflow, bitmap and unused pages, live and dead items, and free percent. By default only the pg_stat_scan_tables role and superusers can execute it.

Sources

  1. PostgreSQL Documentation, 65.6 Hash Indexes (current docs, read 2026-11-19)
  2. PostgreSQL Documentation, 11.2 Index Types (current docs, read 2026-11-19)
  3. PostgreSQL Documentation, CREATE INDEX (current docs, read 2026-11-19)
  4. PostgreSQL Documentation, Release 10 notes (read 2026-11-19)
  5. PostgreSQL Documentation, F.33 pgstattuple (current docs, read 2026-11-19)
Share