PostgreSQL Index Types: When to Use Each

Master the seven PostgreSQL index types and learn when to use each one to maximize query performance and minimize storage overhead.

Overview of PostgreSQL Index Types

PostgreSQL supports multiple index types, each optimized for specific access patterns:

B-tree Index (Default)

Overview

B-tree is the default and most commonly used index type. It keeps data sorted and allows efficient lookups, range scans, and sorting.

When to Use

• Equality comparisons (=)
• Range queries (<, >, BETWEEN)
• Sorting (ORDER BY)
• Pattern matching with prefix (LIKE 'abc%')

Examples

-- Create basic B-tree index
CREATE INDEX idx_users_email ON users (email);

-- Composite B-tree index
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

-- Descending index for sorting
CREATE INDEX idx_orders_date_desc ON orders (order_date DESC);

-- Partial index (only active users)
CREATE INDEX idx_users_active ON users (email) WHERE active = true;

-- Covering index (includes extra columns)
CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (total, status);

Performance Characteristics

-- These queries use B-tree efficiently
SELECT * FROM users WHERE email = '[email protected]';  -- Equality
SELECT * FROM orders WHERE order_date > '2025-01-01';   -- Range
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10; -- Sorting
SELECT * FROM users WHERE email LIKE 'john%';           -- Prefix matching

💡 Tip: B-tree index column order matters. Place the most selective columns first, and columns used in equality conditions before range conditions.

Composite Index Order

-- Good: Equality column first, then range
CREATE INDEX idx_orders_lookup ON orders (customer_id, order_date);

-- This query uses the index efficiently
SELECT * FROM orders
WHERE customer_id = 100 AND order_date > '2025-01-01';

-- This query can still use the index (leading column)
SELECT * FROM orders WHERE customer_id = 100;

-- This query CANNOT use the index efficiently
SELECT * FROM orders WHERE order_date > '2025-01-01';

Hash Index

Overview

Hash indexes use a hash function to map values to buckets. They're only useful for equality comparisons.

When to Use

• Simple equality lookups
• When you never need range queries on the column
• PostgreSQL 10+: Hash indexes are now WAL-logged and crash-safe

Examples

-- Create hash index
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);

-- Useful for UUID lookups
CREATE INDEX idx_items_uuid ON items USING HASH (uuid);

Limitations

-- Works with hash index
SELECT * FROM sessions WHERE session_token = 'abc123';

-- Does NOT work with hash index (use B-tree instead)
SELECT * FROM sessions WHERE session_token > 'abc';
SELECT * FROM sessions ORDER BY session_token;

⚠️ Consideration: In most cases, B-tree is a better choice. Hash indexes only provide marginal benefits for equality-only lookups and cannot be used for sorting or range queries.

GiST Index (Generalized Search Tree)

Overview

GiST is a generalized index that supports many different indexing strategies through operator classes. It's particularly useful for geometric and range data.

When to Use

• Geometric data (PostGIS)
• Range types (tsrange, int4range, etc.)
• Full-text search (alternative to GIN)
• Nearest-neighbor searches

Examples

-- Geometric data (PostGIS)
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

-- Range types
CREATE INDEX idx_events_period ON events USING GIST (period);

-- Full-text search
CREATE INDEX idx_articles_fts ON articles USING GIST (to_tsvector('english', content));

-- Exclusion constraint (no overlapping ranges)
CREATE TABLE room_bookings (
    room_id INTEGER,
    booking_period TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, booking_period WITH &&)
);

Range Type Queries

-- Create table with range type
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    period TSTZRANGE
);

CREATE INDEX idx_events_period ON events USING GIST (period);

-- Find overlapping events
SELECT * FROM events
WHERE period && '[2025-06-01, 2025-06-30]'::tstzrange;

-- Find events containing a specific time
SELECT * FROM events
WHERE period @> '2025-06-15 10:00:00'::timestamp;

Nearest Neighbor Search

-- Find 10 closest locations to a point
SELECT name, ST_Distance(geom, ST_MakePoint(-73.9857, 40.7484)) AS distance
FROM locations
ORDER BY geom <-> ST_MakePoint(-73.9857, 40.7484)
LIMIT 10;

SP-GiST Index (Space-Partitioned GiST)

Overview

SP-GiST supports partitioned search trees like radix trees and quadtrees. It's optimized for data that can be recursively divided into non-overlapping regions.

When to Use

• IP addresses (inet type)
• Phone numbers with prefix matching
• Geometric points (quadtree)
• Non-overlapping geometric data

Examples

-- IP address lookups
CREATE INDEX idx_ip_ranges ON network_ranges USING SPGIST (ip_range);

-- Phone number prefix search
CREATE INDEX idx_phone_prefix ON contacts USING SPGIST (phone_number text_ops);

-- Geometric points
CREATE INDEX idx_points_quad ON locations USING SPGIST (point);

IP Address Queries

-- Find network containing an IP
SELECT * FROM network_ranges
WHERE ip_range >>= '192.168.1.100'::inet;

-- Find networks within a range
SELECT * FROM network_ranges
WHERE ip_range << '192.168.0.0/16'::inet;

GIN Index (Generalized Inverted Index)

Overview

GIN indexes are inverted indexes optimized for values that contain multiple elements, such as arrays, JSONB, and full-text search vectors.

When to Use

• Array columns
• JSONB documents
• Full-text search (tsvector)
• hstore columns

Examples

-- Array containment
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- JSONB documents
CREATE INDEX idx_products_data ON products USING GIN (data);

-- JSONB with jsonb_path_ops (smaller, faster for @>)
CREATE INDEX idx_products_data_path ON products USING GIN (data jsonb_path_ops);

-- Full-text search
CREATE INDEX idx_articles_fts ON articles USING GIN (to_tsvector('english', body));

-- Trigram text search (requires pg_trgm)
CREATE INDEX idx_names_trgm ON users USING GIN (name gin_trgm_ops);

Array Queries

-- Create table with array column
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[]
);

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- Find posts with specific tag
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];

-- Find posts with any of these tags
SELECT * FROM posts WHERE tags && ARRAY['postgresql', 'mysql'];

-- Find posts with all of these tags
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'database'];

JSONB Queries

-- GIN index for JSONB
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- Containment query
SELECT * FROM products
WHERE attributes @> '{"brand": "Apple"}';

-- Key existence
SELECT * FROM products
WHERE attributes ? 'warranty';

-- Any key existence
SELECT * FROM products
WHERE attributes ?| array['warranty', 'guarantee'];

🚀 Performance Tip: Use jsonb_path_ops for containment queries only - it creates a smaller, faster index but doesn't support ?, ?&, or ?| operators.

BRIN Index (Block Range Index)

Overview

BRIN indexes store summary information about ranges of physical table blocks. They're extremely compact but only effective when data is naturally ordered.

When to Use

• Very large tables (hundreds of millions of rows)
• Data inserted in order (timestamps, sequential IDs)
• When storage is a concern
• Append-only tables

Examples

-- Timestamp column (data inserted chronologically)
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (created_at);

-- With smaller page range (more accurate, larger index)
CREATE INDEX idx_logs_timestamp_small ON logs USING BRIN (created_at)
    WITH (pages_per_range = 32);

-- Serial ID column
CREATE INDEX idx_events_id ON events USING BRIN (id);

Size Comparison

-- Compare index sizes (example with 100M rows)
-- B-tree: ~2.1 GB
-- BRIN:   ~100 KB (20,000x smaller!)

SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE tablename = 'logs';

When BRIN Works Well

-- Check correlation (how well physical order matches logical order)
SELECT
    attname,
    correlation
FROM pg_stats
WHERE tablename = 'logs';

-- correlation close to 1.0 or -1.0 = BRIN will work well
-- correlation close to 0 = BRIN will be ineffective

⚠️ Important: BRIN requires data to be physically ordered. If you have random inserts or heavy updates, BRIN will be ineffective.

Bloom Index

Overview

Bloom indexes use a Bloom filter data structure to test whether a set contains an element. They're useful when you need to index many columns for equality searches.

When to Use

• Tables with many columns
• Queries that filter on random subsets of columns
• When creating many B-tree indexes would be too expensive

Examples

-- Enable bloom extension
CREATE EXTENSION bloom;

-- Create bloom index on multiple columns
CREATE INDEX idx_products_bloom ON products USING BLOOM (
    category_id,
    brand_id,
    color_id,
    size_id,
    material_id
) WITH (length = 80, col1 = 2, col2 = 2, col3 = 2, col4 = 2, col5 = 2);

Query Patterns

-- Any combination of columns can use the bloom index
SELECT * FROM products
WHERE category_id = 5 AND color_id = 3;

SELECT * FROM products
WHERE brand_id = 10 AND material_id = 2;

SELECT * FROM products
WHERE category_id = 5 AND brand_id = 10 AND size_id = 7;

💡 Trade-off: Bloom indexes may produce false positives, requiring a recheck. They're a space-efficient alternative when you can't create B-tree indexes on all column combinations.

Index Type Comparison

Performance Characteristics

Decision Flowchart

Is your query...
├── Equality only?
│   └── Many columns? → Bloom
│   └── Single column? → Hash or B-tree
│
├── Range or sorting?
│   └── Data physically ordered + very large table? → BRIN
│   └── Otherwise → B-tree
│
├── Array/JSONB containment?
│   └── GIN
│
├── Geometric/spatial?
│   └── GiST or SP-GiST
│
├── Full-text search?
│   └── GIN (faster queries) or GiST (faster updates)
│
└── Range type overlaps?
    └── GiST with exclusion constraint

🔧 Index Maintenance

Monitor Index Usage

-- Find unused indexes
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
    idx_scan AS scans
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisunique
ORDER BY pg_relation_size(i.indexrelid) DESC;

Check Index Bloat

-- Estimate index bloat
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    round(100 * pg_relation_size(indexrelid) /
          nullif(pg_relation_size(relid), 0)) AS index_to_table_pct
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Rebuild Indexes

-- Rebuild index (blocks writes)
REINDEX INDEX idx_users_email;

-- Rebuild concurrently (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_users_email;

-- Rebuild all indexes on a table
REINDEX TABLE users;

✅ Best Practices

Index Creation Guidelines

1. Start with B-tree - It handles most use cases well
2. Index WHERE clause columns - Focus on filtered columns
3. Consider composite indexes - For multi-column queries
4. Use partial indexes - When queries filter on specific values
5. Add covering columns - To enable index-only scans

Avoid These Mistakes

1. Over-indexing - More indexes = slower writes
2. Indexing low-selectivity columns - Won't help queries
3. Wrong column order - In composite indexes
4. Ignoring maintenance - Bloated indexes hurt performance
5. Wrong index type - Match index to query pattern

Quick Reference Table

Conclusion

Choosing the right index type is crucial for PostgreSQL performance. While B-tree handles most cases, understanding when to use specialized indexes like GIN, GiST, or BRIN can dramatically improve query performance for specific workloads.

Key Takeaways

• ✅ B-tree - Default choice for most queries
• ✅ GIN - Arrays, JSONB, and full-text search
• ✅ GiST - Geometric and range types
• ✅ BRIN - Very large, ordered tables
• ✅ Monitor usage - Remove unused indexes

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in PostgreSQL, SQL optimization, and enterprise database solutions.

Related Articles

• PostgreSQL Query Planner Deep Dive
• PostgreSQL Full-Text Search Implementation
• PostgreSQL JSON and JSONB Performance

Need help with PostgreSQL optimization? Contact us for expert database consulting.