Navigate
Courses Tutorials Practice Pricing Tools Upgrade
Lesson 3 of 3

Bigger-Than-Memory Data

Maya's bakery chain has been logging every sale for five years. That till log is now a single CSV of about 38 million rows, roughly 12 GB on disk. Her laptop has 8 GB of memory. When she runs the import she has used since Lesson 1, R thinks for a minute and then quits:

read.csv("till_log_2021_2025.csv")
#> Error: cannot allocate vector of size 6.7 Gb

The file is bigger than the memory. In Lessons 1 and 2 you learned the DT[i, j, by] bracket and watched data.table beat dplyr on speed and memory, but every one of those tricks still assumed the table fit in RAM. This lesson is about the moment it does not.

By the end you will be able to:

  • Explain why a file larger than memory cannot simply be loaded into R
  • Stream a too-big file in chunks, keeping only a small running answer
  • Query data on disk with DuckDB, in SQL and in dplyr, never loading the whole thing

Prerequisites: you can run R; you know the data.table bracket from Lesson 1 and the dplyr verbs and the pipe. No databases or SQL assumed; we build the SQL up from scratch.

Why it breaks

R works in memory

Here is the thing to understand first, because everything else follows from it. R holds every object in RAM (random-access memory, the fast working space your computer can read instantly). When you call read.csv, R builds the entire data frame in RAM before you can touch a single row. Disk (the hard drive or SSD where the file lives) is far larger but far slower, and R does not work from it directly. So the file has to pass through memory, and a 12 GB file simply will not fit in 8 GB.

And memory grows in lock-step with the rows. A table with \(n\) rows costs about \(O(n)\) memory, meaning double the rows and you double the RAM. Watch it happen: the same four-column table at ten thousand, a hundred thousand, and a million rows.

RInteractive R
set.seed(1) # megabytes used by a 4-column numeric table, as the rows grow 10x each step mb <- sapply(c(1e4, 1e5, 1e6), function(r) { tbl <- data.frame(a = runif(r), b = runif(r), c = runif(r), d = runif(r)) as.numeric(object.size(tbl)) / 1e6 }) round(mb, 1) #> [1] 0.3 3.2 32.0

  

  





Ten times the rows, ten times the memory. There is no clever option that makes 38 million rows weigh nothing; the chart below is the wall Maya hit.




Note
Tidier types help a little: store a category as a factor or an integer instead of text, drop columns you do not need, and a table can shrink two or three times over. But that is a constant factor. It buys you a bigger laptop, not an unlimited one. Past some row count you still need a different idea.