The default index in every relational database is a B-tree. When you say CREATE INDEX with no qualifier, you get a B-tree. When the planner reaches for an index, it usually reaches for a B-tree. The B-tree is the right answer for ordered access, range queries, equality lookups, and the vast majority of backend workloads. None of what follows is an argument against it.
The argument is for the cases where it is not the right answer. A B-tree on a column that contains a million-row JSON document is largely useless. A B-tree on a geographic point cannot efficiently answer "find all points within ten kilometers of this location." A B-tree on a sparsely-indexed boolean is wasted space. The other index types in PostgreSQL, MySQL, and SQLite exist for these cases, and the discipline of using them is part of what separates query plans that scale from query plans that don't.
Hash indexes
A hash index supports exactly one operation: equality. Given a value, find the rows that match. Range queries, ordered access, and sorting are all impossible with a hash index. In return, the lookup is O(1) on average, faster than the B-tree's O(log n) for very large tables.
For most workloads the constant-factor difference is invisible: a B-tree lookup on a billion rows is around 30 comparisons; a hash lookup is one. Both are fast. Hash indexes earn their place when the table is enormous, the lookup pattern is purely equality, and the difference between 30 comparisons and one matters at scale. They also earn their place when the indexed column is wide (UUIDs, hashes), because hash indexes store fixed-size hashes rather than the values themselves.
PostgreSQL's hash indexes were unsafe for replication until version 10; MySQL has hash indexes only on memory tables; SQLite does not have them at all. The default of "use a B-tree" is the right one. The exceptions are real but narrow.
Partial indexes
A partial index is a B-tree that only contains the rows matching a WHERE clause. You define it with CREATE INDEX ... WHERE status = 'active', and the index contains only the active rows. The lookup is the same as any B-tree; the savings is in space and write cost, because inserts that don't match the WHERE clause skip the index entirely.
The sweet spot is sparse predicates. A status column where 99% of rows are 'archived' and 1% are 'active' is a perfect partial index target. The full B-tree would store the archived rows for no benefit; the partial B-tree on WHERE status = 'active' indexes the rows you actually query.
Partial indexes are also the right answer for soft-delete patterns. WHERE deleted_at IS NULL in the index definition keeps the index small and fast for the only queries that matter. The deleted rows accumulate in the table without bloating the index.
The catch: the planner only uses a partial index when the query's WHERE clause is provably a subset of the index's WHERE clause. SELECT * FROM users WHERE active = true uses a partial index defined on WHERE active = true; SELECT * FROM users WHERE active = true AND created_at > ... also uses it; but SELECT * FROM users WHERE active = true OR something_else does not. The query has to fit inside the index's predicate.
GIN indexes
GIN stands for Generalized Inverted iNdex, and it is PostgreSQL's index for "containment" queries. The classic case is full-text search: a column of documents, a query of words, find the documents that contain all the words. GIN inverts the relationship: the index stores, for each word, the list of documents containing it. Lookup is O(words in query) rather than O(documents).
The same structure works for arrays (find rows where the array column contains 'admin'), JSON (find rows where the JSON contains a particular key), and trigrams (find rows where the text contains a substring). PostgreSQL's jsonb_path_ops GIN operator class is the right answer for application logs stored as JSONB; without it, every query scans every row.
GIN indexes are slow to build and slow to update. Each insert has to update many index entries (one per token in the inserted value). For write-heavy workloads, GIN can become a bottleneck; the typical mitigation is fastupdate=on, which batches the updates and applies them lazily. The trade-off is that occasional vacuum operations become much more expensive.
BRIN indexes
BRIN (Block Range INdex) is a niche index designed for one specific shape of data: append-only or near-append-only tables where the indexed column is correlated with insertion order. The classic example is a time-series table where the timestamp is monotonically increasing and rows are inserted in roughly chronological order.
A BRIN index does not store one entry per row. It stores one entry per block range (typically 128 contiguous pages, configurable). Each entry records the min and max of the indexed column in that range. A query for "rows where timestamp is between A and B" scans the index, identifies which block ranges might contain matches, and only reads those blocks from disk.
The space saving is dramatic: a BRIN index on a billion-row time-series table can be a few megabytes, where a B-tree would be tens of gigabytes. The cost is that BRIN only helps when the data is correlated. A BRIN on an unsorted column is worse than no index at all, because every block range will overlap with every query.
GiST indexes
GiST (Generalized Search Tree) is the framework PostgreSQL uses for indexes that don't fit the B-tree's strict ordering model. Spatial indexes (PostGIS), range type indexes, and trigram indexes are all GiST. The defining feature is that the index stores rough bounding regions and refines down to exact answers; the lookup is fast even when the data has no natural total order.
The most common GiST application is geographic. PostGIS uses GiST to store bounding boxes around geographic objects, and a "find points within ten kilometers of this location" query becomes a bounding-box intersection plus a filter, both of which the index supports. Without GiST, the query is a sequential scan of every point.
The discipline
The honest summary is that 95% of indexes should be B-trees and most developers will never need to think about the other types. The remaining 5% is where the interesting performance work lives. A partial index on a soft-delete column. A GIN index on the JSONB metadata column that the application queries by key. A BRIN index on the events table that holds two years of timestamps. Each one turns a query plan that does not scale into one that does.
The way to develop the instinct is to read your EXPLAIN output. When you see a sequential scan on a table that should be indexed, ask why. If the answer is "the column is JSONB" or "the predicate is sparse" or "the data is time-correlated," the right index is one of the four above. The B-tree is the default, and the default is usually right; the discipline is recognizing when it isn't.
The four APIs we run at DocuMint, CronPing, FlagBit, and WebhookVault use mostly B-trees, one partial index per soft-deleted table, and one BRIN index on WebhookVault's request log because the timestamp is monotonically increasing and the query pattern is exclusively time-range. That is a complete picture of what works in our scale band; bigger systems would have more.