Forem: Aayush Jain

Understanding Database Indexes: How They Work and When They Hurt Performance

Aayush Jain — Fri, 12 Dec 2025 21:05:14 +0000

We’ve all been there. You build a feature, test it on your local machine with 50 rows of data, and it feels lightning-fast. You deploy to production. Three months later, the database CPU is pinned at 99%, and your users are staring at loading spinners.

The fix is usually a single command: CREATE INDEX.

But while indexes are the "magic wand" of database performance, they aren't free. If you treat them as a "set and forget" feature, you might actually be making your application slower.

In this guide, we’re going to look at what’s actually happening inside your database when you search for data, and when an index goes from being a savior to a bottleneck.

1. The Nightmare: The Full Table Scan

Imagine walking into a library with 100,000 books and looking for a specific title. If the books are just piled in the middle of the room in no particular order, you have to pick up every single book until you find the right one.

In database terms, this is a Full Table Scan.

If your table has 1 million rows and no index on the column you are searching, the database engine has to read every single row from the disk. This is O(N) complexity. It’s slow, it’s expensive in terms of I/O, and it doesn't scale.

2. The "Dictionary" Analogy

The most common way to explain an index is the "Table of Contents" at the back of a book. It works, but a Dictionary is actually a better comparison.

In a dictionary, words are stored in alphabetical order. Because they are sorted, you don't start at page one to find the word "Node." You jump to the middle, see that "N" comes after the current page, and narrow your search.

This is what an index does: It creates a separate, sorted data structure that points to the actual location of the data.

3. Under the Hood: The B-Tree

If you want to move from Junior to Senior, you need to know the name B-Tree (Balanced Tree). Most modern databases (PostgreSQL, MySQL, SQL Server) use B-Trees for their default indexing.

A B-Tree solves the write-speed problem of simple sorted lists by organizing data into a hierarchy of "nodes."

The Root: The entry point.
Internal Nodes: These act like signposts ("Go left for A-M, go right for N-Z").
Leaf Nodes: The bottom of the tree that contains the actual pointer to the row on your disk.

Because the tree is balanced, the distance from the top to any piece of data is always roughly the same. Searching a B-Tree is O(log N). To put that in perspective: searching 1 million rows only takes about 20 "jumps" through the tree.

Conceptual Search Example

Imagine we are searching for a user with email = 'chris@example.com' in a large users table, and we have an index on the email column.

Instead of reading a million rows, the database conceptually performs a few logical steps:

Start at the Root Node (Jump 1): The root node holds boundaries (e.g., the lowest and highest values in each sub-branch). It determines that 'chris@example.com' falls between the values of the Middle Internal Node.
Move to Internal Node (Jump 2): The database loads the middle node. This node might say:

* Emails < F: Go Left

* Emails F-O: Go Middle

* Emails > O: Go Right Since C is before F, the database chooses the **Left Leaf Node** pointer.

Move to Leaf Node (Jump 3): The Leaf Node is loaded. It is a dense, sorted list containing the email and the primary key ID (the pointer). The database quickly performs a binary search within this small list:

// Example content of the Leaf Node (sorted list)
[
  { email: 'adam@example.com', userId: 101 },
  { email: 'ben@example.com', userId: 450 },
  { email: 'chris@example.com', userId: 221 }, // Found it!
  { email: 'diana@example.com', userId: 890 }
]

Fetch the Data: The database takes the userId: 221 and uses it to find the entire user record from the main table.

The search is complete in four disk operations (log N*)* instead of a million. That is the power of a B-Tree index.

4. Clustered vs. Non-Clustered Indexes

This is where many developers get confused. There are two main ways a database stores these indexes:

Clustered Index (The Physical Order)

Think of this as the dictionary itself. The data is physically stored on the disk in the order of the index. This is almost always your Primary Key.

Rule: You can only have one clustered index per table (because you can't physically sort the same data in two different ways).

Non-Clustered Index (The "Phone Book")

This is a separate structure from the actual table. It contains a copy of the indexed column and a "pointer" (like a GPS coordinate) to where the rest of the row lives.

Rule: You can have many non-clustered indexes, but each one adds "weight" to your database.

5. The Cost: When Indexes Hurt Performance

If indexes are so magical, why not just index every column? Because every index is a trade-off. It’s faster to read but slower to write, and costs money in storage.

A. The Write Penalty

Every time you perform one of these operations on a column that is indexed, the database has to update the B-Tree structure for that index:

INSERT: The database has to insert the new value and potentially re-balance the B-Tree to maintain its sorted structure.
UPDATE: If you update an indexed column (e.g., changing a user's email), the database has to delete the old entry in the index and insert a new one—a costly operation.
DELETE: The database must locate and remove the entry from the B-Tree.

If you have five indexes on a table, a single INSERT means the database must perform five index write operations in addition to writing the main record. On tables with very high write traffic (like a logging or telemetry table), too many indexes can severely degrade performance.

B. Storage and Memory Overhead

Indexes are separate data structures. They consume disk space and, critically, they consume memory.

Databases attempt to keep frequently accessed index nodes in RAM for speed. If your index size is larger than your available memory, the database constantly has to read those index nodes from the disk, negating some of the speed benefits and causing I/O bottlenecks. The larger your indexes, the less memory is available for caching your actual data.

6. Advanced Concepts: Indexing with Precision

To use indexes effectively, you must go beyond indexing individual columns. You need to understand your query patterns.

A. Composite Indexes (Order Matters)

A composite index uses multiple columns in a specific order. They are crucial for supporting complex WHERE and ORDER BY clauses.

Example: You frequently run the query:

SELECT * FROM orders
WHERE customer_id = 123 AND order_date > '2023-01-01'
ORDER BY order_date DESC;

You should create a composite index that matches the pattern: (customer_id, order_date).

Why order matters:

The database uses the first column (customer_id) to find a narrow slice of the data.
The database then uses the second column (order_date) to immediately satisfy the next condition and the ORDER BY clause without needing to sort the results later.

If you reverse the order to (order_date, customer_id), the index becomes useless for queries that only filter by customer_id. The database cannot skip the initial order_date search. The rule is: put the most selective column first.

B. The Cardinality Trap

Cardinality refers to the number of unique values in a column relative to the total number of rows.

High Cardinality: A column like email or SSN (every value is unique). Indexes are extremely effective here.
Low Cardinality: A column like is_active (only two values: true/false) or country (a few dozen values).

The Trap: Indexing a low-cardinality column is often pointless. If you search for is_active = true and that covers 90% of your table, the database optimizer will often decide it is faster to just do a full table scan than to jump through the index B-Tree, because it has to fetch almost all the rows anyway.

Summary: Rules of Thumb for Indexing

When to Index	When NOT to Index
Columns used in the `WHERE` clause.	Columns on small tables (e.g., < 10,000 rows).
Columns used in `JOIN` conditions.	Columns with very low cardinality (`is_active`, `status`).
Columns used in `ORDER BY` or `GROUP BY`.	On tables with extremely high write frequency.
The most restrictive column in a composite index (put it first).	Columns that are frequently updated.

The key takeaway is that indexing is a crucial exercise in balancing read speed against write cost. Always look at your database query execution plans (using EXPLAIN ANALYZE in Postgres or MySQL) to confirm that the index is actually being used and benefiting your performance.

Node.js Architecture Explained Simply: A Developer’s Guide to the Event Loop, Async Code, and Scaling

Aayush Jain — Mon, 08 Dec 2025 14:44:50 +0000

If you ask five developers, "Is Node.js multi-threaded?", you might get five slightly different answers.

"No, it's single-threaded." "Sort of, but it uses C++ threads in the background." "It depends on if you use Worker Threads."

If you are building backends with Node.js, you cannot treat it like a black box. Understanding how Node handles heavy traffic, how it manages async tasks, and why it sometimes "blocks" is the difference between an app that handles 10,000 users effortlessly and one that crashes when two people try to upload a file at the same time.

In this guide, we are going deep. We will skip the textbook definitions and look at what actually happens under the hood of your runtime.

1. Why Node.js Uses a Single Thread

To understand Node, you have to understand the problem it was trying to solve when it was created in 2009.

In traditional server architectures (like older versions of Java or PHP), the model was often "One Thread per Request."

User A requests a file? Spin up a thread.
User B requests a database row? Spin up another thread.
User C uploads an image? Another thread.

This works fine until you hit scale. Threads are expensive in terms of memory. If you have 10,000 concurrent connections, your server might crash just trying to manage the RAM for 10,000 threads, even if those threads are just waiting for a database to reply.

Node.js flipped the script.

It uses a Single Main Thread to handle the orchestration of requests. This eliminates the overhead of thread management context switching. But if there is only one thread, shouldn't one slow database query block the whole application?

It would, if Node didn't have its secret weapon: The Event Loop.

2. The Event Loop: A Clear Breakdown

The Event Loop is the mechanism that allows Node.js to perform non-blocking I/O operations despite being single-threaded.

Think of the Event Loop as an infinite while loop. It keeps running as long as there is work to do. But it doesn't just run code randomly. It cycles through specific Phases.

The Phases (Simplified)

Timers Phase: This is where setTimeout() and setInterval() callbacks are executed.
Pending Callbacks: Executes I/O callbacks that were deferred (like some TCP errors).
Poll Phase: The most important phase. This is where Node retrieves new I/O events (incoming data, file reads, connection requests) and executes their callbacks. Node will often pause here if there are no timers pending.
Check Phase: This is where setImmediate() callbacks run.
Close Callbacks: Cleanup tasks, like socket.on('close', ...).

Microtasks vs. Macrotasks

There is a "VIP Line" called the Microtask Queue. This is where Promises (.then(), await) and process.nextTick() live.

Critical Rule: The Event Loop checks the Microtask Queue after every single operation and between phases. If you have a Promise that resolves another Promise in an infinite loop, the Event Loop will never move to the next phase. You will starve the I/O and crash the server.

A Real Example

Let's look at how Node schedules tasks. What is the output order here?

console.log('1. Script Start');

setTimeout(() => {
  console.log('2. setTimeout');
}, 0);

setImmediate(() => {
  console.log('3. setImmediate');
});

Promise.resolve().then(() => {
  console.log('4. Promise');
});

process.nextTick(() => {
  console.log('5. nextTick');
});

console.log('6. Script End');

The Output:

1. Script Start (Synchronous)
6. Script End (Synchronous)
5. nextTick (Microtask VIP - runs immediately after main stack)
4. Promise (Microtask - runs after nextTick)
2. setTimeout (Macrotask - Timers phase)
3. setImmediate (Macrotask - Check phase)

Note: The order of setTimeout vs setImmediate can vary depending on context, but this is the general priority flow.

3. How Node.js Handles Async Operations

If Node is single-threaded, how does it read a file without stopping the rest of the app?

It cheats. It offloads the work.

Node.js is built on top of a C++ library called libuv. Libuv gives Node access to the operating system's underlying asynchronous capabilities.

There are two ways async work is handled:

Kernel Async (Network I/O): For things like TCP/HTTP requests, modern OS kernels (Linux, macOS, Windows) have built-in non-blocking mechanisms (like epoll, kqueue, or IOCP). Node hands the network request to the OS and says "Wake me up when data arrives." No extra threads are used here.
Thread Pool (File I/O, Crypto, DNS): The OS file system APIs are generally blocking. To get around this, libuv maintains a Worker Thread Pool (default size is 4 threads). When you run fs.readFile(), Node sends that task to one of these background C++ threads. When the thread finishes reading the file, it signals the Event Loop to run the callback.

So, is Node single-threaded? JavaScript execution is single-threaded. But the underlying runtime uses C++ threads for heavy lifting.

What this means in practice:

When you call fs.readFile or a crypto function that uses the thread pool, Node will schedule that work on a libuv worker thread. Your main event loop thread is free to keep handling other connections.
For true non-blocking operations such as many network sockets, the OS notifies libuv and callbacks are invoked on the main thread when data is ready.
If your code uses synchronous file APIs or performs heavy CPU loops on the main thread, the event loop cannot make progress until that work completes.

4. What Non-blocking Really Means (with Misconceptions Corrected)

Let's clear up some confusion about what "non-blocking" actually means in Node.js.

Misconception 1: All Node.js code is non-blocking

Not true. Only I/O operations have non-blocking APIs by default. Your JavaScript code runs synchronously on the main thread. If you have a loop that processes 10,000 items, that blocks.

Misconception 2: Async means concurrent

In Node.js, async means "this will complete later, do other things in the meantime." But the callback still runs on the same single thread. You can't have two pieces of JavaScript executing at the exact same moment in Node.

Misconception 3: Using promises or async/await makes code non-blocking

Promises and async/await are syntax for managing async operations. They don't make blocking code non-blocking:

// Still blocks for 5 seconds
async function slowWork() {
  const start = Date.now();
  while (Date.now() - start < 5000) {}
  return 'done';
}

What non-blocking actually means

When we say Node.js uses non-blocking I/O, we mean that when you initiate an I/O operation, the function returns immediately. Your code continues running. When the operation completes, Node invokes your callback.

The benefit isn't that I/O is fast. The benefit is that while waiting for slow I/O, your program can handle other requests. It's about better resource utilization, not raw speed.

5. CPU-bound vs I/O-bound Work

Understanding the difference between CPU-bound and I/O-bound work is crucial for knowing when Node.js is a good fit and how to architect your application.

I/O-bound work (Node is Great) is when you're waiting on external resources:

Database queries
API calls
File system operations
Network requests

Node excels at this. One thread can manage thousands of concurrent I/O operations because it's not actually doing the work, just coordinating.

CPU-bound work (Node is Weak) is computation:

Image processing
Video encoding
Complex calculations
Parsing large JSON files
Encryption/hashing large amounts of data

This is where Node's single-threaded model becomes a constraint. While one request is doing heavy computation, all other requests wait.

Here's a real example I've seen cause production issues:

app.post('/resize-image', async (req, res) => {
  const image = req.body.image;
  // This resizing might take 200ms of CPU time
  const resized = await resizeImage(image);
  res.send(resized);
});

If you get 10 requests per second, and each takes 200ms of CPU time, you need 2 seconds of CPU time per second. That's impossible with one core. Requests start queuing up, response times shoot up, and your server falls over.

The solution isn't to avoid Node.js for CPU work. It's to move that work off the event loop using worker threads or by delegating to a separate service.

6. Scaling a Node.js Backend

Node's single-threaded model means one process can only use one CPU core. If you have an 8-core machine, you're leaving 7 cores idle. Here's how to actually scale.

Clustering

The cluster module lets you fork multiple Node processes that share the same server port:

const cluster = require('cluster');
const http = require('http');
const numCPUs = require('os').cpus().length;

if (cluster.isMaster) {
  // Fork workers
  for (let i = 0; i < numCPUs; i++) {
    cluster.fork();
  }

  cluster.on('exit', (worker) => {
    console.log(`Worker ${worker.process.pid} died`);
    cluster.fork(); // Replace dead workers
  });
} else {
  // Workers share the same port
  http.createServer((req, res) => {
    res.end('Hello from ' + process.pid);
  }).listen(8000);
}

Now you have one process per CPU core, and the OS does round-robin load balancing between them. This is usually the first step in scaling Node.

One big caveat: these processes don't share memory. If you store session data in memory or maintain any in-process state, each worker has its own copy. You'll need to externalize that state.

Load Balancing

For production, you typically put a load balancer in front of your Node processes. This could be:

Nginx or HAProxy on the same machine
A cloud load balancer (AWS ALB, Google Cloud Load Balancing)
Service mesh if you're in Kubernetes

The load balancer distributes traffic across multiple instances of your application, possibly on different machines. This scales beyond one machine's CPU and memory limits.

Worker Threads

For CPU-intensive tasks within a request, worker threads let you run JavaScript in parallel:

const { Worker } = require('worker_threads');

function runHeavyTask(data) {
  return new Promise((resolve, reject) => {
    const worker = new Worker('./heavy-task.js', {
      workerData: data
    });

    worker.on('message', resolve);
    worker.on('error', reject);
    worker.on('exit', (code) => {
      if (code !== 0) {
        reject(new Error(`Worker stopped with exit code ${code}`));
      }
    });
  });
}

app.post('/process', async (req, res) => {
  const result = await runHeavyTask(req.body);
  res.json(result);
});

Worker threads run in separate threads with their own V8 instances. They can do CPU work without blocking the main event loop. But there's overhead in creating workers and passing data between them, so don't spawn a worker for every request. Use a worker pool.

Handling State

When you scale horizontally (multiple processes or machines), you can't rely on in-memory state. Here's what needs to move out:

Sessions: Use Redis or a database instead of memory stores. Libraries like connect-redis make this easy with Express.

Caching: Use Redis or Memcached instead of in-memory caches like node-cache.

Scheduled jobs: Use a distributed job queue like Bull (backed by Redis) instead of setInterval.

WebSocket connections: These are sticky to a process. Use sticky sessions in your load balancer, or consider a pub/sub system like Redis to broadcast messages across all processes.

The general rule: any state that needs to survive a process restart or be visible across multiple instances should be external.

7. Common Mistakes Beginners Make

Let me walk through mistakes I see repeatedly, even from experienced developers new to Node.

Blocking the event loop with heavy computation

// Bad: blocks for 100ms
app.get('/bad', (req, res) => {
  const result = heavyComputation();
  res.json(result);
});

// Good: offload to worker thread
app.get('/good', async (req, res) => {
  const result = await workerPool.exec(heavyComputation);
  res.json(result);
});

Using synchronous APIs in production code

// Never do this in a request handler
app.get('/config', (req, res) => {
  const config = JSON.parse(fs.readFileSync('./config.json'));
  res.json(config);
});

Synchronous file operations block the entire server. Load configuration at startup, not on every request.

Not handling promise rejections

// This will crash your server if the promise rejects
app.get('/data', (req, res) => {
  fetchData().then(data => res.json(data));
});

// Always handle errors
app.get('/data', async (req, res, next) => {
  try {
    const data = await fetchData();
    res.json(data);
  } catch (err) {
    next(err);
  }
});

Unhandled promise rejections used to just log a warning. Now they crash your process.

Creating a new database connection per request

// Bad: connection overhead on every request
app.get('/users', async (req, res) => {
  const db = await MongoClient.connect(url);
  const users = await db.collection('users').find().toArray();
  await db.close();
  res.json(users);
});

Use connection pooling. Create the connection once at startup and reuse it.

Forgetting to set timeouts

// Without timeouts, a slow external API can hang requests forever
const response = await fetch('https://slow-api.com/data');

// Better: set a timeout
const controller = new AbortController();
const timeout = setTimeout(() => controller.abort(), 5000);
const response = await fetch('https://slow-api.com/data', {
  signal: controller.signal
});
clearTimeout(timeout);

Using process.exit() in web applications

This immediately terminates your server, killing any in-flight requests. Let the server finish gracefully or use proper shutdown handling.

Memory Leaks

Storing data in global variables. Since the Node process runs forever (unlike a PHP script that dies after a request), global arrays just keep growing until the server runs out of RAM.

8. Practical Tips to Design Better Node.js Backends

Here's what I've learned from building and scaling Node applications in production.

Profile before optimizing: Use the built-in profiler to find actual bottlenecks:

node --prof app.js
# Generate load, then stop the server
node --prof-process isolate-*-v8.log > processed.txt

Or use clinic.js for a more visual approach. Don't guess where your performance problems are.

Keep the event loop fast: Each callback should complete in microseconds, not milliseconds. If you need to do heavy work, break it into chunks:

async function processLargeArray(items) {
  for (let i = 0; i < items.length; i++) {
    await processItem(items[i]);

    // Let other work run every 100 items
    if (i % 100 === 0) {
      await setImmediate();
    }
  }
}

setImmediate() returns a promise that resolves after the next check phase, giving other callbacks a chance to run.

Design for horizontal scaling from the start: Even if you start with one server, assume you'll need multiple instances later. Don't store state in memory, don't rely on process-level caching, and design your system to be stateless.

Use streams for large data: Instead of loading an entire file into memory:

// Memory efficient
app.get('/large-file', (req, res) => {
  const stream = fs.createReadStream('./large-file.json');
  stream.pipe(res);
});

Streams process data in chunks, keeping memory usage constant regardless of file size.

Increase the Thread Pool: If your app does heavy File I/O or Crypto, the default pool of 4 threads might be a bottleneck. You can increase this by setting the UV_THREADPOOL_SIZE environment variable (e.g., to 64).

Keep Dependencies Light: Node's module system is heavy. Every require adds startup time and memory overhead.

Monitor event loop lag: Libraries like event-loop-stats or loopbench can alert you when the event loop is getting blocked. If you see lag consistently over 50ms, something is blocking.

Separate CPU-heavy services: If you have both CPU-intensive and I/O-heavy endpoints, consider splitting them into separate services. Let Node handle the I/O-bound work, and use a different language or worker-based architecture for CPU work.

Set up proper logging and error tracking: Use structured logging (like pino or winston) and error tracking (like Sentry). When something goes wrong in production, you need to know what path led there.

Write integration tests for async flows: Async code is harder to test. Don't just test happy paths. Test what happens when promises reject, when operations timeout, and when errors occur in callbacks.

Wrap Up

Node.js is a powerhouse when used correctly. Its event-driven architecture makes it perfect for the modern web of real-time applications, microservices, and high-concurrency APIs.

But it requires a shift in thinking. You aren't just writing scripts; you are managing a timeline of events. Master the Event Loop, respect the single thread, and you will build systems that are fast, efficient, and scalable.