This is the next article in my Computer Fundamentals: No BS series.

As a developer, We've worked with MongoDB and PostgreSQL for years. We've written queries, created indexes, handled transactions, and debugged slow endpoints.

But there's a difference between knowing how to use a database and understanding what happens inside it when a query runs.

That's what I wanted to explore in this part.

1. Databases don't work directly with individual records

When we query a database, it's tempting to imagine that it simply finds a record and returns it.

But databases generally read and write data in pages, not one individual row at a time.

A page is a fixed-size block of data containing multiple records. PostgreSQL commonly uses 8 KB pages.

Why does this matter?

Because reading one record may involve loading an entire page into memory. If the required page is already cached, the database can avoid going to storage.

This is one reason why data locality and memory usage matter so much for database performance.

2. RAM vs SSD: Why caching matters

RAM is significantly faster than SSD storage, but it's also limited and volatile.

Databases use memory to keep frequently accessed data available.

For example:

  • PostgreSQL uses shared buffers and also benefits from the operating system's filesystem cache.
  • MongoDB's WiredTiger storage engine maintains its own internal cache and also benefits from the OS cache.

When a query needs a page, the database may find it in memory or need to read it from storage.

This is where the idea of a cache hit and cache miss comes in.

A cache hit means the required data is already available in the cache. A cache miss means it needs to be fetched from another layer, potentially involving disk I/O.

A database can have plenty of RAM and still perform poorly if its working set doesn't fit in memory or its queries access data inefficiently.

3. B-Tree and B+Tree: How indexes help

One of the most important database concepts is indexing.

Imagine a table with a million users and a query like:

SELECT * FROM users WHERE email = 'alok@example.com'; 

Without a suitable index, the database may need to scan a large portion of the table to find the matching record.

An index gives the database a more efficient way to locate data.

A common structure behind database indexes is the B-Tree.

At a high level, a B-Tree keeps values organised so the database can navigate through a tree instead of checking every record.

A B+Tree is a related structure where internal nodes guide the search, while actual indexed entries are stored in leaf nodes. Leaf nodes are typically linked, which makes range scans efficient.

PostgreSQL uses B-Tree indexes, and MongoDB's standard indexes are also based on B-Tree-style structures.

But indexes aren't free.

They consume storage, and every insert, update, or delete may require index maintenance.

An index can make reads faster while making writes more expensive.

That's why adding indexes everywhere isn't necessarily a good idea.

4. What happens when a query runs?

This is probably the most interesting part.

Consider:

SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC; 

The database doesn't immediately start fetching rows.

A simplified query lifecycle looks like this:

  1. Parsing: Is the SQL syntactically valid?
  2. Analysis: Do the referenced tables and columns exist?
  3. Planning: What's the most efficient way to execute this query?
  4. Execution: Run the selected plan and return the results.

The planner is particularly interesting.

It can choose between different strategies:

  • Sequential scan — read through the table.
  • Index scan — use an index to locate matching records.
  • Bitmap scan — combine index results before fetching table pages.
  • Nested loop or hash join — different strategies for joining tables.

And here's something worth remembering:

Having an index doesn't guarantee that the database will use it.

If a query needs a large percentage of the table, a sequential scan might actually be cheaper.

We can use this to analyze a query:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; 

It shows the execution plan along with actual execution statistics.

For MongoDB, a similar tool is:

db.orders .find({ customerId: 42 }) .explain("executionStats"); 

Things like COLLSCAN, IXSCAN, documents examined, and keys examined can tell us a lot about query behaviour.

One important detail: EXPLAIN ANALYZE actually executes the query, so be careful when using it with data-modifying statements.

5. Transactions: More than just BEGIN and COMMIT

Let's take a simple e-commerce example.

A customer places an order. We need to:

  • Create an order.
  • Reduce inventory.
  • Record payment information.

What happens if the order is created but the inventory update fails?

We don't want the database to end up in an inconsistent state.

This is where transactions come in.

Transactions are commonly explained through ACID:

  • Atomicity: All operations succeed or the transaction is rolled back.
  • Consistency: Transactions preserve database constraints and rules.
  • Isolation: Concurrent transactions should not interfere in unexpected ways.
  • Durability: Once committed, changes survive failures, subject to the database's durability configuration.

Databases also use techniques like MVCC (Multi-Version Concurrency Control) to handle concurrent reads and writes.

Instead of making every reader wait for every writer, MVCC allows transactions to work with appropriate versions of data.

PostgreSQL uses MVCC extensively.

Another concept worth exploring is the Write-Ahead Log (WAL). Before database changes are considered committed, the database records the necessary information in its log. This helps with crash recovery.

6. MongoDB vs PostgreSQL: It's not about which is better

The biggest difference starts with their data models.

PostgreSQL is relational. It provides tables, relationships, foreign keys, joins, and strong relational constraints.

MongoDB is document-oriented. It stores BSON documents and often encourages modelling data around how the application reads and writes it.

For example, an order with its items could be represented using related tables in PostgreSQL or embedded documents in MongoDB.

Both approaches have trade-offs.

MongoDB supports multi-document transactions, and PostgreSQL supports JSONB for working with document-like data.

Neither database is universally better.

The right choice depends on data relationships, query patterns, consistency requirements, scaling needs, and how the application accesses its data.

The mental model I'm taking away

When my Node.js application executes a database query, the request goes through much more than a database driver.

A simplified picture:

Node.js Application | v DB Driver | v Database Server | v Parser & Planner | v Query Execution | v Index / Table Scan | v Database Pages | v Memory Cache / OS Cache | v SSD (if needed) 

For writes, there's also transaction management, concurrency control, logging, and recovery.

This changes how we think about database performance.

A slow query isn't always a database problem. It could be an inefficient query, a missing index, excessive disk I/O, poor data modelling, lock contention, or simply a mismatch between the workload and the database.

What's next?

In the next part, I want to explore Concurrency and Node.js Internals.

We've used async/await, promises, callbacks, and the event loop for years. Let's understand what's actually happening when multiple operations run concurrently, why race conditions occur, and how Node.js handles all this without creating a thread for every request.