How a database handles 10GB of Data with 2GB of RAM

Have you ever wondered how a database can query a 10GB dataset on a machine that only has 2GB of RAM? It seems like a physical impossibility. Does the database just "magically" compress the data? Or does it crash your server every time you run a SELECT *?

The truth is much more elegant. Databases are designed with the assumption that data is almost always larger than memory. Here is how they solve the 10GB vs 2GB paradox.

1. The Core Secret: The Buffer Pool

The most important concept to understand is that databases do not load the entire database into RAM.

Instead, they use a component called the Buffer Pool (or Buffer Cache). Think of the Buffer Pool as a high-speed "workspace" in your RAM. The database divides your data into small, fixed-size chunks called Pages (typically 8KB or 16KB).

When you request data, the database looks for the specific pages containing that data:

1. It checks if the page is already in the Buffer Pool.
2. If it's there (a Cache Hit ), it reads it instantly.
3. If it's not (a Cache Miss ), it fetches the page from the disk, puts it in the Buffer Pool, and then gives it to you.

2. Paging and Replacement: Making Room for New Data

If your Buffer Pool is 2GB and you keep requesting new data from a 10GB file, you will eventually run out of space. This is where Page Replacement Algorithms come in.

Most databases use an LRU (Least Recently Used) algorithm or a variation of it. When the Buffer Pool is full and a new page needs to be loaded:

• The database identifies pages that haven't been accessed in a while.
• It "evicts" those old pages from RAM to make room for the new ones.
• If an eviction involves a "Dirty Page" (data that was modified in RAM but not yet on disk), the database must ensure it's not lost.

2.1 The Safety Net: Write-Ahead Logging (WAL)

Updating a page on disk is slow. To stay fast, databases update the page in RAM and mark it as "dirty." But what if the power goes out?

To prevent data loss, databases use WAL :

1. Before changing the data page, the DB writes a small "note" (log record) about the change to a sequential file called the WAL Log.
2. Writing to a sequential log is extremely fast (no seeking).
3. Once the log is safely on disk, the transaction is considered "committed."
4. If the DB crashes, it simply replays the WAL log to rebuild the state of the Buffer Pool.

2.2 The Background Cleaner: Checkpointing

You can't let dirty pages sit in RAM forever. A background process called the Checkpointer periodically:

• Scans the Buffer Pool for dirty pages.
• Writes them back to their actual location in the data files on disk.
• Removes old WAL logs that are no longer needed.

3. How the Request Flow Works

This sequence diagram explains the full lifecycle of data in memory, handling both reading (with memory pressure) and writing (with durability):

4. The Role of Indexes: Speeding Up the Search

If the database had to scan the entire 10GB file (a Full Table Scan ) to find one row, it would have to cycle through all 10GB of pages, evicting and loading them constantly. This is known as Churning.

This is why Indexes are so crucial:

• An index is a smaller data structure (like a B-Tree) that tells the DB exactly which Page contains the data you need.
• Instead of loading 10GB, the DB only needs to load a few "index pages" and one "data page."
• This allows the DB to operate efficiently even when the RAM is a tiny fraction of the total data size.

5. Summary

So, how do you handle 10GB of data with only 2GB of RAM? The answer is simple: You don't have to fit it all at once.

RAM isn't where your data lives; it's where it works. By cycling only the active pages in and out of memory, the database stays lightning-fast without ever needing to swallow the whole 10GB at once.

Key takeaways:

• Pages : Data is sliced into manageable bites.
• Buffer Pool : Your workbench prioritized for what you use most.
• WAL & Checkpointing : Ensuring your work is safe even if the power cuts out.
• Indexes : The map that helps you grab exactly what you need without searching the whole house.

In reality, your "Working Set" (the data being actively used) is often just a small fraction of the total. As long as that set fits in memory, your database will stay fast, regardless of how massive the files on disk actually are.

6. Bonus: What if a single query exceeds 2GB?

If you run SELECT * FROM sales ORDER BY amount on a 10GB table, even the Buffer Pool can't "hide" the size.

When a database needs to sort or join more data than it has memory for, it uses External Merge Sort :

1. It reads chunks of data into RAM.
2. Sorts them in RAM.
3. Writes target "Runs" (sorted chunks) to temporary files on disk.
4. Finally, it merges these sorted runs into one final result.

This is why complex queries on large tables suddenly become much slower—they are literally "spilling to disk."

---