用R导入多CSV至PostgreSQL遇内存不足，求分批处理方案

Absolutely! Your current code loads all CSV files into memory simultaneously before writing to PostgreSQL, which is why you're hitting memory limits. The fix is to iterate over each file individually—load one, write it to the database, then explicitly free up the memory before moving to the next file.

Here's the revised code with this approach:

# Connect to PostgreSQL database
db_tankdata <- 'tankdaten'
host_db <- 'localhost'
db_port <- '5432'
db_user <- 'postgres'
db_password <- 'xxx'

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = db_tankdata, host=host_db, port=db_port, user=db_user, password=db_password)

# Verify connection and table existence
if (!dbExistsTable(con, "prices")) {
  warning("The 'prices' table doesn't exist yet! You may need to create it first with the correct schema.")
}

# Get list of all CSV files
files <- list.files("path...", pattern = "*.csv", recursive = TRUE, full.names = TRUE)

# Process each file one by one
for (file in files) {
  # Print progress (optional but helpful)
  cat("Processing file:", basename(file), "\n")
  
  # Load single CSV file (use col_types to specify types and save memory if needed)
  single_price_df <- read_csv(file)
  
  # Write to PostgreSQL (append to existing table)
  dbWriteTable(con, "prices", single_price_df, append = TRUE, row.names = FALSE)
  
  # Free up memory: remove the data frame and force garbage collection
  rm(single_price_df)
  gc()
}

# Close the database connection when done
dbDisconnect(con)
dbUnloadDriver(drv)

Key improvements and notes: #

• Iterative processing: Instead of loading all files at once, we loop through each file individually, keeping only one dataset in memory at a time.
• Memory cleanup: Using rm() to delete the data frame and gc() to trigger garbage collection ensures that memory is freed up immediately after writing each file.
• Progress feedback: The cat() line lets you track which file is being processed, which is useful for large datasets.
• Schema check: The warning if the prices table doesn't exist reminds you to ensure the table's columns match your CSV structure (you can create the table manually first or use dbWriteTable with create = TRUE for the first file if needed).
• Memory-efficient reading: You can optimize read_csv further by specifying col_types (e.g., col_types = cols(date = col_date(), price = col_double())) to avoid R guessing types, which saves memory and speeds up loading.

内容的提问来源于stack exchange，提问作者Gonny