I ignored database indexing for way too long. My queries worked fine in development with 100 rows. Then production hit a few thousand rows and suddenly everything was slow. A page that loaded in 200ms now took 3 seconds. The fix was almost always the same: add an index.

But I also learned that indexes aren’t free. Add too many and your writes slow down. Add the wrong ones and they sit there taking up space doing nothing. After dealing with this enough times, I finally took the time to actually understand how indexes work in PostgreSQL.

Here’s what I wish I knew earlier.

What an index actually does

Without an index, PostgreSQL scans every row in the table to find what you’re looking for. This is called a sequential scan. If your table has 100,000 rows and you’re looking for one user by email, PostgreSQL checks all 100,000 rows.

An index is like a sorted lookup table. It stores the values of specific columns in a structure (usually a B-tree) that allows PostgreSQL to find the matching rows without scanning everything.

Think of it like a book index. Instead of reading every page to find “PostgreSQL,” you go to the index at the back, find the page number, and jump directly there.

Creating a basic index

CREATE INDEX idx_users_email ON users (email);

Now when you query:

SELECT * FROM users WHERE email = 'hello@baransel.dev';

PostgreSQL uses the index instead of scanning the entire table. On a table with a million rows, this turns a multi-second query into a few milliseconds.

Seeing the difference with EXPLAIN ANALYZE

This is the tool that changed how I think about queries. EXPLAIN ANALYZE shows you exactly how PostgreSQL executes a query.

Without an index:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'hello@baransel.dev';

Seq Scan on users  (cost=0.00..1234.00 rows=1 width=128) (actual time=15.432..45.210 rows=1 loops=1)
  Filter: (email = 'hello@baransel.dev')
  Rows Removed by Filter: 99999
Planning Time: 0.102 ms
Execution Time: 45.312 ms

Sequential scan. It checked all 100,000 rows and removed 99,999 of them. 45ms.

With an index:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'hello@baransel.dev';

Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=128) (actual time=0.028..0.030 rows=1 loops=1)
  Index Cond: (email = 'hello@baransel.dev')
Planning Time: 0.095 ms
Execution Time: 0.052 ms

Index scan. Went directly to the right row. 0.05ms. That’s almost 1000x faster.

I run EXPLAIN ANALYZE on any query that feels slow. It always tells me exactly what’s going on.

Composite indexes

When your queries filter on multiple columns, a single-column index might not be enough. That’s where composite indexes come in.

CREATE INDEX idx_posts_status_created ON posts (status, created_at);

This index is useful for queries like:

SELECT * FROM posts WHERE status = 'published' ORDER BY created_at DESC;

PostgreSQL uses the composite index to both filter by status AND sort by date. Without it, it would filter first, then sort the results separately. On large tables, that sorting step can be expensive.

Column order matters. The index above works for queries that filter by status, or status AND created_at. But it won’t help with queries that only filter by created_at. Think of it like a phone book sorted by last name, then first name. You can look up “Arslan,” but you can’t efficiently look up everyone named “Baransel” across all last names.

Partial indexes

Sometimes you only need to index a subset of rows. For example, if you have a posts table and 90% of your queries filter for published posts:

CREATE INDEX idx_posts_published ON posts (created_at)
  WHERE status = 'published';

This index is smaller and faster because it only includes published posts. Queries for draft or archived posts won’t use it, but that’s fine if you rarely query those.

I use partial indexes a lot for things like:

  • Active users (WHERE deleted_at IS NULL)
  • Recent records (WHERE created_at > '2025-01-01')
  • Specific types (WHERE type = 'premium')

Unique indexes

A unique index does two things: it speeds up queries AND enforces uniqueness.

CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

Now PostgreSQL will reject any insert that tries to use a duplicate email. You get a constraint and a performance boost in one.

In Prisma, when you add @unique to a field, it creates a unique index under the hood. So if you’re using Prisma, you already have these without thinking about it.

When indexes hurt

Indexes aren’t always good. Every index:

  • Takes up disk space. A table with 10 indexes stores that data 11 times (the table + each index).
  • Slows down writes. Every INSERT, UPDATE, and DELETE needs to update all relevant indexes. On a write-heavy table with many indexes, this overhead adds up.
  • Can confuse the query planner. Too many indexes and PostgreSQL might pick a suboptimal one.

I’ve seen tables with 15+ indexes where most weren’t being used. Check which indexes are actually used:

SELECT
  indexrelname AS index_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

If an index has 0 scans and takes up space, consider dropping it.

Index types beyond B-tree

B-tree is the default and covers most cases. But PostgreSQL has others:

GIN indexes — great for full-text search and JSONB columns:

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

If tags is a JSONB array, this lets you efficiently query WHERE tags @> '["javascript"]'.

GiST indexes — useful for geometric and range queries:

CREATE INDEX idx_events_dates ON events USING GIST (date_range);

For most web apps, you’ll stick with B-tree. But knowing GIN exists saves you when you need to query JSONB data efficiently.

My rules for indexing

After enough trial and error, here’s what I follow:

  1. Index every column you filter by in a WHERE clause (if the table has more than a few thousand rows)
  2. Index foreign keys. If you have a posts.author_id column, index it. JOINs on unindexed foreign keys are brutal.
  3. Use composite indexes for queries that filter + sort on different columns
  4. Don’t index columns with low cardinality (like a boolean is_active column with only true/false). The index won’t help much because half the table matches either value.
  5. Review unused indexes periodically. They cost write performance for no benefit.
  6. Always check with EXPLAIN ANALYZE. Don’t guess. Let PostgreSQL tell you what it’s doing.

Start with the slow query

Don’t try to predict which indexes you’ll need upfront. Build your app, run it with real data, find the slow queries, and add indexes to fix them. That’s the approach that works.

EXPLAIN ANALYZE is your best friend. Use it often, trust what it tells you, and your database will stay fast even as your data grows.