Web DevelopmentSoftware Engineering

PostgreSQL Performance: Indexing Strategies That Actually Work

Move beyond basic B-tree indexes. Learn when to use partial indexes, covering indexes, and GIN indexes for real performance gains.

Mbeah Essilfie

July 13, 2026 at 06:02 AM

10 min read 2113 views

PostgreSQL Performance: Indexing Strategies That Actually Work

Indexes Are Not Free

Every index speeds up reads but slows down writes. The goal isn't "index everything" — it's indexing strategically based on your query patterns.

Analyzing Your Queries

-- Always start with EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM posts
WHERE status = 'published' AND published_at > NOW() - INTERVAL '30 days'
ORDER BY published_at DESC
LIMIT 20;

Look for Seq Scan — that's a full table scan you probably want to eliminate.

Index Types and When to Use Them

B-Tree (Default)

Perfect for equality and range queries:

CREATE INDEX idx_posts_published_at ON posts (published_at DESC);
CREATE INDEX idx_posts_status ON posts (status);

Composite Indexes

Order matters — put the most selective column first:

-- Matches: WHERE status = 'published' AND published_at > ...
-- Also matches: WHERE status = 'published'
-- Does NOT efficiently match: WHERE published_at > ...
CREATE INDEX idx_posts_status_date ON posts (status, published_at DESC);

Partial Indexes

Index only the rows you actually query:

-- Only index published posts (maybe 30% of all posts)
CREATE INDEX idx_published_posts ON posts (published_at DESC)
WHERE status = 'published';

Partial indexes are smaller, faster to scan, and faster to maintain. Use them when your queries always filter on a specific condition.

GIN Indexes for Full-Text Search

-- Add a tsvector column
ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- Create a GIN index
CREATE INDEX idx_posts_search ON posts USING GIN (search_vector);

-- Update it with a trigger
CREATE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

With Prisma

model Post {
  id          String   @id @default(uuid())
  status      String
  publishedAt DateTime?

  @@index([status, publishedAt(sort: Desc)])
}

Common Mistakes

Too many single-column indexes — Use composites instead
Indexing low-cardinality columns alone — A boolean column index rarely helps
Forgetting INCLUDE columns — Covering indexes avoid table lookups
Never running ANALYZE — Statistics must be fresh for the planner

Monitor with pg_stat_user_indexes to find unused indexes consuming write overhead.