postgresqlmongodbindexesdatabase-internalsb-treequery-performancebackend-engineering

How Indexes Work Under the Hood: B-Tree, Hash, and Beyond

MM
Moinuddin M Masud
26 min read

There is a specific kind of overconfidence that comes from knowing how to add an index.

You see a slow query, you run EXPLAIN, you spot a sequential scan, you add an index on the filtered column, and the query drops from 4 seconds to 12 milliseconds. That works often enough that it becomes a reflex. The problem is that the reflex eventually leads you somewhere it cannot get you out of: a table with eleven indexes that is slower to write to than a table with two, a compound index that is never used because the column order is wrong, a multikey index on a MongoDB collection that doubles memory usage without improving the queries you actually run.

The engineers who avoid these traps are not the ones who know more index types. They are the ones who understand what an index physically is — how it stores data, how the query planner decides whether to use it, and what it costs to maintain. That understanding is what this article builds.

We will cover B-Tree indexes in depth for both PostgreSQL and MongoDB, then look at Hash indexes, PostgreSQL's specialised index types (GIN, BRIN, partial, and covering indexes), and MongoDB's compound and multikey indexes. At each step the question is not just "what does this do" but "what is happening on disk and in memory when this runs."

What an Index Actually Is

Before going into types, it helps to be precise about what an index is at the mechanical level.

An index is a separate data structure, stored separately from your table or collection, that maps values of one or more columns/fields to the physical location of the rows or documents containing those values. When the query planner uses an index, it does two things: it traverses the index structure to find the matching entries, then it follows the physical location pointers back to the heap (PostgreSQL) or the collection B-tree (MongoDB) to fetch the full row or document.

That second step — following the pointer back to the actual data — is called a heap fetch in PostgreSQL or a fetch in MongoDB. It is often the most expensive part of an indexed read, and it is the step that most engineers forget to account for when reasoning about index performance.

An index does not eliminate I/O. It trades one kind of I/O (scanning every row) for a different kind of I/O (traversing the index structure plus fetching matched rows). Whether that trade is beneficial depends on selectivity — how many rows match your predicate. High selectivity (few matching rows) means the index wins. Low selectivity (most rows match) means a sequential scan often wins, because sequential I/O is much faster than the random I/O of following individual heap pointers.

This is why the query planner sometimes ignores your index on a column and does a sequential scan instead. It is not broken. It is making a correct decision that the index would be slower for that particular query.

B-Tree Indexes: The Universal Default

The B-Tree (Balanced Tree) is the default index type in both PostgreSQL and MongoDB, and for good reason — it handles the vast majority of query patterns efficiently. But "B-Tree" is a category, not a single algorithm. PostgreSQL and MongoDB implement it differently, and those differences matter.

B-Tree Structure: The Fundamentals

A B-Tree index organises values in a sorted tree structure with three levels of nodes:

  • Root node: the single entry point into the tree
  • Internal nodes: contain keys and pointers to child nodes; used for navigation only
  • Leaf nodes: contain the actual index entries — the indexed value plus a pointer to the heap location of the corresponding row
                    ┌─────────────┐
                    │  Root Node  │
                    │   [50, 75]  │
                    └──┬──────┬───┘
                       │      │
          ┌────────────┘      └────────────┐
          ▼                               ▼
   ┌─────────────┐                 ┌─────────────┐
   │Internal Node│                 │Internal Node│
   │  [20, 35]   │                 │  [60, 68]   │
   └──┬───────┬──┘                 └──┬───────┬──┘
      │       │                       │       │
      ▼       ▼                       ▼       ▼
 ┌────────┐ ┌────────┐           ┌────────┐ ┌────────┐
 │  Leaf  │ │  Leaf  │           │  Leaf  │ │  Leaf  │
 │1,5,12, │ │20,28,  │           │60,63,  │ │68,72,  │
 │15,18   │ │31,33   │    ...    │65,67   │ │74,75   │
 └────────┘ └────────┘           └────────┘ └────────┘
  (each entry → heap pointer)

The tree is kept balanced — all leaf nodes are at the same depth. This guarantees that any lookup traverses the same number of levels regardless of what value you are looking for, giving O(log n) lookup time. For a table with 100 million rows, a B-Tree index typically requires only 4–5 node traversals to find any value.

Leaf nodes are linked together in a doubly-linked list. This is what makes range queries efficient — once the B-Tree has found the starting value, it can walk the leaf node list sequentially to collect all values within the range without returning to the root.

PostgreSQL B-Tree: MVCC-Aware Indexing

PostgreSQL's B-Tree implementation (in src/backend/access/nbtree/) is deeply integrated with MVCC. This integration has consequences that affect both index performance and maintenance.

Index entries and tuple visibility: A PostgreSQL B-Tree index entry points to a specific heap tuple via its ctid (page number, slot number). When a row is updated — remember from Article 1 that PostgreSQL writes a new tuple rather than modifying in place — a new index entry is written pointing to the new tuple's ctid. The old index entry still exists, pointing to the old (now dead) tuple.

This means B-Tree indexes accumulate dead entries just as the heap accumulates dead tuples. VACUUM must clean both the heap and the indexes. An index on a high-update table can grow substantially due to dead entries, and those dead entries must be traversed during index scans, adding overhead.

HOT and index pruning: When a Heap Only Tuple (HOT) update occurs — an update that doesn't change any indexed column and fits on the same heap page — PostgreSQL avoids writing a new index entry entirely. The existing index entry still points to the old tuple location, but PostgreSQL follows a chain from the old tuple to the new one on the same page. This is invisible to the application but significantly reduces index write amplification on tables where updates do not touch indexed columns.

The practical implication: index your update patterns, not just your read patterns. If a column is updated frequently and indexed, every update requires an index write. If that column is never used in a query predicate, that index is costing you write overhead with no read benefit.

Page structure: Each PostgreSQL B-Tree page is 8KB (same as heap pages). A leaf page holds multiple index tuples — the indexed value plus a 6-byte ItemId containing the ctid. The number of entries per page depends on the size of the indexed value. An index on a 4-byte integer column holds many more entries per page than an index on a 200-byte varchar column, making integer indexes more cache-efficient.

Index-only scans: PostgreSQL introduced index-only scans (PostgreSQL 9.2+) to avoid the heap fetch step entirely. If all columns needed by a query are present in the index — either as the indexed key or in the INCLUDE clause — and the visibility map indicates the page is all-visible (no dead tuples that might be visible to some transactions), PostgreSQL can return results from the index alone without touching the heap.

-- Covering index: includes email for index-only scan
CREATE INDEX idx_users_username_incl ON users (username) INCLUDE (email);

-- This query can use index-only scan — no heap fetch needed
SELECT email FROM users WHERE username = 'moinuddin';

The visibility map is the limiting factor. Freshly updated pages are not marked all-visible until VACUUM runs. On a heavily updated table, index-only scans may frequently fall back to heap fetches because pages are rarely all-visible.

MongoDB B-Tree: WiredTiger's Implementation

MongoDB's indexes are also B-Trees, but implemented inside WiredTiger rather than as a PostgreSQL-style heap overlay. This creates a structurally different relationship between the index and the data.

In WiredTiger, each index is a separate B-Tree where leaf nodes contain the indexed field value plus a pointer to the document's position in the collection B-Tree (via RecordId). Unlike PostgreSQL's ctid, which encodes a physical page and slot, WiredTiger's RecordId is a logical identifier — it maps to the document's position in the collection B-Tree rather than a physical byte offset.

This indirection has an important consequence: when a document is relocated in the collection B-Tree (because it grew beyond its allocated space and WiredTiger had to move it), MongoDB must update all indexes that reference that document. In PostgreSQL, a HOT update can avoid index writes because the ctid chain handles the redirect. WiredTiger has no equivalent mechanism — every relocation triggers index updates.

Index key compression: WiredTiger compresses index keys using prefix compression within each B-Tree page. Adjacent index entries that share a common prefix store only the differing suffix. This makes WiredTiger indexes more memory-efficient than naive implementations, but it also means that highly random key distributions (like UUIDs as index keys) compress poorly and consume more cache than sequential keys.

// Poor index cache efficiency — UUID keys have no common prefix
db.events.createIndex({ eventId: 1 }) // eventId is UUID v4

// Better cache efficiency — timestamp prefix is shared across many entries
db.events.createIndex({ createdAt: 1, eventId: 1 })

The _id index: MongoDB automatically creates a unique B-Tree index on _id for every collection. This is not optional. It is used for the primary lookup path and for replication (the oplog references documents by _id). The _id index is always present in memory if any portion of the collection is active.

Compound Indexes: Column Order Is Everything

Compound indexes — indexes on multiple columns or fields — are where most engineers make their most consequential indexing mistakes. The column order in a compound index is not cosmetic. It determines which queries the index can serve and which it cannot.

PostgreSQL Compound Indexes

A PostgreSQL compound index stores entries sorted first by the first column, then by the second within each first-column value, then by the third within each second-column value, and so on. The sort order of the index is lexicographic.

-- This index sorts by (department, salary)
CREATE INDEX idx_emp_dept_salary ON employees (department, salary);

This index can efficiently serve:

  • WHERE department = 'Engineering' — uses the first column
  • WHERE department = 'Engineering' AND salary > 80000 — uses both columns
  • ORDER BY department, salary — can return results in index order, avoiding a sort

This index cannot efficiently serve:

  • WHERE salary > 80000 — the second column alone cannot use the index because entries are not globally sorted by salary; they are sorted by salary only within each department group

This is the prefix rule: a compound index can be used for any query that filters on a prefix of the index columns in order. Skipping the first column breaks the ability to do a B-Tree range scan.

Cardinality and column order: The conventional wisdom — "put the highest cardinality column first" — is right for equality predicates but wrong as a universal rule. The correct guidance is:

  • Columns with equality predicates (=) should come first, regardless of cardinality
  • Columns with range predicates (>, <, BETWEEN, LIKE prefix%) should come after equality columns
  • Columns used only for ordering (ORDER BY) should come last
-- Query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY score DESC
-- Correct index order:
CREATE INDEX idx_correct ON events (status, created_at, score DESC);

-- Wrong — range column before equality column wastes the equality advantage
CREATE INDEX idx_wrong ON events (created_at, status, score DESC);

MongoDB Compound Indexes and the ESR Rule

MongoDB has its own compound index ordering guidance: the ESR rule — Equality, Sort, Range. Fields used in equality conditions come first, fields used for sorting come second, fields used in range conditions come last.

// Query: find active users, sort by name, filter by age range
db.users.find({ status: "active", age: { $gt: 25 } }).sort({ name: 1 })

// ESR-correct index: equality (status), sort (name), range (age)
db.users.createIndex({ status: 1, name: 1, age: 1 })

// Wrong — range before sort wastes the sort benefit
db.users.createIndex({ status: 1, age: 1, name: 1 })

The ESR rule exists because MongoDB's query planner can use an index for sorting only if the sort fields appear in the index in the same relative order as the sort, and only if all equality conditions preceding the sort fields are covered by the index. Placing a range field before a sort field breaks the index's ability to serve the sort, forcing an in-memory sort.

The covered query: In MongoDB, a query is "covered" if all fields in the filter, sort, and projection are present in the index, and the projection explicitly excludes _id (or the index includes _id). A covered query never fetches documents from the collection — it serves the entire result from the index alone.

// Covered query — all required fields in the index
db.users.createIndex({ status: 1, name: 1, email: 1 })
db.users.find(
  { status: "active" },
  { name: 1, email: 1, _id: 0 }  // _id excluded; all fields in index
)

Covered queries in MongoDB are the equivalent of index-only scans in PostgreSQL — no document fetch required. But unlike PostgreSQL's index-only scans, which depend on the visibility map, MongoDB's covered queries are always fully covered as long as the field set matches. There is no equivalent of the visibility map complication.

Multikey Indexes: MongoDB's Most Misunderstood Feature

When you index a field that contains an array in MongoDB, you create a multikey index. WiredTiger inserts one index entry per array element, not one entry per document. A document with a ten-element array creates ten index entries.

// Document
{ _id: 1, tags: ["postgresql", "mongodb", "backend", "performance"] }

// Index on tags — creates 4 index entries for this one document
db.posts.createIndex({ tags: 1 })
// Index entries: "backend"→1, "mongodb"→1, "performance"→1, "postgresql"→1

This is powerful — you can query { tags: "postgresql" } and use the index efficiently even though tags is an array. But multikey indexes come with constraints and costs that catch engineers by surprise.

Constraints:

  • At most one field in a compound index can be a multikey field. You cannot create a compound index where two fields both contain arrays — MongoDB will reject the index creation.
  • Multikey indexes cannot cover queries — because the relationship between index entries and documents is one-to-many, MongoDB must always fetch the document to verify the full document matches the query predicate.
  • Multikey indexes cannot be used for $elemMatch conditions that span multiple fields within the same array element.

Cost: The index size grows with the average array length, not just the number of documents. A collection of 10 million documents where each document has an average of 50 array elements in the indexed field creates a 500 million entry index. This has direct implications for memory — the WiredTiger cache must accommodate the working set of the index, not just the collection.

In practice: if you find yourself indexing an array field with more than 10–20 elements on average, reconsider the data model. A separate collection with one document per array element and a reference back to the parent will give you a normal index with predictable size and performance.

PostgreSQL Specialised Index Types

PostgreSQL's indexing system is pluggable — you can write custom index types as extensions. In practice, four built-in specialised types cover the overwhelming majority of use cases beyond B-Tree.

Hash Indexes

Hash indexes store a hash of the indexed value rather than the value itself. This makes them extremely efficient for equality lookups — a hash lookup is O(1) rather than O(log n) — but useless for range queries, sorting, or partial matches.

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- Very fast — hash lookup
SELECT * FROM users WHERE email = 'moinuddin@example.com';

-- Cannot use hash index — no ordering in hash structure
SELECT * FROM users WHERE email > 'k@example.com';

Before PostgreSQL 10, hash indexes were not WAL-logged and would be corrupted on crash. As of PostgreSQL 10 they are fully crash-safe. They are still rarely used because B-Tree on an equality predicate is fast enough for most cases, but on very high cardinality columns with purely equality workloads — email addresses, UUIDs, API keys — hash indexes are measurably faster and significantly smaller.

MongoDB has no native hash index type as a distinct structure, but it supports hashed index fields for shard key distribution:

// Hashed index — used for hash-based sharding, not general equality queries
db.users.createIndex({ userId: "hashed" })

MongoDB's hashed index is for sharding purposes, not query optimisation. For equality queries on high-cardinality fields, a normal ascending index is the MongoDB equivalent of PostgreSQL's hash index.

GIN Indexes: For What's Inside the Value

GIN (Generalised Inverted Index) indexes invert the relationship between documents and values. Instead of mapping a row to its value, GIN maps each value component to the set of rows containing it. This is exactly what a search engine's inverted index does.

GIN indexes are used for:

  • Full-text search (tsvector / tsquery)
  • JSONB containment (@>, ?, ?|, ?&)
  • Array containment (@>, &&, <@)
  • pg_trgm trigram similarity (%, <->)
-- GIN on JSONB — efficient for containment queries
CREATE INDEX idx_events_data ON events USING GIN (data);

-- Uses GIN — "does data contain this key?"
SELECT * FROM events WHERE data @> '{"event_type": "purchase"}';
SELECT * FROM events WHERE data ? 'user_id';

-- GIN on array column
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];

GIN indexes are substantially larger than B-Tree indexes because they store inverted posting lists. A GIN index on a JSONB column can easily be 2–4× the size of the column data. They are also slower to update — adding or removing a document requires updating potentially many posting list entries. PostgreSQL mitigates this with a pending list: new GIN entries are added to a small pending list and bulk-merged into the main structure during VACUUM or when the list reaches a threshold (gin_pending_list_limit, default 4MB).

The practical consequence: GIN indexes on frequently updated columns can have significant write lag between the update and the new value being searchable. For JSONB data that changes rarely but is queried heavily for containment, GIN is exceptional. For rapidly updated JSONB, the pending list overhead becomes a concern.

MongoDB's equivalent for text search is the text index:

// Text index on MongoDB — similar inverted index structure to GIN
db.articles.createIndex({ content: "text", title: "text" })
db.articles.find({ $text: { $search: "postgresql performance" } })

MongoDB text indexes are also inverted indexes with similar trade-offs — large, write-expensive, powerful for text search. Unlike PostgreSQL's GIN with pg_trgm, MongoDB text indexes do not support fuzzy matching or similarity ranking natively. For production full-text search, both systems typically offload to a dedicated search engine (Elasticsearch, OpenSearch, Typesense), using the database index only for simple keyword matching.

BRIN Indexes: For Naturally Ordered Data

BRIN (Block Range INdex) indexes are the opposite of B-Tree in design philosophy. Where a B-Tree index stores an entry for every row, a BRIN index stores a summary (min and max value) for each range of heap pages (typically 128 pages = 1MB of heap by default).

-- BRIN on a time-series table where events are inserted in time order
CREATE INDEX idx_events_ts_brin ON events USING BRIN (occurred_at);

-- Very efficient for range queries on naturally ordered data
SELECT * FROM events WHERE occurred_at > NOW() - INTERVAL '1 hour';

BRIN indexes are tiny — a BRIN index on a 100GB time-series table might be 1MB, versus 10–20GB for a B-Tree on the same column. They are nearly free to update, because a new row inserted at the end of the table only updates the last block range summary.

The catch: BRIN only works well when the data is physically stored in approximately the same order as the indexed column. For a created_at column on an append-only events table, this is naturally true — new events go to the end of the heap in time order. For a general-purpose table where rows are inserted in arbitrary order, BRIN is useless because every block range will contain the full min-to-max value spread.

MongoDB has no equivalent to BRIN. The closest pattern is time-series collections (introduced in MongoDB 5.0), which use a columnar storage format internally and implicitly optimise range queries on the time field. For time-series workloads in MongoDB, using a proper time-series collection is more effective than any index strategy.

Partial Indexes: Indexing What You Actually Query

A partial index covers only the rows that satisfy a WHERE clause. It is one of the most underused optimisations in PostgreSQL.

-- Only index active users — inactive users are never queried by status
CREATE INDEX idx_users_active ON users (last_login DESC)
WHERE status = 'active';

-- This query uses the partial index — predicate matches
SELECT * FROM users WHERE status = 'active' ORDER BY last_login DESC LIMIT 20;

-- This query cannot use the partial index — different status
SELECT * FROM users WHERE status = 'suspended' ORDER BY last_login DESC;

The benefits compound when data is skewed. If 95% of your users are inactive and you only ever query active users by login time, a full index includes 95% entries that will never be accessed and must be maintained on every insert. A partial index is smaller by a factor of 20, faster to scan, and cheaper to maintain.

Partial indexes can also enforce conditional uniqueness:

-- Only one pending order per user at a time — but completed orders are unrestricted
CREATE UNIQUE INDEX idx_one_pending_order ON orders (user_id)
WHERE status = 'pending';

MongoDB does not have partial indexes in the same sense, but it has sparse indexes and partial indexes (added in MongoDB 3.2):

// Partial index — only index documents where status is "active"
db.users.createIndex(
  { lastLogin: -1 },
  { partialFilterExpression: { status: { $eq: "active" } } }
)

MongoDB's partial filter expression is more flexible than PostgreSQL's — you can use any valid query expression as the filter, including $exists, $type, and comparison operators. The query planner will use the partial index only when the query predicate implies the partial filter — meaning the query must include a condition that guarantees only documents matching the filter are being searched.

How the Query Planner Decides Whether to Use an Index

Understanding index structures is only half the picture. The other half is understanding how the query planner decides whether to use an index at all.

PostgreSQL: Cost-Based Planning

PostgreSQL's query planner is cost-based. It estimates the cost of every access path it considers — sequential scan, index scan, bitmap index scan, index-only scan — and chooses the one with the lowest estimated cost.

The cost model has two components:

  • seq_page_cost (default 1.0): the cost of reading one page in a sequential scan
  • random_page_cost (default 4.0): the cost of reading one page in a random access pattern

The default random_page_cost = 4.0 reflects spinning disk behaviour, where random seeks are expensive. On SSDs, random I/O is much cheaper — a common tuning recommendation is random_page_cost = 1.1 for SSD storage. If you are on SSD and your planner is choosing sequential scans when you expect index scans, check this setting first.

The planner uses table statistics — collected by ANALYZE, stored in pg_statistic — to estimate how many rows will match a predicate. If statistics are stale (the table has changed significantly since the last ANALYZE), the planner's row count estimates will be wrong, leading to bad plan choices.

-- Check when statistics were last collected
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE tablename = 'events';

-- Force statistics refresh
ANALYZE events;

When a planner chooses a sequential scan on a table where you expected an index scan, the question to ask is not "why isn't it using my index" but "what does the planner think the row count is, and is it correct." EXPLAIN (ANALYZE, BUFFERS) shows both the planner's estimate and the actual row count.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE user_id = 12345 AND created_at > NOW() - INTERVAL '7 days';

The Rows Removed by Filter and actual rows vs estimated rows discrepancy tells you whether a statistics problem is the root cause.

MongoDB: The Query Planner and Plan Cache

MongoDB's query planner works differently. It does not have a cost model as sophisticated as PostgreSQL's. Instead, it uses a race-based planning approach for new query shapes:

  1. It identifies candidate indexes for the query
  2. It runs each candidate plan in parallel for a small number of documents (up to 101 by default)
  3. It picks the plan that returned results fastest
  4. It caches that winning plan for the query shape

The cached plan is used for subsequent queries with the same shape (same fields, same operators, different values). The cache is invalidated when:

  • The collection grows or shrinks by more than 10%
  • An index is added or removed
  • mongod restarts
  • planCacheClear() is called manually

This approach has a subtle failure mode: the winning plan for the sample run may not be the best plan for all value distributions. If a query is sometimes run with highly selective values (few matching documents) and sometimes with low-selective values (many matching documents), the plan cached for the first case may be wrong for the second.

// Inspect the winning plan for a query
db.events.find({ userId: 12345, createdAt: { $gt: new Date("2024-01-01") } })
  .explain("executionStats")

The fields to look at in executionStats:

  • totalKeysExamined: how many index entries were scanned
  • totalDocsExamined: how many documents were fetched
  • nReturned: how many documents matched

A healthy index scan has totalKeysExamined close to nReturned. A large gap means the index is finding many candidates that don't survive the full document filter — either the index is poorly selective, or the query predicate has conditions that the index cannot fully evaluate.

The Real Cost of Indexes: What Nobody Talks About

Every index you create is a maintenance liability. Understanding the full cost model prevents the most common indexing mistake: adding indexes until the reads are fast, without accounting for what you have done to writes.

Write Amplification

Every INSERT must write one entry to every index on the table. Every UPDATE that touches an indexed column must write a new entry to every affected index (and, in PostgreSQL, mark the old entry as dead). Every DELETE must mark entries as dead in every index.

A table with ten indexes on a high-write workload is paying approximately 10× the index write overhead of a table with one index — plus the heap write. This is not hypothetical overhead. On a table receiving 50,000 writes per second, the difference between five indexes and ten indexes is measurable in CPU time, I/O bandwidth, and WAL generation rate.

The discipline: treat each index as a cost you are paying on every write, and make sure you are getting a read benefit that justifies it. In PostgreSQL, pg_stat_user_indexes tells you how many index scans each index has served. An index with zero or near-zero idx_scan count is pure write overhead.

-- Find indexes that are never used
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(indexrelid) DESC;

In MongoDB, the equivalent is $indexStats:

db.events.aggregate([{ $indexStats: {} }])
// Check "accesses.ops" for each index — zero means unused

Unused indexes in production are surprisingly common. They accumulate over time as features are added and query patterns change. A quarterly audit of index usage statistics pays dividends in write performance and storage savings.

Lock Contention During Index Creation

Creating an index on a large table in PostgreSQL takes an ACCESS SHARE lock that blocks writes for the duration of the build — potentially minutes or hours on a large table. The solution is CREATE INDEX CONCURRENTLY, which builds the index without blocking writes.

-- Blocks writes — never use on a production table without downtime
CREATE INDEX idx_events_user ON events (user_id);

-- Non-blocking — always use in production
CREATE INDEX CONCURRENTLY idx_events_user ON events (user_id);

CREATE INDEX CONCURRENTLY takes longer and uses more CPU (it performs two table scans instead of one), but it does not block. The trade-off is almost always worth it for tables with active write traffic.

In MongoDB, index builds in the background have been the default since MongoDB 4.2. A foreground index build (which blocks all reads and writes) is no longer the default. Background index builds still have an impact — they consume I/O and can slow query performance during the build — but they do not block application traffic.

Conclusion

An index is a physical data structure with real costs — storage, write amplification, cache pressure, maintenance overhead. Building the right index for a workload requires understanding not just what index types exist, but how they are laid out on disk, how the query planner decides to use them, and what they cost to maintain.

The engineers who make the best indexing decisions are those who can look at a slow query and ask: is the issue selectivity, column order, statistics staleness, cache miss, or write amplification from over-indexing? Each of those root causes has a different fix, and confusing them leads to solutions that make things worse.

In the next article, we go one level deeper into the query lifecycle: what happens between the moment your application sends a query and the moment results come back. Understanding that lifecycle — parsing, planning, execution, and result transport — is what lets you read a query plan and know not just what it says, but what it means.