PostgreSQL Indexing Explained for Developers

Your app works fine with 100 rows. Then you hit 1 million rows and suddenly a page takes 4 seconds to load. Nine times out of ten the fix is an index.

This guide explains how PostgreSQL indexing works, how to find queries that need one, and the common mistakes to avoid.

What Is an Index?

An index is a separate data structure — usually a B-tree — that PostgreSQL maintains alongside your table. It stores a sorted copy of one or more columns, with pointers back to the full row.

Without an index, a query like:

SELECT * FROM users WHERE email = 'alice@example.com';

performs a sequential scan — it reads every row in the table until it finds a match. With an index on email, it jumps straight to the matching rows in O(log n) time.

The trade-off: indexes speed up reads but slow down writes (inserts, updates, deletes must also update the index) and consume disk space.

Creating a Basic Index

-- Single-column index
CREATE INDEX idx_users_email ON users (email);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- Composite index (column order matters — see below)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

-- Concurrent index build (doesn't lock the table — use in production)
CREATE INDEX CONCURRENTLY idx_products_category ON products (category_id);

EXPLAIN ANALYZE: Finding Slow Queries

Before adding any index, use EXPLAIN ANALYZE to see what PostgreSQL is actually doing:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND created_at > '2026-01-01';

Look for these keywords in the output:

Keyword	Meaning
Seq Scan	Reading the whole table — usually bad on large tables
Index Scan	Using an index — good
Index Only Scan	Reading only the index, not the table — fastest
Bitmap Heap Scan	Using index + fetching rows in bulk — often fine

High rows= estimates with large actual time= values are where you focus.

When to Index

Good candidates:

• Columns used in WHERE clauses frequently
• Columns used in JOIN conditions (ON orders.user_id = users.id)
• Columns used in ORDER BY when combined with a WHERE
• Foreign key columns (PostgreSQL does not auto-index these)

Don’t bother indexing:

• Small tables (< ~1000 rows) — sequential scan is often faster
• Columns with very low cardinality (e.g. a boolean is_active on a 50/50 split)
• Columns that are rarely queried

Composite Index Column Order

The order of columns in a composite index matters. PostgreSQL can use the index for queries that filter on:

• The leftmost column alone
• The leftmost two columns
• All columns

But not for queries that skip the first column. Given CREATE INDEX ON orders (user_id, created_at):

-- Uses the index ✓
SELECT * FROM orders WHERE user_id = 42;
SELECT * FROM orders WHERE user_id = 42 AND created_at > '2026-01-01';

-- Does NOT use the index ✗
SELECT * FROM orders WHERE created_at > '2026-01-01';

Put the most selective column (the one that filters the most rows) first.

Partial Indexes

Index only the rows you actually query — this keeps the index small and fast:

-- Only index active users
CREATE INDEX idx_users_active ON users (email) WHERE is_active = true;

-- Only index unpaid invoices
CREATE INDEX idx_invoices_unpaid ON invoices (due_date) WHERE paid_at IS NULL;

Finding Unused Indexes

Indexes you never use are pure overhead. Check usage stats:

SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Indexes with idx_scan = 0 since the last stats reset are candidates for removal. Drop carefully — verify in staging first.

Monitoring Index Bloat

Indexes fragment over time. Rebuild them periodically:

REINDEX INDEX CONCURRENTLY idx_users_email;

Or run VACUUM ANALYZE regularly (Postgres autovacuum handles this automatically in most setups).

Key Takeaway

Don’t index everything upfront. Write your queries first, profile with EXPLAIN ANALYZE, then add targeted indexes where you see sequential scans on large tables. Read the PostgreSQL indexing docs for coverage of GIN, GiST, and BRIN index types for more specialised use cases.

Good indexing is one of the highest-leverage skills a backend developer can have.