PostgreSQL Performance: Indexing Strategies

Proper indexing is the single most impactful optimization you can make to a PostgreSQL database. In this post, I will share indexing strategies I learned while building the backend for my portfolio site with Supabase.

Understanding Index Types

B-Tree Index (Default)

The most common index type. Perfect for equality and range queries:

sql

-- Index on slug for fast lookups
CREATE INDEX idx_posts_slug ON posts (slug);

-- Composite index for filtered queries
CREATE INDEX idx_posts_status_published
  ON posts (status, published_at DESC)
  WHERE status = 'published';

The partial index above is especially powerful — it only indexes published posts, making it smaller and faster.

GIN Index

Best for array columns and full-text search:

sql

-- Index on technologies array column
CREATE INDEX idx_projects_technologies
  ON projects USING GIN (technologies);

-- Full-text search index
CREATE INDEX idx_posts_search
  ON posts USING GIN (to_tsvector('english', title || ' ' || content));

With a GIN index on the technologies array, queries like WHERE 'Swift' = ANY(technologies) become significantly faster.

BRIN Index

Best for naturally ordered data like timestamps:

sql

-- Efficient for time-series queries
CREATE INDEX idx_page_views_timestamp
  ON page_views USING BRIN (viewed_at);

BRIN indexes are tiny compared to B-Tree and perfect for append-only tables like analytics data.

When to Create an Index

Follow these rules:

Scenario	Action
Foreign key columns	Always index
Columns in WHERE clauses	Index if queried frequently
Columns in JOIN conditions	Index both sides
Columns in ORDER BY	Index for sorted pagination
Low-cardinality columns	Use partial index instead

Real-World Example: Blog Query Optimization

My blog listing query was slow:

sql

-- BEFORE: Sequential scan on 10,000 rows
SELECT p.*, c.name as category_name
FROM posts p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 10 OFFSET 20;

Adding a composite partial index:

sql

CREATE INDEX idx_posts_published_date
  ON posts (published_at DESC)
  WHERE status = 'published';

Result: Query time dropped from 45ms to 2ms.

Use EXPLAIN ANALYZE

Always measure before and after:

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 10;

Key things to look for:

Seq Scan → needs an index
Index Scan → good
Index Only Scan → best (covers all needed columns)
Bitmap Index Scan → good for OR conditions

Supabase-Specific Tips

Dashboard → SQL Editor: Run EXPLAIN ANALYZE directly
pg_stat_user_indexes: Check which indexes are actually used
Unused indexes waste space: Drop them

sql

-- Find unused indexes
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Key Takeaways

Always index foreign keys — PostgreSQL does not do this automatically
Use partial indexes for filtered queries (e.g., only published posts)
Measure with EXPLAIN ANALYZE — do not guess
GIN for arrays — essential for array column queries in Supabase
Remove unused indexes — they slow down writes

Proper indexing can turn a slow application into a fast one without changing a single line of application code.

PostgreSQL Performance: Indexing Strategies

PostgreSQL Performance: Indexing Strategies

Understanding Index Types

B-Tree Index (Default)

GIN Index

BRIN Index

When to Create an Index

Real-World Example: Blog Query Optimization

Use EXPLAIN ANALYZE

Supabase-Specific Tips

Key Takeaways

Related Articles

Deploying a Next.js Portfolio Site on Vercel

Why I Chose Cloudflare Workers for My Portfolio API