Learn SQL with PostgreSQL - Deep Dive Indexing Strategies

#Introduction

Welcome to episode 14 of the Learn SQL with PostgreSQL series! This is the episode many people have been waiting for — indexing. Almost all database performance problems tagged "the query is slow!" are rooted in the lack of a proper index. Conversely, a wrong index can also backfire: it eats storage, slows down inserts, and never gets used. Understanding indexing is the skill that separates an ordinary developer from one who masters databases.

A database index works like the index at the back of a book: you don't need to read every page to find a single word — just check the index and jump straight to the page. Without an index, the database must read every row of the table (called a Full Table Scan) to find the matches. The bigger the table, the slower it gets.

In this episode, we'll cover why indexing turns a search from O(N) into O(log N), the four main PostgreSQL index types (B-Tree, Hash, GIN, BRIN), then advanced indexing techniques: partial index, expression index, and composite index with the leftmost column rule.

#Why Is Indexing So Critical?

Formally: a Full Table Scan reads N rows — its complexity is O(N). With a B-Tree index, the database searches through a balanced tree with log N depth — complexity O(log N). For a 10-million-row table, the difference between reading 10 million rows vs about 24 tree steps is the difference between minutes and milliseconds.

SELECT * FROM users WHERE email = 'budi@example.com';

Without an index, PostgreSQL scans all rows of users. With CREATE INDEX ON users (email), the query goes straight to the matching leaf node.

But keep in mind: an index is a trade-off. Every index slows down INSERT, UPDATE, and DELETE (because it must be kept in sync) and consumes storage. A proper index is only for columns that are frequently filtered or joined.

#Index Types in PostgreSQL

PostgreSQL provides several index types, each optimized for a different query pattern.

#B-Tree Index: The Default for Everything

B-Tree is the default and most common type. It supports the =, <, >, <=, >=, and BETWEEN operators — and also returns data in sorted order (helping ORDER BY). It's the right choice for almost any scalar column.

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created_at ON orders (created_at);

CREATE INDEX idx_users_email ON users (email) without specifying a type creates a B-Tree. This index will speed up WHERE email = '...' and ORDER BY email.

#Hash Index: Dedicated to Equality

A Hash index is optimized specifically for the = operator. It maps values to hash buckets, making equality lookups very fast. Since PostgreSQL 10, Hash indexes can be replicated and are loggable — safe to use.

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

When to use Hash? Almost all equality cases are already handled very well by B-Tree, so Hash rarely brings significant gains. It usually only wins on columns with very long values that don't need range operations.

#GIN Index: For JSONB, Arrays, and FTS

GIN (Generalized Inverted Index) is an "inverted" index: it maps every element to the rows that contain it. It's the right type for:

• Array columns: finding an element inside an array.
• JSONB columns: the @> and ? operators.
• Full-Text Search: text search (episode 17).

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

The first index speeds up JSONB queries like WHERE attributes @> '{"warna": "hitam"}' (the episode 8 example). The second speeds up tag lookups like WHERE 'sql' = ANY(tags).

#BRIN Index: Storage-Efficient for Ordered Data

BRIN (Block Range Index) stores a summary of value ranges per block of pages, not per row. Its size is very small — tens of thousands of times more compact than a B-Tree on giant tables. It fits time-series tables (logs, events, sensors) where data is ordered by time.

CREATE INDEX idx_events_created_at ON events USING BRIN (created_at);

A table with millions of log rows can be BRIN-indexed in just a few megabytes, while a B-Tree could consume gigabytes. But BRIN is only efficient if the data is physically ordered on disk — on tables with random inserts or frequent updates, performance drops.

#Advanced Indexing Techniques

Now that the basic types are clear, here are techniques that save storage and boost performance.

#Partial Index: Index Only a Data Subset

A partial index indexes only the rows that satisfy a WHERE condition at creation time. The idea: many queries only filter certain values (e.g. the active status), so indexing the entire table is wasteful.

CREATE INDEX idx_users_active_email
ON users (email)
WHERE is_active = TRUE;

This index contains only active user rows. The query WHERE is_active = TRUE AND email = '...' will use it, and it's much smaller than a full index. This technique is popular for tables with a status where most data is "inactive".

#Expression Index: Index on a Function Result

An expression index indexes the result of an expression. The classic example: case-insensitive email lookups. Without an expression index, the query WHERE LOWER(email) = 'budi@x.com' can't use a regular index:

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

Once this index exists, the query WHERE LOWER(email) = 'budi@example.com' becomes an Index Scan. The same pattern applies to WHERE (price * quantity) > 1000 and other functions used in filters.

#Composite Index and the Leftmost Column Rule

A composite index indexes several columns at once, in a specific order:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

This index speeds up queries filtering customer_id alone, customer_id + created_at, and ORDER BY customer_id, created_at. But this raises the leftmost column rule: an index can only be used if the filter uses the leftmost column first. A query filtering only created_at without customer_id will not use the index above.

#Inspecting Existing Indexes

\d users

\d users in psql displays all indexes along with their columns. For a list of all indexes in the database, query pg_indexes:

SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

#Common Mistakes

#Closing

In this episode 14, we've gone deep into indexing: why an index turns a Full Table Scan of O(N) into an Index Scan of O(log N), the four main index types (B-Tree, Hash, GIN, BRIN) with their use cases, and advanced techniques like partial index, expression index, and composite index with the leftmost column rule.

Key takeaways:

• Indexes turn a search from O(N) into O(log N) — the difference between minutes and milliseconds on large tables.
• B-Tree for range/equality, GIN for JSONB/Array/FTS, BRIN for time-series.
• Partial index indexes only a subset — small and fast.
• Expression index indexes a function result like LOWER(email).
• Composite indexes follow the leftmost column rule — order the columns correctly.

In the next episode, episode 15, we learn to prove that indexes work: Query Optimization & EXPLAIN ANALYZE — from reading the execution plan, understanding Sequential Scan vs Index Scan vs Bitmap, join algorithms like Nested Loop and Hash Join, to identifying slow queries with the pg_stat_statements extension.