PostgreSQL Indexes

PostgreSQL Indexes

Indexes are the backbone of PostgreSQL performance they allow blazing-fast queries, efficient sorting, and optimized joins. But here’s the catch: if misused or neglected, they can bloat your database, degrade write performance, and waste precious disk space.

In this guide, we’ll cover:

• All index types in PostgreSQL
• When and how to use them
• How to assign indexes to tablespaces
• Using CONCURRENTLY to avoid downtime
• Detecting unused and duplicate indexes
• Real-world examples and performance tips

Index Types in PostgreSQL

Let’s quickly review all index types — with usage examples and best practices.

1. B-tree Index (Default and Most Common)

• Use for: Exact match (=), range (<, <=, >, >=), ORDER BY
• Syntax:

CREATE INDEX idx_name ON my_table(my_column);

• Example:

CREATE INDEX idx_users_email ON users(email);

• Best for: Primary keys, unique constraints, most query filters.

2. Hash Index

• Use for: Only equality comparisons.
• Historically less used due to WAL issues before PostgreSQL 10.
• Example:

CREATE INDEX idx_hash_username ON users USING HASH(username);

3. GIN (Generalized Inverted Index)

• Use for: Full-text search, JSONB, arrays.
• Example:

CREATE INDEX idx_gin_tags ON articles USING GIN(tags);

CREATE INDEX idx_gin_text ON documents USING GIN(to_tsvector('english', content));

• Operators supported: @>, <@, ?, full-text @@.

4. GiST (Generalized Search Tree)

• Use for: Geospatial (PostGIS), ranges, custom types.

Example:

CREATE INDEX idx_gist_location ON locations USING GIST(geom);

• Great for nearest neighbor, overlapping intervals, etc.

5. BRIN (Block Range Index)

• Use for: Huge tables with naturally ordered data (e.g. time-series)
• Extremely space-efficient but less precise.

Example:

CREATE INDEX idx_brin_logs ON logs USING BRIN(created_at);

6. SP-GiST

• Use for: Data with hierarchical or prefix structure (IP ranges, strings)

Example:

CREATE INDEX idx_spgist_ip ON devices USING SPGIST(ip_address);

Indexes and Tablespaces

Tablespaces let you place your indexes on different storage volumes — ideal for isolating high I/O workloads or using fast SSDs.

Example:

CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd1/pgsql_ts';
CREATE INDEX idx_fast_email ON users(email) TABLESPACE fast_ssd;

You can store heavy indexes on high-performance disks while keeping table data elsewhere.

Index Maintenance — Reindexing & Vacuum

Why it matters:

• Indexes can get bloated due to frequent updates/deletes.
• Can lead to poor planner estimates and slower performance.

1. REINDEX (Classic)

REINDEX INDEX idx_users_email;
REINDEX TABLE users;

Classic REINDEX locks the object and blocks reads/writes.

2. REINDEX CONCURRENTLY

• PostgreSQL 12+ allows reindexing without locking!

Example:

REINDEX INDEX CONCURRENTLY idx_users_email;

This is essential for production environments.

3. CREATE INDEX CONCURRENTLY

• Allows index creation without blocking reads/writes.
• Slightly slower due to background build and catalog safety.

Example:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

Detecting Unused Indexes

PostgreSQL tracks index usage in pg_stat_user_indexes.

SELECT
  relname AS table,
  indexrelname AS index,
  idx_scan,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS size
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND NOT indisunique
ORDER BY pg_relation_size(i.indexrelid) DESC;

Run this after representative production traffic. Indexes with idx_scan = 0 are strong deletion candidates.

Detecting Duplicate or Overlapping Indexes

Duplicate indexes waste disk space and slow down writes.

Query:

SELECT 
    t.relname AS table,
    i.relname AS index,
    pg_size_pretty(pg_relation_size(i.oid)) AS size,
    pg_get_indexdef(i.oid) AS definition
FROM pg_class t
JOIN pg_index ix ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
WHERE t.relkind = 'r'
AND EXISTS (
  SELECT 1
  FROM pg_class i2
  JOIN pg_index ix2 ON i2.oid = ix2.indexrelid
  WHERE i2.oid <> i.oid
  AND ix2.indrelid = ix.indrelid
  AND ix.indkey = ix2.indkey
)
ORDER BY t.relname;

Tips & Best Practices

Use CONCURRENTLY whenever working on production and monitor pg_stat_user_indexes regularly.

Use partial indexes for sparse data:

CREATE INDEX idx_active_login ON users(last_login) WHERE is_active = true;

Drop or merge duplicate indexes and use pgstattuple or pg_repack for analyzing bloat

Conclusion

PostgreSQL indexing is a superpower — if used wisely. Use the right index type, assign heavy ones to the right tablespaces, maintain them regularly with REINDEX CONCURRENTLY, and monitor their usage to keep your database lean and fast.