PostgreSQL indexes: when, what, and why

Effective indexing is critical for the performance of PostgreSQL databases, transforming slow queries into near-instantaneous operations. This article…

Effective indexing is critical for the performance of PostgreSQL databases, transforming slow queries into near-instantaneous operations. This article delves into the various types of indexes available in PostgreSQL, offering guidance on their appropriate use cases, construction, and validation methods to optimize query execution plans.

Understanding PostgreSQL Index Types

PostgreSQL provides several index types, each optimized for different data structures and query patterns. The choice of index significantly impacts search efficiency, storage overhead, and write performance.

B-tree Indexes (Default)

The B-tree (Balanced Tree) is PostgreSQL's default and most commonly used index type. It's suitable for a wide range of workloads and supports equality, range, and ordering operations (e.g., =, <, >, <=, >=, BETWEEN, IN, ORDER BY, MAX(), MIN()). B-trees are efficient for high cardinality columns where a significant portion of the table rows are not scanned.

Use Cases: Primary keys, foreign keys, columns frequently used in WHERE clauses, ORDER BY clauses, or join conditions.
Structure: Data is sorted and stored in pages, forming a balanced tree structure. Each leaf node points to the actual table row (TID - Tuple ID).
Creation Example:

CREATE INDEX ix_users_email ON users (email);
CREATE UNIQUE INDEX ux_products_sku ON products (sku); -- Ensures uniqueness

For multicolumn indexes, the order of columns matters. A multicolumn B-tree index on (col1, col2, col3) can be used efficiently for queries filtering on col1, (col1, col2), or (col1, col2, col3). It cannot effectively use the index to filter only on col2 or col3 without col1.

CREATE INDEX ix_orders_customer_date ON orders (customer_id, order_date);

Hash Indexes (Rarely Recommended)

Hash indexes are suitable only for equality comparisons. In versions prior to PostgreSQL 10, they were not crash-safe and not replicated to standby servers, making them generally unsuitable for production. While improvements have been made in PostgreSQL 10+, B-tree indexes often still perform better or equivalently for equality checks and offer broader query support.

Use Cases: Very specific scenarios where only equality checks are needed on columns with very high cardinality, and B-trees prove to be slower due to their overhead for range scans. However, B-trees generally outperform them for most real-world applications.
Creation Example:

CREATE INDEX ix_users_login_hash ON users USING HASH (login); -- Use with caution

GIN Indexes (Generalized Inverted Index)

GIN indexes are optimized for indexing composite values where the queries search for elements within the composite. They are particularly effective for jsonb documents, arrays, and full-text search.

Use Cases:
- jsonb columns: Efficiently query keys or values within JSON documents using operators like ?, ?|, ?&, @>.
- Arrays: Search for elements within array columns using operators like @> (contains) or && (overlaps).
- Full-text search: Accelerate queries using tsvector and tsquery types for natural language search.
Structure: A GIN index stores a list of locations (TIDs) for each unique "item" (e.g., a word in a document, a key in JSON).
Creation Example:

-- For jsonb columns
CREATE INDEX ix_products_attributes_gin ON products USING GIN (attributes jsonb_path_ops);
-- For full-text search
CREATE INDEX ix_documents_content_fts ON documents USING GIN (to_tsvector('english', content));

The jsonb_path_ops operator class, introduced in PostgreSQL 9.5, is more compact and faster than the default jsonb_ops for queries involving containment (@>) and existence (?, ?|, ?&), but does not support array/object key access via -> or ->> in the index. Choose based on your primary query patterns.

BRIN Indexes (Block Range Index)

BRIN indexes are designed for very large tables where data is naturally ordered on disk. They store minimal information: for each block range in the table, they record the minimum and maximum values of the indexed column within that range. This makes them extremely small and fast to update.

Use Cases: Tables with billions of rows where data is ordered by insertion time (e.g., log tables, time-series data) and queries typically filter on recent data. Effective for columns like created_at or id in append-only tables.
Caveats: Not effective if data is randomly distributed across disk blocks. The default pages_per_range is 128, meaning each BRIN entry covers 128 data pages. Adjust this value based on your data distribution using CREATE INDEX ... WITH (pages_per_range = N).
Creation Example:

CREATE INDEX ix_events_timestamp_brin ON events USING BRIN (event_timestamp);
CREATE INDEX ix_logs_id_brin ON logs USING BRIN (id) WITH (pages_per_range = 1024);

Partial Indexes

Partial indexes index only a subset of rows in a table, specified by a WHERE clause. This significantly reduces the index size and maintenance overhead, making them faster to update and more efficient for queries targeting that specific subset.

Use Cases:
- Tables with many inactive or "soft-deleted" records: Index only is_active = TRUE or deleted_at IS NULL.
- Columns with highly skewed data distribution: Index only the "hot" values.
- Unique constraints on a subset of rows (e.g., unique email for active users).
Creation Example:

-- Index only active users for faster login lookups
CREATE UNIQUE INDEX ux_active_users_email ON users (email) WHERE is_active = TRUE;

-- Index orders that are still pending
CREATE INDEX ix_pending_orders_created ON orders (created_at) WHERE status = 'pending';

Expression Indexes

Expression indexes allow indexing the result of a function or expression on one or more columns. This is useful when queries frequently filter or order by derived values.

Use Cases:
- Case-insensitive searches: LOWER(column) or UPPER(column).
- Extracting parts of dates: DATE(timestamp_column).
- Complex calculated values used in queries.
Creation Example:

-- For case-insensitive email searches
CREATE INDEX ix_users_email_lower ON users (LOWER(email));

-- Index on the date part of a timestamp
CREATE INDEX ix_sales_day ON sales (DATE(sale_timestamp));

Remember that the query must exactly match the indexed expression for the index to be used (e.g., WHERE LOWER(email) = 'test@example.com').

When to Create Indexes

Indexes are not a free lunch; they add overhead to writes (INSERT, UPDATE, DELETE) and consume disk space. Create indexes strategically:

Foreign Keys: Almost always index foreign key columns to speed up joins and referential integrity checks.
Primary Keys: Automatically indexed by PostgreSQL as unique B-tree indexes.
WHERE Clauses: Columns frequently used in WHERE conditions.
JOIN Conditions: Columns used to link tables.
ORDER BY/GROUP BY: Columns used for sorting or grouping, especially if the result set is large.
High Cardinality Columns: Indexes are most effective on columns with many distinct values. Low cardinality columns (e.g., boolean flags) rarely benefit from indexes, as the planner might prefer a sequential scan.
Large Tables: Indexing offers greater performance gains on tables with a significant number of rows.

Validating Index Effectiveness with EXPLAIN

The EXPLAIN command is indispensable for understanding how PostgreSQL executes a query and whether indexes are being utilized. Append ANALYZE and BUFFERS for detailed runtime statistics and I/O information.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM users WHERE email = 'jane.doe@example.com';

Look for operations like Index Scan, Index Only Scan, or Bitmap Index Scan. If you see Seq Scan on a large table for a query that you expect to be indexed, it indicates a problem. An Index Only Scan is the most efficient, as it retrieves all necessary data directly from the index without having to visit the table heap. This is possible if all columns requested by the query are present in the index itself (including any WHERE clause conditions).

Choosing the Right Index Operator Class

Every index type has a default operator class, but for certain data types and operations, alternative operator classes can be specified to optimize index usage. Examples include text_pattern_ops for B-tree indexes on text columns to optimize LIKE 'prefix%' queries, or jsonb_path_ops for GIN indexes on jsonb.

CREATE INDEX ix_products_name_prefix ON products (name text_pattern_ops);

This allows the index to be used for queries like WHERE name LIKE 'Super%', which the default B-tree index on name would not optimize beyond WHERE name = 'Super Product'.

Common Pitfalls

Over-indexing: Too many indexes slow down write operations (INSERT, UPDATE, DELETE) and consume excessive disk space. Each write requires updating all relevant indexes.
Indexing low-cardinality columns: Indexes on columns with few distinct values (e.g., boolean flags, status columns with only a few states) are often ignored by the query planner because a sequential scan is deemed more efficient than reading the index and then the table. Consider partial indexes for such cases.
Incorrect column order in multicolumn indexes: The order matters. If you frequently query (A, B) and sometimes (A), create (A, B). If you query (B) frequently alone, a separate index on B might be necessary.
Indexing without validation: Always use EXPLAIN (ANALYZE, BUFFERS) to confirm that your indexes are being used and are actually improving query performance. An index that is never used is wasted overhead.
Bloated Indexes: Frequent updates and deletions can lead to index bloat, increasing their size and slowing down scans. Regular VACUUM FULL or REINDEX operations may be necessary, but consider tuning autovacuum or using appropriate fillfactors.