Index in PostgreSQL: speeding up without slowing down

An index changes that. Instead of reading everything, PostgreSQL uses the index to jump directly to the relevant rows. The database searches the index structure — usually a B-tree — in a few steps instead of scanning the entire table.

But an index is not free acceleration. Every time you insert, update, or delete data, PostgreSQL must also update the index. The more indexes you create, the more write overhead you introduce. A table with ten indexes and heavy `INSERT` and `UPDATE` traffic can become slower than without indexes. The official documentation warns about this explicitly.

The art is not to index everything, but to index what your queries actually filter on. An index on a column that never appears in a `WHERE` clause only costs space and write time.

Index types in PostgreSQL

PostgreSQL supports multiple index types, each with its own internal structure and use case. The choice determines which operators the index can speed up and how much space it takes up.

B-tree — the default choice

The B-tree (Balanced Tree) is the most commonly used type and the default when you do not specify a type. It supports comparisons with `<`, `<=`, `=`, `>=`, and `>`. That makes it suitable for numeric and textual data on which you run range or equality queries.

CREATE INDEX idx_employees_name ON employees (name);

This query creates a B-tree index on the `name` column of the `employees` table. PostgreSQL uses this index for queries such as `WHERE name = 'Jansen'` or `WHERE name > 'K'`.

Hash — fast but limited

The Hash index supports only equality comparisons (`=`). For exact matches it is faster than a B-tree, but it cannot handle range queries. Moreover, the Hash index was not crash-safe in older versions; that has been improved since PostgreSQL 10, but the restriction to `=` operations remains.

CREATE INDEX idx_users_email_hash ON users USING hash (email);

GiST and SP-GiST — for complex data

GiST (Generalized Search Tree) provides a flexible infrastructure for indexing non-traditional data such as geometric objects, text, or documents. SP-GiST (Space-Partitioned GiST) is a variant for data that can be subdivided into non-balancing spaces, such as prefix trees or radix trees.

These types are relevant when working with PostGIS or other extensions that enable spatial queries. For ordinary relational data, you rarely need them.

GIN — for collections and full-text

GIN (Generalized Inverted Index) is used for arrays, JSON, and full-text searches. It does not index the value itself, but the elements within a collection. A query that searches for a specific element in a JSON field or a word in a document can therefore use the index.

CREATE INDEX idx_documents_content ON documents USING gin (content);

BRIN — compact for large tables

BRIN (Block Range INdex) is intended for very large tables where data is stored in a logical order. Instead of indexing per row, BRIN indexes per block of multiple rows. That takes up much less space than a B-tree and is efficient for range queries on large datasets that are physically sorted.

CREATE INDEX idx_orders_created_at_brin ON orders USING brin (created_at);

This index works well when the `created_at` values in the table are roughly in ascending order. If the data is mixed up, BRIN has to read too many blocks and you lose the benefit.

Bloom — via extension

Via the `bloom` extension, you can create a Bloom filter index. It quickly checks whether a value might be in a set — with a small chance of false positives. After a positive check, PostgreSQL still has to verify the actual rows, but it can quickly rule out many rows.

CREATE EXTENSION bloom;
CREATE INDEX idx_orders_bloom ON orders USING bloom (customer_id, order_date, status);

This type is useful for queries that filter on multiple columns at once, where a single B-tree index does not help.

Creating an index

Creating one is done with `CREATE INDEX`. The choice of which columns you index, which type you use, and whether the index should be unique requires consideration.

For composite queries, you create a composite index over multiple columns:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

This index helps with queries that filter on both fields, such as `WHERE customer_id = 42 AND order_date > '2026-01-01'`. Note: the order of the columns in the index must match the order in which the query filters. An index on `(customer_id, order_date)` does not help with a query that only filters on `order_date`.

Do you want a function-based index because your query applies a function to the column:

CREATE INDEX idx_employees_upper_name ON employees (UPPER(name));

Without this index, PostgreSQL cannot use a regular index on `name` when your query contains `WHERE UPPER(name) = 'JAN'`. The database would then have to scan the entire table anyway.

Dropping an index

You drop an index with `DROP INDEX`. This is useful when an index is no longer needed, performs poorly, or causes too much write overhead. Dropping it has no effect on the data itself — only on the speed of certain queries.

DROP INDEX idx_employees_name;

This operation is permanent. If you want to use the index again later, you will need to recreate it.

Index maintenance

Indexes can fragment over time or even become corrupted. Two utilities can help with this.

`reindexdb` is a command-line tool that rebuilds indexes. This may be necessary if an index has become corrupted or if performance has degraded due to frequent changes to the underlying table.

reindexdb -d mijn_database -t employees

This command rebuilds all indexes on the table `employees` in the database `mijn_database`. Within the database itself, you can do the same with the SQL command `REINDEX`:

REINDEX TABLE employees;

`pg_amcheck` detects corruption in indexes and other database objects, particularly in B-tree indexes. Run this tool before executing a `REINDEX` — then you will know whether rebuilding is actually necessary:

pg_amcheck -d mijn_database

Verification

Check whether an index is actually being used with `EXPLAIN`:

EXPLAIN SELECT * FROM employees WHERE name = 'Jansen';

In the output, you will see whether PostgreSQL uses an `Index Scan` or a `Seq Scan`. A `Seq Scan` means the index is not being used — then check whether the query shape matches the index structure.

To see which indexes exist on a table:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';

Common problems

PostgreSQL is not using my index. The query shape must match the index. A query with `WHERE UPPER(name) = 'JAN'` does not use a regular index on `name` — for that you need a function-based index. A query that filters on a column that is not the first column of a composite index also cannot use the index.

My write performance has degraded after adding indexes. Every index must be updated on `INSERT`, `UPDATE`, and `DELETE`. Remove indexes that do not speed up any queries, or consider a BRIN index if you are working with very large tables and write performance matters more than read speed.

An index is corrupted. Run `pg_amcheck` to confirm the corruption, and then use `REINDEX` or `reindexdb` to rebuild the index. Afterward, check whether the corruption returns — if so, that points to a problem with the hardware or the storage system.

Managing indexes declaratively with SQLAlchemy

Within the ecosystem of ORM tools, SQLAlchemy 2.0 takes PostgreSQL-specific index functionality into account. Through the Schema Definition Language, you can define indexes declaratively in your application code, without writing SQL directly:

from sqlalchemy import Column, Integer, String, Index
from sqlalchemy.orm import declarative_base</p><p>Base = declarative_base()</p><p>class Employee(Base):
    __tablename__ = 'employees'
    __table_args__ = (
        Index('idx_employees_name', 'name'),
    )</p><p>id = Column(Integer, primary_key=True)
    name = Column(String)

SQLAlchemy translates this definition into the appropriate DDL (Data Definition Language) when you create the tables. This is useful when you want to keep indexes under version control along with your schema. It does cost an abstraction layer: you need to know SQLAlchemy's way of working to configure advanced options such as `USING gin` or function-based indexes.

The right index is not a matter of creating it and forgetting about it. Query patterns, data order, and write volume determine whether an index is still worth it after a year — and only `EXPLAIN` tells you that honestly.