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:
1-- Index on slug for fast lookups
2CREATE INDEX idx_posts_slug ON posts (slug);
3
4-- Composite index for filtered queries
5CREATE INDEX idx_posts_status_published
6 ON posts (status, published_at DESC)
7 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:
1-- Index on technologies array column
2CREATE INDEX idx_projects_technologies
3 ON projects USING GIN (technologies);
4
5-- Full-text search index
6CREATE INDEX idx_posts_search
7 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:
1-- Efficient for time-series queries
2CREATE INDEX idx_page_views_timestamp
3 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:
1-- BEFORE: Sequential scan on 10,000 rows
2SELECT p.*, c.name as category_name
3FROM posts p
4LEFT JOIN categories c ON p.category_id = c.id
5WHERE p.status = 'published'
6ORDER BY p.published_at DESC
7LIMIT 10 OFFSET 20;Adding a composite partial index:
1CREATE INDEX idx_posts_published_date
2 ON posts (published_at DESC)
3 WHERE status = 'published';Result: Query time dropped from 45ms to 2ms.
Use EXPLAIN ANALYZE
Always measure before and after:
1EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
2SELECT * FROM posts
3WHERE status = 'published'
4ORDER BY published_at DESC
5LIMIT 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
1-- Find unused indexes
2SELECT schemaname, relname, indexrelname, idx_scan
3FROM pg_stat_user_indexes
4WHERE idx_scan = 0
5ORDER 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.