A database index guide helps developers pick the right index type for their query pattern. Choosing between B-tree, Hash, GIN, GiST, and BRIN indexes depends on your data type, query operators, and cardinality — the wrong choice wastes storage and slows writes without improving reads.
Find Your Index Type
All Index Types Reference
How to Choose a Database Index Type
Choosing the right database index can reduce query time from seconds to milliseconds. This guide focuses on PostgreSQL index types, though similar types exist in MySQL, SQLite, and other databases.
Step 1: Identify Your Query Pattern
The most important factor is which operators your WHERE clause uses. B-tree handles equality (=) and range (<, >, BETWEEN, LIKE 'prefix%'). Hash handles equality-only and is faster for exact lookups. GIN handles @> (contains), @@ (full-text match), and && (overlaps) on array/JSONB/tsvector columns.
Step 2: Check Cardinality
Indexes are most useful on high-cardinality columns. An index on a boolean column with 50% true/50% false distribution is usually ignored by the query planner — a sequential scan is faster. An index on a UUID or email column (extremely high cardinality) provides massive speedups.
Step 3: Consider Table Size and Access Pattern
For very large tables (100M+ rows) with naturally ordered data like timestamps, BRIN indexes are extremely compact. A BRIN index on a 500M-row events table might be 128KB vs 8GB for a B-tree index on the same column — trading some precision for 64,000x storage savings.
Step 4: Measure Before and After
Always use EXPLAIN ANALYZE before adding an index and again after. The query planner may not use your index if statistics are stale. Run ANALYZE tablename to refresh statistics after bulk inserts.
Composite Indexes
A composite B-tree index on (user_id, created_at) supports queries filtering by user_id alone OR by (user_id, created_at). The leftmost column rule: the index can be used for any query that filters on user_id, but not for queries that filter on created_at alone without user_id.
FAQ
Is this database indexing guide free?
Yes, completely free with no account required. All content is available instantly in your browser.
What is the difference between B-tree and Hash indexes?
B-tree indexes support range queries, sorting, and equality checks — making them the most versatile index type and the default in most databases. Hash indexes only support equality comparisons (=) but are faster for exact lookups since they have O(1) average lookup time vs O(log n) for B-tree.
When should I use a GIN index in PostgreSQL?
Use GIN (Generalized Inverted Index) for full-text search, JSONB columns, and array columns where you need to search for elements within the array. GIN is ideal when a single document or column contains multiple indexable items, such as searching for rows where a JSONB array contains a specific value.
What is a BRIN index and when is it useful?
BRIN (Block Range Index) stores summary information about block ranges in a table. It is extremely compact and fast to build but less precise — useful for very large tables with naturally ordered data like timestamps or sequential IDs. BRIN indexes are ideal for time-series data where you filter by date ranges.
How many indexes should a table have?
There is no hard limit, but each index adds write overhead (every INSERT/UPDATE/DELETE must update all indexes). A table should have indexes on columns that appear in WHERE clauses, JOIN conditions, and ORDER BY clauses in frequent queries. Avoid indexing columns with very low cardinality (like boolean flags) as they rarely help the query planner.
What is index cardinality and why does it matter?
Cardinality refers to the number of distinct values in an indexed column. High cardinality (many distinct values like user IDs or emails) benefits most from B-tree indexes. Low cardinality (few distinct values like status or boolean) may not benefit from standard indexes — the query planner may choose a full table scan instead.