Btree vs Hash Index in PostgreSQL: Key Differences and Usage

In PostgreSQL, Btree indexes are the default and support a wide range of queries including range scans, while Hash indexes are optimized for simple equality checks. Btree indexes are more versatile and reliable, whereas Hash indexes can be faster for exact matches but have limitations and require careful use.

Quick Comparison

This table summarizes the main differences between Btree and Hash indexes in PostgreSQL.

Factor	Btree Index	Hash Index
Supported Queries	Equality, range, ORDER BY, DISTINCT	Equality only
Default Index Type	Yes	No
Performance	Good for most queries	Faster for equality lookups
Crash Safety	Fully WAL-logged and crash-safe	WAL-logged since PostgreSQL 10, previously unsafe
Disk Space	Moderate	Usually smaller
Use Cases	General purpose indexing	Specific equality lookups on large tables

Key Differences

Btree indexes organize data in a balanced tree structure, allowing efficient searching for both exact matches and range queries like <, >, BETWEEN. This makes them versatile for many query types including sorting and grouping.

Hash indexes use a hash function to map keys to buckets, making them very fast for equality comparisons (WHERE column = value). However, they do not support range queries or ordering.

Historically, Hash indexes in PostgreSQL were not crash-safe and could be corrupted after a crash, but since version 10 they are WAL-logged and safer. Still, Btree indexes remain the default because of their flexibility and reliability.

Code Comparison

Creating and using a Btree index for a simple equality query:

sql

CREATE TABLE users (id SERIAL PRIMARY KEY, username TEXT);

CREATE INDEX users_username_btree_idx ON users USING btree (username);

-- Query using the Btree index
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'alice';

Output

Index Scan using users_username_btree_idx on users  (cost=0.29..8.31 rows=1 width=36) (actual time=0.010..0.012 rows=1 loops=1)
Planning Time: 0.123 ms
Execution Time: 0.030 ms

Hash Index Equivalent

Creating and using a Hash index for the same equality query:

sql

CREATE TABLE users (id SERIAL PRIMARY KEY, username TEXT);

CREATE INDEX users_username_hash_idx ON users USING hash (username);

-- Query using the Hash index
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'alice';

Output

Index Scan using users_username_hash_idx on users  (cost=0.29..8.31 rows=1 width=36) (actual time=0.010..0.012 rows=1 loops=1)
Planning Time: 0.110 ms
Execution Time: 0.040 ms

When to Use Which

Choose Btree indexes for most cases because they support a wide range of queries including equality, range scans, and sorting. They are reliable and fully crash-safe.

Use Hash indexes only when you have very large tables and your queries are strictly equality checks on columns with no need for ordering or range queries, and you want potentially faster lookups.

In general, Btree is the safer and more flexible choice, while Hash is a specialized tool for specific performance needs.

Key Takeaways

Btree indexes support equality and range queries and are the default in PostgreSQL.

Hash indexes are optimized for equality checks but do not support range or ordering.

Btree indexes are fully crash-safe and reliable; Hash indexes became crash-safe only since PostgreSQL 10.

Use Btree indexes for general purposes; use Hash indexes only for large tables with frequent equality lookups.

Hash indexes may offer faster equality lookups but have limited use cases compared to Btree.