Databases are fast—until they aren't. As your data grows from thousands of rows to millions, queries that used to take milliseconds suddenly take seconds. The most common solution to this problem is indexing.

In this article, we'll look under the hood of PostgreSQL to understand how different types of indexes work and when you should use them.

Explain Analyze

Before adding indexes, always profile your queries using `EXPLAIN ANALYZE`. This will tell you if Postgres is doing a Sequential Scan (bad for large tables) or an Index Scan (good).

1. B-Tree Indexes

B-Tree (Balanced Tree) is the default index type in PostgreSQL. When you run `CREATE INDEX`, this is what you get.

B-Trees are excellent for exact matches and range queries. If you are querying for `<, <=, =, >=, >`, a B-Tree index is almost certainly what you need.

B-Tree Structure
Figure 1: Traversing a Balanced Tree from root to leaf node.
sql
-- Creates a default B-Tree index CREATE INDEX idx_users_email ON users(email); -- This query will now use the index SELECT * FROM users WHERE email = 'jane@example.com';

2. GIN Indexes (Generalized Inverted Index)

B-Trees are great for scalar data like integers or strings. But what if you are storing composite types like arrays, full-text search documents, or JSONB?

This is where GIN indexes shine. A GIN index maps elements (like a specific word in a text document, or a key in a JSON object) to the rows that contain them.

JSONB and GIN

If you use Postgres as a NoSQL datastore by storing JSONB, you absolutely must use GIN indexes to make querying those JSON documents fast.

Conclusion

Indexes trade write performance for read performance. Every time you insert or update a row, Postgres must also update the index. Therefore, don't just index every column—only index the columns you frequently filter or sort by!