PostgreSQL Index Types Compared: B-tree, GIN, GiST, BRIN, and Hash

Indexes are one of the fastest ways to make PostgreSQL feel better and one of the easiest ways to make write performance, storage, and migrations worse. The hard part is not knowing that an index can speed up a query. The hard part is choosing the index type that matches the access pattern instead of treating every slow query as a request for another default B-tree.

This comparison focuses on the index types a product team is most likely to consider in a managed PostgreSQL application: B-tree, GIN, GiST, BRIN, and hash. The goal is practical selection. If you are deciding how to support account lookups, JSONB filters, geospatial-style ranges, event tables, or append-heavy analytics, the index type matters as much as the columns you pick.

The short version

PostgreSQL's default B-tree index is the right first choice for many ordinary application queries: equality, ordering, ranges, primary keys, and unique constraints. That does not make it universal. GIN is often the better fit when one row contains many searchable values, such as JSONB keys, arrays, or full-text tokens. GiST is a framework for more specialized search problems, especially geometric, range, nearest-neighbor, and exclusion-constraint use cases. BRIN is designed for very large tables where nearby physical rows tend to contain nearby values. Hash indexes are narrow: equality only, and usually worth considering only when a B-tree is demonstrably not the better fit.

A useful rule is to start from the question the database must answer, not from the column type. created_at in an orders table may want a B-tree for recent ordered pages. The same kind of timestamp in a billion-row append-only event table may be a BRIN candidate if rows arrive roughly in time order. A jsonb column may not need a GIN index at all if the application only reads it after finding the row by tenant and primary key.

B-tree: the default for a reason

B-tree indexes are PostgreSQL's default because they support the comparisons most applications use every day. Equality lookups, ordered scans, range predicates, unique indexes, primary keys, and many ORDER BY queries all fit naturally. A multi-column B-tree can be excellent when the leading columns match the way the application filters, for example tenant_id followed by a timestamp or a stable business key.

The common production mistake is not choosing B-tree. It is accumulating too many similar B-trees. An index on (tenant_id, created_at), another on (tenant_id, status, created_at), and another on (tenant_id, created_at desc) may all have explanations in isolation, but together they increase write amplification and migration risk. Each insert, update, and delete must maintain every relevant index. On a managed database with predictable plan limits, unnecessary indexes can become the hidden cost behind a workload that seems small by row count.

When adding a B-tree, check whether an existing index can already support the query. The leftmost-prefix behavior of multi-column indexes is often enough for related filters, while a covering index with included columns can avoid heap visits for a very specific read path. Keep the design tied to real query plans, not hypothetical future screens.

GIN: search inside collections

A GIN index is built for cases where a single row contains multiple searchable keys or values. PostgreSQL's documentation describes GIN as useful for composite values where an index must find rows containing a component value. In web applications, the most familiar examples are JSONB containment, arrays, and full-text search vectors.

GIN is often the right answer when a query asks, "which rows contain this key, tag, token, or array element?" For example, a product catalog that filters on JSONB attributes can use a GIN index for containment queries. A document table can use GIN for full-text search. A project table with a tags array can use GIN for overlap and containment operators.

The tradeoff is that GIN indexes are not free. They can be larger than a simple B-tree and more expensive to update, especially when indexed documents or JSON blobs change frequently. They also depend on operator classes. A JSONB GIN index optimized for general containment is not the same design decision as a path-ops variant optimized for a narrower class of containment queries. Before creating one, write down the exact operators the application uses and confirm them with EXPLAIN.

create index concurrently products_attributes_gin
on products using gin (attributes);

For managed PostgreSQL teams, the migration detail matters. Building a large GIN index during a busy period can put pressure on I/O and storage. CREATE INDEX CONCURRENTLY reduces write blocking compared with a regular index build, but PostgreSQL documents important caveats: it takes longer, performs more work, and cannot run inside a transaction block. Treat it as an operational migration, not a tiny schema tweak.

GiST: flexible indexes for specialized operators

GiST is best understood as an indexing framework rather than one single behavior. It supports several specialized operator families, including geometric search, range types, nearest-neighbor queries, and exclusion constraints. If your query is about overlap, containment, distance, or preventing conflicting ranges, GiST may be the correct tool.

A booking system is a good example. A B-tree can help find rows by room ID or start time, but it does not naturally enforce "no overlapping reservations for the same room." PostgreSQL exclusion constraints can use GiST indexes to express that rule at the database level. That is a different kind of value from speeding up a list page: the index helps protect an invariant that application code can easily race.

GiST needs more care because the details depend on the data type and operator class. It is not a universal replacement for GIN or B-tree. For some range queries it is exactly the right model; for ordinary equality on a customer ID, it is needless complexity. Use GiST when the problem statement includes ranges, geometry, nearest-neighbor ordering, or conflict prevention that maps to documented operators.

BRIN: tiny indexes for very large ordered tables

BRIN indexes are designed around block ranges. Instead of storing every indexed value, a BRIN index stores summaries about ranges of pages. That makes it small and cheap to maintain, but it also makes it lossy. PostgreSQL can use the index to skip large parts of a table, then recheck candidate rows from matching block ranges.

This is useful when the physical order of the table correlates with the indexed value. Append-only event tables are the classic application case. If events are inserted roughly in created_at order, a BRIN index on created_at can help queries for a recent day avoid scanning months of older blocks while staying dramatically smaller than a full B-tree on the same column.

BRIN is a poor fit when values are randomly distributed across the table. If every block contains a wide spread of tenants, statuses, or timestamps, the summaries become too broad and the executor must recheck too much data. For SaaS workloads, BRIN often works best for time-based retention, audit logs, metrics, and append-heavy activity streams where the query windows follow insertion order.

create index concurrently events_created_at_brin
on events using brin (created_at);

The practical test is correlation. Run the representative query with EXPLAIN (ANALYZE, BUFFERS) on staging data that resembles production. If the BRIN index still reads a large share of the table, the physical layout may not support the design.

Hash: equality-only and rarely the first pick

Hash indexes support equality comparisons. That sounds attractive for lookup-heavy applications, but B-tree indexes also support equality and support many other operations. Because of that flexibility, B-tree remains the safer default for most primary-key, foreign-key, slug, and email lookups.

A hash index can be reasonable only after you have a narrow equality workload, a tested reason B-tree is not ideal, and confidence that the reduced operator support will not limit future queries. For most product teams, the better performance work is cleaning redundant indexes, improving multi-column order, or fixing query shape before reaching for hash.

How to choose in a real application

The decision becomes easier when you name the workload instead of the column. Account lookup by ID is not the same problem as filtering JSON attributes, even if both appear on the same page. A dashboard over recent audit events is not the same as a back-office search over all historical events.

For a new feature, add the minimum index that supports the first production query well. For an existing slow query, start with the actual plan. Look for sequential scans over large tables, bitmap scans that recheck too much data, sorts that spill, or nested loops that multiply a small mistake. The right index type should reduce the expensive part of the plan you can see.

Operational guidance before you add the index

Indexes are production objects, not just syntax. Before adding one to a managed PostgreSQL database, estimate the storage impact, check whether the table is write-heavy, and decide how the migration will run. Regular CREATE INDEX can block writes; CREATE INDEX CONCURRENTLY is safer for availability but slower and more operationally sensitive. On large tables, schedule the change, monitor I/O, and keep a rollback plan that includes dropping the new index if it hurts more than it helps.

After rollout, verify usage instead of assuming success. pg_stat_user_indexes can show whether an index is being scanned, while query-level measurement shows whether latency actually improved. An unused index is not harmless. It consumes storage, slows writes, adds vacuum work, and makes future migrations slower.

If you run PostgreSQL on ArmorDB, this is also where managed operations help: pair index changes with backups, migration discipline, and connection pooling rather than making performance changes in isolation. The PgBouncer guide is relevant when slow queries are mixed with connection pressure, and the backup documentation is worth checking before large index builds or table rewrites.

Common mistakes

The most common indexing mistake is adding an index for every filter shown in the UI. PostgreSQL can combine some indexes, but a pile of single-column indexes is rarely as good as one carefully ordered multi-column index for the actual query. Another mistake is indexing low-selectivity columns such as boolean flags without considering the rest of the predicate. If half the table matches, the planner may correctly prefer a sequential scan.

A subtler mistake is indexing flexible data too early. JSONB plus GIN can be powerful, but if the application has already settled on a few stable fields, ordinary typed columns with B-tree indexes may be simpler, smaller, and easier to reason about. Use flexible indexing where the product truly needs flexible search.

Takeaway

Choose PostgreSQL index types by access pattern. B-tree is the default for ordinary application lookups and ordering, GIN is for searching within multi-value documents, GiST is for specialized operators and constraints, BRIN is for huge correlated tables, and hash is a narrow equality-only option. The winning index is the one that improves a measured query without quietly taxing every write that follows.

Sources and further reading

• PostgreSQL documentation on index types: https://www.postgresql.org/docs/current/indexes-types.html
• PostgreSQL CREATE INDEX documentation, including CONCURRENTLY behavior: https://www.postgresql.org/docs/current/sql-createindex.html
• PostgreSQL GIN index documentation: https://www.postgresql.org/docs/current/gin.html
• PostgreSQL BRIN index documentation: https://www.postgresql.org/docs/current/brin.html