Skip to content
DBAExperts
PostgreSQL Performance

How to create an index in PostgreSQL: B-tree and partial indexes

A well-chosen index turns a sequential scan into a direct lookup. The B-tree covers most cases; a partial index reduces size when you only query a subset of rows.

  1. 1

    Create a standard B-tree index

    This is the default index and works for equality and ranges (=, <, >, BETWEEN, ORDER BY). Put it on the column that appears in the WHERE clause.

    CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);
  2. 2

    Create the index without blocking writes

    CREATE INDEX blocks writes on the table. In production, use CONCURRENTLY to avoid that; it takes longer but does not stop the application.

    CREATE INDEX CONCURRENTLY idx_pedidos_cliente ON pedidos (cliente_id);
  3. 3

    Use a partial index for frequent subsets

    If you almost always query rows with a fixed condition (for example, active orders), a partial index takes up less space and is maintained faster.

    CREATE INDEX idx_pedidos_activos ON pedidos (cliente_id) WHERE estado = 'activo';
  4. 4

    Confirm that the query uses the index

    Check the plan with EXPLAIN. If you see Index Scan instead of Seq Scan, the index is working for that query.

    EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 42;

// common mistake

Creating too many indexes has a cost too: each index slows down INSERTs/UPDATEs and takes up disk. An index on a column with low selectivity (few distinct values) often is not even used.

// when it's worth an expert

When the database is large and queries are already affecting users, choosing the wrong index makes writes worse without fixing reads. At dba.mx we analyze the real query plans and tune the index set based on the actual workload, not by guesswork, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.