Database Indexing Strategies: Advanced Deep Dive

In this tutorial, you'll learn about Database Indexing Strategies: Advanced Deep Dive. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Database indexing strategies go beyond basic B-tree creation to cover partial indexes, covering indexes, index-organized tables, bitmap indexes, bloom filters, index maintenance, and index advisor tools for production workloads.

What You'll Learn

You will understand advanced index types (partial, covering, bitmap, bloom), index-only scan mechanics, index fragmentation detection and repair, how to design indexes for specific query patterns, and how to use index advisory tools.

Why Indexing Strategies Matter

A well-designed index makes queries 100x faster. A poorly designed one wastes storage and slows writes. Doda Browser indexes millions of bookmarked pages; choosing the right index Strategy cut search latency from 800ms to 4ms.

Indexing Strategies Learning Path

flowchart LR
  A[Database Indexing] --> B[Advanced Indexing Strategies]
  B --> C[Query Optimization]
  B --> D[Performance Monitoring]
  B:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

Partial Indexes

A partial index indexes only a subset of rows that match a WHERE condition. It is smaller and faster than a full index.

-- Full index: 10 million rows indexed
CREATE INDEX idx_orders_status ON orders (status);

-- Partial index: only 50,000 'pending' rows indexed
CREATE INDEX idx_orders_pending ON orders (status)
WHERE status = 'pending';

-- Size comparison
SELECT pg_size_pretty(pg_relation_size('idx_orders_status')) AS full_size,
       pg_size_pretty(pg_relation_size('idx_orders_pending')) AS partial_size;

Expected output:

 full_size | partial_size
-----------+--------------
 220 MB    | 1.2 MB

When to Use Partial Indexes

• Queries that always include a WHERE condition on a low-cardinality column
• Unique constraints on active subsets (one active subscription per user)
• Archival tables where only recent data is queried

-- Partial unique index: each user can have one active session
CREATE UNIQUE INDEX idx_active_sessions
ON sessions (user_id) WHERE ended_at IS NULL;

Covering Indexes and Index-Only Scans

A covering index contains all columns needed by a query, eliminating table heap access entirely.

-- Query: Select specific columns with a filter
SELECT user_id, order_date, total
FROM orders
WHERE status = 'completed'
ORDER BY order_date DESC;

-- Covering index: includes all returned columns
CREATE INDEX idx_orders_completed_covering
ON orders (status, order_date DESC) INCLUDE (user_id, total);

-- Now EXPLAIN shows "Index Only Scan" instead of "Index Scan"
EXPLAIN SELECT user_id, order_date, total
FROM orders
WHERE status = 'completed'
ORDER BY order_date DESC;

Expected EXPLAIN output:

Index Only Scan using idx_orders_completed_covering on orders
  Index Cond: (status = 'completed'::text)

Index-only scan performance gain:

Bitmap Indexes

Bitmap indexes store bitmaps for each distinct value. They excel at low-cardinality columns and complex boolean combinations.

-- PostgreSQL: bitmap scan happens automatically for OR conditions
EXPLAIN ANALYZE
SELECT * FROM products
WHERE category = 'Electronics'
   OR brand = 'DodaTech'
   OR price_range = '100-500';

PostgreSQL internally creates bitmap indexes per condition and combines them with bitwise AND/OR.

Comparison:

Bloom Indexes

Bloom filters are probabilistic indexes for multi-column equality queries. They are useful when any subset of columns may appear in WHERE clauses.

-- PostgreSQL: Bloom index extension
CREATE EXTENSION IF NOT EXISTS bloom;

CREATE INDEX idx_products_bloom ON products
USING bloom (name, category, brand, price);

-- Works for any combination of these columns
SELECT * FROM products
WHERE name = 'Laptop' AND brand = 'DodaTech';

SELECT * FROM products
WHERE category = 'Electronics' AND price = 999.99;

Bloom indexes trade some false-positive reads (re-checking rows) for the ability to index many columns efficiently.

Index Maintenance

Indexes degrade over time due to page splits, fragmentation, and bloat.

Detecting Fragmentation

-- PostgreSQL: Index bloat estimation
SELECT
    indexrelid::regclass AS index_name,
    round(100 * (1 - avg_leaf_density)::numeric, 2) AS fragmentation_pct
FROM pg_stat_user_indexes, LATERAL (
    SELECT avg_leaf_density
    FROM pgstatindex(indexrelid::text)
) stats
WHERE indexrelid::regclass IN (
    'idx_orders_status'::regclass,
    'idx_orders_pending'::regclass
);

Expected output:

    index_name     | fragmentation_pct
-------------------+-------------------
 idx_orders_status |             12.45
 idx_orders_pending|              3.21

Rebuilding Indexes

-- PostgreSQL: Rebuild without locking (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_status;

-- MySQL: Reorganize table (rebuilds all indexes)
OPTIMIZE TABLE orders;

-- SQL Server: Rebuild with online option
ALTER INDEX idx_orders_status ON orders REBUILD WITH (ONLINE = ON);

Recommended maintenance schedule:

Index Design Patterns

Pattern 1: Leftmost Prefix for Multi-Column Filters

-- Design index for queries filtering on (a), (a,b), and (a,b,c)
CREATE INDEX idx_a_b_c ON t (a, b, c);

Pattern 2: Sorting Without Explicit Sort

-- Cover both WHERE and ORDER BY with one index
CREATE INDEX idx_status_date ON orders (status, created_at DESC);

-- No separate sort step needed
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

Pattern 3: Exclusion Constraints (PostgreSQL)

-- Prevent overlapping date ranges for the same room
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
    room_id INT,
    booked_during TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, booked_during WITH &&)
);

Pattern 4: Expression Indexes

-- Index on a function result
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- Now this query uses the index
SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');

Common Indexing Strategy Errors

1. Creating Duplicate Indexes

An index on (a, b) covers any query that (a) alone would serve. Remove redundant single-column indexes.

2. Not Considering Write Overhead

Each index adds ~20% write overhead per index on INSERT. A table with 10 indexes is 3x slower to write to than one with none.

3. Using Default Index Types for Everything

B-tree is the default but not always the best choice. Use GIN for JSONB, GiST for geometry, and bloom for multi-column equality.

4. Ignoring Index-Only Scans

Adding INCLUDE columns turns an index scan into an index-only scan, often 2-5x faster for common queries.

5. Never Rebuilding Indexes

Fragmented indexes can be 30-50% slower than freshly rebuilt ones. Schedule regular maintenance.

6. Indexing Low-Selectivity Columns Alone

An index on a boolean column with 50% true / 50% false is almost never used. Combine it with other columns.

7. Not Dropping Unused Indexes

Run pg_stat_user_indexes to find indexes with idx_scan = 0. They waste write performance and storage.

Practice Questions

1. What is the difference between a partial index and a regular index?

A partial index only indexes rows matching a WHERE condition, making it smaller and faster for targeted queries.

2. How does an index-only scan work?

The index itself contains all columns needed by the query, so the database never reads the main table heap, reducing disk I/O.

3. When should you use a bitmap index?

For low-cardinality columns combined with OR conditions. PostgreSQL automatically uses bitmap scans when appropriate.

4. How do you detect index fragmentation in PostgreSQL?

Use pgstatindex() for detailed statistics or check avg_leaf_density to estimate fragmentation percentage.

5. Challenge: Design indexes for a SaaS dashboard.

Given these queries:

-- Query A: daily active users
SELECT COUNT(DISTINCT user_id) FROM sessions
WHERE login_date = CURRENT_DATE;

-- Query B: user's recent sessions
SELECT * FROM sessions
WHERE user_id = 123 AND login_date > CURRENT_DATE - 7
ORDER BY login_date DESC;

-- Query C: active users by plan
SELECT u.plan, COUNT(DISTINCT s.user_id)
FROM users u JOIN sessions s ON s.user_id = u.id
WHERE s.login_date = CURRENT_DATE
GROUP BY u.plan;

Design indexes. Answer: (A) Partial index on sessions(login_date) WHERE login_date = CURRENT_DATE with INCLUDE(user_id). (B) Composite index on sessions(user_id, login_date DESC). (C) Index on sessions(login_date, user_id) and index on users(id, plan). Consider a materialized view for Query C.

FAQ

Try It Yourself

Audit your current database indexes:

1. Run pg_stat_user_indexes to list all indexes and their usage counts
2. Identify indexes with idx_scan = 0 (unused)
3. For each unused index, review the queries on that table
4. Drop confirmed unused indexes to speed up writes
5. Rebuild fragmented indexes with REINDEX INDEX CONCURRENTLY
6. Measure query performance before and after maintenance

What's Next

You have mastered advanced indexing strategies including partial indexes, covering indexes, bloom filters, and maintenance patterns. Start by running an index audit on your production database and dropping unused indexes today.