DataTerminology.comNEW
An index that uses a hash function to map keys to buckets, enabling very fast equality lookups.
DESCRIPTION
A hash index accelerates point lookups by hashing the indexed key and using the hash value to locate matching entries in buckets. It is highly efficient for exact-equality predicates but generally unsuitable for range queries or ORDER BY operations. Many relational databases implement hash indexes or hash-based access paths, though capabilities vary. Hash indexes can reduce I/O and CPU for workloads dominated by equality filters and equi-joins. They often rely on fixed-size hash codes and may require bucket splits or rehashing. Maintenance costs can rise with frequent updates or skewed key distributions. Some systems provide adaptive or automatic hash indexing for hot access paths.
ALTERNATIVE DEFINITIONS
PostgreSQL Documentation
Hash indexes store a 32-bit hash of the value of the index key and support only simple equality comparisons.
Highlights 32-bit hash storage and the equality-only nature of hash indexes.
MySQL Reference Manual
The InnoDB adaptive hash index automatically builds a hash index for frequently accessed index pages to speed up lookups.
Emphasizes adaptive, automatic creation of hash access paths for hot workloads.
Microsoft Learn
A hash index for memory-optimized tables uses an array of buckets to locate rows for equality searches on the index key.
Covers SQL Server’s memory-optimized hash indexes and their bucket-based lookup behavior.
RELATED TERMS
Index Hash Table Equality Predicate Nonclustered Index Join Hash Bloom Filter Partitioning Online Transaction Processing Key-Value Store
COMPETING TERMS
RELATIONS
- Broader: Index
KNOWLEDGE AREAS
Data Storage & Operations Data Engineering Data Architecture Data Warehousing & BI
TARGET AUDIENCE
Data Engineer Software Developer Data Architect Data Modeler
EXTERNAL SOURCES
- Wikipedia - Database index
- PostgreSQL Documentation - PostgreSQL hash index reference
- MySQL - Adaptive hash index guide
- Microsoft - SQL Server hash indexes (Hekaton)
TOP QUESTIONS
When should I use a Hash Index?
Use a Hash Index for workloads dominated by equality predicates on the indexed key. It excels at point lookups but is generally unsuitable for range scans or sorting.
Category: Requirements
Why is a Hash Index poor for range queries?
Hash functions scramble key order, so adjacent keys don’t map to adjacent buckets. As a result, range predicates cannot exploit locality, leading to full scans.
Category: Misunderstanding
What is a common mistake with Hash Indexes?
Creating them on columns used for range filters or ORDER BY is a mistake. B-tree indexes are a better choice for ordering or range-based access.
Category: Mistakes
How does a Hash Index compare to a B-tree Index?
Hash indexes are typically faster for equality lookups, while B-trees support both equality and range queries. B-trees are more versatile but may be slightly slower for pure equality.
Category: Alternatives
What is the risk of using a Hash Index with skewed data?
Skewed key distributions can cause bucket hotspots and collisions, degrading performance. Monitoring and periodic rehashing or alternative indexing may be necessary.
Category: Risks
Does a Hash Index reduce storage size?
Not necessarily. It stores hash codes and pointers, which can be compact, but overhead varies by implementation. Compression is uncommon compared to columnar formats.
Category: Misunderstanding
Can Hash Indexes accelerate joins?
Yes, for equi-joins on the indexed key, hash indexes can speed lookups of matching rows. Performance depends on collision rates and memory locality.
Category: Example
How are collisions handled in a Hash Index?
Typical strategies include chaining and open addressing. Implementations vary and influence performance, especially under high load or skew.
Category: Example
Are Hash Indexes transactional and durable?
In mature databases, hash indexes integrate with transaction logging and recovery. Durability depends on the engine’s WAL or journaling implementation.
Category: Requirements