Currently Available: Need a skilled Software Developer for your next project?
Categories
Databases PostgreSQL

Choosing the Right PostgreSQL Index Type

When a PostgreSQL query takes too long to find or sort rows, the right index can reduce the work the database must do. An index is a separate data structure that helps PostgreSQL locate table rows; it also takes storage and adds work when rows change. The best choice depends on what the query searches for, how it orders results, and how the table’s data is arranged.

Start with B-tree when a query checks whether a value matches exactly or falls within a range. Choose another index type when the query searches inside values made up of multiple parts, uses specialized operators, or reads a large table whose values follow its physical row order. PostgreSQL’s query planner decides whether to use an available index, so confirm the choice against representative data and the actual query.

Match the index to the query

Before creating an index, identify how the query compares and orders values. A query that checks for one exact value needs different index features from one that checks a span of values, such as dates between two points. Searches for text patterns, values inside arrays, or the nearest values also need different features. PostgreSQL connects an index method to supported data types and operators through an operator class, which defines how that index handles a particular kind of value and search operation. The PostgreSQL index type documentation describes the operators each method supports.

Index type Good starting point Main trade-off
B-tree Equality, ranges, sorting, and queries that return the first rows in an order Flexible, but it does not support every search pattern.
Hash Equality comparisons only Narrower capabilities than B-tree.
GiST Specialized searches supported by a matching operator class, including some spatial and distance searches What it can search depends on the operator class.
SP-GiST Data suited to partitioned structures such as quadtrees, k-d trees, or tries Specialized and dependent on operator-class support.
GIN Searches for components inside values, such as array elements A row can create multiple index entries, increasing write and maintenance work.
BRIN Large tables where column values track physical row order Compact summaries produce candidate rows that PostgreSQL must recheck.

These methods serve different query patterns, so no type is fastest for every workload. Indexes also add storage and data-change costs; adding one for a query that rarely runs can slow writes without improving much overall. PostgreSQL’s index overview describes this basic trade-off.

Use B-tree for ordinary searches and ordering

For columns that hold single values, such as identifiers, dates, and numbers, B-tree is the default choice. It supports checks for an exact value or a range of values, can search for NULL values, and can return rows in index order. A B-tree can also support some text searches where the pattern starts with fixed text, such as LIKE 'foo%'; locale and operator-class details matter. A pattern that starts with a wildcard, such as LIKE '%bar', does not become a normal B-tree lookup.

B-tree is especially useful when a query filters to a small set of rows and then sorts them. For example, a feed query that selects recent events for one tenant can use a multicolumn B-tree:

CREATE INDEX events_tenant_time_idx
    ON events (tenant_id, created_at DESC);

SELECT id, created_at
FROM events
WHERE tenant_id = $1
ORDER BY created_at DESC
LIMIT 50;

A matching index orders each tenant’s events by descending time, so PostgreSQL can find the requested tenant’s newest rows first, avoid a separate sort, and stop after finding enough rows for LIMIT. If a query reads a large share of the table, a sequential scan followed by a sort can still be faster. See PostgreSQL’s guidance on indexes and ORDER BY.

Column order matters in a multicolumn B-tree. Put columns the query checks for one exact value first, followed by a column the query checks against a range of values or uses to order results. For example, (tenant_id, created_at) fits queries that constrain tenant_id and then search or sort by created_at. PostgreSQL can sometimes use skip scan when a query omits a leading-column condition, but that depends on the planner’s estimates. Design the index for recurring query patterns rather than relying on that behavior. The multicolumn index documentation also advises using multicolumn indexes sparingly.

Choose specialized types for specialized searches

A hash index supports equality comparisons only. PostgreSQL stores a 32-bit hash code for each indexed value, so hash indexes do not provide range searches or sorted output. For workloads with concurrent activity on columns that hold single values, PostgreSQL’s documentation favors B-tree over hash. Treat hash as a candidate to benchmark for a specific equality-only workload, not as a general replacement.

GiST and SP-GiST provide search structures for particular data types and operators. GiST can support searches such as finding nearby values when the relevant operator class provides a distance operation. SP-GiST supports quadtrees and k-d trees, along with tries, when the operator class is designed for those partitioned structures. Before choosing either method, confirm that the data type’s operator class supports the exact search the query performs.

GIN is suited to values with multiple searchable parts. It can index the components of an array, for example, so a query can search for an element inside the array. PostgreSQL 17 documents GIN as the preferred index type for full-text search. GiST text-search indexes can return extra candidate rows, so PostgreSQL must check those rows again. That recommendation is specific to the PostgreSQL 17 text-search documentation.

Because GIN indexes the separate parts of a value, inserting or updating one row can add several index entries and take more work. With GIN’s fastupdate setting enabled, PostgreSQL defers some index work to a pending list, which can speed up many updates. Searches may then need to inspect those entries, and cleanup can slow an individual update. PostgreSQL describes these trade-offs in its index locking documentation.

BRIN stores summaries for ranges of neighboring table pages. A BRIN index suits a very large table when a column’s values correlate with physical row order, such as timestamps in a table that mostly receives new rows in time order. PostgreSQL uses the summaries to skip page ranges that cannot match, then rechecks candidate rows because the summaries do not identify exact matches. Smaller pages_per_range values create more index entries and more precise summaries; larger values make the index smaller but less precise. See the BRIN documentation.

Shape indexes around recurring query patterns

A composite index is not always the right answer when different queries filter on different column combinations. For some AND and OR conditions, PostgreSQL can combine separate indexes with bitmap scans. These scans collect the locations of matching rows in memory. That gives the planner flexibility, but the combined result loses the ordering of the original indexes and may need a separate sort. Compare separate indexes with a composite index when query patterns vary; use the composite index when the same filter-and-order pattern recurs. PostgreSQL explains this behavior in its documentation on combining multiple indexes.

If a query applies a function to a column, index the expression the query uses. For case-insensitive email lookup, for example:

CREATE INDEX users_lower_email_idx
    ON users (lower(email));

SELECT *
FROM users
WHERE lower(email) = lower($1);

A plain index on email does not store the lowercase value that the query searches. An expression index stores the result of lower(email), which PostgreSQL can then use for the lookup. The expression adds work to inserts and relevant updates, and PostgreSQL requires functions and operators in index definitions to be immutable: their results must depend only on their arguments. The expression index documentation covers these requirements.

A partial index includes only rows that satisfy a condition. For example, an index on open orders can avoid storing entries for closed orders:

CREATE INDEX orders_open_created_idx
    ON orders (created_at)
    WHERE status = 'open';

The planner uses this index only when it can establish that the query’s conditions imply status = 'open'. It does not use general mathematical reasoning to prove that implication, and parameterized conditions can prevent it from recognizing the relationship. Check the plan for the query as PostgreSQL receives it before relying on a partial index. The partial index documentation explains when the planner can use one.

A covering index includes extra columns a query needs, which can let PostgreSQL return results from the index without fetching table rows. That is an index-only scan. It helps when PostgreSQL can confirm that the relevant table pages are visible to the query without checking each row. Frequently updated pages are less likely to support that shortcut. Extra columns also enlarge the index, so include only data that benefits a measured query. PostgreSQL explains these conditions in its index-only scan documentation.

Check the plan with realistic data

An index that looks suitable on paper may not help a particular query. PostgreSQL weighs the expected number of matching rows and the cost of accessing them, so its planner may choose a sequential scan even when an index exists. After loading representative data, run ANALYZE to update statistics, then inspect the query with EXPLAIN ANALYZE:

ANALYZE events;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM events
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN ANALYZE runs the query and reports actual row counts and execution time alongside the planner’s estimates. BUFFERS reports how PostgreSQL accessed data in memory and from storage. Compare estimated and actual rows, whether PostgreSQL used the index, whether it still sorted, and how much table and index data it accessed. The PostgreSQL index examination guidance recommends testing with realistic data because small or unrepresentative tables can lead to misleading choices.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *