Types of Indexes
Not all indexes are the same. Different index types serve different access patterns. Understanding B-tree, Hash, Partial, Unique, Full-Text, and GiST indexes helps you pick the right tool for each performance problem.
Index Types Overview
- B-tree (default) - balanced tree, great for range queries and equality: =, <, >, BETWEEN, ORDER BY
- Hash - equality only (WHERE col = val), faster than B-tree for = only, no range
- Partial - index only rows matching a condition: WHERE status = 'active'
- Unique - unique B-tree, enforces uniqueness (PRIMARY KEY, UNIQUE constraint)
- Full-Text (GIN) - for text search: WHERE to_tsvector(col) @@ query
- GiST - spatial/geometric data (PostGIS, range types)
- BRIN - Block Range Index, for very large tables with naturally sorted data (timestamps)
Index Type Examples
-- B-tree (default - most common)
CREATE INDEX idx_products_price ON products(price);
-- Helps: WHERE price > 500, ORDER BY price, BETWEEN
-- Hash index (equality only)
CREATE INDEX idx_orders_status_hash ON orders USING HASH (status);
-- Helps: WHERE status = 'pending' (not: WHERE status > 'p')
-- Partial index (only index active orders)
CREATE INDEX idx_orders_pending ON orders(user_id)
WHERE status = 'pending';
-- Only indexes pending orders - smaller, faster for that filter
-- Full-text search index (PostgreSQL GIN)
CREATE INDEX idx_products_fts ON products
USING GIN (to_tsvector('english', name || ' ' || COALESCE(description, '')));
-- Enables fast: WHERE to_tsvector ... @@ to_tsquery(...)
-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Enforces uniqueness AND speeds up email lookups
-- BRIN index (for huge append-only tables)
CREATE INDEX idx_orders_created_brin ON orders
USING BRIN (created_at);
-- Very small index, fast for time-range queries on huge tablesQuick Quiz
Tip
Tip
Practice Types of Indexes in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
B-Tree default. Hash for = only. Full-text for search. GiST for geospatial + PostGIS.
Common Mistake
Warning
A common mistake with Types of Indexes is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Types of Indexes from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Not all indexes are the same.
- B-tree (default) - balanced tree, great for range queries and equality: =, <, >, BETWEEN, ORDER BY
- Hash - equality only (WHERE col = val), faster than B-tree for = only, no range
- Partial - index only rows matching a condition: WHERE status = 'active'