GIN vs B‑Tree Indexes in PostgreSQL: A Complete Comparison

PostgreSQL supports multiple index types, but the two most commonly used are B‑Tree and GIN (Generalized Inverted Index).
Choosing the wrong index type can lead to slow queries, large indexes, and poor performance.

In this article, you will learn:

• What B‑Tree and GIN indexes are
• How they work internally
• When to use each index
• Performance differences
• Real‑world examples

This guide is beginner‑friendly and practical.

---

What Is a B‑Tree Index?

B‑Tree (Balanced Tree) is the default index type in PostgreSQL.

Characteristics:

• Stores values in sorted order
• Very fast for range and equality queries
• Efficient for most relational data

Typical Use Cases

• Primary keys
• Foreign keys
• Equality searches
• Range queries

Example:

SQL

CREATE INDEX idx_users_email

ON users (email);

Show more lines

---

What Is a GIN Index?

GIN (Generalized Inverted Index) is designed for composite values where one row contains multiple searchable items.

Characteristics:

• Indexes individual elements inside a value
• Excellent for JSONB, arrays, and full‑text search
• Slower to update, faster to search complex structures

Typical Use Cases

• JSONB columns
• Array columns
• Full‑text search (tsvector)

Example:

SQL

CREATE INDEX idx_users_profile

ON users

USING GIN (profile);

Show more lines

---

Core Difference: How Data Is Indexed

B‑Tree

• One index entry per row
• Row‑based indexing
• Best for simple scalar values

GIN

• Multiple index entries per row
• Element‑based indexing
• Best for structured or multi‑value data

---

Supported Operators Comparison

B‑Tree Supports

• =
• <, >, <=, >=
• BETWEEN
• ORDER BY
• LIKE 'text%'

GIN Supports

• @> (JSON containment)
• ?, ?|, ?& (JSON operators)
• Array membership
• Full‑text search operators

---

Performance Comparison

Feature	B‑Tree	GIN
Read performance	✅ Very fast	✅ Fast (complex queries)
Write performance	✅ Fast	❌ Slower
Index size	✅ Small	❌ Large
Range queries	✅ Excellent	❌ Not supported
JSON / Array search	❌ Poor	✅ Excellent
Default index type	✅ Yes	❌ No

---

Example 1: Equality Search (B‑Tree Wins)

Query:

SQL

SELECT *

FROM users

WHERE email = 'ravi@example.com';

Show more lines

Best index:

SQL

CREATE INDEX idx_users_email

ON users (email);

Show more lines

✅ B‑Tree is optimal
❌ GIN provides no benefit here

---

Example 2: JSONB Search (GIN Wins)

Query:

SQL

SELECT *

FROM users

WHERE profile @> '{"role": "admin"}';

Show more lines

Best index:

SQL

CREATE INDEX idx_users_profile

ON users

USING GIN (profile);

Show more lines

✅ GIN excels
❌ B‑Tree cannot index JSON structures effectively

---

Example 3: Range Query (B‑Tree Only)

Query:

SQL

SELECT *

FROM orders

WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

Show more lines

Best index:

SQL

CREATE INDEX idx_orders_created_at

ON orders (created_at);

Show more lines

✅ B‑Tree supports range scans
❌ GIN does not support range queries

---

Write Performance Impact

B‑Tree

• Low overhead on INSERT/UPDATE
• Suitable for high‑write systems

GIN

• Each write updates multiple index entries
• Slower inserts and updates
• Requires autovacuum and reindexing

---

Storage Differences

• B‑Tree indexes are compact
• GIN indexes can grow very large
• JSONB with many keys increases GIN size significantly

Choosing GIN without need wastes storage and CPU.

---

When to Use B‑Tree Index

Use B‑Tree when:

• Indexing primary keys
• Filtering by single values
• Performing range queries
• Sorting data
• High write performance is required

✅ Default choice in most cases

---

When to Use GIN Index

Use GIN when:

• Searching inside JSONB or arrays
• Using @> or full‑text search
• Queries involve multiple values inside one column
• Read performance is more important than write speed

✅ Specialized but powerful

---

Common Mistakes

❌ Using GIN for simple columns
❌ Using B‑Tree for JSONB searches
❌ Over‑indexing with both types unnecessarily
❌ Not verifying with EXPLAIN ANALYZE

---

How to Decide (Quick Rule)

• Simple values → B‑Tree
• Complex values → GIN
• Range queries → B‑Tree
• Containment / search → GIN

When in doubt, start with B‑Tree, then optimize.

---

Summary

• B‑Tree is the default and most widely used index
• GIN is designed for complex, multi‑value data
• B‑Tree excels at equality and range queries
• GIN excels at JSON, arrays, and full‑text search
• Choosing the right index dramatically improves performance

You now understand GIN vs B‑Tree indexes in PostgreSQL.