Database Indexing for Web Developers

Your API returns 500ms for a table with ten rows. You launch, hit a million rows, and the same query now takes three seconds. No code changed. The difference is the database reading every row (a sequential scan) instead of finding matching rows directly. That “find it directly” mechanism is the index, and in this guide we will build one of everything PostgreSQL offers - from basic B-tree to trigram-powered search - and prove each one with EXPLAIN ANALYZE.

Objective: Diagnose a slow query, pick the right index type, prove the fix. Key tech: PostgreSQL 15+, B-tree, composite/partial/expression indexes, GIN + pg_trgm, and EXPLAIN (ANALYZE, BUFFERS).

Prerequisites

• A running PostgreSQL instance and a SQL client (psql or any GUI).
• A table big enough to notice: index design on a 1,000-row table is meaningless. Use generate_series to seed ~1,000,000 rows.

Why Indexes Matter: Full Table Scan vs Index Scan

Without an index, a WHERE clause forces a sequential scan - the planner walks every block of the table, checking every row. That is O(n) and it ignores all the work you did to prune rows.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..19342.00 rows=480 width=220)
                     (actual time=0.015..105.223 rows=480 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999520
Planning Time: 0.104 ms
Execution Time: 105.356 ms

Every EXPLAIN ANALYZE output in this post follows the same grammar. Read the indented tree inside-out: leaf nodes run first, results flow up. The second parenthesis shows reality - actual milliseconds, actual rows, and loops (multiply time by loops when it is above 1). If estimated rows= diverge wildly from actual rows=, you have stale statistics - run ANALYZE before trusting anything.

B-Tree Basics

The B-tree (balanced tree) is PostgreSQL’s default index type. It keeps keys sorted in a hierarchy of 8KB pages, so a lookup descends root -> internal nodes -> leaf node in O(log n) page reads. For ten million rows that is typically 3-4 reads instead of ~100,000 for a full scan. B-trees serve equality (=), ranges (<, >, BETWEEN), ordering (ORDER BY), and LIKE 'prefix%' patterns.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=480 width=220)
                                          (actual time=0.004..0.052 rows=480 loops=1)
  Index Cond: (customer_id = 42)
Execution Time: 0.087 ms

105ms -> 0.087ms. That is the entire value proposition of indexing.

Composite Indexes and Column Ordering

When a query filters on multiple columns, a single multi-column (composite) index beats two separate single-column indexes. For B-tree composites, column order is the design decision: the index is most efficient when constraints hit the leftmost (leading) columns first. The rule of thumb: equality filters first, range filters last.

-- Filter by customer, then sort/filter recent orders
CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42 AND created_at > '2026-01-01'
ORDER BY created_at DESC;

If you instead filtered on created_at alone, the leading customer_id column is of no help and the planner falls back to a scan. Also note INCLUDE: adding non-key payload columns (e.g. INCLUDE (total)) enables index-only scans, where PostgreSQL never touches the heap.

Partial Indexes: Index Only What You Query

A partial index stores entries for a subset of rows, defined by a WHERE clause. It is smaller, faster to scan, and cheaper to maintain than a full index. Web apps are full of these patterns: soft deletes, active status, pending jobs.

CREATE INDEX idx_orders_active
  ON orders (customer_id, created_at DESC)
  WHERE status = 'active' AND deleted_at IS NULL;

Gotcha: PostgreSQL will only use a partial index when the query’s WHERE mathematically implies the index predicate. Your query must say status = 'active' AND deleted_at IS NULL - a differently-worded equivalent will silently skip the index.

Expression Indexes

When you search on a computed value, index the expression itself. The query must use the exact same expression.

-- Case-insensitive login: never index the raw column for lower() lookups
CREATE INDEX idx_users_email_lower ON users (lower(email));

EXPLAIN ANALYZE
SELECT * FROM users WHERE lower(email) = 'ada@example.com';

Expression indexes are also the standard way to index JSONB fields you filter on in apps storing flexible metadata:

CREATE INDEX idx_devices_platform
  ON devices ((metadata->>'platform'))
  WHERE metadata->>'platform' IS NOT NULL;

Search is fast; maintenance is not free - the expression is recomputed on every insert/update.

GIN and Trigram Indexes for Search

B-tree cannot help with substring patterns like ILIKE '%search%' - a leading wildcard defeats its sorted structure. For “find anywhere in the string,” use a GIN index with pg_trgm, which decomposes text into 3-character chunks and indexes each chunk.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_products_name_trgm
  ON products USING GIN (name gin_trgm_ops);

EXPLAIN ANALYZE
SELECT * FROM products WHERE name ILIKE '%laptop%';

Bitmap Heap Scan on products
  Recheck Cond: (name ~~* '%laptop%'::text)
  ->  Bitmap Index Scan on idx_products_name_trgm
        Index Cond: (name ~~* '%laptop%'::text)

GIN is also the right tool for full-text search (tsvector) and array containment. Note that patterns with fewer than 3 characters extract no trigrams and degenerate to a full scan.

When NOT to Index

Indexes carry real costs: every insert/update/delete must also update every index, and each one consumes storage and memory. Skip the index when:

• The table is small. A 1,000-row lookup table scans faster than the index round-trip.
• The predicate is not selective. A query returning >5-10% of a table usually beats the index with a sequential scan; the planner ignoring your index here is correct behavior.
• The index is unused. Audit with pg_stat_user_indexes and drop dead indexes with DROP INDEX CONCURRENTLY.
• Writes dominate. High-write tables (logs, events) get only the few indexes that serve real read paths.

Putting It All Together

A complete exercise: a small SaaS schema, one million seeded orders, three progressive migrations, and measured results.

CREATE TABLE orders (
  id           bigserial PRIMARY KEY,
  customer_id  bigint NOT NULL,
  status       text NOT NULL DEFAULT 'active',
  total        numeric(10,2) NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  deleted_at   timestamptz
);

-- migration 1: the hot dashboard query
CREATE INDEX idx_orders_customer_created_active
  ON orders (customer_id, created_at DESC)
  WHERE status = 'active' AND deleted_at IS NULL;

-- migration 2: case-insensitive, heavily-used search
CREATE INDEX idx_orders_reference_trgm
  ON orders (reference) USING GIN (reference gin_trgm_ops);

-- migration 3: covering payload for the report query
CREATE INDEX idx_orders_customer_total
  ON orders (customer_id)
  INCLUDE (total, status);

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, total
FROM orders
WHERE customer_id = 42 AND status = 'active' AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 10;

Limit
  ->  Index Only Scan using idx_orders_customer_created_active on orders
        Index Cond: (customer_id = 42)
Buffers: shared hit=4
Execution Time: 0.067 ms

Read the evidence: an Index Only Scan (migration 3’s payload means no heap visits), Buffers: shared hit=4 means everything came from memory/cache, and LIMIT prunes the scan early. That is the difference between a database that works and one you spend your day babysitting.

Conclusion & Next Steps

Indexing is not about adding more indexes - it is about adding the right ones and removing the rest. The workflow stays the same every time:

1. Turn on logging via pg_stat_statements or auto_explain and catch the slow query.
2. Run EXPLAIN (ANALYZE, BUFFERS) before touching anything.
3. Choose the index matching the predicate: B-tree, composite (equality first), partial, expression, or GIN/trigram.
4. Re-run EXPLAIN ANALYZE; keep the index only if the plan genuinely improved.
5. Re-check pg_stat_user_indexes monthly; drop what the planner never uses.

Next steps: read up on covering index-only scans, ORDER BY support in indexes, and the pg_stat_statements extension for production monitoring.

References / Sources

• PostgreSQL Documentation: Chapter 11, Indexes
• PostgreSQL Documentation: 11.2, Index Types
• PostgreSQL Documentation: 11.3, Multicolumn Indexes
• PostgreSQL Documentation: 11.7, Indexes on Expressions
• PostgreSQL Documentation: F.35, pg_trgm
• PostgreSQL Documentation: 14.1, Using EXPLAIN