⚡ CrackLab
beginner~4h

Indexes — Clustered, Non-Clustered, Composite, Covering

Indexes are database structures that improve query performance by allowing fast data lookup without full table scans. Understanding indexes is one of the most critical skills for database performance

Every other performance topic in this course — query planning, EXPLAIN output, pagination — assumes you already understand what an index actually is and what it trades away. Indexes are the single most impactful lever for read performance in a relational database, and misunderstanding them is the root cause of most "why is this simple query slow" incidents.

Without an index, finding a matching row means scanning every single row in a table, in order, checking each one — perfectly correct, but hopelessly slow once a table has millions of rows. An index trades some write overhead and storage space for the ability to jump straight to matching rows instead of scanning past all of them.

Clustered index — determines the physical storage order of table rows; a table has at most one. Non-clustered index — a separate structure pointing back to the table's rows, independent of physical storage order; a table can have many. Composite index — an index over multiple columns together, most useful when queries filter/sort on that same combination. Covering index — an index that includes every column a query needs, letting the database skip fetching the full row entirely.

Indexes are database structures that improve query performance by allowing fast data lookup without full table scans. Understanding indexes is one of the most critical skills for database performance tuning.

Index types

  • B-Tree Index (default) — balanced tree; supports =, <, >, BETWEEN, LIKE 'prefix%', ORDER BY. Used for most queries.

  • Hash Index — O(1) lookup for = only; no range queries; faster than B-Tree for equality. PostgreSQL supports.

  • GIN (Generalized Inverted Index) — for array/JSONB/full-text search. PostgreSQL.

  • BRIN (Block Range Index) — very small; for physically ordered data (timestamps). PostgreSQL.

  • Clustered Index — the table data IS the index (rows stored in index order). One per table.

  • Non-Clustered Index — separate structure pointing to table rows. Multiple per table.

  • Composite Index — index on multiple columns. Column order matters.

  • Covering Index — includes all columns needed by a query; no heap lookup needed.

  • Partial Index — indexed subset of rows: WHERE condition. Smaller, faster.

  • Functional Index — index on expression: LOWER(email), EXTRACT(YEAR FROM date).

PostgreSQL terminology: PostgreSQL uses "heap" for tables and does not have a true clustered index concept. The CLUSTER command physically reorders the heap once. Use BRIN or range partitioning for similar effects.

B-Tree Index Structure

  • Root node: range split points directing to child nodes.

  • Internal nodes: more range split points.

  • Leaf nodes: actual index key values + pointer (TID = table identifier: block number, row offset).

  • Leaf nodes are doubly-linked for range scan efficiency.

Index Lookup Process

  • Query: SELECT * FROM employees WHERE dept_id = 2.

  • PostgreSQL reads index root page.

  • Traverses tree to find dept_id = 2 leaf entry.

  • Reads TID (physical row location: block 4, row 3).

  • Reads heap page 4, row 3 directly (random I/O).

  • Returns the row.

Covering Index (Index-Only Scan)

  • Index contains ALL columns needed by query.

  • PostgreSQL reads only the index — never touches the heap.

  • Sequential index I/O vs random heap I/O — much faster for range queries.

  • Requires visibility map to be up-to-date (VACUUM ensures this).

Step 1: Identify slow queries using EXPLAIN ANALYZE or pg_stat_statements.

Step 2: Look for Seq Scans on large tables in the query plan.

Step 3: Determine which columns appear in WHERE, JOIN ON, and ORDER BY.

Step 4: Choose index type (B-Tree for most; GIN for JSONB/arrays; BRIN for time-series).

Step 5: Create composite index with most selective column first.

Step 6: Consider covering index to avoid heap lookups.

Step 7: Use CREATE INDEX CONCURRENTLY in production to avoid table lock.

Step 8: Verify index is being used: EXPLAIN ANALYZE again.

Step 9: Monitor index usage: pg_stat_user_indexes (idx_scan = 0 means unused).

Step 10: Drop unused indexes — they slow down INSERT/UPDATE/DELETE.

  • Index columns used in WHERE, JOIN ON, and ORDER BY — these are the primary index candidates.

  • Composite index column order: most selective/most frequently filtered column first (leftmost prefix rule).

  • Use INCLUDE clause for covering indexes — add projected columns to leaf nodes without bloating the index tree.

  • Create indexes CONCURRENTLY in production — avoids table-level lock.

  • Limit indexes per table — every index costs INSERT/UPDATE/DELETE performance (each index must be updated).

  • Use partial indexes for skewed data: WHERE status = 'PENDING' (few pending vs millions completed).

  • Drop unused indexes regularly — monitor pg_stat_user_indexes.idx_scan = 0.

  • Functional indexes for expressions: LOWER(email), UPPER(name).

  • Avoid indexing low-cardinality columns (boolean, status with 2 values) — seq scan may be faster.

  • VACUUM regularly to keep the visibility map current for index-only scans.

  • Index on WHERE UPPER(email) = 'X' but index is on email — index not used. Create functional index on UPPER(email).

  • Too many indexes — a table with 15 indexes has 15× the write overhead. Each INSERT updates all 15 indexes.

  • Wrong composite index column order — index on (salary, dept_id) doesn't help WHERE dept_id = 1.

  • Index on low-cardinality columns (boolean flag) — planner chooses seq scan anyway when cardinality is low.

  • Not using CONCURRENTLY for large production tables — table locked for minutes/hours during index creation.

  • Index not maintained (bloat) — dead tuples accumulate; REINDEX periodically or auto_vacuum tunes.

  • Using LIKE '%pattern%' — index cannot be used for leading wildcard; use full-text search (GIN index).

  • Forgetting NULL handling — index entries exist for NULLs in PostgreSQL; WHERE col IS NULL can use index.

  • Use EXPLAIN (ANALYZE, BUFFERS) to see buffer hits vs disk reads.

  • Index-only scan: if all needed columns are in the index, heap is never touched.

  • Bitmap Index Scan: PostgreSQL batches multiple index lookups, then reads heap pages in order — better for range queries than Index Scan.

  • Multi-column statistics: CREATE STATISTICS for correlated columns (PostgreSQL 10+): CREATE STATISTICS stats_dept_salary ON dept_id, salary FROM employees.

  • pg_trgm extension: trigram indexes for LIKE '%middle%' patterns.

  • hypopg extension: simulate hypothetical indexes without creating them — test impact before committing.

Indexes don't themselves introduce a security risk, but an index that leaks in query plans or slow-query logs can reveal schema details (column names, cardinality) to anyone with log access — restrict access to production query logs and EXPLAIN output the same way you'd restrict schema access generally.

Track index usage statistics (pg_stat_user_indexes in PostgreSQL) to find indexes that are never actually used by the planner — an unused index still pays its full write-overhead cost on every insert/update with zero read benefit. Also watch for index bloat over time on heavily updated tables, which degrades index efficiency until a rebuild.

  • Before creating: verify with pg_stat_statements which queries are slow.

  • During creation: use CREATE INDEX CONCURRENTLY — takes longer but no lock.

  • After creation: run EXPLAIN ANALYZE to verify index is being used.

  • Regularly: check pg_stat_user_indexes for unused indexes (idx_scan = 0 after weeks of traffic).

  • REINDEX CONCURRENTLY to rebuild bloated indexes without downtime.

  • Index maintenance window: consider VACUUM ANALYZE after bulk loads.

  • Monitor: index bloat ratio — if (relpages * 8192) / (reltuples * avg_row_size) > 2.0, consider REINDEX.

  • Create the orders_large table (1 million rows). Run EXPLAIN ANALYZE on a query without an index, then create a B-Tree index and run again. Document the difference in execution time and cost.

  • Design a composite index for the query: SELECT * FROM orders WHERE status = 'PENDING' AND order_date > '2024-01-01'. Create both a regular composite index and a partial index. Compare their sizes using pg_indexes and pg_stat_user_indexes.

  • Create a covering index to make this query an index-only scan: SELECT customer_id, amount FROM orders WHERE status = 'COMPLETED'. Verify with EXPLAIN ANALYZE (look for 'Index Only Scan').

  • Find all unused indexes on a table using pg_stat_user_indexes. Create some indexes, run queries, and see which get used and which don't.

  • B-Tree: default; supports =, ranges, ORDER BY, LIKE 'prefix%'. Most indexes are B-Trees.

  • Composite index: leftmost prefix rule — column order matters critically.

  • Covering index (INCLUDE): all needed columns in index → index-only scan (no heap I/O).

  • Partial index: index subset of rows with WHERE clause — smaller, faster for skewed data.

  • CREATE INDEX CONCURRENTLY: production-safe index creation without table lock.

  • Too many indexes: every write must update all indexes — balance reads vs writes.

  • EXPLAIN (ANALYZE, BUFFERS): the essential tool for index verification.

  • pg_stat_user_indexes: monitor index usage; drop unused (idx_scan = 0).

Want a visual for this concept?

Generate a diagram tailored to “Indexes — Clustered, Non-Clustered, Composite, Covering” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Sign in to generate a visual →

Practice quiz

Next Step

Continue to Transactions — ACID, COMMIT, ROLLBACK, SAVEPOINT →← Back to all SQL chapters